Tag Archives: T-sql

Find out column name, data type and maximum number of character in that column in SQL Server 2008

Yesterday I got one question about how to find column name, data_type and maximum number of character used in that column if it is varchar and if it is numeric than maximum number in that column. Basically you can find column_name and Data_type from the Information_Schema.Columns but it won’t return length of real data inserted into table so I tried to achieve it logically. Look at the script.
–using AdventureWorks database for demo
–if you don’t have AdventureWorks db
–you can use your own
use adventureworks
GO
declare @sql varchar(max), @table sysname
–going to show you demo on Person.Contact table
–in adventureworks database,
–you can change your own table name
select @table=‘Contact’, @sql=
select @sql=
case when character_maximum_length is null then
@sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(‘+column_name+‘) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
else
    @sql+‘select
    ”’+column_name+”’ as column_name,
    ”’+data_type+”’ as data_type,
    max(len(‘+column_name+‘)) as column_length
    from ‘+Table_Schema + ‘.’+ Table_Name+‘ union all ‘
end
from information_schema.columns
where table_name=+@table+ and  data_type not in (‘xml’,‘bit’,‘datetime’,‘uniqueidentifier’)
set @sql=left(@sql,len(@sql)-9)
–print @sql
exec(@sql)
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

Row count in big tables with DMV DM_DB_Partition_Stats and SP_SpaceUsed

I have seen people counting row by using COUNT or may be COUNT_BIG, if it is very big table, aggregate function. When you go for COUNT function, you will scan full table and it may take very big amount of time, especially when you are having VERY BIG table with few millions of rows. So .NET developer also uses same COUNT aggregate function in .NET front end application which is really very time consuming.
I just see one of my .NET developers was doing this for the table, having 150 million rows so I corrected him with following two ways and thought to share it with my blog readers.
Rather than scanning full table I always prefer to use either DMV or SP_SpaceUsed stored procedure. In my early days few years back, I was using SP_MSTABLESPACE but in SQL Server 2005+, SP_MSTABLESPACE is just for backward compatibility so I do not recommend using SP_MSTABLESPACE and promoting dm_db_partition_stats and SP_Spaceused.

Let us see  TSQL for each.

–Method1
SELECT
SUM(row_count) as TotRows
FROM
sys.dm_db_partition_stats
WHERE
object_name(object_id) = ‘YourTableName’
AND (Index_Id = 1)
GO
–Method2
SP_Spaceused ‘YourTableName’
GO
–Method3
SP_MSTABLESPACE ‘YourTableName’
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

Read registry from SQL Server with xp_instance_regread and find the path of your SQL Server installation

Some machines are really mess up with couple of successful and unsuccessful installation/un-installation of SQL Server and the result is you can find program files/Microsoft SQL Server folder in couple of drive. If you want to find current running installation of SQL Server than there are few different ways. Out of which I am going to show you couple of different approaches.
Method 1:
You can query sysaltfiles system catalog resides in MASTER database. You can see the path of system databases especially MASTER database. Most probably master database would be in the same folder of SQL Server.
Here is the TSQL:
select * from master..sysaltfiles
Method 2:
There is a system stored procedure in Master database, named “xp_instance_regread” which can help you to read registry.  There is a specific location in system registry which will give you path of the installation directory. This method is more appropriate than previous one. Here the code snippet of the same.
declare @regreader int, @directory nvarchar(4000)
exec @regreader = master.dbo.xp_instance_regread
     N’HKEY_LOCAL_MACHINE’,
     N’Software\Microsoft\MSSQLServer\Setup’,
     N’SQLPath’,
      @directory output, ‘no_output’
select @directory AS InstallationDirectory
GO
If the user you are using while running above snippet doesn’t have permission to read registry, you might greeted with permission error.
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

INDEX and SELECT * query in SQL Server 2008

“SELECT *” in T-SQL select query affect performance of the query adversely, there are many reasons about why we shouldn’t use “SELECT *” query but today I am going to share one of my observation about how index works with “SELECT *” query. In short, if you really don’t want ALL FIELDS in result sets, don’t use “*” as it increase IO load, network traffic. One more reason is, it is not readable that which columns you are going to use so better to use Field Name rather “*”.
There are two types of main index 1.) Clustered Index 2.) non-Clustered Index. What are these indexes and how internally it works, is something out of the scope of this article but I would like to clear one point that there are two possibilities about index when you run SELECT query. Either it will “scan” index or it will “seek” index.  Generally for big tables “SEEK” is good than “SCAN” as “SCAN” touches every row of the table. For more details on SEEK and SCAN, please read my friend and guru Pinal Dave’s article.
Actually I and my friend Bihag were discussing about Indexes, SEEK and SCAN. While our discussion, I felt to write this article.
Let us look small example to see in which situation we will get SCAN and in which situation we will get SEEK. Remember, we should try to achieve SEEK rather SCAN.
–create table for demo
create table ClusteredIndexTest
(
      ID INT identity(1,1),
    FirstName varchar(10),
    LastName varchar(10)
)
GO
–create clustered index on our demo table
CREATE CLUSTERED INDEX idx1 ON ClusteredIndexTest(ID)
GO
–create non clustered index on demo table
CREATE NONCLUSTERED INDEX idx2 ON ClusteredIndexTest(LastName)
GO
–insert few records.
INSERT INTO ClusteredIndexTest
SELECT ‘Ritesh’,‘shah’ union all
SELECT ‘Rajan’,‘Jain’
GO
–now select below given query, press CTRL+M to include actual Execution Plan with query results
–and hit F5 to run query
Select * From ClusteredIndexTest –(Always Clustered Index Scan)
Select * From ClusteredIndexTest Where ID=1 –(Clustered Index Seek)
Select * From ClusteredIndexTest Where LastName=‘shah’ –(ALWAYS CLUSTERED INDEX SCAN DUE TO SELECT *)
Select LastName from ClusteredIndexTest Where LastName=‘shah’ –(Always Index Seek on LastName Key)
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 DMO and SQL SMO with .NET application

