Shortcut’s to SSMS

To open SSMS: Windows+R -> ssms -> enter

To get list of options for connecting: Windows+R -> ssms -? -> enter

To open new query window: Ctrl + N

To change database: Ctrl + U

To comment out part of query : Ctrl+K followed by Ctrl+C

To un-comment out part of query : Ctrl+K followed by Ctrl+U

To convert code to upper case: Ctrl+Shift+U

To convert code to lower case: Ctrl+Shift+L

To go to specific line in code: Ctrl+G and specify line number.

To parse query: Ctrl+F5

To list parameters of SP : Ctrl+Shift+Space

To toggle result pane: Ctrl+R

To return to grid in result pane: Ctrl+D

To display qeury execution plan without executing: Ctrl+L

To get actual execution plan: Ctrl+M

To include client statistics: Shift+Alt+S

To cancel long running query: Alt+Break

To toggle query window to fullscreen mode: Shift+Alt+Enter

To toggle back query window to normal mode: Shift+Alt+Enter

To get meta information of object: Alt+F1 after selecting the object

Links …

Are Data Pages bricks of home called SQL SERVER?

Page:

Page is the smallest unit of storage in SQL Server.

Like a brick is the smallest unit for a house page also the same for SQL Server.

Size of page is 8KB (8192 bytes). So a database contains 128 pages per megabyte. A page contains 3 sections mainly like Page Header, Row Offsets and data. Page header occupies 96bytes it contains information like page number, amount of free space and so on. Row Offsets stores the information about the order of rows like row offset 0 indicates its first row, 1 indicates its second row and so on. Data is where the actual information/data is stored.

So the maximum amount of data that can be stored in a Page is 8060 bytes.

Extent:

Group of 8 pages is termed as an extent. So size of extent will be 64KB.

Based on type of pages an extent contains it is classified into 2 types.

If an extent contains all 8 pages belongs to same object it is termed as Uniform Extent.

If an extent contains 8 pages which belong to different object these are Mixed extents. So maximum of 8 different objects can have pages in an extent.

                

Links …

Query to find nth highest salary in SQL Server

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP n salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary

where n > 1 (n is always greater than one)

Thanks to Pinal Dave!!!

Reference …

Resource Database

This is the additional database introduced from sql server 2005. The main advantage of this DB is it makes upgradation much easier compared to earlier versions and this is the DB where all the system objects physically reside.  

We can’t find this DB in management studio but the mdf and ldf files (mssqlsystemresource.mdf, mssqlsystemresource.ldf) can be found in “<drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\” folder.

These files should always be kept in the same location where master DB files are residing.

We can attach these files using command ‘sp_attach_db’ incase if we want the DB to be visible in Management studio.

This DB cannot be backed up/restored using normal ‘backup’/’restore’ command. We have to backup manually.

The DBID of this DB is always 32767.

Links …

Drawback of Miantenance Plans compared to SP's

Maintenance plans:

·         Not advisable for complex tasks.

·         If mixed recovery models on one server it’s much easier to use t-sql scripts than maintenance plans.

·         Difficult to us in database mirroring. As MP’s

·         Consistency between sql versions.

·         Time window logic is much easier in t-sql scripts than MP’s. Like after full bckup if enough time is there then if planning to perform defragmentation and so on..