Thursday, October 30, 2014

How to take Transaction Log backup ? TIP# 68


As we discussed in earlier tips how to take full backup and differential backup now in this tip we will see how to take transaction backup .

Before taking transaction backup the first and mandatory condition is that you should have a full backup.

Just right click the database and go to backup option as you will get following screen. Now we need to select Transaction Log   option in   backup type dropdown.

Once this is selected we need to give a transaction log backup name as shown below. The extension should be .TRN




Once you provided all the mandatory values just click on then you will get your  Transaction Log backup.

We can take Log backup with  SQL command also

For example :-

BACKUP LOG IndiandotnetDB
TO DISK ='E:\IndiandotnetTransaction.trn'

When you run above command you will get following screen and your log backup is done.


I hope this will help you.

Thanks & Enjoy!!!


Tuesday, October 28, 2014

How to take differential backup ? TIP #67

We were discussing backup from last few post. Now lets take a look one more feature of backup which is differential backup.
Differential backup  is backup after last full backup. Before taking Differential backup  it is a mandatory condition to take full backup.
Differential backup can speed up your recovery process or we can say restore process.
In below example we would understand how to take differential backup
To take differential backup we can run following command
TO DISK = 'D:\IndiandotnetDB.dif'

Or we can take differential backup by following steps also
Right click the database select database backup option you will get same screen which we explain at the time of full backup.
if you see below screen you will find we have change backup type to Differential and  taking backup to d drive with name Indiandotnetdb.dif
Once you click OK button. your differential backup will be completed.
I hope this tip might help you somewhere.
Thanks & Enjoy!!!

Saturday, October 25, 2014

Oh! I forgot When I took last Database Backup ? How to determine this? TIP # 66


In last few tips TIP #64 & TIP #65  we get information  how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup.

Problem:-   Sometimes , we are interested to know when last backup is taken for particular database ?

Solution:  The first thing I want to share here that  you should have proper maintenance plan to take backup of database and by job History you are able to know when last backup is taken but if this is not the case then not to worry SQL Server maintain itself information related to backup. to determine this you need to write a simple query as shown below

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   CASE msdb..backupset.type 
       WHEN 'D' THEN 'Database' 
       WHEN 'L' THEN 'Log' 
   END AS backup_type, 
   msdb.dbo.backupmediafamily.physical_device_name, AS backupset_name,
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id


  When I run above command I get information related backup like databasename, backup start date, Backup finish date, expiry date(if any) , backup type like whether it is database backup or Log backup , size of backup , path where database is taken, name of backup set.

see below snap for detail


I hope this tip might help you somewhere.

Thanks !!


Thursday, October 23, 2014

Did I take right backup ! how to ensure backup can be restore ? TIP # 65


Problem:- We have seen last time how to take backup in tip # 64.   but sometimes it happened we took backup  and we are not able to restore it. It might be corrupted.

Now , Next step thought come in our mind how to ensure we took right backup which can be restore.

Solution:-  To ensure backup is correct. We can check following option as shown in below figure.

in Reliability section  check following option

1) Verify backup when Finished

2) Perform checksum before writing  to media


Once this option is check SQL server automatically cross check verify the backup when it is finished and by checking “Verify backup when Finished”

With CheckSum SQL Server cross check before taking backup.

We can also write following query

TO DISK = 'D:\Indiandotnet.bak'


Now to assure more we can write following command and verify whether the database can be restored or not whether the backup set is valid or not.

FROM DISK = 'D:\Indiandotnet.bak'


I hope this might helpful you somewhere.



Tuesday, October 21, 2014

How to take compressed backup in SQL Server TIP #64

