27.5.10

Shortcuts in SQL Server

Shortcuts in SQL Server:
Am seeing a frequent questions in the forums about creating shortcuts in SQL Server for frequent queries. How can we achieve it?

Considering am using sp_who query for more than 100 times in a day. In that case, I am fed up with writing this query and execute it. Instead we can create short cuts in the SSMS. So that, we can use it easily.

To achieve it. Goto Tools -> Options ->Environment -> Key board. You can type your query and press Ok button. That particular query will be set for the corresponding short cuts.

Simple to use... Enjoy....


Cheers,
Venkatesan Prabu .J

SQL server Interview queries

Here are some SQL queries asked frequently in interviews,


Write an SQL query to display the records whose date is having time stamp of "14" hrs.

CREATE TABLE VENKAT_TABLE(ID INT, MOBILE_NUMBER VARCHAR(15),AMOUNT INT, TIME_STAMP DATETIME)

INSERT INTO VENKAT_TABLE VALUES(1,'9840578690',10000,'1/1/2010 15:20:20')
INSERT INTO VENKAT_TABLE VALUES(1,'9840578690',5000,'1/1/2010 14:00:00')



-- The below command will fetch all the entries happened around 14 hours.
SELECT * FROM VENKAT_TABLE WHERE DATEPART(HH,TIME_STAMP) =14


-- The below command will fetch all the entries happened exactly at 14 hours.
SELECT * FROM VENKAT_TABLE WHERE DATEPART(HH,TIME_STAMP) =14 and DATEPART(mi,TIME_STAMP) =00
and DATEPART(s,TIME_STAMP) =00



Write an SQL query to display the unique number series (Starting 4 digits is the series) present in the table.

-- DISTINCT WILL PROVIDE YOU THE UNIQUE DATA
SELECT DISTINCT ID FROM VENKAT_TABLE


Write an SQL query to postfix '0' to the mobileno whose balance is more than 6000.

UPDATE VENKAT_TABLE SET MOBILE_NUMBER = MOBILE_NUMBER +'0' WHERE AMOUNT>6000

Cheers,
Venkatesan Prabu .J

sp_who and sp_who2 in SQL Server

sp_who2 is an Undocumented/Upgraded extension of sp_who.

SP_WHO :
This will provide the following options,

1. System process ID.
2. Status of the process.
3. Login name of the user.
4. Name of the user. I
5. f the process is blocked, the SPID of the blocking process.
6. Database the process is using.
7. Command currently being executed.

SP_WHO2:

Along with the above options, sp_who2 will provide the following additional linformations

1. Total CPU time of each process.
2. Total amount of disk reads for each process.
3. Last time a client called a procedure or executed a query.
4. Application connected.

Cheers,
Venkatesan Prabu .J

Char / Varchar / Nvarchar datatype in SQL Server

Char data type:

It is a fixed length data type and allows character datatype.

For instance, if you have char(5) then it occupies fixed length of 5 bytes even though you have a string which have less than 5 character's length. The rest of the character are treated as blank spaces.


Varchar datatype:

It is a variable length data type and it occupies length for each row dynamically. So it doesn't have fixed length. In most of the cases, varchar type is preferred due to its proper memory usage. It occupies 1 bytes for each character. varchar (max) will have the max of 8000 in lower versions and 2 GB is upper versions.


Nvarchar data type :

To support different language beyond English language, Nvarchar data type is used.
Basically, it supports unicode characters. It has the base property of varchar with memory of 2 bytes for each character. nvarchar (max) will have the max of 4000 in lower versions and 2 GB is upper versions.

Cheers,
Venkatesan Prabu .J

Enable xp_cmdshell in SQL Server


Cofiguring xp_cmd shell option can be done using two methods. Explicit xp_cmdshell option enabling is introduced in SQL Server 2005 and above. In SQL Server 2000, we should provide more access to the specific sql login user. In 2005 and above,

1. Using Surface Area configuration. Enable the xp_cmdshell option.
2. Use the below queries to achieve it.


-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1
GO

-- To update the currently configured value for advanced options.

