February 2009 - Tip of the Week

How to allow multiple NULL values in a UNIQUE column

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...

Published 25 Feb 2009 2:42 PM by BrandonGalderisi

Filed under: NULL, schemabinding, unique, views, constraint

Viewing SQL Server Jobs and History

In SQL Server 2000, it was cumbersome to allow those who were not members of the sysadmin group the ability to view SQL Agent Jobs and job history through Enterprise Manager. SQL Server 2005 has made this a lot easier through the use of specialized database...

Published 25 Feb 2009 2:05 PM by TimChapman

Filed under: jobs, job history, Intermediate SQL

Server Role Members

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...

Published 25 Feb 2009 10:53 AM by TimChapman

Filed under: Intermediate SQL, Server Roles

Database Role Members

Below is a quick and easy way to query SQL Server 2005 system views to determine which users belong to which database roles on your SQL Server databases. SELECT rolename = rolep.name, membername = memp.name FROM sys.database_role_members rm JOIN sys.database_principals...

Published 25 Feb 2009 10:52 AM by TimChapman

Filed under: Intermediate SQL, Beginner SQL, Advanced SQL

Using Table Aliases

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...

Published 25 Feb 2009 10:37 AM by TimChapman

Filed under: Intermediate SQL, Beginner SQL, shortcuts, Advanced SQL

Deleting Duplicate Values in SQL Server 2005

SQL Server 2005 provides a very easy way to remove duplicate values through the use of a Common Table Expression (CTE). The script below creates a temp table named #PendingAccounts. Notice that I will check in the tempdb to see if the table already exists...

Published 23 Feb 2009 9:29 PM by TimChapman

Filed under: duplicates, delete, Beginner SQL

Management Studio Quick Keys

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...

Published 23 Feb 2009 9:10 PM by TimChapman

Filed under: Management Studio, SSMS, shortcuts

How to Automate SQL Server Express Backups

SQL Server Express is great for small business with small data requirements. It is essentially a scaled down version of SQL Server that is free. However, because it is throttled down there are some features that are missing...one such feature is the SQL...

Published 23 Feb 2009 8:09 PM by TimChapman

Filed under: Intermediate SQL, Beginner SQL, Advanced SQL

IDENTITY Columns

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...

Published 23 Feb 2009 7:34 PM by TimChapman

Filed under: Beginner SQL, identity

SELECT...INTO

The SQL Server SELECT statement includes an INTO clause which is used to create a table object based on the resultset of the query. The table created by the INTO clause can be a session-level temporary table, a global temporary table, or a user defined...

Published 23 Feb 2009 2:00 PM by admin

Filed under: SELECT INTO, Advanced SQL