Hi All,

We always listen the words like sargable, non- sargable in performance tuning. Below is the small understanding of those words

Queries which avoid WHERE clauses are non-sargable. If a WHERE clause is sargable, this means that it can take advantage of a useful index (assuming one is available) to speed completion of the query.

If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query’s performance to suffer.

Non-sargable search arguments in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’” generally prevents (but not always) the query optimizer from using a useful index to perform a search.

In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.

But not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use a useful index (if one exists) to help access the data quickly

I will send the detailed explanation on this with example in next article.

Regards,

Chaitanya

http://www.Sqlblogging.com

Hi Folks,

We have issues in the production at our end where the master table is update with leading spaces and it is causing the incorrect results as it master table and configuration is present in those tables.

I quickly checked the leading spaces in the table using the below query

We have created sample tables for this demo

use tempdb

go

create table #tempo

(

id int,

name varchar(100)

)

insert #tempo

select 1,’chaitu’

union all

select 2,’pradee’

union all

select ‘3’,’ krishna’

select * from #tempo

Below is the query for getting the leading spaces in the name column

select * from #tempo where LEFT(name,1)=”

For trailing spaces

use tempdb

go

create table #tempo

(

id int,

name varchar(100)

)

insert #tempo

select 1,’chaitu’

union all

select 2,’pradee’

union all

select ‘3’,’krishna ‘ à trailing space is inserted

select * from #tempo

Below is the query for getting the trailing spaces in the name column

select * from #tempo where right(name,1)=”

Please let us know if you have other options

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging

Twitter: @chaitanya512

Sql Server Auto formatting tool

Today I saw the post of Pinal Dave in http://blog.sqlauthority.com and got to know one excellent tool for formatting the sql server and intellisense

You can download this tool from the below website

http://www.devart.com/dbforge/sql/sqlcomplete/download.html

install this tool and it will integrate with your SSMS and you can see the difference of using the SSMS

It will give all the details with excellent intellisense. It will automatically format the code

You can write the code very fast using this.

You can see the complete tutorial of this in the below link

http://www.devart.com/dbforge/sql/sqlcomplete/sql-complete-demo.html

Thanks Pinal for this information

At last this is the Must Tool for sql server Resources.

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging

Twitter: @chaitanya512

Below is the article I have written on BizTalk flat file processing

http://www.eggheadcafe.com/tutorials/biztalk/fa6cd489-8219-473a-8f30-7b9af6ef94d0/biztalk-flat-text-file-processing.aspx

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging

Twitter: @chaitanya512

Entire Data file is divided into 8kb physical chunks called pages

Why we are dividing this files into pages à for easy of organizing the data.

Group of 8 consecutive pages is called extent

What is in the page

Page is consists page header

And it consists of location of page, address, and owner, pointer to previous page or next page

Extent is logical grouping of the pages.

Page fragmentation:

If the pages of the table are located at different places, then it will cause the page level fragmentations

One page should be allocated to particular object only. A single page cannot store the information of 2 tables. If you have table 1(t1) data in 3 pages and last 3 page is not full, then you have created the table 2(t2) then it will use new page, not the 3rd page which is free and of table 1(t1). Single object can resides on different pages.

This page and extent management of sql server is different from other rdbms products but expect from Sybase. As the roots of sql server is from Sybase only.

We have 2 types of fragmentation

1. Inter page fragmentation : when a data is deleted from the page

2. Across pages fragmentation: when the pages of table is spitted across different locations

Important point: A Row cannot be span over multiple pages

How we can eliminate this limitation

Sql server will store only 1023 columns and another 1 column is used for replication

If the row is >7.1 kb, then the solution is below one

Other than integer, character data we have image, textual, xml which will not use the page concept

Due to this fragmentation will occur, but the space that will left blank will be used for future purpose, if the row 1 is updated with some more data, then it will use that space.

This is high level explanation of how the data is organized.

We will see the internals of pages, records in next article.

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging

Twitter: @chaitanya512

Hi All,

Below is the script for the All Table count for instance.

We have excluded the system databases

DECLARE @Sql NVARCHAR(2000)
DECLARE @Dbname NVARCHAR(200)
DECLARE @Name VARCHAR(150)

Pluarsight is one famous online training site which is giving the offer of learning your favorite technology subject.

But this offer is valid for only one. And you should have the twitter account for this.

