T-SQL

• 1- Functions can be used in a SELECT statement - SPs cannot.
• 2- SPs can have both input and output parameters, but functions can ony have input parameters.
• 3- Functions cannot return values of type text, ntext, image and timestamps, but SPs can.
• 4- Functions can be used as user-defined datatypes in CREATE TABLE, but SPs cannot.

• SET @VariableName = Value
• - You can only assign one variable, and it can NOT read from the database.

• SELECT @VariableName = Value
• - It can do everything that SET can plus it can assign multiple variables and can read from the database.

• Global variables are created by SQL Server and are used for obtaining info. from the server on the current connection.
• - Global variable names begin with @@
• - They cannot be created by T-SQL
• - They are read-only

• INSERT INTO tableA(Col-one-A, Col-two-A, Col-three-A)
• SELECT Col-one-B, Col-two-B, Col-three-B
• FROM tableB

Use instead of the WHERE clause when using aggergate function such as: Count, Sum, Max, Min, etc...

• Syntax:
• SELECT Max(a) AS newA
• FROM tableA
• HAVING (Count(d)>2)

• SELECT a, b, MAX(c) FROM
• tableA
• GROUP BY a, b

-Notice that column "c" which is being aggregated is not in the "Group By" section.

• - Used to store the results of computations or database queries between multiple T-SQL statements.
• -Local variable declaration:
• DECLARE @VariableName Datatype[,....]
• -Each variable must have:
• 1- A name beginning with an @ symbol.
• 2- A valid SQL server or user-defined datatype.
• 2- Local variables are stored in the memory of the server.
• 2a- The number of variables is limitted by available memory.
• 3- They last only for the batch or SP

Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the UNION.

• UNION - Does not include duplicate records.
• UNION ALL - includes duplicate records.

• Usually the Foreign Key / Primary Key columns.
• - Sorting of the columns you specify that "point" back to the data page in the clustered index. This is why choosing the clustered index is so important. It affects other indexes.
• - Does not re-order the actual table data.
• - Sometimes called a "heap table" for tables lacking clustered indexes because it points to the actual data pages that are essentially unordered and non-indexed.
• - Logical order does not match the physical order of rows on disk.
• - Similar to an index in the back of the book.
• - Add to queries that return small result sets.

• There is ONLY ONE clustered index allowed per table.
• - This index should be the most common column that is in your WHERE clauses.
• - Reordering happens every time index changes (Updates, Inserts, Deletes)
• - Affects the physical order of data so these can be only one.
• - Keeps the rows in order within a page (8k) of data.
• - Re-orders the records in the table physically.
• - Each page is a collection of data. The order of pages is controlled by clustered indexes.
• - Inserts are slower.

Provide all values in Table A and only those values in Table B where the linked fields and their values are equal.

• SELECT a, b, c FROM tableA
• INNER JOIN a ON tableA.a = tableB.b

• -Functions support parameters
• -Views are standard SQL

• - To change a parameter into an output parameter put the word OUTPUT after the parameter in the CREATE PROC statement.
• CREATE PROC procName (@Param1 datatype() OUTPUT) AS....

• - To assign a vallue to the output parameter use the extended form of the SELECT or SET statements.
• - To call a procedure and recieve an output parameter put OUTPUT after the parameter in the EXEC statement.
• EXEC spname 'Param', @OutputPramName OUTPUT
• - Use SELECT with out a FROM to select the value of the variable.

• Syntax: CREATE PROC procname (@Param1 datatype = Default, ...) AS [sql statements] GO
• - Parameters are optional, Default parameter values are optional.
• - Parameters must start with an @ sign and must have a valid datatype.
• - Parameters can be used just like local variables.
• - Parameters are passed after the procedure nae when exectuting - EXEC spname 'Parameter'
• - Failing to supply a parameter without a default may cause an error - use '%' for flexibility of default value.

IDENT_CURRENT('table')

SCOPE_IDENTITY()

