Hi Friends,

This is very interesting topic and one of my favourite. And very useful most of the times, atleast for me. 

Ranking Functions are 4 in number.

(1) Rownumber

While using row number functionwith 'partition by' it will give count of repeatetions considering the value in the 
row.

Example: select *,ROW_NUMBER() over (partition by id order by id) as [RowNumber] from ForRankFunctions

(2) Rank

While using rank function it will consider the row value and will give the same rank for repeated numbers. And the count will start considering the eaten up values. 

Example: select *,Rank() over (order by id) as [Rank] from ForRankFunctions


(3) Dense Rank

While using dense rank function it will consider the row value and will give the same rank for repeated numbers. 
And the count will start from the next value. 

Example: select *,dense_Rank() over ( order by id) as [DenseRank] from ForRankFunctions 

(4) Ntile

Ntile divides the # of rows into # of groups mentioned in parenthesis. And will rank the entire group with same #.

Example: select *,ntile(5) over (order by id)as [Ntile] from ForRankFunctions 

--Create table with below structure

create table ForRankFunctions (id int,pname varchar(20))

--Insert below mentioned values

 insert into ForRankFunctions values (1,'pen'),(2,'paper'),(3,'pencil'),(1,'blue pen'),(3,'blue pencil'),(4,'eraser'), (3,'green pencil'),(4,'small eraser'),(5,'ruler') 

Execute all the above examples:

This is how it will look like: 

Check all the rank functions in a single query as below: 

select *,ROW_NUMBER() over (order by id) as [RN],Rank() over (order by id) as [RANK],dense_Rank() over ( order
by id) as [DENSERANK], ntile(5) over (order by id) AS [NTILE]from ForRankFunctions

Below is how I used rank functions to find out duplicates using CTE (common table expression).

with CTE_ForRankFunctions( id,pname,Rnum)
as
(
select id,pname,ROW_NUMBER() over (partition by id order by id,pname) from ForRankFunctions

)
select * from CTE_ForRankFunctions where Rnum!=1

Here are few control-flow statements that you can use in SQL Server's T-SQL batches and stored procedures. These statements let you control the execution flow within a T-SQL batch or stored procedure, and they let you use T-SQL to execute complex programming tasks.

1) CASE:

CASE provides a structured method of evaluating a list of options and then returning a single value. You can use the CASE statement alone or within a SELECT statement.

Example: 

SELECT au_fname, au_lname,
CASE state
WHEN 'OR' THEN 'Oregon'
END AS StateName
FROM authors

2) WHILE- BREAK - CONTINUE

WHILE is a powerful T-SQL control-flow statement. The WHILE statement causes repeated execution of a statement or block of statements while a given condition is true. You can specify the optional BREAK and CONTINUE keywords to exit from the while loop or cause the loop to continue.

WHILE @sal<2000
BEGIN
update emp_table set sal=sal+1000 
END

3) RETURN

RETURN lets you exit from a T-SQL batch or stored procedure. You can specify an optional integer variable with RETURN to pass a status value to the calling procedure, which can evaluate the return code and perform different actions depending on the results of the T-SQL batch or stored procedure.

Return @return_code

4) WAITFOR

WAITFOR lets you delay the execution of a T-SQL batch either for a given amount of time (when you specify the DELAY keyword) or until a specified system time (when you specify the TIME keyword).

WAITFOR TIME '23:00'
WAIT FOR DELAY ''00:01:00''

5) BEGIN END

BEGIN-END lets you group T-SQL statements and execute multiple statements as a unit.

BEGIN
SET @ErrorNumber = @@ERROR
PRINT 'Error encountered'
END

6) IF ELSE

The IF statement lets you test a variable's contents and conditionally execute the T-SQL statements that follow, depending on the test's results. When the IF test evaluates to false, the optional ELSE portion of the statement lets an alternative T-SQL statement execute.

IF (@@Error 0)
ROLLBACK
ELSE
COMMIT
END

7) GO TO

GOTO is a basic T-SQL control-flow statement. It causes the execution of a T-SQL batch to branch to the label specified in the line with the GOTO statement.

GOTO error_condition

If you ever wanted to check

Aggregate function (CLR)
Check constraint
Default constraint
Foreign key
SQL scalar function
SQL inline table valued table valued function
Internal table
SQL stored procedure
Primary key constraint
Table valued function
SQL DML trigger
Table type
Table
Unique constraint
View
Extended stored procedure

