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:
Commands For User Handling:
ALTER TABLE TableName DROP COLUMN ColumnName
Ex. ALTER TABLE Employee DROP COLUMN MobileNumberCreate 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:
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
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
Post a Comment