Monday, 18 February 2013

Steps to change the server name for a SQL Server machine

http://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

Steps to Attach a SQL Server database without transaction log file

Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.

Solution: In the below script I have created the database,dropped its log file and created the database with the .mdf file.

--created database with .mdf and .ldf file
CREATE DATABASE [singleFileDemo] ON  PRIMARY 
( NAME = N'singleFileDemo', FILENAME = N'L:\singleFileDemo.mdf' , SIZE = 2048KB , FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'singleFileDemo_log', FILENAME = N'F:\singleFileDemo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 5120KB )
GO

--inserting data into database
use singleFileDemo
create table tb1 (name varchar(10))

--inserting records
insert into tb1 values('Jugal')
go 10;

--deleting the log file
--detaching the database file
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'singleFileDemo'
GO

-- now next step is delete the file manually or you can do it from command prompt
EXEC xp_cmdshell 'del F:\singleFileDemo_log.ldf'

-- script to attach the database 
USE [master]
GO
CREATE DATABASE [singleFileDemo] ON 
( FILENAME = N'L:\singleFileDemo.mdf' )
FOR ATTACH
GO

Different ways to check the SQL Server Instance Port number

Problem: If there are multiple SQL instances running on the same computer, it is difficult to identify the instance port number. You can use the below solution to find the instance specific port numbers.
Solution: You can check the list of port number used by the SQL Server instances using one of the below way.
Soln 1# Using SQL Server Configuration Manager

Go to SQL Server Configuration Manager
Select Protocols for SQL2005/2008 under SQL server Network Configuration
Right click on TCP/IP and select Properties
Select the IP Addresses-tab
In the section IP ALL, you can see the ports

Soln 2#From Registry Values
SQL Server 2005
Type the regedit command in Run window and check the below registry values.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.#
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL.#\ MSSQLServer\ SuperSocketNetLib\TCP\IPAll
SQL Server 2008
Default instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Named instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.(InstanceName)\MSSQLServer\SuperSocketNetLib\TCP\IPAll
Soln 3# Error Log
Query the error log as below to get the port number.
EXEC xp_readerrorlog 0,1,”Server is listening on”,Null
Soln 4# Command Prompts
Execute the below command from the command prompt.
Netstat -abn

Performance Tuning – Wait Statistics

Many times we got a call from the business team regarding the performance issue on the database server. As a first step you can check for the blocking, if the blocking is not there. We have to check for the waits, Query is internally waiting for the resources to complete its process.
By identifying the correct wait type will give you the directions to troubleshooting issue further. You can execute below query to get the 10 wait statistics.

SELECT TOP 10
        wait_type ,
        max_wait_time_ms wait_time_ms ,
        signal_wait_time_ms ,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
                                    AS percent_total_waits ,
        100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
                                    AS percent_total_signal_waits ,
        100.0 * ( wait_time_ms - signal_wait_time_ms )
        / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0
ORDER BY wait_time_ms DESC

You have to checkout for the below kind of wait statistics and troubleshoot as per the stats.
CXPACKET :Most of the time it indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, it may be the symptom of another problem, associated with one of the other high value wait types in the instance.
SOS_SCHEDULER_YIELD :The tasks executing in the system are yielding the scheduler, having exceeded their quantum, and are having to wait in the runnable queue for other tasks to execute. This may indicate that the server is under CPU pressure.
THREADPOOL :A task had to wait to have a worker bound to it, in order to execute.
LCK_* :These wait types indicate that blocking is occurring in the system and that sessions have had to wait to acquire a lock of a specific type, which was being held by another database session. This problem can be investigated further using, for example, the information in the sys.dm_db_index_operational_stats.
PAGEIOLATCH_*, IO_COMPLETION, WRITELOG :These waits are commonly associated with disk I/O bottlenecks, though the root cause of the problem may be, and commonly is, a poorly performing query that is consuming excessive amounts of memory in the server.
PAGELATCH_* :Non-I/O waits for latches on data pages in the buffer pool. A lot of times PAGELATCH_* waits are associated with allocation contention issues. One of the best-known allocations issues associated with PAGELATCH_* waits occurs in tempdb when the a large number of objects are being created and destroyed in tempdb and the system experiences contention on the Shared Global Allocation Map (SGAM), Global Allocation Map (GAM), and Page Free Space (PFS) pages in the tempdb database.
LATCH_* :These waits are associated with lightweight short-term synchronization objects that are used to protect access to internal caches, but not the buffer cache. These waits can indicate a range of problems, depending on the latch type. Determining the specific latch class that has the most accumulated wait time associated with it can be found by querying the sys.dm_os_latch_stats DMV.
ASYNC_NETWORK_IO :This wait is often incorrectly attributed to a network bottleneck.

Script to drop all SQL Logins

Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.
Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”
SA login account and the few policy certificates also come in SQL Login type, I have excluded them.

`1`	`select` `*`

`2`	`from` `sys.server_principals`

`3`	`where` `name` `=` `'sa'` `or` `substring(name, 1, 1) =` `'#'`

----/

select 'DROP LOGIN ' + [name]
from sys.server_principals
where type_desc = 'SQL_LOGIN'
and sid <> 0x01 and substring(name, 1, 1) <> '#'

Primary Key, Unique Key Constraints – Clustered Index and Non Clustered Index

You can use the below script to create the Primary Key on the already existing tables. Primary key enforces a uniqueness in the column and created the clustered index as default.
Primary key will not allow NULL values.

1-- Adding the NON NULL constraint

2ALTER TABLE [TableName]  

3ALTER COLUMN PK_ColumnName int NOT NULL

4 

5--Script to add the primary key on the existing table

6ALTER TABLE [TableName]

7ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)

