Next step of programming

With new feature of CLR integration we can provide function with in our assemblies which can be accessed by the user in the TSQL statements. Let us now try to create one title case function. First create one library project in visual studio 2005. Then create one class with one function inside that.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace AssemblyFunctions
{

public partial class MySQLFunctions    
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static string TitleCase(string data)
{
string caseData = data.ToString().Substring(0, 1).ToUpper()
+ data.ToString().Substring(1, data.ToString().Length -1).ToLower();

return caseData;
}
}
}

Now we can compile this assembly and keep it at one location. Then move to MSSQL Management Studio and there in query window. execute following commands one bye one
First we need to enable the CLR integration for that we have to use the sp_configure store procedure in the following way

--Configure the clr enabled state
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Now to access the assembly into our MSSQL Server we have to register it first

--REGISTER CLASS LIBARIES IN SQL SERVER
CREATE ASSEMBLY ASSEMBLYFUNCTIONS 
FROM 'E:\personal\projects\AssemblyFunctions\AssemblyFunctions\bin\Debug\AssemblyFunctions.dll'
WITH PERMISSION_SET = SAFE
GO

To drop that assembly use the following commands

--DROP/UN-REGISTER THE ASSEMBLY 
DROP ASSEMBLY ASSEMBLYFUNCTIONS
GO

Now to access our functions we have to first register our functions, procedures or what ever is there

--REGISTER USER DEFINED FUNCTION IN SQL SERVER 
CREATE FUNCTION TITLECASE(@DATA NVARCHAR(MAX)) RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME AssemblyFunctions.[AssemblyFunctions.MySQLFunctions].TitleCase  GO

Since now all the things are done you can call your respective function

Select dbo.TitleCase('sample')
go

Also this assembly and function will be added to the mssql server. see the image below

CLR Integration

To get the count of the rows we normally use

select count(ContactID) from AdventureWorks.Person.Contact 

But there is another way which is fast and less on cost as compared to previous method and the way is

select rows from sysindexes where id = OBJECT_ID('AdventureWorks.Person.Contact')
 and indid < 2

Below see the query cost details

Query Cost Details

Some of the time databases are not attached to the MSSQL server and while attaching them we need the name of the database which is there in the mdf file. Getting the name is very much important while attaching to the MSSQL Server so for that purpose we can utilise the following command

dbcc checkprimaryfile ('F:\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\DATA\AdventureWorks_Data.mdf',2)

It will give the following result

Identity fields are auto incremented field. There can be only one identity field per table. It needs the base value and the increment value with which it will be incremented whenever new record is inserted in the table. An IDENTITY column of tinyint datatype can go upto 255, smallint can go upto 32767, int can go upto 2147483647 and bigint can go upto 9223372036854775807.

CREATE TABLE dbo.Employee ( EmployeeID smallint identity(7,2), EmployeeName char(20) )

Now we need to just enter the name of the employee not his id.

Insert into table dbo.Employee(EmployeeName) Values(“Kshyup”)

After insertion and deletion of data we may be in need of resetting the identity column counter for that we can make use of DBCC CHECKIDENT. For example

DBCC CHECKIDENT(‘Employee’, RESEED, 1)

Some time we way find a situtation where we need to get the information about the backup file or device before restoring that backup for example we may want the file it is containing or the media information for that purpose we can use the following command

To get the information  regarding the file it contains

Restore FilelistOnly From Disk = N'C:\Sample.Bak'

This will return the information about the file which is there in the backup and if you want to see the labels only then you can use

Restore LabelOnly From Disk = N'C:\Sample.Bak'

Other useful arguments can be REWINDONLY and VERFIYONLY. For more details on this visit MSDN

http://msdn.microsoft.com/en-us/library/ms173778.aspx

We can utilize the query of listing foreign key with respective table and fields to generate the alter query for adding constraint. For that we will use the XML and temp table and will generate the list of the alter queries

SELECT TABS.NAME AS [REFTABLE],
   (SELECT NAME
       FROM SYS.TABLES AS CTAB
       WHERE (OBJECT_ID = FK.PARENT_OBJECT_ID)) AS [PARENTTABLE], 
          FK.NAME AS [KEYNAME],
          AC.NAME AS [COLUMNNAME] INTO #TEMPTABLE