Problem:-  A good maintenance plan always said to take backup but sometimes it is very difficult to take backup of database just because of space constraint.
We take backup but we don’t have enough space available.
Solutions:  SQL server provided  the best way and gave us solution of compressed backup. Now how to take compressed backup let see step by step here.
Step 1:-  Select the database whose backup you want. Just right click and select backup option as shown in below figure
Step 2:-  Once you click the backup option an new screen will appear as shown below
Step 3:-  Now click on  Add button as shown in above screen. Once you click on the add button a new screen will appear as shown below where you have to provide the path and file name of compressed backup file.
Step 4:-   Once you provided the filename click OK to the button now click on the options menu on right hand you will get new tab option here in the last you will get compress option as shown in below figure with arrow. Select compress backup option in dropown
once this done click on OK button now you are good to go. Your compressed backup is done. and you will get backup complete message as shown below
Or else you can use following command
TO DISK = 'E:\MyCompressedBackup.bak'

You will get compressed backup.
I hope this will help you somewhere.

Monday, October 20, 2014

Various available string functions in SQL Server TIP # 63


There are various functions available in SQL Server and it is good to know all of them you never know when they will be helpful to you.

So lets start one by one.

1) LEN :-   By the name it is clear that LEN function give length of the parameter

For example :-

DECLARE @Name AS VARCHAR(100) = 'Rajat'


By the name it is clear that both LTRIM  & RTRIM trim the white space from the parameter value.
LTRIM  do left trimming & RTRIM do Right side trimming.

For example :-

DECLARE @Name AS VARCHAR(100) = '     Rajat   '
SELECT LEN(@Name)  AS LengthWithSpace



LEFT and  RIGHT both the function provide part of the parameter according to provided length.
For example we have taken 3 characters from Left of "Rajat" which is "Raj" and with RIGHT function we got 2 letters from Right which is "AT"

For example:-

DECLARE @Name AS VARCHAR(100) = 'Rajat'

SELECT LEFT (@name,3) AS LeftFunction
SELECT RIGHT(@Name,2) As RightFunction



By The name it is clear that you can change case of provided string parameter.

For example
DECLARE @Name AS VARCHAR(100) = 'I am Rajat Jaiswal.'

SELECT LOWER(@Name) AS LowerCase
SELECT UPPER(@Name) AS  UpperCase


5) CONCAT :-

This is a new function introduce in latest versions. It concatenating all the provided parameters.
The best part is it handle NULL value also.

As shown in below figure we are concating FirstName, LastName, MiddleName in a new column name FullName.

and if you see below figure you will find the MiddleName have some  null values also but we did not do anything for that concate function handle it himself.

SELECT FirstName,
       MiddleName ,
       CONCAT(LastName,',' ,FirstName, ' ', MiddleName) As FullName 
FROM person.Person



I hope this might be useful for any new SQL person. There are many other functions which I will describe in  next post.


RJ !!

Friday, October 17, 2014

Converting Row to Column use Pivot feature TIP #62


Problem:  Sometimes we may require to convert row data to column. we require pivot view of data.

Solution:  Lets understand this by an example below

I am using a table variable which have few columns like  Employee, Amount, Month and year. Lets define and create some sample data here.

DECLARE @tblEmployee AS TABLE (Employee        VARCHAR(50),
                           Amount DECIMAL(10,3),
                           intMonth       TINYINT,
                           intYear        INT)

INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '10000',1,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '15000',2,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '16000',3,2013)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '10700',1,2014)
INSERT INTO @tblEmployee (Employee, Amount, intMonth, intYear)
VALUES ('RAJAT', '16000',2,2014)

Now just cross check the data how it look

SELECT * FROM @tblEmployee


Now we want result something like as shown in below figure


Now to achieve this we wrote following pivot syntax

SELECT Employee,
       [1] As Jan,
       [2] AS Feb,
       [3] As mar
FROM ( SELECT Employee,
       FROM @tblEmployee )p

PIVOT (SUM (Amount) FOR intMonth IN ([1],[2],[3]))Q


I hope this may help you somewhere.

In coming tips we would share how to create dynamic Pivot and  UNPIVOT.

Thanks & Enjoy !!!


Thursday, October 16, 2014

Database Diagram support object cannot be install error-SSMS TIP# 61


