CHARINDEX function returns the starting position in a character string

To get the starting position of a character that you might be looking, there is in SQL a function called CHARINDEX that will give you just that.

Example:

SELECT CHARINDEX(' ', [ContactName]) AS PositionOfWSInContactNameFROM [Northwind].[dbo].[Customers]This will return to you the position where the white space begins in a column.

The Syntax is:

CHARINDEX ( expression1 , expression2 [ , start_location ] )

expression1 - An expression that contains the sequence of characters to be found
expression2 - An expression that is typically a column searched for the specified sequence
start_location - Not necessary - The character position from which to start searching forexpression1 in expression2. If start_location is not given, is a negative number, or is 0, the search starts at the beginning of expression2.

Inserting data in SQL table with identity

To insert identity values in a table there is a function IDENTITY_INSERT that needs to be set on a table before Insert statement is executed.

Example:

SET IDENTITY_INSERT Table1 ON

INSERT INTO Table1
(Id, firstname, lastname)
(SELECT Id, firstname, lastname
FROM Table2)

SET IDENTITY_INSERT Table1 OFF

Merge values from multiple rows into one row

Today I needed to make a report to show a few Item in a single column attribute. What I needed is to join values from many rows into a single column attribute.

A nice example that I found quickly from a blog SQL Server Curry saved me quite some time so I decided to post this on my blog as well.

Example:

Items in table ItemMaster with :
ItemMaster  -> 

Shirt
Trouser
TShirt
Capri
Jeans
Barmuda

Result I wanted to get is:

[Shirt, Trouser, TShirt, Capri, Jeans, Barmuda] in a single attribute column


My prefered way that I found on blog was to use MS SQL STUFF:

SELECT DISTINCT STUFF( (SELECT ', ' + [Item] from [ItemMaster] FOR XMLPATH('')),1,1,'') as [Item] FROM [ItemMaster]

Another aproach that I would do as well if not finding previous reference is to store the row values into a variable using COALESCE in SQL.

DECLARE @str VARCHAR(100)SELECT @str = COALESCE(@str + ', ', '') + [Item]FROM [ItemMaster]

SELECT Item= @str

Use of COALESCE

The purpose of COALESCE function in MS-SQL is to return nonnull expression within given arguments. In an example below the result will be the value that is not null and is found first within the given arguments (attributes in the table).

USE AdventureWorks ;
GO
SELECT Name, Class, Color, ProductNumber, COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product ;
GO
RESULTS:
If Class and Color Values are null and ProductNumber is '123' it will print '123' As FirstNotNull.

We can use Coalesce for Pivot also. Run the below command in Adventureworks database:

SELECT Name FROM HumanResources.Department WHERE (GroupName = 'Executive General and Administration')

Here we will get the standard Result like this:

Now if we want to Pivot the data, we can use like this:

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';' 
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')

SELECT @DepartmentName AS DepartmentNames

and get the following result set.

Update all but the top n records from database table

In my project I needed to update all my records but the top(1) that is displayed. A very easy and efficient way that I found out to do it is to use syntax like this:
Hello SQL -> Update for me all records but the one that are not in top(1). In SQL this could be translated as the following example.

Example:

UPDATE table1
SET filed1 = 'New value'
WHERE Id NOT IN
(Select TOP(1) Id FROM table1)
You could as well rewrite this using INNER JOIN and achieve the same results.

TRIGGER INSTEAD OF Implement logical deletes

INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them. Actual insert, delete or update operation do not occur at all. However they have their associated inserted and deleted tables simulating the DML operation. INSTEAD OF triggers do not work in a recursive manner. Even if you update the same table inside the INSTEAD OF trigger, the trigger will not be invoked a second time. So INSTEAD OF triggers can be used to avoid recursion.


INSTEAD OF trigger may be attached for delete operations. 


The example below demonstrates simple logical delete implementations:


CREATE TRIGGER trigg_MyTable on MyTable
INSTEAD OF DELETE
AS
IF @@ROWCOUNT = 0
RETURN
UPDATE tbl
SET Deleted = '1'
FROM MyTable tbl 
JOIN deleted d ON d.PK_ID = tbl.PK_ID

Remove unwanted characters from a string

Sometime it is required to remove some part of string from a string and replace that with some other string. The replace function will help you solve the problem when you need to remove or replace string values from your specified queries results.

Example:

SELECT REPLACE ([ContactName], 'M','') AS ContactNamesNoM
FROM [Northwind].[dbo].[Customers]This will return ContactNames where M in ContactNames will be replaced by white space.

The Syntax goes as:

REPLACE ( string_expression , string_pattern , string_replacement )

Import data from comma-sepertated values file.

Sometime we need to insert data in SQL Table from some Text/CSV File. Here, we have CSV file in drive C: with name import.csv with following content. The location of the file is C:\import.csv.

ID,UserName,FName,LName

1,user1,Akhil,Shah

2,user2,Nikhil,Raj

3,user3,Samar,Das 

4,user4,Arun,Ladha

Now, to import the above CSV file in SQL, We have to create a table with 4 Columns:

CREATE TABLE ImportFromCSV (  

  ID INT,  

  UserName VARCHAR(25),  

  FName VARCHAR(50),  

  LName VARCHAR(50)

); 

Now we can import the above CSV file into ImportFromCSV Table. We will use BULK INSERT to insert data into table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT ImportFromCSV   

FROM 'c:\import.csv'  WITH  

(FIRSTROW = 2,  

 FIELDTERMINATOR = ',',  

 ROWTERMINATOR = '\n')  