above mentioned things in a DB, this is the place you chould check in 'sys.objects'.

Sample query to help you guys: 

GO
SELECT *
FROM sys.objects
WHERE parent_object_id = (OBJECT_ID('index_check')) 
AND type IN ('C','F', 'PK'); 
GO

Explanation: With above query you are looking for specific constraints like C - Check constraint, F - Foreign key constraint , PK - Primary key constraint  with the help of object id function. You can pass object name to object id function and match that with parent_object_id from sys.objects to get the details of the table you need. 

Question is how will understand what the types of constraint are existing or what are the short names for them like PK is primary key. Few mostly used types:

AF: Aggregate function (CLR)
C : Check constraint
D : Default constraint
F: Foreign key
FN : SQL scalar function
IF : SQL inline table valued table valued function
IT : Internal table
P : SQL stored procedure
PK : Primary key constraint
TF : Table valued function
TR : SQL DML trigger
TT : Table type
U : Table
UQ : Unique constraint
V : View
X : Extended stored procedure

Let me know if I can be of some help.

-- A learning and contributing member of SQL Server Community

SP_WHO2

Running sp_who2 on the affected server reveals that there are indeed blocked processes, as is evidenced by the BlkBy field in the results. See from below screenshot

With the above stored procedure you will know which SPID is blocked and which SPID is blocking it. What are we going to do with this. Here is what we can do:

Pass the spid to DBCC INPUTBUFFER(SPID) example: DBCC INPUTBUFFER(56) 

INPUTBUFFER, allows you to see the underlying query that a specific SPID is executing. 

Following above practice works well but when the query is little big, you might not be comfortable reading it. 

Returning the results to text, which is simply a matter of clicking the "Results to Text" button on the Management Studio toolbar, usually delivers better results. 

KIILING THE OFFENDING QUERY

At this point, my goal is simply to kill the blocking SPID so that any queries backing up behind it can start to flow through. So, after confirming that my manager has signed off to kill the SPID.

After executing KILL <SPID>, it will show a normal success command as below. 

 In the above query red circles are the three SPIDs running on my server. 

SPID '51' has blocked SPID '56' so we have killed 51. Red rectangle is the normal sucess command which you see after a successful murder. 

USING SP_LOCK

To know which query is causing the pain and which query makes you breath normal use this SP_LOCK.

You might need to know this:

Some common lock types are:

RID – single row lock
KEY – a range of keys in an index
PAG – data or index page lock
EXT – Extent Lock
TAB – Table Lock
DB – Database Lock

In addition to lock types that refer to resources or objects that can be locked, SQL Server has common lock modes:

S – Shared lock
U – Update Lock
X – Exclusive lock
IS – Intent shared
IU – Intent Update
IX – Intent Exclusive
BU – Bulk update

Note: The SQL Server 2005, and above, equivalent of sp_lock would be the DMV sys.dm_tran_locks.

I have a very intelligent friend Maninder Singh, who behaves as if he forgets everthing but not. Everytime he saw me reading a book on SQL Server, he used to ask me "why are you reading a book when you have lot of eBooks?" and I feel that is correct most of the times but not everytime because sometimes while reading books you will get to read some thing new or some thing which you might be thinking has never existed.

My friend this article is dedicated to you.

Before getting straight to the topic, let me explain what my terminology is, just to be on the same page.

When I mention . . .

Running: A query which is being executed on a CPU is called Running query. This time is called CPU Time.

Runnable: A query which is ready to execute and waiting for resources is called Runnable query. The amount of time in this state is the Signal Wait Time.

Suspended: A query which is waiting, for any reason to be converted to runnable, is a suspended query.The time spent in this is called Query Wait Time.

Here is the Meat:
When you were on a cofee break there might have been few wait stats and you want to find them when you are back. MS people are so kind and gave us few DMV's to work around. Here is one

select * from sys.dm_os_wait_stats

This DMV will collect all the information since SQL server has started. but there would be lot of tasks which did not wait for other process and thus their wait_stat will be 0. To filter them up, you can write like this:

select * from sys.dm_os_wait_stat where wait_time_ms>0

After you run above query, you will find 5 columns and you might be interested to know them. Here is
the explanation:

