SQL Server Q&A

Hi,

Below is the facts on the SQL Server, which might help from the interview perspective.

sp_help	To Display Information About a Database Object
sp_depends	To Display Information on Database Object Dependencies
sp_helpconstraint	To list all constraints defined for a specified table
sp_recompile	To recompile Procedures & Triggers
sp_helptext	Print the Text of the Stored Procedure
sp_helpextendedproc	Show the Name of the DLL Associated with an Extended Stored Procedure
sp_helpindex	Display Indexes Defined for a Table
sp_settriggerorder	To set the order in which Multiple triggers executes

Performance Impact of Constraints

· Constraints help maintain database integrity in a number of ways; however, such protections introduce performance drawbacks.

· Adding constraints can slow down the process of bulk imports, as well as other highspeed operations that need to occur against your user-defined tables.

· If you are doing bulk insert operations, you may want to consider temporarily dropping constraints (and indexes too) as a means of decreasing the load time.

Constraints vs Triggers

· If you have a choice between triggers and constraints, choose constraints if the functionality will meet your application needs.

· Constraints operate against the data before a change is made and are generally faster and less of a performance drain.

· Triggers, generally should be used for more complex business logic and data-driven decision-making.

· There are also cases where constraints cannot be used; for example, when you require referential integrity for cross-database or cross-server tables.

Unique Constraint

· You can choose multiple UNIQUE constraints for each table and are

· Allowed to set a UNIQUE constraint for columns that allow NULL values (although only one NULL value is allowed per column).

· Like primary keys, UNIQUE constraints enforce entity integrity by ensuring that rows can be uniquely identified.

Foreign Key Reference

· The referenced table column or columns must be part of a primary key or unique constraint.

· The referenced and referencing columns should also be of the same data type.

· Also, you cannot define foreign key constraints across databases or servers (for that, use triggers).

Cascading Change with ON DELETE

· The two types of cascading changes are ON DELETE and ON UPDATE.

· When ON DELETE is used, if someone attempts to delete a primary key or unique value, referencing rows in the foreign key table will also be deleted.

· Cascading updates and deletes can sometimes have a negative effect on performance; so do test this behavior before deploying it in a production environment

Use ROWGUIDCOL

· Unlike the IDENTITY column, which guarantees uniqueness within the defined table, the ROWGUIDCOL property ensures uniqueness across all databases in the world.

· This unique ID is generated by a combination of the ROWGUIDCOL property, the UNIQUEIDENTIFIER data type, and the NEWID() function

CREATE TABLE BookShipment

(unqShipmentID uniqueidentifier ROWGUIDCOL CONSTRAINT defID DEFAULT NEWID(),

vchShipmentDescription varchar(100) NULL)

Disable and Enable Constraints

ALTER TABLE <table_name>

[NOCHECK or CHECK] CONSTRAINT [ALL or <constraint_name> [,] <constraint_name_n…>]

Temporary Table

· Temporary tables are defined just like regular tables, only they are stored in the tempdb database.

· Temporary tables are often used to store temporary results, for use in queries, updates, and stored procedures.

· They can be used to return or pass a set of results from a called procedure to a calling stored procedure.

· Developers often use temporary tables to avoid Transact-SQL cursors.

· Temporary tables are defined like regular tables, and can have indexes, constraints (but not foreign key constraints), and other related elements just like regular tables.

· Temporary tables suffers due to the performance overhead that they sometimes incur, including excessive transaction logging, and locking of database objects.

· There are two different temporary table types: local and global.

Local Temporary Table

· Available for use by the current user connection that created them.

