DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them, but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.

Overview of SQL Server Management Studio 2012

Here's a 10-minute crash course in SQL Server Management Studio 2012. In addition to showing you the new undockable panes, I get into some of the tried and true tricks...
Topics: 
Media Format: 

Determine if a Column has an IDENTITY property

If you need to get a list of tables and the IDENTITY field in each one, use the following query.
SELECT OBJECT_NAME(id) as TableName, name as ColumnName
FROM syscolumns
WHERE status = 0x80
An alternative way to find the IDENTITY property is to use the COLUMNPROPERTY.
Topics: 
Media Format: 

Determine if a Table has an IDENTITY property

To get a list of tables with a column indicating if the table has an identity field, use the following query.

select name, OBJECTPROPERTY(id, 'TableHasIdentity') AS TableHasIdentity
from sysobjects
where xtype = 'U'
Tags: 
Topics: 
Media Format: 

Use Information Schema Columns view to quickly view a table definition

 

I’ll highlight the .COLUMNS view by demonstrating some of the useful information contained therein.  Here’s an example query which displays and parses information from this view in a helpful way.  Obviously you can simply use SELECT * FROM INFORMATION_SCHEMA.COLUMNS to see all the columns that are available.

 

Topics: 
Media Format: 

Information Schema System View Overview

 

The INFORMATION_SCHEMA views make information available that is buried in system tables or is internal to the system and not otherwise queryable.  The image below shows a listing of the view that are available.

Topics: 
Media Format: 

Coalesce Basic Usage

The COALESCE function is very useful in selecting the first non-null in a series of arguments. A typical example might be to display a custom message in case a field is null.

COALESCE(MyManager,'I''M TOP DOG')
Note that I said a series of arguments. Unlike ISNULL() you can put as many items in the list and SQL will output the first non-NULL item on the list.
Tags: 
Topics: 
Media Format: 

Table Variables How and Why to use them

So what are table variables and why should I use them? There’s plenty of information out there about the table variables particularly as compared to a temporary table so let me just summarize some of the differences and point out a few use cases that are ideal for developers. First the syntax is identical except that temporary tables are CREATED with a # while table variables are DECLARED with a @.
CREATE TABLE #TestTable (
      ID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      SomeValue varchar(30) NOT NULL
)
 
DECLARE @TestTable TABLE (
Topics: 
Media Format: 

Drag and Drop Column List into query window

 

This next tip works as long you have SSMS 2008 against any instance.  You can drag the table name, column name or even the column folder to write a query.  So if I want to write a select against a table and especially when I don’t have intellisense, I can just drag and drop.

 

If I drag the Columns folders I get the a comma-separated list of the columns.  You can also drag several other items such as individual table and column names.

Topics: 
Media Format: 

Mouse over fields to see their definition

If there’s a tool I could not work without, it’s SQL Server Management Studio or SSMS for short. The tool just keeps getting better and I keep learning more about it every week. Today I pass along a simple yet otherwise unknown tip from a developer.

Topics: 
Media Format: 

Using Table Aliases to Future Proof your Code

When you’re dealing with a single table a table aliases is not necessary, but when you join to another table you can run into trouble. Consider the following scenario. Your stored procedure returns a list of users and passwords as illustrated in the following SELECT statement.
SELECT
      [Server],
      [Username],
      [Password],
      [DateAdded],
      [Notes]
FROM dba.Passwords p
JOIN dba.Users u
ON p.UserID = u.UserID
Tags: 
Topics: 
Media Format: 

Pages

Subscribe to SQL Tidbits RSS