Sql Server keyboard shortcuts

Some of the useful keyboard shortcut keys for Sql Server are given here:

Bookmarks: Clear all bookmarks.	CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle).	CTRL+F2
Bookmarks: Move to next bookmark.	F2
Bookmarks: Move to previous bookmark.	SHIFT+F2
Cancel a query.	ALT+BREAK
Connections: Connect.	CTRL+O
Connections: Disconnect.	CTRL+F4
Connections: Disconnect and close child window.	CTRL+F4
Database objects information.	ALT+F1
Editing: Clear the active Editor pane.	CTRL+SHIFT+ DEL
Editing: Comment out code.	CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT.	CTRL+C
Editing: Cut. You can also use SHIFT+DEL.	CTRL+X
Editing: Decrease indent.	SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane.	CTRL+DEL
Editing: Go to a line number.	CTRL+G
Editing: Increase indent.	TAB
Editing: Make selection lowercase.	CTRL+SHIFT+L
Editing: Make selection uppercase.	CTRL+SHIFT+U
Editing: Remove comments.	CTRL+SHIFT+R
Editing: Repeat last search or find next.	F3
Editing: Replace.	CTRL+H
Execute a query. You can also use CTRL+E (for backward compatibility).	F5
Books Online	F1
Help for the selected Transact-SQL statement.	SHIFT+F1
Navigation: Switch between query and result panes.	F6
Navigation: Switch panes.	Shift+F6
Navigation: Window Selector.	CTRL+W
New Query window.	CTRL+N
Object Browser (show/hide).	F8
Object Search.	F4
Parse the query and check syntax.	CTRL+F5
Results: Display results in grid format.	CTRL+D
Results: Display results in text format.	CTRL+T
Results: Move the splitter.	CTRL+B
Results: Save results to file.	CTRL+SHIFT+F
Results: Show Results pane (toggle).	CTRL+R
Templates: Insert a template.	CTRL+SHIFT+INSERT
Templates: Replace template parameters.	CTRL+SHIFT+M
Tuning: Display estimated execution plan.	CTRL+L
Tuning: Display execution plan (toggle ON/OFF).	CTRL+K
Tuning: Index Tuning Wizard.	CTRL+I
Tuning: Show client statistics	CTRL+SHIFT+S
Tuning: Show server trace.	CTRL+SHIFT+T
Use database.	CTRL+U

Differences between sqlserver 2000, 2005 and 2008 versions

Introduction

The table shows the differences between sqlserver different versions. This can be helpful in interview questions.

Sql server 2000	Sql server 2005	Sql server 2008
Query analyser and enterprise manager are seperate	Both are combined into SSMS(sql server management studio)	Both are combined as SSMS
No XML datatype used	XML datatype is introduced	XML datatype is used
We can create maximum of 65,535 databases only	We can create maximum of (2^20)-1 databases	We can create maximum of (2^20)-1 databases
Nil	Exception handling	Exception handling
Nil	Varchar(max) datatype	Varchar(max) datatype
Nil	DDL triggers	DDL triggers
Nil	Database mirroring(sql DBA)	Database mirroring
Nil	Row number function for paging	Row number function for paging
Nil	Table fragmentation	Table fragmentation
Nil	Full text search	Full text search
Nil	Bulk copy insert	Bulk copy update
Nil	Can't encrypt	Can encrypt the entire database introduced in 2008
Can't compress the tables and indexes	Can compress tables and indexes(introduced in 2005 SP2)	Can compress tables and indexes
Datetime datatype used for both date and time	Datetime datatype used for both date and time	Date & time are seperately used for date & time datatype, geospatial and timestamp with internal timezone is used
No varchar(max) or varbinary(max) is available	varchar(max) and varbinary(max) is used	varchar(max) and varbinary(max) is used
No table datatype is introduced	Table datatype is introduced	Table datatype is introduced
No SSIS is included	SSIS is started using	SSIS available in this version
CMS(centralized management server) is not available	CMS is not available	CMS is introduced
PBM(policy based management) is not available	PBM is not available	PBM server is introduced

What is ADO.NET

I would like to explain some basic concepts about ADO.NET for beginners

what is a Database 

A Database of interrelated data is called as Database
Database are divided into 3 types

What is ADO..?


ADO means ActiveX Data Objects


ADO.NET is an object  library (collection of classes ) which is used to communicate with databases


ADO.NET helps to develop client server architecture


ADO.NET supports two types of connections
a)managed connection
b)Unmanaged connection

Unmanaged connections works over OLEDB provides (object linking and embedding databases)

OLEDB provides are pre-developed Dll files which are COM components

As COM is platform Dependant ,hence connections over OLEDB are called as UnManaged connections

managed connections works with help of tabular data stream

managed connections are faster.

Namespaces for ADO.NET programming

ADO.NET related namespaces are divided in to 5 groups

Common namespaces
a)System.Data
b)System.Data.common
c)System.Data.SqlTypes


UnManaged namespaces :

d)System.data.OLEDB(supports all databases)

Managed namespaces :

e)System.Data.SqlClient(for sqlserver)
f)System.Data.OracleClient(for Oracle)

ODBC namespace :

g)System.Data.ODBC(supports all Databases)

LINQ Related

h)System.Data.LINQ

Difference between Data Reader and Dataset

I would like share some important differences between Data Reader and Dataset

Data set:

1.       Dataset is a class, which is the part of System. Data Namespace

2.       Dataset supports connectionless architecture. That is, active connection is not required while working with datasets

Dataset cannot communicate directly with database


Hence data adapter is required to carry the data in between dataset and database

àData Adapter is used to carry the data but not to hold the data

àDataset holds a collection of tables, where CLR gives an index number for every table

à Dataset supports to create Relations with the help of primary and foreign keys

àDataset works with the help of xml technology

Data Reader:

àData Reader holds a collection of records

à Data reader is an abstract class

àData Reader is forward only

à Data Reader Is Read-only

àData Readers are connection oriented that is, if connection sate is opened then only data reader works

àIf connection is closed the all associated data readers will be closed automatically

àWhile using data readers, if numbers of clients are more in the network then project efficiency will be decreases
Major Differences

à“Dataset” is a disconnected architecture, while Data Reader has live connection while reading data, if we want to cache data and pass to a different tier “Dataset forms the best and it has decent Xml support.


àWhen application needs to access data from more than one table “Dataset” is the best one


àIf we need to move back while reading records,” Data Reader” does not support functionality


àThe main draw back with dataset is speed. as dataset carry considerable overhead because of relations ,multiple tables etc speed is slower than “Data reader”. Always try to use “Data Reader” wherever possible, as it is meant especially for speed performance.


Syntax for Data Reader:

Sqlconnection con= new sqlconnection();  

 Con.open();  

   Sqlcommand com =new sqlcommand (select * from emptable);  

Data Reader dr= com.ExecuteReader ( );        

Syntax for Dataset:

Sqlconnection con= new sqlconnection();  

   Con.open();    

  SqlDataAdapter da =new SqlDataAdapter (select * from emptable);  

  Dataset ds=new dataset ( );    

Da.fill(ds,”newtablename”) // where newtablename is table alias name in dataset