Differences between sqlserver 2000, 2005 and 2008 versions

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

How to Change table name or column name in Sqlserver 2005/2008/2012

How to Change table name or column name in Sqlserver 2005/2008

Introduction

sp_rename is used to change the user created objects in a database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type.

sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.

Remember: Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. So this is not recommended to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

Syntax for sp_rename: sp_rename [objectname] [newname] [objecttype]

Example 1: Renaming Table

    USE AdventureWorks; GO

    EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';

    GO

Example 2: Renaming a column

    USE AdventureWorks;

    GO

    EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';

    GO

Example 3: Renaming an Index

    USE AdventureWorks;

    GO

    EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';

    GO

Note: if you rename a table column and that column is referenced in a trigger, the trigger must be modified to reflect the new column name.

Comments/Suggestions are invited. Happy coding......!

SQL Server keyboard shortcuts

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