RECONFIGURE
GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 0 -- 0 for disable, 1 for enable
GO

-- To update the currently configured value for this feature.

RECONFIGURE
GO

Cheers,
Venkatesan Prabu .J

24.5.10

Hierarchy data retrieval in SQL Server

This is one of my best short article in this blog. A very interesting one and I like the most.

Usually, we will face this scenario like -> Fetch an employee and get the entire hierarchy of an employee like. A is reporting to B, B is reporting to C, C is reporting to D.

If I need to find who is reporting to C, we need to fetch B followed by A and its depth. A fantastic Optimized solution to retrieve this information is,

drop table Venkat_SampleTable
create table Venkat_SampleTable(id int, nam varchar(10),bossid int)
insert into Venkat_SampleTable values(1,'venkat',0)
insert into Venkat_SampleTable values(2,'Arun',1)
insert into Venkat_SampleTable values(3,'Suba',1)
insert into Venkat_SampleTable values(4,'Karthi',2)
insert into Venkat_SampleTable values(5,'Krishiv',3)
insert into Venkat_SampleTable values(6,'Santhi',3)
select * from Venkat_SampleTable

DECLARE @boss_id int
SET @boss_id = 2;

WITH Venkat_CTE_Table (id, nam, BossID, Depth)
AS
(
SELECT id, nam, BossID, 0 AS Depth
FROM Venkat_SampleTable WHERE id = @boss_id
UNION ALL
SELECT Venkat_SampleTable.id, Venkat_SampleTable.nam, Venkat_SampleTable.BossID, Venkat_CTE_Table.Depth + 1 AS Depth FROM Venkat_SampleTable
JOIN Venkat_CTE_Table ON Venkat_SampleTable.BossID = Venkat_CTE_Table.id
)

SELECT * FROM Venkat_CTE_Table


Cheers,

Venkatesan Prabu .J

Convert decimal values in SQL Server

Convert a integer value with multiple decimals to fixed number of decimals(2 decimals):

Consider a scenario, am having a decimal data and I need to restrict the decimal values. In this case, we need to use convert operator.


create table aa(id decimal(10,5))

insert into aa values(1.90955)

select convert(decimal(10,2),id) from aa

Cheers,

Venkatesan Prabu .J

Stored procedure execution on SQL Server startup

Stored procedure execution on SQL Server startup:


I have studied a very interesting topic in SQL Server and wish to blog the same in my site.
Scenario:
On each SQL Server database startup, I need to execute a procedure in my database. It's a very basicscenario in all places.


Solution:


For this, SQL Server is providing an option of using a system stored procedure sp_procoption


create procedure Venkatesan_Insert_Procedure

as

begin

insert into venkat1(id,val) values (5,'F')

end

EXEC sp_procoption @ProcName = 'Venkatesan_Insert_Procedure',@OptionName = 'startup',@OptionValue = 'true'

Now, your stored procedure is set as a initial startup which will execute on DB start.

Cheers,

Venkatesan Prabu .J

23.5.10

Truncate Log file in SQL Server

I got an issue with my database. Entire database is hanging and I didn't find an option to think the reason for this issue. We are unable to do any transaction. On checking the log file, we found the issue lies with log files.

1. Log file is around 20MB size with restricted growth option set for the database.

Solution:

1. I have changed the option to have un restricted growth.
2. Backup the database before doing truncate option.
3. Truncated the log file.

USE VenkatDB;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE VenkatDB SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (VenkatDB _Log, 1);
GO

-- Reset the database recovery model.

ALTER DATABASE VenkatDB SET RECOVERY FULL;

GO

Cheers,
Venkatesan Prabu .J

To check the logfile used

DBCC SQL Perf :

DBCC SQL Perf function is used for the following purposes,

1. To list down the percentage of log file used.
2. Clear the OS waits information (sys.dm_os_wait_stats)
3. Clear the Latch waits information ( sys.dm_os_latch_stats )

Considering the database VenkatDB is having 1 MB for the log file,


DBCC SQLPERF(LOGSPACE)

