SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com

Book Review – SQL Server Interview Questions and Answers

2012-01-02T12:46:00.000+05:30

Well, I am not as qualified in SQL Server as the authors ofthis book (Pinal Dave & Vinod Kumar) though I dare to writemy review for his book as both of the authors are in my favourite list and I don’teven miss any article written by them then how could I miss a book written bythem? After reading the book, I really tempted to share my personal opinion with my blog reader.

“SQL Server Interview Questions and Answer” makes firstimpression that this book is written to prepare you for the interview of DBA orSQL Developer or BI professional but NO, this book is not ONLY for those whoare preparing for an interview, even seasoned developer or DBA could refer thisbook to master the basics which we may avoid or forget over the time butknowing those may create a firm ground for the project we all are working on.

Both of these authors are well known to present hard &difficult concept in very simple yet powerful manner which directly executeINSERT command in your memory without any trigger or exception, just like straitthrown dart pinch in board, this really show very hard work of both authors.

I really impressed with the book for following points.

“Points to Ponder” section at the end of each chapter as a Quick references to Joes 2 Pros books (I had privilege to read few of them)
Very inspiring quote at the begging of chapters (I enjoy it in Vinod Kumar’s twits too, in twitter)
Links to SQLAuthorty’s articles

When I first heard about this book, I though how much morecontent could be there in this book? As Pinal has already written the seriesarticles on this topic but with my surprise, there are LOT MORE to learn inbook so even if you have read all articles on this series in Pinal’s blog, don’thesitate to have this book, you will have so many (80%) new stuffs to look at.

This book scores 10/10 and I personally highly recommend thisbook as a good & quick reference to any professional who are dealing withSQL Server at any level.

To order the copy of the book for your own, visit SQLAuthority.com

Happy Reading!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Error Fix: Msg 605 Attempt to fetch logical page (3:1307866) in database 6 failed. It belongs to allocation unit 72057594113359872 not to 72057594113490944

2011-11-03T08:20:00.000+05:30

Msg 605, Level 21, State 3, Line 1

Attempt to fetch logical page (3:1307866) in database 6failed. It belongs to allocation unit 72057594113359872 not to72057594113490944.

Msg 21, Level 21, State 1, Line 1

Warning: Fatal error 605 occurred at OCT 31 2011 6:08AM. Note the error and time, and contact your system administrator.

This error comes in red color is disguise, if you login withany user which is not sysadmin type user, you will see this kind of error incorruption of page(s), if you login with SA, Administrator or and otherSysAdmin login, you will not see error comes in red color but the descriptiveerror given in the title

There was a festival holidays in our Indian office from 26^thto 28^th OCT 2011 (Wed/Friday) so obviously I was out of town andback after 5 days on 31^st OCT 2011. As soon as I come to the officeand checked few of my email, I suddenly get complain that few pages in oursoftware are throwing an error so I picked up the query which was there inthose pages along with the same parameter they were providing and found theerror given above.

As soon as I read this error, I came to know that there is apage corruption in database. It may bedue to heavy snow fall in NJ in October, they have got power failure there fromfew days and tried to keep the server up and running via power generator. Assoon as Power Generator’s fuel get finished, everything gets down and afterrefuel, they starts everything again. I came to know this as soon as I comeback from holiday. I think this is the Indian New Year gift to me.

I tried to gather some more information about the data fileand page number given in error message with DBCC PAGE command.

dbcc traceon(3604)

dbcc page('MyDatabaseName',3,1307866,3)

dbcc traceoff(3604)

You can use option 0,1,2,3 as the last parameter (bold 3) ofDBCC PAGE command.

So now I have two options.

1.) Restorethat page from full Database backup

2.) TryDBCC commands and if needed, allow data loss

I HIGHLY RECOMMEND to go for 1^st option but ifyou are out of luck and don’t have last full backup, you must have to go forsecond way.

I have restored page from my last full database backup withbelow given TSQL.

RESTORE DATABASE MyDatabaseName
PAGE = '3:1307866'
FROM DISK = 'D:\MyDatabaseName.bak'
WITH NORECOVERY

Now, let us talk about second option if you don’t have fullbackup of your database. Use DBCC CHECKDB command.

--checkingdatabase's integrity and won't show so many informational message,

--it will onlyshows error messages and warnings.

DBCC CHECKDB ('MyDatabaseName') WITH NO_INFOMSGS

Once you execute above command, it will recommend you repairlevel. it may recommend REPAIR_REBUILD if you really lucky but if you will seerepair level REPAIR_ALLOW_DATA_LOSS, you have to be ready to lose some of yourdata.

You may use either of the below given command based on theRepair Level you have been suggested.

1.)

ALTER DATABASE MyDatabaseName SETSINGLE_USER

DBCC CHECKDB('MyDatabaseName', REPAIR_REBUILD)

ALTER database MyDatabaseName SETMULTI_USER

2.)

ALTER DATABASE MyDatabaseName SETSINGLE_USER

DBCC CHECKDB('MyDatabaseName', REPAIR_ALLOW_DATA_LOSS)

ALTER database MyDatabaseName SETMULTI_USER

Moral of the story is, always have FULL recovery model foryour database and schedule full/ transaction/ differential backup policy. Thisis a MUST DO for any DBAs. You never know, when will you need it!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examplesand explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Serverrelated question at my “ASK Profile”

Playing with OPTION KEEPFIXED PLAN hint in SQL Server

2011-11-02T12:26:00.002+05:30

My previous article was on Compilation& Recompilation of Stored Procedure. After reading that article, two ofmy reader sent me an email about more clarification on some terminology and thatis the base of writing this article.

