Setup Certificate mirroring in SQL Server 2008 R2

Setup Certificate mirroring in SQL Server 2008 R2

Mirroring is my one of the favorite high availability feature till SQL Server 2008 R2 edition. I used to setup mirroring for every important production database I have in our production system if version is SQL Server 2008 R2 or less.

Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Mirroring can be easily established between servers when they are in same network domain with fully trusted environment but what, if server are in different network or located at different places? Can’t we setup mirroring between those? Answer is yes, still we can setup mirroring if both servers are able to connect each other via any media like VPN or anything else.

Please note that mirroring is deprecated feature from SQL Server 2012 and will be removed from next version so it is good practice to use Always On Availability Group feature in SQL Server 2012.

I will have two servers with SQL Server installed.

Server 1 instance name is SQLX64 which is SQL Server 2008 R2 Enterprise edition on Windows Server 2008 Enterprise OS. This is primary (Principal) server.

Server 2 instance name is MARS which is SQL Server 2008 R2 Enterprise edition on Windows Server 2008 Enterprise OS. This is mirror server.

Both servers are located in same datacenter within same domain name Edison.

I want our database “SMXP” to be mirrored.

Before you start actually implementing steps given here, please confirm that your principal database is on full recovery mode.

Take full and latest transaction backup of your database and restore it to secondary/slave/mirror server with NORECOVERY option.

Execute following code in primary (Principal) server. In my case, it is SQLX64.

[sourcecode language=”sql”]–execute following script in SQLX64 which is principal server

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘StrongPa$$word’;
GO

–Create certificate in principal server and keep it safe for future use
CREATE CERTIFICATE SQLX64_mirroring_cert
WITH SUBJECT = ‘SQLX64 certificate’,
START_DATE = ‘2012-12-01 00:00:00’,
EXPIRY_DATE = ‘2025-12-31 00:00:00’
GO

— backup certificate which we have created in previous step
— and manually copy it to MARS which is our mirror server (Secondary server)
BACKUP CERTIFICATE SQLX64_mirroring_cert TO FILE = ‘D:\Database\SQLX64_mirroring.cer’;
GO
–creating endpoing for mirroring in principal server
CREATE ENDPOINT Mirroring_Endpoint
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLX64_mirroring_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
GO

— Create a login for MARS which will be used to connect MARS to SQLX64 server

CREATE LOGIN MARS_mirroring_login WITH PASSWORD = ‘StrongPa$$word’;
GO
— Create a user for new login we have created above
CREATE USER MARS_mirroring_user FOR LOGIN MARS_mirroring_login;
GO
[/sourcecode]

 

Execute following code in secondary (mirror/slave) server. In my case, it is MARS.

[sourcecode language=”sql”]–execute following script in MARS which is mirror server

USE master
GO

— setting up master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘StrongPa$$word’;
GO

— creating certificate, keep it safe for future use
CREATE CERTIFICATE MARS_mirroring_cert
WITH SUBJECT = ‘MARS certificate’,
START_DATE = ‘2012-12-01 00:00:00’,
EXPIRY_DATE = ‘2025-12-31 00:00:00’
GO

— Backup certificate and coppy it manually to SQLX64
BACKUP CERTIFICATE MARS_mirroring_cert TO FILE = ‘D:\Database\MARS_mirroring.cer’;
GO

–creating endpoint
CREATE ENDPOINT Mirroring_Endpoint
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE MARS_mirroring_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE = ALL
)
GO

— Create a login for the SQLX64 in MARS
CREATE LOGIN SQLX64_mirroring_login WITH PASSWORD = ‘StrongPa$$word’
GO

— Create a user for the new login we have created above.
CREATE USER SQLX64_mirroring_user FOR LOGIN SQLX64_mirroring_login;
GO
[/sourcecode]

After restoring full and latest transaction backup of principal database to mirror database and performing all of the above given steps.  we are just few steps away now.

We had created certificate in principal as well as in mirror server and I have mentioned that copy certificate backup to other server. Hope you have copied Principal server’s certificate to mirror server and vice versa.

Execute following code in primary (Principal) server. In my case, it is SQLX64.

[sourcecode language=”sql”]

— Associating MARS’s certificate in our primary server SQLX64

CREATE CERTIFICATE MARS_cert
AUTHORIZATION MARS_mirroring_user
FROM FILE = ‘D:\Database\MARS_mirroring.cer’
GO

–we have to grant permission to login for connecting with ENDPOINT
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [MARS_mirroring_login]
GO
[/sourcecode]

Execute following code in secondary (mirror/slave) server. In my case, it is MARS.

