Best Practice: coding SQL Server triggers for multi-row operations There are many forum posts where people code triggers but these triggers are coded incorrectly because they don’t account for multi-row operations. A trigger fires per batch not per row, if you are lucky you will get an error…if you are not lucky you will not get an error but it might take a while before you notice that you are missing a whole bunch of data
This is an archive of the posts published to LessThanDot from 2008 to 2018, over a decade of useful content. While we're no longer adding new content, we still receive a lot of visitors and wanted to make sure the content didn't disappear forever.
There can only be one clustered index per table because SQL Server stores the data in the table in the order of the clustered index . When you use a UniqueIdentifier as the first column in a clustered index, every time you insert a row in the table, it is almost guaranteed to be inserted in to the middle of the table. SQL server stores data in 8K pages. If a page is full, SQL Server will do a page split, which causes another 8k page to be allocated and half the data from the previous page to be moved to the new page.
I am writing this blog as an introduction to matrixes. In SQL Server 2005, Reporting Services has two main controls, tables and matrixes. Tables, however useful, only expand vertically. Matrixes, however, expand both horizontally and vertically, giving you a different view of data. A good set of data to view in a matrix is Quotes. Usually finance or operation will want to see how much money has been generated in quotes on a quarterly basis, and usually want to compare the quarters.
Post something in this topic http://forum.lessthandot.com/viewtopic.php?f=100&t=8683 (you will need to create an account on this site for that) and I will send you the invitation. No need to post your email, I will abuse my admin powers for that. First in first invited. Rmember Google wave is in Beta. And. Invite others to Google Wave Google Wave is more fun when you have others to wave with, so please nominate people you would like to add.
By definition, primary keys must contain unique data. They are implemented in the database through the use of a unique index. If there is not already a clustered index on the table, then the primary key’s index will be clustered. It’s not always true, but most of the time, you want your primary keys to be clustered because it is usually the key criteria in your requests to the data. This includes join conditions and where clause criteria.
Column names (and table names) should not have spaces or any other invalid characters in them. This is considered bad practice because it requires you to use square brackets around your names. Square brackets make the code harder to read and understand. The query (presented below) will also highlight columns and tables with numbers in the names. Most of the time, when there is a number in a column name, it represents a de-normalized database.
While I may use PHP here at the LessThanDot site, I’ve been using Visual Studio and .Net since the first .Net release (and bribery in the form of scotch may convince me to share some of my pre-.Net experiences as well). While I have tried a few .Net plugins over time, my standard Visual Studio layout is actually only a very lightly customized version of the standard out-of-the-box settings. Perhaps I change the settings for recent projects to display a longer list, add some additional User Task types, and tweak other small settings, but overall my experience is pretty vanilla.
Last week I wrote that SQL Server 2008 R2 Editions pricing has been announced, Microsoft today made availabe for download the SQL Server 2008 R2 November CTP. SQL Server 2008 R2 November CTP is available today for MSDN and TechNet subscribers and it will be available to the general public on November 11th Go to http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx to get more information and to download! The November CTP is “feature complete” so try out the new features in this CTP release which include:
When you use the decimal (or numeric) data type, you should always identity the precision and scale for it. If you do not, the precision defaults to 18, and the scale defaults to 0. When scale is 0, you cannot store fractional numbers. If you do not want to store fractional numbers, then you should use a different data type, like bigint, int, smallint, or tinyint. How to detect this problem:
The class listed below was born as a result of this discussion in UniversalThread Non-standard combobox Thread #1424555 where the idea of using ListBox with MultiSelect property instead of the standard combobox was suggested by Gregory Adam.