Daily micro-tips for C#, SQL, performance, and scalable backend engineering.
NOCOUNT is by default deactivated (OFF) at the server level. It means by default, the server will send to the client the number of rows affected by the SQL query executed which is, in most cases, useless because no one will read this information. Deactivating this feature will save some network traffic and improve the […]
“NULL” is never equal to anything, even itself. Therefore comparisons using equality operators will always return False, even when the value actually IS NULL. For that reason, comparison operators should never be used to make comparisons with NULL; IS NULL and IS NOT NULL should be used instead. Bad example: UPDATE books SET title = ‘unknown’ WHERE title = NULL — Noncompliant […]
In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well. SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS — Skip this number of rows FETCH NEXT 1 ROWS ONLY; — Return this […]
select ‘Parameter_name’ = name, ‘Type’ = type_name(user_type_id), ‘Length’ = max_length, ‘Prec’ = case when type_name(system_type_id) = ‘uniqueidentifier’ then precision else OdbcPrec(system_type_id, max_length, precision) end, ‘Scale’ = OdbcScale(system_type_id, scale), ‘Param_order’ = parameter_id, ‘Collation’ = convert(sysname, case when system_type_id in (35, 99, 167, 175, 231, 239) then ServerProperty(‘collation’) end) from sys.parameters where object_id = object_id(‘MySchema.MyProcedureName’)
When working with Code First Migrations, developers often struggle with setting default values at the database level. While you can set a default value in your C# class, doing it directly in the SQL schema is much more robust. Here is how you can achieve this using defaultValue and defaultValueSql. 1. Using Static Default Values […]
SELECT o.name as [Trigger Name], CASE WHEN o.type = ‘TR’ THEN ‘SQL DML Trigger’ WHEN o.type = ‘TA’ THEN ‘DML Assembly Trigger’ END AS [Trigger Type], sc.name AS [Schema_Name], OBJECT_NAME(parent_object_id) as [Table Name], o.create_date [Trigger Create Date], o.modify_date [Trigger Modified Date] FROM sys.objects o INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id WHERE (type = […]
The Context: SQL Server Service Broker is a powerful framework for building highly scalable, asynchronous database applications. Whether you are using Query Notifications, External Activations, or Distributed Messaging, knowing how to properly manage the Broker state is essential for any DBA or Backend Developer. The Challenge: Simply running an ALTER DATABASE command often hangs indefinitely […]
Recursive triggers are set at the database level. It’s part of the database metadata information and is available through “sys.databases” view. You can use the below query to check whether the recursive triggers are enabled or not on your database: SELECT name AS ‘Database’, is_recursive_triggers_on AS ‘Recursive Trigger Enabled’ FROM sys.databases Here is the output:
The easiest way to do that is using a function CREATE FUNCTION [dbo].[HideNameWithAsterisks](@Name varchar(max)) RETURNS varchar(MAX) AS BEGIN DECLARE @loop int = LEN(@Name) WHILE @loop > 1 SELECT @Name = STUFF(@Name, @loop, 1, CASE WHEN SUBSTRING(@Name, @loop – 1, 2) like ‘% ‘ THEN ‘ ‘ WHEN SUBSTRING(@Name, @loop – 1, 2) like ‘ %’ […]
DECLARE @MyValue int SELECT @MyValue = Column1 WHERE Column2 > 50 IF @MyValue IS NULL BEGIN RAISEERROR(‘This is my custom message from SQL.’, 16, 1) END ELSE SELECT MyValue END Use an error code within 11-16, or just use 16 for a “general” case. RAISERROR(‘This is my custom message from SQL.’,16,1) Why? Here’s summary of […]
The Scenario: You are handed a legacy database with hundreds of tables, or perhaps you’re debugging a “Type Mismatch” error in your backend. You need to know exactly what a table is made of—data types, nullability, and precision—without clicking through a clunky UI. The Solution: While every database engine has its own way, the INFORMATION_SCHEMA […]
Declare @Parameter1 int; Declare @Parameter2 int; DECLARE Cur1 CURSOR FOR SELECT Column1 From Table1; OPEN Cur1 FETCH NEXT FROM Cur1 INTO @Parameter1; WHILE @@FETCH_STATUS = 0 BEGIN PRINT ‘Processing Column1: ‘ + Cast(@Parameter1 as Varchar); DECLARE Cur2 CURSOR FOR SELECT Column2 FROM Table2 Where Column2 = @Parameter1; OPEN Cur2; FETCH NEXT FROM Cur2 INTO @Parameter2; […]
SELECT (SCHEMA_NAME(A.schema_id) + ‘.’ + A.Name) AS TableName , SUM(B.rows) AS RecordCount FROM sys.objects A INNER JOIN sys.partitions B ON A.object_id = B.object_id WHERE A.type = ‘U’ GROUP BY A.schema_id, A.Name ORDER BY RecordCount DESC
If you only want to match N” as an empty string SELECT COLUMN FROM TABLE WHERE DATALENGTH(COLUMN) > 0 If you want to count any string consisting entirely of spaces as empty SELECT COLUMN FROM TABLE WHERE COLUMN <> ” If you want to use DATALENGTH for any string consisting entirely of spaces then you […]
SELECT m1.Column1, m1.Column2, m1.Column3, r.MaxTime FROM ( SELECT Column1, MAX(TimeColumn) as MaxTime FROM MyTable GROUP BY Column1 ) r INNER JOIN MyTable m1 ON m1.Column1= r.Column1AND m1.TimeColumn = r.MaxTime
The SQL Server CONCAT_WS() function concatenates two or more strings into one string with a separator. CONCAT_WS() means concatenate with separator.
Please note that with a little bit changing you can also use this query as a stored procedure.
The Struggle: You are writing a complex stored procedure on your laptop screen. Every time you execute a query, the bottom half of your screen gets hijacked by the “Messages” or “Results” window, leaving you with a tiny viewport for your actual code. The Fix: Stop reaching for the mouse to minimize that window. Master […]
Let’s say we have this values in the database: num — 1 2 4 5 6 8 9 11 To Find the first missing value, we can use this: ;WITH CteRN AS( SELECT *, RN = num – ROW_NUMBER() OVER(ORDER BY num) FROM tbl ) SELECT TOP 1 num – RN FROM CteRN WHERE RN […]
Always use with statement like ;WITH then you’ll never get this error. The WITH command required a ; between it and any previous command, by always using ;WITH you’ll never have to remember to do this.
You need to define a length when specifying the varchar parameter: SqlParameter TheFirstJob = cmd.Parameters.Add(“@spParam”, SqlDbType.VarChar, 50); Note: You should use the same length as defined in your SQL Server stored procedure.
CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO
Creating Local Temp Table [with #]: SELECT TOP(10) ID INTO #TempTable FROM MyTable Craeting Global Temp Table [with ##]: SELECT TOP(10) ID INTO ##TempTable FROM MyTable Reaching Local Temp Table with Query: SELECT name FROM tempdb..sysobjects WHERE name LIKE ‘#TempTable%’
SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable (IdentityColumn, col2, col3, …) VALUES (IdentityValue, col2value, col3value, …) SET IDENTITY_INSERT MyTable OFF Note that you can not insert explicit value for identity column in table ‘MyTable’ when IDENTITY_INSERT is set to OFF.
DBCC CHECKIDENT(‘TableName’, RESEED, 0) Note that after running DBCC CHECKIDENT(‘TableName’, RESEED, 0): – Newly created tables will start with identity 0 – Existing tables will continue with identity 1
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint […]
– In Object Explorer, connect to an instance of the Database Engine. – On the toolbar, click New Query. – In the query window, enter the following statement that uses the sp_helptext system stored procedure. Change the database name and stored procedure name to reference the database and stored procedure that you want. EXEC sp_helptext […]
string_split() is a table-valued function that splits a string into rows of substrings, based on a specified separator character. SELECT * FROM string_split(’21;32;34;47;53;61;’, ‘;’) OUTPUT ====== 21 32 34 47 53 61
You can use SQL “Replicate” command to mask the data. Below code shows how to make it:
EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’