It is often useful to keep an eye on the size of your database, indexes always plays an important role in SQL Server databases so it is really good to keep an eye on that as well. We used to do rebuild, defrag indexes sooner or later but have you ever think about how much space those indexes consumes? Let us try to find out with “SysIndexes”.

If you observe SysIndexes table than you came to know that it has lots of crucial information about clustered and non-clustered indexes. How can you differentiate clustered and non-clustered indexes from “SysIndexes” ? well, it is really very easy, Clustered index always have “1” in IndID field and non-clustered index always have >1 in IndID field.

You might even know that data used to get stored in page of 8KB in SQL Server so we can calculate those pages of 8KB (8192 byte) to get values in MB.

Now let us have a look at simple yet useful T-SQL statement.

--for non-clustered index

select sum(cast(reserved as bigint))*8192/(1024*1024) AS TotalMegabytes_Non_Clust

from sysindexes

where indid > 1;

go

--for clustered index

select sum(cast(reserved as bigint))*8192/(1024*1024) AS TotalMegabytes_Clust

from sysindexes

where indid = 1;

go

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

Error Fix:

Msg 402, Level 16, State 1, Procedure SPIDTester2, Line 6

The data types varchar and uniqueidentifier are incompatible in the add operator.

 

We used to use uniqueidentifier so many times in our database, since it is one of the unique field in table, we may need to put it in dynamic SQL quite a few times but I have seen so many times that even seasoned developer don’t know how to use UniqueIdentifier in dynamic SQL, may be in Store procedure in SQL Server. This is the reason I tempted to write something for this topic.

Let us see it practically:

--create table for testing

if OBJECT_ID('IDTester') is not null drop table IDTester

create table IDTester

(

      ID uniqueidentifier default newid(),

      name varchar(20)

)

GO

 

--insert few records

insert into IDTester(name)

select 'Ritesh' union all

select 'Rajan' union all

select 'Bihag' union all

select 'Abhijit'

GO

 

--let us see what we come up with

select * from IDTester

GO

 

--create simple SP

Create proc SPIDTester

@ID uniqueidentifier

as

begin

      select * from IDTester where ID=@ID

end

GO

 

----I got '7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5' for first record

----you may get something else as a ID of first records.

exec spidtester '7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5'

GO

 

--let us create another SP with dynamic SQL but it will show us an error

Create proc SPIDTester2

@ID uniqueidentifier

as

begin

      declare @sql varchar(max)

      set @sql='select * from IDTester where ID=' + @ID

      exec (@sql)

end

GO

--if you will try to create above SP, you will be greeted with

--following error

--Msg 402, Level 16, State 1, Procedure SPIDTester2, Line 6

--The data types varchar and uniqueidentifier are incompatible in the add operator.

 

 

--you have to use sp_executeSQL to get rid of above error

--with additional parameter

create proc SPIDTester2

@ID uniqueidentifier

as

begin

      declare @sql nvarchar(max)

      set @sql='select * from IDTester where ID=@I'

      exec sp_executesql @sql,N'@I uniqueidentifier',@I=@ID

end

GO

 

--let us see whether SP actually works

exec spidtester2 '7F1D8BC8-48AA-437E-B19F-4ABD139AD5E5'

GO

 

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

If you are new to SQL Server 2008, if you want to learn new features of SQL Server 2008, if you are well aware with SQL Server so don’t want to learn how SELECT works and how INSERT works but just wanted to get good knowledge of SQL Server 2008’s features than who else can you give proper guidance other than Microsoft. I found one small book about SQL Server 2008 from Microsoft press itself, written by Peter DeBetta, Greg Low, Mark Whitehorn.

Not only this is a good material with good amount of interesting topic, it is absolutely free and you can
download it from following URL:

http://csna01.libredigital.com/?urss1q2we6

You need to register there before downloading it; this would really be a good starter for new joiner of SQL Server 2008.

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

Sometime we need to find the list of columns for all table, some time column list of one table and may be one column name available in how many tables? In short finding table, column, schema of table and default value of column related information is much easier with INFORMATION_SCHEMA.Tables. Have a look at one of the short yet very handy and useful script given below.

USE AdventureWorks

GO

--list all tableName with schema, columnName and Default value of column

