Select Favorite SQL Server 2012 Articles

The following articles are a collection of my favorite SQL Server related posts over the last 6 months.

3 Steps to Work More Efficiently in SSMS. Posted 2/17/2013

Recently the 3 Steps to Work More Efficiently in SSMS article was one of my favorites. The three steps were:

  1. Display results in a separate tab.
  2. Hotkey for SELECT TOP 100 * FROM …
  3. sp_help by using Alt+F1

The Display results in a separate tab is one of the first things I do when working in SSMS on a new computer.

VARCHAR diff function Posted 2/13/2013

What made the VARCHAR diff function posting one of my favorite was how it built on a number of previous articles to finally get to something that was much more useful, all based on common table expressions. The VARCHAR diff function used code from a previous posting called Using a CTE to Split a String Into Rows with Line Numbers. A couple weeks after writing the VARCHAR diff function with CTE’s I ended up using this on a project to create an automated testing script to compare the output from an outdated code project to the results from the new T-SQL implementation.  The use of the VARCHAR diff function allowed for a single tester to test the work of 3 developers in a time far less than what was anticipated on this project.

The VARCHAR diff function probably isn’t something that anyone would use on a daily basis, but it is a massive time saver when needed.

TSQL 2012 – Generating letters with a SEQUENCE object Posted 11/9/2012

This article was a lot of fun. Having learned about how to use a SQL Server 2012 sequence object to generate letters at a SQL Saturday presentation, I just had to blog about this one. It was a fun post.

Adventureworks2012 data to Google GeoChart Visualization Posted 10/19/2012

On this article I pulled address data from the AdventureWorks2012 database, and covered how to extract it and reformat it correctly to display on the Google GeoChart Visualizations.

Visualization1

Again another fun posting.

I hope everyone enjoys these as much as I did.

Using a SEQUENCE object to generate letters.

SQL Server 2012 introduces a new T-SQL object used to generate numbers, a SEQUENCE, similar to an identity column. You can think of a sequence as a take a number object, you just ask it to give you a number, and you get a number.

When creating a SEQUENCE you need to specify the following:

  • Start
  • Increment
  • Min Value, Max Value
  • Cycle / No Cycle – starts of when the Max value is hit or not
  • Cache

The SEQUENCE is all about generating numbers…

The question comes up on using a SEQUENCE to generate letters, to rotate through a SEQUENCE of A to Z, then to start over?

Technically a SEQUENCE can only generate numbers, but here is how you can use it to generate letters.

Here is the sample code to do it.


CREATE SEQUENCE lettersSequence
 AS tinyint
 MINVALUE 65
 MAXVALUE 90
 CYCLE
 INCREMENT BY 1 ;

SELECT char(NEXT VALUE FOR lettersSequence);

SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);
SELECT char(NEXT VALUE FOR lettersSequence);

Enjoy.

SQL Saturday Presentation

I am at SQL Saturday in Vancouver BC today.

Here is the download of my presentation.

Download zip file.

Here is the presentation outline.

  • OVER Clause Enhancements
  • ROWS PRECEDING, FOLLOWING, UNBOUNDED
  • RANGE PRECEDING, FOLLOWING, UNBOUNDED
  • IIF – Immediate IF or Inline IF (from Access)
  • CHOOSE (from Access)
  • OFFSET / FETCH
  • FORMAT
  • CONCAT
  • SEQUENCE (from Oracle)
  • sp_describe_first_result_set
  • New Date and Time Functions
  • Conversion Functions
  • PARSE, TRY_PARSE, TRY_CONVERT
  • THROW exception

Are you wasting half the capacity of IDENTITY?

When creating a table you can specify IDENTITY with the following syntax:


IDENTITY [ (seed , increment) ]

For example
CREATE TABLE [dbo].[TestIntIdentity](
  [cpuID] [int] IDENTITY(1,1) NOT NULL,
  [logTime] [datetime] NOT NULL DEFAULT GetDate()
) ON [PRIMARY];

