Category: SQL

SQL

CHARINDEX() in SQL Server
CHARINDEX() function returns the position of the string to search in the string to be searched.

Syntax: CHARINDEX ( search string, string to be searched, position search to start )

Remarks:

    1. CHARINDEX is a case insensitive search

    2. CHARINDEX always returns first occurrence of the search word from the search position is defined

    3. If search position is not defined, it returns the first occurrence position

Examples: 

Declare @SearchtoString varchar(100) = 'SQL Server is a database server. I love SQL Server.'
Declare @SearchString varchar(100) = 'Server'

SELECT CHARINDEX(@SearchString, @SearchtoString,4) AS MatchPosition;


--Another example
;With cte as
(
	Select @SearchtoString stringtobesearched, CHARINDEX(@SearchString, @SearchtoString, 0) StringPosition
	union all 
	Select stringtobesearched, CHARINDEX(@SearchString, stringtobesearched, StringPosition+1) From cte
	where StringPosition > 0
)
Select * From cte where StringPosition  0

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

sp_procoption in SQL Server
sp_procoption procedure set or clears a stored procedure for automatic execution that runs every time an instance of SQL Server is started.

Syntax: 

exec sp_procoption @ProcName = ['stored procedure name'], 
@OptionName = 'STARTUP', 
@OptionValue = [on|off]

ProcName – is the procedure name

OptionName – is the option name ( Currently, this does not really make sense as there is only one value permitted, which is ‘STARTUP’

OptionValue – is the value whether it is (ON or OFF)/(1 or 0)/(TRUE or FALSE)/(YES or NO)

Remarks:

    1. Startup procedure must be in the master database and owned by DBO

    2. It allows either SQL Procedures / CLR Procedures / Extended Procedures

    3. The procedure cannot contain INPUT or OUTPUT parameters

    4. It changes server configuration – “scan for startup procs”

    Once the server is rebooted, then the “run_value” also gets changed to 1.

    5. Execution of the stored procedures starts when all databases are recovered and the “Recovery is completed” message is logged at startup

    6. Requires sysadmin permission

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE() and SYSDATETIME() in SQL Server
CURRENT_TIMESTAMP – Returns the current system date and time.

GETDATE() – Returns the current system date and time.

GETUTCDATE() – Returns the current UTC date and time.

SYSDATETIME() – Returns the date and time.

Precisely, let us quickly see the output of each one as below. 

Select current_timestamp,getdate(),getutcdate(),sysdatetime()

Whats the difference between CURRENT_TIMESTAMP and GETDATE()?

CURRENT_TIMESTAMP is an ANSI SQL function whereas GETDATE is the T-SQL version of that same function.It is to be mentioned that CURRENT_TIMESTAMP is getting converted to GETDATE() when creating the object within SSMS. Both functions retrieve their value from the operating system in the same way. CURRENT_TIMESTAMP is the recommended usage because it is portable to any ANSI compliant database, where as GETDATE() is not. So, its more of a project requirement and for future demands or compatibility, few people would go with CURRENT_TIMESTAMP over GETDATE().

About performance – I have not seen any notable or any difference between thee two, it may be due to the fact that SQL-SERVER engine is converting CURRENT_TIMESTAMP to GETDATE(). 

Select CURRENT_TIMESTAMP,getdate() Where CURRENT_TIMESTAMP=getdate()

Whats the difference between GETDATE() and GETUTCDATE()?

The difference between GETDATE() and GETUTCDATE() is in timezone. GETUTCDATE() returns current time and date in UTC (Universal Time Coordinate) or GMT timezone. The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

Whats the difference between GETDATE() and SYSDATETIME()?

If you want more precision, yes SYSDATETIME() is your friend. SYSDATETIME() which has millisecond precision. It actually returns a datetime2(7) value, but the last four decimals are just noise to me. I hardly seen people are using it.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

LEFT() and RIGHT() functions in SQL Server
Extracts defined number of characters from the string provided, from left or right depending on LEFT() or RIGHT() function used.

That means Left(‘string’,3) returns “str”, 3 characters from the left side. Likewise, RIGHT does it from right side of the string.

A simple representation as below:

SELECT 'Latheesh' Fullname,Left('Latheesh', 4) AS LeftString,RIGHT('Latheesh', 4) AS RightString;

Another common use case is when we would like to add the ‘0’ characters prefixing depending on available numbers.For an example, if we need to represent digit like as always 6 characters irrespective the actual number by adding “0” (s) to the digit, we can easily achieve with RIGHT() function. 
Declare @mydigit int 

Set @mydigit = '34'
Select Right('000000'+cast(@mydigit as varchar(6)),6)

Set @mydigit = '100'
Select Right('000000'+cast(@mydigit as varchar(6)),6)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

LEN() and DATALENGTH() in SQL Server
LEN() function returns the number of characters in the string excluding the trailing spaces, however, DATALENGTH() returns the bytes in the string that means it counts both trailing and leading spaces. Len() function return type is bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

Let us quickly see the difference with a small set of data with various datatypes as below. 

Create table LENandDATALENGTH
(
	varcharString varchar(100),
	nvarcharString nvarchar(100),
	charString char(100),
	ncharString nchar(100)
)

Insert into LenandDatalength Values
('SQLServer','SQLServer','SQLServer','SQLServer'),--There is no leading or trailing space
(' SQLServer',' SQLServer',' SQLServer',' SQLServer'), -- There is leading space
('SQLServer ','SQLServer ','SQLServer ','SQLServer ') -- There is trailing space

Select *,
	Len(varcharString) varcharlen, Datalength(varcharString) varcharDatalen,
	Len(nvarcharString) nvarcharlen, Datalength(nvarcharString) nvarcharDatalen,
	Len(charString) charlen, Datalength(charString) charDatalen,
	Len(ncharString) ncharlen, Datalength(ncharString) ncharDatalen
From LenandDatalength

Drop table LenandDatalength

The below chart is arrived from the above results for a better understanding.

By now, we know the difference between LEN() and DATALENGTH() better, so use the functions as required to the right information as per the requirement.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!