select TABLE_CATALOG as 'DB', TABLE_SCHEMA as 'schema', TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT

  from INFORMATION_SCHEMA.COLUMNS

 where TABLE_NAME in (select Table_name from INFORMATION_SCHEMA.tables where table_type='Base Table')

GO

--list all tableName with schema, columnName and Default value of column based on filter provided

--for columnName

select TABLE_CATALOG as 'DB', TABLE_SCHEMA as 'schema', TABLE_NAME,COLUMN_NAME,COLUMN_DEFAULT

  from INFORMATION_SCHEMA.COLUMNS

 where TABLE_NAME in (select Table_name from INFORMATION_SCHEMA.tables where table_type='Base Table')

and column_name='ProductID'

GO

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

I am very happy and proud to be the part of TechED event held in Ahmedabad, Gujarat –India. I was disappointed when I was not able to go to TechED event held in Hydrabad – India but this Ahmedabad event remove my all disappointment.

Event was really overwhelmed by so many tech people not only from Ahmedabad city, not only from Gujarat state but people came over from different cities and states as well. It was really pleasure to meet so many technical people under one roof.

I was eager to meet both the SQL Server MVPs Mr. Pinal Dave (SQLAuthority.com) and Mr. Jacob Sebastian (BeyondRelational.com ) . Do you know what my bonus was? I met two industry leader Pinal and Jacob along with .NET MVP Kaushal Parikh. Isn’t is great to have three MVPs under one event in Ahmedabad, Gujarat? Yes, it is.

As long as technical session concern, I liked the speech of both the great speaker Pinal and Jacob and the way they have presented their topic with so much fun yet with great information. I have attended Pinal’s seminar in past but I attended Jacob’s speech first time ever in my life and I am really very impressed with his knowledge as a technocrat and very simple and kind hearted person as a human being. As long as Pinal concern, he is one of the great talented person and very co-operative by nature that I have felt so many time.

SQL Server cheet sheet was one of the attractions there along with the Query Flow chart. We can download cheet sheet from pinal’s personal site pinalDave.com but I guess there is no soft copy available for Query Flow chart. I would like to request Jacob and Pinal both to please upload soft copy of the same so whoever was not able to come to event, can have that amazing chart with them.

Now, let me tell you something about arrangement, this is also something I can’t stop myself to write about. It was really so impressive, once again Pinal and Jacob ran out of chair due to great response from the people and it has been arranged very quickly.  Official starting time of event was 13:30, I reach there by 13:20 and you know what, I get chair in last row, people came there by 12:00PM to reserve chair, isn’t it HISTORICAL moment in history of Ahmedabad regarding any technical seminar? I guess it is.

Moreover, there were so many gifts given by organizer, I personally got four gifts for answering questions there.

In short, I really want to thanks everybody who has taken initiative to make this event successful and hope to see this kind of events again and again.

I don’t have much photograph so far but have only one right now when Pinal called me with him to give me one of the gifts. Have a look.

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

SQL Server 2005 came up with so many tools and ways to keep watch on the performance of your databases. Today I am going to introduce you one interesting tool of SQL Server 2005 which is SQL Server Profiler with which you can create a trace file or table to see what is going in your server at specific time, later on you can use that trace file/table in your Database engine tuning adviser to get help from SQL Server itself to know what area you need to improve.

Let us follow the simple steps given below and check database whether anything is going wrong.

1)  Open SQL Server Profiler from Start-> All Programs->Microsoft SQL Server 2005-> Performance Tools->SQL Server Profiler

2.) Click on File->New Trace

3.)It will open Profiler windows where you have to give your SQL Server credential

4.) Give the Trace Name

5.) Select the template, there are many types of templates available, you have to choose right one for you.

6.) Since we can save trace into file/table, we will choose check box of “Save to File” give the name and path of the file, I have save the file to C drive with TestTrace name. have a look at below screen shot.

7.) Go to “Events Selection” tab from where you can select/deselect events and column, I kept everything as it is.

Have a look at it in screen shot below.

8.) click on “Run” button and it will start capturing data, please be informed that, tracing will make your server’s performance bit slow so use this whenever you really wants to do some analysis, don’t keep it running 24*7

9.) When you captured your desired data, stop tracing with red square button seen under “Windows” menu.

10.) Now open your “Database Engine tuning adviser” to get suggestion about how you can improve your T-SQL performance, observe screen capture given below.