• - @@ERROR: Error status of the last TSQL statement. 0 = None.
• - @@ROWCOUNT: Number of rows affected by the last TSQL statement.
• - @@TRANCOUNT: Number of active transactions for the current connection. A commit is only effective if @@TRANCOUNT=1
• - @@IDENTITY: Returns the last identity value inserted by the last statement - Maybe from the statement or a trigger and could be from any table.

• 1- Temporary SPs - Local use #, Global use ## before the names.
• 2- System SPs - Created and stored in the Master DB - Have sp or xp prefixes.
• 3- Automatically Executing SPs - One or more SPs that can execute automatically when SQL Server starts.
• 4- User SPs - Created by developer / user.

• - Views can have only SELECT statements (CREATE, UPDATE, TRUNCATE, DELETE are not allowed)
• - Views can NOT have "SELECT INTO", "GROUP BY", "HAVING", or "ORDER BY" clauses.

• - Returns all the rows that an INNER JOIN returns plus one row for each of the other rows in the first table that did not have a match in the second table.
• - The rows that do not have matching values will have a NULL value returned.

- Use @@Error as an output parameter

• - The process of taking the level of normalization withing the database DOWN a notch or two.
• - Normalizing can actually slow database performance.
• - Allows controlled redundancy which increases the database performance, and it should be the only reason to denormalize.
• - It is NOT the opposite of normalizing.

• - Data redundancy.
• - Application coding complications.
• - Referential integrity is more difficult to maintain.

• First: SPs in Master database.
• Second: SPs based on any qualifiers provided (db name or owner).
• Third: SPs using dbo as the owner, if one is not specified.

• - A text file that stores SQL statements.
• - A number of T-SQL batches stored in a file.
• - Can use the OSQL utility or Query Analyzer to run them.
• - Can be used to create a database and all of its tables and constraints.

It is a saved T-SQL routine that returns a value.

• 1- Scalar Functions - The RETURNS clause specifies one of the scalar datatypes.
• 2- In-line Table-valued Functions - The RETURNS clause specifies TABLE with no accompanying column list.
• 3- Multi-statement Table-valued Functions - The RETURNS clause specifies a TABLE type with columns and their datatypes.

• The process of efficiently organizing data in a database.
• Goals:
• - Eliminate redundant data (same info in more than one table).
• - Ensure data dependencies make sense (only storing related data in a table).

• First Normal Form (1NF) sets the very basic rules for an organized database.
• 1- Eliminate duplicate columns from the same table.
• 2- Create separate tables for each group of related data and identify each row with a unique column or set of columns (the Primary Key)

• Second Normal From (2NF) addresses the concept of removing duplicate data.
• 1- Meet requirements in 1NF.
• 2- Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• 3- Create relationships between these new tables and their predecessors through the use of Foreign Keys.

• Third Normal Form (3NF)
• 1- Meet all the requirements of 2NF
• 2- Remove columns that are not dependant upon the Primary Key.

• Fourth Normal Form (4NF)
• 1- Meet all the requirements of 3NF.
• 2- A relation is in 4NF if it has no multi-valued dependencies.

• - Can not be used to perform a set of actions that modify the global database state.
• - Just like system functions, user-defined functions can be invoked from a query.
• - They can be executed through an EXECUTE statement like SPs.

It is a function within a DLL (written in C, C++, using Open Data Services (ODS) API) that can be called from T-SQL.

• - Allows the current database to be switched in T-SQL code.
• - Can be in a batch, on its own, or part of a batch with other statements.

• - Pointers used to fetch rows from a result set.
• - A data structure that decribes the results returned from an SQL SELECT statement.
• - One of the variables in this structure is a pointer to the next record to be fetched from the query results.

• - Pre-compiled code.
• - Control access to data (users may enter or change data but not write procedures).
• - Preserve data integrity (information is entered in a consistent manner).
• - Improve productivity (statements in a SP only need to be written one time).
• - Execution-plan retention and reuse.
• - Query auto-parameterization.
• - Encapsulation of business rules and policies.
• - Application modularization.
• - Network bandwidth conservation.
• - Support for automatic execution on startup.

• CREATE PROCEDURE spName AS
• DECLARE @intInteger int
• SELECT * FROM tableA
• GO

