Perhaps firing a TextChanged event only when the ‘Enter’ Key is pressed is an oversight by MicroSoft, or maybe they came up with a slick solution that I just don’t know about; but everywhere I look I don’t see an answer that doesn’t involve a little custom JavaScript.  I found some that I thought was the cleanest solution, but it was broken; so I’ve fixed it up and offer it here in the event someone else needs this little jewel.

The following bit of code assumes that you want to fire Click event of a button named “Button1″ when enter is pressed within an ASP.Net TextBox named “TextBox1″.  It should be placed in the Page_Load event (or Page_Init of a Master Page).  You then create and code your button’s click event as desired…  Be sure that the TextBox1 AutoPostBack = FALSE and no OnClick event is defined for it.

Dim js As String = “javascript:if (event.keyCode == 13){__doPostBack(‘” + Button1.ClientID.Replace(“_”, “$”) + “‘,”);}”
TextBox1.Attributes.Add(“OnKeyPress”, js)

The above code hooks into the client side “OnKeyPress” event so each time a key is pressed in the textbox the browser interprets the key.  If the key is the ‘Enter’ key the Javascript function uses the button’s Client ID to mimic the buttons “__dopostback” call, thus causing postback to interpret the “Enter Key” the same as a button click.

In my last blog post on Files and File Groups I left off with the tease,

“You know what would be really amazing?  Being able to automatically move table data between files based on criteria that would help us better manage performance.  Oh yeah, Microsoft did that already!”  

So let me elaborate on the topic of partitions further.  Using multiple files gives us multiple IO threads (and multiple disks and spindles in a non striped disk or SAN environment.).  In this basic form we can use this to gain IO efficiencies by putting tables and indexes in separate files based on the IO demands we expect.  But we can also have multiple files per table, thus allowing us to divide high query or transaction workload for a single table across multiple IO channels.  But in this basic configuration we have very little control over how the data from a single table is divided among the files, so to resolve this Microsoft cleverly invented “partitions”.

Partitions allow us to define rules that tell MSSQL Server 2008 which filegroup (files) each row should be placed on based on column values in each row.  This allows us to distribute data based on how it will most likely be accessed.

Let’s take a scenario where we manage the database of a large e-commerce website.  We want to ensure that our online orders are not hindered by order fulfillment and customer support inquiries.  We have a column on the orderdetail table that tells us if the item is in a cart, waiting for picking, shipping or received.  We can use partitions to move each order detail row to different files based on the status of the order!  This way all of the cart items have one dedicated thread, while items in-process have another, and completed orders have a third.

 Partitions are created in a two step process.  First you define the “Partition Function” that will provide the rules for data distribution.  Then you create a “Partition Scheme” that uses the partition function.  Here is the “Books Online” syntax for each:

In addition to separate threads, consider how this can help us with contingency and snapshots.  Since page locking is the most common isolation level and pages are stored in files, you will not have cart and in-process orders blocked by each other or order history inquiries.  Database Snapshots track changes at the extent level (8 pages), therefore you can keep your snapshot size smaller by arranging the data based on update frequency.

Yet another amazing partition trick is mass movement of data between tables.  So long as the table structures match, data can be moved between tables by assigning the partition from one table to the other.  In our example above, let’s say we have an OrderHistory table that we periodically want to move closed orders to.  Assuming our partitioning plan is in line with this, we can simply move the partition containing the desired data from Order to OrderHistory.  This is a much faster and more efficient approach than a “Select /Insert” or SSIS solution.

 Data is moved using the Alter Table…Switch syntax.  Here is the “Books Online” syntax:

As with most server features few things are absolute “magic bullets”, but Partitions are a great tool to better manage your limited database resources.