Problem:-  It is general problem that you are interested in generating the database diagram of a database and when you right click on database diagrams folder of database and try to create a new database diagram you get following error as shown in below snap.



The above snap says that you are not authorize person to generate database diagram so to resolve this we have to do following steps and then you are good to go to create database diagram

step 1:- Go to database properties by right clicking on database and choose files option from left side bar your will get following screen.


If you see above screen you will find the owner text box is empty.

Step 2:-  Now click the button near by the textbox and you will get another popup window just select login “sa” user (or who has permission) as shown in below figure


Step 3:-  Once it is  selected  the owner text box will be filled  as shown in below figure now click on OK button



By following above 3 simple steps you will be able to create database diagram for particular database.

I hope this solution may help you if you stuck in such situation.

Thanks & Enjoy!!!


Wednesday, October 15, 2014

Grouping Sets–Good to know feature TIP# 60


Grouping sets is one of the cool feature came in SQL SERVER 2008. Lets understand here with problem and solution.

Problem:-  Suppose , We want  an aggregation result in a query with different groups. Firstly we want aggregated result on first column then combination of First & second column then other column combination.

So, to resolve this problem a basic traditional way is to create 3 separate query and combine there result.

Solution:-  Now in SQL Server 2008 onwards we have a new feature for to achieve such problem which is called GROUPING SETS.

Lets understand this by an example.

I am taking here Adventureworks2012 database. Now we want total due amount on different basis  example

1) total due amount on Territory name and sales person basis

2) Total due amount on  Territory name

3) total due amount on sales person basis

4) total due amount on sales order date  basis

To achieve above  results we write following query

SELECT sod.OrderDate,
p.LastName + ','+ p.FirstName  As SalesMan,
SUM(sod.TotalDue) as totalDue
FROM [Sales].[SalesOrderHeader] sod
INNER JOIN [Sales].[SalesPerson] sp ON sp.BusinessEntityID = sod.SalesPersonID
INNER JOIN [HumanResources].[Employee] emp ON emp.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [Person].[Person] p ON p.BusinessEntityID = sp.BusinessEntityID
INNER JOIN [sales].SalesTerritory st ON st.TerritoryID = sod.TerritoryID
   (st.Name,p.LastName + ',' +p.FirstName ),
   (p.LastName + ',' +p.FirstName ),
ORDER BY  st.Name,sod.OrderDate

Now when we run the query and we get results which we want.


I hope this may be help you some where.

Thanks !!!


Monday, October 13, 2014

Find last statistics updated date detail ?–Maintenance TIP #59


Problem:- One of the pain point in any SQL engineer  is “Performance”. There are various reasons due to which your SQL Server database is slow.

One of the possible reason is your maintenance.   You don’t know when statistics last updated and take further step if those are not updated


Here we have simple query to find when the statistics was last updated for a table.

SELECT, AS [Index Name], 
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)   ON i.[object_id] = s.[object_id]
                      AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

When you run it you will find last statistics update date if it is too old it means you have to run the maintenance for those tables.

see below screenshot which I run on my machines adventureworks2012 database.


I am sure you will analyze your database tables stats and run maintenance accordingly.

I hope this tip may help you some where.

Enjoy !!!


Sunday, October 12, 2014

Merge Statement one of the way to synch destination from source table TIP# 58


Problem:-  Suppose you have a source table and one destination table and you want to synch destination table from source table. means if record is already exists then you need to update and the record with latest value and insert the record if record is not exists.

Solution:-  Although there are various ways exist but here we are using a new feature came in SQL Server 2008 which is MEGE statement.

Suppose we have a  tblStudentSource table and one tblStudentDestination table

both have equal columns as shown in below figure.

both have studentId, Firstname, LastName & course column



if you see above figure you will find that destination table there are 2 rows while in tblstudentSource table we have 3 rows.

Now if you see in tblStudentDestination LastName of StudentID  2 is spelled wrong.

So what we have to do here. We have to Insert a new row from tblStudentSource and update existing row.