[sourcecode language=”sql”]

—- Associating SQLX64’s certificate in our mirror server MARS

CREATE CERTIFICATE SQLX64_mirroring_cert
AUTHORIZATION SQLX64_mirroring_user
FROM FILE = ‘D:\Database\SQLX64_mirroring.cer’
GO

–we have to grant permission to login for connecting with ENDPOINT
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [SQLX64_mirroring_login]
GO

— Setting up partnership between principal and mirror
–provide with fully qualified server.domain name or IP.

ALTER DATABASE SMXP SET PARTNER =’tcp://SQLX64.Edison:5022′
GO

[/sourcecode]

 

Execute following code in primary (Principal) server. In my case, it is SQLX64.

[sourcecode language=”sql”]

— Setting up partnership between principal and mirror
–provide with fully qualified server.domain name or IP.

ALTER DATABASE SMXP SET PARTNER =’tcp://MARS.Edison:5022′
GO

[/sourcecode]

After performing all steps given above, mirroring will be ready and looks something like this:

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

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

TableDiff utility to compare data between two databases

Introduction
As a database professional, we might come across in situation where we need to compare data row by row or column wise between two tables which either resides in same database or in same instance or may be in different instance in different server.
What do you in this situation?
1.) Do you write down script of your own?
2.) Do you use any third party software?
3.) Do you use “TableDiff” utility comes with SQL Server itself?
Third option, out of all of the above, seems good to me as we neither need to invent the zero again by writing down the script by our own nor we need to pay extra money to compare data.
“TableDiff” is one of the wonderful and oldest utility provided by Microsoft. It works fine with SQL Server 2000 to the latest SQL Server edition. However, I am providing you the script and example from my SQL Server 2008 instance.
Getting Ready

Before you move forward, you need to find out two tables whose data you wanted to compare. It might be in publisher/subscriber in replication, it might be in two different databases you are using for scale out or may be anywhere else.
If you don’t have this situation at the moment in your environment, don’t worry, I will be giving a script to raise the scenario to test “TableDiff” utility.


How to do it…

1.)    Open New Query window in you SQL Server
2.)    Create two different database by using following script:
USE master
GO
CREATE DATABASETableDiffDb1
GO
CREATE DATABASETableDiffDb2
GO
3.)    Create a sample table in “TableDiffDB1” database with following script
 USETableDiffDb1
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLEorders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 1000 sample rows into table
INSERT INTOorders(OrderDate, Amount, Refno)
SELECT TOP1000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOINsys.all_objects b
GO
4.)    Creating “Orders” table in second database by copying 900 records (out of total 1000 records) from “Orders” table from “TableDiffDB1” database by using following script.
USE TableDiffDb2
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLE orders
END
GO
–creating table
CREATE TABLEorders(OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 900 sample rows into table from TableDiffDb1 database’s Orders table
INSERT INTOorders(OrderDate, Amount, Refno)
SELECT TOP900 OrderDate,Amount,Refno FROM TableDiffDb1.dbo.orders
5.)    Now use following command to see the difference between two tables.
exec master..xp_cmdshell‘tablediff -sourceserver [RITESH-SHAH\MSSQL2008] -sourcedatabase TableDiffDb1 -sourcetable Orders -destinationserver [RITESH-SHAH\MSSQL2008] -destinationdatabase TableDiffDb2 -destinationtable Orders -et Difference -f D:\OrdersDifference.sql’

Replace your server instance name in “SourceServer” and “destinationServer” parameter in above given command and you will get one .SQL file in D drive. Running that SQL file will insert all missing records in “Orders” table of “TableDiffDb2” database as it shows you the list of all missing records there.

There’s more…

I would like to draw your attention to some of the facts which can help you if you don’t find “TableDiff” working in your environment.
Remember that “TableDiff.exe”  file resides in installation directory of SQL Server by default which is “C:\Program Files\Microsoft SQL Server\100\COM” in my case.  So, there is chance that “TableDiff” command is not accessible via DOS prompt, you have to set path for “TableDiff” in “ServerVariable”.
You can reach “ServerVariable” by “MY Computer Properties > Advanced System Settings > Advanced > Environment Variables > System Variables > PATH
If you find any path under “PATH” in “ServerVariable”, you can put “;” (semicolon) after that path and can add your path for “TableDiff”.
Generally people tend to use “TableDiff” from DOS prompt itself or via .bat (batch file) file but I have used “xp_cmdshell” extended stored procedure to show the use of command right from SQL Server but there may be a chance that “xp_cmdshellis disable in your environment. If your security constraint allows, you can enable “xp_cmdshell”. For more details about the steps, click here.
Reference: Ritesh Shah
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

