Sql Code Masterz

What is difference between DELETE and TRUNCATE commands?

2013-10-23T03:55:00.001-07:00

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, its columns, constraints, indexes and so on, remains.
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 cannot be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table

DELETE:

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. If you want to retain the identity counter, use DELETE instead.
If you want to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE can be rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.

Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

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

2013-10-23T03:45:00.000-07:00

Both primary key and unique key enforces 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 types of Collation Sensitivity?

2013-10-23T03:42:00.003-07:00

Case sensitivity - A and a, B and b, etc.

Accent sensitivity

Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity - A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

What are the different index configurations a table can have?

2013-10-23T03:40:00.001-07:00

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

Schema Binding and Indexed Views in SQL Server

2012-09-11T20:47:00.000-07:00

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 without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.

In SQL Server, views are not bound to the schema of the base tables by default. In such case we may change the schema of the base table at any time, regardless of the fact that the associated view may or may not work with the new schema. We can even drop the underlying table while keeping the associated view without any warning. In this case when the view is used, we will get an invalid object name error for the base table.
So if you want to create an index on a view or you want to preserve the base table schema once a view has been defined, in both these cases you have to use the "WITH SCHEMABINDING" clause to bind the view to the schema of the base tables.
To use schema binding for views, first we will create a sample table in AdventureWorks database then a view "vw_sampleView" is created on this new table.
Create sample table and sample view

USE AdventureWorks

GO

SELECT * INTO SampleTable

FROM sales.SalesOrderDetail

GO

CREATE VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS

SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate

FROM dbo.SAMPLETABLE

GO

--If the view already existed we could use this to add SCHEMABINDING

ALTER VIEW [dbo].[vw_sampleView] WITH SCHEMABINDING AS

SELECT salesorderid, productid, unitprice, linetotal, rowguid,modifieddate

FROM dbo.SAMPLETABLE

GO
Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.
So here we are with a SCHEMABOUND view "sampleView" on base table "sampleTable". Now we will check that we are able to create an index on the view.

An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes. The script below shows us how we can create this unique clustered index or you can do this via SQL Server Management Studio (SSMS).
Create Unique clustered index on view

CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[vw_sampleView]

(

[rowguid] ASC

)

GO

When this is run the command successfully creates the unique clustered index "TestIndex" on "vw_sampleview". At this point we can add any additional indexes as needed.
Here are some things to consider:
You can not create an index on a view with outer joins used in it, even if you use schema binding

You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".

You will not be able to create clustered index on a view if the view references any nondeterministic functions.

You can not use aggregate functions when using schema binding.

You can not migrate the base table of a schema bound view.

--------------------------------------------------------------------------------
Now we are left with our second part of the problem, how schema preservation is implemented for the base table. Again we have our "sampleTable" and "vw_sampleView" with schema binding. We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.

By using SSMS to alter the data type of a column in the base table"sampleTable", I get a warning message notifying me that because of the schema bound view this will not work.

To change the base table we need to remove the SCHEMABINDING option from our objects or drop and recreate these objects once the table has been altered.

On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.

Ref :- http://www.mssqltips.com/sqlservertip/1610/sql-server-schema-binding-and-indexed-views/

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

2012-09-05T23:02:00.001-07:00

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 consecutive integers.

The DENSE_RANK ranking function returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question. If two or more rows tie for a rank in the same partition, each tied rows receive the same rank.

@@IDENTITY VS SCOPE_IDENTITY() VS IDENT_CURRENT

2012-06-02T06:06:00.003-07:00

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 than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)

It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Types of User Defined Functions

2012-05-23T21:13:00.000-07:00

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(GETDATE())
Notice the User Defined Function must be prefaced with the owner name, DBO in this case. In addition, GETDATE can be used as the input parameter, but could not be used inside the function itself. Other built in SQL functions that cannot be used inside a User Defined Function include: RAND, NEWID, @@CONNCECTIONS, @@TIMETICKS, and @@PACK_SENT. Any built in function that is non-deterministic.
The statement begins by supplying a function name and input parameter list. In this case, a date time value will be passed in. The next line defines the type of data the UDF will return. Between the BEGIN and END block is the statement code. Declaring the output variable was for clarity only. This function should be shortened to:

