Comma Separated values

-- Sample Table 

create table #Emp(Name varchar(10),Skills varchar(max))

insert into #Emp values('Ramesh','Hadoop,SQL,DBA')
insert into #Emp values('Arjun','SQL,MSBI')
insert into #Emp values('Mohan','Java')

select * from #Emp

Name  Skills
---------------------------
Ramesh  Hadoop,SQL,DBA
Arjun  SQL,MSBI
Mohan  Java

-- Code to Display below output

Name  Skill
---------------------
Arjun  MSBI
Arjun  SQL
Mohan  Java
Ramesh  DBA
Ramesh  Hadoop
Ramesh  SQL


SELECT DISTINCT Name,LTRIM(RTRIM(i.value('.', 'VARCHAR(MAX)'))) AS SKILL into #sample
FROM
(
SELECT Name,Skills
,CAST('' + REPLACE(Skills, ',', '') + '' AS XML) AS Des
FROM #Emp
) List
CROSS APPLY Des.nodes('//i') x(i)


select * from #sample 

-- for the above output, Code to Display below output

Name  Skills
---------------------------
Ramesh  Hadoop,SQL,DBA
Arjun  SQL,MSBI
Mohan  Java

SELECT Distinct Name
,STUFF((SELECT ','+SKILL FROM #sample f where f.Name=s.Name FOR XML PATH('')),1,1,'') AS Skills
from #sample s

Differences....

Differences Between Funtion and Procedure

• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• functions can be called from Procedure whereas Procedures cannot be called from function.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a function.
• We can go for transaction management in Procedure whereas we can't go in function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored Procedures cannot be.
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Differences Between Char , Varchar and Nvarchar

• Char datatype is used to store fixed length of characters. For example , if we declared char(10) it will allocates memory for 10 characters. Once we insert only 6 characters of word then only 6 characters of memory will be used and other 4 characters of memory will be wasted.
• Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. For example, if we declared varchar(10) it will allocates memory of 0 characters at the time of declaration and Once we insert only 6 characters of word it will allocate memory for only 6 characters.
• Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

Differences Between Primary Key and Unique Key

• Primary Key creates clustered Index by default , where as Unique Key Create NoN-Clustered Index
• Primary Key doesn't allow NULLs , where as Unique Key allows only one NULL value

Differences Between Delete ,Drop and Truncate

DELETE

• Delete is a DML Command
• We can use where clause to filter data, It deletes specified data if where condition exists
• DELETE statement is executed using a row lock, each row in the table is locked for deletion
• DELETE retain the identity
• Delete activates a trigger because the operation are logged individually
• Slower than truncate because, it keeps logs
• Rollback is possible

DROP

• DROP is a DDL Command
• Removes all rows and also the table definition, including indexes, triggers, grants, storage parameters
• No filter criteria allowed, removes all rows and No triggers fired
• DROP ANY privilege on a specific table cannot be granted to another user or role.
• Drop operation cannot be rolled back in any way as it is an auto committed , while statementDelete operation can be rolled back and it is not auto committed.

TRUNCATE

• TRUNCATE is a DDL command
• It Removes all the data
• TRUNCATE TABLE always locks the table and page but not each row
• If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
• TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions
• Faster in performance wise, because it doesn't keep any logs
• Rollback is possible with TRANSACTION

SQL Server Joins......

Steps to Access a Database which is in Inaccess mode

Steps to Remove Database Inaccess Mode

select databaseproperty('irisdb_admin','isShutdown')

alter database irisdb_admin set offline

alter database irisdb_admin set online

Find table being locked in SQL Server 2008/2005

Find table being locked in SQL Server 2008/2005

SQL Scripts to find Locked table in SQL Server 2005/2008

Script for the list of tables which are being locked :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()

Script for list of tables with schema mode change lock :

select object_name(resource_associated_entity_id) as 'TableName',*
     from sys.dm_tran_locks where resource_type = 'OBJECT'
     and resource_database_id = DB_ID()
     and request_mode = 'Sch-M'

Snapshot vs Log Shipping vs Mirroring vs Replication vs Failover Clustering

--->Snapshot : A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation.

--->Log Shipping : Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.

--->Mirroring : Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs. Additional advantages of Mirroring include support at .NET Framework level plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

--->Replication : Replication is the process of copying data between two databases on the same server or different servers on the same database. This is one of the methods to maintain the redundant database site for disaster recovery purpose. Replication methodology maintains copies of main database from the primary server on the secondary sever, both of the databases will be in synchronization state and this technology is used to provide high availability.

--->Snapshot Replication: Snapshot replication is initiated at firs by all types of replication to initialize subscribers. In this method of replication, entire data will be captured and sent to the subscriber. What happens in this replication is, all the changes that occur in the publisher will be captured and then later sent to the subscriber when the snapshot agent runs again. Snapshot agent and distribution agents are used in the snapshot replication.
--->Merge Replication: Merge replication is the process of distributing data from publisher to the subscriber. In the merge replication, updates are allowed even when subscriber or publisher are connected or disconnected. If they are disconnected, the updates will be applied when both get connected. Merge agent and snapshot agent is used in merge replication. Merge replication is the most difficult replication method to implement and manage.
--->Transactional Replication: In transactional replication, all changes made in the publisher are captured and stored in the distribution database, and then these changes are transferred to the subscriber database. The best situation to use transactional replication is when data changes frequently and data is transferred to the subscriber immediately as in almost real time scenario.
--->Peer-Peer Replication: Peer-Peer replication is built on the foundation of the transactional replication. This replication method is introduced in SQL Server 2005. This replication is available only in SQL Server Enterprise Edition. Peer-to-Peer replication provides high availability solution by providing multiple copies of databases across multiple instances.

---> Failover Clustering : Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s a good article on adding geo redundancy to a failover cluster setup.

SQL SERVER – Best Practices for Query Optimization

--> Table should have primary key
--> Table should have minimum of one clustered index
--> Table should have appropriate amount of non-clustered index
--> Non-clustered index should be created on columns of table based on query which is running
--> Following priority order should be followed when any index is created
a) WHERE clause
b) JOIN clause
c) ORDER BY clause
d) SELECT clause
--> Do not to use Views or replace views with original source table
--> Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
--> Remove any adhoc queries and use Stored Procedure instead
--> Check if there is atleast 30% HHD is empty – it improves the performance a bit
--> If possible move the logic of UDF to SP as well
--> Remove * from SELECT and use columns which are only necessary in code
--> Remove any unnecessary joins from table
--> If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)