Working with Microsoft SQL Server
What is SQL Server Management Studio?
SSMS enables you to create database objects (such as databases, tables, stored procedures, views etc), view the data within your database, configure user accounts, perform backups, replication, transfer data between databases, and more.
 |
| Fig 3 : SSMS Console Window |
User Login :
When creating a new user login, the administrator can assign that login to any number of roles and schemas. This will depend on what that particular login is entitled to.
server level, not at the database level) and right click n Logins.
Creating a database can be done by running a SQL script or by "point and click".
Step 2 : Name your database and click OK:
Create a New Login
Step 1 : Using SQL Server Management Studio, expand the Security option (at the
server level, not at the database level) and right click n Logins.
Step 2 : Click on New Login.
Step 3 : Complete the login properties in the General tab by providing a name for the login, choosing the Authentication method (providing a password if you choose SQL Server authentication), and selecting the database to use as a default.
Step 4 : Click the Server Roles tab if you need to apply any server-wide security privileges.
Step 5 :
Click the User Mapping tab to specify which databases this user account is allowed to access. By default, the login will be assigned to the Public role, which provides the login with basic access.
If the login needs more access in one or more databases, it can be assigned to another role with greater privileges. In this case, select Task Tracker database and db_owner role for that database.
Step 6 : Click OK to create the login.
NOTE :
These roles are database roles and are different to the server roles in the previous tab. Server roles are for administering the SQL Server. Database roles are more limited. They are created within each database and specify what the login can do within that particular database.
 |
| Fig : Server Roles |
Server Roles :
When we created a SQL Server login, we had the option of assigning the login one or more server roles. Server roles (not to be confused with database roles) are available to manage permissions on a server.
View the server roles in SSMS, from the Object Explorer, expand the Security folder
Database :
A database is a collection of information that is organized so that it can be easily accessed, managed and updated.Creating a database can be done by running a SQL script or by "point and click".
System Databases
When you install SQL Server, the following four databases are created.
1.master
This database stores system level information such as user accounts, configuration settings, and info on all other databases.
2.model
This database is used as a template for all other databases that are created.
3.msdb
The msdb database is used by the SQL Server Agent for configuring alerts and scheduled jobs etc
4.tempdb
This one holds all temporary tables, temporary stored procedures, and any other temporary storage requirements generated by SQL Server.
Create Database :
 |
| Fig 4 : Create DB |
The following steps demonstrate how to create a database in SQL Server 2014 using SQL Server Management Studio.
Step 1 : From the Object Explorer, right click on the Databases folder/icon and select New Database
Step 2 : Name your database and click OK:
Database with the Given Name will be created.
NOTE :
The new database is based on the Model database (System Database).
It already contains system functions, system views, system stored procedures, and (hidden) system tables. These are system objects which provide information about the database.
Create Table :
 |
