Posts

Showing posts from 2012

Schema Binding and Indexed Views in SQL Server

Problem Recently while creating an archival job I noticed that job was taking way too long to complete. To help troubleshoot I executed the stored procedure directly and it was also taking a long time to fetch the rows from the view that I was using. Based on the query plan it looked like creating an index on the view may help the issue, so I first looked to see if any indexes were in place for the views, but none were found. The next step was to create an index on the view, but I was presented with this error message "Cannot create index on view, because the view is not schema bound". Solution The error message "Cannot create index on view '*' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)" clearly suggested that in order to create an index on this view I have to make it schema bound, but what is schema binding and how does this work? Schema binding ties an object to the base object that this new object depends upon. So

Which of the following ranking functions distribute the rows in an ordered partition into a specified number of groups

Which of the following ranking functions distribute the rows in an ordered partition into a specified number of groups? A. ROW_NUMBER B. RANK C. DENSE_RANK D. NTILE E. None of the Above Ans :- D Explanation:- The NTILE ranking function distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs. The ROW_NUMBER ranking function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. The RANK ranking function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. Just like the DENSE_RANK, in the RANK ranking function, if two or more rows tie for a rank, each tied rows receive the same rank. Unlike the DENSE_RANK, the RANK ranking function does not always return con

@@IDENTITY VS SCOPE_IDENTITY() VS IDENT_CURRENT

SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. SELECT SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather t

Types of User Defined Functions

