Tag Archives: ms sql server 2008

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 default SQL Server port and change it in SQL Server 2008

Listening port plays really very vital role in any version of SQL Server. There are some port number like 1433, 1533, 1434 etc. are really widely known and famous so whenever hackers try to scan port of your SQL Server, they first try to scan these ports as those are very famous.
It is really good practice to change default SQL Server port to something else from 1433. After reading this short message, if you are not experience DBA and SQL Server developer, you might think, how can read the port of SQL Server? It is really very easy with few different ways.
Read/Change Default SQL Server Port:
Method 1:
1.)   
Go to Start->All Programs->Microsoft SQL Server 2008->Configuration Tools->SQL Server Configuration Manager
2.)    From the configuration manager, expand “SQL Server Network Configuration” from left hand side tree.
3.)    Now click on “Protocols for YourSQL ServerInstance”
4.)    From the right hand side, you can find “TCP/IP”, right click on it and click on property
5.)    From the dialog box, click on the “IP Addresses” tab and look at port no in “TCP Port” property under “IP ALL”.
6.)    If you want to change your port, change it in “TCP Port” and Restart your SQL Server instance
Method 2:
Read the port from your registry with below code snippet.
DECLARE @tcp_port nvarchar(10)
EXEC xp_regread
@rootkey    =    ‘HKEY_LOCAL_MACHINE’,
–SQL2K8 is my instanace name of SQL Server
–may be different in your case
@key        =    ‘SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQLServer\SUPERSOCKETNETLIB\TCP\IPAll’,
@value_name    = ‘TcpPort’,
@value        =    @tcp_port OUTPUT
select @tcp_port
If you wish to change port # from registry, open “regedit” from command prompt, go to the path given in above SP. Path is already provided in @key argument. Double click on “TcpPort” key and change the value right from there.
I have used XP_RegRead stored procedure to read registry. Sometime back I have used other system stored procedure to read registry and find default installation path of your sql server. The name of that stored procedure is XP_Instance_RegRead.
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

Encryption – Decryption in SQL Server 2008 and backup database with encrypted data and restore it somewhere else

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, we have improved and in-built facilities for Encryption and Decryption within SQL Server.

To encrypt data and decrypt it, it is really a very crucial task as one mistake and your data go out of your reach. It can become more difficult when you will encrypt some data in one database in one server and try to restore that database into different server.

In this Article, I am going to show you the script which can encrypt data in one database on one server, take its backup, and restore that encrypted database anywhere else and you will get your data with 100% security and no data risk.

We will now create one database which is going to be used in throughout this example.

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

Set update trigger to keep watch on certain column’s update in SQL Server 2008

I have seen many times that people keep asking the question how to track the updates in certain column.Well there are several ways to do so but the oldest way is to set UPDATE trigger on the table and keep an eye on the important column.

Please be informed that excesses use of trigger may affect performance so use this method carefully not for keeping track of so many columns in so many tables.

Anyway, let us get back to our core topic and see one small script.

–create one table for demonstration
CREATE TABLE TriggerTest
(
UserName varchar(20),
[Password] varchar(20)
)
GO
–create one table which will LOG information
CREATE TABLE TriggerTestLog
(
ID INT identity(1,1),
UserName varchar(20),
OldPassword varchar(20),
NewPassword varchar(20),
DateOfUpdate datetime
)
GO
–insert some records in first table
INSERT INTO TriggerTest
SELECT ‘ritesh’,‘shah’ union all
SELECT ‘rajan’,‘shah’ union all
SELECT ‘bihag’,‘goodluck’ union all
SELECT ‘bhaumik’,‘shruti’
GO
–set after update trigger for first table
–when password will be updated, it will log
–information in second table.
Create TRIGGER TrgTriggerTest ON TriggerTest
   AFTER Update
AS
BEGIN
      SET NOCOUNT ON;
      IF UPDATE([Password])
      BEGIN
            insert into TriggerTestLog(username,OldPassword,NewPassword,dateOfUpdate)
            select i.username,d.[password],i.[password],getdate()
            from inserted i join deleted d on i.username=d.username
      END
END
GO
–select records from second table
–which is blank right now ofcourse
select * from TriggerTestLog
GO
–update first table
UPDATE TriggerTest SET [Password]=‘furious’ WHERE UserName=‘ritesh’
GO
–you will get information automatically inserted in second table.
select * from TriggerTestLog
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