· They are defined by adding a two pound (##) signs.

· Multiple connections can create the same named temporary table for local temporary tables without encountering conflicts

· The internal representation of the local table is given a unique name so as not to conflict with other temporary tables with the same name created by other connections in the tempdb.

· Local temporary tables are dropped by using the DROP statement or are automatically removed when the user connection is closed.

Global Temporary Table

· Once a connection creates a global temporary table, any user with access to the current database can access.

· the tableThey are defined by adding a single pound (#) sign.

· You cannot create simultaneous versions of a global temporary table, as this will generate a naming conflict.

· Global temporary tables are removed if explicitly dropped by DROP TABLE or, automatically, after the last connection exits and the global temporary table is no longer referenced by any connections.

Table Variable

· Can sometimes work more efficiently than a temporary table, as SQL Server automatically clears it after it has been used; you do not need to DROP a table variable explicitly.

· Table variables can incur less locking and logging than a temporary table, because they are only available for the duration of the batch, function, or stored procedure where they are used.

· Table variables can also reduce recompilations of a stored procedure, compared to the temporary table counterparts.

DECLARE @<variable_name> TABLE (<table_definition>)

DECLARE @Authors TABLE

(iAuthorId int NOT NULL ,

vchLastName varchar (50) NULL ,

vchFirstName varchar (50) NULL)

Views

· Is a virtual representation of a table

· Used to provide de-normalized data.

· Views are also useful For Managing Security And Protecting sensitive data. If you wish to obscure the database's schema from the end user, you can grant permissions exclusively to views, rather than the underlying table.

Regular Views

· You define a regular view with a SELECT statement.

· Views are also updateable if they meet the following conditions:

o They have no aggregate functions or the TOP, GROUP BY, UNION, or DISTINCT clauses in their definition.

o They don't contain computed values or values based on functions.

o Any non-nullable columns on the base table must be included in the view definition.

· DELETE operations are only allowed if the view references one table in the FROM clause that defines the view.

CREATE VIEW <view_name>

{WITH ENCRYPTION | WITH SCHEMABINDING | WITH VIEW_METADATA }

AS

<select_statement>

{WITH CHECK OPTION }

GO

Note:

WITH CHECK OPTION	Used to make sure any INSERT activity in the view does not include a value that cannot be read from that view.
WITH ENCRYPTION	To not to allow your view definition to be viewable

Indexed Views

Note: Indexed Views is diferent than Clustered Index on the Table. Reason - The underlying (base) tables are not impacted physically by the creation of indexed views, as the view is treated like a separate database object.

· A view is no more efficient than the underlying SELECT query used to define it, unless an index is added to it.

· You must first create a unique clustered index on the view. Once this view has been built, the data that makes up the view is stored in much the same way as a clustered index for a table is stored.

· You can also create additional nonclustered indexes, as you would for a regular table. Prior to creating nonclustered indexes on the view, you must first define the clustered index.

· An indexed view are used implicitly even if the table name is used in the from clause, in place of View name.

· Are more useful for static data (reporting or data warehousing data), than data that is frequently updated; this is because base tables with frequent updates will trigger frequent index updates against the view, potentially reducing the benefit of query performance against data update speed.

Stored Procedures

· Produce faster execution times than ad hoc queries

· The key to a stored procedure's good performance is SQL Server's ability to store a Pre-Compiled Execution Plan for commands used within the stored procedure

· Increased Network performance - As the Transact-SQL statements from the stored procedure are invoked on the server itself, reducing Transact-SQL calls over the network. This leaves only the stored procedure call and parameters and returned results (when applicable) to be transferred over the network.

· Force a Stored Procedure to Recompile - When table data referenced from within a stored procedure undergoes significant changes, or new indexes are added to the referenced tables.

Extended Stored Procedures

· Allows to extend the functionality of SQL Server by referencing functionality from dynamic link libraries (DLLs).

· Extended stored procedures are defined by referencing a DLL file and are not defined with Transact-SQL statements like a regular stored procedure.

· Because a DLL is loaded and used from within SQL Server, there is an increased risk of memory leaks or performance issues (depending on the DLL). If a DLL misbehaves, SQL Server can crash.

sp_addextendedproc @functname = <'procedurename'>,

@dllname = <'dll_name'>

User-Defined Functions

· Were introduced in SQL Server 2000.

· Types of UDFs

o Scalar UDFs –

§ Return one value, and cannot return table data types.

o Inline UDFs

§ Return a table data type, and do not explicitly define the returned table, but rather use a single SELECT statement for defining the returned rows and columns

o Multistatement UDFs

§

Stored Procedures	User-Defined Functions
Accept parameters	Same
Can contain output parameters	Cannot contain output parameters
Optional	Must always return a scalar value or a table
No	Can be used within your queries (Select, Where & From) and DML statements. Used as a substitute for a sub-query.
	A function call can also be joined with other tables.

Indexes

· Indexes assist with query processing by speeding up data access against tables and views.

· Indexes are defined by selecting an index key, which is made up of one or more columns from the table or view.

· If indexes do not exist for a table, that table is called a Heap; meaning the data pages are stored in no particular order. A heap referenced in queries results in a table scan. A table scan occurs when all rows in the table are evaluated against the required end-result (instead of just a subset of the data).

· Two types of indexes, Clustered And Nonclustered.

· SQL Server uses a B-Tree data structure for each clustered and nonclustered index.

· When an index is used, SQL Server navigates from the Btree structure's root and traverses down to the leaf nodes.

Clustered Indexes

· Determine the physical order of data in a table.

· You can only have one clustered index for each table, as the data pages can only be physically stored in one way

· After a clustered index is created, data pages are physically contiguous, which can assist with the speed of queries.

· For a table with a clustered index, the leaf nodes are the data pages themselves.

· Good candidates - Columns that are

o Queried often in range queries (Between, <, >)

o Order large result sets,

o Used in aggregate functions, and

o Whose contents are distinct (primary or unique keys)

o Lastly, Choose a Smaller Clustered Index Key over a wider column, reducing the size of the index needed and improving I/O operations.

· Bad candidates - Columns that are

o Frequently updated columns and

o Non-unique columns

Non-Clustered Indexes

· It stores index pages separately from the physical data, with pointers to the physical data located in the index pages and nodes. In a way it contains poitner to the Clustered Index leaf pages

· Nonclustered index columns are stored in the order of the index key column values.

· You can have up to 249 nonclustered indexes on each table or indexed view.

· For nonclustered indexes, the leaf node level is the Index Key Coupled To A Bookmark.

· The nonclustered index bookmark points to the B-Tree structure of the table's clustered index (if one exists). If the base able is a heap, the nonclustered index bookmark points to the table row's Row-ID.

· Good candidates - Columns that are

o Used in Where clause

o Order smaller result sets on unique data

o involved in searchable arguments using operators such as =, <, >, IN, EXISTS, and LIKE.

To Index or not to Index?

· Indexes take up space, and should not be added 'just in case'. View Tools for testing the effectiveness of your indexes.

· Indexes can slow down data loads significantly, and may not make sense for tables that are used expressly for data Imports And Staging.

· If your table will not be queried and is used as an intermediate staging ground, leaving indexes off the table may be a better choice.

· The same goes for very small tables where, no matter what, the query optimizer always performs a table scan.

· Keep in mind that heaps become naturally fragmented over time and, if data updates are frequent enough, even small tables can begin to take up more space than necessary. There are those who believe that a clustered index should exist for every table in the database, period.

· One technique for reducing bulk load operations on a table is to remove the index during the load and add the index once the load is finished

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[ WITH < index_option > [ ,...n] ]

[ ON filegroup ]

< index_option > :: =

734

{ PAD_INDEX |

FILLFACTOR = fillfactor |

IGNORE_DUP_KEY |

DROP_EXISTING |

STATISTICS_NORECOMPUTE |

SORT_IN_TEMPDB

}

Triggers

· There are two types of triggers,

o AFTER triggers - Execute after the data modification has been completed against the table

o INSTEAD OF triggers -

§ Execute instead of the original data modification.

§ INSTEAD OF triggers are allowed for both tables and views

· Multiple triggers can be defined for one table. The order in which they are fired can be configured using the sp_settriggerorder stored procedure

· The Nested Triggers server option will determine if triggers can cause other triggers to fire (cascading triggers). If enabled, triggers can cascade in a chain of up to 32 firings.

· SQL Server creates two "virtual" tables specifically for triggers, the Deleted and Inserted tables. These two tables capture before and after pictures of the modified data.

· We can Enable/Disable triggers.

Operation	Inserted Table	Deleted Table
INSERT	New Rows	-
UPDATE	New Rows	Old Rows
DELETE	-	Deleted Rows

· Points to Remember

o Triggers should be written to run quickly.

o Constraints usually run faster than a trigger

o You can only create triggers on table/viewss that you own

o If cascading referential integrity for DELETE or UPDATE is defined for the base table, you cannot define an INSTEAD OF DELETE or INSTEAD OF UPDATE on that table.

Creating an AFTER trigger

CREATE TRIGGER trg_BookAudit ON Books FOR INSERT

AS

BEGIN

UPDATE Inventory

SET iTotalBooks = iTotalBooks + (SELECT COUNT(*) FROM inserted)

END

GO

Creating an INSTEAD OF trigger

CREATE TRIGGER trg_BookAuthor_INSTEAD ON vBookAuthors INSTEAD OF INSERT

AS

INSERT Books (vchBookName, iAuthorId)

SELECT inserted.vchBookName, inserted.iAuthorId FROM inserted

INSERT Authors (iAuthorId, vchLastName, vchFirstName)

SELECT inserted.iAuthorId, inserted.vchLastName, inserted.vchFirstName

FROM inserted

GO

DELETE TABLE and TRUNCATE TABLE

DELETE TABLE	TRUNCATE TABLE
Logging	NA
No RollBack	Can be RollBack
Can have criteria	NA

Thanks & Regards,

Arun Manglick || Tech Lead