GO  

In above script, parameter FIRSTROW will tell BULK LOAD that we don’t want to import first line (= 1) because it contains headers.

If you will have table what have less columns than the csv file these columns will be added after text in the last column. If this column will reach maximum length, this line will not be inserted. With MAXERRORS you can specify the maximum number of error lines what are allowed before whole INSERT will be considered as failed.

You can try this also:

SELECT * FROM OPENROWSET(’MSDASQL’,’Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\import.csv; Extensions=CSV; HDR=No;’,’SELECT * FROM import.csv’)

In all cases you can write a script which will import the data manually. Parsing the csv file and running the insert is easy to write. 

But IN this CASE you have to ENABLE the Ad Hoc Distributed Queries.

Repair a Suspect database

Today when I connect to the Database Server, I saw Database is in Suspect Mode. So, Server is not allowing me to do anything on the Database. A database can go into suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc. To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘DBName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
The above query will give the exact status of the Database. Now, To repair the database, I ran the following queries,
EXEC sp_resetstatus ‘DBName’;

ALTER DATABASE DBName SET EMERGENCY

DBCC checkdb(‘DBName’)

ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘DBName’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBName SET MULTI_USER

and now I can access my Database.

One thing we should keep in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be reverted. There is no way to go back to the previous state of the database. So as a precautionary step we should take backup of our database before executing above mentioned queries.

SQL Server Backup

Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it.

Today I got the error in one system.
"Login failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it."


the main cause of this error is 
"SQL Account only locked out, if it is enfoced to use password policies."

To solve this error run the below query:

ALTER LOGIN sa WITH PASSWORD = ‘password’ UNLOCK

We can aslo do this from GUI

Why are my insert, update statements failing with the following error? Server: Msg 8152, Level 16, State 9, Line 1 String or binary data would be truncated

This error occurs, when the length of the value entered by you into a char, varchar, nchar, nvarchar column is longer than the maximum length of the column. For example, inserting 'FAQ' into a char(2) column would result in this error.

Profiler is handy in troubleshooting this error. If data truncation is okay with you and you don't want to see this error, then turn off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF.

Steps to reproduce the problem:CREATE TABLE MyTable
(
Pkey int PRIMARY KEY,
Col1 char(10)
)
GO
INSERT INTO MyTable (Pkey, Col1) VALUES (1, 'SQL Server Clustering FAQ')
GO
Make sure, you restrict the length of input, in your front-end applications. Check length of input before inserting or updating in Database.


For example, you could use the MAXLENGTH property of the text boxes in HTML forms. 

Error coming while dropping Database: Already in use

Sometime when we try to delete the database, an error comes "Cannot drop database because it is currently in Use".


This is coming because the connection to database is opening somewhere.


Now, we can delete database this way:


Right Click on the Database > Delete > Check the checkbox "Close existing connections" at the bottom > Ok.

Query to get CPU and memory Information

Run below query to get the CPU and memory information:


EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 'ProcessorNameString';


-- Query to get CPU and Memory Info
SELECT CPU_Count AS [No. of Logical CPU], CPU_Count/Hyperthread_Ratio AS [No of Physical CPU], Physical_Memory_In_Bytes/1048576 AS [Total Physical Memory (MB)] 
FROM sys.dm_os_sys_info OPTION (RECOMPILE)

SQL script to create Folder on local disk

We can create Folder in local Hard Disk directly from SQL. Check below script for Create Folder. Below query first check for the existence of folder in HDD if not found than it creates new.


DECLARE @ChkFolder AS nvarchar(4000)
DECLARE @Folder_Exists AS INT
set @ChkFolder = 'C:\Temp\'
DECLARE @File_Result TABLE
(File_Exists INT,
File_Is_A_Folder INT,
Parent_Folder_Exists INT
)
INSERT INTO @File_Result
(File_Exists, File_Is_A_Folder, Parent_Folder_Exists)
EXEC [master].dbo.xp_FileExist @ChkFolder
SELECT @Folder_Exists = File_Is_A_Folder
FROM @File_Result
--script to create Folder
if @Folder_Exists = 0
BEGIN
PRINT 'Folder is not exists, creating new one '
EXECUTE [master].dbo.xp_Create_SubDir @ChkFolder
PRINT @ChkFolder +  'created on ' + @@servername
END
ELSE
PRINT 'Folder already exists'
GO

How to get rid of the time part from the date returned by GETDATE function?

There are various ways you can get Date only from DateTime.


You can use DATEPARTfunction to get Date only 

SELECT LTRIM(RTRIM(STR(DATEPART(YY, GETDATE())))) + '-' + LTRIM(RTRIM(STR(DATEPART(M, GETDATE())))) + '-' + LTRIM(RTRIM(STR(DATEPART(D, GETDATE()))))

You can use Convert Function. It is better than above.
You have to use the CONVERT function to strip the time off the date. Any of the following commands will do this:

SELECT CONVERT(char,GETDATE(),101)
SELECT CONVERT(char,GETDATE(),102)
SELECT CONVERT(char,GETDATE(),103)
SELECT CONVERT(char,GETDATE(),1)



See SQL Server Books Online for more information on CONVERT function.

You can also do like this

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS Date_Only

If you do not want thetimepart at all better declare the column as smalldatetime

Check Hard Disk Space

CREATE PROCEDURE sp_totalHDDspace
AS
SET NOCOUNT ON

DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur
DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive

DROP TABLE #drives

RETURN
go