Reference Site: functionx/sqlserver/

1. TRAINING SESSIONS SQL Server Basics Reference Site: http://www.functionx.com/sqlserver/ Design By.: .Net Prepared By: Dilip Namdeo Dated: 23-Dec-09

2. INDEX • Databases • CREATE DATABASE • DROP DATABASE • Tables • Views • Stored Procedures • Functions • Triggers • Cursors • Transact-SQL : • Columns • Records Operations • Naming Convention

3. Databases CREATE DATABASE: To create a database in SQL, use the following formula: CREATE DATABASE DatabaseName Here is an example: CREATE DATABASE CarRental; If you want the name of the database to be in different words, include them in square brackets. Here is an example: CREATE DATABASE [Car Rental];

4. CREATE DATABASE: To visually create a database, open Microsoft SQL Server Management Studio. In the Object Explorer, expand the server name followed by the Databases node. Right-click Databases and click New Database...

5. In the Name text box, type the desired name of the new database. Here is an example:

6. Then specify the other properties of the new database:

7. To create a table, you can follow this formula: CREATE TABLE Country( Column1, Column2, Column3); Each column is created as: ColumnNameDataType Options Here is an example: CREATE TABLE Customers ( FullNamenvarchar(50), Address NVARCHAR(120), City NvarChar(40), State NVarChar(50), PostalCodenvarchar(20), HomePhonenvarchar(20), OrganDonor BIT); GO Creating a Table

8. You can then modify/customize this code. To visually create a table, in the Object Explorer, expand the database and expand its Tables node. Right-click the Tables node and click New Table... Enter a name for each column and select its data type:

9. Views • Overview of Views When studying data analysis, a query is a technique of isolating a series of columns and/or records of a table. This is usually done for the purpose of data analysis. This can also be done to create a new list of items for any particular reason. Most of the time, a query is created temporarily, such as during data analysis while using a table, a form, or a web page. After using such a temporary list, it is then dismissed. Many database applications, including Microsoft SQL Server, allow you to create a query and be able to save it for later use, or even to use it as if it were its own table. This is the idea behind a view. • Definition A view is a list of columns or a series of records retrieved from one or more existing tables, or as a combination of one or more views and one or more tables. Based on this, before creating a view, you must first decide where its columns and records would come from. Obviously the easiest view is one whose columns and records come from one table.

10. Visually Creating a View To create a view, you can use the Object Explorer, a query window, the Command Prompt, or Windows PowerShell. Before starting the view, you would have to specify the table(s) that would be involved. To create a view from the Object Explorer, you can expand the database, right-click Views and click New View... This would open the Add Table dialog box:

12. With Transact-SQL • To programmatically create a view, you use the following SQL syntax: CREATE VIEW ViewName AS SELECT Statement CREATE VIEW dbo.ListOfMen AS SELECT dbo.Genders.Gender, 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'); GO

13. Executing a View • Open an empty query window associated with the database that contains the view. In the query window, write a SELECT statement using the same formulas and rules we saw for tables. Here is an example: • From the Object Explorer, expand the database and its Views node. Right-click the name of the view and click Open View

14. Stored Procedure • Definition A stored procedure is an already written SQL statement that is saved in the database. A stored procedure is a mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks. • Benefits of Stored Procedures Why should you use stored procedures? Let's take a look at the key benefits of this technology: • Precompiled execution: SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly. • Reduced client/server traffic: If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

15. Efficient reuse of code and programming abstraction: Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time. • Enhanced security controls: You can grant users permission to execute a stored procedure independently of underlying table permissions. • Based on this, the simplest syntax of creating a procedure is: CREATE PROCEDURE ProcedureName AS Body of the Procedure • Modifying a Procedure ALTER PROCEDURE ProcedureName AS Body of Procedure • Deleting a Procedure DROP PROCEDURE ProcedureName

16. Here is an example of executing the procedure:

17. Function • Stored Procedures vs. User Defined Functions in Microsoft SQL Server • SQL Server user-defined functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two: • Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement. • Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

18. In Transact-SQL, the primary formula of creating a function is: CREATE FUNCTION FunctionName() For a function to be useful, it must produce a result. This is also said that the function returns a result or a value. When creating a function, you must specify the type of value that the function would return. To provide this information, after the name of the function, type the RETURNS keyword followed by a definition for a data type. Here is a simple example: CREATE FUNCTION Addition() RETURNS Decimal(6,3) After specifying the type of value that the function would return, you can create a body for the function. The body of a function starts with the BEGIN and ends with the END keywords. Here is an example: CREATE FUNCTION Addition() RETURNS Decimal(6,3) BEGIN  END

19. Optionally, you can type the AS keyword before the BEGIN keyword: CREATE FUNCTION Addition() RETURNS Decimal(6,3) AS BEGIN END Between the BEGIN and END keywords, which is the section that represents the body of the function, you can define the assignment the function must perform. After performing this assignment, just before the END keyword, you must specify the value that the function returns. This is done by typing the RETURN keyword followed by an expression. A sample formula is: CREATE FUNCTION Addition() RETURNS Decimal(6,3) AS BEGIN RETURN Expression END • Here is an example CREATE FUNCTION GetFullName() RETURNS varchar(100) AS BEGIN RETURN 'Doe, John' END

