LINKED SERVER

                                                                  LINKED SERVER

What is linked server?

Linked servers are configured to allow you to connect to other database instance on the same server or another machine or remote servers. This allows the SQL server to execute the SQL scripts against the OLEDB data sources on remote servers (the remote sources can be SQL, Oracle, etc. those Databases that supports the OLE DB can be used for linking servers)   using OLE DB providers.

How to create a linked server?

There are two ways of creating a linked server first, is using the GUI (Graphical User Interface) in SSMS and second, using the T-SQL script.

Using the GUI (Graphical User Interface) in SSMS:

1.   

       1. In the SSMS, expand Server Objects and right click on the Linked Servers and the click on the New Linked Server option.

      

      

      2. Then a New Linked Server window appears. Where you can enter the information related to new     linked server. In the General page, under the Linked server enter the name of the Linked Server we are about to create (you can enter any name of your choice). In my example, I’m creating a Linked Server with the name RAIN_SQLLINKEDSERVER. Then under the Server type select Other Data     source.

      

      

      3. Under the Other Data source for Provider choosing SQL Server Native Client 11.0 as I am using SQL Server 2012.For Product name select SQL SERVER and for Data source enter the name of the remote SQL Server Instance name to which you want to connect. If the remote SQL Server instance is a named instance then enter the data source as MACHINENAME\INSTANCENAME.

      

      

      Leave the Provider string blank and Catalog option is optional (If entered use the default database you will be using).

      

      4. Then click on security from the left pane. Under the security you could see some options

      

      Local login: Specify the local login name that connect to the linked server. This local login can be either a login using SQL Server Authentication or Windows Authentication.

      Impersonate: Pass the username and the password from the local login to the linked server. For SQL Server Authentication the login with the exact same name and password must exist on the remote server. For Windows Authentication, the logins must be a valid login on the linked server.

      Remote User: Use the remote user to map users not defined in the Local login. The remote user must be the SQL Server Authentication login on the remote server.

      Remote password: Specify the password of the remote user.

      Add: You can add a new local login by clicking this.

      Remove: You can remove an existing login.

      Not be made: Specify that a connection will not be made for the logins not defined in the list.

      Be made without using a security context:  Specify that the connection will be made to the linked server without using the security context for logins not defined in the list.

      Be made using the login’s current security context: Specify that the connection can be made using the security context of the login for logins not defined in the list. If connected to the local server using the windows authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, a login name and password will be used to connect to the remote server.In this case the login with the exact same name and password must exist on the remote server.

      Be made with using this security context: Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

      So, from all the security options above I’m going to select the last option Be made using this security context option. If you select this option make sure that the username with that password must exist on the remote server.

      

      6. Next click on the Server options on the left side pane and under that you have options to select the Server Options. For example, you can select Data Access as True if you want to access the data on the remote server using the Linked server and you can also enable the RPC (remote procedure calls) against a remote server to True. This option enables us to validate the remote login. And there are other options where you can select of your choice.

      

      7. Then click OK, the new linked server is created. You can see the new linked server created under the Linked Server in the Server Objects.

USING T-SQL Script

The other method of creating the Linked server is by using the T-SQL code which is shown below:

The following is the syntax to add a linked server:

EXEC sp_addlinkedserver

@server = ‘SERVERNAME’ (Name of the linked server to create)

@srvproduct = ‘SERVER PRODUCT NAME’ (This is the product name of the OLE DB data source to add as a linked server),

@provider = ‘PROVIDER NAME’ (Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source.),

@datasrc = ‘INSTANCE NAME or MACHINENAME/INSTANCE NAME (if it’s a named instance)’;

The following is the syntax to add a login in the security context:

EXEC sp_addlinkedsrvlogin

@rmtsrvname =’Linked server name’,

@useself = ‘false’,(this determines whether to connect to remote server by impersonating the local logins or explicitly submitting a login or password)

@locallogin = ’local login’,(is the login on the local server)

@rmtuser = ‘rmtuser’, (Is the remote user used to connect to the remote server when @useself is false)

@rmtpassword = ‘rmtPassword’(Password associated with the remote username);

Example :

Here in my example I’m adding one SQL Server as Linked Server

EXEC sp_addlinkedserver

@server = ‘RAIN_SQLLINEKDSERVER’,

@srvproduct = ‘SQL SERVER’,

@provider=’SQL Server Native Client 11.0’,

@datasrc=’RAIN-SQL’;

If the remote SQL Server does not have the instance name just enter the server name in the @datasrc.

The below example creates a mapping to make sure that the windows user ‘Rain\shiny’ connects through to the linked server accounts by using the login ‘ShinyP’ and password ‘abcd123’.

EXEC sp_addlinkedsrvlogin

@rmtsrvname = ‘RAIN_SQLLINEKDSERVER’,

@useself = ‘false’

@locallogin = ‘Rain\shiny(Local login name that connect to the linked server)’,

@rmtuser = ‘ShinyP’,

@rmtpassword = ‘abcd123’;

Methods to copy tables from one Database to another Database in SQL Server

                    
How to copy the tables from one Database to another in SQL server?

In some cases, as a DBA you are requested to copy the schema and contents of the specific tables from one database to another database on the same instance or to a different instance. Then how do you do this. There are three ways to copy the specific tables from one database to another database 

Using the Select into query
Using the Export/Import Wizard
Using the Generate Scripts

 Using the SELECT INTO query :

                           select * into Test1.dbo.Results1 from Partition.dbo.Results

In my above query I’m trying to copying the data of the dbo.Results table from the Partition (Source Database) database to a new table called dbo.Results1 in the database Test1 (Destination database).First it will create the table and then it will copy the data from source table to the destination table. 

 Using the EXPORT/IMPORT Wizard :

Another method of copying the tables from one database to another database is using the Export/Import Wizard in the SQL Server Management Studio. Below is the step by step process to do this:

1. From the SQL Server Management Studio Right click on Source Database(in my case its Partition) and click on Tasks and then click Export Data to Export the data or Import Data to import the data to this Database. Here I am exporting the Data from this database to another Database so selecting Export Data option. 

2. When you click the Export Data the SQL Server Import and Export Wizard appears, then click Next.

3. Choose a Data Source of SQL Server Import and Export Wizard appears. Here in this wizard you need to select the Data source and the Server name (instance name) on which the Source Database lies and also select the source database. Once you finish selecting the options related to Source Database, then click Next.

 4. Choose a Destination of SQL Server Import and Export Wizard appears. Here in this wizard you need to select the Data source and the Server name (instance name) on which the Destination Database lies and also select the Destination database. Once you finish selecting the options related to Destination Database, then click Next.

5. Then the Specify Table Copy or Query of SQL Server Import and Export Wizard appears. Here you have two options one is copy data from one or more table or views and the second option write a query to specify the data to transfer. From this two options select the option you want and I am selecting the first option as I want to copy all the data from Source DB to Destination DB, then click Next.

 6. Select source tables or views of SQL Server Import and Export Wizard appears in this select the tables you want to copy from source(in my case I selected only one table i.e., dbo.Results ) to destination table( you can rename the table name in the destination if you want. I renamed it to dbo.Results2).To make sure that all the tables will be created in the Destination Database then click on Edit Mappings in the Select source tables or views of SQL Server Import and Export Wizard and then tick the Create Destination Table and if any of your tables contain IDENTITY column make sure to tick Enable Identity Insert and then click OK. Before clicking on OK check the mappings and you can ignore the columns you don’t want to copy to the destination. Then click Next in the Select source tables or views. 

7. Complete the Wizard appears and double check everything in this wizard and then click finish.

8. Then the execution successful of the SQL Server Import and Export Wizard appears where you can see the step by step process of copying the data.

Using the Generate Scripts :

Another method of copying the table from one Database to another Database is using the Generate Scripts option. Below is the step by step process to do this:

1. From the SQL Server Management Studio Right click on Source Database (in my case it’s Partition) and click on Tasks and then click the Generate Scripts option.

2. Choose Objects of the Generate and Publish Scripts wizard appears. Under the Choose Objects select the option you want. If you want script the entire database and database objects then select the first option and if you want to select specific database objects then select the second option and select the tables you want to script. Then click Next.

3. Set scripting Options of the Generate and Publish Scripts wizard appears and specify the path where you want to save the script file and click Advanced.

4. The Advance scripting options window appears and select Schema and data under the Types of Data to script and there are other options where you can select like the Table/Views Options. Click OK and then click Next.

5. The Summary window appears and double check all the information and then click Next.

6. The Save or Publish Scripts window appears where you can see the process of copying the data to a file and once it’s successful then click Finish.

        

   Once you click Finish. Then go to the path where you saved the script file and open the script file and run it on the Destination Database.

RANKING FUNCTIONS IN SQL

                                                          RANKING FUNCTIONS


