Tag Archives: T-sql

Became “SAGE” in Experts-Exchange.com

Saturday 11th July 2009 was really memorable day for me. I had an achievement in my personal as well as professional life.

I got married on 11th July 2008 and last Saturday I have completed one year with my beautiful wife who used to encourage me all the time to help community more and more.

One more achievement was that, I became “SAGE” in MS SQL SERVER zone in Experts-Exchange.com. “SAGE” is really one of the prestigious awards and I am proudly say that I have achieved it in just 3 months and 10 days.

I have joined Experts-Exchange.com on 26th March 09 but actively started participating by 1st April 2009 with 0 points. Right now I am about to complete 800,000 points in Experts-Exchange.com. Out of which I have more than 500,000+ points in MS SQL SERVER zone because of which I have been awarded with “SAGE” certificate in MS SQL Server zone. Apart from “SAGE” certificates, I have achieved few more certificates in Experts-Exchange.com. You can have a look at my profile in EE at:

http://www.experts-exchange.com/M_4847866.html

In this wonderful moment I would like to thanks to my wife, parents and few of my friends in technical fields (Pinal Dave and Bihag Thaker) who always encourage me to achieve more and more, help community more and more. Apart from all these person, how can I forget my blog reader? You are really wonderful people and always send me suggestions for improvement. BIG THANK TO YOU ALL.

Hope to have co-operation from all of you and blessings as well in future and I promise that I will do my best to help community.

Thank you very much once again.

 

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 ofhttp://www.SQLHub.com

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

Find stored procedure in your database with T-SQL in SQL Server 2005/2008

I have observed quite a few times in different forum, people keep asking question that I have created stored procedure in my database but I can’t find it. Well there are quite a few reason for that, either you have created your stored procedure in different database and you are trying to run in different database or you have not specified schema name for your stored procedure so default schema of your login will be automatically applied to it, while running SP, you don’t specify that schema name and you will face an error like:
“Could not find stored procedure ‘Your SP name’”
Well, to ensure that you stored procedure is created and it is exists in particular database, I used to run following very simple query to confirm.

select ss.name + ‘.’ + sp.name as ‘Available Stored Procedure’ from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id



Above query will show you all the available stored procedure along with its schema name available in your database.
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

Keep watch on your database file size in SQL Server 2008/2005

Keep watch on your database file size in SQL Server 2008/2005

Well, in my previous article, I saw the method of how to keep watch on your disk drive (Click here to view), how to get information about your data and log file size as those are the very important task of DBA. Today, I am going to show one more important job which DBA simply can’t ignore. DBA needs to keep watch on every data and log file of databases server wide.  If it reaches at certain limits, DBA should be notified by email immediately so that s/he can take any action regarding this. Let me show you one very simple script which can make this task very easy for DBA.

Here is the script with Cursor:

 

DECLARE @DiskSpace bigint
DECLARE @DataBaseName VARCHAR(50)
DECLARE @Name VARCHAR(50)
DECLARE @DriveLetter CHAR(1)
DECLARE @DiskSize bigint
Declare @fileName varchar(max)
SET   @DiskSpace = 1
DECLARE DriveSpace CURSOR FAST_FORWARD FOR

SELECT
databasename=DB_NAME(dbid),
name,
filename,
drive=LEFT(filename,1),
[size]=convert(bigint,[size])*8/1024
FROM
sysaltfiles
ORDER BY
dbid

OPEN
DriveSpace
FETCH NEXT FROM
DriveSpace
INTO
@DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize

WHILE
(@@FETCH_STATUS = 0)
BEGIN
IF
@DiskSize > @DiskSpace
BEGIN
DECLARE @mes VARCHAR(MAX)
SET
@mes='Hi Ritesh, ' + @Name + ' file of ' + @DataBaseName + ' database exceeds 1000 MB limit, current size is ' + cast(@DiskSize as varchar(10)) + ' and file name is ' + @fileName
EXEC
msdb..sp_send_dbmail
@recipients = 'Rits4Friends@gmail.com',
@subject= 'Drive is about to full',
@body= @mes
End

