SQL Server Unique Constraints for Large Text Columns

In SQL Server, an unique constraint is enforced using an unique index on the column. But the index key size has a limitation of 900 bytes and this is why we can't enforce the unique constraint on columns over 900 bytes. This limitation not only applies to indexes, but also for foreign keys and primary keys as well. This 900 byte limitation is documented in the Maximum Capacity Specifications for SQL Server.

http://www.mssqltips.com/tip.asp?tip=1868

Generating SQL Scripts using Windows PowerShell

In this tip Edwin Sarmiento covers the topic: how to generate scripts using Windows PowerShell

http://www.mssqltips.com/tip.asp?tip=1842

Best Practice - Increasing the Number of SQL Server Error Logs

The SQL Server error log is a great place to find information about what is happening on your database server. Each SQL Server Error log will have all the information related to failures / errors that has occurred since SQL Server was last restarted or since the last time you have recycled the error logs. By default, there are six achieved SQL Server Error Logs along with the ERRORLOG which is currently used. However, it is a Best Practice to increase the number of SQL Server Error Logs from the default value of six. In this tip, you will see the steps which you need to follow to increase the number of SQL Server Error Logs.

http://www.mssqltips.com/tip.asp?tip=1835

SQL Server UDF to pad a string

While Transact-SQL (T/SQL) does not offer a comparable function similar to LPAD or RPAD available in other RDBMSs, the SQL Server Professional does have the REPLICATE() function that can be used to build a simple user-defined function that can be used to pad a string. Let's take a look at the REPLICATE() function and what it offers before moving on to the code for the custom padding function.

REPLICATE (string_expression ,integer_expression) will allow you to replicate a character string (the string_expression parameter the number of times consecutively per the integer_expression parameter).

http://www.mssqltips.com/tip.asp?tip=1738

Sending email from SQL Server Integration Services (SSIS)

Sending an email is a frequent requirement to notify a user on the occurrence of certain events, especially if an unexpected event happens (for example sending notification on failure that could be either logical or physical). SSIS provides a built-in "Send Mail Task" to send email in these circumstances. The Send Mail Task is quite simple and straight forward in its configuration and use, but it has some inherent limitations for example, first it supports only sending plain text email (doesn’t support HTML formatted mail) and second it doesn’t support passing username and password while connecting to SMTP server (it only supports Windows authentication i.e. none windows authentication is not allowed) nor does it support specifying a SMTP port number to send emails if your SMTP server does not use the default value.

http://www.mssqltips.com/tip.asp?tip=1731

Different ways to execute a SQL Agent job

Every database person might have come across the situation of maintenance tasks such as backing up of databases, re-indexing tables and other such tasks. We often schedule jobs for such tasks, so that they execute as per the set schedule. But there is sometimes the need for these tasks to be executed “On Demand”. This tip shows various ways of executing such tasks on demand by any user regardless of whether the person is technical or not.

http://www.mssqltips.com/tip.asp?tip=1730

Database level permissions for SQL Server 2005 and 2008

SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the database.

Below are the list of database-level permissions:
http://www.mssqltips.com/tip.asp?tip=1718

Writing to an operating system file using the SQL Server SQLCLR

The solution is to create a stored procedure that is implemented in the SQLCLR, which allows writing code in .Net languages and running them within SQL Server. Stored procedures can be written in C#, VB.Net or C++ and the compiler produces an assembly, which is the code compiled into .Net Intermediate Language (IL). The assembly is then loaded into SQL Server and a stored procedure is defined to call one of the static methods in the assembly. When the stored procedure is invoked by a T-SQL EXECUTE statement the .Net assembly is loaded, Just-in-Time (JIT) compiled into machine code and the machine code is loaded into SQL Server's memory. SQLCLR code is similar to extended stored procedures and it is intended to replace extended stored procedures when that feature is phased out of SQL Server in a future release.

http://www.mssqltips.com/tip.asp?tip=1662

How To Get Active Directory Users and Groups Using SQL Server Integration Services SSIS 2005

We have a requirement to implement security in our data warehouse to limit what data a user can see on a report. As an initial step we have created tables for users and roles; we also have a user role table where we specify the role(s) that a user is assigned. We would like to update the user, role, and user role tables automatically from Active Directory. Ideally we'd like an SSIS package that we could schedule and/or run on demand to take care of the update. Can you provide an example of how to do this?

http://www.mssqltips.com/tip.asp?tip=1657

Enforcing business rules using CHECK constraints

SQL Server allows you define column level CHECK constraints which check the integrity of a single column. Furthermore, SQL Server allows you to check the values of multiple columns together using a "table level" CHECK constraint. Though they're labeled as "table level" constraints, these type of CHECK constraints are actually checked at the row level. Lastly, CHECK constraints work by examining a defined condition to see if it evaluates to either TRUE or FALSE.

http://www.mssqltips.com/tip.asp?tip=1650

SQL Server 2008 T-SQL Auditing Commands for SELECT statements

This Server Audit object will be responsible for collecting server and/or database-level actions and groups of actions that you want to monitor and is created at the instance level. This also means that if you have multiple instances running on a server, you would have to create Server Audit objects per instance.

