How many times a character (string) is repeated within a string

Use below procedure to find the occurrence of some characters within a string.


DECLARE @strColumn VARCHAR(20)
DECLARE @searchString  VARCHAR(10)
DECLARE @intLen INT


SET @strColumn = '123asd123asd123' 
SET @searchString ='123'
SET @intLen = LEN(@searchString)


SELECT (LEN(@strColumn) - LEN(REPLACE(@strColumn, @searchString, '')))/@intLen 

Export query result to Excel or CSV

If you want to export the result of a query or Stored procedure from SQL Server Management Studio without writing any scripts then use following procedure:

Option1:

You don't have to change any formats for this option. You just execute your SP or SELECT statement and once you got the output on the Results Window. Just "Right click" on the result and choose "Save Result As" to save it as CSV file.

Option2::

One of the easiest option if you are using SQL Server 2005 or above is to make use of the output format specifier in SQL Server Management Studio.

Step1: Open SQL Server Management Studio.

Step2: Click on New Query

Step3: Goto Menu Query >> Query Options >> Within Results Choose Text

Step4: On the right window you can see an option called "Output Format". Select "Comma Delimited".

Step5: Next either Press Control + T (to select Results to Text option). Or goto Menu Query >> Results To >> Results to Text option.

Now you are all set. Just run your SELECT statement or SP with the SELECT statement to see the result as Comma Separated Values on the screen.

You can copy and save it as a file now.

Find when SQL Server was last started

When ever SQL Server is (re)started ‘TempDB’ gets recreated. So the below query should give us the time of when ‘SQL Server’ was last started.

Select create_date from sys.databases where [name] = 'tempdb'

When a SQL Server starts all the tasks which it initiates makes an entry in sysprocesses table with its ‘login time’. That said, all we need to do is find the least login_time which has got recorded in that table!

1. Select min(login_time) from master.dbo.sysprocesses 
OR
2. Select login_time from master.dbo.sysprocesses where spid = 1 
OR
3. Select login_time from sys.dm_exec_sessions where session_id = 1

Limit a VarChar Column to 10,000 Characters

Before SQL Server 2005 then maximum length of a VarChar data type is 8000. In SQL Server 2005 VarChar(Max) data type was introduced. VarChar(Max) can store upto 2,147,483,645 characters. 


Now you want to restrict the length of a VarChar data type to 1000. You had defined the column as VarChar(10000) and upon saving you got an error:

Msg 131, Level 15, State 3, Line 1

The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

Now it means you can set length of a column to either MAX or <= 8000. So follow below procedure to define max length to 10000.

To do this you had to create a check constraint against that column and set data type to Varchar(Max).

Example: added a column Address in CustomerMaster Table with Data type Varchar(Max). Now add a contraint on address column to restrict input length to 10000.

ALTER TABLE CustomerMaster ADD CONSTRAINT [MaxLen10000] CHECK (DATALENGTH([Address]) <= 10000)

Add or Modify IDENTITY property to an existing column

Method 1
Easiest method is to use SSMS to change the property.

• Goto SQL Server Management Studio
• Right click on the table name
• Choose Design/Modify
• Choose the column which needs to be set as Identity
• At the bottom window (Column Properties) for that column you can see "Identity Specification" has been by default set to "NO".
• Just change it to "YES" and save the table (CTRL + S).

Method 2

If there is no data in the table then use this method.

Drop the already exist column 

ALTER TABLE TempTable DROP Column ID;

GO;

Now add the ID column again with Identity specification on.

Alter Table TempTable Add ID INT IDENTITY
GO 

Method 3

If there is data in the table then use this method.

• Create a new table with identity column
• Enable IDENTITY_INSERT for this new table
• Move the data from this old table to this new table.
• Disable IDENTITY_INSERT for this new table
• Delete the old table.
• Rename the newtable with the oldtable name.