TRIM Function in SQL Server

For trim the spaces SQL Server has no function which can do trimming from both end instead it has function LTRIM and RTRIM.

You can create your own function and use it in your code or query whenever required. It will be termed as UDF.

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

RETURN LTRIM(RTRIM(@string))

END

GO

This function will take input as string which need to be trimmed.

What are different types of Collation Sensitivity?

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 is Execution Plan?

A query plan (or query execution plan) is an ordered set of steps used to access data in a SQL relational database management system. This is a specific case of therelational model concept of access plans.

Since SQL is declarative, there are typically a large number of alternative ways to execute a given query, with widely varying performance. When a query is submitted to the database, the query optimizer evaluates some of the different, correct possible plans for executing the query and returns what it considers the best alternative. Because query optimizers are imperfect, database users and administrators sometimes need to manually examine and tune the plans produced by the optimizer to get better performance.

Timestamp Datatype in SQL Server

“Timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.”

Let us see one small example which proves above statement.

create table TimeStampTesting

(

Name varchar(10),

TS TimeStamp

)

Insert Into TimeStampTesting(Name)

Select 'Ritesh' union all

Select 'Rajan' union all

Select 'Bihag'

GO

--since we are making order by on TS

--Bihag should be first as that record was inserted last

Select * from TimeStampTesting order by TS desc

Go

Update TimeStampTesting set Name='Rajan S.' where Name='Rajan'

GO

--if you observe, this time Bihag wouldn't first

--but Rajan S. would be the first as it updated last

--so TS is a binary unique number which updates itself automatically

--for new upate and/or insert

Select * from TimeStampTesting order by TS desc

Go

BTW, now a day, you should use RowVersion datatype rather than TimeStamp as I told you above too that TimeStamp will be deprecated and RowVersion is synonyms for TimeStamp. 

http://msdn.microsoft.com/en-us/library/ms177566.aspx

What is Sequence Container?

Sequence Containers handle the flow of a subset of a package and can help you divide a

Package into smaller, more manageable pieces. Some nice applications that you can use

Sequence containers for include the following:

·         Grouping tasks so that you can disable a part of the package that ’ s no longer needed
·         Narrowing the scope of the variable to a container
·         Managing the properties of multiple tasks in one step by setting the properties of the      Container
·         Using one method to ensure that multiple tasks have to execute successfully before the next task executes
·         Creating a transaction across a series of data - related tasks, but not on the entire package
·         Creating event handlers on a single container, wherein you could send an email if anything inside one container fails and perhaps page if anything else fails

SSIS Interview Questions

Index Scan VS Index Seek

Index Scan
In index scan, SQL Server scans all the data pages from the first data page to the last data page. For example there is an index existing in the table and the query is fetching large amount of data which is more than 50 percent of the data then the Query Optimizer would just fetch all the data pages to retrieve the desired result sets.

      Also if there is no indexes in the table, then SQL server will automatically do table scan. So table scan and index scan is same but while doing table scan you moved into one more level of data to retrieve original data.

Index Seek
When SQL Server does a seek then it knows where in the index the data is going to be or when fewer number of rows such as only 10% of the whole data needs to be fetched, so it loads the index and directly goes to the part of the index that it needs and reads till the required data is fetched.
Most of the time query optimizer tries to use an Index Seek which indicates that it has found an useful index to fetch the desired result set. But in case it fails to use the index or using index would not help the cause because the fetched number of records is almost around 90% of the whole data then it does Index scan.
Index scan is efficient if the table is small or most of the rows qualify for the record set.

Difference between STUFF and REPLACE

Difference between STUFF and REPLACE

 

STUFF function is used to overwrite existing characters using this syntax: STUFF (string_expression, start, length, replacement_characters), where string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.

 

REPLACE function is used to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), every incidence of search_string found in the string_expression will be replaced with replacement string

DATEDIFF IN SQL SERVER

Date diff tells the difference between the two dates in terms of the entity provided.

DATEDIFF (datepart , startdate , enddate )

Datepart could e mm or dd or yy or mi depends upon the requirement.

Below are the few points related to date diff.

DATEDIFF does not guarantee that the full number of the specified time units passed between

2  datetime Values.

-- Get difference in hours between 8:55 and 11:00

   SELECT DATEDIFF(hh, '08:55', '11:00');

   -- Returns 3 although only 2 hours and 5 minutes passed between times

   -- Get difference in months between Sep 30, 2011 and Nov 02, 2011

   SELECT DATEDIFF(mm, '2011-09-30', '2011-11-02')

   -- Returns 2 although only 1 month and 2 days passed between date

To get the number of full time units passed between date times, you can calculate the difference

in lower Units and then divide by the appropriate number:

   SELECT DATEDIFF(mi, '08:55', '11:00')/60;

   -- Returns 2 hours now

ACID PROPERTIES

In Database, ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.

An example of a transaction is a transfer of funds from one bank account to another, even though it might consist of multiple individual operations (such as debiting one account and crediting another).

Atomicity

Atomicity refers to the ability of the DBMS to guarantee that either all of the tasks of a transaction are performed or none of them are.

For example, the transfer of funds from one account to another can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won’t be debited if the other is not credited.

Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic” if when one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.

Consistency

The consistency property ensures that the database remains in a consistent state before the start of the transaction and after the transaction is over (whether successful or not).

Consistency states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.

Isolation

Isolation refers to the requirement that other operations cannot access or see the data in an intermediate state during a transaction. This constraint is required to maintain the performance as well as the consistency between transactions in a DBMS. Thus, each transaction is unaware of other transactions executing concurrently in the system.

Durability

Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won’t need to abort the transaction.

Many databases implement durability by writing all transactions into a transaction log that can be played back to recreate the system state right before a failure. A transaction can only be deemed committed after it is safely in the log.

Durability does not imply a permanent state of the database. Another transaction may overwrite any changes made by the current transaction without hindering durability.

TEMP TABLE & GLOBAL TEMP TABLE & TABLE VARIABLE

Local Temporary Tables

These tables are created within a procedure and stored in the temp_db provided by

SQL server. They can be identified with a session specific identifier. It can be used when data is coming from another stored procedure. It also reduces the amount of locking required and also involves less logging. Still, there are few limitations such as character limit is 116 and transactions can create unnecessary locks in temp_db.

Syntax:

Create table #Student (Id int, Name varchar(50))

Global Temporary Tables:

These tables are same as local temporary table but the difference lies in the lifetime as it is available for all the sessions. This can be useful when the same set of data is required by one or more users. But the issue will come when the user, who should not be given access to this data, will have access to it as it is a global table and available for all users.

Syntax:

Create table ##Student (Id int, Name varchar(50))

See the difference of two #.

Table Variables:

Same structure as a normal table but only difference is the shortest life time among all the varieties. This table is created and stored in memory and its lifetime is decided by the stored procedure who have created it. Once stored procedure/DML statement exits, this table gets auto cleaned and memory gets free. Apart from that, log activity is truncated immediately. An important note, If we have a requirement to use a table structure in user defined function then we have only one option as Table variable and no other variety can be used.

Syntax:

Declare @Student Table (Id int, Name varchar(50))