-- This command is used to list down the spaces available for the database + How much % of log file is used + status of the log file(either its available and working or not) - 0 indicates, there is no potenition issue with the database.







Clearing OS wait/Latch statistics:

Below is the command to clear all the statistics collected for OS wait and Latch wait.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC SQLPERF("sys.dm_os_latch_stats ",CLEAR);


Cheers,
Venkatesan Prabu .J

18.5.10

Compatibility Level in SQL Server 2008

Compatibility Level is a very nice feature which decides the nature of the database. Considering am having a SQL Server 2000 database. I want this database to be work as a SQL Server 2005 in 2005 environment. Is it possible with out doing actual big migration work?

Yes, it's possible with this option -> Compatibility Level

Compatibility Levels in SQL Serve 2008:
SQL will support 3 versions of database at their each releases. SQL Server 2005 supports (SQL Server 7.0/2000 and 2005). Now, SQL Server 2008 is supporting 3 versions (2000/2005/2008).
****************************************
Versions Compatibility Level
****************************************
SQL Server 2000 80
SQL Server 2005 90
SQL Server 2008 100
****************************************

Right Click on the database -> Properties -> Options -> Compatibility Level


Cheers,
Venkatesan Prabu .J

SQL Server 2008 SSMS enhancements - Reporting in SQL Server

SQL Server 2008 SSMS enhancements:



One of the fantastic enhancement with SQL Server 2008 is their enhancement in Reporting services. Everything is given in your hand in a very friendly manner.

1. You can get the statistics of each individual databases. Even granular information is given to you in a very detailed reports.

2. Considering, I want some information about a database like -> Disk usage of this database, blocking transactions in the database, Users in the database.




3. Right click on the database -> Reports -> Standard Reports -> Required reports.



Below report shows me the detailed memory structure for my database.


Now, I have selected "All blocking transaction". This report will provide a detailed analysis on the transactions which is blocking.

User statistics provides the users available in the database. I think, sp_who stored procedure is used to fetch this report :-)


Cheers,
Venkatesan Prabu .J

Unable to start SQL Server instance

Sometimes, we used to face the below error while trying to restart the database services or during login with a specific user credentials.

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

It's due to a setting in SQL Server. User instance in SQL Server is not enabled. Enter into your database with administrative access possibly windows account.

Step 1: Enabling User Instances on your SQL Server installation

First, you need to enable User Instances for SQL Server installation.

Query Window in SQL Server Management Studio and execute the below query

exec sp_configure 'user instances enabled', 1

Go

Reconfigure

Restart the SQL Server.

Step 2:

We need to delete all the old User Instances.

Go to your C drive search for the below path,


C:\Documents and Settings\YOUR_USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Step3: Restart your machine. If the above steps didn't resolve your issue.

Cheers,

Venkatesan Prabu .J

15.5.10

Copy Billions of data from One database table to another database table

I faced a very different scenario, Copy huge amount of data(Billion records) from one database table to another database table. Do I need to consider anything or Just put select * into or Insert Into query for moving the data.?

It's a very good challenging issue. We can think about some possible options for this issue.

1. Change the database property to "Simple recovery model" Else, you ldf file willbe bombarded due to huge transaction.

2. Do not do the transaction as a whole. Instead, do it in batches. This will provide room to SQL Server to process it.

3. Else you can put it in while loop. So that, your records will be processed in batches.

4. If the database is identical except that particular table. In that case, restore the database.

5. We can think about database mirroring to mirror the database.

Cheers,
Venkatesan Prabu .J

SQL Server releases in 2008

While checking one of the forums, I got the below information on the recent changes done in the SQL Server releases.

SQL Server will be released along with the key word PCU and CU.

PCU - It denotes Server packs. Now we are having PCU2 for SQL Server 2008

CU - It denotes hot fixes. To my knowledge, there are 7 CU's released for SQL Server 2008.

Cheers,
Venkatesan Prabu .J

11.5.10

DBA issues in SQL Server - File size growing more.

Scenario based solution:

I have a database which has a huge table compared to its overall size. The database is 60GB and this table is 40GB. It contains random generated codes, about 500M of them and this column is the primary key and the clustered index. New code generation batches are inserting 1-5M codes into the table which causes massive fragmentation and eventually, we end up with DBCC CHECKDB running 6 hours on the database (for reference: it completes in 45 minutes for a 120GB database on the same server). Defragmenting this table is again a huge task, because it eats up all the spare resources we have (or maybe more) and needs even more space on the data disk.

The best solution (or workaround) we found so far is that we’re going to rebuild the clustered index with 80% fill factor. Given that insertions are random and there’s no range scan (apart from maintenance tasks like CHECKDB), I think we’ll waste some space and that’s all. (And this table will increase its proportion, in two years, we’re expecting a 120GB database with a 100GB code table.)

Solution:

1. In this scenario, they are tring to create index or building the index on a column which is generating random ID's. Instead, they can create an identity column with clustered index on that column + Non clustered index on the random number generated column.
2. Placing the fill factor around 80 will resolve this problem with quite apt relief.
3. Why can't we go for partitioning the table or archiving the old data

Cheers,
Venkatesan Prabu .J

10.5.10

Date value from datetime field

Getting Date value from the Datetime field:

In SQL Server 2008, MS have provided an option of creating columns with Date datatype whereas in SQL Server 2005, this datatype is not available. Instead, We need to do minor tweeks to get the date value from the datetime field.

Below is a small code snippet to achieve it,

CREATE TABLE VENKAT_TABLE(ID INT,JOININGDATE DATETIME)
INSERT INTO VENKAT_TABLE VALUES(1,'1/1/2009')
SELECT CONVERT(VARCHAR,JOININGDATE,103) AS JOININGDATE FROM VENKAT_TABLE


Cheers,
Venkatesan Prabu .J

5.5.10

Resource file missing issue in Dotnet

Accidently, I got an issue with resource file in Dotnet module. Some how, my resouce file is missed out and I am bit confused to recreate the file. Below is the error received,

"Unable to read the resource information from the resx file."

MS is providing an easy fix for this issue. Open the project explorer and click the (+ sign ) near the control file created, your resource file will be displayed with cross symbol indicating its an errored file.

Delete the file and close your project.

Now, re-open the project your resource file will be created automatically. Simple fix.

Hats-Off to Microsoft

Cheers,
Venkatesan Prabu .J

3.5.10

Executing SQL Scripts using SQL Server

Today, One of my friend have asked about executing a set of script files in SQL Server 2000.
In Oracle, we can execute the scripts by using @ command.

>@script1.sql
>@script2.sql

But, in SQL Server we can't acheieve it directly. Let's see a simple approach to achieve it.

Step 1: Change the sql file into a cmd file.

Step 2: Create another file with bat extension, and type your command

isql -U(UserName) -P(your Password) -i "File path"


Execute your batch file in the command prompt.


Else, You can create SSIS package and loop through your sql files.
Cheers,
Venkatesan Prabu .J

My T-SQL Gallery @code.msdn.microsoft


Created my own T-SQL Gallery in Microsoft site. Do visit the same and share your feedback,

http://code.msdn.microsoft.com/VenkatSQLSample/Thread/List.aspx

Thanks and Regards,
Venkatesan Prabu .J

SQL Server Interview questions - Part 1

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.


The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables


What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.


What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

Select Statement in SQL Server

Select Statement in SQL Server

String Functions in sql server

String Functions in sql server
Substring/Len/replace/Ltrim/Rtrim

SQL Server Interview Question - Part 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database


What is a trigger?

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.


What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table.

Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Interview question

Extent Vs Page?

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB.

Eight consecutive pages will form an extent 8 * 8KB = 64KB.

Thus I/O level operation will be happening at pages level.The pages will hold a template information at the start of each page (header of the page).

They are,

1. page number,

2. page type,

3. the amount of free space on the page,

4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents

2. Mixed extents

Uniform Extents:It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.Mixed

Extents:Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.

Herecomes, our fragmentation and reindexing concepts.

Best Joke - Enjoy it

Best Joke - Enjoy it