Link for this is http://bit.ly/V8tEjq

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging

Twitter: @chaitanya512

Below concepts, we will discuss in this article.

1. What is SQL Server Database?
2. What does SQL Server Database contain?
3. Database Architecture
4. Files and File groups
5. Understanding Log Files
6. Database Design Considerations
7. System Databases
8. Resource Database
9. Types of SQL Server Databases

What is SQL Server Database?

1. A Database in SQL Server is made up of a collection of tables. These tables contain tables and other objects that are defined to support activities performed with the data.
2. At physical level, the Database is can be seen as a collection of simple files.
3. Each database can store either interrelated or unrelated data.

What does Database contain?

1. A database consists of :

1. Data

2. Different Objects useful for managing the Data

2. Data is physically stored in specific objects called : TABLES
3. There are tens of Database Objects that are needed for easy retrieval, working with and securing the data in a database.

Database Architecture

Data is stored in tables logically and physically it was stored in Files

We should not have the files separately in 2 different machines for a single database

Database is collection of Minimum 2 files

1. Data file( represented as .MDF)- all the data and meta data is stored here

2. Log file(represented as .LDF): all the Audit Records are stored here

Audit: which operation is performed, which user is doing operations etc.

Whenever a new database is created, we have the MDF created initially which have the meta data and LDF created for audit information

Depending on size of the Database, we can have the multiple data files.

These data files are organized as

1. Primary data file : contains the User defined data and Meta data

2. Secondary data file : contains the User defined data and Meta data

These data files can be having any extension.

We can have the data files with any extension, but it is Microsoft convention not a rule

Another convention is MDF files have Data +Meta Data

NDF files have only Data. It is not a rule, but we can have the Data + Meta Data in the NDF

Database can have 32,767 files

NDF file is mandatory

Files and File Groups

Data files can be grouped together in FILEGROUPS for allocation and administration purposes.

Logical related group of files are file groups

Ex:

employee details in employee group

Manager details in employee group

We can have min 1 file group and max 32767 file groups for database and for 1 file group is having 32767 files

§ Primary data file is in primary file group and this group was created when creating the data base

§ Every data file should belong to file group

§ Log files should not belong to file group

Understanding Log Files:

Assume your database as a Big Apartment, for apartment we have the watchmen

Depending up on the apartment size, we have watch men.

A minimum 1 watch man is there and they will audit all the information of persons who is coming and leaving.

Whenever any theft happened, we can go to watchmen he will check the log records and identify the UN authorized person in that time lines. He will not tell anything if the theft happened before 2 years.

So think of Log file as watch men and apartment as Database and each floor as File group and each flat as file.

Your transaction log file should not belong to any file group. Your watchmen should not belong to any file. (Just for understanding)

We should not split the data files and log file separately.

Think of your apartment, your watchmen is working outside (Not possible right)

System Databases

Whenever sql server is installed, we have default databases created.

1. master : Records all the system-level information for an instance of SQL Server.

i. Ex: instance name, edition, collation etc. all the Meta data information level at instance level. It also stores the Meta data for the newly created databases

2. model: Used as the template for all databases created on the instance of SQL Server.

1. Template for the new Database creation.

3. msdb : Used by SQL Server Agent for scheduling alerts and jobs.

a. Scheduling and administrative information

4. tempdb : Workspace for holding temporary objects or intermediate result sets.

1. For day to day operation we need this tempDB

Where these files will be seen.

Go to databases, select properties and select files, and we can see the files below

Take the path of file and go to there, we can have the files

Master database is entry of Instance

We have another system database is there, but it is hidden

Resource Database:

In addition to the FOUR system databases discussed previously, a separate system database called “Resource” database is also installed. But this database cannot be seen in SSMS for security reasons

In sql server 2000, we have the system objects in master database and any one can go and update.

In sql server 2005, we have system objects in master database, but the source is coming from resource database

System objects are physically persisted in the Resource database

We don’t have direct access resource expect master database.

If we open the resource database .mdf file using any tool, we don’t get anything. This was much protected with encrypted format

Precautions for Database design:

1. Only one database is allowed per database file, but a single database can span multiple files.

2. Transaction logs must reside on their own file. There could be more than one transaction file per database.

3. Transaction log files are filled up one at a time.

4. When you create a database and do not specify the size of the log file, the log file will be 25% of the database file but minimum is 1MB by default