Actually SQL Server Query Optimizer is really one of thesmart tools which used to find or generate best execution plan for query or storedprocedure and make the task easy for DBA. Basically it uses Cost BasedOptimizer (CBO) to do this task, even sometime feels to have specific ExecutionPlan for query so Microsoft have provided many different hints at differentlevel.

Query Optimizer (QO) used to save execution plan when itcompiles Stored Procedure first time and use the same execution plan when thesame stored procedure is being used again so that you can get rid of overheadof generating execution plan again and again.

When index statistics changes heavily, QO used to recompilethe statement to get best plan for your stored procedure but if you are surethat the existing Plan would works fine than one should go for OPTION KEEPFIXEDPLAN for that statement so that you can save your resources and boost upperformance.

I will demonstrate this with few queries run in SQL ServerManagement Studio (SSMS) and capture the results in Profiler.

I will select following Events in Profiler while creatingnew Trace for my server.

Stored Procedure

SP:Completed
SP:Recompile
SP:smtpCompleted
SP:smtpStarting

Now execute following TSQL in your SSMS.

--create onedatabase which you can delete after running this example

create database SQLHub

USE SQLHub

--if orderstable is already there. you can delete it than create new one with name"Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

DROP TABLE orders

END

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

--creatingnonclustered index

CREATE NONCLUSTERED INDEXIDX_ORD ON Orders(amount)

--inserting only1 record in our table

INSERT INTO orders VALUES(GETDATE(),1001,111)

--creating SPwhich will SELECT all records from Orders table where Amount is 1001

CREATE PROC spGet_ord

SELECT * FROM orders WHERE Amount=1001

--execute SP

exec spGet_ord

If you will see in profiler than you would get followingevent captured

Now, make change in Index statistics by inserting so manyrecords in table so that we can test the effect in profiler after executing SPagain.

--inserting50000 fack rows into table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 50000

DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

ABS(a.object_id % 10),

CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

--execute SPagain

exec spGet_ord

Now, if you see the profiler, you will see “SP:Recompile”event as your statistics are updated so it need recompilation of statement. If youagain few more thousands record in same table and execute the same SP again,you will see recompilation again.

Now, after droping table and SP, we will make one smallchange in Stored Procedure and will use “KEEPFIXED PLAN” hint so that we canavoid recompilation.

drop proc spGet_ord

drop table orders

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

CREATE NONCLUSTERED INDEXIDX_ORD ON Orders(amount)

INSERT INTO orders VALUES(GETDATE(),1001,111)

CREATE PROC spGet_ord

SELECT * FROM orders WHERE Amount=1001

--adding belowstatement other than that, same code as previous SP

OPTION (KEEPFIXED PLAN);

exec spGet_ord

After recreating table along with fresh records, we aregoing to see what happened while executing SP in above statement.

Since our table now has only 1 record so it is time tochange statistics with bulk insert as follows again.

--inserting100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERTscript from one of his demo to generate big table)

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 50000

DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

ABS(a.object_id % 10),

CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

exec spGet_ord

--if you wish,you can uncomment below code and delete SQLHub database

--use master

--go

--drop database sqlhub

Here is the screen capture of Profiler which doesn’t showRecompilation.

You can even add few more thousands of rows and execute SPagain, you won’t get recompilation.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Some facts about Stored Procedure Compilation & Recompilation

2011-10-31T09:23:00.002+05:30

I generally ask one question in interview “If we have onecomplex query which JOIN five table, what would work best from .NETapplication, Call of Query or Stored Procedure?” 80% of candidate used to say “SPworks fast”. My next question always would be “WHY?” than out of those 80%, 60%would say “Because SP is a compiled code”. After that I fire my follow upquestion, I can see option of “Compile page or project in .NET but I never seethat kind of option in SSMS, how do you compile your SP?”, once I EXECUTE thisquestion, 90% candidate of those 60% candidate would like to keep silence ordivert the talk.

Anyway, intention of this article is to let you know somefacts about SP compilation & Recompilation. Since it is big topic andwouldn’t cover under on article, I may come up with some more articles on thistopic but right now, let me explain some basic facts only.

First of all, let me tell you that you don’t need to compileStored Procedure manually, when you execute it, SQL Server will compile yourStored Procedure for you and save the execution plan for future use so that itdoesn’t need to compile again and again, this is generic understanding, itdoesn’t work all time as few facts are there which cause recompilation manytime or every time. If you want to recompile your Stored Procedure manually,you should use “SP_Recompile” Stored Procedure given by SQL Server.

Now, you think if recompilation is that bad than whyMicrosoft has given facility to recompile? Well, let me have an opportunity tobreak the ice, recompilation of stored procedure is not always bad. It may bebeneficial or may be harmful, it is totally depends on the situation.

Actually compilation of Stored Procedure stores theexecution plan first time you execute your Stored Procedure and every follow upcall would use the same execution plan but recompilation of SP would be helpfulif you have new statistics or new index on the table. BTW, in SQL Server 2008+there is in-built functionality to recompile at statement level rather thanrecompiling whole stored procedure which is less resource centric.

Following is the list of basic cause which forces StoredProcedure to recompile.

Change in SET option within Stored Procedure
Execution plan is very old
Schema change in table, index, view or temptables which are used in Stored Procedure
“Deferred object resolution”, means object wasnot available while compiling Stored Procedure but you have created later on,may be some temp table you have created in Stored Procedure.
Call of “SP_Recompile” Stored Procedure.
Call of RECOMPILE clause in Stored Procedure.
Statistics are old

How to avoid Stored Procedure recompilations?

Avoid using temp table or other DDL statements as long aspossible.
Use table variable in Stored Procedure if needed
Avoid changing SET option in Stored Procedure likeARITHABORT, Quoted_Identifier, ANSI_NULLS, ANSI_WARNINGS etc.
Avoiding recompilation by statistics change by using “KEEPFIXEDPLAN” hint.
Disable Auto Update statistics for your database.