If you want to define or create the non-clustered index on the existing table, you can use the below script. If the data in the column is unique, you can create the Unique Constraint as well.
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

`1`	`--script to create non-clustered Index`

`2`	`create` `index` `IX_ColumName` `on` `TableName(ColumnName)`

`3`	`--script to create Unique constraint on the existing table`

`4`	`ALTER` `TABLE` `TableName` `ADD` `CONSTRAINT` `ConstraintName` `UNIQUE(ColumnName)`

T-SQL script to Identify the SQL Server version and edition

Execute below T-SQL Script to get the SQL Server version details.

1SELECT SERVERPROPERTY ('productversion') as ProductVersion,

2SERVERPROPERTY ('productlevel') as Productlevel,

3SERVERPROPERTY ('edition') as Edition

Output:
-Product version (for example, 10.0.1600.22)
-Product level (for example, SP3)
-Edition (for example, Development)
SQL Server 2012 version number examples
11.0.2316 SQL Server 2012 CU1
11.0.2100.6 SQL Server 2012 RTM

How to grant the execute permission to User on all the procedure of the database?

-- creating the database role 
CREATE ROLE Database_Executor
-- granting the execute permission to database role
GRANT EXECUTE TO Database_Executor

-- Here I am granting the SQLDBPool login execute permission
USE [DBName]
GO
CREATE USER [SQLDBPool] FOR LOGIN [sqldbpool]
GO
USE [DBName]
GO
EXEC sp_addrolemember N'Database_Executor', N'sqldbpool'
GO

How much memory is used by each database in SQL Server?

SELECT
    CASE WHEN database_id = 32767 THEN 'Resource DB' ELSE DB_NAME (database_id) END AS 'DBName',
    COUNT (1) AS 'Page Count',
    (COUNT (1) * 8)/1024 AS 'Memory Used in MB' ,   
   CASE WHEN is_modified = 1 THEN 'Dirty Page' ELSE 'Clean Page' END AS 'Page State' 
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY   db_name(database_id)
GO

Update Statistics Useful Scripts

Script to find out the statistics update date for all the indexes in the current database

`1`	`sp_MSforeachtable` `'sp_autostats "?"'`

Script to update the statistics of all the indexes

`1`	`EXEC` `sp_MSforeachtable` `'UPDATE STATISTICS ? WITH FULLSCAN'`

T-SQL Script to identify tables without Primary Key

When designing tables, It is a good practice of having one column that is unique and can be a primary key. You can include one of the below type column as Primary Key
- Add Auto Increment Column
- Identify the column which unique for all the rows
Make sure Primary keys should be as small as necessary. Prefer a numeric data type because numeric types are stored in a much more compact format than character formats. Most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache.
You can execute below script to identify the tables without Primary Key and add the Primary Key into tables as per the above suggestions..

`1`	`Use <Database` `Name>`

2

`3`	`SELECT` `SCHEMA_NAME(schema_id)` `AS` `[Schema` `Name],` `name` `AS` `[Table` `Name]`

`4`	`FROM` `sys.tables`

`5`	`WHERE` `OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0`

`6`	`Order` `by` `name`

7 GO

Script to get the SQL Server Properties

create table #server(ID int,  Name  sysname null, Internal_Value int null, Value nvarchar(512) null)
insert #server exec master.dbo.xp_msver

declare @RegRootDir nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUT

