Normal Vs Correlated Sub Query

Normal Vs Correlated Sub Query

In this article I am trying to explain related to normal and correlated subquery. It is a short article but gives you a solid concept related to it.

Normal or no correlated subquery

In normal or no correlated subquery, the inner subquery portion is independent; it not depends on outer query. The result of inner query helps to execute the outer query. The outer query is depends on the result or output of the inner query. The inner query is completely independent and can execute independently.

USE Products

SELECT *

FROM   Product AS P1

WHERE  P1.Unit = (SELECT MAX(P2.Unit)

                  FROM   Product AS P2

                  WHERE  P2.ProductName = 'Tooth Brush');

Correlated or repeatingsub

But in the correlated or repeating subquery, the subquery is depends on outer query for it's values. This means the subquery is executed repeatedly once for each row that might be selected by the outer query.

USE Products

SELECT *

FROM   Product AS P1

WHERE  P1.Unit = (SELECT MAX(P2.Unit)

                  FROM   Product AS P2

                  WHERE  P2.ProductName = P1.ProductName);

Summary related to Performance

Subqueries are a powerful feature of SQL. However, subqueries that contain an outer reference can be very inefficient. In many instances these queries can be rewritten to remove the outer reference which can improve performance. It is worthwhile to review the SQL Execution plan to help identify potential inefficiencies.

Hope you like that

Distributed queries

Distributed queries

This article contains a beautiful collection related to distributed query. It is a small contains, hope you like.
Distributed queries access data from multiple heterogeneous data sources. Microsoft SQL Server supports distributed queries by using OLE DB.

Distributed query can be accessed by the:

·         From multiple instances of SQL Server.

·         Heterogeneous data stored in various relational and no relational data sources accessed by using an OLE DB provider.

OLE DB providers expose data in tabular objects called rowsets. 
The linked server allows us to create OLE DB provider.

Reference row seats from the OLE DB data sources as tables in Transact-SQL statements. Pass commands to the OLE DB data sources and include the resulting row sets as tables in Transact-SQL statements.

OLE DB defines optional Interface:

·         ITransactionLocal 
Supports local transactions in the OLE DB data source.

·         ITransactionJoin 
Lets the provider join a distributed transaction that includes other resource managers.

Note that any provider that supports ITransactionJoin also supports ITransactionLocal.

Only read operations are allowed against providers that do not support ITransactionLocal.All update operations are allowed against any providers that support ITransactionLocal.
Only read operations are allowed against providers that do not support ITransactionJoin.Providers that do not support any transactions or only support ITransactionLocal cannot participate in update operations.
If SET XACT_ABORT is ON, all update operations are allowed against any providers that support ITransactionJoin. The controlling instance of the Database Engine automatically calls ITransactionJoin in each linked server participating in an update operation to enroll it in the distributed transaction. Microsoft Distributed Transaction Coordinator (MS DTC) then either commits them or rolls them back when the controlling server indicates that the transaction is either committed or rolled back.
If SET XACT_ABORT is OFF, the linked server must also support nested transactions before update operations are allowed. Nested transactions are supported if the provider supports calling ITransactionLocal::StartTransaction when there is already an existing transaction for the session. This allows SQL Server to roll back individual statements in distributed queries without rolling back the entire transaction.

Attaching Database Error

Attaching Database Error

There is a very common error wend going to attach database in SQL Server 2008R2. It generally gives the error

"CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)"

The problem is due to two different login do detach and attach.

So the files, when detached, were owned by the first login, but attach failed because the login that was used was not the owner of the mdf and ldf files.

We can detach the database by:

EXEC sp_detach_db mydb;

We can attach the database by:

CREATE DATABASE mydb

ON

(FILENAME='C:\MSSQL\mydb.mdf'),

(FILENAME='C:\MSSQL\mydb.ldf')

FOR ATTACH;

To solve this problem we have to give the full permission to both mdf and ldf file to attach login name by right clicking the file and selecting property and then security and then adding login name.

Hope you like that.

Ad-Hoc Query Paging

Ad-Hoc Query Paging

The SQL Server 2012 code named "Denali" has lot of new feature. In this article I am trying to explain a new feature of SQL Server 2012 called "Ad-Hoc Query Preparing"

The ORDER BY clause of SELECT statement has been enhanced by SQL Server 2012 by using the combination of OFFSET and FETCH.  OFFSET and FETCH along with ORDER BY gives us the option to paging through the result set.

The OFFSET clauses specifies the numbers of rows to skip before returning the query and the FEATCH specifies the numbers of rows to return after OFFSET clause has been proceed.

Let's takes an example to understand it properly. 

Suppose we have a table named TABLE-A.  The structure of the TABLE-A is mentioned bellow. The TABLE-A Contains more then 10,000 records.

STUDNAME	STUCLASS
JOYDEEP DAS	1
PALASH KANTI PAUL	1
SUDIP DAS	1
TUHIN KUMAR SHINAH	1
SANGRAM JIT BHATTERCHARYA	1
N th …

Now we want to display the result set like this.

First remove the 10 records and from 11^th to next 10 records to be displayed in the result set.

Frist I am providing a query that supports SQL 2005 to display the desired result st using CTE.

WITH stud(stuname, stuclass, rowNUM)

AS 

(SELECT Row_number() OVER (ORDER BY p.stuname) AS rowNUM,

        stuname, stucalss

 FROM  TABLE-A p

)SELECT *

 FROM   stud p

 WHERE  p.rowNUM BETWEEN 11 AND 20

