Monday 28 January 2013

SQL Server



What is SQL Server?
Microsoft SQL Server is Relational database management system (RDBMS) developed and owned by Microsoft. It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time).
See more details


SQL Server 2012 Editions:



Free Editions of SQL Server:
You can download free edition of SQL Server with these links


Application to manage SQL Server:
Microsoft provides a software application named SQL Server Management Studio . This provides an Editor with intelligence and graphical management tools. 

DBCC Commands for SQL Server


DBCC Commands:
DBCC commands are most useful for performance and troubleshooting.
The DBCC Commands are used in Maintenance, Informational use, Validation on a database, index, or file group. Theses commands are helpful to check physical and logical consistency of database. Some DBCC statements fix errors automatically. Here I am showing some commands that are frequently used.
Command
Description
Level
When to Check
DBCC SQLPERF(logspace)

--To see transaction log size of each database on Server.

Server Level
Weekly
DBCC SHOWFILESTATS
--Show Total Extents and used extents for database
Database Level
Weekly
DBCC CHECKCATALOG
--Checks for catalog/tables consistency within the specified database
Database Level
Weekly
DBCC CHECKCONSTRAINTS
--Checks the integrity of a specified constraint or all constraints on a specified table in the current database
Database Level
When you suspect that there are rows in your tables that do not meet the constraints/rules.
DBCC CHECKALLOC
-- checks page usage and allocation in the database.
Database Level
 if allocation errors are found for the database
DBCC CHECKTABLE(tablename)
--It verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets on table.
Table level
Whenever required.
DBCC CHECKIDENT(tablename)
--Checking identity information,return current identity value of specified table
Table Level
Whenever required.
DBCC DBREINDEX(tablename)
-- rebuilds an index for a table or all indexes defined for a table.
Table level
 (should not used rather use ALTER INDEX command)
DBCC INDEXDEFRAG (databasename, tablename,indexname)
--defragment clustered and nonclustered indexes on tables and views
Table/View Level
 (Should not use, Replacement is ALTER INDEX ... REORGANISE)
SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid;
--returns request_id


DBCC INPUTBUFFER(sessionid)

--to view the last statement sent by the client connection to SQL Server

Database level
Whenever required
DBCC SHRINKDATABASE(databasename)
--Shrinks the size of the data and log files in the specified database

Database Level
Avoid executing this command during busy periods in production
DBCC SHRINKFILE(file_id)
Use
exec sp_helpfile
 to know filename,fileid,filegroup,size
--allows you to shrink the size of individual data and log files

Database level
Avoid, as in most cases the database will just regrow and shrinking data files causes fragmentation.
DBCC TRACEOFF
--used to disable tracing

Server Level

DBCC TRACEON

--used to enable tracing

Server Level

DBCC TRACESTATUS
--used to know trace status with TraceFlag,Status,Global,Session
Server Level

DBCC USEROPTIONS
--Returns the SET options active (set) for the current connection
Server Level
Whenever required





Thursday 24 January 2013

Frequently asked Scripts during Interviews


1. Select no of Months between two dates:


SELECT
    DATEDIFF(MONTH, '4/1/2011', '01/23/2013') +
    CASE
        WHEN DAY('4/1/2011') < DAY('01/23/2013')
        THEN 1
        ELSE 0
    END
Output:
----------------
22


2. What will be the output of following script

CREATE TABLE dbo.t1
(
 id INT NOT NULL IDENTITY (1, 1),
 name VARCHAR(100)
);

INSERT  INTO dbo.t1
        (name)
VALUES  ('sonu');

DBCC CHECKIDENT ('dbo.t1', RESEED, 24);

INSERT  INTO dbo.t1
        (name)
VALUES  ('queryingsql');

SELECT  id
FROM    dbo.t1;
Output:
---------------------------
1
25


3. Create a query that display the Emp name and indicate the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand rupees. Sort the data in descending order of salary. As for example if Emp name sonu have salary 11000. Then output should be Sonu***********.


select FirstName+SUBSTRING('****************************
********************************************************
********************************************************
********************************************************
*************************************************',1,ROUND(salary/1000,0))
    as Employee_and_salary ,salary from Employee order by salary desc;

Output:
---------------------------------------------------------------
Employee_and_salary    salary
sonu***********                         11000
mohit**********                         10000
mohit**********                         10000
monu*********                            9000


More script coming soon...........