CREATE FUNCTION testDateOnly(@InDateTime datetime)

RETURNS varchar(10)

AS

BEGIN

        RETURN CONVERT(varchar(10),@InDateTime,101)

END

Inline Table UDFs

These User Defined Functions return a table variable that was created by a single select statement. Almost like a simply constructed non-updatable view, but having the benefit of accepting input parameters.
This next function looks all the employees in the pubs database that start with a letter that is passed in as a parameter. In Query Analyzer, enter and run:

USE pubs

GO

CREATE FUNCTION dbo.LookByFName(@FirstLetter char(1))

RETURNS TABLE

AS

RETURN SELECT *

FROM employee

WHERE LEFT(fname, 1) =  @FirstLetter

To use the new function, enter:

SELECT * FROM dbo.LookByFName('A')

All the rows having a first name starting with A were returned. The return is a Table Variable, not to be confused with a temporary table. Table variables are new in SQL 2000. They are a special data type whose scope is limited to the process that declared it. Table variables are stated to have performance benefits over temporary tables. None of my personal testing has found this result though.

Multi Statement UDFs

Multi Statement User Defined Functions are very similar to Stored Procedures. They both allow complex logic to take place inside the function. There are a number of restrictions unique to functions though. The Multi Statement UDF will always return a table variable–and only one table variable. There is no way to return multiple result sets. In addition, a User Defined Function cannot call a Stored Procedure from inside itself. They also cannot execute dynamic SQL. Remember also, that UDFs cannot use non-deterministic built in functions. So GETDATE and RAND cannot be used. Error handling is restricted. RAISERROR and @@ERROR are invalid from inside User Defined Functions. Like other programming languages, the purpose of a User Defined Function is to create a stand-alone code module to be reused over and over by the global application.
For a Multi Statement test, we will create a modified version of the LookByFName function. This new function will accept the same input parameter. But rather than return a table from a simple select, a specific table will be created, and data in it will be manipulated prior to the return:

CREATE FUNCTION dbo.multi_test(@FirstLetter char(1))

RETURNS @Result TABLE

        fname varchar(20),

        hire_date datetime,

        on_probation char(1)

AS

BEGIN

        INSERT INTO @Result

               (fname, hire_date)

               SELECT fname, hire_date

               FROM employee

               WHERE LEFT(fname, 1) =  @FirstLetter

        UPDATE @Result

        SET on_probation = 'N'

        UPDATE @Result

        SET on_probation = 'Y'

        WHERE hire_date < '01/01/1991'

        RETURN

END

To use the new function, execute:

SELECT * FROM dbo.multi_test('A')

With the new Multi Statement Function, we can manipulate data like a Stored Procedure, but use it in statement areas like a View.
For example, only specific columns can be returned.

SELECT fname FROM dbo.multi_test('A')

The function can also be joined like a view:

SELECT e.lname, f.fname

FROM employee e INNER JOIN dbo.multi_test('A') f ON e.fname = f.fname

Advantages and Disadvantages of User Defined Functions

2012-05-23T21:07:00.003-07:00

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 the function is called, a different value is returned. Therefore, GETDATE cannot be called from inside a UDF you create.

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

2012-05-19T09:17:00.000-07:00

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.
Distribution - Database responsible for managing replicated data. This database could reside on the publisher or subscriber.

System Databases Do's and Don'ts

2012-05-19T09:08:00.000-07:00

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. So you can review the objects and learn from the techniques used by Microsoft.
Dropping Objects - The most prominent reason to drop system objects are for specific types of lock downs and auditing in particular industries. Although some of those practices are well documented, be sure you understand the ramifications related to administering and developing applications once those restrictions are in place.
Security - Do not forget about the Public role and Guest user, they are the conduit for users to access the system objects. So that should answer the question of how people (logins\users) can access the objects based on the object owner or schema, depending on the SQL Server version.
Backups - Be sure to have a consistent backup process for your system databases. Including the system databases with your user defined databases might be the best approach if a disaster occurs.
Scope - Each SQL Server instance (including the Express Edition) has its own set of SQL Server system databases. As such, if a single Windows server has multiple SQL Server instances installed, a change to one system database only impacts the single instance, not all instances on the Windows server.

SQL Server System Databases

