SQL Statement Processing in SQL server

A SELECT statement is nonprocedural; it does not state the exact steps that the database server should use to retrieve the requested data. This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. This is referred to as optimizing the SELECT statement. The component that does this is called the query optimizer. The input to the optimizer consists of the query, the database schema (table and index definitions), and the database statistics. The output of the optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. The contents of a query plan are described in more detail later in this topic.

The inputs and outputs of the query optimizer during optimization of a single SELECT statement are illustrated in the following diagram:

A SELECT statement defines only the following:

• The format of the result set. This is specified mostly in the select list. However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
• The tables that contain the source data. This is specified in the FROM clause.
• How the tables are logically related for the purposes of the SELECT statement. This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
• The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. These are specified in the WHERE and HAVING clauses.

                The process of selecting one execution plan from potentially many possible plans is referred to as optimization. The query optimizer is one of the most important components of a SQL database system. While some overhead is used by the query optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the query optimizer picks an efficient execution plan. 

              The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

The SQL Server query optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. The SQL Server optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

SQL Interview Question part 3

What is the difference between primary key and unique key?
1) By default Primary Key will generate Clustured Index whereas Unique Key will generate Non-Clustured Index.

2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t have duplicate values or any Null whereas SQL Server can have only one NULL.

3) A table can have only one PK but it can have any number of UNIQUE Keys.

4) Primary Key does not allow null values whereas unique constraint allow ‘single’ null value.

What is the difference between truncate table and delete table?
DELETE:  The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE:  TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

What is the temp table and table variable?
In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables.

Local Temp Tables

The local temp table is the most commonly used temp table.

CREATE TABLE #TempTable

(ID INT IDENTITY(1,1) NOT NULL,

Description VARCHAR(10) NULL)

Global Temp Tables

Global temporary tables work just like local temporary tables (stored in TempDB, less locking necessary).

CREATE TABLE ##TempTable

(ID INT IDENTITY(1,1) NOT NULL,

Description VARCHAR(10) NULL)

Table Variable

Table variables are cleared automatically when the procedure, function or query goes out of scope.

What is Cursor?
A cursor is a set of rows together with a pointer that identifies a current row. Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis. 
Cursors extend result processing by:

·         Allowing positioning at specific rows of the result set.

·         Retrieving one row or block of rows from the current position in the result set.

·         Supporting data modifications to the rows at the current position in the result set.

·         Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.

·         Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.

Type of Cursors

Forward-only

·         A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor.

Static

·         The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. Static cursors detect few or no changes, but consume relatively few resources while scrolling.

·         The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement

Keyset

·         The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

Dynamic

·         Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor.

SQL Interview question part 2

What is the difference between in and exist operator in sqlserver?
IN:  Returns true if a specified value matches any value in a sub-query or a list.

Exists:  Returns true if a sub-query contains any rows.

What is a function?  What are the different types of user defined functions?
Scalar function:  A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.

Table-value function: User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)

RETURNS TABLE

AS

RETURN

(

    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'

    FROM Production.Product AS P

      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID

      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID

    WHERE SH.CustomerID = @storeid

    GROUP BY P.ProductID, P.Name

);

GO

SELECT * FROM Sales.ufn_SalesByStore (602);

Built-in function:  Built-in functions are provided by SQL Server to help you perform a variety of operations. They cannot be modified. You can use built-in functions in Transact-SQL statements to:

·         Access information from SQL Server system tables without accessing the system tables directly.

·         Perform common tasks such as SUM, GETDATE, or IDENTITY.

What is a stored procedure and types of parameters in it?
Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result.

IN Parameter:

CREATE PROCEDURE uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM AdventureWorks.Person.Address

WHERE City LIKE @City + '%'

GO

OUTPUT Parameter:

CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT

AS

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

How can you execute dynamic sql in sqlserver?
A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements.

CREATE PROCEDURE usp_GetSalesHistory

 (

         @WhereClause NVARCHAR(2000) = NULL

 )

 AS

 BEGIN

         DECLARE @SelectStatement NVARCHAR(2000)

         DECLARE @FullStatement NVARCHAR(4000)    

         SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory '

         SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')    

         PRINT @FullStatement

         EXECUTE sp_executesql @FullStatement    

                /*

  --can also execute the same statement using EXECUTE()

         EXECUTE (@FullStatement)     

         */

 END

What is un-updatable views?
Aggregate functions or Group By clause in the View to be non-updatable.  SQL Server would not be able to determine which of the summarized rows should be updated.