To achieve this we will use merge statement as shown in figure


Now if you see above snap  we write basic merged statement which check condition of tblSource and destination table and if condition is matched then we wrote update statement if not matched then we wrote insert statement.

MERGE tblStudentDestination AS Destination
USING tblStudentSource  AS Source
ON Source.StudentId = Destination.StudentId
SET Destination.FirstName = Source.FirstName,
    Destination.LastName = Source.LastName,
    Destination.Course = Source.Course
INSERT (FirstName,LastName,Course)
VALUES (Source.FirstName,source.LastName,Source.Course);


Now we run above statement we got the desire result and our source and destination table is in synched.

I hope this post may help and give you a general Idea of merge statement.



Friday, October 10, 2014

What is TVP (Table Value Parameter) & How to use it ? TIP #57


This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter.

I know above statement is not digestive enough so lets understand this by an example.

Suppose , I have a table tblStudent with 4 columns studentId, FirstName, LastName, and class  as shown in below figure


Now I need to insert value in this table so I have created a simple stored procedure as shown in below figure


Now , I can easily insert values into it by calling the stored procedure for each student FirstName, LastName, course,

Suppose , I need to insert 4 records in the table then I need to call this stored procedure 4 times as shown in below figure


Now think you have .net program and you have to call this stored procedure more than 100 times Smile .

I know you are bit scary.

Not to worry thanks to SQL Server again by providing Table value parameter to handle such kind of situation.

Now let me explain how to create table value parameter and call it to resolve such problem step by step

Step 1:-

To create Table value parameter you have to  choose  user define data type  as shown in below figure


Step 2:-

Now you can modify the template script in my case I am creating a student type User defined table type as shown in below figure . You will find I am using all the column which I need to insert into tblStudent table



Step 3:-

Now I am writing a  new stored procedure which use this table type as shown in below figure


You find in the above snap we have created a procedure with student type parameter which we have created .

The point to remember here that the table value parameter should be READONLY when passes in a stored procedure

Step 5:- Now let see how to call this stored procedure which has student type table value parameter


Now when we run above snap statement we inserted 3 rows in tblStudent.

Now lets consider the above problem calling stored procedure 1000 times instead of that you will call stored procedure only once with this amazing Table value parameter feature.

You can pass any collection and data table as a parameter from .NET.

Isn’t it made our life easy ?  I am big fan of this feature.

I hope if you use you will also.

Hope this tip may help you somewhere.

Thanks & Enjoy !!!

RJ !!!

Thursday, October 9, 2014

Good to Know SQL JARGON - An interview TIP #56


Hello friends,

Just wanted to share some technical words here again it is good to know

1) OLAP :  On Line Analytical  Processing

2) MOLAP :- Multidimensional  On Line Analytical Processing

3) ROLAP :- Relational On Line Analytical Processing

4) HOLAP :- Hybrid On Line Analytical Processing

5) RTOLAP :- Real Time On Line Analytical Processing

6) DOLAP :- Desktop On Line Analytical Processing

8) SSIS :- SQL Server Integration Services

9) SSAS :- SQL Server Analysis Services

10) SSMS :- SQL  Server Management Studio

11) SSRS :- SQL Server Reporting Services

12)Magic Table:- A temporary table use at the time of  trigger

13)DML :- Data Manipulation Language (Insert/Update/Delete)

14) DDL :- Data Definition Language  (Create /Alter /Drop)

15) DCL :- Data Control Language (Grant /Revoke)

16) TCL:- Transaction Control Language  (Rollback /Commit)

17) ACID :- Atomicity,  Consistency , Isolation ,Durability

18) PK- Primary Key

19) FK – Foreign Key

20) NF:- Normal Form (1NF,2NF,3NF)

21) ODBC :-  Open Database Connectivity

22) OLEDB :- Object Linking  and Embedding Database

23) RDBMS :- Relational Database Management System

24) CRUD:- Create, Read , Update, Delete

