How to get a date in YYYY-MM-DD format from a TSQL datetime field?
SELECT CONVERT(char(10), GetDate(),126) Limiting the size of the varchar chops of the hour portion that you don’t want.
sql-server
Confused about UPDLOCK, HOLDLOCK
Why would UPDLOCK block selects? The Lock Compatibility Matrix clearly shows N for the S/U and U/S contention, as in No Conflict. As for the HOLDLOCK hint the documentation states: HOLDLOCK: Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. … SERIALIZABLE: … The scan is performed with the same semantics … Read more
Get list of databases from SQL Server
Execute: SELECT name FROM master.sys.databases This the preferred approach now, rather than dbo.sysdatabases, which has been deprecated for some time. Execute this query: SELECT name FROM master.dbo.sysdatabases or if you prefer EXEC sp_databases
Why use the INCLUDE clause when creating an index?
If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause is where you use INCLUDE. The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it’s not part of the tree INCLUDE columns … Read more
best way to convert and validate a date string
First of all, since you’re using SQL Server 2005, you should put your code that might fail into BEGIN TRY…..END TRY BEGIN CATCH….END CATCH blocks – try/catch blocks for T-SQL! Second, for all date manipulation, I would always use ISO-8601 format which will work regardless of what current date format is set in SQL Server. … Read more
SSMS Results to Grid – CRLF not preserved in copy/paste – any better techniques?
This issue has been fixed in SSMS 16.5 build 13.0.16000.28 with the addition of an option to preserve CR/LF on copy/save (more details) (Connect bug). Tools > Options Expand Query Results > SQL Server > Results to Grid Tick Retain CR/LF on copy or save Restart SSMS This will cause CR, LF, and CRLF to … Read more
SQL Server: Attach incorrect version 661
SQL Server 2008 databases are version 655. SQL Server 2008 R2 databases are 661. You are trying to attach an 2008 R2 database (v. 661) to an 2008 instance and this is not supported. Once the database has been upgraded to an 2008 R2 version, it cannot be downgraded. You’ll have to either upgrade your … Read more
Using a sort order column in a database table
Update product set order = order+1 where order >= @value changed Though over time you’ll get larger and larger “spaces” in your order but it will still “sort” This will add 1 to the value being changed and every value after it in one statement, but the above statement is still true. larger and larger … Read more
What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
The max size for a column of type NVARCHAR(MAX) is 2 GByte of storage. Since NVARCHAR uses 2 bytes per character, that’s approx. 1 billion characters. Leo Tolstoj’s War and Peace is a 1’440 page book, containing about 600’000 words – so that might be 6 million characters – well rounded up. So you could … Read more
Limitations of SQL Server Express
There are a number of limitations, notably: Constrained to a single CPU (in 2012, this limitation has been changed to “The lesser of one socket or four cores”, so multi-threading is possible) 1GB RAM (Same in 2008/2012) 4GB database size (raised to 10GB in SQL 2008 R2 and SQL 2012) per database http://www.dotnetspider.com/tutorials/SqlServer-Tutorial-158.aspx http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx With … Read more