Search content within the blog

Friday, April 17, 2009

Features in SQL Server part-I

1) User-Defined Functions.
User-Defined Functions (UDFs) - one or more Transact-SQL statements that can be used to encapsulate code for reuse. User-defined functions cannot make permanent changes to the data or modify database tables. You can’t use insert, update or select statements to modify a table. UDF can change only local objects for this UDF, such as local cursors or variables. This basically means that the function can’t perform any changes to a resource outside the function itself.

2) Distributed partitioned views.
Distributed partitioned views allow you to partition tables horizontally across multiple servers.
So, you can scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers.

3) New data types.
There are new data types:
• bigint data type: - This type is an 8-byte integer type.
• sql_variant data type: - This type is a type that allows the storage of data values of different data types.
• table data type: - This type lets an application store temporary results as a table that you can manipulate by using a select statement or even action queries—just as you can manipulate any standard user table.

4) INSTEAD OF and AFTER Triggers.
There are INSTEAD OF and AFTER Triggers in SQL Server 2000. INSTEAD OF triggers are executed instead of the INSERT, UPDATE or DELETE triggering action. “AFTER” triggers are executed after the triggering actions.

5) Cascading Referential Integrity Constraints.
There are new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.
The ON DELETE clause controls what actions are taken if you attempt to delete a row to which existing foreign keys point.
The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point.

The ON DELETE and ON UPDATE clauses have two options:
• NO ACTION: - NO ACTION specifies that the deletion/updation fail with an error.
• CASCADE: - CASCADE specifies that all the rows with foreign keys pointing to the deleted/updated row are also deleted / updated.

6) XML Support.
SQL Server 2000 can use XML to insert, update, and delete values in the database, and database engine can return data as Extensible Markup Language (XML) documents.

7) Indexed Views.
Unlike standard views, in which SQL Server resolves the data-access path dynamically at execution time, the new indexed views feature lets you store views in the database just as you store tables. Indexed views, which are persistent, can significantly improve application performance by eliminating the work that the query processor must perform to resolve the views.

Rules of Normalization
Normalization is used to avoid redundancy of data and inconsistent dependencies within a table. A certain amount of Normalization will often improve performance.
First Normal Form: -
For a table to be in First Normal Form (1NF) each row must be identified, all columns in the table must contain atomic values, and each field must be unique.

Second Normal Form: -
For a table to be in Second Normal Form (2NF); it must be already in 1NF and it contains no partial key functional dependencies. In other words, a table is said to be in 2NF if it is a 1NF table where all of its columns that are not part of the key are dependent upon the whole key - not just part of it.

Third Normal Form: -
A table is considered to be in Third Normal Form (3NF) if it is already in Second Normal Form and all columns that are not part of the primary key are dependent entirely on the primary key. In other words, every column in the table must be dependent upon "the key, the whole key and nothing but the key." Eliminate columns not dependent upon the primary key of the table.

Referential Integrity: - The referential integrity of a database concerns the parent/child relationship between tables. The relationships between tables are classified as one-to-one, one-to-many, many-to-one, and many-to-many. If the child records have no parent records then they are called orphan records.

An index is a separate table that lists in order, ascending or descending, the contents of a particular table with pointers to the records in the table. An index increases the speed at which rows are retrieved from a database. The index can consist of one column or can be a composite index composed of many columns. There are two types of indexes.

i) Clustered Indexes: - A clustered index means that the data is sorted and placed in the table in sorted order, sorted on what columns are contained in the index. Since the rows are in sorted order and an entity can exist in sorted order only once, there can be only one clustered index per table. The data rows are actually part of the index. A table should have at least one clustered index unless it is a very small table.

ii) NonClustered Indexes: - In a Nonclustered index, the data rows are not part of the index. A Nonclustered index stores pointers to the data rows in the table and is not as efficient as a clustered index but is much preferable to doing a table scan.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

A View is a copy of a table or tables that doesn’t exist except as a result set that is created when the view is queried.

A Trigger is a special kind of stored procedure that becomes active only when data is modified in a specified table using one or more data modification operations (Update, Insert, or Delete).

