SQL Server Tips

sql-tips.md

SQL Server Tips

Contents

• Search For Text in Routines
• Rename an Index
• Find Column Type Of Query Results
• Change DB Compatibility Level
• Converting a Date to Another Time Zone
• SQL Server Data Types Table

Search For Text in Routines

In stored procedures:

SELECT * FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%sometext%'

In views:

SELECT * FROM INFORMATION_SCHEMA.Views WHERE VIEW_DEFINITION LIKE '%sometext%'

In functions, triggers and other objects:

SELECT O.[name], O.type_desc 
  FROM sys.sql_modules M
  JOIN sys.objects O ON O.object_id = M.object_id
 WHERE M.definition LIKE '%sometext%';

In table names:

SELECT * 
  FROM sys.tables 
 WHERE name LIKE 'something[_]%'; -- remember to escape certain characters like _

Back to top

Rename an Index

EXEC sp_rename N'SCHEMA.TABLE.OLD_INDEX_NAME', N'NEW_INDEX_NAME', N'index';

Back to top

Find Column Type Of Query Results

SELECT name, user_type_name, system_type_name, max_length, [precision], scale
  FROM sys.dm_exec_describe_first_result_set(
    'SELECT ISNULL(MAX(columnId), 0) AS maxID FROM someTable WHERE id = 1', 
    null, 
    0
  );

Back to top

Change DB Compatibility Level

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

USE [master]
GO
ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 160
GO

Back to top

Converting a Date to Another Time Zone

This assumes that the input date is in same zone as target:

DECLARE @dt1 smalldatetime, @dt2 smalldatetime;
SET @dt1 = '2023-06-01 10:00:00';
SET @dt2 = '2023-06-07 10:00:00';
SELECT
  @dt1 AS [@dt1],
  @dt2 AS [@dt2],
  @dt1 AT TIME ZONE 'GMT Standard Time' AS [GMT Time: @dt1],
  @dt2 AT TIME ZONE 'GMT Standard Time' AS [GMT Time: @dt2];

This assumes your source date is in UTC and will convert to specified zone:

DECLARE @dt1 smalldatetime, @dt2 smalldatetime;
SET @dt1 = '2023-06-01 10:00:00';
SET @dt2 = '2023-06-07 10:00:00';
SELECT
  @dt1 AS [@dt1],
  @dt2 AS [@dt2],
  @dt1 AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' AS [GMT Time: @dt1],
  @dt2 AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' AS [GMT Time: @dt2];

If you use DATETIMEOFFSET then you can use the first query:

DECLARE @dt1 datetimeoffset, @dt2 datetimeoffset;
SET @dt1 = '2023-06-01 10:00:00 +00:00';
SET @dt2 = '2023-06-07 10:00:00 +00:00';

SELECT
  @dt1 AS [@dt1],
  @dt2 AS [@dt2],

  @dt1 AT TIME ZONE 'GMT Standard Time' AS [GMT Time: @dt1],
  @dt2 AT TIME ZONE 'GMT Standard Time' AS [GMT Time: @dt2],

  @dt1 AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' AS [GMT Time 2: @dt1],
  @dt2 AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time' AS [GMT Time 2: @dt2];

More TimeZone info:

• https://database.guide/convert-a-date-to-another-time-zone-in-sql-server/
• https://sqlperformance.com/2016/07/sql-plan/at-time-zone
• https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16
• https://learn.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?view=sql-server-ver16
• https://stackoverflow.com/questions/7908343/list-of-timezone-ids-for-use-with-findtimezonebyid-in-c

Back to top

SQL Server Data Types Table

SQL Server Database Engine type	.NET Framework type	SqlDbType enumeration	SqlDataReader SqlTypes typed accessor	DbType enumeration	SqlDataReader DbType typed accessor
bigint	Int64	BigInt	GetSqlInt64	Int64	GetInt64
binary	Byte[]	VarBinary	GetSqlBinary	Binary	GetBytes
bit	Boolean	Bit	GetSqlBoolean	Boolean	GetBoolean
char	String Char[]	Char	GetSqlString	AnsiStringFixedLength, String	GetString GetChars
date (SQL Server 2008 and later)	DateTime	Date	GetSqlDateTime	Date	GetDateTime
datetime	DateTime	DateTime	GetSqlDateTime	DateTime	GetDateTime
datetime2 (SQL Server 2008 and later)	DateTime	DateTime2	None	DateTime2	GetDateTime
datetimeoffset (SQL Server 2008 and later)	DateTimeOffset	DateTimeOffset	none	DateTimeOffset	GetDateTimeOffset
decimal	Decimal	Decimal	GetSqlDecimal	Decimal	GetDecimal
FILESTREAM attribute (varbinary(max))	Byte[]	VarBinary	GetSqlBytes	Binary	GetBytes
float	Double	Float	GetSqlDouble	Double	GetDouble
image	Byte[]	Binary	GetSqlBinary	Binary	GetBytes
int	Int32	Int	GetSqlInt32	Int32	GetInt32
money	Decimal	Money	GetSqlMoney	Decimal	GetDecimal
nchar	String Char[]	NChar	GetSqlString	StringFixedLength	GetString GetChars
ntext	String Char[]	NText	GetSqlString	String	GetString GetChars
numeric	Decimal	Decimal	GetSqlDecimal	Decimal	GetDecimal
nvarchar	String Char[]	NVarChar	GetSqlString	String	GetString GetChars
real	Single	Real	GetSqlSingle	Single	GetFloat
rowversion	Byte[]	Timestamp	GetSqlBinary	Binary	GetBytes
smalldatetime	DateTime	DateTime	GetSqlDateTime	DateTime	GetDateTime
smallint	Int16	SmallInt	GetSqlInt16	Int16	GetInt16
smallmoney	Decimal	SmallMoney	GetSqlMoney	Decimal	GetDecimal
sql_variant	Object	Variant	GetSqlValue	Object	GetValue
text	String Char[]	Text	GetSqlString	String	GetString GetChars
time (SQL Server 2008 and later)	TimeSpan	Time	none	Time	GetTimeSpan
timestamp	Byte[]	Timestamp	GetSqlBinary	Binary	GetBytes
tinyint	Byte	TinyInt	GetSqlByte	Byte	GetByte
uniqueidentifier	Guid	UniqueIdentifier	GetSqlGuid	Guid	GetGuid
varbinary	Byte[]	VarBinary	GetSqlBinary	Binary	GetBytes
varchar	String Char[]	VarChar	GetSqlString	AnsiString, String	GetString GetChars
xml	Xml	Xml	GetSqlXml	Xml	none

More info at https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

Back to top