Types of User Defined Functions There are three different types of User Defined Functions. Each type refers to the data being returned by the function. Scalar functions return a single value. In Line Table functions return a single table variable that was created by a select statement. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required. Scalar UDFs Our first User Defined Function will accept a date time, and return only the date portion. Scalar functions return a value. From inside Query Analyzer, enter:   CREATE FUNCTION dbo.DateOnly(@InDateTime datetime) RETURNS varchar(10) AS BEGIN         DECLARE @MyOutput varchar(10)         SET @MyOutput = CONVERT ( varchar(10) ,@InDateTime,101)         RETURN @MyOutput END To call our function, execute: SELECT dbo.DateOnly( GETD

Advantages and Disadvantages of User Defined Functions

Advantages of User Defined Functions Before SQL 2000, User Defined Functions (UDFs), were not available. Stored Procedures were often used in their place. When advantages or disadvantages of User Defined Functions are discussed, the comparison is usually to Stored Procedures. One of the advantages of User Defined Functions over Stored Procedures, is the fact that a UDF can be used in a Select, Where, or Case statement. They also can be used to create joins. In addition, User Defined Functions are simpler to invoke than Stored Procedures from inside another SQL statement. Disadvantages of User Defined Functions User Defined Functions cannot be used to modify base table information. The DML statements INSERT, UPDATE, and DELETE cannot be used on base tables. Another disadvantage is that SQL functions that return non-deterministic values are not allowed to be called from inside User Defined Functions. GETDATE is an example of a non-deterministic function. Every time th

What are the SQL Server system databases and can you outline the general functionality of each database?

What are the SQL Server system databases and can you outline the general functionality of each database? Master - Database responsible for SQL Server instance related data.  You can also think of this database corresponding to the Windows SQL Server service account. Resource - Database responsible for SQL Server system objects.  This database was introduced in SQL Server 2005 and is intended to ease the upgrade and rollback of SQL Server system objects. Model - Template database for the creation of new user defined databases. MSDB - Database responsible for SQL Server Agent related data such as Jobs, Alerts, Operators, etc. TempDB - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc.  Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database. Distribu

System Databases Do's and Don'ts

System Databases Do's and Don'ts Data Access - Based on the version of SQL Server query only the recommended objects.  In general the system database objects are being deprecated to a set of views, so be sure all of your scripts are accessing the right objects.  If not, you are going to have a big project in the future to convert all of your scripts. Changing Objects - Do not change system objects.  In SQL Server 2005 all of the database objects have been moved to the Resource database which stores the definition for the system objects and can be updated via new SQL Server releases independent of the data. New Objects - Creating objects in the system databases is not recommended.  If you have objects that are needed for the instance i.e. administrative items, just create a separate DBA database to store these objects. Sneaking a Peak - Up to this point, all of the T-SQL code for the tables, views, stored procedures, functions, etc. has been clear text.

SQL Server System Databases

SQL Server System Databases Master Database Purpose - Core system database to manage the SQL Server instance.  In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema.  In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects. Prominent Functionality Per instance configurations Databases residing on the instance Files for each database Logins Linked\Remote servers Endpoints Additional Information The first database in the SQL Server startup process In SQL Server 2005, needs to reside in the same directory as the Resource database Sources for Database Information - SQL Server 2000 to 2005 Crosswalk SQL 2000 to 2005 Crosswalk - Startup Parameters Resource Database Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the u

SQL Text vs VARCHAR

When to use VARCHAR and When to use TEXT TEXT is used for large pieces of string data. If the length of the field exceeed a certain threshold, the text is stored out of row. VARCHAR is always stored in row and has a limit of 8000 characters. If you try to create a VARCHAR(x) , where x > 8000 , you get an error: Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000) These length limitations do not concern VARCHAR(MAX) in SQL Server 2005 , which may be stored out of row, just like TEXT . Note that MAX is not a kind of constant here, VARCHAR and VARCHAR(MAX) are very different types, the latter being very close to TEXT . In prior versions of SQL Server you could not access the TEXT directly, you only could get a TEXTPTR and use it in READTEXT and WRITETEXT functions. In SQL Server 2005 you can directly access TEXT columns (though you still need an explicit cast

SQL SERVER – ACID (Atomicity, Consistency, Isolation, Durability)

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved. Atomicity is an all-or-none proposition. Consistency guarantees that a transaction never leaves your database in a half-finished state. Isolation keeps transactions separated from each other until they’re finished. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. Above four rules are very important for any developers dealing with databases. Reference : Pinal Dave ( http://blog.SQLAuthority.com )

What is XML Datatype?

What is XML Datatype? The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of SQL Server.

Which are new data types introduced in SQL SERVER 2008?

Which are new data types introduced in SQL SERVER 2008? The GEOMETRY Type: The GEOMETRY data type is a system .NET common language runtime (CLR) data type in SQL Server. This type represents data in a two-dimensional Euclidean coordinate system. The GEOGRAPHY Type: The GEOGRAPHY datatype’s functions are the same as with GEOMETRY. The difference between the two is that when you specify GEOGRAPHY, you are usually specifying points in terms of latitude and longitude. New Date and Time Datatypes: SQL Server 2008 introduces four new datatypes related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2. DATE : The new DATE type just stores the date itself. It is based on the Gregorian calendar and handles years from 1 to 9999. TIME : The new TIME ( n ) type stores time with a range of 00:00:00.0000000 through 23:59:59.9999999. The precision is allowed with this type. TIME supports seconds down to 100 nanoseconds. The n in TIME ( n ) defines this level of f

What is MERGE Statement?

What is MERGE Statement? MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

What is CTE(Common Table Expression )

What is CTE? CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement . A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query

What is Replication and Database Mirroring?

What is Replication and Database Mirroring? Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients which are known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.

What is Service Broker?

What is Service Broker? Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is feature which provides facility to SQL Server to send an asynchronous, transactional message. it allows a database to send a message to another database without waiting for the response, so the application will continue to function if the remote database is temporarily unavailable

What is an execution plan in SQL server

What is an execution plan? When would you use it? How would you view the execution plan? An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

What is DataWarehousing?

What is DataWarehousing? Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together; Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time; Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting. Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

What is Identity in SQL Server?

What is Identity? Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

What are different Types of Join in SQL Server

What are different Types of Join? Cross Join A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price. Inner Join A join that displays only the rows that have a match in both joined tables is known as inner Join.  This is the default type of join in the Query and View Designer. Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join.  You can create three different outer join to specify the unmatched rows to be included: Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left” table, which appears leftmost in the JOIN clause are included. Unmatched rows in the rig

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?

Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible? Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.

What is SQL server agent?

What is SQL server agent? SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

What is SQL Profiler?

What is SQL Profiler? SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly. Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query

Difference between Function and Stored Procedure?

Difference between Function and Stored Procedure? UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

What is difference between DELETE & TRUNCATE commands?

What is difference between DELETE & TRUNCATE commands? Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. TRUNCATE TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE can not be Rolled back. TRUNCATE is DDL Command. TRUNCATE Resets identity of the table. DEL

What's the difference between a primary key and a unique key?

What's the difference between a primary key and a unique key? Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

What are different type of Collation Sensitivity?

What are different type of Collation Sensitivity? Case sensitivity A and a, B and b, etc. Accent sensitivity a and á, o and ó, etc. Kana Sensitivity When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. Width sensitivity When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive

What is Collation?

What is Collation? Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying casesensitivity, accent marks, kana character types and character width.

What is a Linked Server?

What is a Linked Server? Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is the use of DBCC commands?

What is the use of DBCC commands? DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks. E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked. DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

What is cursors?

What is cursors? 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. In order to work with a cursor we need to perform some steps in the following order: Declare cursor Open cursor Fetch row from the cursor Process fetched row Close cursor Deallocate cursor

What are the different index configurations a table can have?

What are the different index configurations a table can have? A table can have one of the following index configurations: No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered indexes

What is the difference between clustered and a non-clustered index?

What is the difference between clustered and a non-clustered index? A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

What is Index?

What is Index? An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance. Clustered indexe s define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

What is View?

What is View? A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Stored Procedure?

What is Stored Procedure? A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What are different normalization forms?

What are different normalization forms? 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many rela

What is normalization?

What is normalization? Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What is RDBMS?

What is RDBMS? Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage

Reading lines from a file using SQL Server

Create the ReadFileAsTable stored procedure in your test database, and try it out with something like:   Select line from   Dbo . uftReadfileAsTable ( 'MyPath' , 'MyFileName' ) where line not like '#%' --where line doesnt begin with a hash Just fill in an existing file name and path to the file you wish to read, instead of ' MyPath ' and ' MyFileName ', and away you go. This is a method I use for reading web logs and gathering usage statistics. It is also useful where the data feed has to be validated before one can parse it into the final SQL data format. ----------- [dbo].[ReadfileAsTable] ------------- Create FUNCTION [dbo].[ReadfileAsTable] ( @Path VARCHAR(255), @Filename VARCHAR(100) ) RETURNS @File TABLE ( [LineNo] int identity(1,1), line varchar(8000)) AS BEGIN DECLARE @objFileSystem int ,@objTextStream int, @objErrorObject int, @strErrorMessage Varchar(1000),

Composite key vs Candidate Key vs Alternate Keys

Composite Key : A composite key is a combination of more than one column to identify a unique row in a table. Exp : EmpID, EmailID, SSN in Employee table and project ID in project table .if EmpID and project id are put in projectionHours table then combination of empid and project ID called as composite key because combination of these two act as primary key in projectionHours table. Candidate Key: All keys in a table that become unique called as candidate key. Exp : EmpID , EmailID and SSN all will be always unique for any employee in that case all these three columns called as candidate keys. Alternate Key: Among of candidate keys if any single key or combination of keys made as primary key then rest candidate key called as alternate key. Exp : Suppose in employee table EmpID is primary key then Emailid and SSN are called as alternate key mean later on these key can be act as primary key without affecting existing data in table.

How many tables can be used in a single SELECT statement ?

It depends on Version SQL Server 2005: Maximum tables can be 256 SQL Server 2008: Depends on resource availability SQL Server 2008 R2: Depends on resource availability You can confirm by using script given below /*Creating 300 Tables for testing*/ Use [Master] Go Declare @I Int, @Script Varchar(500) Select @I = 1 While (@I <=300) Begin Select @Script = 'Create Table Table' + CAST(@I as varchar) + '(Id Int)' Exec(@Script) Select @I = @I + 1 End Go /*Using all the tables in SELECT statement*/ Use [Master] Go Declare @I Int, @Script Varchar(Max) Select @I = 1 Select @Script = 'Select A1.* From ' While (@I <=299) Begin if (@I >1) Select @Script = @Script + ' Join Table' + CAST(@I+1 as varchar) + ' A' + CAST(@I+1 as varchar) + ' On (' + 'A' + CAST(@I+1 as varchar) + '.Id=' + ' A' + CAST(@I as varchar) + '.Id)' else Select @Script = @Script + 'Table' + CAST

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys . A key formed by combining at least two or more columns is called composite key .

What are user defined data types and when you should go for them?

User defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8) . In this case you could create a user defined data type called Flight_num_type of varchar(8) and use it across all your tables.

What's the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key does not allow NULL s, but unique key allows one NULL only.

How many types of triggers are there in SQL

There are mainily 3 types of triggers DML trigger Instead of trigger written on only view System trigger DML trigger written on table that gets fired on dml events like insert or delete or update operation that trigger may fire  After DML event  Before DML event these are timings for trigger firing Instead of trigger written only on view that view is not modifiable that also gets fired when DML event occurs System trigger gets fired on system event like before log off or after log on or before shutdown this trigger  may be written on   Database level   Schema level  --------------------------------------------------------------------------------------------------------- OR you can see as follows --------------------------------------------------------------------------------------------------------- Based on timing: 1. Before 2. After 3. Instead of Based on Event: 1. DDL (Insert, update, delete ) 2. DML ( Create, alter, analyze, comment ) 3

Reduce memory footprint of a .NET application

Reduce memory footprint of a .NET application   try { Process process = Process . GetCurrentProcess (); process . MaxWorkingSet = loProcess . MaxWorkingSet ; process . Dispose (); } catch { }  

Is it possible to link SQL Server with Oracle server

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

Why to use SET ANSI NULLS ON in store procedure?

When SET ANSI NULLS ON it means ISO Standard is being followed. = and <> should not be used for null comparison.Instead use is null and is not null

What is index tuning

As all of us know it is very typical to choose column for non cluster index. We should consider creating non-cluster index on any column that are frequently referenced in the where clauses of Sql statements.we may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as covered queries and experience excellent performance gains. Index Tuning is the process of finding appropriate column for non-clustered indexes. SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process.

What is NullIf function in sql

NULLIF function returns null value if the two specified expressions are equivalent. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL. NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression. Syntax of NULLIF Function is given below:- NULLIF (expression1 , expression2)

Different types of Security Functions

Security functions are basically used to get information about users and there roles. All the security functions are nondeterministic means they return different value every time they are called with a specific set of values or parameters. Below are the list of some security functions available in sql server. (1)fn_trace_geteventinfo (2)fn_trace_getfilterinfo (3)fn_trace_getinfo (4)fn_trace_gettable (5)HAS_DBACCESS (6)IS_MEMBER  (7)IS_SRVROLEMEMBER (8)SUSER_SID (9)SUSER_SNAME (10)USER_ID (11)USER

Advantage of using temporary table

Below are the advantages of using temporary table in SQLServer (1)Temporary table improves performance. (2)Temporary table makes the complex logic simple. (3)Temporary table cache Intermediate results.

What is SubQuery and its advantage/disadvantahes

A subquery in sql sever is a SELECT statement within another SQL statement. The SQL statement can be SELECT, WHERE clause, FROM clause, JOIN, INSERT, UPDATE, DELETE, SET, DO, or another subquery.The query that contains the subquery is normally called outer query and the subquery itself is called inner query. Why to use subquery:- (1)Subqueries structure a complex query into isolated parts so that a complex query can be broken down into a series of logical steps for easy understanding and code maintenance. (2)Subqueries allow you to use the results of another query in the outer query. (3)In some cases, subqueries can replace complex joins and unions and subqueries are easier to understand. Why not to use subquery:- (1)When subquery is used, the database server (actually the query optimizer) may need to perform additional steps, such as sorting, before the results from the subquery are used. If a query that contains subqueries can be rewritten as

Different ways to move data or database between server and databases

There are many options available to move data or databases in between servers and databases in sql server so we have to choose good option depending upon our requirements. Some of the options we have are:- (1)BACKUP/RESTORE (2)detaching and attaching databases (3)Replication (4)DTS (5)BCP (6)logshipping (7)INSERT...SELECT (8)SELECT...INTO (9)creating INSERT scripts to generate data.

What is pessimistic locking

In pessimistic locking whenever user wants to update any data first it locks the record and till then no one can update data. Other users can only view the data when there is pessimistic locking.

What does ON DELETE CASCADE and ON UPDATE CASCADE do

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables. Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.

What is Composite Indexes in SQL Server

Composite index is an index on two or more columns. Both clustered and nonclustered indexes can be composite indexes. Composite indexes are especially useful in two different circumstances. First, you can use a composite index to cover a query. Secondly we can use a composite index to help match the search criteria of specific queries.

Different properties of subquery?

A subquery must be enclosed in the parenthesis. A subquery must be put in the right hand of the comparison operator, and A subquery cannot contain a ORDER-BY clause. A query can contain more than one sub-queries.

Define function available in catch block of SQL

Below are the different functions use inside CATCH block:- (1)ERROR_NUMBER:- The number of the error that occurred. This is similar to @@ERROR except that it will return the same number for the duration of the CATCH block. (2)ERROR_MESSAGE:- The complete text of the error message including any substitute parameters such as object names. (3)ERROR_LINE:- This is the line number of the batch or stored procedure where the error occurred. (4)ERROR_SEVERITY:- This is the severity of the error. The CATCH block only fires for errors with severity 11 or higher. Error severity from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 are usually software or hardware errors where processing may not be able to continue. (5)ERROR_STATE:- This is sometimes used by the system to return more information about the error. (6)ERROR_PROCEDURE:- If the error was generated inside a stored procedure this will hold the name of the proce

What is the use of Workload governor in SQL?

Workload governor sits between the client and the database engine and counts the number of connections per database instance. If Workload governor finds that the number of connections exceeds eight connections, it starts stalling the connections and slowing down the database engine. One important thing is that it does not limit the number of connections but makes the connection request go slow. By default 32,767 connections are allowed both for SQL SERVER and MSDE. But it just makes the database engine go slow above eight connections.

What is Page Split in SQL

As all of us know pages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand. So when a page becomes full it creates a new page, this process is called as Page Split.