UNION 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.

Joined Tables
Tables are joined for the purpose of retrieving related data from two or more tables by comparing the data in columns and forming a new table from the rows that match. The types of joins are:
i) Inner Join: An inner join is the usual join operation using a comparison operator. It displays only the rows with a match for both join tables.

ii) Outer Join: An outer join includes the left outer join, right outer join, and full outer join.

The relational operators for an outer join are:
a) Left outer join: Left join returns all the rows from the first table in the JOIN clause with NULLS for the second table’s columns if no matching row was found in the second table.

b) Right outer join: Right join returns all the rows from the second table in the JOIN clause with NULLS for the first table’s columns if no matching row was found in the first table.

c) Full outer join: When a Full Outer Join occurs and a row from either the first table or the second table does not match the selection criteria, the row is selected and the columns of the other tables are set to NULL.
iii) Cross Join: A Cross Join results in the cross product of two tables and returns the same rows as if no WHERE clause was specified in an old, non-ANSI-style.

Stored Procedure
A stored procedure is essentially a routine you create that will execute an SQL statement. There are four major reasons why using them can be beneficial.

1. They can dramatically increase security.
If you set up a series of Stored Procedures to handle all of your interaction to the data then this means that you can remove all the user rights on all of your tables and such. For example, say I create a stored procedure for inserting a new employee. I can remove everyone's rights to the actual EMPLOYEES table and require them to only do INSERTS via the stored procedure. I have effectively forced them to always insert data my way.

2. They can assist you in centralizing your code.
If I ever need to change the structure of that EMPLOYEES table I don't have to worry about any applications crashing if when they try to insert something new. Since all interaction is via that stored procedure I just have to make the updates in that one stored procedures code and nowhere else.

3. They are executed on the Server's machine.
Because they actually reside on the server's machine, they will use the process resources there as well. Generally your Database Server will be much more 'beefy' as far as processor and memory resources go than your clients machines.

4. Better than that. (They are precompiled)
The database can convert your stored procedure into binary code and execute it as one command rather than parse the SQL statement through an interpreter as if it was text. Execution speeds can be vastly improved by this alone.

Q. What are the types of User-Defined Functions available in SQL Server 2000?
There are three types of UDF in SQL Server 2000:
? Scalar functions: returns one of the scalar data types. Text, ntext, image, cursor or timestamp data types are not supported.
? Inline table-valued functions: returns a variable of data type table whose value is derived from a single SELECT statement.
? Multi-statement table-valued functions: return a table that was built with many TRANSACT-SQL statements.

Q. What is the difference between User-Defined functions and Stored Procedures?
? A stored procedure may or may not return values whereas a UDF always return values.
? The function can't perform any actions that have side effects. This basically means that the function can't perform any changes to a resource outside the function itself. You can't create a procedure that modifies data in a table, performs cursor operations on cursors that aren't local to the procedure, sends email, creates database objects, or generates a result set that is returned to the user.
? SELECT statements that return values to the user aren't allowed. The only allowable SELECT statements assign values to local variables.
? Cursor operations including DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE can all be performed in the cursor. FETCH statements in the function can't be used to return data to the user. FETCH statements in functions can be used only to assign values to local variables using the INTO keyword. This limitation is also minor because you can populate a table variable within a cursor and then return the table to the user.
? UDFs can return only one rowset to the user, whereas stored procedures can return multiple rowsets.
? UDFs cannot call stored procedures (except extended procedures), whereas stored procedures can call other procedures.
? UDFs also cannot execute dynamically constructed SQL statements.
? UDFs cannot make use of temporary tables. As an alternative, you are allowed to use table variables within a UDF. Recall however, that temporary tables are somewhat more flexible than table variables. The latter cannot have indexes (other than a primary and unique key); nor can a table variable be populated with an output of a stored procedure.
? RAISERROR statement cannot be used within a UDF. In fact, you can't even check the value of the @@ERROR global variable within a function. If you encounter an error, UDF execution simply stops, and the calling routine fails. You are allowed to write a message to the Windows error log with xp_logevent if you have permission to use this extended procedure.

No comments:

Post a Comment