Insert Result Into Hash/Temp Table- Sql Server

Jul 22, 2019 BY xtreme

Insert Result Of Another Store Procedure CREATE TABLE #Result ( Status nvarchar(100), Id INT, Name nvarchar(200) ) INSERT INTO #Result EXEC dbo.rpt_get_result @id,@filter Insert Result of Dynamic Query CREATE TABLE #Result ( Status nvarchar(100), ...

Compatibility Level Check / Set - performance Issue - Sql Server

Jul 22, 2019 BY xtreme

To Compatible Transact-SQL and query processing behaviors with the specified version of the SQL Server Database Engine. -- To check the compatibility level: select compatibility_level from sys.databases where name = '' -- To set the compatibility level: alter database database_name...

Format Date in Tsql - Sql Server

Jul 22, 2019 BY xtreme

SQL Server 2012 or above versions SELECT TOP 1 FORMAT (GETDATE(), 'd', 'en-us') --US -MM/DD/YYYY SELECT TOP 1 FORMAT (GETDATE(), 'd', 'fr-FR') --FR -DD/MM/YYYY SELECT TOP 1 FORMAT (GETDATE(), 'd', 'ja-JP') --JP -YYYY/MM/DD SELECT TOP 1 FORMAT (GETDATE(), 'D', 'en-us') --...

Get Object Updated or Created in last N Days - Sql Server

Jul 22, 2019 BY xtreme

Store Procedure / Tables Updated in Last 30 Days select dateadd(day, -30, getdate()) ,* from sys.objects where (type = 'P' ) and modify_date > dateadd(day, -30, getdate()) and name like '%Table_Name%' order by modify_date desc NOTE- Use U for User Defined Ta...

Tips to improve performance of stored procedure - Sql Server

Jun 03, 2019 BY xtreme

1. Use SET NOCOUNT ONThis one line of code, put at the top of a stored procedure turns off informational messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. By removing this extra overhead ...

Split Comma Separated String into Rows - Sql Server

May 02, 2019 BY xtreme

3 different approaches to split string into rows.Approach 1: Simple Split Approach declare @strString AS Varchar(4000) ='A,B,C,D,E' declare @Result TABLE(Value varchar(20)) DECLARE @str VARCHAR(20) DECLARE @ind Int IF(@strString is not null) BEGIN SET @ind = CharIndex(',...

Create a comma separated string- Sql Service

May 02, 2019 BY xtreme

In SQL Server, we have 2 best options to create a comma separated string. COALESCE Till SQL Server 7.0 and SQL Server 2000, we had only one option Use of COALESCE to create comma separated list DECLARE @List VARCHAR(8000) SELECT @List = COALESCE(@List + ',', '') + CAST(Color AS VARCH...