The TABLE custom type / Using a TVP - Table Valued Parameter

Some Code to demonstrate

1. Creating a custom TABLE type
2. Passing a Table as a parameter (Table Valued Parameter)

In reality my 80s themed example would be far more exciting...

SET NOCOUNT ON;
GO

-- Create Target Table
CREATE TABLE EightiesHits
  (Artist VARCHAR(50)
  ,Title VARCHAR(50)
  ,ReleaseYear NUMERIC(4,0))
GO

-- Create Custom Type
CREATE TYPE Release AS TABLE
  (Artist VARCHAR(50)
  ,Title VARCHAR(50)
  ,ReleaseYear NUMERIC(4,0));
GO

-- Create Stored Procedure to demonstrate passing a table valued parameter.
CREATE PROCEDURE dbo.AddToCollection
    @TVPRelease Release READONLY
AS 
  SET NOCOUNT ON
  INSERT INTO EightiesHits
          (Artist,Title,ReleaseYear)
  SELECT Artist,Title,ReleaseYear
  FROM  @TVPRelease;
GO

-- Declare TVP
DECLARE @EightiesTunesTVP AS Release;

-- Put some data in!
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Do They Know It''s Christmas?', 'Band Aid', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('I Just Called to Say I Love You', 'Stevie Wonder', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Relax', 'Frankie Goes to Hollywood', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Two Tribes', 'Frankie Goes to Hollywood', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Careless Whisper', 'George Michael', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Last Christmas / Everything She Wants (Remix)', 'Wham!', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Hello', 'Lionel Richie', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Agadoo', 'Black Lace', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Ghostbusters', 'Ray Parker, Jr', 1984)
INSERT INTO @EightiesTunesTVP(Artist,Title,ReleaseYear)  
  VALUES ('Freedom', 'Wham!', 1984)

-- Now pass te contents of TVP @EightiesTunesTVP 
-- to table EightiesHits via stored procedure AddToCollection
EXEC AddToCollection @EightiesTunesTVP;

-- Now prove the data is there
SELECT * FROM EightiesHits

--Clean up
DROP TABLE EightiesHits;
DROP PROCEDURE AddToCollection;
DROP TYPE Release;
GO

OPTIMIZE FOR UNKNOWN and other query hints

Query Hints go in the OPTION clause at the end of a query.

SELECT <columnlist>
FROM <table>
WHERE <clause>
OPTION(query hint)

The only one I've really had cause to use in the past is MAXDOP 1 to prevent large queries unsuccessfully using multiple processors.

Some new ones (to me) I read about today, are...

• KEEP PLAN - Reduces likelihood of plan recompilation by relaxing the recompile threshold.
• KEEPFIXED PLAN - Tells optimiser not to recompile plan even if statistics have changed.
• ROBUST PLAN - Use plan with maximum possible rowsize.
• OPTIMIZE FOR (parameter value) - use this value as a local variable in query optimization
• OPTIMIZE FOR UNKNOWN (New to SQL 2008)- Use statistical data to determine value for local variable in query optimization

Links :
OPTIMIZE FOR UNKNOWN – a little known SQL Server 2008 feature
How OPTIMIZE FOR UNKNOWN Works
Query Hints

Revision : Transfer a table to another schema

Created a table in the wrong schema?
Easily done. Easy to drop and recreate, but what if you've populated it?

Here's how to transfer a table to a different schema.

ALTER SCHEMA TargetSchema
TRANSFER dbo.myTable

Covered this back in 2007 too - Solace : Moving Tables Between Schemas
and 2009 - Solace : Changing Table Schema

Column Level Encryption Walkthrough

Here is a quick script to demonstrate reversible encryption.

First, some configuration -

1. Setup a master key 
2. Create a Certificate 
3. Create a Symmetric key

CREATE MASTER KEY ENCRYPTION BY 
PASSWORD = 'ins3cur3-p4ss'
GO

CREATE CERTIFICATE Certificate1
   WITH SUBJECT = 'AES Encryption';
GO
 
CREATE SYMMETRIC KEY Key1
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Certificate1;
GO

Next create the test environment by

1. Creating a table 
2. Inserting Data 
3. Showing the results

CREATE TABLE EncryptionTest
 (ID INT IDENTITY(1,1)
 ,email VARCHAR(100) NOT NULL 
 ,passwordplain NVARCHAR(16) NOT NULL
 ,passwordencrypt VARBINARY(256) NULL
 )
GO

INSERT INTO EncryptionTest (email,passwordplain) VALUES ('someone@someplace.com', 'simplepass')
GO

SELECT * FROM EncryptionTest 

ID email passwordplain passwordencrypt 
1 someone@someplace.com simplepass NULL 

Now populate the passwordencrypt function using the EncryptByKey function.

OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE Certificate1;