SELECT
(select Value from #server where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(select Value from #server where Name = N'Language') AS [Language],
(select Value from #server where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #server where Name = N'ProcessorCount') AS [Processors],
(select Value from #server where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #server where Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@RegRootDir AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation]

drop table #server

Script to check the database Properties

select 
 sysDB.database_id,
 sysDB.Name as 'Database Name',
 syslogin.Name as 'DB Owner',
 sysDB.state_desc,
 sysDB.recovery_model_desc,
 sysDB.collation_name, 
 sysDB.user_access_desc,
 sysDB.compatibility_level, 
 sysDB.is_read_only,
 sysDB.is_auto_close_on,
 sysDB.is_auto_shrink_on,
 sysDB.is_auto_create_stats_on,
 sysDB.is_auto_update_stats_on,
 sysDB.is_fulltext_enabled,
 sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

Transactions and Locks in SQL Server

Posted by Jugal Shah on March 19, 2008

37 Votes

• What is a “Database Transactions “?
A database transaction is a unit of work performed against a database management system or similar system that is treated in a coherent and reliable way independent of other transactions. A database transaction, by definition, must be atomic, consistent, isolated and durable. These properties of database transactions are often referred to by the acronym ACID.
Transactions provide an “all-or-nothing” proposition stating that work units performed in a database must be completed in their entirety or take no effect whatsoever. Further, transactions must be isolated from other transactions, results must conform to existing constraints in the database and transactions that complete successfully must be committed to durable storage.
In some systems, transactions are also called LUWs for Logical Units of Work.
• What is ACID?
The ACID model is one of the oldest and most important concepts of database theory. It sets forward four goals that every database management system must strive to achieve: atomicity, consistency, isolation and durability. No database that fails to meet any of these four goals can be considered reliable.
Let’s take a moment to examine each one of these characteristics in detail:
Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.
Consistency states that only valid data will be written to the database.If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.
Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. For example, if Joe issues a transaction against a database at the same time that Mary issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform Joe’s entire transaction before executing Mary’s or vice-versa. This prevents Joe’s transaction from reading intermediate data produced as a side effect of part of Mary’s transaction that will not eventually be committed to the database. Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other.
Durability ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.
• What is “Begin Trans”, “Commit Tran”, “Rollback Tran” and “Save Tran”?
Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.
Users can group two or more Transact-SQL statements into a single transaction using the following statements:
* Begin Transaction
* Rollback Transaction
* Commit Transaction
Begin Transaction
Marks the starting point of an explicit, local transaction. BEGIN TRANSACTION increments @@TRANCOUNT by 1.
Rollback Transaction
If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology.
A ROLLBACK, on the other hand, works regardless of the level at which it is issued, but rolls back all transactions, regardless of the nesting level
Commit Transaction
If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.
A COMMIT issued against any transaction except the outermost one doesn’t commit any changes to disk – it merely decrements the@@TRANCOUNT automatic variable.
Save Tran
Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn’t affect the @@TRANCOUNT value. A rollback to a savepoint (not a transaction) doesn’t affect the value returned by @@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK TRAN without a specific name will always roll back the entire transaction.
• What are “Checkpoint’s” in SQL Server?
Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages modified after entered into the buffer cache, but the modifications have not yet been written to disk.
Syntax
CHECKPOINT
• What are “Implicit Transactions”?
Microsoft SQL Server operates in three transaction modes:
Autocommit transactions
Each individual statement is a transaction.
Explicit transactions
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
Implicit transactions
A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
• Is it good to use “Implicit Transactions”?
If you want all your commands to require an explicit COMMIT or ROLLBACK in order to finish, you can issue the command SET IMPLICIT_TRANSACTIONS ON. By default, SQL Server operates in the autocommit mode; it does not operate with implicit transactions. Any time you issue a data modification command such as INSERT, UPDATE, or DELETE, SQL Server automatically commits the transaction. However, if you use the SET IMPLICIT_TRANSACTIONS ON command, you can override the automatic commitment so that SQL Server will wait for you to issue an explicit COMMIT or ROLLBACK statement to do anything with the transaction. This can be handy when you issue commands interactively, mimicking the behavior of other databases such as Oracle.
What’s distinctive about implicit transactions is that reissuing SET IMPLICIT_TRANSACTIONS ON does not increase the value of @@TRANCOUNT. Also, neither COMMIT nor ROLLBACK reduce the value of @@TRANCOUNT until after you issue the command SET IMPLICIT_TRANSACTIONS OFF. Developers do not often use implicit transactions; however, there is an interesting exception in ADO. See the sidebar, Implicit Transactions and ADO Classic.
• What is Concurrency?
When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. This is called concurrency control.
Concurrency control theory has two classifications for the methods of instituting concurrency control:
Pessimistic concurrency control
A system of locks prevents users from modifying data in a way that affects other users. After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it. This is called pessimistic control because it is mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.
Optimistic concurrency control
In optimistic concurrency control, users do not lock data when they read it. When an update is performed, the system checks to see if another user changed the data after it was read. If another user updated the data, an error is raised. Typically, the user receiving the error rolls back the transaction and starts over. This is called optimistic because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction outweighs the costs of locking data when read.
• What are “Dirty reads”?
Uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction. The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row.
• What are “Unrepeatable reads”?
Inconsistent Analysis (Nonrepeatable Read)
Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. Also, inconsistent analysis involves multiple reads (two or more) of the same row and each time the information is changed by another transaction; thus, the term nonrepeatable read.
• What are “Phantom rows”?
Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction’s first read of the range of rows shows a row that no longer exists in the second or succeeding read, as a result of a deletion by a different transaction. Similarly, as the result of an insert by a different transaction, the transaction’s second or succeeding read shows a row that did not exist in the original read.
For example, an editor makes changes to a document submitted by a writer, but when the changes are incorporated into the master copy of the document by the production department, they find that new unedited material has been added to the document by the author. This problem could be avoided if no one could add new material to the document until the editor and production department finish working with the original document.
• What are “Lost Updates”?
Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data.
• What are different levels of granularity of locking resources?
Microsoft SQL Server 2000 has multigranular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, SQL Server locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency, but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but has a lower overhead because fewer locks are being maintained.
SQL Server can lock these resources (listed in order of increasing granularity).
RID: Row identifier. Used to lock a single row within a table.
Key: Row lock within an index. Used to protect key ranges in serializable transactions.
Page: 8 kilobyte –(KB) data page or index page.
Extent: Contiguous group of eight data pages or index pages.
Table: Entire table, including all data and indexes.
DB: Database.
• What are different types of Isolation levels in SQL Server?
READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
REPEATABLE READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.
SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
• If you are using COM+, what “Isolation” level is set by default?
SERIALIZABLE transaction isolation level is the default isolation level for the COM+ application.
• What are “Lock” hints?
A range of table-level locking hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements to direct Microsoft SQL Server 2000 to the type of locks to be used. Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.
• What is a “Deadlock”?
Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server identifies the problem and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
• What are the steps you can take to avoid “Deadlocks”?
Here are some tips on how to avoid deadlocking on your SQL Server:
* Ensure the database design is properly normalized.
* Have the application access server objects in the same order each time.
* During transactions, don’t allow any user input. Collect it before the transaction begins.
* Avoid cursors.
* Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
* Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
* If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
* Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
* If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
* Consider using bound connections.
• What is Bound Connection?
Bound connections allow two or more connections to share the same transaction and locks. Bound connections can work on the same data without lock conflicts. Bound connections can be created from multiple connections within the same application, or from multiple applications with separate connections. Bound connections make coordinating actions across multiple connections easier.
To participate in a bound connection, a connection calls sp_getbindtoken or srv_getbindtoken (Open Data Services) to get a bind token. A bind token is a character string that uniquely identifies each bound transaction. The bind token is then sent to the other connections participating in the bound connection. The other connections bind to the transaction by calling sp_bindsession, using the bind token received from the first connection.
• Specity the types of Bound Connections
Local bound connection
Allows bound connections to share the transaction space of a single transaction on a single server.
Distributed bound connection
Allows bound connections to share the same transaction across two or more servers until the entire transaction is either committed or rolled back by using Microsoft Distributed Transaction Coordinator (MS DTC).
• How can I know what locks are running on which resource?
Use SP_Locks system stored procedure

SQL Integrity Constraints or Constraints

Constraints are some rules that enforce on the data to be enter into the database table. Basically constraints are used to restrict the type of data that can insert into a database table.Constraints can be defined in two ways:

Column Level
The constraints can be specified immediately after the column definition with the CREATE TABLE statement. This is called column-level constraints.
Table Level
The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Types of SQL Constraints

In Microsoft SQL Server we have six types of constraints

Primary Key Constraints
Primary key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table. It can not accept null, duplicate values.
Primary key constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY,
col2 datatype
); 
```
Primary key constraint at table level
```
 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] PRIMARY KEY (col1,col2)
```
Unique Key Constraints
Unique key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table.It is like Primary key but it can accept only one null value and it can not have duplicate values
Unique key constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] UNIQUE,
col2 datatype
); 
```
Unique key constraint at table level
```
 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] UNIQUE (col1,col2)
```

Foreign Key Constraints

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.
Foreign key constraint at column level


 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_column_name),
