There is a schema called INFORMATION_SCHEMA schema which contains a set of views on tables from the SYS schema that you can query to get what you want. A major upside of the INFORMATION_SCHEMA is that the object names are very query friendly and user readable. The downside of the INFORMATION_SCHEMA is that you have … Read more
From MSDN: Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. … Read more
YES ! A page is the most basic element of storage in SQL Server. Of the 8192 bytes on a page, approx. 8060 are available to you as a user. If you can manage to fit your data rows onto the page nicely, they’ll take up a lot less storage. If your data row e.g. … Read more
Just make a call to RETURN: IF EXISTS (<some condition>) BEGIN // NEED TO STOP STORED PROCEDURE EXECUTION RETURN END This will return the control back to the caller immediately – it skips everything else in the proc.
Update(column) merely states that column participated in update, but not that its value has changed. For instance, update Person SET Name = Name yields true in update(name) even though Name has not been changed in any row. To check if new values differ from old ones, you would employ except because except will remove rows … Read more
sp_helptext works to get the sql that makes up a trigger. The text column in the syscomments view also contains the sql used for object creation.
On SQL Server 2005 and up, you can use something like this: ;WITH TableRows AS ( SELECT SUM(row_count) AS [RowCount], OBJECT_NAME(OBJECT_ID) AS TableName FROM sys.dm_db_partition_stats WHERE index_id = 0 OR index_id = 1 GROUP BY OBJECT_ID ) SELECT * FROM TableRows WHERE [RowCount] = 0 The inner select in the CTE (Common Table Expression) calculates … Read more
From the SQL Server Management Studio you can right click on your database and select: Tasks -> Generate Scripts – Next -> Select objects that you need from list -> click on the advanced Then simply proceed through the wizard. Make sure to set ‘Script Data’ to TRUE when prompted to choose the script options. … Read more
Separate table, normalized Not as XML or json , but separate numbers in separate rows No matter what you think, it’s the best way. You can thank me later
A bit later response but may help. In SQL 2016 MS introduced function DATEDIFF_BIG which will (according to type size) overflow in difference bigger than something like 290k years. But technet article have same time difference as basic DATEDIFF – https://msdn.microsoft.com/en-us/library/mt628058.aspx