SQL Server 2008 for Beginners

How to create Database:

CREATE DATABASE [Source]

OR

CREATE DATABASE [neuSource] ON PRIMARY
(
NAME = 'neuSource',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source.mdf' ,
SIZE = 1280KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
LOG ON
( NAME = 'neuSource_log',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Source_log.LDF' ,
SIZE = 504KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%
)
GO

How to see databases:

Use master
Go

Select * from Sysdatabases

How to create Table

CREATE TABLE [dbo].[Audit](
[AuditKey] [int] IDENTITY(100,10) NOT NULL,
[TableProcessKey] [int] NOT NULL,
[PackageBranchName] [varchar](50) NOT NULL,
[PackageBranchRowCount] [int] NULL,
CONSTRAINT [Audit_PK] PRIMARY KEY CLUSTERED
)
GO



Create Table Statement to create Foreign Key (Column Level )

USE AdventureWorks2008
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
);


GO

Table Level

CREATE TABLE ProductSales
(
SalesID INT,
ProductID INT,
SalesPerson VARCHAR(25)
CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
);
GO

Alter Table Statement to create Foreign Key

ALTER TABLE ProductSales
ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
GO

Alter Table Statement to Drop Foreign Key

ALTER TABLE ProductSales
DROP CONSTRAINT fk_productSales_pid;
GO

How to create duplicate table without records

--select * into NewTable from OldTable where 1 = 2
OR
select top 0 * into NewTable from OldTable

Create Table Statement to create Primary Key (Column Level )

USE AdventureWorks2008
GO

CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);

GO

Table Level

CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(25)
CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
);
GO

-- Alter Table Statement to create Primary Key

ALTER TABLE Products
ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
GO

-- Alter Statement to Drop Primary key

ALTER TABLE Products
DROP CONSTRAINT pk_products_pid;
GO


How to check table is existing or not?

IF exists (select * from sys.objects where type= 'u' and name = 'Audit')
Print 'Table Already Exists'
Else
Print 'Need to create this table'
Go

How to check Procedure is existing or not?

IF NOT exists (select 1 from sys.objects where type= 'p' and name = 'TestSP')
Print 'Procedure Already Exists'
Else
CREATE PROCEDURE TestSP
@LastName nvarchar(50) = NULL,
@FirstName nvarchar(50)= NULL
AS
SELECT * FROM dbo.ETLConfiguration
WHERE ConfigurationFilter LIKE @FirstName
AND ConfigurationFilter LIKE @LastName;
GO

How to check Function is existing or not?

IF Not exists (select 1 from sys.objects where type= 'fn' and name = 'TestFunc')
Print 'Function does not Exists'
GO
CREATE FUNCTION dbo.TestFunc
-- Input dimensions in centimeters.
(@CubeLength decimal(4,1),
@CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

Few most frequently used commands:

SP_HELPDB
SP_Help
SP_HelpText ProcedureName/Functionmname
SP_WHO
SP_WHO2
SP_Depends TableName/StoredPRocedure

--Rename Table
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr','Table';
GO

--Renaming a column
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO

--Renaming an index
EXEC sp_rename 'Purchasing.ProductVendor.IX_ProductVendor_VendorID', 'IX_VendorID', 'INDEX';
GO

--Renaming an alias data type
EXEC sp_rename 'Phone', 'Telephone', 'USERDATATYPE';
GO

Common Functions what we used on regularly basis:

Sum()
Getdate()
ISNULL(A,B)
Convert()
Cast()
Max()
Min()
DatePart(d,-2,Getdate())
NULLIF()
Round()
Left()
Right()