1) Wait_type: This is the name of wait type. There can be three different types of wait types- resource, queue & external.
2) waiting_tasks_count: This incremental counter is a good indication of how frequently the wait is happening. It is quite possible that the wait time is considerably low but the frequency of wait time is higher.
3) wait_type_ms: This is total accumulated time is milliseconds for any type of wait. This is a total wait time and includes runnable, running and suspended time.
4) max_wait_time_ms: This indicates max time ever encountered for that particular task. With this we can estimate the intensity of the wait time the task took.
5)signal_wait_time_ms: This is the time a thread spends the time marked as runnable, before it gets to the running state.

Further tuning it, if we arrange signal_wait_time_ms in descending order, we will get the most expensive task.

select * from sys.dm_os_wait_stats where wait_time_ms>0 order by signal_wait_time_ms desc

More coming .....

I have a friend Anil, who is SQL DBA working in Hyderabad and wanted to learn Indexes and was confused where to start and how to proceed. This is what I suggested him:

(1) Understanding Indexes
  Index Basics
  Types of Indexes
(2) Designing Indexes
  Index design basics
  General Index design basics
  Clustered index basics
  Non-clustered index basics
  Unique index basics
  Filtered index basics
(3) Implementing indexes
  (a) Creating indexes
  (b) Modifying indexes
    (i)Disabling Idexes
    (ii)renaming indexes
    (iii)Reorganizing and rebuilding indexes
    (iv)Setting Index options
  (c)Determining Index space requirements
  (d)Dropping indexes
  (e) Viewing index information (using DMV's)
(4) Optimizing Indexes
  Reorganizing and rebuilding indexes (repeated as it comes    under both)
  Fill factor
  Performing index operations online
  Configuring parallel index operations
  bulk copy and indxes
  Choosing a recovery model for Index operations

PS: All the above hierarchy is suggested by Microsoft. 

Let me start this way today, Million Rupee question as I am from INDIA and our currency is Rupee but not dollar :)

Microsoft says
(1) NULL is undefined, nothing or unknown
(2) NULL is not equal to NULL as we cannot measure NULL and thus we cannot compare two NULLS.

Agreed

Check this out: : Lets say I am having a unique constraint on a column and want to insert NULL values. As per MS SQL Server, I cannot insert two NULL values as NULL is already existing. Question is how Microsoft comparing two NULL values?

PS: Accepted that MS doesn't follow ANSI in regards with this but expecting some better answer from MS.

#1)performance <30 re-organize index
performance >30 re-build index

#2) ntext doesn't store data in tables it stores data as large objects.

#3) For LOB column, first 100 bytes and last 100 bytes are read to update the statistics.

#4) Start UPDATE STATISCTICS with FULLSCAN, you can go home eat, sleep, wake watch a movie and come back to office and wait for it to complete. If you have data types like TEXT, NTEXT, IMAGE, VARBINARY and you need to run UPDATE STATS better run it with out FULLSCAN.

I was asked to create few logins. I thought to go through the theoretical part before I start working on that. 

Here is what I found:

Sysadmin: Members with Sysadmin role have all the access to do anything in the server.

Serveradmin: Members with Serveradmin role have server wide access to do server configuration option like shutdown or restart. 

Securityadmin: Members with this role manage logins and their properties. They can GRANT, DENY & REVOKE server level permissions. They can also do database level login configuration if they have access to that database. They can also edit passwords of SQL server logins.

Processadmin: Members with this role can end process that are running in instance of SQL server. 

Setupadmin: Members with this privilege can add or remove linked servers.

Bulkadmin: As the name says members with this role can do the bulk insert. 

Diskadmin: Members with this role can manage disk files.

Dbcreator: Members with this access can create, alter, drop  and restore databases.

Public: Every SQL server login belongs to the public role. This comes by default when we create a login for Windows authenticated or SQL authenticated. This is can modified like GRANT, DENY or REVOKED.

WANT TO FEEL LIKE GOD ... Complete below explanation, you will feel like one.

To check all the above roles in SQL server, you are supposed to run this stored procedure.

sp_helpsrvrole

To check who can do what, run this 

sp_srvrolepermission

Now you might want to check who is what in your server and edit (provided you are a sysadmin or securityadmin), then run the below mentioned stored procedure.

sp_helpsrvrolemember

To Check the list of server level permissions:

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

Scenario: Let us say I am logged into a server and want to check if I am a sysadmin, simple way you can check is:

if (IS_SRVROLEMEMBER ('sysadmin'))=1
print'current user is a sysadmin'
else if (IS_SRVROLEMEMBER ('sysadmin'))=0
print'current user is NOT a sysadmin'
else if (IS_SRVROLEMEMBER ('sysadmin')) is null
print'ERROR: server role mentioned is not valid'

You might not want to check your server role evertime and what if you want to some specific login server role. Here is a snippet:

if (IS_SRVROLEMEMBER ('sysadmin','login_from_gui'))=1
print'current user is a sysadmin'
else if (IS_SRVROLEMEMBER ('sysadmin','login_from_gui'))=0
print'current user is NOT a sysadmin'
else if (IS_SRVROLEMEMBER ('sysadmin','login_from_gui')) is null
print'ERROR: server role mentioned is not valid'

To make you understand the return types, here is the explanation:

If return value is 1, login is a member of that specific role.
If return value is 0, login is not a member of that specific role. 
If return value is NULL, login is a not valid or you do not have permissions. 

Hello everyone. Today I have seen something, which is very new to me and I have never expected such simple but interesting way of accesing tables for testing purpose.  

And as soon as I saw, I wanted to use it and share it. 

With this blog page, I completed both my tasks. 

select * from product, sales

Catch here is::: defining two tables after FROM clause which are being seperated with comma. 

Hope you have seen something interesting ! 

DELETE logs the data for each row affected by the statement in the transaction log and physically removes the row from the file, one row at a time. The recording of each affected row can cause your transaction log grow massively if you are deleting huge numbers of rows. However, when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover the database to the most recent state, should a problem arise. The fact that each row is logged explains why DELETE statements can be slow.

Myth)  TRUNCATE does not log the data.

TRUNCATE is faster than DELETE due to the way TRUNCATE “removes” rows. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, BOL refers to TRUNCATE operations as “minimally logged” operations.This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, BOL refers to TRUNCATE operations as “minimally logged” operations. You can use TRUNCATE within a transaction,and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.

Some limitations do exist for TRUNCATE

· You need to be db_owner, ddl_admin, or owner of the table to be able to fire a TRUNCATE statement.

· TRUNCATE will not work on tables, which are referenced by one or more FOREIGN KEY constraints.

So if TRUNCATE is so much faster than DELETE, should one use DELETE at all? Well, TRUNCATE is an all-or-nothing approach. You can’t specify just to truncate those rows that match a certain criteria. It’s either all rows or none.

When you select an interger column to be IDENTITY and you think there would a lot of data getting inserted and might overflow. Here are the precautinary measures you could choose. 

1) Changing the integer column as BIGINT. (I believe this is the easiest)

2) Using decimal DT instead of integer.

Example:

**Actual table structure***

create table [tweaking_identity_scope](id int identity(1,1),name varchar(10) default 'SATISH')

This would fail after you reach 2.147.483.647 value in your identity column.

**Proposed table structure***

create table [tweaking_identity_scope](id decimal(38,0) identity(-9999999999999999999999999999999999999,1),name varchar(10) default 'SATISH')

Did you ever see any error while working on MS SQL Server?

Ever wanted to know where are they saved?

Want to manipulate them according to your need?

Want to know how many pre-defined error's are saved?

Tadaa ..You are in correct place...

Check this table sys.messages and here is the query

select * from sys.messages where message_id=232 and language_id=1033

Note: Message id should be your error number and rest the same.

Do comment once you finish reading. :) :) 

/****CREATE TABLE SALES*******/
GO
CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
GO
INSERT INTO Sales VALUES(1, 2005, 12000),(1, 2006, 18000),(1, 2007,
25000),(2, 2005, 15000),(2, 2006, 6000),(3, 2006, 20000),(3, 2007, 24000)
GO

SELECT EmpId, Yr, SUM(Sales) AS Sales --11 rows
FROM Sales
GROUP BY ROLLUP(EmpId, Yr)
GO

SELECT EmpId, Yr, SUM(Sales) AS Sales --14 rows
FROM Sales
GROUP BY CUBE(EmpId, Yr)
GO

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), ())

GO

/**
EmpId Yr Sales
----------- ----------- ---------------------
1 2005 12000.00
1 2006 18000.00
1 2007 25000.00
1 NULL 55000.00
2 2005 15000.00
2 2006 6000.00
2 NULL 21000.00
3 2006 20000.00
3 2007 24000.00
3 NULL 44000.00
NULL NULL 120000.00

This query explicitly asks SQL Server to aggregate sales by employee
and year, to aggregate by employee only, and to compute the total
for all employees for all years. The () syntax with no GROUP BY columns
denotes the total. Similarly, we can express the above CUBE query by
asking SQL Server to compute all possible aggregate combinations**/

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())