Recently I was assigned one project for optimization which has been developed in windows .net 2.0 environments with C# language. Along with optimization I have to move database from SQL Server 2005 to SQL Server 2008.
When I configured that application in my server, I used to get following error:
System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {10020100-E260-11CF-AE68-00AA004A34D5} failed due to the following error: 80040154
That application was working fine on my client’s server and showing me error in my server so I looked at the code in debug mode and found when the application is trying to open connection form SQL DMO, connect don’t get open and showing the above error.
When I observed that I brush up my memory and remember that SQL DMO is older version, used to use with SQL Server 2000 and it was there in SQL Server 2005 too for backward compatibility. The server I was using to test that application had just SQL Server 2008 and this issue start raising.
Now I have two choices.
1.)    Change complete code to SQL SMO from SQL DMO as SQL DMO is older version and deprecated in newer SQL Server
2.)    Use backward compatibility tool which used to ship with SQL Server 2005
First choice is fair but since I am testing I can’t go for that as of now so I choose second way and downloaded component from following link and error got disappeared.
There are so many different downloaded on above link, you have to go for following section:
Microsoft SQL Server 2005 Backward Compatibility Components
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

CHARINDEX and PATINDEX with STUFF function for string manipulation in SQL Server 2008

Whenever it comes to the string manipulation, people stops thinking about SQL Server and start thinking about how we can do it from front-end programming. This is not always true. I agree that SQL Server is not for string manipulation but in many scenarios, it becomes easy to manipulate string in SQL Server itself rather than in front-end programming.
Let us see one example about this.
Suppose we are storing pointer to image in our database and suddenly folder structure get changed and we have to update path in SQL Server.  In this situation two very popular string manipulation functions CHARINDEX and PATINDEX comes to our help. There are several ways in SQL Server with which we can manipulate the string. I am going to show you two different ways today.
–create table and insert some dummy data
create table StringManipulate
(
      ImagePath varchar(100)
)
GO
INSERT INTO StringManipulate
SELECT ‘~/ROOT/IMAGES/11/ONE.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/1/TWO.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/111/THREE.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/111/FOUR.jpg’ UNION ALL
SELECT ‘~/ROOT/IMAGES/111/FIVE.jpg’
GO
Now, let us think that we are going to remove subfolders of IMAGES and going to put all the images directly into the IMAGES folders so we have to update path accordingly in SQL Server also. We have sub folder from 1 to 999 in our IMAGES folder.
Before we make actual update, let us first SELECT it and will see how it will look.
–First and efficient way with PATINDEX to find a pattern
–and STUFF to manipulate string
SELECT    
      CASE
      WHEN PATINDEX(‘%/[0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9]/%’, ImagePath), 2, )
      WHEN PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9]/%’, ImagePath), 3, )
      WHEN PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9][0-9]/%’, ImagePath), 4, )
    END
FROM StringManipulate
WHERE      PATINDEX(‘%/[0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0
GO
–second way with CHARINDEX and very popular string manipulation functions
–LEFT and RIGHT
select left(ImagePath,14)+right(ImagePath,len(ImagePath)-charindex(‘/’,ImagePath,15))
from StringManipulate
GO
Once we are confident about our manipulation in SELECT statement, we will move to update our records in table. We will use same above SELECT query’s logic in UPDATE statement to actually manipulate string.
–update with first method
update StringManipulate set ImagePath=CASE
      WHEN PATINDEX(‘%/[0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9]/%’, ImagePath), 2, )
      WHEN PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9]/%’, ImagePath), 3, )
      WHEN PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0 THEN STUFF(ImagePath, PATINDEX(‘%[0-9][0-9][0-9]/%’, ImagePath), 4, )
    END
WHERE      PATINDEX(‘%/[0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9]/%’, ImagePath) > 0
            OR PATINDEX(‘%/[0-9][0-9][0-9]/%’, ImagePath) > 0
