The SQL Server CONCAT_WS() function concatenates two or more strings into one string with a separator. CONCAT_WS() means concatenate with separator.
Category: SQL
Search text in all tables in SQL Server
Please note that with a little bit changing you can also use this query as a stored procedure.
How to hide message window in MS SQL Server
How to find the first missing value in a series in MS SQL
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 […]
How to solve ‘incorrect syntax near the keyword WITH’ problem?
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.
How to solve ‘Size property has an invalid size of 0’ in C#
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.
How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL
CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO
How to create Local and Global Temp Tables in MS SQL
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 On/Off in MSSQL
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.