It is quite complicated for the developer who uses the SQL Server 2012…. What you think?

So now I am providing you the new Example using OFSET and FEATCH NEXT feature of SQL 2012.

SELECT p.stuname, p.stuclass

FROM   TABLE-A p

ORDER  BY p.stuname

OFFSET 10 rows

FETCH NEXT 10 rows only

So it is so easy now with SQL Server 2012.

Hope you like it.

Memory problem to Execute Query

Memory problem to Execute Query

This article gives a simple demonstration to increase the Query memory size of SQL Server 2008.

SQL server 2008 takes 1024 KB of memory for executing the query. This memory is allocated generally by per session.

When we fall the lack of memory problem to execute the query we can increase the size for better performance.

We can set the size from 512 KB to 2 GB for SQL Server 2008.

If we increase the memory size the performance of processer-intensive operation such as sorting or hashing is improved.

To adjust the minimum query size we have to determine some points mentioned bellow.

1.    Total amount of free memory when SQL server is in running or idle mode.

2.    The average number of simultaneous query runs by per session.

3.     The average query size.

4.    The query responses time that you hope to achieve.

By this we can create a formula like this

Free memory / (AVG Query Size * AVG Number of Simultaneous Query)

Let's take an example:

Free Memory                                                  = 2200 MB

The Average query size                                 = 2 MB

The Average number of simultaneous query = 50

So

= 220 MB/(2 * 50)

= 22 MB

To allocate memory for Query

1.    Server property dialog box go to the Memory Page and set the value for the Minimum memory per Query (in KB)

2.    Click OK

3.    Restart the SQL Server services.

Hope you like it.

sp_tables

sp_tables

It returns a list of objects that can appears in the FORM cluse. It unable to returns the synonym objects. To view the details related to synonym just go to the sys.synonyms system catalog views.

The syntax of the sp_tables is mentioned bellow.

sp_tables [ [ @table_name= ] 'name' ]

     [ , [ @table_owner= ] 'owner' ]

     [ , [ @table_qualifier= ] 'qualifier' ]

     [ , [ @table_type= ] "type" ]

     [ , [@fUsePattern = ] 'fUsePattern'];

Now we are discussing about each of the parameters

1.    [ @table_name = ]
Table name is used to return the catalog information. Supported nvarchar(384). Default value is NULL. Wildcard character can be used.

2.    [ @table_owner = ]
Owner of the table is used to return the catalog information. Supported nvarchar(384). Default is NULL.

3.    [ @table_qualifier = ]
It is the name of the table qualifier. In others RDBMS supports qualifier.owner.name. In SQL server it is the database name.

4.    [ @table_type] = ]
This can be includes TABLE, SYSTEMTABLE or VIEWS. The Default is NULL. It is the list of values separated by a comma, which gives the information about.

5.     [ @fUserPattern = ]
Determines whether the underscore ( _ ), percent ( % ), and bracket ( [ or ] ) characters are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). fUsePattern is bit, with a default of 1.

Example:

USE my_DB;

GO

EXEC sp_tables

     @table_name = '%',

     @table_owner = 'student',

     @table_qualifier = 'my_DB';

GO

Return Result set:

Columns	Data Type	Descriptions
TABLE_QUALIFIER	sysname	In SQL Server it contains the Database name.
TABLE_WONER	sysname	Name of the Database owner who create the table.
TABLE_NAME	sysname	The name of the Table.
TABLE_TYPE	Varchar(32)	Table, System Table or Views
REMARKS	Varchar(254)	No Value returned

Hope you like it.

Move the User define Database

Move the User define Database

This article contains "To move the User define database in new location by attach and detach functions in SQL Server"   

Sometimes it is necessary to move the database and transaction log in different locations to increase the performance. As transaction log is growing quickly we have to make some strategy to get the solutions by moving it in a separate drive or separate disk.

One of my readers gives me a note related to it, and I want to share it with all of you.

"Shrinking of a database or log file causes file-fragmentation on the hard disk, this leads to poor performance. Therefore:

1. The transaction log should consist of two files: One which cannot expand itself and has the size you normally need between two full backups (plus some safety).
2. You create a second file which expands automatically.
If a transaction log grows too much only the second file will grow. Next day after full backup you just delete the second file and create a new one."

To move the existing database to new Location

Important

Before moving any database we must take the backup of the database.

Syntax

sp_detach_db [ @dbname= ] 'database_name'

            [ , [ @skipchecks= ] 'skipchecks' ]

            [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

sp_attach_db [ @dbname = ] 'dbname' 
                     , [ @filename1 = ] 'filename_n' [ ,...16 ]

How to Move

Step-1 [ Detach the Database ]

USE MASTER

GO

SP_DETACH_DB 'MY_DATABASE'

GO

Step-2 [ Copy the Data File and Log File from Current Location to New Location ]

Just copy the File in New Location. For Example Copy the file from E:\myData\SQLData to F:\MyData\NewLocation

Step-3 [Re-Attach the Database from New Location ]

USE MASTER

GO

SP_ATTACH_DB 'MY_DATABASE',

             'F:\MYDATA\NEWLOCATION\MY_DATABASE.MDF',

             'F:\MYDATA\NEWLOCATION\MY_DATABASE.LDF'

GO

Step-4 [Verify the Changes ]

USE MY_DATABASE

GO

SP_HELPFILE

GO