20. Triggers • Introduction to Triggers When an action has been performed on a table, such as adding a new record, changing (editing/updating) an existing record, or deleting a (or some) records, the table produces a notification. We say that the table fires an event. You can use this occurring event to take some action. A trigger is an action that is performed behind-the-scenes when an event occurs on a table. • Creating a Trigger: CREATE TRIGGER RecordInsertion ON Rooms AFTER INSERT AS BEGIN INSERT INTO DatabaseOperations VALUES(N'Table', N'Rooms', SUSER_SNAME(), N'Created a new record', GETDATE()) END GO

21. Types of DML Triggers: • After Updating: Instead of record insertion time, a DML trigger can act when a record has been updated on a table. To support this operation, you can use the following formula: CREATE TRIGGER TriggerName ON TableName AFTER/FOR UPDATE AS TriggerCode The new keyword in this formula is UPDATE. This indicates that the DML trigger will act when the record has been updated. Everything else is as described for the INSERT operator. Remember to use either AFTER UPDATE or FOR UPDATE.

22. After Deleting: • When a record has been removed from a table, you can apply a DML trigger in response. To make it possible, you can use the following formula: CREATE TRIGGER TriggerName ON TableName AFTER/FOR DELETE AS TriggerCode This time, the formula uses the DELETE operator in an AFTER DELETE or a FOR DELETE expression. This is used for record removal. The other factors follow the same description we saw for the INSERT operator. When a DELETE trigger has acted on a table, the database engine creates a special temporary table named deleted. This table holds a copy of the records that were deleted. Eventually, if necessary, you can access this table to find out about those records.

23. Cursor • Definition: Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. • Create a simple CURSOR in SQL server: DECLARE @id INT --Declaring the Variable @id DECLARE @getID CURSOR -- Declaring the Cursor SET @getID = CURSOR --Assigning the cursor FOR SELECT intID FROM tbl_student --Query related to Cursor OPEN @getID -- Opening the Created cursor FETCH NEXT FROM @getID --Retrieving the record one by one INTO @ID --Assigning the value in declared Variable WHILE @@FETCH_STATUS = 0 BEGIN PRINT @ID FETCH NEXT FROM @getID INTO @ID END CLOSE @getID -- Closing the Cursor DEALLOCATE @getID -- Deallocating the Cursor Memory

24. Transact-SQL : Columns • Adding a Column to a Table: To add a new column to a table, follow this formula: ALTER TABLE TableName ADD ColumnName Properties Here is an example: ALTER TABLE StaffMembers ADD Address varchar(100) NULL GO • Identity Column: An identity column is one whose value is automatically created by the database engine when a new record is added. This makes sure that each record has a unique value for that field. Here is an example: CREATE TABLE StoreItems( I temID int IDENTITY(1, 1) NOT NULL, Category nvarchar(50), ItemName nvarchar(100) NOT NULL, Size nvarchar(20), UnitPrice money); GO

25. Deleting a Column: To delete a column using code, first open or access an empty query window, and use the following formula: ALTER TABLE TableName DROP COLUMN ColumnName On the right side of the ALTER TABLE expression, type the name of the table. On the right side of the DROP COLUMN expression, enter the name of the undesired column. Here is an example: ALTER TABLE StaffMembers DROP COLUMN CurrentResidence; GO • Renaming a Column: Execute sp_rename using the following formula: sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN‘ The sp_rename factor and the 'COLUMN' string are required. The TableName factor is the name of the table that the column belongs to. The ColumnName is the current name of the column. The NewColumnName is the desired name you want to give to the column. Here is an example: sp_rename 'StaffMembers.FullName', 'EmployeeName', 'COLUMN' GO

26. The Default Value of a Column: To specify the default value in a SQL statement, when creating the column, before the semi-colon or the closing parenthesis of the last column, assign the desired value to the DEFAULT keyword Here are examples: CREATE TABLE Employees ( FullName NVARCHAR(50), Address NVARCHAR(80), City NVARCHAR(40), State NVARCHAR(40) DEFAULT N'NSW', PostalCode NVARCHAR(4) DEFAULT N'2000', Country NVARCHAR(20) DEFAULT N'Australia' ); GO The default value can also come from a function. CREATE TABLE Employees ( EmployeeName nvarchar(50), DateHired date default GETDATE(), Address nvarchar(50), City nvarchar(40), State nchar(2) DEFAULT 'VA‘, ); GO

27. Null Values on a Column: To visually create a null field, display the table in Design View: To specify the nullity of a column using SQL, on the right side of the column creation, type NULL. To specify that the values of the column are required, on the right side, type NOT NULL. If you don't specify NULL or NOT NULL, the column will be created as NULL. Here are examples: CREATE TABLE Persons ( FirstNamevarchar(20) NULL, LastNamevarchar(20) NOT NULL, Gender smallint ); GO

28. Unique Values on a Column: To specify that a column will require unique values, when creating it in SQL, use the UNIQUE keyword. Here is an example: CREATE TABLE Students ( StudentNumber int UNIQUE, FirstName nvarchar(50), LastName nvarchar(50) NOT NULL ); GO When a column has been marked as unique, during data entry, the user must provide a unique value for each new record created. If an existing value is assigned to the column, this would produce an error

29. Naming Convention • For Table: • tbl_TableName • For View: • vw_ViewName • For StoredProcedure: • usp_USPName • For Function: • udf_UDFName • Commenting: Commenting should be used for SQL statements specially inside the View, USP and UDF. /* Put your comment here */

30.  Cheers! TRAINING SESSIONS Design By.: .Net Prepared By: Dilip Namdeo Dated: 23-Dec-09