SQL Queries

Basic Restore commands all in one

 

 

Restore database adventureworks from disk = 'D:\AdventureWorks.bak'



Restore with different file names and/or file locations

RESTORE DATABASE Adventureworks

FROM DISK = 'D:\AdventureWorks.bak'

WITH MOVE 'Adventureworks_data' TO 'C:\Adventureworks_data.mdf',

MOVE 'Adventureworks_log' TO 'C:\Adventureworks_log.ldf'


Restore with different database name:

RESTORE DATABASE Adventureworks1

FROM DISK = 'D:\AdventureWorks.bak'

WITH REPLACE


 

SQL Server Indexes

What is index?

In English Indexes are defined as something that serves to guide, point out, or otherwise facilitate reference. And the same concept applies to the SQL Server. To understand this concept let’s take example of Telephone Directory. Assume that data is written in the telephone directory on the basis of person applying for connection there is no sorting based on name or anything. Just imagine how much effort you have to do in order to search one name from that directory. So if you have to search name ‘Roli’ you have to go through all the records till you find Roli. Now in next scenario if the data is arranged alphabetically how will you search? You will open pages with R then Ro then Rol and then you can go through records to find name Roli.

Why to use indexes?
Index helps in faster data retrieval of the data and reduces number of I\O operations of the server.

Types of indexes present in SQL Server?

There are two types of Indexes in SQL Server.
1. Clustered
2. Non Clustered

Architecture of Clustered Index

Properties

• Clustered Index is that its leaf pages in contain data pages.
• There is only one Clustered index possible on any table in SQL Server. The database engine allows the creation of a single clustered index per table for the reason that data is physically sorted in case of Clustered indexes. And only one time you can sort any data physically .Let’s go back to our example of telephone directory If the data is sorted based on first name you cannot sort it again by Surname.
• A clustered index is built by default for each table for which you define the primary key using the primary key constraint. Also each clustered index is unique by default that is each data value can appear only once in a column for which the clustered index is defined. If a clustered index is built on a non-unique column the database system will force uniqueness by adding 4 byte identifier to the rows that have duplicate values.
• Clustered Index allows very fast access in cases where a query searches for a range of values.

Syntax

Architecture of Non-Clustered Index

 

 

 

 

 
Properties

• A non clustered index does not change the physical order of the order of the rows in the table.
• The leaf pages of the non clustered index consist of an index key plus a bookmark.
• For each  non Clustered  Index SQL Server engine creates an additional index structure that is sorted in index pages. 

Significant differences between Clustered & Non Clustered Index 

• A non clustered index does not change the physical order of the rows in the table.
• The leaf pages of a non clustered index consist of an index key plus a bookmark.

Partitioning of Table in SQL Server

If you have very large database then you can use Partition on those tables to optimise the performance of your database. The concept of partition was introduced with SQL Server 2005. The benefit is that partitioining allows you to spread data into different physical disks,leveraging the concurrent performance of those disks to optimize query performance.

Partitioning a SQL Server database table is 3 steps process :

1. Creating Partition function

2. Create Partition Scheme

3. Partitioning a Table

Let's have a closer look at each of these steps.

Step 1 : Creating a Partition Function.

The partition function defines how you want SQL Server to partition the data.Here you need to define boundaries of each partition.For example suppose we have a Salesorderdetail table that contains information of sales order , identified by unique salesorderID and holding records ranging from 1 to 400 .We decided to partition the table into four equally spaced partition.

 

 Following is the syntax to create Partition Function -

CREATE PARTITION FUNCTION SALES_PARTFUNC (int) As RANGE RIGHT FOR VALUES ( 100 , 200 , 300 )

These boundaries define four partitions.The first contains all values less than 100. The Second contains values between 100 and 199. The third contains vaules between 200 and 299. All the values greater than or equal to 300 go in the fourth partition.

I used the "RANGE RIGHT" clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used "RANGE LEFT", the first partition would have included all values less than or equal to 100; the second partition would have included values between 101 and 200 and so on.

Step 2 : Create Partition Scheme

Once you have a partition function describing how you want to split your data, you need to create a partition scheme defining where you want to partition it.

Creating partition scheme will link partitions function to filegroups for eg. If I created four filegroups named FG_example1 to FG_example4 then following statement will create the partition scheme.

CREATE PARTITION SCHEME RolSALES_partscheme

AS PARTITION RolSALES_PARTFUNC

TO (FG_example1, FG_example2, FG_example3, FG_example4)

Did you notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. We could use this partition scheme on any number of database tables.That’s where the power of reusability comes into picture.

Step 3: Partitioning a Table

After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an "ON" clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.

For example, if you wanted to create a customer table using our partition scheme, you would use the following Transact-SQL statement:

CREATE TABLE SalesOrderUpdate (ProductName nvarchar(40), ProductID nvarchar(40), SalesOrderCode int)

ON RolSALES_partscheme (SalesOrderCode)

 

Once tacit this is very useful concept of SQL Server :)