February 2009 - Tip of the Week

SQL Server can guarantee unique values in two ways, Primary keys and a unique indexes/constraints. The primary difference between the two is that fields bound to a unique index or constraint can be NULL while a primary key field can not. For the purpose of the unique indexes and constraints, NULL values are treated equal. That means that you cannot have two NULL values. Fortunately, there is a workaround for that will allow you to store multiple NULL values while ensuring that non-NULL values are...

Below is a quick and easy way to query SQL Server 2005 system views to determine which users belong to which server roles on the instance. SELECT rolename = rolep.name, membername = memp.name FROM sys.server_role_members rm JOIN sys.server_principals rolep ON rm.role_principal_id = rolep.principal_id JOIN sys.server_principals memp ON rm.member_principal_id = memp.principal_id To determine which users are members of your database level roles on your instance, see this article .

Table aliases are an often overlooked, underused, and confusing facet of database management systems. While table alaises may be confusing at first, they are wonderful for making queries easier to read, and easier to write. A table alias is nothing more than assigning a pseudo name to a table name in a SQL statement. For example, the following SELECT statement DOES NOT use a table alias because it is not helpful to do so. Table aliases are most helpful when you're joining two or more tables and...

SQL Server Management Studio provides some neat shortcut features that makes it very quick easy to run certain stored procedures if you're in a hurry. Using these shortcut keys, you can assign administrative procedures to certain keyboard combinations to allow for quick execution without having to type the procedure name. First, from the Tools menu, select Options. Once on the Options screen, select the Keyboard option in the Environment menu. On they Keyboard menu, you can assign certain stored...

SQL Server Identity Columns SQL Server IDENTITY columns are numeric columns which are set to auto increment when new values are inserted into the table. These columns are great for uniquely identifying records in a table, especially when you do not have any natural key to define a primary key constraint on the table. Like a primary key, there can only be one IDENTITY property defined on a column in a table. An IDENTITY column has a seed value (where the values begin) and the numeric increment value...