Since both the server audit object and database audit specification are disabled by default after creation, we need to enable them. To verify their state, you can query the sys.server_file_audits and the sys.database_audit_specifications system objects. The is_state_enabled column would tell us of their state.

http://www.mssqltips.com/tip.asp?tip=1655

Finding a string value in a SQL Server table

Option 1: Use the CHARINDEX() Function
Option 2: Use the LIKE Condition

http://www.mssqltips.com/tip.asp?tip=1654

New Security Catalog Views in SQL Server 2005/2008

SQL Server 2005 introduced the concept of securables, which are anything within SQL Server which you can assign permissions against. This includes the SQL Server itself. Microsoft did this to provide more granular access in order to customize security to what you need. But with more flexibility comes more complexity. As a result, you've got to look in a few more places to understand the permissions a given login or database user has.

The use of stored procedures for roles such as sp_helprolemember and sp_helpsrvrolemember is still necessary because some permissions are assigned directly to the roles. Some roles, like db_datareader and db_datawriter, have implicit rights that will need to be taken into account. Those should stay a staple of your toolset when trying to determine security within SQL Server. The reason sp_helprotect is no longer viable at the database level is because it is included for backward compatibility and therefore only reports on those securables that were present in SQL Server 2000, such as tables, views, functions, and the like. Newer securables, such as schemas and databases, weren't in SQL Server 2000 and as a result, sp_helprotect won't report permissions on them because it doesn't report against any securable that wasn't present in SQL Server 2000.

http://www.mssqltips.com/tip.asp?tip=1653

Using OPENROWSET to read large files into SQL Server

Using OPENROWSET to read large files into SQL Server

The BULK option was added to T-SQL in SQL Server 2005 and it persists in SQL Server 2008. When using SQL Server 2000 it was possible to read and write to the file system using the sp_OA_Create and sp_OA_Method extended stored procedures. These XPs continue to work, but are disabled by default because of security concerns and it is a better practice to use more secure capabilities like OPENROWSET when they are available.

http://www.mssqltips.com/tip.asp?tip=1643

Using Page Level Restore as a Disaster Recovery Procedure in SQL Server 2005

Using Page Level Restore as a Disaster Recovery Procedure in SQL Server 2005

Let's have a look at how to use page-level restores on a corrupted database. I will still be using the Northwind database for this tip - except that the database version that I will be using is corrupted. Note that it is not that easy to generate a corrupt database in your production environment so you would have to create one on your own to test these procedures. What I did was to use a hex editor to modify the values of the database file. This would introduce inconsistencies in the database file causing it to be corrupt. You would need to do some trial-and-error to get a specific page corrupted. In my case, I have chosen to corrupt a data page affecting the Orders table as it would be easy to find the text values of the records inside this table (as compared to the Order Details table that I have been using in the previous examples which contains mostly numerical data) from the hex editor.

http://www.mssqltips.com/tip.asp?tip=1645

Using SQL Server meta data to list tables that make up views

One of the issues I often face is the need to find views that are already established for certain tables. This maybe for other developers, end users or even for myself. I could search the system tables to find this or explore each view, but are there other ways to easily find a list of all tables that are used for a view or even a list of all views that a table is tied to?

http://www.mssqltips.com/tip.asp?tip=1638

SQL Server 2008 Sparse Columns Identifying Columns For Conversion

SQL Server 2008 has introduced a new way to store data for columns that contain excessive NULL values called Sparse Columns. What this means is that when you declare a column as Sparse and any time a NULL value is entered in the column it will not use any space. Is there a way to identify what columns would make a good candidate for this without having to analyze each column individually?

http://www.mssqltips.com/tip.asp?tip=1639

Using SQL Server DMVs to Identify Missing Indexes

Using SQL Server DMVs to Identify Missing Indexes
The dynamic management views are:

- sys.dm_db_missing_index_details - Returns detailed information about a missing index
- sys.dm_db_missing_index_group_stats - Returns summary information about missing index groups
- sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
- sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.

http://www.mssqltips.com/tip.asp?tip=1634

How to setup and use a SQL Server alias

By using an alias, we can keep the application configuration the same, but tell the operating system to look somewhere else for the database. One proviso is that once an alias is created, it is set at the operating system level. That means if we have two applications which connect to MySQLServer on that computer, once the alias is created, both will be re-pointed based on the alias. You can create an alias using one of two utilities:

1. SQL Server Configuration Manager
2. SQL Server Client Network Utility

http://www.mssqltips.com/tip.asp?tip=1620

Using the FORFILES Command to Delete SQL Server Backups

Recently I constructed a new backup process that I want to institute globally across all my SQL Server 2005 instances. This backup process will not only backup all the databases I point it towards, but at the same time will script out the backup commands to a single file in the format of F_YYYYMMDD.sql if the backup process is a full database backup or D|T_YYMMDD_HHMMSS.sql if the backup process is a differential (D) or transaction log (T) backup. These script files are then stored in a subfolder under the backup directory on the SQL Server. The process works great, but I only don't want to keep every .sql file. I cant see the need to keep these scripts after 30 days. Is there a way I can automate a file deletion process from within SQL Server?

http://www.mssqltips.com/tip.asp?tip=1618