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

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

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

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

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

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

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

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

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