Monday, September 5, 2011

Sql Server:Complete Description With All Sql Queries

                                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.
Microsoft sql server
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 , 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.

                                                    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.

2 comments:

Anonymous said...

Hello, I enjoy reading through your article post.
I like to write a little comment to support you.
Have a look at my web page : casino refund

Anonymous said...

Do you have a spam issue on this site; I also am a blogger, and I
was wanting to know your situation; many of us have developed some nice procedures and
we are looking to trade solutions with others, be sure to shoot me
an email if interested.
Here is my web page : best online casino bonus

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Powered by Code Imagine