What is trigger?  How can you invoke trigger on demand?
A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made using INSERT,UPDATE,OR DELETE statements.  Triggers are used to enforce data integrity and business rules such as automatically updating summary data.  A table can have up to 12 triggers defined on it.

Triggers can't be invoked on demand. They get triggered when the associated INSERT, DELETE or UPDATE is performed.

What is difference between Views and stored procedure?

Views	Stored Procedures
No option to return customized result set	Can be customized to handle many requirements in single SP
Views cannot be parameterized.	Can be parameterized. can return multiple result sets. Can return different result based on parameters
no control on the way end user manipulate Views	have control over final data
Bad usage* of view may kill production server performance(*non sargable conditions in view)	more control over the stored procedure usage
Internal data processing not possible	Data can be processed using programming constructs
Views can be used in SELECT commands and can be joined with other views or tables	Stored procedures usually won’t be part of SELECT statement.

SQL Interview Question - part 1

What is Schema? How can you provide security to schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

You can assign an user login permissions to a single schema so that the user can only access the objects they are authorized to access.  Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

What is the difference between rule and constraint?

Rules are used for backward compatibility.  One the most exclusive difference is that we a bind rules to a data types whereas constraints are bound only to columns.  So we can create our own data type with the help of Rules and get the input according to that.

Explain different types of constraints in SQL SERVER?
1) Entity Integrity:  Ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint.

2) Referential Integrity:  Ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint.

3) Domain Integrity:  Ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints.

Unique: CREATE TABLE Products(

    ProductID int PRIMARY KEY,

    ProductName nvarchar (40) Constraint IX_ProductName UNIQUE)

Check:  CREATE TABLE Products_2(

    ProductID int PRIMARY KEY,

    UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100)   )

Default:  CREATE TABLE Product_3(

   ProductID int PRIMARY KEY,

    UnitPrice NULL DEFAULT (0) )

What is an @@IDENTITY?
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement of the Identity column.

Example: CREATE TABLE new_employees

        (

         id_num int IDENTITY(1,1),

         fname varchar (20),

         minit char(1),

         lname varchar(30)

        )

INSERT new_employees (fname, minit, lname)

VALUES ('Karin', 'F', 'Josephs')

SELECT @@IDENTITY AS 'Identity'

Output:  1

What is difference between user and login?
A "Login" grants the principal entry into the SERVER instance.

A "User" grants a login entry into a single DATABASE.

One "Login" can be associated with many users (one per database).

What is transaction?  What are the acid properties?

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.

        --Please feel free to comment.

What is Fill Factor ?

                                                       Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100. 

                                                    If the page is completely filled and new data is inserted in the table which belongs to completely filled page, the “page split” event happens to accommodate new data. When new data arrives, SQL Server has to accommodate the new data, and if it belongs to the page which is completely filled, SQL Server splits the page in two pages dividing the data in half. This means a completely filled page is now two half-filled pages. Once this page split process is over, the new data is inserted at its logical place. This page split process is expensive in terms of the resources. As there is a lock on the page for a brief period as well, more storage space is used to accommodate small amounts of data. The argument usually is that it does not matter as storage is cheaper now a day. Let us talk about Fill Factor in terms of IO in the next paragraph.

                                  

                It is absolutely true that storage is getting cheaper every day.      .Now let us assume that there is Table 1 which contains the data worth of 1000 pages. All these pages are 100% filled. If we run a query to retrieve all the data from the SQL Server, it should retrieve all the 1000 pages. If pages are only 50% filled to accommodate Table 1, we will need 2000 pages, which means SQL Server has to read twice the amount of the data from the disk, leading to higher usage of memory, CPU and IO bandwidth.

                                Reading previous paragraph gives us an impression that having Fill Factor 100 is the best option as there won’t be any empty space in the page and IO will be always optimal. Again, this is true if we never do any insert, update or delete in the table. If we insert new data, ‘Page Split’ will happen and there will be few pages which will have 50% Fill Factor now. An OLTP system which is continuously modified ‑ this has always been a challenge with regard to having the right Fill Factor. Additionally, note that Fill Factor only applies to a scenario when the index was originally created or index was rebuilt; it does not apply subsequently when page split happens. As discussed earlier, page split increases IO as well storage space.

                         So,higher Fill Factor and high transaction server implies higher page split. However, Fill Factor can help us to reduce the number of the page splits as the new data will be accommodated in the page right away without much difficulty and further page splits. We can measure the page split by the watching performance monitor counter “SQLServer:AccessMethods:Page Splits/Sec”.

 Additionally, you can measure the same using the following sys query.

