Difference between PRIMARY KEY and UNIQUE KEY

PRIMARY KEY	UNIQUE KEY
NULL value is not allowed	NULL is allowed, subject to the condition that only a single NULL is allowed. Inserting more null throws “Cannot insert duplicate key” error.
Table can have only one PRIMARY KEY constraint defined	Table can have multiple UNIQUE KEY constraints Defined
By default, if a clustered index is not defined already on the table, defining a PRIMARY KEY constraint results in a clustered index with same name as PRIMARY KEY	By default non-clustered index is create when a UNIQUE KEY constraint is added.
Purpose is to provide a unique identity to each row	Purpose is to avoid duplication

A Good Link on "Generics, Reference Types, Value Types, and Interview Questions " http://geekswithblogs.net/michelotti/archive/2010/08/06/generics-reference-types-value-types-and-interview-questions.aspx

Another good resume tips : http://ofb.net/~niniane/resume_howto.html http://www.detrition.net/resume.html

http://ofb.net/~niniane/resume_howto.html


http://www.detrition.net/resume.html

Which can be rolled back - DELETE or TRUNCATE ?

Refer  http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/



If DELETE or TRUNCATE are enclosed within a TRANSACTION, then both can be rolled back.


DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.

because : In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files.

SQL : What is going under the hood ? Very good articles to get info about current SQL activities

1. http://dotnetslackers.com/articles/sql/Identifying-currently-running-SQL-queries.aspx

Identifying currently running SQL queries

SELECT  

es.session_id, es.host_name, es.login_name   

, er.status, DB_NAME(database_id) AS DatabaseName   

, SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,  

  ((CASE WHEN er.statement_end_offset = -1  

  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2  

  ELSE er.statement_end_offset 

  END - er.statement_start_offset)/2) + 1) AS [Individual Query]  

, qt.text AS [Parent Query]  

, es.program_name, er.start_time, qp.query_plan 

, er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads 

, er.blocking_session_id, er.open_transaction_count, er.last_wait_type 

, er.percent_complete 

FROM sys.dm_exec_requests AS er 

INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id 

CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt 

CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp 

WHERE es.is_user_process=1  

AND es.session_Id NOT IN (@@SPID) 

ORDER BY es.session_id

2. What's blocking my running SQL?  http://dotnetslackers.com/articles/sql/Whats-blocking-my-running-SQL.aspx

SELECT 
Blocking.session_id as BlockingSessionId 
, Sess.login_name AS BlockingUser  
, BlockingSQL.text AS BlockingSQL 
, Waits.wait_type WhyBlocked 
, Blocked.session_id AS BlockedSessionId 
, USER_NAME(Blocked.user_id) AS BlockedUser 
, BlockedSQL.text AS BlockedSQL 
, DB_NAME(Blocked.database_id) AS DatabaseName 
FROM sys.dm_exec_connections AS Blocking 
INNER JOIN sys.dm_exec_requests AS Blocked  
ON Blocking.session_id = Blocked.blocking_session_id 
INNER JOIN sys.dm_os_waiting_tasks AS Waits  
ON Blocked.session_id = Waits.session_id 
RIGHT OUTER JOIN sys.dm_exec_sessions Sess  
ON Blocking.session_id = sess.session_id   
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL 
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL 
ORDER BY BlockingSessionId, BlockedSessionId

Criterias for Primary Key

Which key will you choose as a primary key ?
1. Primary key should not contain null values (should be declared as NOT NULL)
2. Should be unique in the domain ( current and future expected values)
3. Should not change over time

Can a foreign Key refer to same  column in a table : YES

As per http://dotnetslackers.com/articles/sql/Difference-Between-Candidate-Keys-and-Primary-Key.aspx :

"A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules - 1) Not Null, 2) Unique Value in Table and 3) Static - are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key, the decision should be made by experienced DBAs who should keep performance in mind."

Value of type '1-dimensional array of ServiceReference1.CompositeType' cannot be converted to 'System.Collections.Generic.List(Of ServiceReference1.CompositeType) Why WCF returns array representation on wire by default

Value of type '1-dimensional array of ServiceReference1.CompositeType' cannot be converted to 'System.Collections.Generic.List(Of ServiceReference1.CompositeType)



Why WCF returns array representation on wire by default
http://creamcode.wordpress.com/2011/05/15/why-wcf-returns-array-representation-on-wire-by-default/