UPDATE EncryptionTest
SET passwordencrypt = EncryptByKey(Key_GUID('Key1'), passwordplain);

CLOSE SYMMETRIC KEY Key1;

SELECT * FROM EncryptionTest 

ID email passwordplain passwordencrypt 
1 someone@someplace.com simplepass 0x00543AEC5035FA48BE3115FF0E14A3320100000093F2435E1F1DC8ACCC03097E6AD5D1262C4F2FF1ADE341D38207035E1FCD1B2A82123F5DCBDA3414BC3490593924B830 

Finally, prove we can decrypt the password again.

OPEN SYMMETRIC KEY Key1
DECRYPTION BY CERTIFICATE Certificate1;

SELECT ID, email, passwordencrypt, CONVERT(NVARCHAR(16), DecryptByKey(passwordencrypt)) 
AS [DecryptedPassword]
FROM EncryptionTest

CLOSE SYMMETRIC KEY Key1;

ID email passwordencrypt DecryptedPassword 1 someone@someplace.com 0x00543AEC5035FA48BE3115FF0E14A3320100000093F2435E1F1DC8ACCC03097E6AD5D1262C4F2FF1ADE341D38207035E1FCD1B2A82123F5DCBDA3414BC3490593924B830 simplepass

Revision : sp_refreshview

For views that do not use WITH SCHEMABINDING, sp_refreshview should be run when the objects underlying the view are changed.

Usage :

exec sp_refreshview 'dbo.myView'

MSDN : sp_refreshview

Revision : Log file control

Got a Large Log File (LDF) ?

Hopefully your maintenance plans have this all under control but on occasion there might be reason to manually intervene , e,g an unexpected growth in the database or a failure of a FULL backup.

1. Back it up
2. Attempt to shrink it

For example :

USE db;
BACKUP LOG db TO db_log_backup;
DBCC SHRINKFILE (db_log);

Hopefully the system isn't so busy that the log gets written to between the BACKUP and SHRINKFILE commands.

NB: Prior to SQL 2008 you could reduce with the tranaction log with
BACKUP LOG db WITH TRUNCATE_ONLY

Others may advocate changing the recovery model to SIMPLE (the transaction log truncted on checkpoint) but this will break the backup chain and you will have to perform a FULL backup again when you return it to the FULL recovery mode.

DBCC SHRINKFILE Gotchas -

TRUNCATEONLY - Removes free space at end of data file

NOTRUNCATE - Moves data within data file, frees pages at end. Does not shrink data file itself.

NB: TRUNCATEONLY & NOTRUNCATE Only work on data files.

Your success in shrinking log files relies on logs being written to the front portion of the log file immediately after a backup (and the rate at which that occurs).

DBCC SHRINKFILE
Database Checkpoints
What Happens to Your Transaction Log in SIMPLE Recovery Model?

SQL 2012 : SEQUENCE Walkthrough

SQL 2012 introduces a SEQUENCE (apparently Oracle has had this for a while).
You create a SEQUENCE like this -

CREATE SEQUENCE [TestSequence]
AS [INT]
START WITH 1
INCREMENT BY 1
MAXVALUE 3
GO

Retrieving values from the sequence works like this -

SELECT NEXT VALUE FOR [TestSequence]

First Run : 1 
Second Run : 2 
Third Run : 3 

The Forth Run produces the following error, as the sequence had reached it's maximum value of 3 -

Msg 11728, Level 16, State 1, Line 1 The sequence object 'TestSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. 

By default, a sequence does not repeat itself.
You could restart the sequence...

ALTER SEQUENCE [TestSequence] RESTART

Or add the keyword CYCLE to the original statement...

DROP SEQUENCE [TestSequence]
GO

CREATE SEQUENCE [TestSequence]
AS [INT]
START WITH 1
INCREMENT BY 1
MAXVALUE 3
CYCLE
GO

Having done so, we'll run the statement again -

SELECT NEXT VALUE FOR [TestSequence]
GO

First Run : 1 
Second Run : 2 
Third Run : 3 
Forth Run : -2147483648 
Fifth Run : -2147483647 
Sixth Run : -2147483646

So without specifying a minimum value, the SEQUENCE uses the minimum value for the INT data type and continues cycling from there.

Let's try again.

DROP SEQUENCE [TestSequenceCycle]
GO

CREATE SEQUENCE [TestSequenceCycle]
AS [INT]
START WITH 1
INCREMENT BY 1
MAXVALUE 3
MINVALUE 1
CYCLE
GO

SELECT NEXT VALUE FOR [TestSequenceCycle]
GO

First Run : 1 
Second Run : 2 
Third Run : 3 
Forth Run : 1 
Fifth Run : 2 
Sixth Run : 3 

The most obvious use for the sequence is populating several child tables with a key value from a parent. This saves us looking up an inserted IDENTITY value.