SQL Server doesn’t have a way to define a default value for UPDATE. So you need to add a column with default value for inserting: ADD modstamp DATETIME2 NULL DEFAULT GETDATE() And add a trigger on that table: CREATE TRIGGER tgr_modstamp ON **TABLENAME** AFTER UPDATE AS UPDATE **TABLENAME** SET ModStamp = GETDATE() WHERE **ID** IN … Read more
This will work in SQL Server: ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N’SANDNES’ FOR CityBorn;
You need CROSS APPLY here, it can refer to outer references, no annoying subqueries or CTEs needed: select col1, col2 from table1 as outer_table — can also have multi-row values cross apply (values (complex_expression_1) ) as v1 (col1) cross apply (values (expression_referring_to_col1) ) as v2 (col2) — alternate syntax, select without from returns a single … Read more
Maintaining compatibility with Lotus 1-2-3 back in the day, which had a bug in that it thought the year 1900 was a leap year (or pretended?). The explanation is too long to quote, but for the sake of curiosity, here are some snippets. http://blogs.msdn.com/b/ericlippert/archive/2003/09/16/53013.aspx http://www.joelonsoftware.com/items/2006/06/16.html 1900 wasn’t a leap year. “It’s a bug in Excel!” … Read more
This is a duplicate of SO# 19412. Here’s the answer I gave there: select top 1 * from mytable order by newid() In SQL Server 2005 and up, you can use TABLESAMPLE to get a random sample that’s repeatable: SELECT FirstName, LastName FROM Contact TABLESAMPLE (1 ROWS) ;
A trusted connection is the same thing as using Windows Authentication in SQL Server 2005. Authentication is done by the domain, and authorization is handled by SQL Server. SQL Server can also use its own logins, such as the sa user. These are both authenticated and authorized by SQL Server. They are only viable if … Read more
Remember that there are errors that TRY-CATCH will not capture with or without XACT_ABORT. However, SET XACT_ABORT ON does not affect trapping of errors. It does guarantee that any transaction is rolled back / doomed though. When “OFF”, then you still have the choice of commit or rollback (subject to xact_state). This is the main … Read more
Just for completeness – in SQL 2008 you would use the plus + operator to perform string concatenation. Take a look at the MSDN reference with sample code. Starting with SQL 2012, you may wish to use the new CONCAT function.
varchars and equality are thorny in TSQL. The LEN function says: Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks. You need to use DATALENGTH to get a true byte count of the data in question. If you have unicode data, note that the value … Read more
My solution (Guids is a list of ids you would like to filter by): List<MyTestEntity> result = new List<MyTestEntity>(); for(int i = 0; i < Math.Ceiling((double)Guids.Count / 2000); i++) { var nextGuids = Guids.Skip(i * 2000).Take(2000); result.AddRange(db.Tests.Where(x => nextGuids.Contains(x.Id))); } this.DataContext = result;