Nagendra Prasad

Reduce size/Shrink Transaction Log of the DB - SQL Server

2009-08-21T13:21:15-12:00:00

In most of the DBs in the server will have more space, when the backup is used to store it in the development environment or testing environment, it might not have much data, but it will occupy more space physically.

To reduce the database size, then there is a way to reduce it. Shrink the Transactional Log. There are two ways of shrinking the Transaction Log. One is through Front-End and another is manual script.

Front-End:

Databases -> DbName. Right click on the DbName, then go to Tasks->Shrink->Files.
Here both the Data and Log files can be shrinked. you can see the amount of unused space in this window.

Script:

-- Shrink the Transaction Log
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

If you have any problems/confusions with the TransactionLogName, it will be stored in the table and it can be retrieved using:

select * from sys.database_files

I came across this issue, I had the database backup of 3GB .bak file. Once it is restored in the development environment, it occupies 80GB of space in which Transactional Log is nearly 70GB. I resolved it by doing the above.

I just want to share this, so that anyone else facing the same problem may get benefit out of this.

Restore DB with mdf file alone - SQL Server

2009-08-21T13:06:07-12:00:00

Basically, we need two files to restore the database. They are .mdf and .ldf files. There is another way to restore the database, if we have the database backup file(.bak). But we can restore using one file(.mdf file) as well. Here's how it will be restored using .mdf file.

-- Restore Database using MDF File alone
EXEC sp_attach_single_file_db @dbname = 'testerDB',
@physname = 'c:\testDB.mdf'

Hope this might help.

Find Position of a particular string in SQL Server

2009-03-31T16:24:52-12:00:00

To find a position of particular string/Char in the field of table, it is done using patindex. It might be well known for most of them. But what i found in that may be known to only few people. I want to share with everyone.

Table: tblNames
SELECT Name FROM tblNames

Name
Nagendra
John
Edward

3 row(s) affected.

Example 1: To find Char or String, here is the way. ( Many of them know this )
SELECT PATINDEX('John', name) as Pos, Name FROM tblNames

Pos Name
0       Nagendra
1       John
0       Edward

3 row(s) affected.

Example 2: To find particular Char or String in between the word. for eg., you need to look 'ohn' exists in any of the row. Then the above example will not work. it will throw the position as 0. It can be done using the wild card symbol '%'

SELECT PATINDEX('ohn', name) as Pos,PATINDEX('%ohn%', name) as PosNew, Name FROM tblNames

Pos PosNew Name
0       0               Nagendra
0       2               John
0       0               Edward

3 row(s) affected.
From the above example, it returned the position as 2(PosNew) with the wild card symbol example. But in the first one, it returned 0(Pos).
In simple, Behaviour of PATINDEX will be same as LIKE , where LIKE is used in Where clause and PATINDEX is used for search of a string/Char.

This can be used for search of a character or specific word according to your requirement.

Export SQL Server Data into Excel

2009-03-26T11:32:28-12:00:00

Lets see how to export SQL Server data into Excel.
First we have to enable the Ad Hoc Distributed Queries option.

Enable Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

After enabling it, we can proceed further to write the query for exporting the SQL Serer Data.
Export into Excel Query:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Email FROM [Sheet1$]')
SELECT Name, Email FROM tblnames
GO

Before executing the query, we need to do few steps:

Provide a Excel sheet path and Create a excel sheet as mentioned in the path. For eg., i have mentioned "C:\testing.xls" created testing.xls in the C:\
Fill the header row with the columns we provided in the query. For eg., i have Name and Email as columns. I have entered the column names in the excel as show below

Excel File - testing.xls

	A	B	C
1	Name	Email
2

Enter the provider in the query according to the Microsoft Excel version you installed in your version. For eg., i provided Microsoft.ACE.OLEDB.12.0 since i have installed Microsoft Office 2007. This provider works for MS Office 2003 and above. For MS Office 2002 and earlier versions, provider will be Microsoft.Jet.OLEDB.4.0. (Please check it).
If the sheet name of the Excel sheet varies from the default, then you need to change that in the query. For eg., i have used [Sheet1$] since i did not change the sheet name in my excel file.

Once everything is done, you can execute the above query and you will have the SQL Server data exported into your excel file.
Note: If you fail to create the excel file as mentioned in the query or fail to create the column names as mentioned in the query, you may get the error below:
Error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Wait/Delay in SQL Server

2009-03-25T14:50:05-12:00:00

If you have two queries and you have to execute one after another with some amount of delay, then that is possible in SQL Server.

Lets say, your first query fills up the table with some records. Lets assume that, you will get some responses based on this records which will fill other table. Now your second query should run based on the responses table. Then you can make a delay for quite amount of time.

