Anthony Bloesch's Web Log
A WebLog about SQL Server development.
Translate This Page
Translate this page
instead of trigger
SQL Server Modeling Services
Browse by Tags
Anthony Bloesch's Web Log
Tagged Content List
Bulk loading data with IDataReader and SqlBulkCopy
Introduction Often large amounts of data need to be quickly loaded into a database. A common approach is to fill a DataTable and use the SqlBulkCopy class to load the data. The problem with this approach is that the data set must be materialized in memory. This is inefficient because the data must...
23 Jan 2013
Finding and stopping rogue SQL traces
Introduction A common cause of mysterious performance issues are traces that have been left running. Naïve use of traces can leave many traces running—traces that are slowing down the application by consuming critical resources. This happens because the SQL Server Performance Analyzer process...
14 Dec 2012
Implementing uniqueness constraints on large columns
SQL Server uniqueness constraints create an underlying unique index. SQL Server index keys may not be more than 900 bytes. Below I discuss how to implement uniqueness constraints with hash indexes when the key size can exceed 900 bytes and give the results of some tests on the relative performance of...
23 Nov 2009
SQL Server Modeling Services announcement
The code name “Oslo” repository now has the official name “SQL Server Modeling Services”. SQL Server Modeling Services will be a SQL Server workload like SQL Server Reporting Services. Details will be announced at the PDC where the most relevant session is http://microsoftpdc.com/Sessions/SVR19 . You...
19 Nov 2009
Paging SQL Server result sets
Paging through result sets is an approach for reducing the network and client resources used to display large result sets. Essentially, the approach is to load only a page (e.g. 100 rows) of data at a time. It is likely that a user will only want to see a page of data. If they want to see more data then...
9 Nov 2009
Favorite keyboard shortcuts
One of the duties of working at Microsoft is the day-to-day use of pre-beta and pre-release products—we call it eating our own dog food. The idea is to find and fix real-life product issues before customers see them. In honor of the wonderful experience I have had with Windows 7 dog-food, attached are...
24 Mar 2009
SQL Server 2008 error handling best practice
Error handling in SQL Server 2008 needs careful implementation. The Microsoft “Oslo” Repository’s API has the further problem that we cannot mandate the error handling logic in our callers. Thus a stored procedure call could be in a transaction or not and in a try-catch block or not. Below is the pattern...
10 Mar 2009
Testing strings for equality counting trailing spaces
The SQL standard requires that string comparisons, effectively, pad the shorter string with space characters. This leads to the surprising result that N'' ≠ N' ' (the empty string equals a string of one or more space characters) and more generally any string equals another string if they differ only...
20 Feb 2009
Instead of triggers over views (part 1)
Views are useful for creating a business entity based view data while allowing for an efficient logical schema. However, views are normally not updatable--limiting their utility. However, SQL Server’s instead of triggers allow many of these views to be updatable. SQL Server view-based instead of triggers...
16 Feb 2009
Large tuple uniqueness constraints in SQL Server
SQL Server 2008 limits unique constraints to 900 bytes of data per tuple. Here is a technique for enforcing uniqueness over larger tuple sizes. In many cases, the technique is more efficient than SQL Server’s unique constraints and can be used for performance critical cases. Hash index based implementation...
4 Feb 2009
SQL Server fragmentation
Recently we started shredding “Oslo” daily builds into the “Oslo” Repository. We did this to help with internal development but also to get realistic operational data to validate our design and get realistic traces to use for stress and performance testing. We are learning a lot and have found a major...
18 Dec 2008
Hierarchies (trees) in SQL Server 2005
There is much debate about how to implement hierarchies (trees) relationally in SQL Server. I decided to test the main techniques out and see which performed best for my application. My problem is efficiently representing hierarchies in an experimental application code analysis database. The main...
15 Feb 2006
Adding intellisense and refactoring to the T-SQL editor
One of the features I would like to see in our tools for SQL Server is a T-SQL language service. This would give customers features like intellisense, text completion, refactoring (e.g. renaming a table everywhere at once), error highlighting (as in Word’s red squiggly underlining), and host of other...
26 Sep 2005
Page 1 of 1 (13 items)
© 2013 Microsoft Corporation.
Privacy & Cookies