Well, these are very basic understanding and each point ofthis article may consume separate dedicated article and I may come up withseries on this very soon.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Last chance to win free Apple iPad this year

2011-09-12T12:36:00.003+05:30

SQL Server MVP/Auther/Trainer Jacob Sebastian haslaunched another quiz “SQLServer DBA Quiz 2011” which is the last quiz of this year. So don’t missthis chance and answer the quiz question asked by Quiz master.

My question is already published regarding DeadLock, Live Lock and Blocking. If you have not participated yet, still, youhave chance to win as all questions are still open.

If you are not seasoned DBA, still you can refer allquestions and answer given by expert to enhance your knowledge.

If you are experienced DBA and you know the answer, you cananswer the question, share you knowledge with community and have chance to winiPad. So what are you waiting for? Try to answer as more question as possible.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Move login with password from one SQL Server instance to another

2011-08-05T13:03:00.001+05:30

I have already written a small script to fix orphanusers while moving database from one server to anotherserver but the major problem is, that script just auto fix the login but can’thave login with the same password which old instance/server was having.

Microsoft has provided one small script to help you outin this situation. Look at the below script which will generate T-SQL for allusers available in current instance with password so that you can run that sameLogin script in another server/instance of SQL Server and have samelogin/password.

USE master

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

DROP PROCEDURE sp_hexadecimal

CREATE PROCEDURE sp_hexadecimal

@binvalue varbinary(256),

@hexvalue varchar (514) OUTPUT

DECLARE @charvaluevarchar (514)

DECLARE @i int

DECLARE @length int

DECLARE @hexstringchar(16)

SELECT @charvalue= '0x'

SELECT @i = 1

SELECT @length = DATALENGTH (@binvalue)

SELECT @hexstring= '0123456789ABCDEF'

WHILE (@i <= @length)

BEGIN

DECLARE@tempint int

DECLARE@firstint int

DECLARE@secondint int

SELECT @tempint= CONVERT(int, SUBSTRING(@binvalue,@i,1))

SELECT@firstint = FLOOR(@tempint/16)

SELECT@secondint = @tempint - (@firstint*16)

SELECT@charvalue = @charvalue +

SUBSTRING(@hexstring,@firstint+1, 1) +

SUBSTRING(@hexstring,@secondint+1, 1)

SELECT @i = @i + 1

END

SELECT @hexvalue = @charvalue

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

DROP PROCEDURE sp_help_revlogin

CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @type varchar (1)

DECLARE @hasaccessint

DECLARE @denyloginint

DECLARE@is_disabled int

DECLARE@PWD_varbinary varbinary(256)

DECLARE@PWD_string varchar(514)

DECLARE@SID_varbinary varbinary (85)

DECLARE@SID_string varchar (514)

DECLARE@tmpstr varchar(1024)

DECLARE@is_policy_checked varchar (3)

DECLARE@is_expiration_checked varchar (3)

DECLARE @defaultdbsysname

IF (@login_nameIS NULL)

DECLARElogin_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

DECLARElogin_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name,l.hasaccess, l.denylogin FROM

sys.server_principals p LEFT JOIN sys.syslogins l

ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary,@name, @type,@is_disabled, @defaultdb, @hasaccess, @denylogin

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSElogin_curs

DEALLOCATElogin_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin script'

PRINT @tmpstr

SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr= '-- Login: ' + @name

PRINT@tmpstr

IF (@type IN ( 'G', 'U'))

BEGIN -- NT authenticated account/group

SET@tmpstr = 'CREATE LOGIN' + QUOTENAME( @name ) + ' FROM WINDOWS WITHDEFAULT_DATABASE = [' + @defaultdb + ']'

END

ELSE BEGIN -- SQL Serverauthentication

-- obtainpassword and sid

SET@PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

EXECsp_hexadecimal @PWD_varbinary, @PWD_string OUT

EXECsp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtainpassword policy state

SELECT@is_policy_checked = CASEis_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SELECT@is_expiration_checked = CASE is_expiration_checked WHEN1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET@tmpstr = 'CREATE LOGIN' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' +@SID_string + ',DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked ISNOT NULL )

BEGIN

SET@tmpstr = @tmpstr +', CHECK_POLICY = ' +@is_policy_checked

END

IF ( @is_expiration_checked ISNOT NULL )

BEGIN

SET@tmpstr = @tmpstr +', CHECK_EXPIRATION = ' +@is_expiration_checked

END

IF (@denylogin = 1)

BEGIN -- login is denied access

SET@tmpstr = @tmpstr +'; DENY CONNECT SQL TO ' + QUOTENAME( @name )

END

ELSE IF (@hasaccess = 0)

BEGIN -- login exists but does not have access

SET@tmpstr = @tmpstr +'; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

END

IF (@is_disabled = 1)

BEGIN -- login is disabled

SET@tmpstr = @tmpstr +'; ALTER LOGIN ' +QUOTENAME(@name ) + ' DISABLE'

END

PRINT@tmpstr

END

FETCH NEXT FROM login_curs INTO @SID_varbinary,@name, @type,@is_disabled, @defaultdb, @hasaccess, @denylogin

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

This script is copied from here.

Now all you need to do is, run stored procedure “sp_help_revlogin” with following command.

USE master

EXECsp_help_revlogin

This is really very helpful script which I have even used somany times. Keep it handy, you never know when you will need it.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Do I need backup of my database?

2011-08-01T18:35:00.003+05:30

If you are working as a DBA or you have responsibility tomaintain your SQL Server database up & running, take my words, you have NOOPTION of full database backup.

