Sql Server:Complete Description With All Sql Queries

SQL Server:

                                SQL Server is a relational database server, developed by Microsoft. It is a software product whose primary function is to store and retrieve data as requested by other software applications.

Need:

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

SQL Server 2005:

                                                     SQL Server 2005 , released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals in queries.

SQL Server 2008:

                                                    The next version of SQL Server, SQL Server 2008,was released on August 6, 2008 and aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime.

"Some Frequently Required SQL Commands"

Commands for database:

To create a database:

                                CREATE DATABASE  DatabaseName

To drop database:

                               DROP DATABASE  DatabaseName;

Renaming database:

                              EXEC sp_renamedb 'ExistingName', 'NewName'

Commands for database table:

To create a table:

                            CREATE TABLE TableName(Column1, Column2, Column3);

Example: 

CREATE TABLE Employee(

EmpID INT, FirstName VARCHAR(50), LastName VARCHAR(50), Address VARCHAR(200), Local VARCHAR(50), City VARCHAR(40), State VARCHAR(50), 

Salary INT, PostalCode VARCHAR(20), MobileNumber BIGINT(20), JoiningDate DATE);

To drop a table:

DROP TABLE TableName

Table Renaming:

 sp_rename ExistingTableName, TableNewName;

Commands for database View: 

To create a view:

CREATE VIEW ViewName
                         AS
                         SELECT Statement 

Ex.

CREATE VIEW Vw_Employee
AS
SELECT EmpID ,FirstName, LastName,MobileNumber, Salary
FROM Employee
WHERE Salary >= 200000; 

To alter a view:

ALTER VIEW ViewName
                             AS
                             SELECT Statement

Example:

     ALTER VIEW dbo.ListOfMen AS SELECT dbo.Persons.FirstName, dbo.Persons.LastName FROM dbo.Genders INNER JOIN dbo.Persons 

ON dbo.Genders.GenderID = dbo.Persons.GenderID 

WHERE (dbo.Genders.Gender = N'Male');

Commands for Table Column: 

To create Primary Key:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);

[Add Primary Key as Constraint]

CREATE TABLE Persons
(
    PersonID int identity(1,1) NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT Pk_Person PRIMARY KEY(PersonID)
); 

Add a Foreign Key:

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);

[Add Foreign Key as Constraint] 

CREATE TABLE Persons
(
    PersonID int identity(1,1) PRIMARY KEY NOT NULL,
    GenderID int NULL CONSTRAINT Fk_Gender
                       FOREIGN KEY REFERENCES Genders(GenderID)
);
 

To add a new column to a database table

ALTER TABLE TableName ADD ColumnName DataType

Example:

ALTER TABLE Employee ADD Age TINYINT  NULL;

Add AN Identity column:

columnName DataType IDENTITY(seed,increment);

Ex. 

 
CREATE TABLE StoreItems(
                        ItemID int IDENTITY(1, 1) NOT NULL, 
                        Category nvarchar(50),
                        UnitPrice money);
  
To Rename a column:
sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'
Ex.
sp_rename 'Employee.PhoneNumber', 'ContactNo', 'COLUMN' 
TO Drop a column:
ALTER TABLE TableName DROP COLUMN ColumnName
Ex.
       ALTER TABLE Employee DROP COLUMN MobileNumber 
 Commands For User Handling:
Create User:
Ex. 
          CREATE USER [Pavan Bhardwaj]  FOR LOGIN pkbharwaj;
          GO
          USE DatabaseTest;
           GO
           GRANT CREATE FUNCTION  TO pkbharwaj;
 
Granting a Permission:
GRANT Permission TO User 
Ex.
        GRANT CREATE ANY DATABASE TO pkbharwaj;
        GRANT CREATE ANY DATABASE, ALTER ANY LOGIN TO pkbhardwaj, azziet;

 
Denying a Permission:
DENY Permission1,Permission2, Permission_n
TO User1, User2,..
Ex. 
       DENY CREATE ANY DATABASE TO pkbhardwaj;

Working with Index 

Create Index:

                       CREATE INDEX IndexName ON Table/View(Column(s)) 

                                                             OR 

                       CREATE INDEX <index_name, sysname, ind_test>
                        ON <schema_name, sysname, Person>.<table_name, sysname, Address> 
                        (
                               <column_name1, sysname, PostalCode>
                         )

Ex.
         1.  CREATE INDEX IX_Employees ON Employee(EmpID); 
         2.  CREATE INDEX IX_Employees ON Employee(FirstName, LastName);

Drop Index after checking existence:

IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_Employee') 
DROP INDEX IX_Employee ON Employee

New features in SQL Server 2008

• Support for structured and semi-structured data.
• Support for digital media formats for pictures, audio, video and other multimedia data[stored as binary large objects]
• Support for xml
• Support email, time/calendar, file and document.
• Support spatial datatype[Geometric and geographic] for geographic and location related data.
• Perform fast search, query, analysis, sharing, and synchronization across all data types.
• Support for database backs up or restores the referenced files as well. SQL Server 2008 also natively supports hierarchical data.