–let us check whether our UPDATE has properly been made or not.
SELECT * FROM StringManipulate
GO   
–update with second method.
–if you have already run first update given above, you have to do this UPDATE in another table with same data
–if you will run this UPDATE statement with modfied data, it won’t give you expected results.
UPDATE StringManipulate SET ImagePath=left(ImagePath,14)+right(ImagePath,len(ImagePath)-charindex(‘/’,ImagePath,15))           
SELECT * FROM StringManipulate
GO
Isn’t it easy to do in SQL Server rather than C#?
BTW, I had written some more articles about string handling and CHARINDEX. If you wish, 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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

SP_Depends is very useful stored procedure which can give you list of the dependent object of your table or views. If you pass table name in SP_Depends, it will give you depended views, stored procedures, functions etc. let us look at this by small demo.

–create one small database for testing
create database DependCheck
go
use DependCheck;
go
–create one table and insert some data
create table TestDepend
(
ID Int Identity(1,1),
Name Varchar(20)
)
GO
Insert into TestDepend
SELECT ‘RITESH’ UNION ALL
SELECT ‘RAJAN’ UNION ALL
SELECT ‘ALKA’
go
–create one PROC which is depend on TestDepend Table
CREATE PROC uspTestDependSelectAll
AS
SELECT * FROM TestDepend
GO
–try executing PROC
EXEC uspTestDependSelectAll
GO
–check SP_Depends system procedure to get all dependent objects
sp_depends ‘TestDepend’
GO
–here is one alternate way to get dependent list
SELECT * FROM information_schema.routines ISR WHERE charindex(‘TestDepend’, ISR.ROUTINE_DEFINITION)>0
GO
–USE master;
–GO
–DROP DATABASE DEPENDCHECK
–GO
Well we have seen two different ways to get list of dependent objects of tables but in my live database when I run both way, it gives me different results however in our case it will give same results. So the question I would like to ask is, which way is correct and reliable?

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

ORDER BY clause and function with alias column name in SQL Server

ORDER BY clause is really tricky sometime.  It doesn’t accept alias column name in some situation nor it is being used in Subquery. However we do have some alternate way with the trick to do so.

There was small discussion going about this on Vijaya’s Blog Where I have participated.  .NET MVP Vijaya’s raised the question about this topic and I found it interesting that is why I am sharing it here.

Basically he asked readers that why Order By is working with column alias

For eg:

SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY Current_Dt
And why the same alias doesn’t work with the function in ORDER BY clause.

For eg:

SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate));

I found one trick with CTE which can work for this:

For Eg:

with cte as
(
SELECT
LoginID,
HireDate,
GETDATE() AS Current_Dt
FROM HumanResources.Employee
)select loginid,hiredate,current_dt from cte
ORDER BY ABS(DATEDIFF(DAY,Current_Dt,HireDate))

I would like my blog reader to participate there. Here is the link for that article. DotNetVJ

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

Upgrade from SQL Server 2000 to SQL Server 2005

There were major changes in architecture & engine of SQL Server 2005 as compare with SQL Server 2000. So as per my observation and experience in live environment, many different kind of error used to come while upgrading, I don’t mean that upgrade doesn’t work at all but personally I have came across so many different kind of error so rather than upgrading from SQL Server 2000 to SQL Server 2005, I would like to follow different approach which has been given below.

1.)    Detach all use databases (don’t need to have system databases with us)
2.)    Back up all the job if you have any in your SQL Server 2000
3.)    Create login transfer script so that all logins of your SQL Server 2000 works fine in SQL Server 2005 with same passwords. There are few different approaches for this but I would like to point out one of the Microsoft’s link for this task. http://support.microsoft.com/kb/246133
4.)    Now, this is the time to uninstall SQL Server 2000
5.)    Install SQL Server 2005 and upgrade it with latest hot fixes and patches
6.)    Attach all your databases which were detached from SQL Server 2000
7.)    Restore all jobs which were backed up from SQL Server 2000
8.)    Run the transfer login script which we have generated in step 3 above.

Isn’t it simple to go for? I find this easiest way to go for rather than upgrading the server. This is my personal point of view which I used to follow. The main reason of writing this article is, yesterday one of my friend was upgrading his SQL Server 2000 to 2005 and came across one weird error which he was not able to solve. I suggested him to follow these steps in future and also suggested few steps which might resolve the error he was facing. I used to get update from him today, If the way I suggested had helped him to resolve the error, I will provide full case study in my blog so everybody can take advantage of the same.

Reference: Ritesh Shah               

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

Article on Audit facility in SQL Server 2008 in Experts-Exchange

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server.

In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace which is not the most intuitive tools. However in the latest editions, we are now given quite a lot of power for auditing with some purpose built tools. The SQL 2008 Audit is meant to be replacing SQL Trace, it is faster, more granular and easier to interact with from SQL Server Management Studio, and with code. In this article, I am using a T-SQL code approach.

Since this is one of the big topics in SQL Server 2008 (and bound to become more popular), it is not possible to cover it completely here. However, I will try to give you one detailed but simple example as an introduction to SQL Audit.

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