Recently I came to know that one person is not taking backupjust because he has RAID and Mirroring setup so he is least worried aboutbackup. Believe me, no matter what redundant hardware / software and highavailability setup you have, you MUST take backup regularly because even goodand up-to-date disaster recovery system could get failed. So, you never know,when and how do you need your latest backup file.

Let us discuss why database backup is really that muchimportant.

Suppose you have high availability solutions like RAID orserver clustering but what if you hardware gets corrupt and may be completedisk array get corrupt? You have to build up your database from backup only.

Sometime, catastrophic events or natural disasters likeflooding, earth quake etc. could damage your hardware or may be complete datacenter and if you have no backup at other location, you have no place to gofor.

May be sometime Security issues comes to the picture andsomebody intentionally or unintentionally damage you data which could beaffected adversely and in that situation you might look for the latest databasebackup to restore that data.

Sometime your OS or even your SQL Server gets corrupted andyou might need to restore data from backup itself.

In short, I would HIGHLY recommend database backup as “Preventionis always better than cure”. So, keep strong backup policies with full databasebackup, differential database backup and transaction log backup, depends onyour need and business policies.

BTW, I am not against the high availability concepts likemirroring, clustering, replication or log shipping. You should implement thosetoo as per your business needs but also MUST keep strong backup policies.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Index Fill Factor in SQL Server

2011-07-28T17:41:00.003+05:30

Today once again I felt to write something about Index, soopen up the listof Index articles I have written, find out the topic which are stillmissing for concept of Index and thought to write about fill factor.

Generally, when you create an Index, your data get stored inB-Tree format where you can find “Root Page”, may be “Intermediate Page” and “LEAFPage”. “Leaf Page” would have your actual data sorted in order of Index key andeach “Leaf Page” would be of 8KB.

When you insert/update/delete data to/from table, it triesto insert in the proper data page according to the record you are inserting.For example, you have Index on SSN number you are inserting new row with SSN number,SQL Server tries to insert that record in the appropriate page, suppose yourSSN starts with “2” so it will find what is the last page which has SSN numberstarts with “2”, SQL Server will insert your new row in that page only. If your8 KB page is full and don’t have room to accommodate new row whose SSN startswith “2”, it will split page and the data in that page will be shared betweentwo pages so now you have two pages which are half full so your row will be accommodatedin that page.

If your page would already had space for accommodating newrow, you wouldn’t need to wait for extra I/O overhead and wait until page splitgets finish and space for your row would become available.

This is the time when FillFactor comes into the picture.Fill Factor decides how much your page would be filled up initially. Supposeyou give 10 in FillFactor than your data page will consume only 10% of your 8KBpage size and when you exceed this limit of 10%, it keeps 90% page empty andcreate new page for other records. Now,when you insert new records, you don’t need to worry about I/O overhead of pagesplit as you would have 90% free space and your record will be accommodate inthat space easily. So, if you have lower number of Fillfactor, you can decreaseI/O over head generated by Page Split which helps you to write your datafaster.

Now, you might be thinking now that why shouldn’t I use lownumber always like 5% or 10% in Fillfactor? Well, it will decrease your pagesplit but it will increase number of data page in your index so every time youscan your index, you have to read more pages and it is again over head whilereading the data and decrease the speed of reading data. Suppose you have 10records in one table and its size is 8KB, you can fit all 10 records in onepage only but if you have fill factor of 50 than those 10 records will bestored in two pages rather than one while creating an index.

Uff, what to do now? if we decrease fillfactor figure, ithelps in writing but creates problem in reading and if we increase fillfactorfigure may be 100% than helps in reading but creates issues while writing indatabase?

You have to be reasonable and have to take decision basedthe load on your database, you have to decide first that you have more writesor more read?

Personally I majority go for figure somewhere between 80 to90 for fillfactor so that you have some space remain for new records anddecrease page split at the same time, we don’t keep too much free space in datapages so number of pages doesn’t get increased. Even, it is not final figure I use for all my databases, if my database is read only or very rare insert/update, I would go even for 100% Fillfactor and if my database is write intensive, I would have even 50% Fillfactor.

BTW, you can define Fill Factor while creating an Indexor rebuildingan Index or you can set default value for fill factor via following T-SQL (bydefault it is 0 in SQL Server).

--turning onadvanced configuration option

Sp_configure 'show advanced options',1

RECONFIGURE

--setting upfill factor

sp_configure 'fill factor', 90

RECONFIGURE

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

ISNULL, COALESCE or CONCAT_NULL_YIELDS_NULL in SQL Server

2011-07-22T15:29:00.003+05:30

Before we jump into the core logic, let me explain you whatare the use of this functions / property.

BTW, this is the table to be used in example:

CREATE TABLE EMPTEST

(

FirstName varchar(10)

,MiddleName varchar(10)

,LastName varchar(10)

)

INSERT INTO EMPTEST

select 'Ritesh','A','Shah' UNION ALL

select 'Roger',NULL,'Federer' UNION ALL

select 'Steffi',NULL,'Graf'

Select FirstName,LastName fromEMPTEST

ISNULL: Thisfunction replaces the NULL value with specified value given in function.Suppose we have probability to have Middle Name NULL in Employee table, wecould do something like below given TSQL statement.

Select FirstName, ISNULL(Lastname,'') as LastName From EMPTEST

If Lastname will be NULL, it will be replaced with blank inresult set.

COALESCE: BasicallyCOALESCE function will return first not null value from the given list, so wecan use this function in place of ISNULL too, like this:

Select FirstName, COALESCE(Lastname,'') as LastName From EMPTEST

NULL is nothing but the absent of value, it doesn’t evenrepresent a blank or space or zero. When you try to concatenate two or morethan two strings and any of the string is NULL, it will return the NULL only. Seefollowing example:

Select FirstName + ' ' + MiddleName + ' ' + LastName as FullName fromEMPTEST