SELECT cntr_value
FROM MASTER.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'

Fill factor is usually measured at the server level as well as table level. Below we have the scripts for the same.

Here is the script to measure the Fill Factor at the server level:

SELECT *
FROM sys.configurations
WHERE name ='fill factor (%)'

And, here is the script to measure the Fill Factor at the table/index level:

USE YourDatabaseName;
SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor
FROM sys.indexes

How Does SQL Server Store Data?

                        This is one of the important topic for database developer.We all know data is stored in table in sql server.All of us are interested to know  little more about it.How Sql Server stores data into table.I will try to explain in simple word.
                      Microsoft SQL Server databases are stored on disk in two files: a data file and a log file.
Data file named as .MDF (Master data file) and .LDF (Log data file).Next thing ,What kind of data is stored in .MDF and .LDF file.

Whats .MDF file stores :
                                     The SQL MDF file contains tables, stored procedures and user accounts. The MDF file is "attached" to the database. The MDF file is automatically created when the administrator creates a new database. The file continues to grow each time a user creates a new record in the tables. The user tables are the main storage objects in the MDF file. These objects can hold millions of records for the company.

                                      MDF files can grow to several megabytes. Databases with millions of rows can even grow into gigabytes of information. For this reason, database administrators who maintain large, enterprise servers should ensure that the hard drive has enough storage space to hold the growing MDF file. This is especially important when the hard drive houses several databases for the company. With each MDF file growing to several gigabytes, hard drive space is quickly consumed.The database administrator can use the MDF file as a backup. The MDF file is copied to an external media device such as an external hard drive, USB flash drive, CD or DVD. 

Whats .LDF file stores :                                    LDF is a file extension for a log file used with Microsoft SQL Server. LDF files contain logging information for all transactions completed by the server. LDF files are used to time stamp any transactions to the SQL Server database, allowing the SQL database to be easily recoverable in the case of data loss.

e.g  . 

           I am creating table MyMemory .Now I am going to explain how data will store internally

 Query : 

              Create table MyMemory (ID int identity(1,1),Name varchar(100))

 Insert :

        Insert into MyMemory (Name) values  ('Shrikant')

        Insert into MyMemory (Name) values  ('Sai')

        Insert into MyMemory (Name) values  ('Santosh')

        Insert into MyMemory (Name) values  ('deelip')

Now ,This data will store in table .It will look like 

      

ID     Name

1 Shrikant

2 Sai

3 Santosh

4 deelip

         We are interested to know how it will store internally.We have DBCC command which will help us to know how data is stored internally.

Data Files Are Broken Up Into 8KB Pages 

           These pages are the smallest unit of storage both in memory and on disk.  When we write the very first row into a table, SQL Server allocates an 8KB page to store that row – and maybe a few more rows, depending on the size of our data.  In our Friends example, each of our rows is small, so we can cram a bunch of ‘em onto a page.  If we had bigger rows, they might take up multiple pages even just to store one row.  For example, if you added a VARCHAR(MAX) field and stuffed it with data, it would span multiple pages.

Each page is dedicated to just one table.  If we add several different small tables, they’ll each be stored on their own pages, even if they’re really small tables.

DBCC IND('Shri_Test_17012014', 'MyMemory', -1);

 -->Database name - Shri_Test_17012014

--> Table Name - MyMemory

            Result of this command will tell us how many pages it has occupied for storing data. Above table has occupied 2 pages means 16KB of data.

 

 PagePID        ObjectID       Table Name [object_Name(Object_id)]

 611164   1280827725 MyMemory

 611163   1280827725 MyMemory

How the Data File and Log File are Accessed :

                      Data files, on the other hand, are a jumbled mess of stuff.  You’ve got tables and pages all over the place, and your users are making unpredictable changes all over the place.  SQL Server’s access for data files tends to be random, and it’s a combination of both reads and writes.  The more memory your server has, the less data file reads happen – SQL Server will cache the data pages in memory and just work off that cache rather than reading over and over.  This is why we often suggest stuffing your SQL Server with as much memory as you can afford; it’s cheaper than buying good storage.

                    Log files are written to sequentially, start to finish.  SQL Server doesn’t jump around – it just makes a little to-do list and keeps right on going.  Eventually when it reaches the end of the log file, it’ll either circle back around to the beginning and start again, or it’ll add additional space at the end and keep on writing.  Either way, though, we’re talking about sequential writes.  It’s not that we never read the log file – we do, like when we perform transaction log backups.  However, these are the exception rather than the norm.