col2 datatype
);

Foreign key constraint at table level


 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_col)

Not Null Constraints
This constraint ensures that all rows in the database table must contain value for the column which is specified as not null means a null value is not allowed in that column.
Not Null constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] NOT NULL, 
col2 datatype
);
```
Not Null constraint at table level
```
 ALTER TABLE table_name
ALTER COLUMN col1 datatype NOT NULL
```

Check Constraints

This constraint defines a business rule on a column in the database table that each row of the table must follow this rule.
Check constraint at column level


 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] CHECK (condition), 
col2 datatype
);

Check constraint at table level


 ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(condition)

Different Types of SQL Server Triggers

Triggers are database object. Basically these are special type of stored procedure that are automatically fired/executed when a DDL or DML command statement related with the trigger is executed. Triggers are used to assess/evaluate data before or after data modification using DDL and DML statements. These are also used to preserve data integrity, to control server operations, to audit a server and to implement business logic or business rule.

Types of Triggers

In Sql Server we can create four types of triggers Data Definition Language (DDL) triggers, Data Manipulation Language (DML) triggers, CLR triggers and Logon triggers.

DDL Triggers
In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.
Example : If you are going to execute the CREATE LOGIN statement or the sp_addlogin stored procedure to create login user, then both these can execute/fire a DDL trigger that you can create on CREATE_LOGIN event of Sql Server.
We can use only FOR/AFTER clause in DDL triggers not INSTEAD OF clause means we can make only After Trigger on DDL statements.
DDL trigger can be used to observe and control actions performed on the server, and to audit these operations. DDL triggers can be used to manage administrator tasks such as auditing and regulating database operations.
DML Triggers
In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types
1. After Trigger (using FOR/AFTER CLAUSE)
  This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.
  Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints, like as primary key constraint, and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
2. Instead of Trigger (using INSTEAD OF CLAUSE)
  This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
  Example : If you insert record/row in a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints, such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
CLR Triggers
CLR triggers are special type of triggers that based on the CLR (Common Language Runtime) in .net framework. CLR integration of triggers has been introduced with SQL Server 2008 and allows for triggers to be coded in one of .NET languages like C#, Visual Basic and F#.
We coded the objects(like trigger) in the CLR that have heavy computations or need references to objects outside the SQL Server. We can write code for both DDL and DML triggers, using a supported CLR language like C#, Visual basic and F#. I will discuss CLR trigger later.
Logon Triggers
Logon triggers are special type of trigger that fire when LOGON event of Sql Server is raised. This event is raised when a user session is being established with Sql Server that is made after the authentication phase finishes, but before the user session is actually established. Hence, all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.
Synatx for Logon Trigger
```
CREATE TRIGGER trigger_name
ON ALL SERVER
[WITH ENCRYPTION]
{FOR|AFTER} LOGON
AS
sql_statement [1...n ] 
```

Syntax for Trigger


CREATE TRIGGER trigger_name
ON {table|view} 
[WITH ENCRYPTION|EXECUTE AS] 
{FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} 
[NOT FOR REPLICATION] 
AS 
sql_statement [1...n ]

trigger_name
This is the name of the trigger. It should conform to the rules for identifiers in Sql Server.
table|view
This is the table/view on which the trigger is to be created.
ENCRYPTION
This option is optional. If this option is specified, original text of the CREATE TRIGGER statement will be encrypted.
EXECUTE AS
This option is optional. This option specifies, the security context under which the trigger is executed.
FOR/AFTER
FOR/AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified.AFTER triggers cannot be defined on views.
INSTEAD OF
INSTEAD OF specifies that the trigger is Instead Of Trigger.
CREATE|ALTER|DROP|INSERT|UPDATE|DELETE
These keywords specify on which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used.
NOT FOR REPLICATION
Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.
AS
After this we specifies the actions and condition that the trigger perform.
sql_statement
These are the trigger conditions and actions. The trigger actions specified in the T-SQL statements.

Note

The name of a trigger should follow the rules for identifiers.
DML trigger can be composed by any T-SQL statements, except CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and RESTORE LOG statements.
You cannot create triggers against system tables or dynamic management views. Moreover, the TRUNCATE TABLE statement does not fire a trigger because this operation does not log individual row deletions.
If you use the DATABASE option, the scope of your DDL trigger will be the current database. If you use the ALL SERVER option, the scope of your DDL triggers to the current server.
AFTER triggers cannot be defined on views.
AFTER is the default, if FOR is the only keyword specified.

Different Types of SQL Server Views

Views are virtual tables that are compiled at run time. The data associated with views are not physically stored in the view, but it is stored in the base tables of the view. A view can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. Once you have created the view, you can query view like as table. We can make index, trigger on view.

In Sql Server we make views for security purpose since it restricts the user to view some columns/fields of the table(s). Views show only those columns that are present in the query which is used to make view.One more advantage of Views is, data abstraction since the end user is not aware of all the data present in database table.
Syntax for View


 CREATE VIEW view_name
AS
 select_statement[]

Types of Views

In Sql Server we have two types of views.

System Defined Views
System defined Views are predefined Views that already exist in the Master database of Sql Server. These are also used as template Views for all newly created databases. These system Views will be automatically attached to any user defined database.

We have following types of system defined views.
1. Information Schema View
  In Sql Server we have twenty different schema views. These are used to display information of a database, like as tables and columns. This type of view starts with INFORMATION_SCHEMA and after this view name.
```
 --Create a table 