You will get first record “Ritesh A Shah” as full name and remainingtwo records as NULL as those two records are having NULL value in itsMiddleName field so concatenate results will be NULL only.

You can have solution for that with ISNULL & COALESCE.

Select FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName asFullName from EMPTEST

Select FirstName + ' ' + COALESCE(MiddleName,'') + ' ' + LastName asFullName from EMPTEST

In this solution, you have to wrap up all the fields all thefields with either ISNULL or with COALESCE if it has probability of containingNULL value. If you have many fields in one query which needs this wrapping, itis bit tedious for you as a developer. At this time, CONCAT_NULL_YIELDS_NULLproperty comes as a rescue.

CONCAT_NULL_YIELDS_NULL:this property controls concatenation of the string, like what should do if anyof the string is NULL and it is being used in concatenation.

You can set this property at database level by ALTERDATABASE command or you can use this for your current session/connection.

Select FirstName + ' ' + MiddleName + ' ' + LastName as FullName fromEMPTEST

As I have already told you that above query returns tworecord with NULL value as FullName, you can do something like below TSQL to setCONCAT_NULL_YIELDS_NULL for your batch.

SET CONCAT_NULL_YIELDS_NULL OFF;

Select FirstName + ' ' + MiddleName + ' ' + LastName as FullName fromEMPTEST

SET CONCAT_NULL_YIELDS_NULL ON;

By default CONCAT_NULL_YIELDS_NULL set with TRUE (ON) for every database but youcan turn it OFF for your batch or for your database (by ALTER DATABASEcommand). It will save you from writing ISNULL and COALESCE command for manytimes in your query.

If you want to check whether your database is set to TRUE orFALSE for this property or your SESSION has this property TRUE or FALSE, youcan use following TSQL.

SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsNullConcat')

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')

Personally I prefer to go for either ISNULL or COALESCErather than CONCAT_NULL_YIELDS_NULL . There are few reasons for that.

--> CONCAT_NULL_YIELDS_NULL is supported in even SQL Server 2008 R2 alongwith previous versions of SQL Server but as per Microsoft, They will remove this feature in new releases, it will be by default ONin new versions and you will not be able to set it OFF

--> If you havethis option in Stored procedure, it will force your Stored Procedure to recompile every time you execute your SP.

Apart from these, as long as possible, we should avoid tempering with default settingsof SQL Server unless you know what exactly your doing.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Should Auto_Close database property be ON or OFF in SQL Server?

2011-07-21T12:01:00.002+05:30

One of my friends just leased one dedicated SQL Serverhosting server with managed support for his own business purpose. Though it ishosted at hosting company’s data centre with very good hardware configurationand being used for his own (one) database only, it was performing very slowly atcertain point of time though there is no heavy work load.

He asked me if I can help him out in this matter so I hadquick look into it and after sometime I found Auto_Close property a culprit.

Let me first tell you what this property is and how &where does it comes into the picture.

When we first time access or open the database in SQLServer, our database is assigned some resources to maintain its own stat, somememory for its use, some buffer space and many more. Auto_Close helps you tofree up these resources when last user disconnects from the database.

This is good thing to keep unused resource free but what ifyour last user disconnects and every resource gets free for claim and after fewminutes of freeing up resource another user came to connect to this database? ObviouslySQL Server will assign all resource to the database again and user has to waituntil all resource assigned to the database means, slow performance for thatuser first time.

If you are having only one or may be few databases in yourinstance, I highly recommend keep Auto_Close property to OFF. I have seen thathosting company keep these settings on for their shared hosting server whereyou can find hundreds of databases in one instance.

BTW, As per Microsoft, this feature will be removed from laterversion so this is one more reason not to use this setting in yourdatabase.

Here is the TSQL to check whether Auto_Close is ON or OFFfor your database.

SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsAutoCLose');

Here is the syntax which helps you to set ON or OFF forAuto_Close property in your database.

Alter Database YourDatabaseName SetAuto_Close ON

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36 Could not create a capture instance because the capture instance name 'dbo_ChangeDataCapture' already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

2011-07-20T11:10:00.001+05:30

After reading my previous article on CDC(Change Data Capture), one of the reader had tried to do it in hisdevelopment server but in his server, CDC was already enabled and anotherco-incident was that, the table he has choose for CDC, was already having itscapture instance, may be any of the other team member might have done it and hedoesn’t knew that , so he was greeted with the error like given below.

Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance,Line 36

Could not create a capture instance because the capture instance name'dbo_ChangeDataCapture' already exists in the current database. Specify anexplicit unique name for the parameter @capture_instance.

Error message is pretty much clear that the table he was usingwas already having capture instance so obviously he should use another tablefor this testing purpose or remove CDCfrom that table and re-create it or while activating CDCfor that table, he has to provide another name for capture instance table by providing@Capture_Instance name explicitly (not recommended). I will provide TSQL neededfrom disable CDClater in this article.

Well, these are some of the solution when you face abovegiven error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I knoweven before activating CDCthat the table is CDC enabled or not.

It is not a big issue; you can use very small TSQL querieslike below when you want to know it.

--list out thename of databases which are CDC enabled

SELECT [name], database_id

FROM master.sys.databases WHERE is_cdc_enabled =1

--list out alltables which are CDC enabled in your database

SELECT [name] AS Table_name

FROM sys.tables WHERE is_tracked_by_cdc =1

--know indetails like which table is CDC enabled

--which is thecapture instance of that table

EXEC sys.sp_cdc_help_change_data_capture

--disable CDCfrom your table.

EXECUTE sys.sp_cdc_disable_table

@source_name = N'ChangeDataCapture',

@source_schema =N'dbo',

@capture_instance =N'dbo_ChangeDataCapture';

--disable CDCfrom your database