Difference Between Index Scan and Index Seek.

Index Scan :
                   Index Scan scans each and every record in the index. `Table Scan` is where the table is
processed row by row from beginning to end. If the index is a clustered index then an
index scan is really a table scan. Since a scan touches every row in the table whether or
not it qualifies, the cost is proportional to the total number of rows in the table.
Hence, a scan is an efficient strategy if the table is small.

Index Seek :
                   Since a seek only touches rows that qualify and pages that contain these qualifying
 rows, the cost is proportional to the number of qualifying rows and pages rather than to
 the total number of rows in the table.

               Before we go over the concept of scan and seek we need to understand what SQL Server does before applying any kind of index on query. When any query is ran SQL Server has to determine that if any particular index can be applied on that particular query or not.SQL Server uses search predicates to make decision right before applying indexes to any given query.

Predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.

Suppose,
TableX has five columns : Col1,Col2,Col3,Col4,Col5
Index1 on TableX contains two columns : Col2,Col3
Query1 on TableX retrieves two columns : Col1,Col3

          Now when Query1 is ran on TableX  it will use search predicates Col1,Col3 to figure out if it will use Index1 or not. As Col1,Col3 of Query1 are not same as Col2,Col3 or Index1 there are good chances that Query1 will not use Index1. This scenario will end up in table scan. If Query1 would have used Index1 it would have resulted in table seek.

What is BigData?

                                         Big data is a buzzword, or catch-phrase, used to describe a massive volume of both structured and unstructured data that is so large that it's difficult to process using traditional database and software techniques.

While the term may seem to reference the volume of data, that isn't always the case. The term big data -- especially when used by vendors -- may refer to the technology (which includes tools and processes) that an organization requires to handle the large amounts of data and storage facilities.

The term big data is believed to have originated with Web search companies who had to query very large distributed aggregations of loosely-structured data.

An Example of Big Data

An example of big data might be petabytes (1,024 terabytes) or exabytes(1,024 petabytes) of data consisting of billions to trillions of records of millions of people -- all from different sources (e.g. Web, sales, customer contact center, social media, mobile data and so on). The data is typically loosely structured data that is often incomplete and inaccessible.

When dealing with larger datasets, organizations face difficulties in being able to create, manipulate, and manage big data. Big data is particularly a problem in business analytics because standard tools and procedures are not designed to search and analyze massive datasets.

What is Hadoop?

                                                                Hadoop is a free, Java-based programming framework that supports the processing of large data sets in a distributed computing environment. It is part of the Apache project sponsored by the Apache Software Foundation.

Hadoop makes it possible to run applications on systems with thousands of nodes involving thousands of terabytes. Its distributed file system facilitates rapid data transfer rates among nodes and allows the system to continue operating uninterrupted in case of a node failure. This approach lowers the risk of catastrophic system failure, even if a significant number of nodes become inoperative.

Hadoop was inspired by Google's MapReduce, a software framework in which an application is broken down into numerous small parts. Any of these parts (also called fragments or blocks) can be run on any node in the cluster. Doug Cutting, Hadoop's creator, named the framework after his child's stuffed toy elephant. The current Apache Hadoop ecosystem consists of the Hadoop kernel, MapReduce, the Hadoop distributed file system (HDFS) and a number of related projects such as Apache Hive, HBase and Zookeeper.

The Hadoop framework is used by major players including Google, Yahoo and IBM, largely for applications involving search engines and advertising. The preferred operating systems areWindows and Linux but Hadoop can also work with BSD and OS X.

Getting number from alphanumeric string in SQL Server

 Suppose ,
            We have string which is containing numbers,character,special character.Requirement is we want only numbers .We don't want characters ,special character etc.I created function which will only return  number.

Create function fnOnlyNumbers(@Value varchar(255))
 returns varchar(255)
 begin
 if PATINDEX('%[0-9]%',@Value)>0
 begin
 while  isnumeric(@Value)=0
 begin
 set @Value=ltrim(rtrim(REPLACE(REPLACE (@Value, SUBSTRING (@Value ,PATINDEX ( '%[!@#$a-z`?><.,/A-Z() *&]%' , @Value ),1),'') ,' ','')))
 end
 end
 return  case when  isnumeric(@Value)=1 then   @Value else Null end
 end


How to run :
select dbo.fnOnlyNumbers('sss1$2>>34 ')
Output : 1234

Note : If we are not passing any value then we will get Null value.