Exploring SQL Server Management Studio (SSMS) and Essential SQL Operations

SQL Server Management Studio (SSMS) serves as a crucial client tool for crafting and executing SQL queries. However, it's important to note that SSMS is not a server itself; rather, it's employed to connect to the database server (often SQL Server) residing on a dedicated machine. Developers access the server through SSMS from their local development computers. In this article, we'll delve into various fundamental aspects of SSMS and SQL operations.

Database Creation, Modification, and Deletion

Creating a database using a query is simple:

CREATE DATABASE DatabaseName;

To alter a database after its creation:

ALTER DATABASE DatabaseName MODIFY NAME = NewDatabaseName;

Dropping or deleting a database:

DROP DATABASE DatabaseThatYouWantToDrop;

When databases are created, whether graphically or through queries, two key files are generated:

• .MDF file: This data file contains the actual data.

• .LDF file: The transaction log file is instrumental in database recovery.

When a database is dropped, both the .LDF and .MDF files are removed.

Table Creation and Manipulation

Tables are central components in databases. A primary key uniquely identifies each row in a table. Foreign keys establish links between tables by referencing primary keys. This prevents the insertion of invalid data into foreign key columns.

Creating a sample table:

CREATE TABLE tblGender (
    ID INT NOT NULL PRIMARY KEY,
    Gender NVARCHAR(50)
);

To add a foreign key reference:

ALTER TABLE ForeignKeyTable ADD CONSTRAINT ForeignKeyTable_ForeignKeyColumn_FK 
FOREIGN KEY (ForeignKeyColumn) REFERENCES PrimaryKeyTable (PrimaryKeyColumn);

Default Constraints

Default constraints insert predefined values into columns for new records, unless other values are specified.

To add a default constraint to an existing column:

ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME };

To add a new column with a default value:

ALTER TABLE { TABLE_NAME }
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL }
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE };

Check Constraints

Check constraints restrict the value range for a column.

Example:

ALTER TABLE tblPerson
ADD CONSTRAINT CK_tblPerson_Age CHECK (Age > 0 AND Age < 150);

Identity Columns

Identity columns automatically generate values upon new row insertion.

Example:

CREATE TABLE tblPerson (
    PersonId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(20)
);

Retrieving Last Identity Value

• SCOPE_IDENTITY() returns the last identity value within the same session and scope.

• @@IDENTITY returns the last identity value within the same session but across scopes.

• IDENT_CURRENT('TableName') returns the last identity value created for a specific table across sessions and scopes.

Unique Key Constraints

Unique key constraints ensure column uniqueness.

Example:

ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name UNIQUE (Column_Name);

Select Statement and Filtering

Basic select statement syntax:

SELECT Column_List
FROM Table_Name;

For distinct rows, use the DISTINCT keyword:

SELECT DISTINCT Column_List
FROM Table_Name;

Filter rows with the WHERE clause:

SELECT Column_List
FROM Table_Name
WHERE Filter_Condition;

Group By and Aggregate Functions

Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() summarize data.

Group By clause groups rows into summary rows based on one or more columns or expressions:

SELECT City, SUM(Salary) AS TotalSalary
FROM tblEmployee
GROUP BY City;

Filtering groups:

• WHERE filters rows before aggregation.

• HAVING filters groups after aggregation.

SELECT City, SUM(Salary) AS TotalSalary
FROM tblEmployee
WHERE City = 'London'
GROUP BY City;

Conclusion

SQL Server Management Studio is a powerful tool for database management and querying. Understanding database creation, table manipulation, constraints, select statements, and aggregation functions are foundational skills for working with databases effectively. With this knowledge, developers can harness the capabilities of SSMS to streamline database development and management tasks.