MERGE statement in SQL Server 2008 and later version

MERGE is really a fantastic improvement in SQL Server 2008 which is really underutilized, I have seen many time recently that developers are still using separate DML statement for Insert / Update and Delete where there is a chance they can use MERGE statement of they can use condition based Insert / Update and Delete in one shot. 
This will give performance advantage as complete process is going to read data and process it in one shot rather than performing single statement to table each time you write.
I will give you one small example so that you can see how one can use MERGE statement or which situation we can use MERGE statement in???
Suppose we have one Member’s personal Detail table where we can find Memberid, member name, registration date and expiration date. There is one more table there for Member’s user name and password.
Now, we want to delete those users from memberLogin table whose expiration date has been met, we want to set default password for those member who are not expired right now and we want to make entry of those user who are just registered and id/password is not set yet.
–create Member’s personal detail table and insert data in it.
Create Table MemberPersonalDetail
(
MemberID INT Identity(1,1),
MemberName Varchar(20),
RegisterDate date,
ExpirationDate date
)
GO
INSERT INTO MemberPersonalDetail
SELECT ‘Ritesh Shah’,’01/01/2000′,’12/31/2015′ Union ALL
SELECT ‘Rajan Shah’,’02/07/2005′,’06/20/2011′ Union ALL
SELECT ‘Teerth Shah’,’06/22/2011′,’12/31/2015′
GO
SELECT * FROMMemberPersonalDetail
go
–create Member’s login detail table and insert data in it.
CREATE TABLE MemberLoginDetail
(
MemberID INT,
UserName varchar(20),
UserPassword varchar(20)
)
GO
INSERT INTO MemberLoginDetail
SELECT 1,‘Ritesh Shah’,‘TestPassword’ UNION ALL
SELECT 2,‘Rajan Shah’,‘goodluck’
GO
SELECT * FROMMemberLoginDetail
go
–MERGE statement with Insert / Update / Delete…..
–if you just need Insert / update or Insert / delete or Update / Delete anyting
— you can use any combo
— I have explained all three DML in one MERGE statement to demonstrate it.
MERGEMemberLoginDetail AS mld
USING (SELECT MemberID,MemberName,ExpirationDate FROM MemberPersonalDetail) AS mpd
ON mld.MemberID = mpd.MemberID
WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE
WHEN MATCHED THEN UPDATE SET mld.UserPassword = ‘DefaultPassword’
WHEN NOT MATCHED THEN
INSERT(MemberID,UserName,UserPassword)
VALUES(mpd.memberID,mpd.MemberName,‘DefaultPassword’);
GO
–check the table whether operation is successfully done or not.
SELECT * FROMMemberLoginDetail
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Filtered Index in SQL Server 2008/Denali

Filtered Index is nothing but just a feature of Non clustered index which I shown in previous two articles. It is just a non clustered index with WHERE clause in simple terms.
It is mainly used while you have big tables and you used to select only subset of data from that table. Like you have one big customer table and have one field of “Reference Person” in that table, it has NULL value if customer directly comes to us and has reference person’s name, if customer came from any of the reference. In this case if you want only those customers list that has reference person so that we can distribute some sort of consolation to those reference people.
The main advantage of “Filtered Index” is, it will have lower amount of root pages to store the data as it will consider only those rows which cater the needs of “Where” clause of “Filtered Index”.
Less number of pages means reduced storage size.  Since “Filtered Index” has only those data in root pages which caters the need of “Where” clause, means when you perform any DML operation like Insert, Delete or Update, “Filtered Index” will get effect only if it affects the Index Key which comes under the “Where” clause of Index so low maintenance cost. 
BTW, you can’t create “Filtered Index” on View but it will surely get benefit of the “Filtered Index” created on base table.
Let us check the impact of  “Filtered Index” practically.
–create one database which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘SQLHubFilteredIndex1’,‘U’) IS NOT NULL BEGIN
      DROP TABLE SQLHubFilteredIndex1