Lets see a simple example of using waitfor and delay in SQL Server:

Syntax:

WAITFOR DELAY 'HH:MM:SS'

where HH:MM:SS - Hours, minutes and seconds respectively.

Example:

SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:05'
SELECT GETDATE() CurrentTime

Output:

CurrentTime
2009-03-25 14:47:13.797
CurrentTime
2009-03-25 14:47:18.800

Get dynamic number of rows using TOP in a SQL query - SQL Server

2009-03-24T16:15:04-12:00:00

If you want to get the n number of rows dynamically, then we need use variable. It can be achieved by two different methods.
Method 1:

DECLARE @MyCount VARCHAR(5)
DECLARE @sql NVARCHAR(200) -- note nvarchar datatype should be used, since it is for dynamic query

SELECT @MyCount =2
SELECT @sql = 'SELECT TOP ' + @MyCount + ' * FROM TBLNAMES'

EXEC sp_executesql @sql

Method 2:

DECLARE @MyCount int

SET @MyCount = 2
SELECT TOP (@MyCount) * FROM TBLNAMES;

First method would be the standard method, many people would use it.But i would recommend second method, since it has not many variables used and the @MyCount variable is used as int datatype over here.
In first method also we can use int datatype, but we need to convert it to string when it comes to a query since it is dynamic query.

Any comments are welcome.

Copy a table into new table with/without data - SQL Server

2009-03-20T14:01:23-12:00:00

Lets see how to copy an existing table to new table in SQL Server. There are two options. They are

Copy only the structure of an existing table into new table

Copy only the structure with data of an existing table into new table

Copy only the structure of an existing table into new table:

SELECT * INTO tblNew FROM tblOld WHERE 1=2

The above query will copy the structure of an existing table(tblOld) into the new table(tblNew).

Copy only the structure with data of an existing table into new table:

SELECT * INTO tblNew FROM tblOld

This is also same like the previous query, but it copies the structure of existing table(tblOld) with data as well into the new table(tblNew).

Date Custom Format - SQL Server

2009-03-19T16:29:43-12:00:00

As everyone knows, DATE can be formatted in different ways and the usual way will be using CONVERT

Syntax:
SELECT CONVERT(VARCHAR(30), GETDATE(), 106)

Where as 106 can be replaced by 101, 110, etc according to the requirements.

Custom Format:

If we need custom date format(19-March-2009), which is not a pre-defined format in CONVERT, then consider the below:

Syntax:
SELECT DATENAME(DAY,GETDATE()) + '-' + DATENAME(MONTH,GETDATE()) + '-' + DATENAME(YEAR, GETDATE())

I know, this is just a small thing. But still it might help someone who is in search of requirement like this !

Formatting number to add leading zeros - SQL Server

2009-03-19T12:15:35-12:00:00

Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works:

CREATE TABLE Numbers(Num INT);

Table Created.

Lets insert few values and see:

    INSERT Numbers VALUES('12');
    INSERT Numbers VALUES('112');
    INSERT Numbers VALUES('12');
    INSERT Numbers VALUES('122');
    INSERT Numbers VALUES('122');

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

Now we can see how the numbers are formatted with 6 digits, if it has less than 6 digits it will add leading zeros.
Data:

SELECT * FROM Numbers;

Num
12
112
12
122
122

5 row(s) affected.

Formatting:
SELECT RIGHT('00000'+ CONVERT(VARCHAR,Num),6) AS NUM FROM Numbers;

NUM
000012
000112
000012
000122
000122

5 row(s) affected.

Getting NULL values instead of data while concatenation - SQL Server

2009-03-17T15:38:11-12:00:00

I have to concatenate first name with last name. I got too many NULL values which it should not. Then I found the reason behind it.

Data in TblNames :

FIRST_NAME LAST_NAME
Edward            Jones
Jason               NULL
John                 Smith
Robert              NULL

4 row(s) affected.

Expected Result:

NAME
Edward Jones
Jason
John Smith
Robert

4 row(s) affected.

Before finding the solution:

SELECT FIRST_NAME + ' ' + LAST_NAME AS [NAME] FROM TBLNAMES;

NAME
Edward Jones
NULL
John Smith
NULL

5 row(s) affected.

I realized the if any of the columns(FIRST_NAME or LAST_NAME) is NULL, it returns the NULL value. To resolve this, set the concat_null_yields_null to OFF.

Solution:

SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT FIRST_NAME + ' ' + LAST_NAME AS [NAME] FROM TBLNAMES;
SET CONCAT_NULL_YIELDS_NULL ON;

NAME
Edward Jon
Jason
John Smithes
Robert

4 row(s) affected.

Hope it would help you.