The IDENTITY property is valid for data types of the integer data type category, except for the bit data type, or decimal data type.  So how many rows can you insert into that table?  It depends on the data type used.

  • BIGINT maximum value is 9,223,372,036,854,775,807
  • INT maximum value is 2,147,483,647
  • SMALLINT maximum value is 32,767
  • TINYINT maximum value is 255

What happens when I run out of numbers?  Let’s take a look at an example using SMALLINT.


-- SMALLINT should accommodate 32767 values
CREATE TABLE [dbo].[TestSmallIntIdentity](
 [cpuID] [smallint] IDENTITY(1,1) NOT NULL,
 [logTime] [datetime] NOT NULL DEFAULT GetDate()
) ON [PRIMARY];

GO

INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT * FROM TestSmallIntIdentity;
SELECT count(*) FROM TestSmallIntIdentity;

GO

At this point we have 1 row in the table.  Now lets fill almost all the way full.


INSERT INTO TestSmallIntIdentity DEFAULT VALUES;
GO 32765

-- Run it 32,765 times

SELECT * FROM TestSmallIntIdentity;
SELECT count(*) FROM TestSmallIntIdentity;

GO

Keep in mind that specifying a number after the GO statement specifies that we want the previous statement run that many times.  In this case run the insert statement 32756 times plus the one previous should leave us with 32766 rows in the table as shown here.

Now lets take a quick look at this table using the Identity Column Usage Report from Database Health Reports project.

Now the big question, there are 32766 items in the table, we know that it will hold 32767, what happens when we do another insert or 2?


INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT count(*) FROM TestSmallIntIdentity;
GO

This one works fine and puts our count at 32767,  what next.  Insert one more row…


INSERT INTO TestSmallIntIdentity DEFAULT VALUES;

SELECT count(*) FROM TestSmallIntIdentity;
GO

This message is SQL Servers way of saying that the identity column is full.

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.

How do I fix it?

This is where it gets ugly.  You can’t change the datatype on a IDENTITY value on an existing table with dropping it and recreating it, which would lead to data loss.

If there are no tables with foreign key relationships, you could just do the following:

  1. Create another table with a larger integer type for the IDENTITY column, in this case BIGINT or INT.
  2. Then select everything out of this table into the new table, excluding the primary key.
  3. Then drop the original table.
  4. Then rename the new table to the original name.
The more complex solution is if there are foreign keys, you need to also drop the foreign key relationship,  turn on INDENTITY_INSERT do the insert, and turn off IDENTITY INSERT, then re-enable the foreign keys.  Here are the steps.
  1. Create another table with a larger integer type for the IDENTITY column, in this case BIGINT or INT.
  2. Disable all the foreign keys associated with the original table.
  3. Turn on identity_insert for the new table.
  4. Then select everything out of the original table into the new table, including the identity column.
  5. Then drop the original table.
  6. Then rename the new table to the original name.
  7. Turn off IDENTITY_INSERT for the new table.
  8. Re-enable all the foreign keys.
Oh, and did I mention do this at a time when nobody is inserting anything new into the original table.

How to prevent it?

2 options when you originally create a table.

  • Use a larger integer data type.
  • Seed the identity at the smallest value available for that data type.
  • On SQL Server 2012 you could consider the SEQUENCE object instead of an IDENTITY.

The first option of a larger integer data type is more expensive on the database, for instance if you just decided to set every identity to be a BIGINT, you would have a huge amount of overhead and waste on many of your tables.

The second option of seeding the identity to the smallest value available for the sequence.

  • BIGINT minimum value is -9,223,372,036,854,775,808
  • INT minimum value is -2,147,483,648
  • SMALLINT minimum value is -32,768
  • TINYINT minimum value is 0

There really isn’t anything to be gained with the TINYINT since its minimum value is 0, but with the 3 other INT data types, you double the capacity of your identity values by starting them at their minimum value instead of at 1.

