What is primary key and Candidate Key?

 Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table

What are System database in SQL Server?

  There are mainly four system database:
1.Msdb
2.Master.
3.Model.
4.Tempdb


 Let us see one by one.

Master Database Master database is system database. It contains information about server’s configuration. It is a very important database and important to backup Master database. Without Master database, server can't be started. MSDB Database It stores information related to database backups, DTS packages, Replication, SQL Agent information, SQL Server jobs.

TEMPDB Database

It stores temporary objects like temporary tables and temporary stored procedure.

Model Database

It is a template database used in the creation of new database.

Resourse Database
The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

How to get which Process is Blocked in SQL SERVER ?

 There are two ways to get this sp_who and sp_who2 . You cannot get any detail about the sp_who2 but its provide more information than the sp_who . And other option from which we can find which process is blocked by other process is by using Enterprise Manager or Management Studio, these two commands work much faster and more efficiently than these GUI-based front-ends.

What is synonyms ? Is it useful?

SQL Server 2005 has introduced synonyms which enable the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in your environment.  In short, this means that the original object that is referenced in all of your code is really using a completely different underlying object, but no coding changes are necessary.  Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.

SYNONYM's can be very useful and can be created for

• Tables
• Views
• Assembly Stored Procedures, Table Valued Functions, Aggregations
• SQL Scalar Functions
• SQL Stored Procedures
• SQL Table Valued Functions
• SQL Inline-Table-Valued Functions
• Local and Global Temporary Tables
• Replication-filter-procedures
• Extended Stored Procedures

Benefits

• SYNONYMs provide a layer of abstraction over the referenced object
• Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
• Provides flexibility for changing the location of objects without changing existing code.
• SYNONYMs can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
• SYNONYMs can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations

• SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
• Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.
• Obviously consumes possible object names, as you can not create a table with the same name of a synonym
• The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.
• SYNONYM can not be referenced in a DDL statement

Importance of SET NOCOUNT in Stored Procedures

                    Today , I am going to share one of important concept .It sounds very small but it is very important.Whenever we write any procedure and execute it a message appears in message window that shows number of rows affected with the statement written in the procedure.

e.g

CREATE PROCEDURE Test_no_count

AS

    SET NOCOUNT OFF

  BEGIN

      SELECT 1

      SELECT 2

      SELECT 3

      SELECT 4

  END 

GO

Exec Test_no_count

GO

Go
    Default value is SET NOCOUNT OFF.We don't need to specify OFF.For demo purpose I have specified explicitly.

If it is off then it will return affected row message as marked circle sometime affected row count will be multiple  of 100s so this message will creates an extra overhead on the network. 

By using SET NOCOUNT we can remove this extra overhead from the network, that can actually improve the performance of our database and our application.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

GO

CREATE PROCEDURE Test_no_count

AS

    SET nocount On

  BEGIN

      SELECT 1

      SELECT 2

      SELECT 3

      SELECT 4

  END 

GO

Exec Test_no_count 

GO

     Important thing is @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

           SET NOCOUNT ON statement can be useful in store procedures. SET NOCOUNT ON statement into store procedures can reduce network traffic, because client will not receive the message indicating the number of rows affected by T-SQL statement. Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Disable and Enable the Foreign Key Constraint on the table in SQL Server

     Its  interesting concept but need to know while working on huge database.Recently my friend attended one of the interview .He was rejected there.Interviewer asked him one question which he found difficult while attending that interview.So he asked me to help him to understand the solution.
   He said I  never heard of disabling and enabling constraints but I heard about triggers.Sometime we work on test environment while development. Whenever we deliver any feature to QA team .They test lot of positive and negative scenario for regression.
Let us assume there are some negative cases which we have to test  .Let say we have two table
1.Table having primary key .
2. Table having Foreign key.
e,.g

 CREATE TABLE TEST_PRIMARY
  (
     ID INT PRIMARY KEY
  )

GO

CREATE TABLE TEST_FOREIGN
  (
     ID     INT IDENTITY (1, 1),
     PVALUE INT,
     FOREIGN KEY (PVALUE) REFERENCES TEST_PRIMARY(ID)
  )

GO

INSERT TEST_PRIMARY
       (ID)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

GO

INSERT INTO TEST_FOREIGN
            (PVALUE)
VALUES      (1),
            (2),
            (3)

GO
  Now QA want to test  some negative case for example they are interested to know how system will behave if  constraints are not working properly means there are no constraints on table.

e.g

Syntax :
EXEC Sp_msforeachtable   "ALTER TABLE <table Name> NOCHECK CONSTRAINT all"
Query :
EXEC Sp_msforeachtable   "ALTER TABLE  TEST_FOREIGN NOCHECK CONSTRAINT all" 

Now if  you will try to  insert value into TEST_FOREIGN other than reference value it will allow.

e.g.

INSERT INTO TEST_FOREIGN
            (PVALUE)
VALUES      (4),

  Normally it should not allow.

Now  we can enable constraints back again
e.g
EXEC sp_msforeachtable "ALTER TABLE <table name> WITH CHECK CHECK CONSTRAINT all"

EXEC sp_msforeachtable "ALTER TABLE Test_foreign WITH CHECK CHECK CONSTRAINT all"

Note : before enabling constraint we need to delete data which are conflicting  primary key relation from TEST_FOREIGN table.

Recent execution of Procedures in SQL Server

         Recently I was working on one functionality. Interesting thing was I did not know it fully.
I need to complete it as early as .I can say it was enhancement .When term come as enhancement then as SQL developer we know it involves  lots of R&D if application is complex.It was same scenario which I caught.I  didn't  know anything but need to complete as early as possible.
      So I thought its time to do smart work not hard work!!!
 I knew one thing everything in that functionality was handling through procedures.So I thought If I will get to know flow of execution of procedures I mean order of calling of procedures .If I will get that I can easily capture list of procedures and following flow I can get sequence of procedures .
    I tried and it worked for me.I got list of  procedure and modified code as per  requirement .Unit tested and done all regression it passed all cases.So sometime smartwork also work.
I will demonstrate as  follow
e.g
create procedure X
as select 1
Go
create procedure Y
as select 1
GO
exec x
GO
exec y

  I have created two procedure named X and Y .I have executed this procedures .

I have used following query to get result

SELECT    db_name(DMV.database_id), 

  OBJECT_NAME(object_id, database_id) AS proc_name, 

  DMV.last_execution_time

  FROM sys.dm_exec_procedure_stats AS DMV

  where db_name(DMV.database_id) is not null

   order by   DMV.last_execution_time desc