create table Employee_Test
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Technology varchar(55),
 Emp_Sal decimal (10,2),
 Emp_Designation varchar(20)
)
--To view detailed information of the columns of table Employee_Test
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='Employee_Test'
```
2. Catalog View
  Catalog Views were introduced with SQL Server 2005. These are used to show database self describing information.
```
 select * from sys.tables 
```
3. Dynamic Management View
  Dynamic Management Views were introduced in SQL Server 2005. These Views give the administrator information of the database about the current state of the SQL Server machine. These values help the administrator to analyze problems and tune the server for optimal performance. These are of two types
  1. Server-scoped Dynamic Management View
    These are stored only in the Master database.
  2. Database-scoped Dynamic Management View
    These are stored in each database.
```
 --To see all SQL Server connections
SELECT connection_id,session_id,client_net_address,auth_scheme
FROM sys.dm_exec_connections 
```

User Defined Views

These types of view are defined by users. We have two types of user defined views.

Simple View

When we create a view on a single table, it is called simple view.


 --Now Insert data to table Employee_Test 
Insert into Employee_Test values ('Amit','PHP',12000,'SE');
Insert into Employee_Test values ('Mohan','ASP.NET',15000,'TL');
Insert into Employee_Test values ('Avin','C#',14000,'SE');
Insert into Employee_Test values ('Manoj','JAVA',22000,'SSE');
Insert into Employee_Test values ('Riyaz','VB',18000,'TH');
-- Now create view on single table Employee_Test
create VIEW vw_Employee_Test
AS
Select Emp_ID ,Emp_Name ,Emp_Designation
From Employee_Test


 -- Query view like as table
Select * from vw_Employee_Test

In simple view we can insert, update, delete data. We can only insert data in simple view if we have primary key and all not null fields in the view.


 -- Insert data to view vw_Employee_Test
insert into vw_Employee_Test(Emp_Name, Emp_Designation) values ('Shailu','SSE')
-- Now see the affected view
Select * from vw_Employee_Test


 -- Update data to view vw_Employee_Test
Update vw_Employee_Test set Emp_Name = 'Pawan' where Emp_ID = 6
-- Now see the affected view
Select * from vw_Employee_Test


 -- Delete data from view vw_Employee_Test
delete from vw_Employee_Test where Emp_ID = 6
-- Now see the affected view
Select * from vw_Employee_Test

Complex View

When we create a view on more than one table, it is called complex view.


 --Create another table
create table Personal_Info
(
 Emp_Name varchar(55),
 FName varchar(55),
 DOB varchar(55),
 Address varchar(55),
 Mobile int,
 State varchar(55)
)
-- Now Insert data
Insert into Personal_Info values ('G.Chaudary','22-10-1985','Ghaziabad',96548922,'UP');
Insert into Personal_Info values ('B.S.Chauhan','02-07-1986','Haridwar',96548200,'UK');
Insert into Personal_Info values ('A.Panwar','30-04-1987','Noida',97437821,'UP');
Insert into Personal_Info values ('H.C.Patak','20-07-1986','Rampur',80109747,'UP');
Insert into Personal_Info values ('M.Shekh','21-10-1985','Delhi',96547954,'Delhi');
-- Now create view on two tables Employee_Test and Personal_Info
Create VIEW vw_Employee_Personal_Info
As
Select e.Emp_ID, e.Emp_Name,e.Emp_Designation,p.DOB,p.Mobile
From Employee_Test e INNER JOIN Personal_Info p
On e.Emp_Name = p. Emp_Name


 -- Now Query view like as table
Select * from vw_Employee_Personal_Info

We can only update data in complex view.We can't insert,update data in complex view.


 --Update view 
update vw_Employee_Personal_Info set Emp_Designation = 'SSE' where Emp_ID = 3
-- See affected view
Select * from vw_Employee_Personal_Info

Note

We make views for security purpose since it restricts the user to view some columns/fields of the table(s).
One more advantage of Views is, data abstraction since the end user is not aware of all the data present in database table

Different Types of SQL Keys

A key is a single or combination of multiple fields in a table. Its is used to fetch or retrieve records/data-rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views.

Types of SQL Keys

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

Super Key
Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys.
Candidate Key
A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.
Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.
Primary Key
Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It can not accept null, duplicate values. Only one Candidate Key can be Primary Key.
Alternate key
A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.
Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.
Composite/Compound Key
Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.
Unique Key
Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. For more help refer the article Difference between primary key and unique key.
Foreign Key
Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key.
Example : We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

Defined Keys -


 CREATE TABLE Department 
(
 ID int PRIMARY KEY,
 Name varchar (50) NOT NULL,
 Address varchar (200) NOT NULL, ) 
CREATE TABLE Student 
(
 ID int PRIMARY KEY,
 RollNo varchar(10) NOT NULL,
 Name varchar(50) NOT NULL,
 EnrollNo varchar(50) UNIQUE,
 Address varchar(200) NOT NULL,
 DeptID int FOREIGN KEY REFERENCES Department(DeptID)
)

Basics of SQL Commands

SQL commands are a set of instructions that are used to interact with the database like Sql Server, MySql, Oracle etc. SQL commands are responsible to create and to do all the manipulation on the database. These are also responsible to give/take out access rights on a particular database

Sql Commands Category

We have different sql commands for different-different purpose. We can grouped Sql Commands into five major categories depending on their functionality.

Data Definition Language (DDL)
These SQL commands are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes etc. In this category we have CREATE, ALTER, DROP and TRUNCATE commands.

Note
1. Only with DDL commands we need to write keyword (like table, procedure, view, index, function) with the syntax of command.
2. These commands are used to create/modify the structure of the database object.
Example
```
 CREATE TABLE TABLE_NAME