/**
EmpId Yr Sales
----------- ----------- ---------------------
1 2005 12000.00
2 2005 15000.00
NULL 2005 27000.00
1 2006 18000.00
2 2006 6000.00
3 2006 20000.00
NULL 2006 44000.00
1 2007 25000.00
3 2007 24000.00
NULL 2007 49000.00
NULL NULL 120000.00
1 NULL 55000.00
2 NULL 21000.00
3 NULL 44000.00
**/

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))

/**
EmpId Yr Sales
----------- ----------- ---------------------
1 2005 12000.00
1 2006 18000.00
1 2007 25000.00
1 NULL 55000.00
2 2005 15000.00
2 2006 6000.00
2 NULL 21000.00
3 2006 20000.00
3 2007 24000.00
3 NULL 44000.00
**/

/**We can skip certain rollup levels. For example, we can compute
the total sales by employee and year and the total sales for all
employees and all years without computing any of the intermediate
results**/

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), ())

/**
EmpId Yr Sales
----------- ----------- ---------------------
1 2005 12000.00
1 2006 18000.00
1 2007 25000.00
2 2005 15000.00
2 2006 6000.00
3 2006 20000.00
3 2007 24000.00
NULL NULL 120000.00
**/

Case expression evaluates a list of conditions and returns one of multiple possible result expressions.

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.

• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Here is a small example:

Create table ib_import_event(ib_import_event_id int primary key identity(1,1),
trasform_status char(2),
external_data varchar(max)
transform_date smalldatetime default getdate()
)
GO
select ib_import_event_id,transform_status=
case
when transform_status=0 then 'Connectivity issue'
when transform_status=1 then 'Application no ready'
when transform_status=2 then 'message successful'
when transform_status=3 then 'message transmission failed'
when transform_status=9 then 'Import linking'
when transform_status=99 then 'manual liking necessary-check ib_observation_info table'
end,
external_data
from
ib_import_events
where ib_format_id=10 --Respective format id
GO

I would say that cross apply is like inner join and outer apply is like left outer join. 

The only difference is that inner join\left outer join can be used with only tables where as cross apply\outer apply can also be used with table value expressions.

Let me ask you this, what if a function is returning a table and you want to join returning table to a different table. Would you be able to acheive this through joins ...nope ..

So everytime you want to join table value expressions with tables, you can use cross apply\outer apply.