11.)  Give Name to your trace under “Session Name” and select “File” radio option as we have kept tracing in our file, don’t forget to give file name and path there.  Since trace is server level operation, select database for “Workload analysis” and select database for tune. 

12.) Once you select everything, click on “Start Analysis” button under “View” menu and you will see SQL Database Engine tuning adviser” to make some process, as soon as that process gets finish, you can see two new tab in right hand side with the name “Recommendations” and “reports”. Under “Recommendation” tab, you will see what table of your selected database needs what, Indes, state update etc. you will not only see recommendation but you will see “Defination” of those recommendation as well and estimated improvement after doing those changes. Have a look at below screen 

Happy Observing!!!!

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

Whenever you sit to troubleshoot any problem, you always need to look at error log file. There are quite a few different way to find out where error log of SQL Server resides but there is one of the simplest and easy way I used to use always is undocumented method serverproperty(‘errorlogfilename’)

All you need to run is, just following one statement and you will find full path of ErrorLogFile, which you can open in standard text editor.

SELECT SERVERPROPERTY('ErrorLogFileName')

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

Recently I have been asked that we can change the name of column from GUI easily but how can we do it from T-SQL. Well, this is very small thing but when I have been asked, it strikes in my mind that quite a few people doesn’t know this actually so I decided to write this very small script. This task is easiest stuff in T-SQL with the help of sp_rename system stored procedure

Have a look.

--Emps is a tablename

--Name is current column name in emp table

--want to give new name "Name11" to old name "Name" of emps table

--"Column" at last, is a key word which tells SQL Server that we

--want to change column name

EXEC sp_rename 'emps.[name]', 'Name11', 'COLUMN'

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

Today I have been asked by someone that I am not able to copy MDF and LDF file from our production server.  Whenever I want to copy it, I have to stop SQL Service. This is very common problem and not everybody aware with why this happens? So I tempted to write something small about this.

If you are using your database, you are not able to copy the data or log files; you can do it without even stopping services of SQL with following small script.

 

ALTER DATABASE YourDatabase

SET OFFLINE WITH ROLLBACK IMMEDIATE;

--Now Manually copy your file(s) to anywhere you want

--and set your database online

--during this process your database will be offline

--It's not good but better than stoping SQL Server services

ALTER DATABASE YourDatabase

SET ONLINE;

Actually take a backup of database and move backup file anywhere could be good but even if you wish to copy MDF or/and LDF file, you can have above approach.

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

I was very disappointed when I was not able to attain TechEd event at Hydrabad last time, fortunately One of the good gala event is going to happen in Ahemedabad, heart of Gujarat. Many technology leaders is going to come from all over Gujarat so it would be great opportunity to meet them in-person. I am really eager to meet both the SQL Server MVPs Mr. Pinal Dave (SQLAuthority.com) and Mr. Jacob Sebastian (BeyondRelational.com). 

Here is the highlight of that event.

Date and Time:
June 20, 2009 Saturday 1:45 PM

Agenda:
1:45 - Keynote By Pinal Dave & Jacob Sebastian
2:00 - Microsoft Exchange Server 2010,Windows Server 2008,Virtualization
2:45 - SQL Server Best Practices by Pinal Dave
3:30 - High Tea, Networking Opportunity and Gift Giveaway
4:00 - Understanding Query Execution Flow and Optimization Tips by Jacob Sebastian
4:45 - Data compression in SQL Server by Pinal Dave and Jacob Sebastian

Location:
Rock Regency
C.G. Road, Lal Bunglow,
Ahmedabad

If you have missed TechEd India 2009 at Hyderabad you can now attend the same kind of event inAhmedabad, India on June 20, 2009 Saturday. TechEd on Road is organized in Ahmedabad at Rock Regency a prime location in middle of the city. This event is going to be largest event happening with the brand TechEd in Ahmedabad. If you are technology enthusiast and interested in meeting great people from all over state, this is one event marked for you.

Do show up little early at event around 1:30 PM, if you want good seats and want to meet two MVPs (Pinal Dave and Jacob Sebastian), who will be attending this event. Entry is first come and first serve. Arrive early enough to secure your entry to this FREE event.

Please refer the flayer below for agenda, map of venue, sponsors and giveaway. Yes, there will be lots of gifts and giveaways for everybody.  You can participate in this gala event in many different ways – attendee, volunteers, sponsors. Feel free to send email to pinal ‘at ‘ sqlauthority.com if there are any question regarding this event.

