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  a best way and give 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

Backup_step1

Step 2:-  Once you click the backup option an new screen will appear as shown below

Backup_step2

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.

Backup_step3

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

backup_step5

Or else you can use following command

BACKUP DATABASE INDIANDOTNETDB
TO DISK = ‘E:\MyCompressedBackup.bak’
WITH COMPRESSION ;

You will get compressed backup.

backup

I hope this will help you somewhere.

Enjoy!!!

RJ!!

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’
SELECT LEN(@Name)

2) LTRIM & RTRIM :-

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
SELECT LEN(RTRIM(LTRIM(@Name))) As LenghtWithLTRIMRTRIM

Ltrim_Rtrim

3) LEFT & RIGHT:-

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

Left_right

4) LOWER & UPPER :-

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

LOWER_UPPER

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,
       LastName,
       MiddleName ,
       CONCAT(LastName,’,’ ,FirstName, ‘ ‘, MiddleName) As FullName 
FROM person.Person

Concate

 

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

Enjoy!!!

RJ !!

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,
st.Name,
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
GROUP BY GROUPING SETS (
   (st.Name,p.LastName + ‘,’ +p.FirstName ),
   (st.Name),
   (p.LastName + ‘,’ +p.FirstName ),
   (sod.OrderDate)
  
)
ORDER BY  st.Name,sod.OrderDate

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

GroupSetResult

I hope this may be help you some where.

Thanks !!!

RJ!!!

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

Solution:-

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

SELECT o.name,
       i.name AS [Index Name], 
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
       s.auto_created,
       s.no_recompute,
       s.user_created
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.

Last_update_Date

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

I hope this tip may help you some where.

Enjoy !!!

Rj!!

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

 

table_Structure_Merge

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

Basic_Merged_Statement

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
WHEN MATCHED THEN
UPDATE
SET Destination.FirstName = Source.FirstName,
    Destination.LastName = Source.LastName,
    Destination.Course = Source.Course
WHEN NOT MATCHED THEN
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.

Thanks!!!

RJ!!!

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

Studenttable

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

without_tvpProc

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

call_sp_without_TVP

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

User_Define_Type

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

 

Student_Type_Definie

Step 3:-

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

StudentType_Procedure

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

PassingTVP_Sp_Call

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 !!!

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

 

Friends,

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. For performance it is require to maintain this type of heap table also.

So you have to run following command

“ALTER TABLE  TABLENAME REBUILD;

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

See below snap for detail

Rebuild_Heap 

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

I hope it may help you some where.

Thanks & Enjoy!!!

RJ!!

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

heaptable

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 !!!

RJ!!

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

ALTER TABLE  tableName NOCHECK CONSTRAINT  ALL

b) Enable Constraints on a table

ALTER TABLE TABLE_NAME CHECK CONSTRAINT ALL

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

disable_Constraint

Now when we run enable constraint then see below figure

disable_Constraint

I hope it might helpful you somewhere.

Thanks & Enjoy!!!

RJ!!!