Tag Archives: MS SQL Server 2005

Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Use Uniqueidentifier in dynamic SQL in SQL Server 2008/2005

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

List column names along with Table and Schema name in SQL Server 2008/2005

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

SQL Server profiler and Database Engine tuning adviser in SQL Server 2005

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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find SQL Server error log file

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Rename column name in SQL Server 2008/2005

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Copy LDF and MDF file without stopping SQL Server services:

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

TechEd on Road at Ahmedabad on June 20, 2009 Saturday

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
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find first and last Friday of every month in SQL Server 2008/2005

Today, I helped someone in one of the forum about above question. He wanted to find first and last Friday of every month from 1st Jan 2006 to 31st 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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Read XML node on same level inSQL Server 2008/2005

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 =
  
    ch001
    Ritesh
    Shah
    MIS
    Network
    DBA
 
  
    ch002
    Rushik
    Shah
    Programmer
    Developer
 
–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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah