Sunday, April 24, 2016

Difference between delete, truncate and drop statements

Delete:-
·         Delete statement is used to delete rows from the table.
·         Rollback is possible after delete
·         Delete lock a row table and delete them row by row
·         Data manipulation language (dml)
·         Possibility to use where clause filter to delete specific set of data ex: - where
·         Ex:-delete from transaction where id=5
·         Whenever delete command executed automatically a trigger fired in backend
·         Delete will not reset identity columns values of a table
·         Delete from query require more system resources and it will take long time to execute. It will record all rdbms changes in one row at time in transaction log
·         Syntax: -
      Begin transaction
Delete from transactions
           Select * from transactions
           Rollback
           Select * from transactions
    Truncate:-
·         Sql truncate command is used delete all rows from the table.
·         Roll back is not possible after truncate
·         Truncate will reset the identity columns values to its initial defined values
·         When you use truncate statement it will never use or record much log information so the truncate operation will perform very faster
·         Truncate will not delete data if the table is bind with foreign keys or other constrains or other constrains
·         We cannot use where conditions in truncate statement
·         Data definition language (ddl)
·         Trigger will not be used when truncate operation performed
·         It locks entire table
·         Roll back is not possible in advanced mode.
·         Syntax: -
Truncate table <table name>
 Drop:-  drop is used drop entire table object.

Thursday, April 14, 2016

How to Create a Linked Server

Why we will create linked server?
·         If you want to connect another sql server instance from you instance.
·         When Sql server databases distributed across the network.

What is linked server?
·         OLE DB data source referred as linked server in SQL Server
·         As part of retrieving data from other instances of sql server we will create linked server connection

Steps to follow for creating linked server in SQL Server 2012.
·         There are two ways to create linked server
1.    By using sql server stored procedure sp_addlinkedserver
2.    By using sql server management studio

Using sql server stored procedure sp_addlinkedserver
EXEC sp_addlinkedserver  
   @server=N'ServerName',
   @srvproduct=N'',
   @provider=N'SQLNCLI',
   @datasrc=N'ServerName\DataBaseInstance';

The provider given in above syntax is sql server native client oledb provider when we use sp_addlinkedserver procedure all local logins will be mapped to linked server for security reason we will never do this or we will alter the permission’s to the linked server after creating

Using sql server management studio:-
Login into to sql server management studio there you will find Server objects

Right click on Server objects and select new linked server 
In general page give Linked server name:-
Server type is other data source

Select the provider if another instance you are connection is sql server select Microsoft OLE DB Provider for sql server

Product should not be empty Give nay name SQL Server 2012

Data source is IP/Sql Server instance which is shared to you
xxxx.xxxx.xxxx.xxx/SQL2012

Provider string leave empty

Catalog is database name and optional

Go to security tab and set the [be made using this security context] parameter as below



Remote Login: - user name share to you [remote machine]
With password: - Enter password

And click ok done now you are ready to use. 

Thursday, April 7, 2016

UPDATE from SELECT using SQL Server

Below syntax will show you how the table A data is updated with table B values from select query
UPDATE Table_NA
SET
    Table_NA.col1 = Table_NB.col1,
    Table_NA.col2 = Table_NB.col2
----select query started
FROM
    Some_Table Table_NA
INNER JOIN
    Other_Table Table_NB
ON
    Table_NA.id = Table_NB.id
WHERE
    Table_NA.col3 = 'NEW'