2012-05-19T09:05:00.001-07:00

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 upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
- Prominent Functionality
  - System object definition
- Additional Information
  - Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
  - Prior to SQL Server 2005 the system related data was stored in the master database
  - Read-only database that is not accessible via the SQL Server 2005 tool set
  - The database ID for the Resource database is 32767
  - The Resource database does not have an entry in master.sys.databases
TempDB Database
- Purpose - 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.
- Prominent Functionality
  - Manage temporary objects listed in the purpose above
- Additional Information
  - Properly Sizing the TempDB Database
  - Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
Model Database
- Purpose - Template database for all user defined databases
- Prominent Functionality
  - Objects
  - Columns
  - Users
- Additional Information
  - User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
  - The database configurations such as the recovery model for the Model database are applied to future user defined databases
MSDB
- Purpose - Primary database to manage the SQL Server Agent configurations
- Prominent Functionality
  - SQL Server Agent Jobs, Operators and Alerts
  - DTS Package storage in SQL Server 7.0 and 2000
  - SSIS Package storage in SQL Server 2005
- Additional Information
  - Provides some of the configurations for the SQL Server Agent service
  - For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database
  - Missing SQL Server Agent History
  - MSSQLTips Category - SQL Server Agent
Distribution
- Purpose - Primary data to support SQL Server replication
- Prominent Functionality
  - Database responsible for the replication meta data
  - Supports the data for transaction replication between the publisher and subscriber(s)
- Additional Information
  - MSSQLTips Category - Replication
ReportServer
- Purpose - Primary database for Reporting Services to store the meta data and object definitions
- Prominent Functionality
  - Reports security
  - Job schedules and running jobs
  - Report notifications
  - Report execution history
- Additional Information
  - MSSQLTips Category - Reporting Services
ReportServerTempDB Database
- Purpose - Temporary storage for Reporting Services
- Prominent Functionality
  - Session information
  - Cache
- Additional Information
  - MSSQLTips Category - Reporting Services

SQL Text vs VARCHAR

2012-05-16T04:02:00.002-07:00

`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 to VARCHAR to assign a value for them).
TEXT is good:

If you need to store large texts in your database
If you do not search on the value of the column
If you select this column rarely and do not join on it.

VARCHAR is good:

If you store little strings
If you search on the string value
If you always select it or use it in joins.

By selecting here I mean issuing any queries that return the value of the column.
By searching here I mean issuing any queries whose result depends on the value of the TEXT or VARCHAR column. This includes using it in any JOIN or WHERE condition.
As the TEXT is stored out of row, the queries not involving the TEXT column are usually faster.
Some examples of what TEXT is good for:

Blog comments
Wiki pages
Code source

Some examples of what VARCHAR is good for:

Usernames
Page titles
Filenames

As a rule of thumb, if you ever need you text value to exceed 200 characters AND do not use join on this column, use TEXT.
Otherwise use VARCHAR.

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

2012-05-15T21:35:00.003-07:00

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?

2012-05-14T23:09:00.001-07:00

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?

2012-05-14T23:07:00.001-07:00

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 fractional second precision, from 0 to 7 digits of precision.

The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware version of a datetime datatype. The name will appear less odd when you consider what it really is: a date + a time + a time-zone offset. The offset is based on how far behind or ahead you are from Coordinated Universal Time (UTC) time.

The DATETIME2 Type: It is an extension of the datetime type in earlier versions of SQL Server. This new datatype has a date range covering dates from January 1 of year 1 through December 31 of year 9999. This is a definite improvement over the 1753 lower boundary of the datetime datatype. DATETIME2 not only includes the larger date range, but also has a timestamp and the same fractional precision that TIME type provides

What is MERGE Statement?

2012-05-14T23:05:00.002-07:00

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 )

2012-05-14T23:05:00.000-07:00

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?

2012-05-14T23:03:00.001-07:00

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?

2012-05-14T23:01:00.001-07:00

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

2012-05-14T23:00:00.001-07:00

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?

2012-05-14T22:57:00.001-07:00

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?

2012-05-14T22:56:00.000-07:00

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

2012-05-14T22:54:00.002-07:00

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 right table do not appear.

Right Outer Join: In Right Outer Join all rows in the second-named table i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

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

2012-05-13T22:00:00.002-07:00

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.