FROM SYS.TABLES AS TABS INNER JOIN             
SYS.FOREIGN_KEYS AS FK ON FK.REFERENCED_OBJECT_ID = TABS.OBJECT_ID INNER JOIN             
SYS.FOREIGN_KEY_COLUMNS AS FKC ON FK.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID INNER JOIN             
SYS.ALL_COLUMNS AS AC ON AC.COLUMN_ID = FKC.PARENT_COLUMN_ID
		     AND AC.OBJECT_ID = FKC.PARENT_OBJECT_ID

SELECT 'ALTER TABLE ' + [PARENTTABLE] + ' ADD CONSTRAINT ' + 
	[KEYNAME] + ' FOREIGN KEY (' + REPLACE((SELECT 
[COLUMNNAME]  AS 'data()' FROM  #TEMPTABLE AS TT 
WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] FOR XML PATH('')),' ',',')
+ ') REFERENCES (' + REPLACE((SELECT [COLUMNNAME]  AS 'data()' 
FROM  #TEMPTABLE AS TT WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] 
FOR XML PATH('')),' ',',') + ') ON UPDATE CASCADE ON DELETE CASCADE'    
FROM 
	#TEMPTABLE GROUP BY [KEYNAME], [PARENTTABLE], [REFTABLE]

DROP TABLE #TEMPTABLE

To list the all the foreign keys available in database and there associated tables and fields we can utilize following query

SELECT tabs.name AS [Reference Table],
   (SELECT name FROM sys.tables AS ctab
       WHERE (object_id = fk.parent_object_id)) AS [Parent Table], 
       fk.name AS [Key Name],
        ac.name AS [Column Name] 
FROM sys.tables AS tabs INNER JOIN             
sys.foreign_keys AS fk ON fk.referenced_object_id = tabs.object_id inner join             
sys.foreign_key_columns as fkc on fk.object_id = fkc.constraint_object_id inner join             
sys.all_columns as ac on ac.column_id = fkc.parent_column_id
		     and ac.object_id = fkc.parent_object_id 
ORDER BY tabs.name, 2

In the part 1 we added one server as linked server to another instance of the mssql server now we will right a sample query which will retrun the result from both the server tables and will give single output

select * from master.sys.objects
union
select * from [inventory].master.sys.objects

it will return the result after combining the result of both the databases of different instance. So you will get the list of objects which are there in both the instances. Now we will take another sample in which we will have the product database in one instance and customer database in another instance we will join them on one field and will get the result after joining them

select productname, productid, customername, customerid from products.dbo.productlist as prolist
	inner join [inventory].customers.dbo.customerlist as cuslist on (cuslist.productid = prolist.productid)

here result from the one instance and customer data is coming from the inventory server

There can be case or a requirement to attach the another sql server to your existing sql server instance so that you can work on the both server at same time. Get data from both the server from one query as a single result, managing all server from one location or for various work you can utilise it.

for that we have to use sp_addlinkedserver. one of the example

EXEC sp_addlinkedserver   
@server='inventory', 
@srvproduct='',
   @provider='SQLNCLI', 
@datasrc='server\mssql2005'

for more information on this visit sp_addlinkedserver (Transact-SQL) – MSDN

Here we added the server\mssql2005 as the inventory server as linked server now we have to add the login information for that we have to use

EXEC sp_addlinkedsrvlogin 'inventory', 
'false', 'mydomain\user2', 
'sa', 'sapassword

To get more information on this checkout sp_addlinkedsrvlogin (Transact-SQL) – MSDN

Now that server is linked and ready to be user. we can use the following query to get the data from the server

SELECT * FROM [inventory].master.dbo.sysobjects

Most of time you will find certain tasks to be done which can be done by executing commands from the dos prompt of windows. For example you want to delete certain data files which are dettached from the server or for any other work. For that we need to have access to the server where we can open a command prompt and execute the commands.

But there is a smart way to do it from the sql server also with the help of the xp_cmdshell stored procedure. This procedires spwans the windows command shell and we can exucte neumrous commands through it which will be retured back to us as a result in the tabular format

xp_cmdshell { 'command_string' } [ , no_output ]

It can be used for various requirement. One very good example of it is given at
http://blog.sqlauthority.com/2007/04/13/sql-server-script-to-find-sql-server-on-network/

which tells how to get the list of the sql server running on the network by executing the stored procedure for more information on xp_cmdshell please visit following link 

http://msdn.microsoft.com/en-us/library/ms175046.aspx

if xm_cmdshell is disabled then first we have to enable it by executing following commands

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

but enabling it on server is risky since from network user can excute commands on the server which normally he cann’t do. So some risk are involved. which can be handled by the rights