FETCH NEXT FROM
DriveSpace
INTO
@DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize
End

CLOSE DriveSpace
DEALLOCATE DriveSpace
GO
All you need to do is, keep this script in JOBS and run that job once or twice everyday. If you don’t have database email configured than you could check this by remove comment before “Print” statement in script, don’t forget to comment email sending code, if you don’t have email configured in database.
 

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Convert local DateTime to UTC DateTime in SQL Server 200/2005

I have seen one question in many forums about asking how to convert local DateTime to UTC DateTime? This is really very simple task to done.  All you need to do is, get difference between your local datetime and UTC datetime. GetDate() function will you, your local DateTime and for UTC DateTime, you can use getUTCdate() function. Simply find the difference between these two DateTime in Minutes or in Hours and Add/Deduct that Minutes/Hours from your local datetime and you are done. Let us see how easily we can do it

 

select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),GETDATE()) as ‘UTC DateTime’, GETDATE() as ‘Local DateTime’

–or you can pass your own dateTime field or variable like this.

declare @dt datetime

set @dt=‘9/22/2009 1:25:00 PM’

select DATEADD(hh,datediff(hh,GETDATE(),getUTCdate()),@dt) as ‘UTC DateTime’

 

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

Watch your file size of databases in SQL Server 2008/2005

Keeping watch on file size of databases is one of the important task for DBA. I am using one small yet very useful script since very long time for this task which I wanted to share with all of my blog reader. I don’t exactly remember whether I have developed it or found it from internet somewhere. I tried to find this script by GOOGLE but didn’t find it anywhere so I forced to believe that I might have developed it. If anybody knows where does it located than do let me know so that I can give due credit.

 

select
      databasename=DB_NAME(dbid),
      name,
      fileid,
      drive=LEFT(filename,1),
      filename,
      filegroup=FILEGROUP_NAME(groupid),
      [size]=CONVERT(nvarchar(15),convert(bigint,[size])*8)+N’KB’,
      [maxsize]=(case maxsize when 1 then N’Unlimited’ else CONVERT(nvarchar(15),convert(bigint,maxsize)*8)+N’ KB’ END),
      [growth]=(case status & 0x100000 when 0x100000 then convert(nvarchar(15),growth)+N’%’ else convert(nvarchar(15),convert(bigint,growth)*8)+ N’ KB’ end),
      [usage]=(case status & 0x40 when 0x40 then ‘log only’ else ‘data only’ end)
 from  sysaltfiles
order by dbid


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

Single User , Restricted User and Multi_user database mode in SQL Server 2008/2005

We may need to put our database in single_user mode and restricted_user mode sometime. I have observed that many of the developers are not aware with the differences between these two modes. It becomes very sensitive to choose right mode in certain situation. Let us see what it is.
Single_User Mode:
— does not keep database open only for DBO, first come and first served basis.
— Database can only be accessed by one user and that user can be any one.
Restricted_User Mode:
— restrict database for DBO user, any user can login to database as long as they have permission of db_owner, db_creator and sysadmin
Multi_User Mode:
— Default mode of database, this is normal behavior and you should put your database to Multi_User mode as long as you finish your task with Single_User/Restricted_User.
Let us see small code snippet about how we can set database to different modes.
use master
go
ALTER DATABASE adventureworks
SET single_user WITH ROLLBACK IMMEDIATE;
go
ALTER DATABASE adventureworks
SET restricted_user WITH ROLLBACK IMMEDIATE;
go
ALTER DATABASE adventureworks
SET multi_user WITH ROLLBACK IMMEDIATE;
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

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

SQLHub News – Get Free Microsoft SQL Server 2008 eBook from Microsoft Press

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:


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
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