/*Department table*/
CREATE TABLE [Department](
[DepartmentID] [int] NOT NULL PRIMARY KEY,
[Name] VARCHAR(250) NOT NULL,
) ON [PRIMARY]
/*Inserting values into Department table*/
INSERT [Department] ([DepartmentID], [Name])
VALUES (1, N'Engineering')
INSERT [Department] ([DepartmentID], [Name])
VALUES (2, N'Administration')
INSERT [Department] ([DepartmentID], [Name])
VALUES (3, N'Sales')
INSERT [Department] ([DepartmentID], [Name])
VALUES (4, N'Marketing')
INSERT [Department] ([DepartmentID], [Name])
VALUES (5, N'Finance')
GO
/*Creating Employee table*/
CREATE TABLE [Employee](
[EmployeeID] [int] NOT NULL PRIMARY KEY,
[FirstName] VARCHAR(250) NOT NULL,
[LastName] VARCHAR(250) NOT NULL,
[DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),
) ON [PRIMARY]
GO
/*Inserting values in Employee table*/
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (1, N'NIKHIL', N'VERMA', 1 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (2, N'MAHESH', N'METTAPALLI', 2 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (3, N'SIDD', N'BULUSU', 3 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (4, N'SWATI', N'SING', 3 )
GO

CREATE FUNCTION dbo.fn_EmployeeOfADepartment(@DeptID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Employee E
WHERE E.DepartmentID = @DeptID
)
GO

SELECT * FROM Department D
CROSS APPLY dbo.fn_EmployeeOfADepartment(D.DepartmentID)
GO

SELECT * FROM Department D
OUTER APPLY dbo.fn_EmployeeOfADepartment(D.DepartmentID)
GO

Use satishTempDb

Go

/*Our base table*/

Create table m1(id int,name varchar(20))

GO

/*Creating Audit table for m1*/

create table m1_audit (
id int,
name varchar(20),
Operation char(1),
Trigger_table char(1),
datetime_inserted smalldatetime default getdate())

GO

/*Trigger on table m1 which would insert all the rows inserted in table m1 into m1_audit table*/

create trigger [trigger_m1]
on
m1
after insert
as
begin
insert into dbo.m1_audit(id,name,Operation,Trigger_table) select Id,name,'I','I' from inserted
end
GO

/*Check trigger activity*/

insert into m1 values (2,'bhagat')

GO

select * from m1_audit

/*You entered data in m1 table but could find data in table m1_audit*/

/*Copy rights reserved*/

Create new file                      Ctrl+N

Open File                              Ctrl +O

Display Query Designer          Ctrl+Shift+Q

Close menu or dialog box       Esc

Toggle full screen mode         Shift+Alt+Enter

Display Object Explorer          F8

Display Registered Servers      Ctrl+Alt+G

Display Solution Explorer        Ctrl+Alt+L

Set or Remove Bookmark        Ctrl+K

Next Bookmark                       Ctrl+N

Previous Bookmark                 Ctrl+P

Clear Bookmark                      Ctrl+L

Save all                                  Ctrl+Shift+S

Go to Line                              Ctrl+G

I am tired ....I know you can find them in google but then wanted to remind that we have helpers like above ....Now go N Google for shortcuts ...

--You can run following query and check whether it is enabled for any database.
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO

--You can run this stored procedure in the context of each database to enable CDC at database level.
--(The following script will enable CDC in AdventureWorks database. )
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_db
GO

--First run following query to show which tables of database have already been enabled for CDC.
USE AdventureWorks
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO

--Following script will enable CDC on HumanResources.Shift table.
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL
GO

--Before we start let’s first SELECT from both tables and see what is in them.
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Shift
GO
USE AdventureWorks
GO
SELECT *
FROM cdc.HumanResources_Shift_CT
GO

--Lets run an INSERT operation on the table HumanResources.Shift.
USE AdventureWorks
GO
INSERT INTO [HumanResources].[Shift]
([Name],[StartTime],[EndTime],[ModifiedDate])
VALUES ('Tracked Shift',GETDATE(), GETDATE(), GETDATE())
GO

--To illustrate the effects of an UPDATE we will update a newly inserted row.
USE AdventureWorks
GO
UPDATE [HumanResources].[Shift]
SET Name = 'New Name',
ModifiedDate = GETDATE()
WHERE [Name] = 'Tracked Shift'
GO

--To illustrate the effects of an DELETE we will delete a newly inserted row.
USE AdventureWorks
GO
DELETE
FROM [HumanResources].[Shift]
WHERE [Name] = 'New Name'
GO

--Before we end let’s SELECT from both tables and see what is in them.
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Shift
GO
USE AdventureWorks
GO
SELECT *
FROM cdc.HumanResources_Shift_CT
GO

--Checking details of CDC
USE AdventureWorks;
GO
EXEC sys.sp_cdc_help_change_data_capture
GO

--Disabling Change Data Capture on a table
USE AdventureWorks;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@capture_instance = N'HumanResources_Shift';
GO

--Disable Change Data Capture Feature on Database
USE AdventureWorks
GO
EXEC sys.sp_cdc_disable_db
GO

Let us assume you have two tables as below:

table A

id   name

1    Ram

2    Laxman

3    Sita

table B

id     name

3      Krishna

4      Bheem

5      Arjun

Q) Write a join with which will get you the details of records 1 & 2 only. (Need to do this in a minute)

Note:This should be strictly to be done in one minute.

If did not answer this in a minute, hold on, please start referring basics. 

Do comment after you answer?

Hi I am Satish ... from Visakhapatnam ...  worked for a couple of years at  Hyderabad .. moved to chennai ...2morrow donno ...

I am a database & BI developer and has lot of passion towards it. 

Lot to do, lot to explore. 

My interests are to read books, play cricket and table tennis. Forgot to mention that I love watching movies. 

I THINK

* It is very important to spend time with your family as much as you can. 

* It is not important where you are, what matters is 'whom you are with'......enough.... meat here is FRIENDS are the essence of the life. You are always lucky if you have a good friend. I guess you are lucky as well coz u have "me".

* To update my blog regularly.

I LIKE

* Watching movies

* Reading

* Adventure tours

* Drives

* Sleeping