25) ETL :- Extract Transform Load

26) BI :- Business Intelligence

27) SP :- Stored Procedure

28) VLDB :- Very Large Data Base

29) CTE:- Common Table Expression

30) CDC :- Change Data Capture

31) TDE :- Transparent Data Encryption

I will add more in future.

Thanks & Enjoy !!!

RJ !!!

Maintenance of fragmented Heap table–TIP #55



In last post  post #54  we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so.

Now our task is to run maintenance of this highly fragmented table.

So you have to run following command


For example  I run the Rebuild for  Adventureworks2012 database’s heap table which we found in post #54.

See below snap for detail


So, with this REBUILD command the table is rebuild and the fragmentation issue resolved.

I hope it may help you some where.

Thanks & Enjoy!!!


Wednesday, October 8, 2014

What is Heap table in SQL Server and How to get all the heap table from a database ? TIP # 54


Heap table:-

A table without cluster index is called Heap table. Now you are thinking why we are talking about this.

So as  SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table.

Now our next question is how to determine all the heap table from a database

so below is simplest query to determine all the heap tables from a  database

SELECT T.Name 'Heaptable'
FROM sys.indexes I    
    INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = 'U'

I ran this query on Adventureworks database and 2 tables with heap as shown in below figure


now in next post we will discuss how to rebuild fragmented heap table which is a maintenance activity.

I hope this article may help you.

Thanks !!!


Tuesday, October 7, 2014

How Enable /Disable all CONSTRAINT on a table ? tip #53


Dear Friends,

Although , When we have designed our database he/she knows what type of constraints is require to make data perfect and the Database designer applies those constraints but it might be possible that sometime we have to disable those constraints make some tweak with the data in the table and then again enable the constraints.

Below are two simple command to Enable & Disable constraints of a table.

a) Disable constraints on a table


b) Enable Constraints on a table


Lets understand this by an example suppose, I have city_seed table which having Foreign key constraint of country_seed table.

Now  when we run disable constraint command as shown above then you will find the constraint is disable  as shown in figure


Now when we run enable constraint then see below figure


I hope it might helpful you somewhere.

Thanks & Enjoy!!!


Monday, October 6, 2014

A hidden feature sp_MSforeachdb TIP #52


As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB.

Sp_MsForEachDB  by the name it is clear that it will run provided command to each database.

The syntax of sp_MsForEachDB  is very simple almost like sp_MsForEachTable.

Lets understand this by an example

Suppose we want name of each database  of our SQL server for this we will run following command

Execute sp_MSforeachdb " SELECT  '?' AS Name"

Now  when we execute it we will get following result as shown in below figure.


This two stored procedures are not documented but it help a lot in maintenance task and other tasks.

I will share some maintenance query  with sp_MsForEachDB & sp_MsForEachtable  soon.

I hope this will help you somewhere.

Enjoy !!!


Sunday, October 5, 2014

A hidden feature sp_MSforeachtable–run on entire tables of a Database TIP# 51


Sometimes it happened that you need to run a single statement on entire tables which exists in database.  so most of the time we think of cursor which run for each sys.objects (table)  and we execute the dynamic statement by replacing table name.


Although the solution we are thinking is correct there is no issue at all but SQL SERVER made our life more each by providing “sp_MSForEachTable”  which is a hidden stored procedure.

By the name it is clear that it will run on each table.

Lets understand this by an example. Suppose you want to  row count of each table then you can write following statement.

DECLARE @tblRowCount AS TABLE (Counts  INT,
                               TableName VARCHAR(100))

INSERT INTO @tblRowCount (Counts,TableName)
EXEC sp_MSforeachtable
@command1='SELECT COUNT(1) As counts,''?'' as tableName FROM ?'

SELECT * FROM @TblRowCount ORDER BY Counts desc

Now when we run it we will get row count of each table as shown in below snap



A part from it you can use this hidden feature in maintenance also like running re indexing, re organizing entire table index etc.

I hope this may help you some where.