Take a look at the following example, seeding the value with -32768, instead of 1.


-- SMALLINT should accommodate -32768 to 32767 values
CREATE TABLE [dbo].[TestSmallIntIdentity2](
[cpuID] [smallint] IDENTITY(-32768,1) NOT NULL,
[logTime] [datetime] NOT NULL DEFAULT GetDate()
) ON [PRIMARY];

GO

INSERT INTO TestSmallIntIdentity2 DEFAULT VALUES;

SELECT * FROM TestSmallIntIdentity2;
SELECT count(*) FROM TestSmallIntIdentity2;

GO

After inserting 1 row, we see the following output.

Where you can see that the first value inserted has an identity value of -32768.  Now what happens when we insert 37268 values.

INSERT INTO TestSmallIntIdentity2 DEFAULT VALUES;</pre>
GO 32768

-- Run it 32,768 times
-- take a look at the last to inserted.
SELECT TOP 10 *
FROM TestSmallIntIdentity2
ORDER BY cpuID DESC;

SELECT count(*) FROM TestSmallIntIdentity2;

Which produces the following output.

Where you can see that after inserting 32769 rows were are now at an IDENTITY value of 0, rather than overflowing.

Now lets take a look at the same table using the Identity Column Usage Report from Database Health Reports project.

Summary

From what we have observed, I ponder the question of why would you ever not start an identity at its minimum value in order to take full advantage the capacity of the datatype you are using.

-Steve Stedman

Bellingham WA

SEQUENCE Sample Code from SQL Saturday in Olympia WA

The sample code below is a quick run down of the new sequence object introduced in SQL Server 2012.


USE [tsql2012];

-- SEQUENCE

-- using default where does this one start?
--DROP SEQUENCE myTestSequence;
CREATE SEQUENCE myTestSequence;
select NEXT VALUE FOR myTestSequence;

-- using int... where does this one start?
--DROP SEQUENCE myTestSequence2;
CREATE SEQUENCE myTestSequence2
 AS INT;
select NEXT VALUE FOR myTestSequence2;

-- using smallint
--DROP SEQUENCE myTestSequence3;
CREATE SEQUENCE myTestSequence3
 AS SMALLINT;
select NEXT VALUE FOR myTestSequence3;

-- using tinyint... where does this one start?
--DROP SEQUENCE myTestSequence4;
CREATE SEQUENCE myTestSequence4
 AS TINYINT;
select NEXT VALUE FOR myTestSequence4;

-- start it at 0
-- DROP SEQUENCE myTestSequence5;
CREATE SEQUENCE myTestSequence5
 START WITH 0;
select NEXT VALUE FOR myTestSequence5;
DROP SEQUENCE ordersKeySequence;

CREATE SEQUENCE ordersKeySequence
 AS bigint
 START WITH 100
 INCREMENT BY 1 ;
-- Create a table
--DROP TABLE Orders;
CREATE TABLE Orders
 (OrderID int PRIMARY KEY,
 Name varchar(20) NOT NULL,
 Qty int NOT NULL);
GO
-- Insert three records
INSERT Orders (OrderID, Name, Qty)
 VALUES (NEXT VALUE FOR ordersKeySequence, 'Hat', 2) ;
INSERT Orders (OrderID, Name, Qty)
 VALUES (NEXT VALUE FOR ordersKeySequence, 'Shirt', 1) ;
INSERT Orders (OrderID, Name, Qty)
 VALUES (NEXT VALUE FOR ordersKeySequence, 'Shoes', 1) ;
GO

-- View the table
SELECT * FROM Orders ;
GO
-- Another way to do it.
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR ordersKeySequence;
INSERT Orders (OrderID, Name, Qty)
 VALUES (@NextID, 'Belt', 2) ;
GO
SELECT * FROM Orders ;
GO
-- the easy way