EXEC sys.sp_cdc_disable_db

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Change Data Capture (CDC) in SQL Server 2008

2011-07-18T12:46:00.003+05:30

Recently I was working on one project and client required toaudit the data, means, insert / update / delete should be tracked on someimportant table. There are many ways you can capture these information (AuditTrail), I have written quite a few articles on this subject too but all thoseneeds additional code to be written. Fortunately my client is using SQL Server2008 R2 version so I don’t even need to write down any specific code to capturechanged data as there is facility of CDC (Change Data capture) in SQL Server2008.

Even before we look at CDC in details, make sure you haveyour SQL Server Agent is running as CDC in SQL Server 2008 will use SQL ServerAgent to make audit trail for you. If your SQL Server Agent is not running atthe moment, start it from control Panel->Administrative Tools-> Services.You can find “SQL Server Agent (YourInstanceName)”, just start this service andthen you will be able to work on CDC.

create database SQLHub

use SQLHub

Create Table ChangeDataCapture

(

ID INT Identity(1,1)

,Name varchar(20)

)

--enable CDC inSQLHub database

--this willcreate "cdc" schema in SQLhub database too

--along with"cdc" schema, it will create some system table

--in"cdc" schema

USE SQLHub

EXEC sys.sp_cdc_enable_db

--now enable CDCfor our table created above.

--when you willenable cdc for ChangeDataCapture table

--it will createtwo job under SQL Server Agent

--which willread data from transaction whenever you will make any change in data

--and stores itin CDC table

USE SQLHub

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'ChangeDataCapture',

@role_name = NULL

--now you haveorginal table, named "ChangeDataCapture"

--another tableto keep all changes "ChangeDataCapture",

--which has beencreated in step above

select * fromChangeDataCapture

--followingtable will be there under category of "System Tables" folder.

select * from cdc.dbo_ChangeDataCapture_CT

--now let usmake some DML operation in ChangeDataCapture table and

--observe howdoes it stores data in cdc.dbo_ChangeDataCapture_CT

INSERT INTO ChangeDataCapture

SELECT 'Ritesh Shah' UNION ALL

SELECT 'Rajan Shah' UNION ALL

SELECT 'Teerth Shah'

--see the datain both tables

select * fromChangeDataCapture

--incdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.

--2 representINSERT.

select * from cdc.dbo_ChangeDataCapture_CT

--see effect ofUPDATE now.

UPDATEChangeDataCapture

SET Name='Rajan Jain' WHERE Name='Rajan Shah'

--see the datain both tables

select * fromChangeDataCapture

--incdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.

--3 representvalue before UPdate and 4 represent new value after update.

select * from cdc.dbo_ChangeDataCapture_CT

--see effect ofDELETE now

Delete From ChangeDataCapture WHEREID=2

--see the datain both tables

select * fromChangeDataCapture

--incdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.

--1 representDELETE operation

select * from cdc.dbo_ChangeDataCapture_CT

BTW, if you wish to look at the other manual method of capturing table tracking as known as Audit Trail, have a look at following links:

http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html

http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html

http://www.sqlhub.com/2009/11/audit-in-sql-server-2008.html

http://www.sqlhub.com/2009/08/output-clause-in-sql-server-20082005.html

http://www.sqlhub.com/2009/03/keep-log-of-update-in-same-table-with.html

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Find your backup history in SQL Server

2011-07-16T07:58:00.000+05:30

While managing multiple databases, it is often needed tocheck database backup history like when did we taken backup? Where did we takelast few backups? What was the size of last few backup? And many other information.

I have setup maintenance plan for few database, fewdatabases backup taken by third party software and all works on schedule on particulartime even I keep one small T-SQL script handy to check all information I havementioned above.

SELECT

bkSet.Backup_Start_Date as BackupStartDate,

bkSet.Backup_Finish_Date as BackupEndDate,

MedFam.Physical_Device_NameAS BackupPath,

MedSet.Software_NameAS SoftwareUsedForBackup,

bkSet.User_Name ASBackupTakenBy,

bkSet.Server_NameAS ServerName,

bkSet.Database_NameAs DatabaseName,

CASE bkSet.Type

WHEN 'L' THEN 'TransactionLogBackup'

WHEN 'D' THEN 'FullBackup'

WHEN 'F' THEN 'FileBackup'

WHEN 'I' THEN 'DifferentialBackup'

WHEN 'G' THEN 'DifferentialFileBackup'

WHEN 'P' THEN 'PartialBackup'

WHEN 'Q' THEN 'DifferentialPartialBackup'

ELSE NULL END AS BackupType,

CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB

FROM

msdb..BackupMediaFamily MedFam

INNER JOIN

msdb..BackupMediaSet MedSet

MedFam.Media_Set_ID = MedSet.Media_Set_ID

INNER JOIN

msdb..BackupSet bkSet

bkSet.Media_Set_ID = MedSet.Media_Set_ID

WHERE

--keep your database name incondition

bkSet.Database_Name = 'Adventureworks'

AND

--put the date between which you wantto find details of backup

bkSet.Backup_Finish_Date BETWEEN '2011-07-01' AND '2011-07-10'

ORDER BY

bkSet.Backup_Finish_Date DESC

Reference:Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Askme any SQL Server related question at my “ASK Profile”

FileStream in SQL Server 2008+

2011-07-15T12:57:00.003+05:30

One of my friends is working on one project which used tostore images and some document files. He is using BLOB fields to get this taskdone, when he was discussing this with me, I suggested him to use FileStreamfeature instead of the way he is using currently. There are some reasons forthat and I am going to explore it in this article.

In SQL Server 2005, we had two option to deal with binarydata (image, document etc.).

1.) Storeimages/documents in OS’ file system and keep pointer (path) of thatimages/documents into database table