(
COL1 VARCHAR(10),
COL2 VARCHAR(20),
);
--Here "TABLE" is a keyword that is used to create table "TABLE_NAME"
CREATE VIEW VIEW_NAME
AS
BEGIN
 SELECT * FROM EMP_TABLE
END
--Here "VIEW" is a keyword that is used to create VIEW "VIEW_NAME" 
```
Data Manipulation Language (DML)
These SQL commands are used to store, modify, and delete data from database tables. In this category we have INSERT, UPDATE, and DELETE commands.
Data Query Language (DQL)
These SQL commands are used to fetch/retrieve data from database tables. In this category we have only SEELCT command.
Transaction Control Language (TCL)
These SQL commands are used to handle changes which affect the data in database. Basically we use these commands with in the transaction or to make a stable point during changes in database at which we can rollback the database state if required. In this category we have SAVEPOINT, ROLLBACK and COMMIT commands.
Data Control Language (DCL)
These SQL commands are used to implement security on database objects like table,view,stored procedure etc. In this category we have GRANT and REVOKE commands.

Note
1. Grant Command : This command is used to give permission to specific users on specific database objects like table, view etc.
2. Revoke Command : This command is used to take out permission from specific users on specific database objects like table, view etc.

Different Types of SQL Joins

Sql joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join. Basically data tables are related to each other with keys. We use these keys relationship in sql joins. Also, refer the article SQL Joins with C# LINQ.

Types of Joins

In Sql Server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition.

Inner Join
Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as
```
 Select * from table_1 as t1
