Host your database on Amazon cloud for FREE

You could setup your database on the amazon cloud here for free.The database can have a 20GB capacity with 10 million I/Os.

It is easy to signup for an account, create an instance, provide access and launch it.For step by step guide view this link.

Rename Logical Filename

What would be a good reason to change the logical name of a database?

Well, every time there is a requirement to create a database for development,the DBA  provides a name that would be most applicable to that database at that point in time.In most cases an abbreviation of the project.

Couple of months/years later,this database has to be setup for System test,Technical test and so on.A backup of the development database would be restored on to the new environment.While the data and log files are named based upon the destination environment,the logical names remains the same.In most cases, by the time the product is delivered the logical and physical names are way different.

The logical name of the database can be changed using the below syntax.

ALTER DATABASE AdventureWorks2012
  MODIFY FILE ( NAME = AdventureWorks2012_Data ,
                                               NEWNAME = AdventureWorks2012_lob_data )

Though the logical name can be changed post restore for each environment,I would steer clear of this approach.Instead a better way would be to avoid environment names in the logical file name.

A sample template could be to just use

            <Projectname>_data/log or <Projectname>_<purpose>_data/log
  Eg. Proj_LOB_Data/Log.

This would make sure that the filename does not have to be modified at a later stage and the initial name remains vaild throughout the lifecycle of the project.

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

Data Dictionary for SQL Server

Every now and then I face this scenario where I am required to provide a quick Data Dictionary in a spreadsheet.This spreadsheet needs to have just the list of all tables and their properties.This is not one of the cases where the comments are required, rather a quick view to just skim thru the table list or for somebody to be aware of what table structures are there is the database.


I was able to come up with a query to consolidate all the required information and to format it so that it can be copied to a spreadsheet and sent out.


select 
case when clm.column_id = 1 then object_schema_name(clm.object_id) else '' end as 'Schema',
case when clm.column_id = 1 then object_name(clm.object_id) else '' end as 'Table',
clm.name as 'Column',
typ.name as 'Datatype',
clm.max_length as 'Length',
case   
when typ.name in ('int','smallint','decimal','money','tinyint','date','smallmoney','time','numeric','float','real','bigint','datetime2','datetimeoffset')  
then ltrim(rtrim(str(clm.precision))) +',' + ltrim(rtrim(str(clm.scale))) 
else ''  
end as 'Precision,Scale',
clm.is_nullable as 'Nullability',
case 
when cns.definition is null 
then '' 
/* below condition not reqd for SQL 2000 database upgraded to later versions*/
when isnumeric(substring(cns.definition,3,len(cns.definition)-4)) = 1 
then substring(cns.definition,3,len(rtrim(cns.definition))-4) --2005 onwards
else substring(cns.definition,2,len(cns.definition)-2)  --for 2000 database upgraded to later versions 
end as 'Default'
from sys.columns clm join sys.types typ 
  on clm.user_type_id = typ.user_type_id
left join sys.default_constraints cns
  on clm.default_object_id = cns.object_id
where objectpropertyex ( clm.object_id , 'IsUserTable' ) = 1
order by object_name(clm.object_id),clm.column_id





When I got the defaults to be displayed I could see that SQL Server 2005 onward there were two sets of parentheses around the default value especially for numeric datatypes as against just one for character default values. Whereas for SQL Server 2000 databases upgraded to later versions, there was an uniform parentheses around the default value.
I added a condition to check if the datatype being processed is numeric so that the query works across SQL Server 2005/2008/2012.
For SQL 2000 database upgraded to later versions just comment out the 



Also I was not sure if I had to be using the system_type_ids or user_type_ids while getting the datatypes.
The user_type_ids are distinct whereas for certain datatypes system_type_ids are repetitive.So I have used the user_type_ids to get the datatypes.


I have also displayed precision and scale for the datypes where this information is relavent and blanks for the rest.


--Aravind

First Blog

For quite sometime now ,I have been thinking about creating a blog to post my views and thoughts mostly from a technical perspective.I am a SQL Server DBA and I would be posting information I find interesting, ones I have been working on,thinking about and have fun blogging