complete SQL Server guide with queries from start to end covering how to create tables, add/drop columns, insert/update/delete data, and manage advanced features like constraints, indexes, and stored procedures. This is your go-to reference for working with SQL Server

.

🧱 1. Create Table

sql

CREATE TABLE Employee (

    EmpId INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DOB DATE,
    Salary DECIMAL(18,2)
);

2. Add Columns
sql
ALTER TABLE Employee
ADD Department VARCHAR(50),
    JoiningDate DATE;
🧹 3. Drop Columns
sql
ALTER TABLE Employee
DROP COLUMN JoiningDate

 4. Modify Column Data Type
sql
ALTER TABLE Employee
ALTER COLUMN Salary FLOAT;
📥 5. Insert Data
sql
INSERT INTO Employee (EmpId, FirstName, LastName, DOB, Salary, Department)
VALUES (101, 'John', 'Doe', '1990-05-15', 75000, 'HR');
🧾 6. Update Data
sql
UPDATE Employee
SET Salary = 80000
WHERE EmpId = 101;
❌ 7. Delete Data
sql
DELETE FROM Employee
WHERE EmpId = 101;
🔍 8. Select Data
sql
SELECT * FROM Employee;
SELECT FirstName, Salary FROM Employee WHERE Department = 'HR';
🔐 9. Add Constraints
Unique Constraint:
sql
ALTER TABLE Employee
ADD CONSTRAINT UQ_Employee_Email UNIQUE (Email);
Default Value:
sql
ALTER TABLE Employee
ADD CONSTRAINT DF_Employee_Salary DEFAULT 50000 FOR Salary;
🧱 10. Add Index
sql
CREATE INDEX IX_Employee_Department ON Employee(Department);


11. Create Stored Procedure
sql
CREATE PROCEDURE InsertEmployee
    @EmpId INT,
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50),
    @DOB DATE,
    @Salary DECIMAL(18,2),
    @Department VARCHAR(50)
AS
BEGIN
    INSERT INTO Employee (EmpId, FirstName, LastName, DOB, Salary, Department)
    VALUES (@EmpId, @FirstName, @LastName, @DOB, @Salary, @Department);
END;
🧪 12. Execute Stored Procedure
sql
EXEC InsertEmployee 
    @EmpId = 102, 
    @FirstName = 'Jane', 
    @LastName = 'Smith', 
    @DOB = '1985-03-22', 
    @Salary = 85000, 
    @Department = 'Finance';
🧾 13. Create Table-Valued Parameter (TVP)
Create Type:
sql
CREATE TYPE EmployeeType AS TABLE (
    EmpId INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DOB DATE,
    Salary DECIMAL(18,2),
    Department VARCHAR(50)
);
Create Procedure:
sql
CREATE PROCEDURE InsertEmployeesBulk
    @Employees EmployeeType READONLY
AS
BEGIN
    INSERT INTO Employee
    SELECT * FROM @Employees;
END;
🔄 14. Drop Table or Type
sql
DROP TABLE Employee;
DROP TYPE EmployeeType;
🧰 15. Other Useful Features
Rename Column:
sql
EXEC sp_rename 'Employee.FirstName', 'GivenName', 'COLUMN';
Rename Table:
sql
EXEC sp_rename 'Employee', 'Staff';
View Table Structure:
sql
EXEC sp_help 'Employee';
Check Constraints:
sql
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = 'Employee'