Okay, so Files and File Groups are not new to SQL Server 2008, but they need to be understood to take full advantage of some new MSSQL 2008 features. 

 Presumably most of us recognize the value of using different files to put data on multiple disk drives (or spindles) to improve performance.  It’s pretty obvious that the more hard drives we can divide the data search and update across, the better our read/write performance will be.  Yet in a lot of configurations we are writing to striped disk arrays or SANS, so the spindal workload is automatically handled for us.  This has lead to even some of my most tech savvy, enterprise clients to use one data file for very, very, large databases where performance is critical.  Not good…

 The reason multiple files are beneficial even on a SAN or striped disk array is MSSQL creates a separate thread for each file.  Yes, seems obvious now that you think about it, doesn’t it?  A separate thread for each file means that you don’t have to have one busy little thread trying to process all of the IO for the entire database, but that’s exactly what most of us are doing! 

 The first advantage we should take in this regard is to immediately create a second data file after creating a database and make it the default.  When the database is created it will place all of the system tables on the initial file.  Creating a second, default file will place all of the other data files on a separate file unless otherwise specified in the table or index creation.  This configuration gives us a single thread to handle all of the system table IO.

 Next consider moving non-clustered indexes off onto a separate file.  This gives them a separate thread to do index seeks while another thread processes the tables.  The leaf nodes of clustered indexes are actually rows in the indexed table, so an argument could be made that their performance is better when stored in the same file as the table, but I haven’t had the opportunity to benchmark this theory.

 Now consider placing high transaction tables in their own file to allow a single, dedicated thread to process their IO.  Maybe place all rarely used, small, dimension and lookup tables together on a single file to allow another thread to handle the sum of their light work load.  Finally, large query only tables may need their own file so one thread can focus on those queries.

 Also remember that file groups can be restored even while a database is operational.  While there are a lot of data relationship issues to consider related to this feature, multiple file groups help ensure all of our “data eggs” are not in one basket.  Options like these make multiple files a very effective strategy, even in a SAN storage configuration. 

 You know what would be really amazing?  Being able to automatically move table data between files based on criteria that would help us better manage performance.  Oh yeah, Microsoft did that already!  Be sure to check out my next blog post on partitions!

Frankly I have never put a lot of stock in “certifications” of any kind as a measure of someone’s ability to design solutions or resolve problems.  Those attributes are the result of qualities and experiences that simply can’t be written in a book, studied and tested.  That said, there are some individuals and training centers who are dedicated to more than getting candidates certified, and those folks need to be rewarded with additional business. 

While I did not seek a certification with Brian Knight of “Pragmatic Works“, I have to say that his home-grown curriculum and onsite SSIS training was exceptional real-world training.  With regard to certification training I would have to say that Chester Flake who runs “Advanced Custom Training” (He was formerly with CED) has the winning formula.  I have achieved MCITP certifications in MSSQL Business Intelligence and MSSQL Server Administration with ACT in the past couple of months.  The days are packed with explanation, labs and discussion based on students’ real-world needs; while evenings are spent using study resources to prepare you for the exam.  This is intense, 7 day training (class begins on Saturday).

I recently returned from ACT’s SQL Admin bootcamp.  I have worked with MS SQL Server on a daily basis since it was a joint Sybase/Microsoft product running on OS/2, so I know it well from a developer perspective.  Nevertheless, I picked up a number of DBA items that are crucial for developers to know about.  Over the coming months I plan to do brief introduction articles on these items.  Here’s a list of the topics I plan to address:

• File Groups and Performance
• Partitions and Performance
• Snapshots
• Resource Governor
• Policies
• Data Type Selection (memory)
• Full Text Searches
• Table Data Encryption
• DM views
• Data Collection and Performance Data Warehouse 

Like so many other professions, our needs seem to always fall behind those of our customers.  Consequently, our website is just beginning a much needed overhaul, but leveraging the services of sister company Q1 Marketing to give it a cleaner look and to make it easier to maintain on an ongoing basis.

Keep an eye on this blog.  The intention is to add tips, tricks and information that we come across in the field on a regular basis.  It should be interesting over time!

Thanks for stopping by and be sure to contactus@q1technology.com for the best quality IT consulting services.