- The RETURN statement can specify an integer value to return to the calling application, batch, or function. If no values are specified on RETURN, the SP returns the value 0 (zero). SPs return a value of 0 if no errors were encountered. non-zero = error.

• 1- Use "No Count"
• 2- Use less joins.
• 3- Use indexes on tables.

If an index is added from which the SP might benefit. Optimization does not automatically happen (until the next time the SP is run after SQL Server is restarted).

• - Transactional: Copies data from publisher to subscribers (one way).
• - Merge: Combines data from multiple sources into a single central database. Allows changes on both the subscriber & the publisher. Good for when continuous connection is not guaranteed.
• - Snapshot: Take snapshot of data from one server & moves it into another. Requires copying all data each time a table refresh occurs.

• 1- SP_RECOMPILE: Forces a recompile the next time SP runs.
• 2- Create an SP with the WITH RECOMPILE option in its definition - can make SP run slow - use when SPs have parameters whose values differ widely between executions.
• 3- Specify WITH RECOMPILE when executing the SP.

• It is a program in a database that gets called each time a row in a table is INSERTED, UPDATED, or DELETED.
• -Triggers allow you to check that any changes are correct, or to fill in missing information before it is committed.

• One or more T-SQL statements.
• - Sent to SQL in on network packet.
• - Statements are parsed together, the whole batch fails in case of a syntax error.
• - A batch is terminated by a GO statement.
• - Multiple SELECT statements can be sent in on batch to reduce network traffic.
• - The GO statement cannot be commented out by a multi-line comment tag.

• 1: You would create three tables.
• Table 1: tblStudent (has StudentID as PK)
• Table 2: tblClass (has ClassID as PK)
• Table 3: tblLink (This is due to the many-to-many relationship between tblStudent and tblClass. It will contain the StudentIDs and ClassIDs as "Foreign Keys". This is called an "Associative Entity")
• tblStudent will be joined to tblLink on StudentID.
• tblClass will be joined to tblLink on ClassID.
• 2: SELECT tblClass.ClassNameFROM (tblClass LEFT JOIN tblLink ON tblClass.ClassID = tblLink.ClassID) LEFT JOIN tblStudent ON tblLink.StudentID = tblStudent.StudentIDGROUP BY tblStudent.StudentID, tblClass.ClassNameHAVING (((tblStudent.StudentID) Is Null));

• A stored procedure is a set of SQL statements that are in compiled form and are given an assigned name that reside on the server. These stored procedures can then
• be shared among programs.

• Pros:
• - Pre-compiled code.
• - Control access to data (users may enter or change data but not write procedures).
• - Preserve data integrity (information is entered in a consistent manner).
• - Improve productivity (statements in a SP only need to be written one time).
• - Execution-plan retention and reuse.
• - Query auto-parameterization.
• - Encapsulation of business rules and policies.
• - Application modularization.
• - Network bandwidth conservation.
• - Support for automatic execution on startup.

• Cons:
• - Security chaos
• - Stored procedures run in the database and the database can be the bottleneck.
• - If you need to make changes to the stored procedure you will need write permissions to the database (which is sometimes not granted) or someone else has to created it for you.
• - More knowledge is required to create a stored procedure rather than a simple SELECT statement.
• - Too many stored procedures can clutter the database.
• - Additional handling is needed to parse multi-valued parameters in SSRS.

• Char:
• 1.Fixed length memory storage
• 2.CHAR takes up 1 byte per character
• 3.Use Char when the data entries in a column are expected to be the same size

Ex: Declare test Char(100); test="Test" - Then "test" occupies 100 bytes first four bytes with values and rest with blank data.

• VarChar:
• 1.Variable length memory storage(Changeable)
• 2.VARCHAR takes up 1 byte per character, + 2 bytes to hold length information
• 3.varchar when the data entries in a column are expected to vary considerably in size.

Ex: Declare test VarChar(100); test="Test" - Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.

• Conclusion:
• 1.When Using the fixed length data's in column like phone number, use Char
• 2.When using the variable length data's in column like address use VarChar