Free Microsoft Ebooks

Here is the link to Free Microsoft Ebooks. The best thing about this is, it is available in a format that could be saved and accessed from your tablet or thru Kindle apps.

Dropping functions referenced as default constraints

When user defined functions are referenced as defaults values for columns and there is a need to Alter or Drop the function, SQL Server will not allow this without dropping the dependencies first.

It is manageable if the function is referred in one or two columns.However if these functions are referred across the database, it would become time consuming and cumbersome  to manually delete these default constraints.

The below queries will provide the table,schema,constraint and  column names that are needed to successfully drop and recreate the constraints.

I was able to come up with three ways of doing this.This one is my personal favorite since this uses just two tables and does the job.Here in this example [schema].[defaultfunction()] will be the function that is being referenced in the default constraints


SELECT '[' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + ']' AS 'Table Name',col.name AS 'Column Name',def.name AS 'Constraint Name',def.definition AS 'Default Value'
FROM sys.default_constraints def,sys.columns col
WHERE def.parent_OBJECT_ID = col.object_id
AND def.parent_column_id = col.column_id
AND def.definition = '([schema].[defaultfunction()])'


This is the next approach.The results are as good as the first.

SELECT '[' + SCHEMA_NAME(obj.uid) + '].[' + OBJECT_NAME(obj.parent_obj) + ']'  AS 'Table Name',col.name AS 'Column Name',obj.name AS 'Constraint Name',com.text AS 'Default Value'
FROM sysobjects obj,syscolumns col,syscomments com
WHERE col.cdefault = obj.id
AND   obj.id = com.id
AND com.text = '([schema].[defaultfunction()])'

This is the third approach.This would work fine if there is just one default schema.However there is a drawback.When there are multiple schema this would need the schema name and the existing columns will not be able to supply that information.There needs to be an extra join on sysusers.uid to fetch this information.

SELECT OBJECT_NAME(con.id) AS 'Table Name',col.name AS 'Column Name',OBJECT_NAME(con.constid) AS 'Constraint Name', com.text AS 'Default Value'
FROM SYSCONSTRAINTS con,sys.columns col,syscomments com
WHERE con.constid = col.default_object_id
AND con.constid = com.id
AND com.text = '([schema].[defaultfunction()])'

Now, going back to the first one, lets execute the it on AdventureWorks2012, using getdate() as the function referenced in default constraints .This is going to bring up the 77 rows.

The first column will provide the syntax to drop the constraint and the second column will recreate it.


USE AdventureWorks2012
GO


SELECT 'ALTER TABLE  [' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + '] DROP CONSTRAINT ' + def.name as 'drop constraint',
'ALTER TABLE  [' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT ' + def.name + ' DEFAULT(getdate()) FOR ' + col.name  as 'create constraint'
FROM sys.default_constraints def,sys.columns col
WHERE def.parent_OBJECT_ID = col.object_id
AND def.parent_column_id = col.column_id
AND def.definition = '(getdate())'

-Aravind

GO

Recently at an  SQLSaturday ,I learnt a trick - using GO to execute a statement or block of statements in a loop.

GO is a batch separator and it is not a T-SQL command.A long time back, I used to change the batch separator option to my name instead of GO and have a unwary developer look at SQL Server execute a piece of code with my name on it and GO crazy .

Since SQL Server 2005,the GO syntax has been changed to GO [count], where count is an integer.

Specifying a value for [count] would cause the block of code preceding the GO to be executed [count] times.

For example lets say we have a block of code

/*Start SQL Block*/

:

:

/*End SQL Block*/

GO 10

This will cause all the statements in this block to execute 10
 times.

This is the code from Books online that is used to generate a random number 4 times


DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 5
   BEGIN
      SELECT RAND() Random_Number
      SET @counter = @counter + 1
   END;
GO

This can be replaced by

SELECT RAND() Random_Number
GO 4


Generally, while tuning a query, I would execute a block of statements or a  stored procedure multiple number of times to get a distribution of the execution times each time after clearing the cache and repeat this under varying test conditions.Whenever there is a necessity to execute statements in a loop, GO [count] syntax would come handy without having to code additional logic.

SQLSaturday in Pittsburgh

I am double minded about going to this one....Its going to be a 4.5 hour drive

Welcome to SQLSaturday #171

SQLSaturday is a full day training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Oct 6, 2012 at La Roche College, Zappala Center, 9000 Babcock Boulevard, Pittsburgh , PA 15237. Admittance to this event is free, but we do charge a lunch fee of  $10.00 so that we can provide a cafeteria lunch - not pizza!   This donation helps to defer the cost of lunch and morning coffee, juice and water. 

24 Hours of PASS

24 Back-to-back Hours of Free SQL Server Training!

Get your learning on - indulge in free online SQL Server training with the upcoming 24 Hours of PASS Summit Preview event on September 20, 2012. Join SQL Server experts as they take to the virtual stage with in-depth sessions on the hottest SQL Server topics over 24 consecutive (and free!) technical webcasts. No matter where you are in the world, you’ll gain access to best SQL Server training, delivered to your computer.
 

Browse the sessions by schedule or track and register today, it's free.
This 24 Hours of PASS event gives you a glimpse at the unmatched technical content PASS Summit 2012 has to offer across 190+ technical sessions, pre-conference seminars, and invaluable networking opportunities. Find out why you should attend and justify your attendance today. You won’t want to miss PASS Summit 2012 in Seattle, WA, Nov. 6-9.

Powershell 3.0 is available

Powershell 3.0 is available for download.Yeah!.It reqires .net framework 4.0 as a prerequisite.You can down load it here.

Webcast on High Availability features in SQL Server 2012

Quest has this webcast on High Availability features in SQL Server 2012 coming up on August 16th.

Register to attend the Experts’ Perspective Webcast: Achieving High Availability with SQL Server 2012