SQL : Data Search Script - Version 5

version 5 :        1) copes with schemas other than dbo!
                        2) restrict to tables only (was doing views!)
                        3) nolock table hint added
                        4) uniqueidentifier added to data types searched
                        5) relays progress to window instantly using raiserror
(http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)

/*
DATA SEARCH SCRIPT

version 5 :  
   1) copes with schemas other than dbo!
   2) restrict to tables only (was doing views!)
   3) nolock table hint added
   4) uniqueidentifier added to data types searched
   5) relays progress to window instantly using raiserror (http://sqlsolace.blogspot.co.uk/2008/09/sql-messages-window-get-messages.html)

YOU MUST RUN THIS IN GRID VIEW!!! 
*/

declare @columncount int
declare @searchdata varchar(2550)
-- set string to search for here
set @searchdata = 'doering'

select @columncount = count(*) from information_schema.columns
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock)  where type = 'u')
select 'declare @resultslist varchar(max)' + char(10) + 'set @resultslist = '''''
union
select 'if exists (select 1 from [' + 
  table_schema + '].[' + table_name + 
  '] with (nolock) where [' + 
  column_name  + '] like ''%' + @searchdata + '%'' ) ' + char(10) +
  ' begin ' + char(10) +
  '  set @resultslist = @resultslist + char(10) + ''select [' + column_name + '],* from ['+ table_schema + '].[' + table_name + '] with (nolock) where [' + column_name + '] like ''''%' + @searchdata + '%'''''''+ char(10) +
  '  raiserror( ''' + table_schema + '.' + table_name  + '/' +column_name  + ''', 10, 1) WITH NOWAIT' + char(10) + 
  ' end ' + char(10) + 
  '' + char(10) +
  ' raiserror( ''' + cast(cast(cast(rank() OVER (ORDER BY table_schema, table_name, column_name) as float)/ @columncount *100 as decimal(10,2)) as varchar(10))+ '%%' +
  ''', 10, 1) WITH NOWAIT' + char(10)
 from  information_schema.columns with (nolock)
 where data_type in ('ntext','char', 'varchar', 'nvchar', 'nvarchar')
 and table_name not like 'sync%'
 and column_name <> 'order' 
 and table_name in (select name from sys.sysobjects with (nolock) where type = 'u')
union
select 'print @resultslist' + char(10) 

SQL 2012 - sp_dboption is no more....

Ultra confident a set of trusted scripts would work on SQL 2012, I came across this today, sp_dboption has gone from SQL 2012.

The solution is to use the relevent ALTER DATABASE syntax.

Old : EXEC sp_dboption 'Adventureworks', 'single user', 'TRUE';

New : ALTER DATABASE Adventureworks SET SINGLE_USER

Old : EXEC sp_dboption 'Adventureworks', 'single user', 'FALSE';

New : ALTER DATABASE Adventureworks SET MULTI_USER

This post from Jacob Sebastian details the rest ...
Replacement for system stored procedure sp_dboption in SQL Server 2012 (Denali)

Link : Move Database Data Without Taking the Database Offline

This article shows how you can move database data to a new .MDF data file via the ALTER DATABASE and DBCC SHRINKFILE commands.

Pros

1. Can be done online 

Cons

1. Speed 
2. Cannot move system objects 
3. Index Fragmentation may be caused

Link :32Bit SQL on a 64 Bit Box

I've seen this on a number of boxes now.
32bit SQL in a 64bit environment.

{screams silently}

Thomas LaRock has a script to test for this -

HOW TO: Determine If You Are Running A 32-bit Version Of SQL Server On A 64-bit O/S

MCSE : Data Platform SQL 2012

Over the past 3 weeks I have taken (and passed) the following three SQL Server 2012 upgrade exams -

• 70-457 : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 
• 70-458 : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 
• 70-459 : Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform

The first 2 exams made me an MCSA, completing the third made me an MCSE for Data platform. A little confusing as I was already an MCSE some years ago. To clarify...

The old world MCSE (I did mine on Windows 2000) stood for Microsoft Certified Systems Engineer.
The new MCSE is an acronym for Microsoft Certified Solutions Expert.

All the new professional exams are MCSEs with the exact titles differing as per the product specialism.

Link : New MCSE Certifications

Link : Relational Databases Aren't Dead.

I like this link.

I like it because academic developers whom are keen on CV polishing are convinced the RDBMS is old news. If it's not an ORM model or even better a NoSQL implementation like Cassandra or MongoDB then it's old hat.

Yes, they can scale immensely which is fantastic but they are not suitable everywhere, Those implementations don't lend themselves to structure, to referential integrity and the beauty of having one master version of a data set.

I'll let the title speak for itself ...

Relational Databases Aren't Dead. Heck, They're Not Even Sleeping

Formatting FAT32 Volumes larger than 32 GB

For an exercise in (ahem) modding a games console, I needed to format an external USB drive to FAT32.
On doing so, I found I couldn't do so...

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.


C:\Users\RMD>format g: /q /fs:fat32

The type of the file system is NTFS.
The new file system is FAT32.

WARNING, ALL DATA ON NON-REMOVABLE DISK
DRIVE G: WILL BE LOST!
Proceed with Format (Y/N)? y
QuickFormatting 238472M
The volume is too big for FAT32.

The solution I used was to find a tool that would perform the format.
Fat32Format by Ridgecrop Consultants is as good as any, i.e. it is fast and free.

C:\Users\RMD>fat32format g:

Warning ALL data on drive 'g' will be lost irretrievably, are you sure
(y/n) :y
Size : 250GB 488392002 sectors
512 Bytes Per Sector, Cluster size 32768 bytes
Volume ID is 1ee6:304a
32 Reserved Sectors, 59604 Sectors per FAT, 2 fats
7629261 Total clusters
7629260 Free Clusters
Formatting drive g:...
Clearing out 119304 sectors for Reserved sectors, fats and root cluster...
Wrote 61083648 bytes in 2.33 seconds, 25.05 Megabytes/sec
Initialising reserved sectors and FATs...
Done

C:\Users\RMD>

SQL 2012 on Windows 2012 : Install Stuck

My install of SQL Server 2012 on Windows 2012 hung today on

'Enabling OS feature 'NetFx3'

A little searching drove me to this blog post - Error while enabling Windows feature NetFx3

The instructions above show how to resolve it, but you need to add ".NET Framework 3.5 Features" as a feature via the Add Roles and Features Wizard.

Why this couldnt have been detected in the prerequisites part of the install check, I don't know....

SQL 2008 - Debugging failed backups

Here are some steps I followed today on SQL 2008 concerning some failed backups (not my server)...

Maintenance plan history

Viewing the history of the Maintenance plan shows 2 things

1. The Success/Failure of the plan
2. Which Task it has failed on

Detail concerning a failure is buried in a single line error message for each Maintenance Plan task.

Maintenance plan errors tend to obfuscate the true cause as a Maintenance Plan is an Integration Services wrapper around database management tasks.

 " Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End ...  The package execution fa...  The step failed. "

A SSIS developer would understand these errors, a DBA maybe not so.

If a task was backing up mulitple databases and the final one failed, there is a lot of text to wade through. Creating log files on our Maintenance plans is the way to go to log at this level.


SQL Server Agent History

Again the agent history hides the details we are after.
When a maintenance plan is scheduled, an agent job is created to run it.
The agent job has a parent entry with step ID 0 which reveals the job outcome.
The maintenance plan is implemented as a single step (step ID 1).
Even if a small part of the plan fails, the whole agent step (and parent job) are seen to fail.
The message output here is displayed over several lines and is more readable. It still lacks the detail we want however.


SQL Server Logs

These can be accessed via management studio or the event viewer.
Focusing here on one database, we can locate the reported cause much easier.

SQL Server Logs show 3 separate entries for each backup failure.

Date                       20/03/2013 9:17:03 AM
Log                          SQL Server (Current - 20/03/2013 9:42:00 AM)

Source                    Backup

Message
BACKUP failed to complete the command BACKUP DATABASE Obfuscated. Check the backup application log for detailed messages.

Generic Backup Failure Message, followed by the detail below

Date                      20/03/2013 9:17:03 AM
Log                         SQL Server (Current - 20/03/2013 9:42:00 AM)

Source                  Backup

Message
Error: 3041, Severity: 16, State: 1.

Yes, the backup has failed, we could search for Severity 16, State 1 if we wished.
The next step tells us more however.

Date                      20/03/2013 9:17:03 AM
Log                       SQL Server (Current - 20/03/2013 9:42:00 AM)

Source                  spid76

Message
BackupDiskFile::CreateMedia: Backup device 'R:\BACKUP\FULL\Obfuscated\Obfuscated_backup_2013_03_18_225452_3071857.bak' failed to create. Operating system error 5(failed to retrieve text for this error. Reason: 15105).

So we need to look up OS Error 5

From a command prompt, lookup the OS error message like this,

NET HELPMSG 5

" Access is denied " is fairly blatantly a security thing that I resolved by giving a more generous range set of permissions to the service account responsible for performing the backups.

I never did get to the bottom of how the permissions changed in the first place though. I suspect a change in group membership for the service account but I'll never be able to prove it.

TSQL : Find the largest table

A script to find the largest table in the current database.

SELECT  t.name, s.row_count
FROM sys.dm_db_partition_stats s
INNER JOIN sys.tables t
ON t.object_id = s.object_id
WHERE s.index_id < 2
ORDER BY s.row_count DESC

This works for SQL 2005 up ...

To do this in SQL 2000, you would have to use sp_spaceused and loop through all tables.

SQL Server 2012 : Offset / Fetch

SQL Server 2012 launches Offset/Fetch which provide server side paging of results sets.

A Simplified example is this -

SELECT
  [columnlist]
FROM
  [table]
ORDER BY [column]
OFFSET [offset] ROWS
FETCH NEXT [rowcount] ROWS ONLY;

The new options are market in red above, and are -

• [offset] - Position to start retrieving rows from.
• [rowcount] - How many rows to retrieve

You've been able to achieve this in the past with a little ingenuity as the table below shows.

SQL Server	Method	Links
2012	OFFSET & FETCH	New T-SQL features in SQL Server 2012 – OFFSET and FETCH OFFSET – FETCH in SQL Server 2012
2005	ROW_NUMBER()	T-SQL: Paging with ROW_NUMBER()
2000	SET ROWCOUNT Trick	A More Efficient Method for Paging Through Large Result Sets

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.

ROWVERSION Walkthrough

ROWVERSION is used for versioning rows (did the name not give it away?)

Equivalent to VARBINARY(8), here is a demonstration of how it works (using the bestselling albums of 1983!)
First we create a table with a ROWVERSION column and populate it.

CREATE TABLE TopAlbums
( ID INT IDENTITY(1,1)
 ,Artist NVARCHAR(50)
 ,Title NVARCHAR(50)
 ,Version ROWVERSION)

GO

INSERT INTO TopAlbums (Artist,Title) VALUES ('Michael Jackson','Thriller')
INSERT INTO TopAlbums (Artist,Title) VALUES ('David Bowie','Let''s Dance')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Culture Club','Colour By Numbers')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Paul Young','No Parlez')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Spandau Ballet','1')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Wham!','Fantastic')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Men At Work','Business As Usual')
INSERT INTO TopAlbums (Artist,Title) VALUES ('The Police','Synchronicity')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Genesis','Genesis')
INSERT INTO TopAlbums (Artist,Title) VALUES ('Eurythmics','Sweet Dreams')

GO

Here we can see the value of the Version column that ROWVERSION has created.

SELECT * FROM TopAlbums WHERE ID = 10

10 Eurythmics Sweet Dreams 0x00000000000007F8 

Now we give album 10, it's full title -

UPDATE TopAlbums  
SET Title = Title + ' (Are Made Of This)'
WHERE ID = 10

and we can see that the amendment has updated the Version column.

SELECT * FROM TopAlbums WHERE ID = 10

10 Eurythmics Sweet Dreams (Are Made Of This) 0x00000000000007F9 

Practical uses of this are data warehousing systems, i.e. only reloading data that has changed.

Policy Based Management Basics

Introduced in SQL 2008 , PBM allows you to standardise your SQL servers.

PBM is found under Management \ Policy Management in SQL Management Studio,
PBM relies on Facets (which in turn have properties), Conditions & Policies.

FacetsThese are the area of SQL we wish to control; e.g. 'Log File'
(There are lots of these, even more in SQL 2012).

PropertiesThe 'Log File' Facet has 15 properties, 'Growth' being a potentially important one.

ConditionsThe condition compares the Facet and it's property. For example we might want Log file Growth to always be set to 10%.

PoliciesThe policy itself defines the target servers and the evaluation modes.

Evaluation Modes are

• On Demand
• On Schedule
• On Change Prevent
• On Change Log Only

So to sum up, to implement a policy -

1. Create condition based on a facet

2. Create a policy

1. Based on the named condition
2. Set the evaluation mode

3. Enable the Policy


Simple Talk : Introduction to Policy Based Management

Revision : I/O Affinity Mask

I/O Affinity is a server option typically for datawarehouse implementations.
It helps when a system is reading & writing large amounts of data.

Solace : Affinity & Affinity I/O

Minimal Configuration & Single User Modes

In the event of a server failure, you might need to start SQL Server in either the Minimal Configuration or Single User Mode.

They both

• Only allow 1 user to connect
• Disable the CHECKPOINT process

Minimal Configuration Mode also prevents startup procedures from being run however.

Links:

Starting SQL Server in Single-User Mode (Startup option -m)
Starting SQL Server with Minimal Configuration (Startup option -f)

Revision : Backups

Recovery Models

FULL Recovery Models

• Supports FULL , DIFF & LOG backups
• The only mode that supports PAGE restores - Page Restore requires FULL recovery model

SIMPLE Recovery Models

• Supports FULL & DIFF backups

Backup Options

COPY_ONLY Use this to take a copy of a production system for a development environment.
It won't mess up the backup chain.

CONTINUE_AFTER_ERROR - Does what it says!

BACKUP DATABASE database_name TO DISK='c:\database_name.BAK'
WITH CHECKSUM, CONTINUE_AFTER_ERROR;

It can also be used on a restore (last resort)

RESTORE DATABASE database_name FROM backup_device
WITH CONTINUE_AFTER_ERROR;

SQL 2012 : Enabling Filestream

Filestream launched in SQL 2012 and introduces the concept of a FileTable.
This is a directory structure (folder) viewable from SQL.

Here's a quick walkthrough to see Filestream in action.

1) Enable Filestream at the INSTANCE Level by -

Launching SQL Server configuration manager
Navigate to the properies of the instance service and enable via the Filestream tab
Chose the appropriate options -

• Enable FILESTREAM for Transact-SQL access
• Enable FILESTREAM for file I/O streaming access (Also provide a share name)
• Allow remote clients to have streaming access to FILESTREAM data.

Launch a query window in Management Studio and run

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

2) Add a Filestream filegroup at the database level 

ALTER DATABASE SandPit
ADD FILEGROUP fsTestFileGroup
CONTAINS FILESTREAM;
GO

3) Add a file to the filegroup 

ALTER DATABASE SandPit
ADD FILE
(
NAME='fsTestFile',
FILENAME='C:\fsTestFile'
)
TO FILEGROUP fsTestFileGroup;
GO

4) Enable Filestream on the database

ALTER DATABASE SandPit
SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME ='fsTestFile')

5) Create a table 

CREATE TABLE fsTestTable AS FILETABLE
WITH
(
FILETABLE_DIRECTORY ='fsTestFile',
FILETABLE_COLLATE_FILENAME = database_default
);
GO

6) Test !!! 

SELECT * FROM fsTestTable;

Creating Views - WITH CHECK OPTION

Something I hadn't appreciated today, the WITH CHECK OPTION when creating views.

The WITH CHECK OPTION is implemented at the end of a view definition.

CREATE VIEW redCars AS
    (
        SELECT type, model, registration, colour
    FROM Vehicles
    WHERE type = 'Car'
    AND Colour = 'Red'
    )
    WITH CHECK OPTION

It ensures changes that would alter the returned rows cannot occur.

UPDATE redCars SET Registration = 'ABC123' will therefore succeed.
UPDATE redcars SET Colour = 'Blue' will not

Links :
DevX.com - The WITH CHECK OPTION option with views
Tony Rogerson - WITH CHECK on CREATE VIEW

Revision :Schemabinding

Schemabinding can be specified on VIEWS and UDFs (user defined functions).

For example ;

 
CREATE VIEW [dbo].[vw_myView] WITH SCHEMABINDING AS
SELECT columnlist
FROM dbo.table
GO

Creating an object with SCHEMABINDING prevents the underlying structure of the tables being reference from being changed.

It's advantage is the increased performance of objects that use it.

It's disadvantage is maintainability in that it complicates scripting when underlying objects need to change i.e. the schema bound ones must first be removed.

It is necessary in order to create INDEXED VIEWS in editions of SQL Server that support it.

Solace : Schemabinding & UDFs

TSQL : Processes being blocked

How to determine the Processes being blocked via TSQL

Old way : exec sp_who 80
New way (sql 2005+): SELECT * FROM sys.dm_exec_requests WHERE Session_id = 80

With sp_who the column blk holds the session id of the blocking process.

On the sys.dm_exec_requests view there is the blocking_session_id column.


To stop a blocking process, use KILL spid e.g. KILL 71

Other uses of sys.dm_exec_requests -

Solace : Long Running Queries
Solace : % Percentage complete of running requests

MSDN : sys.dm_exec_requests
MSDN : sp_who

Table hint basics

SELECT columnlist FROM myTable WITH (TABLOCK);

Generally avoid table hints and let SQL Server manage locking.
Exact behaviour depends on the isolation level.

• TABLOCK - Shared table lock
• TABLOCKX - Exclusive table lock
• NOLOCK - Like the READUNCOMMITTED isolation level.
• UPDLOCK - Use update locks
• XLOCK - Take exclusive locks

Full list at MSDN

Trace Flag 1222 - DBCC TRACEON (1222, -1)

Trace Flag 1222 allows you to diagnose deadlocks.

DBCC TRACEON 1222 - (from MSDN)

"Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema "

Setting as a server startup option is done via the SQL Server Configuration Manager.
In SQL 2008 they are done from the Advanced tab of the Service properties as shown here.
From SQL 2012 they have their own tab ...

Turning it on & off via TSQL is as easy as these commands -

At session level - 

• DBCC TRACEON (1222,)
• DBCC TRACEOFF (1222)
  
  At server level - 

• DBCC TRACEON (1222, -1)
• DBCC TRACEOFF (1222, -1)

My development background means I like the idea of placing commands in a stored procedure that is executed on startup. Much nicer and more maintainable (for me) than messing service properties.
@MsSqlGirl Julie Koesmarno has done exactly that, here.

You can check which flags are enabled by using DBCC TRACESTATUS .

Links :

Finding SQL Server Deadlocks Using Trace Flag 1222
MSDN : trace Flags
Technet : DBCC TraceOn
Turn On Deadlock Trace Flag
How do I work with Trace Flags
SQL Server 2012 Startup Parameters

Enabling the Dedicated Admin Connection (DAC)

The Dedicated Admin Connection (DAC) or Remote Admin Connection allows one administrator connection to a server. A good thing to have enabled for an emergency.

To enable the DAC ;

1. Open port 1434 on Windows firewall / ISA / Any hardware firewalls present.
   
2. sp_configure 'remote admin connections', 1;
   GO
   
3. RECONFIGURE;
   GO

Links :

Enabling DAC via TSQL 
The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It
MSDN : remote admin connections Server Configuration Option

Database Mirroring Basics

Prerequisite : Source database must be in FULL RECOVERY model

Initialization Steps

1. Perform FULL backup of source database
2. Perform LOG backup of source database
3. Restore FULL backup with NORECOVERY on target server
4. Restore LOG backup with NORECOVERY on target server

Configure Mirroring

1. Right-Click Database, select Mirroring page.
2. Use Wizard to configure Principal & Mirror Servers
   * You may need to open port 5022 on the firewall (or whichever alternative you choose).
3. Choose between High Performance (asyncronous) or High Saftey (syncronous) modes
4. Start Mirroring

Adding a witness

1. Create an endpoint on the new server
2. Ensure the same Windows logins exist on each server & grant connect permissions
3. On the Principle Server, alter the pricinipal database to use the endpoint on the new server as a witness.

NB ;

• High Safety (syncronous) could slow a system if bandwidth is limited as commits need to occur on both servers.
• High Performance (asyncronous) allows the changes to be commited at the principle first.

Links :
Configure SQL Server Database Mirroring Using SSMS
Database Mirroring Tips

Debugging connectivity to a named instance

Start > Run > services.msc

Find the services "SQL Server Browser"

1. Ensure it is running
2. Ensure it starts automatically
3. Ensure the Firewall allows UDP 1434 through

Find the service "SQL Server (instance name)"

1. Ensure it is running
2. Ensure it starts automatically

SSIS - SQL Server Agent Permissions

Creating SSIS Jobs that can access network resources

1. Create domain user
2. Grant access to network share
3. Create Credential that references the domian user
4. Add Proxy that references credential
5. Assign the proxy to ssis execution system

sqlsolace : SSIS Credential & Proxy

SQL 2012 - Compatibility Level 110

In SQL Server 2012, the default compatibility level is 110
For a database to include 2012 functionality it needs to be on a SQL 2012 Server in a 2012 Compatibility Level of 110.

ALTER DATABASE MigratedDb
SET COMPATIBILITY_LEVEL = 110;
GO

Compatibility Levels

• 70 - SQL Server 7
• 80 - SQL Server 2000
• 90 - SQL Server 2005
• 100 - SQL Server 2008 / 2008 R2
• 110 - SQL Server 2012

MsDN Compatibility Modes

SQL Server : FOR XML Basics

FOR XML clause supports 4 modes

• RAW 
• AUTO 
• PATH 
• EXPLICIT 

RAW produces 1 line per record.

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML RAW;

AUTO on it's own lists columns as attributes

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO;

AUTO, ELEMENTS makes each column an element.

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML AUTO, ELEMENTS;
 

 

  1
  Ken
  J
  Sánchez


  2
  Terri
  Lee
  Duffy


  3
  Roberto
  Tamburello

XML PATH

SELECT TOP 3 BusinessEntityID, FirstName, MiddleName, LastName
FROM Person.Person
ORDER BY BusinessEntityID
FOR XML PATH('Donkey');

 

  1
  Ken
  J
  Sánchez


  2
  Terri
  Lee
  Duffy


  3
  Roberto
  Tamburello

For XML EXPLICIT to work, the first 2 columns must be Tag and Parent.
Need to UNION ALL a header row to the data.

 
SELECT    1 AS Tag
         ,NULL AS Parent
         ,ProductCategoryID as [Category!1!ID]
         ,Name AS [Category!1!Name]
         ,NULL AS [Subcategory!2!Name] -- Placeholder for Subcategory.Name
FROM Production.ProductCategory 
 
UNION ALL 
 
SELECT   2 AS Tag
        ,1 AS Parent
        ,s.ProductSubcategoryID
        ,c.Name -- (1) Placeholder for Category.Name.  (2) Category.Name is needed for ORDER BY 
                ,s.Name 
FROM Production.ProductSubcategory s,
Production.ProductCategory c
WHERE c.ProductCategoryID = s.ProductCategoryID 
ORDER BY [Category!1!Name], [Subcategory!2!Name] 
FOR XML EXPLICIT 

Links 
Simple Talk : Using the FOR XML Clause

Malware Terminology

Following a 'scare' on a relatives laptop, I found this article which serves to explain security threats simply.

Malware and Viruses - What is the Difference

Terms explained :

• Virus
• Trojan
• Worm
• Key loggers
• Dialers
• Backdoor
• Exploit
• Spyware
• Adware
• Rootkit
• Rogues/Scareware
• Ransomware

Numeric Data type for 1 decimal point.

I wanted to store a number with 1 decimal point and found myself looking how to do it today.

DECIMAL allows you to specify first the number of digits in total (precision) and secondly how many will be after the decimal point.

DECIMAL(2,1) is what I need therefore, to store a single digit before & after the point
e.g. 1.0, 2.0, 2.1, 2.2 etc.

NB : DECIMAL and NUMERIC are interchangable in SQL Server, the latter being a hangover from Sybase days.

Links :
Number Data Types in T-SQL

SQL Server 2012 : New Date & Time Functions

SQL Server 2012 introduces several new Date & Time Functions. To create a SQL formatted date from component parts you would previously have to use some code similar to this.

DECLARE @YEAR INT
DECLARE @MONTH INT
DECLARE @DAY  INT

SET @YEAR = 2013
SET @MONTH = 1
SET @DAY = 17

-- Convert Version
SELECT CONVERT(DATE,CAST (@YEAR AS VARCHAR)+ RIGHT ('0'+ CAST (@MONTH AS VARCHAR),2) + RIGHT ('0'+ CAST (@DAY AS VARCHAR),2))

-- DateAdd version
SELECT DATEADD(MONTH,(@YEAR-1900)* 12 + @MONTH - 1,0) + (@DAY-1)

SQL Server 2012 provides a series of 'FROMPARTS' functions however.
The Syntax is like this ,

• DATEFROMPARTS (YEAR, MONTH, DAY)
  
• TIMEFROMPARTS (HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)
  
• DATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, MILLISECONDS)
  
• DATETIME2FROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)
  
• SMALLDATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE)
  
• DATETIMEOFFSETFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, FRACTIONS, HOUR_OFFSET, MINUTE_OFFSET, PRECISION)

In Addition an end of month function is available too.

• EOMONTH (START_DATE, [, MONTH_TO_ADD ])

Their use is demonstrated below.

DECLARE 
  @YEAR INT = 2013
, @MONTH INT = 01 
, @DAY INT = 09 
, @HOUR INT = 11
, @MINUTE INT = 59 
, @SECONDS INT = 59
, @MILLISECONDS INT = 0 
, @TODAY DATETIME = GETDATE()

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [DATEFROMPARTS]

SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS [TIMEFROMPARTS]

SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS [DATETIMEFROMPARTS]

SELECT DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3) AS [DATETIME2FROMPARTS]

SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [SMALLDATETIMEFROMPARTS]

SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE,@SECONDS,500, 5, 30, 3) AS [DATETIMEOFFSETFROMPARTS]

SELECT EOMONTH (@TODAY)  AS LASTDAYTHISMONTH
SELECT EOMONTH (@TODAY, -1) AS LASTDAYLASTMONTH
SELECT EOMONTH (@TODAY, 1)  AS LASTDAYNEXTMONTH

Links :
SQL Server 2012 New Date Functions

New Date,Logical Functions in SQL Server 2012

The ultimate guide to the datetime datatypes

SQL Server 2012 : New Windowing Functions

SQL Server 2012 Introduces 8 New Windowing Functions

 • FIRST_VALUE
 • LAST_VALUE
 • LAG
 • LEAD
 • PERCENT_RANK
 • CUME_DIST
 • PERCENTILE_CONT
 • PERCENTILE_DISC

SQL 2005 Ranking Functions

 • ROW_NUMBER
 • RANK
 • DENSE_RANK
 • NTILE

Traditional Aggregate Functions

 • SUM
 • COUNT
 • MIN
 • MAX
 • AVG

Windowing Functions work with OVER and the optional ORDER BY, PARTITION BY and BETWEEN functions.

SQL Server 2012 lets the aggregate functions work with OVER (ORDER BY ...). Previously only the Ranking Functions worked.


Links

TSQL : Using NTILE to divide a results set up.

SQL 2005 : RANK() vs DENSE_RANK()

Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

SQL Server 2012 Windowing Functions Part 1 of 2: Running and Sliding Aggregates

SQL Server 2012 Windowing Functions Part 2 of 2: New Analytic Functions

SQL Window Functions and You

Tip: OVER and PARTITION BY

SQL 2012 : LEAD & LAG (Transact-SQL)

LEAD and LAG are Windowing functions new to SQL Server 2012 returning the next and previous rows in a set respectively.

This eliminates the need for a self join (joining back to the same table)

Here are a couple of examples using AdventureWorks2012

Fistly, alphabetical currency names...

SELECT 
  [Name]
, LEAD(Name,1) OVER (ORDER BY Name ) LeadValue
, LAG(Name,1) OVER (ORDER BY Name ) LagValue
FROM [Sales].[Currency]

More usefully, LEAD and LAG used to compare the totals of shipped products with the next and previous days.

SELECT 
   ShipDate
  ,LAG(ValueShipped,1) OVER (ORDER BY ShipDate) AS DayBefore 
  ,ValueShipped
  ,LEAD(ValueShipped,1) OVER (ORDER BY ShipDate) AS DayAfter
FROM

 (
  SELECT ShipDate, SUM(TotalDue) AS ValueShipped
  FROM [Purchasing].[PurchaseOrderHeader]
  GROUP BY [ShipDate]
 ) ShippingTotals

ORDER BY [ShipDate]

Links :

Introduction to LEAD and LAG – Analytic Functions Introduced in SQL Server 2012

SQL Server 2012 Functions - Lead and Lag

LAG (Transact-SQL)

LEAD  (Transact-SQL)

Trigger Happy - New SQL 2012 Functions

SQL 2012 introduces new functions to test for changes inside a trigger

UPDATE() tests for UPDATE or INSERT attempts on one column.

COLUMNS_UPDATED() tests for UPDATE or INSERT actions performed on multiple columns.

CREATE TRIGGER trgColumnChange
ON TABLE FOR UPDATE
AS
IF UPDATE(columnname)
BEGIN
-- Do Audit stuff here
END

COLUMNS_UPDATED (Transact-SQL)

Error Catching with @@ERROR and @@ROWCOUNT

Error Catching with @@ERROR and @@ROWCOUNT

@@ERROR  - Error Number
@@ROWCOUNT - Rows affected

Retrieve both values directly after a modification statement

The following code fails as the CostRate cannot be negative.
This is due to a Column Constraint on the location table (SQL 2012 Adventureworks)
It does however demonstrate catching the error number

DECLARE @myError INTEGER
DECLARE @myRowcount INTEGER
INSERT INTO [Production].[Location] ([Name],[CostRate],[Availability],[ModifiedDate])
VALUES('Tool ',-2,    0.00, GETDATE())
SELECT @myError = @@ERROR , @myRowcount = @@ROWCOUNT

SELECT 'Error Number ' + CAST(@myError AS VARCHAR(10))
SELECT 'Row Count ' + CAST(@myRowcount AS VARCHAR(10))

Using @@ROWCOUNT to test data existance

THROW (SQL 2012 Error Handling)

THROW (SQL 2012 Error Handling)

TRY - CATCH error testing was added back in SQL 2005.
THROW has finally arrived in SQL 2012
It should be used in place of RAISERROR

Logging errors the old way -
RAISERROR ('An error occurred querying the table.', 16, 1);

And the new way -
THROW 50000, 'An error occurred querying the table.', 1;

Disadvantages to using THROW

• Error messages are buffered, and don’t appear in real-time (No WITH NOWAIT equivilent)
• Doesn’t support token substitutions
• Always uses severity level 16

A full comparison is in  Lenni Lobel's article linked below.
Throwing Errors in SQL Server 2012
THROW (Transact-SQL)

UPDATE .WRITE (SQL 2012)

UPDATE .WRITE (SQL 2012)

Syntax: .WRITE ( expression, @Offset , @Length )

UPDATE table
SET column.WRITE('Inserted Text', 18, 3) 
WHERE id = 1

As per MS BOL the new .WRITE clause of the UPDATE DML statement enables partial or full updates and high performance appends to varchar(max), nvarchar(max) and varbinary(max) data type columns.

A traditional UPDATE statement is logged
Using the .WRITE clause is minimally logged (faster)

If @Offset is NULL, the update operation appends expression at the end of the column.


NB : only columns of varchar(max), nvarchar(max), or varbinary(max)  work with UPDATE.WRITE
 
Links :
UPDATE statement with new .WRITE Clause
0201-SQL Server-UPDATE statement-use .WRITE for partial updates

Transaction error control - XACT_ABORT & XACT_STATE (SQL 2012)

XACT_ABORT can be used to control the behaviour of SQL when an error in a transaction occurs.
(it has been around since SQL 2000)

SET XACT_ABORT ON
If a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

SET XACT_ABORT OFF
Only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.


XACT_STATE  is new to SQL 2012.

It indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

XACT_STATE() = 1 - Committing the transaction will suceed
XACT_STATE() = -1 - Committing the transaction will fail
XACT_STATE() = 0  - No user transaction for request.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
        -- Do a DML operation - Insert, Delete or Update
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'Failed, Rollback occuring'
        ROLLBACK TRANSACTION;
    END;
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'Succeeded, Transaction committed'
        COMMIT TRANSACTION;  
    END;
END CATCH;
GO

Links :
XACT_STATE (Transact-SQL)
XACT_ABORT (Transact-SQL)

SQL Server PERSISTED Columns

SQL Server PERSISTED Columns (SQL 2005+)

Normal calculated column values are calculated each time a query references them.

Implementing a calculated column as PERSISTED physically stores results in the table.

PERSISTED Calculated Columns
+ Give Better Performance
+ You can create indexes on them
- Take up storage space

Calculated Column
ALTER TABLE invoice ADD invoicetotal AS itemtotal + itemtax



PERSISTED Calculated Column
ALTER TABLE invoice ADD invoicetotal AS itemtotal + itemtax PERSISTED



Links :

Using Computed Columns in SQL Server with Persisted Values
Top 10 Hidden Gems in SQL Server 2005
Persisted Columns Problem : Persisting a time difference

What's new in SQL Server 2012?

What's new in SQL Server 2012?

Rather than regurgitate a press release or add another list, here are some others who have already formed their opinions.

Top 12 Features of SQL Server 2012 (Joey D'Antoni)

My Top 5 SQL Server 2012 Features (Aaron Bertrand)

Top 10 New Features in SQL 2012 (John Weston)

Using SQL Server 2012 T-SQL New Features (Henry He)

T-SQL Improvements in SQL Server 2012 (Jonathan Allen)

Cool New SQL Server 2012 Metadata (Kalen Delaney)

Differences between SQL Server 2008 and SQL Server 2012 (Virendra Yaduvanshi)

SQL Server 2012 - Why it Rocks (Michael Corey)

Upgrading to SQL 2012 - Ten things you don't want to miss