sql solace

Link : Move Database Data Without Taking the Database Offline

2013-05-22T19:20:00.000+01:00

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

Pros

Can be done online

Cons

Speed
Cannot move system objects
Index Fragmentation may be caused

Link :32Bit SQL on a 64 Bit Box

2013-04-26T20:00:00.000+01:00

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

2013-04-03T20:18:00.000+01:00

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

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.

2013-04-01T02:41:00.002+01:00

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

2013-03-27T01:09:00.000Z

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

2013-03-23T23:01:00.004Z

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

2013-03-20T12:30:00.002Z

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

The Success/Failure of the plan
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

2013-03-13T17:58:00.000Z

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

2013-03-07T20:48:00.000Z

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

2013-02-07T19:24:00.000Z

Some Code to demonstrate

Creating a custom TABLE type
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

2013-02-06T01:58:00.000Z

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

2013-02-05T00:05:00.000Z

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

2013-02-04T23:51:00.000Z

Here is a quick script to demonstrate reversible encryption.

First, some configuration -

Setup a master key
Create a Certificate
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

Creating a table
Inserting Data
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

2013-02-03T22:00:00.000Z

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

2013-02-02T19:31:00.000Z

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.

Back it up
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

2013-02-01T19:05:00.000Z

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

2013-01-31T22:49:00.000Z

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

2013-01-30T20:15:00.000Z

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

Based on the named condition
Set the evaluation mode

3. Enable the Policy

Simple Talk : Introduction to Policy Based Management

Revision : I/O Affinity Mask

2013-01-29T23:19:00.000Z

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

2013-01-28T23:04:00.000Z

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

2013-01-27T22:41:00.000Z

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

2013-01-26T20:16:00.000Z

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

2013-01-25T02:47:00.000Z

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

2013-01-24T18:22:00.000Z

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

2013-01-23T19:06:00.000Z

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