You can find more details of the event at

http://techedahmedabad.eventbrite.com/

Hope to see you there!!!!

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

Today, I helped someone in one of the forum about above question. He wanted to find first and last Friday of every month from 1^st Jan 2006 to 31^st Dec 2020. This is bit a challenging job. There are quite a few ways to do so like temp table, CTE etc. and I have had my hand in CTE to solve this problem. Let us see the script itself.

WITH CTE AS

(

select CONVERT(datetime,'01/06/2006') as dt,  datename(dw,CONVERT(datetime,'01/06/2006')) as dy,datename(mm,CONVERT(datetime,'01/06/2006')) as mn, YEAR(CONVERT(datetime,'01/06/2006')) AS yr

union all

select s.dt+7 as dts,datename(dw,dt+7) as dy  ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr

from cte s

where s.dt<CONVERT(datetime,'12/31/2020')

)

select MAX(dt) as dt,dy,mn,yr from cte

group by dy,mn,yr

UNION

select MIN(dt) as dt,dy,mn,yr from cte

group by dy,mn,yr

ORDER BY dt

Quite a simple to do so in CTE but if you will try to attempt above query, you will be greeted by following error.

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

How to fix that annoying error? It is crossing default recursion limit of SQL Server which is 100 and above CTE will iterate much more time than 100 so obviously you are going to face an error. All you need to do is, just have one clause after above CTE, OPTION (MaxRecursion 0).  So finally query will looks like

WITH CTE AS

(

select CONVERT(datetime,'01/06/2006') as dt,  datename(dw,CONVERT(datetime,'01/06/2006')) as dy,datename(mm,CONVERT(datetime,'01/06/2006')) as mn, YEAR(CONVERT(datetime,'01/06/2006')) AS yr

union all

select s.dt+7 as dts,datename(dw,dt+7) as dy  ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr

from cte s

where s.dt<CONVERT(datetime,'12/31/2020')

)

select MAX(dt) as dt,dy,mn,yr from cte

group by dy,mn,yr

UNION

select MIN(dt) as dt,dy,mn,yr from cte

group by dy,mn,yr

ORDER BY dt

OPTION (MaxRecursion 0)

Enjoy!!!

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

I have written one article few days back for loading relational data in SQL Server from XML file. I got very good response from that article so I am tempted write another one on XML. Suppose you have different occurrences of data within same level of XML file, how would you query or populate in SQL Server? Let us have a look at it.

--create one variable with XML data

--you can have .XML file saved on your disk

--and can use the same query

DECLARE @xData xml;

SET @xData = '

<chem>

   <employee>

    <id>ch001</id>

    <FName>Ritesh</FName>

    <LName>Shah</LName>

    <dept>MIS</dept>

    <dept>Network</dept>

    <dept>DBA</dept>

 </employee>

   <employee>

    <id>ch002</id>

    <FName>Rushik</FName>

    <LName>Shah</LName>

    <dept>Programmer</dept>

    <dept>Developer</dept>

 </employee>

</chem>'

--suppose employee work in more than one department

--how would you list employee with all concern department?

--ofcourse with below give query ;)

SELECT outr.ID,outr.FName, outrmost.y.value('text()[1]','varchar(200)') AS imageUrl

FROM

(

        SELECT

            Inr.i.value('id[1]','varchar(20)') AS ID,

            Inr.i.value('FName[1]','varchar(20)') + ' ' + Inr.i.value('LName[1]','varchar(20)') AS FName,

            Inr.i.query('.') AS employee_desc

        FROM @xData.nodes('/chem/employee') AS Inr(i)

) AS outr

CROSS APPLY outr.employee_desc.nodes('//dept') AS outrmost(y)

--or you can just list of department based on ID

SELECT outrmost.y.value('text()[1]','varchar(200)') AS imageUrl

FROM

(

        SELECT

            Inr.i.value('id[1]','varchar(20)') AS ID,

            --Inr.i.value('FName[1]','varchar(20)') + ' ' + Inr.i.value('LName[1]','varchar(20)') AS FName,

            Inr.i.query('.') AS employee_desc

        FROM @xData.nodes('/chem/employee') AS Inr(i)

) AS outr

CROSS APPLY outr.employee_desc.nodes('//dept') AS outrmost(y)

where outr.ID='ch001'

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