What are Ranking Functions?

Ranking functions are a subset of the built in functions in SQL server which are used to provide a rank of one kind or another to set of rows in a partition. The partition can be a full result set, if there is no partition.

There are four types of ranking functions in SQL:

1. ROW_NUMBER

2. RANK

3. DENSE_RANK

4. NTILE

Before learning about the use of the above four ranking functions lets take an example and then see what happens when we use the above functions.

EXAMPLE :

In my example I created a table called Results with the column names Name, Subjects and Marks.

So, now lets see what happens when you use the four functions on the above table in detail.

1. ROW_NUMBER : 

This function will return a row number for each record in the result set.We can further change the resulting row number to reset the row number based on some values in result set.

SYNTAX :

ROW_NUMBER ()  OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

OVER - Specify the order of the rows.

PARTITION BY - This clause is an optional part of the Row_Number function. If you  don't use this clause then all the records of the result set are considered as the single record group or single partition and then the ranking function is applied.

When you specify a column or set of columns with PARTITION BY clause then it will divide the result set into record partitions and then finally ranking functions are applied to each record partition separately and the rank will restart from 1 for each record partition separately.

ORDER BY - This is the specified order of the row number that you would like. If you wanted the order of the row number to increment by an employee name (alphabetically), you do that here.

Example :

ROW_NUMBER function without using Partition clause:

select Name,Subject,Marks,

ROW_NUMBER() over(order by Name) as RowNumber

from Results

ROW_NUMBER function with using Partition clause:

select Name,Subject,Marks,

ROW_NUMBER() over(PARTITION BY Subject order by Name) as RowNumber

from Results

If you see the above result we applied Partition By on Subject column and based on that it divided into three partitions and then ranking function is applied starting from 1 for each record partition.

2. RANK :

Rank function provides consecutive numbering except in case of tie. It means whenever there is a tie between the rows then the ties are assigned the same rank with the next ranking skipped. So, if you have 2 items at rank 1 then the next row is ranked 3.

SYNTAX :

RANK  ()  OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

Example :

RANK() without Partition By clause

select Name,Subject,Marks,

RANK() over(order by Name) as RowNumber

from Results

RANK function with Partition By clause

select Name,Subject,Marks,

RANK() over(partition by Subject order by Name) as Rank

from Results

3. DENSE RANK :

Dense Rank function provides consecutive numbering even in case of ties. It means whenever there is a tie between the rows then the ties are assigned the same rank without the next ranking skipped. So, if you have 2 items at rank 1 then the next row is ranked 2 in this case.

SYNTAX :

DENSE_RANK  ()  OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

Example :

DENSE_RANK function without partition

select Name,Subject,Marks,

DENSE_RANK() over(order by Subject) as DenseRank

from Results

DENSE_RANK function with partition

select Name,Subject,Marks,

DENSE_RANK() over(partition by Subject order by Name) as DenseRank

from Results

4. NTILE :

This function distributes the result set into specified number of ordered partitions.

For each row in result set NTILE() will returns a group number to which the row is associated.

This is very useful while distributing the result set into multiple groups in case you need to distribute the results into multiple tables.

SYNTAX :

NTILE (No of groups into which each partition must be divided) OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

Example :

NTILE function without Partition By

select Name,Subject,Marks,

NTILE(2) over(order by Subject) as NTile

from Results

In the above example, we have specified the no of groups as ‘2’ so it will distribute the result set into 2 different groups by distributing the rows. In this case the rows cannot be distributed evenly so it will be divided as 5+4. If the number of rows were 10 then it will be divided as 5+5.

NTILE function with Partition By

select Name,Subject,Marks,

NTILE(3) over(partition by Name order by Subject) as NTile

from Results

In the above example we have specified the no of groups has ‘3’, so it will distribute the result set into 3 different groups by distributing the rows. In this case the rows are first partitioned by Name and then divided into three groups within each name.

9Rows à    Group 1 à Name=Mahesh =3 Rowsà Partition1 = 1 row

                                                                                    Partition2 = 1 row

                                                                                    Partition3 = 1 row

                           Group 2 à Name=Raj =3 Rowsà Partition1 = 1 row

                                                                                    Partition2 = 1 row

                                                                                    Partition3 = 1 row

                         Group 3 à Name=Ravi =3 Rowsà Partition1 = 1 row

                                                                                    Partition2 = 1 row

                                                                                    Partition3 = 1 row