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.
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