Restart SQL Server and SQL Server Agent from Command Prompt.

Many times I need to restart SQL Server service and SQL Server Agent service in my testing server.. Generally people used to go at Control Panel->Administrative Tools->Services, find SQL Server service or/and SQL Server agent service and start, restart or stop it. This is general practice but I would like to do this from command prompt itself.

Syntax is:

Net Stop mssql$YourSQLServerInstanceName
Net Start mssql$YourSQLServerInstanceName
Above command is used to start and stop SQL Server Service.  Now, here is the command for SQL Server agent.
Net Stop sqlagent$YourSQLServerInstanceName
Net Start sqlagent $YourSQLServerInstanceName
My instance name of SQL Server 2008 is “SQL2k8” so my command would be
Net Stop mssql$SQL2K8
Net Start mssql$SQL2K8
Net Stop sqlagent$SQL2K8
Net Start sqlagent $SQL2K8
Indeed I have one batch file which do this stuff for me. Just go to command prompt and run .BAT file is really easy than going to Control Panel->Administrative Tools->Services, find services and restart it.
Look at the screen below for more details.
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 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 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