2.) Use BLOB fields to store images/documents directly in database table.

So far we used to take any of the above approaches but bothhave their own limitations. In first approach, transactional consistency is theissue, when you take backup of database, it won’t get backup of the folderswhere we are keeping our files (images/documents). In second approach, you canstore binary data directly in SQL Server database table so whenever you getdatabase backup, it will backed up by its own but it affects performance whileconverting that binary data from database table itself, other than that, BLOB field has limitation of 2GB.

To overcome these limitations, Microsoft developer teamprovided very cool feature, named “Filestream” in SQL Server 2008. With help of“Filestream”, you can store images/documents/videos directly in windows NTFSfile system, it has no limitations of 2GB like BLOB and when you take backup ofdatabase, your Filestream data will be backed up by its own. Apart from that,you can get advantage of NTFS streaming APIs for efficient and performancedriven file operation.

To create Filestream enable column in your table, you haveto have a file group in your database which is enable for “FileStream” and youwill also need one column in your table which should have varbinary(max) data typeso that images/documents/videos could be stored there.

Even before doing any of the above things, you have toenable “Filestream” in your SQL Server which is by default disable.

USE MASTER

--if file streamis not already enable in your server

--look atfollowing link to enable it.

--http://technet.microsoft.com/en-us/library/cc645923.aspx

EXEC sp_configure filestream_access_level, 2

--why I haveused 2? know it from below link.

----http://technet.microsoft.com/en-us/library/cc645956.aspx

RECONFIGURE

CREATE DATABASE SQLHubFileStream ONPRIMARY

(

NAME = SQLHubFileStream_data,

FILENAME = N'D:\TestDB\SQLHubFileStream_data.mdf'

FILEGROUPSQLHubFileStream_FS CONTAINS FILESTREAM

(

NAME =SQLHubFileStream_FILESTREAM,

FILENAME = N'D:\TestDB\SQLHubFileStream_FS'

)

LOG ON

(

NAME = SQLHubFileStream_LOG,

FILENAME = N'D:\TestDB\SQLHubFileStream_log.ldf'

);

USESQLHubFileStream

CREATE TABLE Customers

(

ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,

Name varchar(25),

CustPhoto VARBINARY(MAX) FILESTREAM

)

--I have alreadykept "Ritesh-Teerth.JPG" file in my D drive,

--you have toprovide your own path and file name here.

INSERT INTO Customers

VALUES

(NEWID(),'Ritesh Shah',(select * FROM OPENROWSET(BULK 'D:\Ritesh-Teerth.JPG', SINGLE_BLOB) AS img))

select * from Customers

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Get your server hardware information via DM_OS_SYS_INFO in SQL Server 2005/2008/Denali

2011-07-13T08:12:00.004+05:30

DMV(Dynamic Management View) is very powerful tool given my Microsoft in SQLServer 2005+ version; you can get much crucial information from it. Today I amgoing to show you “sys.dm_os_sys_info” which will reveal some of the hardwareinformation of the server which you are using for your SQL Server instance.

SELECT

cpu_count asTotal_Logical_CPU_In_Server

,HyperThread_Ratio

,cpu_count/HyperThread_Ratio asphysical_CPU_In_Server

--converting bytes of physical memoryto MB

,(physical_memory_in_bytes/1024)/1024 as Total_Memory_Of_Server_In_MB

,(Virtual_memory_in_bytes/1024)/1024 as Current_Usage_Of_Memory_SQLServer_In_MB

--following fields will work only inSQL Server 2008+

--if you are using SQL Server 2005,comment following field.

,sqlserver_start_time

FROM

sys.dm_os_sys_info

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Know your processor by XP_Instance_RegRead SP in SQL Server 2005/2008

2011-07-11T07:30:00.005+05:30

XP_Instance_RegRead could be quite a useful extended storedprocedure which ships with SQL Server 2005/2008.

Sometime it happens that you don’t know which processoryour server is using and you want to know it; however, you don’t have physicalaccess of the server than you can use following T-SQL to know thoseinformation.

EXEC master.dbo.xp_instance_regread

'HKEY_LOCAL_MACHINE',

'HARDWARE\DESCRIPTION\System\CentralProcessor\0',

'ProcessorNameString';

BTW, I have already written two articles before too with useof this kind of extended stored procedure.

“XP_Instance_RegRead”

“XP_RegRead”

Please note that if the user you are using to access SQLServer, wouldn’t have permission in OS to read registry, you will face an errorrather than result.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Take mirror backup of database in SQL Server 2005/2008/Denali

2011-07-09T07:41:00.002+05:30

Backup is one of the most important tasks of DBA. Withouthaving proper backup, you are running in danger mode always so it is good tokeep more than backup file with you always so in any emergency if one backupgets corrupt or missing, you can use other one.

In early days of SQL Server, especially before SQL Server2005, DBA used to take full backup of database and copy it somewhere else viaDOS command XCOPY to make another copy of the same to other disk drive orsomewhere in network but SQL Server 2005+ provides the facility to take backupwith mirror copy so you don’t need to put additional efforts to make copy ofyour database backup and move it somewhere else.

Suppose my database name “SQLHub” so I would use following commandto take copy of that database in D drive and mirror copy in E drive.

--database backup of SQLHub

--full back in Ddrive SQLHubBackup folder

--same backupcopy will be copied at E drive SQLHubBackup folder

BACKUP DATABASE SQLHub

TO DISK= 'D:\SQLHubBackup\SQLHub.bak'

MIRROR

TO DISK= 'E:\SQLHubBackup\SQLHub.bak'

WITH FORMAT

“With Format” option in backup is not mandatory. If we takebackup of database with backup file name SQLHub.bak on 8^th July 2011,when we again run the same database backup with same backup file name “SQLHub.bak”on 9^th July 2011, SQL Server keeps previous backup of database inSQLHub.bak file and copy the next so your both backup taken on 8^thand 9^th july 2011 will now be there in SQLHub.bak file, if you have NOT used “WITH FORMAT” option.

If you want to take backup on 9^th July 2011 and wantto remove database back taken on 8^th July 2011, which is there withthe same backup file name “SQLHub.bak”, you have to use “WITH FORMAT” clause.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

SP_RefreshSqlModule in SQL Server 2005/2008

2011-07-08T07:05:00.001+05:30

According to the MSDN, SP_RefreshSQLModule:

Updates the metadata for the specified non schema-boundstored procedure, user-defined function, or view. Persistent metadata for theseobjects, such as data types of parameters, can become outdated because ofchanges to their underlying objects.

Rather than going into detailed theory session, let me giveyou one example to make it clear in your mind.

Suppose you have one table, named “baseTable” and you createone SP, ”usp_getBaseTableData” , which used to return all records of “baseTable”table. ”usp_getBaseTableData” is nowdepend on ”BaseTable”. SQL Server used to store this relational information in “sysdepends”. In “sysdepends”,information gets stored with Object_ID rather than Object_Name so if you dropyour table “baseTable” and check the dependency of ”usp_getBaseTableData”, youwon’t get anything but the message like this:

“Object doesnot reference any object, and no objects reference it.”

Obviously you will not get any reference as you have alreadydropped the table, now create the table with same name and structure and afterthat run SP_Dependsto check dependency and you will again get the same message, though you havecreated table and the same table name is being reference in SP. So if you willrun your SP, it will work fine now as it will find the object but SP_Depends won’t be able to find new object as new “baseTable”would have different Object_ID than older one.

Surprised!!!!!.... No, you shouldn’t if you have tried thisbefore….

Now, to make reference of new “BaseTable” with ”usp_getBaseTableData”,you have to drop SP and recreate again. But this shouldn’t be the practicalsolution and that is why you have “SP_RefreshSQLModule” to refresh nonschema-bound SPs or functions or views. Let us see how it works.

--create onetable

create table baseTable

(

ID INTIDENTITY(1,1)

)

--create one SPto return data of baseTable

create proc usp_getbaseTableData

select * from baseTable

--check whetherSP_Depends returns any data or not

--I am sure, itwill return :)

