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