END
GO
–creating table
CREATE TABLE SQLHubFilteredIndex1 (ID INT IDENTITY Primary Key Clustered, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting fack rows into table
INSERT INTO SQLHubFilteredIndex1 (OrderDate, Amount, Refno)
SELECT TOP 100
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
Union All
SELECT TOP 100000
      NULL,
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
–run the following query with execution plan together and see the results in execution plan
–you can see execution plan with the following steps
–first select both of the below given query
–Press Ctrl+M
–press F5
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
CREATE NONCLUSTERED INDEXidx_SQLHubFilteredIndex1 ONSQLHubFilteredIndex1(OrderDate)
WHERE OrderDate is not null
SELECT * fromSQLHubFilteredIndex1 where OrderDate is not null
GO
–if you wish, you can uncomment below code and delete SQLHub database
—-use master
—-go
—-drop database sqlhub
You can see in above screen shot that the same query ran faster after creating index.

if you want to refer all other articles related to index, click here.
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
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Update NULL records in all columns with any value which is NOT NULL in same column SQL Server 2005/2008

Today I gave solution for one strange problem in one of the forum; I thought to share that script with all of you. Requirement was something like below:

— Update all field of Table which is NULL

–NULL data should be populated with NOT NULL value of the same column

Well, this is somehow strange but it was needed so I quickly create one small script with the help of cursor, however, I always avoid cursor as long as possible. I didn’t find any other quick solution at that time.

–create table for demo

if OBJECT_ID(’emps’,‘U’) is not null drop table emps

CREATE TABLE [dbo].[emps](

      [Name] [varchar](50) NULL,

      [Dept] [varchar](10) NULL,

      [Company] [varchar](15) NULL

) ON [PRIMARY]

 

GO

–insert some data

INSERT INTO emps

SELECT ‘RITESH’,‘MIS’,‘CHEM’ UNION ALL

SELECT ‘RAJAN’,NULL,NULL UNION ALL

SELECT NULL,‘ACCT’,‘MAR’

GO

 

–script with cursor

declare @SQL nvarchar(max)

DECLARE @ColName VARCHaR(15)

set @SQL=

 

DECLARE FirstCur CURSOR FORWARD_ONLY

FOR select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=’emps’

 

OPEN FirstCur

FETCH FROM FirstCur INTO @ColName

 

WHILE @@FETCH_STATUS=0

BEGIN

      SET @SQL=@SQL+ ‘ Update Emps SET ‘ + @ColName + ‘ = (SELECT top 1 ‘ + @ColName + ‘ FROM emps where ‘ + @ColName + ‘ is not null) where ‘ + @ColName + ‘ is null; ‘

      FETCH NEXT FROM FirstCur INTO @ColName

END

print @sql

CLOSE FirstCur

DEALLOCATE FirstCur

exec sp_executeSQL @SQL

go

 

–CHECK DATA

select * from emps

Happy Coding!!!!

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 SQL Server Agent JOBS in SQL Server 2008/2005/2000

As a DBA we may need to set the JOB for various task and those tasks will be saved under MSDB database. You can see it from EM or from SSMS GUI tools but it would help sometime to execute query to see the list of available JOB in SQL Server 2000+ versions.
There are two ways to achieve that task. Have a look at it.
–with stored procedure
EXEC MSDB..sp_HELP_JOB


–with sysjobs view
select * from msdb..sysjobs



Both of the above statements will give you list of JOB have been set in SQL Server Agent along with so many other important 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 Specific word or phrase from all stored procedure, views, and triggers available in SQL Server 2005/2008

We may require sometime to find specific TableName or some words or some phrase have been used in any SP, Triggers or in VIEW. This is interesting to know how we can do it with T-SQL rather than manually checking script of everything.

Before I move forward to my original script, let me give you some basic logic behind that. Actually SYSObjects contains Name, ID along with many useful information of objects like stored procedure, view, trigger, function, user table, system table etc and SysComments contain ID of objects along with Text which has been used to create that object. So, now it may have popped up in your mind that I must have joined these two system views.

Before I show you the script I also would like to introduce you with the possible values could be used in “XType” column of “SysObjects” as that is the base and you can decide what to look (SP, Triggers, Views or anything else)

Here are some of the values of “Xtype” column and its meaning.

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

So now you are all set to look at the script, have a look at it.

–display the list of any trigger, view and stored procedure which has used ‘SY’ in the script

SELECT DISTINCT so.name,sc.text

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE so.xtype in (‘P’,‘TR’,‘V’) and sc.TEXT LIKE ‘%sy%’

order by name

 

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 of CUBE and ROLLUP – Difference of CUBE and ROLLUP in SQL Server 2005/2008

SQL Server 2005+ came up with exciting facility of CUBE and ROLLUP clause. Herewith, I am going to show you use and difference of CUBE and ROLLUP in SQL Server 2005 and SQL Server 2008.

CUBE:  generates a result set that represents aggregates for all combinations of values in the selected columns

ROLLUP:  generates a result set that represents aggregates for a hierarchy of values in the selected columns

Let us see one practical scenario which will make your concept much clear about both these exciting features.

–Table 1 for Demo

create table dbo.Orders

(

OrderID varchar(5),

OrderDate varchar(50)

)

 

–date for table1

insert into dbo.Orders

select ‘A1000’,GETDATE()-1 union all

select ‘A1001’,GETDATE()

 

–table 2 for demo

create table dbo.OrderDetails

(

OrderID varchar(5),

SampleNo Varchar(8),

SampleDate varchar(50)

)

 

–data for table 2

insert into dbo.OrderDetails

select ‘A1000’,‘A1000-01’,GETDATE()-1 union all

select ‘A1000’,‘A1000-02’,GETDATE() union all

select ‘A1000’,‘A1000-03’,GETDATE() union all

select ‘A1001’,‘A1001-01’,GETDATE() union all

select ‘A1001’,‘A1001-02’,GETDATE()

 

–let us check both table

select * from dbo.Orders

select * from dbo.OrderDetails

 

–let us check how many samples came for each order id

select

o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

 

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000   3

A1001   2

 

(2 row(s) affected)

 

*************************************************************/

 

 

select

o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with cube

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000   3

A1001   2

NULL    5

 

(3 row(s) affected)

 

*************************************************************/

 

 

select o.orderID,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID

with rollup

/************************************************************

answer would be.

orderID Total Sample

——- ————

A1000   3

A1001   2

NULL    5

 

(3 row(s) affected)

 

*************************************************************/

 

–You will not be able to find big difference between ROLLUP and CUBE with above query.

–isn’t there any difference at all? NO, there is a difference. let us see the difference.

 

 

 

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with cube

/************************************************************

answer would be.

OrderID              SampleDate                                         Total Sample

——————– ————————————————– ————

A1000                May 24 2009  3:53PM                                1

Total Sample on Date May 24 2009  3:53PM                                1

A1000                May 25 2009  3:53PM                                2

A1001                May 25 2009  3:53PM                                2

Total Sample on Date May 25 2009  3:53PM                                4

Total Sample on Date Total Sample for OrdID                             5

A1000                Total Sample for OrdID                             3

A1001                Total Sample for OrdID                             2

 

(8 row(s) affected)

 

*************************************************************/

 

 

select

case when grouping(o.orderID)=1 THEN ‘Total Sample on Date’ else o.OrderID end as OrderID,

case when grouping(od.SampleDate)=1 THEN ‘Total Sample for OrdID’ else od.SampleDate end as SampleDate,

count(od.SampleNo) as ‘Total Sample’

from dbo.Orders o join dbo.OrderDetails od

on o.OrderID=od.OrderID

group by o.OrderID,SampleDate

with rollup

/************************************************************

answer would be.

OrderID              SampleDate                                         Total Sample

——————– ————————————————– ————

A1000                May 24 2009  3:53PM                                1

A1000                May 25 2009  3:53PM                                2

A1000                Total Sample for OrdID                             3

A1001                May 25 2009  3:53PM                                2

A1001                Total Sample for OrdID                             2

Total Sample on Date Total Sample for OrdID                             5

 

(6 row(s) affected)

 

*************************************************************/

In last two queries, you find the difference between CUBE and ROLLUP. Second from last query with CUBE shows you 8 rows while last query which is using ROLLUP, showing 6 rows. CUBE will show you sample received for each orderID on each day and finally it will show you total sample received whereas ROLLUP will show you bit less summary like: which day for which ORDERID, how many samples come in. and finally total sample received.

 

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

Check whether FileExists in SQL Server 2005/2008

Well, you might have done programming in any of the .NET flavor to check whether file is exist in particular folder or not. You might have used many classis of SYSTEM.IO namespaces and big code to check but If I tell you it is more than easy to check the file whether it is exists or not, in SQL Server than .NET, what would be your reaction? Ohh!! What are you talking about!!!!
I am not kidding, it is really very very easy, just one simple statement, no namespace, no use of class etc. etc. Have a look at it.
exec master.dbo.xp_fileexist‘d:\emps.txt’



If emps.txt would be exists in your “D” drive, you would get answer “1” in “File Exist” column or “0”. Isn’t it very easy?
Now think if you are doing some kind of T-SQL Programming and you need this value in variable, again very easy and small code snippet required for that, have a look at it again.
DECLARE @isExists INT
exec master.dbo.xp_fileexist‘d:\emps.txt’, @isExists OUTPUT
SELECT case @isExists when 1 then ‘Yes’ else ‘No’ end as isExists



Happy T-SQLing!!!
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