Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday 30 April 2013

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