inner join table_2 as t2
on t1.IDcol=t2.IDcol 
```
Outer Join
We have three types of Outer Join.
1. Left Outer Join
  Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values. Syntax for Left outer Join is as :
```
 Select * from table_1 as t1
left outer join table_2 as t2
on t1.IDcol=t2.IDcol 
```
2. Right Outer Join
  Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values. Syntax for right outer Join is as :
```
 Select * from table_1 as t1
right outer join table_2 as t2
on t1.IDcol=t2.IDcol 
```
3. Full Outer Join
  Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables.If there are no columns matching in the both tables, it returns NULL values. Syntax for full outer Join is as :
```
 Select * from table_1 as t1
full outer join table_2 as t2
on t1.IDcol=t2.IDcol 
```
Cross Join
Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :
```
 Select * from table_1
cross join table_2 
```
Self Join
Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of Sql join.

Join Examples

Suppose we following three tables and data in these three tables is shown in figure. You can download the SQL script used in this article by using link.

Inner Join


 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price
FROM tblProduct AS t0
INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID
ORDER BY t1.OrderID

Inner Join among more than two tables


 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer
FROM tblProduct AS t0
INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID
INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID
ORDER BY t1.OrderID

Inner Join on multiple conditions


 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price, t2.Name AS Customer
FROM tblProduct AS t0
INNER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID
INNER JOIN tblCustomer AS t2 ON t1.CustomerID = t2.CustID AND t1.ContactNo = t2.ContactNo
ORDER BY t1.OrderID

Left Outer Join


 SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price 
FROM tblProduct AS t0 
LEFT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID 
ORDER BY t0.ProductID

Right Outer Join


 SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price 
FROM tblProduct AS t0 
RIGHT OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID 
ORDER BY t0.ProductID

Full Outer Join


 SELECT t1.OrderID AS OrderID , t0.ProductID , t0.Name , t0.UnitPrice , t1.Quantity AS Quantity , t1.Price AS Price 
FROM tblProduct AS t0 
FULL OUTER JOIN tblOrder AS t1 ON t0.ProductID = t1.ProductID 
ORDER BY t0.ProductID

Cross Join


 SELECT t1.OrderID, t0.ProductID, t0.Name, t0.UnitPrice, t1.Quantity, t1.Price
FROM tblProduct AS t0, tblOrder AS t1
ORDER BY t0.ProductID

Self Join

To understand Self Join, suppose we following two tables and data in these two tables is shown in figure.


 CREATE TABLE emp 
( 
id int NOT NULL primary key, 
name varchar(100) NULL, 
designation varchar(50) NULL, 
supid int foreign key references emp(id) ) -- In this table we have a Foreign key supid that references its own Primary Key id. We use it for Self Join 
INSERT INTO emp(id,name,designation) VALUES(1,'mohan','Manger') 
INSERT INTO emp(id,name,designation,supid) VALUES(2,'raj kumar','SE',1) 
 INSERT INTO emp(id,name,designation) VALUES(3,'bipul kumar','Manager') 
 INSERT INTO emp(id,name,designation,supid) VALUES(4,'mrinal kumar','SE',2) 
 INSERT INTO emp(id,name,designation,supid) VALUES(5,'jitendra kumar','SE',2)


 CREATE TABLE empinfo 
( 
id int primary key, 
 address varchar(50) NULL 
) 
INSERT INTO empinfo(id,address) VALUES(1,'Delhi') 
INSERT INTO empinfo(id,address) VALUES(2,'Noida') 
INSERT INTO empinfo(id,address) VALUES(4,'Gurgaon') 
INSERT INTO empinfo(id,address) VALUES(6,'Delhi') 
INSERT INTO empinfo(id,address) VALUES(7,'Noida')


 select e.id,e.name,e.supid as managerid, ei.name as managername from emp e left join emp ei on e.supid=ei.id; 
-- outer keyword is optional

CLOCK

Monday, 18 February 2013

Steps to change the server name for a SQL Server machine

Problem: There could be situation where you missed the database transaction log file(.LDF) and you have only data file (.MDF). You can attach the database using below solution.

----/

Transactions and Locks in SQL Server

SQL Integrity Constraints or Constraints

Column Level

Table Level

Types of SQL Constraints

Primary Key Constraints

Unique Key Constraints

Foreign Key Constraints

Not Null Constraints

Check Constraints

Different Types of SQL Server Triggers

Types of Triggers

DDL Triggers

DML Triggers

After Trigger (using FOR/AFTER CLAUSE)

Instead of Trigger (using INSTEAD OF CLAUSE)

CLR Triggers

Logon Triggers

Syntax for Trigger

trigger_name

table|view

ENCRYPTION

EXECUTE AS

FOR/AFTER

INSTEAD OF

CREATE|ALTER|DROP|INSERT|UPDATE|DELETE

NOT FOR REPLICATION

AS

sql_statement

Note

Different Types of SQL Server Views

Types of Views

System Defined Views

Information Schema View

Catalog View

Dynamic Management View

Server-scoped Dynamic Management View

Database-scoped Dynamic Management View

User Defined Views

Simple View

Complex View

Note

Different Types of SQL Keys

Types of SQL Keys

Super Key

Candidate Key

Primary Key

Alternate key

Composite/Compound Key

Unique Key

Foreign Key

Basics of SQL Commands

Sql Commands Category

Data Definition Language (DDL)

Note

Example

Data Manipulation Language (DML)

Data Query Language (DQL)

Transaction Control Language (TCL)

Data Control Language (DCL)

Note

Different Types of SQL Joins

Types of Joins

Inner Join

Outer Join

Left Outer Join

Right Outer Join

Full Outer Join

Cross Join

Self Join

Join Examples

Inner Join

Inner Join among more than two tables

Inner Join on multiple conditions

Left Outer Join