DROP TABLE Orders;
CREATE TABLE Orders
 (OrderID int PRIMARY KEY DEFAULT NEXT VALUE FOR ordersKeySequence,
 Name varchar(20) NOT NULL,
 Qty int NOT NULL);
GO
-- Insert three records
INSERT Orders (Name, Qty) VALUES ('Hat', 2) ;
INSERT Orders (Name, Qty) VALUES ('Shirt', 1) ;
INSERT Orders (Name, Qty) VALUES ('Shoes', 1) ;
GO

-- View the table
SELECT * FROM Orders ;
GO

-- TRANSACTIONS
-- DROP SEQUENCE myTestSequence6;
CREATE SEQUENCE myTestSequence6
 START WITH 0;
select NEXT VALUE FOR myTestSequence6;

BEGIN TRANSACTION;
select NEXT VALUE FOR myTestSequence6;
COMMIT TRANSACTION;

select NEXT VALUE FOR myTestSequence6;

-- now what happens if we roll it back??
select NEXT VALUE FOR myTestSequence6;

BEGIN TRANSACTION;
select NEXT VALUE FOR myTestSequence6;
ROLLBACK TRANSACTION;

select NEXT VALUE FOR myTestSequence6;

USE tsql2012;

CREATE SEQUENCE withOverSequence
 AS bigint
 START WITH 100
 INCREMENT BY 1 ;
-- SEQUENCEs with the OVER clause
SELECT NEXT VALUE FOR withOverSequence OVER(ORDER BY year),
 *
FROM REVENUE
ORDER BY departmentID, year;



-- RESETTING A SEQUENCE
SELECT NEXT VALUE FOR withOverSequence ;

ALTER SEQUENCE withOverSequence
RESTART WITH 1 ;

SELECT NEXT VALUE FOR withOverSequence ;

USE [tsql2012];

-- SEQUENCE
-- generating letters with a sequence.
DROP SEQUENCE lettersSequence;
CREATE SEQUENCE lettersSequence
 AS tinyint
 --START WITH 65
 MINVALUE 65
 MAXVALUE 90
 CYCLE
 INCREMENT BY 1 ;

SELECT NEXT VALUE FOR lettersSequence;
--DROP SEQUENCE numberSequence;
CREATE SEQUENCE numbersSequence
 AS INT
 START WITH 0;
select NEXT VALUE FOR numbersSequence;

DROP TABLE Students;
CREATE TABLE Students
 (UserID varchar(30) PRIMARY KEY DEFAULT char(NEXT VALUE FOR lettersSequence)
                      + CAST(NEXT VALUE FOR numbersSequence as VARCHAR),
 Name varchar(20) NOT NULL);
GO
-- Insert three records
set nocount on;
INSERT Students (Name) VALUES ('Some Name');
GO 1000
SELECT *
 FROM STUDENTS
 ORDER BY UserID;

Just part of my Whats New in TSQL 2012 presentation at SQL Saturday 166.

TSQL 2012 – Generating letters with a SEQUENCE object

As shown in my previous posting on SEQUENCES, they are a user-defined object that generates a sequence of numeric values in Transact SQL 2012.  But what if you want to generate letters instead of numbers.

Here is a code example of a sequence used to generate letters (indirectly).


-- SEQUENCE
-- generating letters with a sequence.
--DROP SEQUENCE lettersSequence;
CREATE SEQUENCE lettersSequence
 AS tinyint
MINVALUE 65
 MAXVALUE 90
 CYCLE
 INCREMENT BY 1 ;

SELECT char(NEXT VALUE FOR lettersSequence) ;
SELECT char(NEXT VALUE FOR lettersSequence) ;
SELECT char(NEXT VALUE FOR lettersSequence) ;
SELECT char(NEXT VALUE FOR lettersSequence) ;
SELECT char(NEXT VALUE FOR lettersSequence) ;

Which generates the following output.

Just another cool trick with new features in sql server 2012.

 

For more info on sequences, take a look at my presentation for SQL Saturday 166 in Olympia WA.