| Fig 5 : Create Table |
Steps to create a table in a database using SQL Server Management Studio (SSMS).
Step 1 : Ensuring you have the correct database expanded (in our case, the TaskTracker database), right click on the Tables icon and select Table
Step 2 : A new table will open in Design view. Fill in Column_name, Datatype & if(Null) checkbox for making column .
( for more options such as PRIMARY KEY, is Identity, etc. right click on left pane of required tuple to add property )
Step 3 : Save the table by selecting File > Save able_1 or by right-clicking on the Table's tab and selecting.
Save Table_1 from the contextual menu:
Insert Data :
There are many ways of getting data into your database.
1.Manually: Type data directly into your table rows.
3.Import: You can use the Import and Export Wizard to import data from another source.
4.SQL Scripts: You can run a SQL script that contains all data to insert.
5.Application/Website: Users update the database via an application or website.
1. Manually
We can use the Edit Top 200 Rows option to manually type data directly into the table rows. Manually entering data is OK if you only have a little bit of data to enter.
Steps :
1. In the Object Explorer, right click on the table you wish to open, and select Edit Top 200 Rows:
2.You can now start entering the data directly into your table.
2. Copy/Paste
You could use a similar method to the above by copying from another datasource and pasting into your database table. This is OK for a small number of records but not for a lot of records.
Steps :
1. Select all required records from the datasource
2. In the destination database (i.e. the one you want to populate with data), right-click on the destination table and select Edit Top 200 Rows.
3. Select an empty row by right-clicking in the left-most column (it's more of a button to the left of your left-most column that allows you to select the whole row) and select Paste from the contextual menu:
3 (a). Import
You can import data from another datasource. The end result is similar to the copy/paste method (i.e. data is copied across to the destination database), but importing the data is more flexible and could be more suitable on many occasions.
Steps :
To import data, right-click on the database and select Tasks > Import Data... and follow the Wizard from there.
The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. These include:
•SQL Server
•Flat files
•Microsoft Office Access
•Microsoft Office Excel
Start the SQL Server Import and Export Wizard to import data from an Excel worksheet to a SQL Server database. Click Next to bypass the welcome screen. On the Choose a Data Source page (Figure 8), configure the following:
 |
| Fig 6: Import Wizard |
Step 1 : (Data Source)
Choose Microsoft Excel from the drop-down menu.
Step 2 : (Excel File Path )
Specify the path of the Excel file from which you are importing data.
Step 3: (Excel Version)
Choose the Excel version where you created the Excel.
Click Next to go to the Choose a Destination page (Figure 9), and configure the following:
Step 4 : (Data Source)
Choose SQL Server Native Client 11.0 from the drop-down menu.
Step 5 : (Server Name )
 |
| Fig 7 : Import Wizard Client |
Type the name of the destination database's SQL Server instance.
Step 6 : (Authentication)
Choose the appropriate authentication mode for the data destination connection.
Step 7 : (Database )
Choose which database to copy the data into.
 |
| Fig 8 : Finish Import |
Step 8 :
Click the Next button to go to the Save and Run Package page. Here, select the Run immediately option and click the Next button.
This takes you to the Complete the Wizard page where you can view the choices you made.
Click Finish to run the package.
3 (b). Exporting data (from a SQL Server DB to a Microsoft Excel worksheet) :
To export data, start the SQL Server Import and Export Wizard. Then, click the Next button to bypass the Welcome Screen. On the Choose a Data Source page configure the following:
Step 1 : (Data Source )
 |
| Fig 9 : Export Wizard |
Choose SQL Server Native Client 11. from the drop-downmenu.
Step 2 : ( Server Name)
Type the name of the SQL Server instance that contains the source data.
Step 3 : (Authentication)
Choose authentication mode for the data source connection.
Step 4 : (Database)
Choose the database that contains the source data.
Click Next to go to the Choose a Destination page (Figure 2). On this page, configure the following:
 |
| Fig 10 : Export Wizard Target |
Step 5 : (Destination)
Choose Microsoft Excel from the drop-down menu.
Step 6: (Excel File Path)
Type in the Microsoft Excel worksheet operating system path.
Step 7 : (Excel Version)
Select the version of the Microsoft Excel worksheet.
Step 8:
|
| Fig 11 : Finish Export |
Click the Next button to go to Review Data Type Mapping page.
This is where you'll see how different data types are mapped between the source and the destination and how any conversions issues will be handled.
Follow the wizard ahead.
4. SQL Scripts :
In many cases, you will find it more efficient to run a SQL script that contains the data you need to insert. You can use the SQL INSERT statement to insert just the data you specify in the statement.
Eg :
USE [Database_Name]
GO
INSERT INTO Table_Name (column_name1,column_name2,column_name3) VALUES (‘val1’, ‘val2’, ‘val3’)
5. Application / Website
Server databases are the backend data storage for a front-end application. Users of the application are responsible for adding data to the database (as well as editing it).
The Difference between these scripts and above scripts is, these scripts are dynamic. i.e Parameters are passed dynamically to the database.
T-SQL Scripts :
1. Create
Database :
Creates a user defined database which can hold n number of user defined tables.
Syntax :
Create database
Query :
CREATE DATABASE SCTPL
2. Create Table :
Creates a user defined table which can hold n number of column with their respective constraints defined by user.
Syntax :
USE [DatabaseName]
GO
CREATE TABLE table_name
(
column_name1 Datatype,
column_name2 Datatype,
column_name3 Datatype
)
Query :
USE [SCTPL]
GO
CREATE TABLE Testdb
(
PrimaryID int PRIMARY KEY
IDENTITY,
Year varchar(255)
)
The SQL Server DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.
Syntax :
USE [Database_Name]
GO
DROP TABLE table_name
Query :
USE [SCTPL]
GO
DROP TABLE
TEST
The SQL Server INSERT INTO statement is used to add new rows of data to a
table in the database.
Syntax :
USE [Database_Name]
GO
INSERT INTO Table_Name (column_name1,column_name2,column_name3) VALUES
(‘val1’, ‘val2’, ‘val3’)
Query :
USE [Database_Name]
GO
INSERT INTO Table_Name (Year, Country, Location)
VALUES ('1996','Nigeria','Akwa Ibom’)
4. Select :
SQL Server SELECT statement is
used to fetch the data from a database table which returns data in the form of
result table. These result tables are called result-sets.
Syntax :
/*For selecting all
entries*/
USE [Database_Name]
GO
SELECT * FROM TEST
/*For selecting
Specific entries*/
USE [Database_Name]
GO
SELECT column_name1, column_name2 FROM Table_name
Query :
/*For selecting all
entries*/
USE [Database_Name]
GO
SELECT * FROM TEST
/*For selecting
Specific entries*/
USE [SUVEN]
GO
SELECT Year, Country FROM TEST
5. Update :
The SQL Server UPDATE Query is used to modify the existing records in a
table.
You can use WHERE clause with UPDATE query to update
selected rows otherwise all the rows would be affected.
Syntax :
/* Type 1 */
Use [Database_Name]
GO
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = value WHERE [condition]
/* Type 2 */
USE [Database_Name]
GO
UPDATE TABLE_NAME
SET
COLUMN_NAME = ‘val1', Column_name = ‘val’
Query :
/* Type 1 */
USE [SUVEN]
GO
UPDATE TEST
SET Location = 'RenamedColumn Akwa-Ibom'
WHERE Location = 'Akwa Ibom‘
/* Type 2 */
UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00
6. Delete :
The SQL Server DELETE Query is used to delete the existing records from
a table.
You have to use WHERE clause with DELETE query to delete
selected rows, otherwise all the records would be deleted.
Syntax :
/*For selecting
specific entries*/
USE [Database_Name]
GO
DELETE FROM table_name WHERE [condition]
/*For selecting all
entries*/
DELETE FROM column_name
Query :
/*For selecting
specific entries*/
USE [SUVEN]
GO
DELETE FROM TEST
WHERE SALARY = 1000
/*For selecting all
entries*/
DELETE FROM Location
Clauses
1. WHERE Clause:
The MS SQL Server WHERE clause is used to specify a condition while fetching the
data from single table or joining with multiple tables.
If the given condition is satisfied, only then it returns a specific value from the table. You will have to use WHERE clause to filter the records and fetch only necessary records.
The WHERE clause is not only used in SELECT statement, but it is also used in
UPDATE, DELETE statement, etc.
Syntax :
SELECT column1, column2, columnN FROM table_name WHERE
[condition]
Query:
SELECT
name FROM Stud WHERE id=1
2. LIKE Clause:
The MS SQL Server LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator
- The percent sign (%)
- The underscore (_)
The percent sign represents zero, one, or multiple characters.
The underscore represents a single number or character. The symbols can be used in combinations.
/* Type 1 */
SELECT
*\column-list FROM table_name WHERE column LIKE '%XXXX%'
/* Type 2 */
SELECT
*\column-list FROM table_name WHERE column LIKE ‘_XXXX_'
Query:
/* Type 1 */
SELECT name from Stud
WHERE sid LIKE
'200%'
(Finds any values
that start with 200)
/* Type 2 */
SELECT name from Stud
WHERE SALARY
LIKE '_00_'
Finds any values
that have 00 in the second and third positions & is of four characters.
3. ORDER BY
The MS SQL Server ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sort query results in ascending order by default.
The MS SQL Server ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some database sort query results in ascending order by default.
Syntax :
Use DatabaseName
Go
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]
Query :
/*TYPE 1*/
Use SUVEN
Go
SELECT * FROM Stud ORDER BY NAME
/*TYPE 2*/
SELECT * FROM Stud ORDER BY NAME DES
4. GROUP BY Clause :
The SQL Server GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.
The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
The SQL Server GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.
The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Syntax :
Use
SUVEN
Go
SELECT column1, column2 FROM table_name WHERE [ conditions ]
GROUP BY column1,
column2 ORDER BY column1, column2
Query:
Use
SUVEN
Go
SELECT NAME, SUM(SALARY) as [sum of salary] FROM Stud GROUP BY NAME
6. DISTINCT
The MS SQL Server DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.
There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.
The MS SQL Server DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.
There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records.
Syntax :
Use Suven
Go
SELECT DISTINCT
column1, column2,.....columnN FROM table_name
WHERE [condition]
Query :
Use SUVEN
Go
SELECT DISTINCT SALARY FROM CUSTOMERS ORDER
BY SALARY
Joins :
The MS SQL
Server Joins clause is used to combine records from two or more
tables in a database. A JOIN is a means for combining fields from two tables by
using values common to each.
MS SQL Server Join Types −
There are different types of joins available in MS SQL Server −
INNER JOIN − Returns rows when there is a match in both tables.
LEFT JOIN − Returns all rows from the left table, even if there are no matches in the
right table.
RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the
left table.
FULL JOIN − Returns rows when there is a match in one of the tables.
SELF JOIN − This is used to join a table to itself as if the table were two tables,
temporarily renaming at least one table in the MS SQL Server statement.
CARTESIAN JOIN − Returns the Cartesian product of the sets of records from the two or
more joined tables.
NOTE :
The join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.
Query Designer :
The query designer is a graphical user interface that assists in building queries for your SQL Server database. This can be particularly useful when building complex queries that involves many tables, views etc.
The Query Designer can also be beneficial for those who are learning how to write SQL. By using the Query Designer to generate the SQL, you can study the SQL and learn the syntax as you go.
Step 1 : Open
a new query by clicking New Query on the toolbar
Step 2 : Open
the Query Designer by selecting Query > Design Query in Editor. from the top menu:
Step 3 : Select the tables you want to run the query against (in this case, we will add both tables), click Add, and close the box by clicking Close:
Step 4 :
Select the column/s you want to display in your query by checking the checkbox next to the column name. In the middle pane, you can deselect an Output checkbox to hide that field from being displayed when the query is run (but the field will still be involved in the query).
Add a value under Filter to narrow the results down to only those you're interested in (in this example, it is creating a WHERE clause to select only those records with a StatusId of "1" - which is "To Do"):
Step 5 :
Click OK once you're happy with your query. Query will be automatically Generated by query designer. Execute it using f5 key.
Views :
In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a SELECT statement, and when you run the view, you see the results of it like you would when opening a table.
Some people like to think of a view as a virtual table. This is because a view can pull together data from multiple tables, as well as aggregate data, and present it as though it is a single table.
Syntax :
CREATE VIEW ViewName AS
SELECT ...
Query :
CREATE VIEW ToDoList AS
SELECT Tasks.TaskName, Tasks.Description
FROM Status
INNER JOIN
Tasks ON
Status.StatusId = Tasks.StatusId
WHERE (Status.StatusId = 1)
NOTE : Tasks & Status are two different Tables in a single database.
To execute a View :
Syntax : select * from View_name
NOTE :
The view will return upto date data. If the data in the table changes, the results of the view will change too. So if you were to add a new task with a status of "To Do", next time you run the view, it will include the new record in the result set.
Stored Procedures :
A stored procedure
will typically contain some business
logic. For example, a
stored procedure can accept parameters that are passed to it and test against
those parameters using IF statements. Eg, if the parameter is one value, do this, if it's
another value, do that.
Benefits of Stored Procedures :
1. Modular programming
You can write a stored procedure once, then call it again and again, from different parts of an application (and even from multiple applications).
2. Performance
Stored procedures provide faster code execution and reduce network traffic.
3. Security
Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.
Create a Stored Procedure :
To create a stored procedure, you need to use the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure. If your stored procedure is going to accept parameters, they need to be included after the name.
Syntax :
CREATE PROCEDURE myStoredProcedure AS
...
OR
CREATE PROCEDURE myStoredProcedure @{Parameter Name}
{data type} AS
...
Query :
CREATE PROCEDURE
LatestTasks @Count int
AS
SET ROWCOUNT @Count
SELECT TaskName AS LatestTasks, DateCreated
FROM Tasks
ORDER BY DateCreated DESC
Execute a Stored Procedure
1. T-Sql Script :
Now that you've created your stored procedure, any time you want to execute it, you need to call it using either EXECUTE or EXEC. If the stored procedure requires parameters you provide those after the procedure name.
Syntax :
EXECUTE LatestTasks
EXEC LatestTasks
OR
EXEC LatestTasks @Count = 5
2. Using The GUI
Step 1 : Using the Object Explorer, navigate to the stored procedure.
Step 2 : Right click on the stored procedure and select Execute Stored Procedure.
Step 3 : A dialog will appear. Enter your parameter values.
Step 4 : Click OK. SQL Server will generate the SQL code and execute the stored
System Stored Procedures :
SQL Server includes a large number of system stored procedures to assist in database administration tasks. Many of the tasks you can perform via the GUI can be done via a system stored procedure. For example, some of the things you can do with system stored procedures include:
- Configure security accounts
- Set up linked servers
- Create a database maintenance plan
- Create full text search catalogs
- Add remote login
- Configure replication
- Set up scheduled jobs
- and much more...
NOTE :
System Stored Procedure has a prefix sp_ as naming convention.
It is a good idea to develop a consistent naming convention for your stored procedures, like usp_, select_ , etc.