sp_dependsusp_getbaseTableData

--now drop thebasetable

drop table baseTable

--now if you trysp_depends, it will show you below message as you don't "BaseTable"

--Object doesnot reference any object, and no objects reference it.

sp_dependsusp_getbaseTableData

--now createbase table again with same structure

create table baseTable

(

ID INTIDENTITY(1,1)

)

--use the samecommand, it will again show you same message.

--though youhave now table, if you will run your SP, it will work

--but sp_dependswill not show you proper information

sp_dependsusp_getbaseTableData

--now you havetwo ways

--1.) drop andre-create SP

--2.) usesp_refreshsqlmodule.

EXEC sp_refreshsqlmodule 'usp_getbaseTableData'

--now you willagain get proper information

--after takingany of the previous suggestion in comment

sp_dependsusp_getbaseTableData

BTW, personally I prefer “information_schema.routines”then “SP_Depends”to get dependency of object.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

What version to go for (updgrade) after SQL Server 2000???

2011-07-07T07:26:00.000+05:30

I have developed one project very long back in .NETFramework 2.0 and SQL Server 2000 for one doctor in USA. He is still using thatsoftware but since than many rules gets changed in USA so he come back to mewith those changes and also told me that I want to use latest technology for myproject and database.

He might have searched internet for getting knowledge aboutthe latest version of .NET and SQL Server. Talking about SQL Server, he told methat the latest version of SQL Server is “Denali” and I want to use it in myproject. While talking about this, I made him understand that we should go forSQL Server 2008 R2 rather than “Denali” by giving some very strong and validreason and one of that strong reason is “Denali” is not in production, it is inCTP (Community Technology Preview).

But after this discussion, I felt to share some of mypersonal opinions on this blog.

After SQL Server 2000 version, you have three choices forupgrade.

1.) SQLServer 2005

2.) SQLServer 2008

3.) SQLServer 2008 R2

I am excluding “SQL Server Denali” from this as it is stillCTP version, not good to choose it for production as of now though it has eyecatchy and flashy features which attract you.

Out of these three versions, I would, personally, like to gofor “SQL Server 2008 R2”. Let me tell you why I am thinking this.

SQL Server 2005 is not a good deal at this time, though it has upgradedengine and many new features as compared with SQL Server 2000, SQL Server 2005is now in “ExtendedSupport”. It means you will neither get any “No-Charge” support nor claimany warranty. Soon after release of “Denali”, Microsoft may end even “ExtendedSupport” for SQL Server 2005 (may be in 2014/2015).

While compare SQL Server 2008 with SQL Server 2008 R2, R2gives much more facility than SQL Server 2008, mainly multiserver management,greater security, self service BI and many more.

Apart from that, SQL Server 2008 was released in 2008 andSQL Server 2008 R2 is released in 2010 so you will get longer support in “SQLServer 2008 R2” then “SQL Server 2008”, means better reward of your money.

Generally in any SQL Server, you get support for yourversion for minimum 10 years. Suppose SQL Server 2008 R2 releases in 2010, Microsoftwill definitely give you support for SQL Server 2008 R2 till 2020. For detailedunderstanding of support life cycle, refer Microsoft website from here.You have to go to “SUPPORT LIFECYCLE” tab in that link.

This is what I am thinking; this article is totally based onmy personal view. You have to decide the proper version by looking at your need;this article is just the guideline to make you aware with some of the factswhich may help you in your decision.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”