New SelecTViews Show Posted
SQL Server tips, industry happenings, trends and…check out the new studio!! Take a look at our new digs – more to come on this… interviews today include Eric Johnson and Jeff Garbus – lots of great information on SSIS, SQL Server and much more.
[Watch the show here]
Webcast: SQL Server and SharePoint Data Integration
SharePoint’s Data View web part can do much more than just view data. It can serve as a complete front-end to your SQL Server (and other data store) information allowing you to create robust applications without writing code. In this session, you will see just how to create such an application including conditional graphics. You’ll even learn how to pass data from SQL Server into a SharePoint Designer workflow for business process automation.
Presented by: Ricky Spears
> Register Now
> Live date: 10/6/2010 at 12:00 Pacific
User Defined Types – More of the Same
When SQL Server 2005 came out my interest in User Defined Types (UDT) increased. I was especially impressed with the ability to do CLR UDTs.
I loved the fact that I could now define a Date data type that was not a string, could do date math, and did not have any concept of time. The smallest increment my UDT was for a single day. Of course this is now a native type in SQL Server 2008. It wasn’t hard to move to a Time data type (also now part of SQL Server 2008) that had no concept of date.
With the CLR data types you could also add methods. It was really nice to be able to format dates using an UDT and a more flexible format string than those provided in the CAST method of TSQL.
But can the fact that just about anything can now be stored in your database be a good design decision. I’ve seen UDT designs storing a .Net TimeSpan object, Tuples, and even time segments consisting of a beginning and ending date. Each of these have their purpose. But do they belong in the database as types? What guidelines would you consider valuable when determining if data not native to SQL Server data types should be stored as an UDT?
Drop your comments to btaylor@sswug.org .
While you’re thinking, here is a response from one of our members regarding the use of table type UDTs to solve sets in stored procedures.
Cheers,
Ben
Mark Writes:
User-Defined Types are a huge value for me. I’ve used them occasionally over the years, usually in conjunction with UDFs. But the new “Table” types are absolutely revolutionary for the sproc/app interface when the application needs to pass a list of values to a sproc. We can finally do set processing at the interface between app and db. This is huge.
For such cases in the past may I have used a delimited list of values. For example: Get the current inventory and unit costs for this list of part numbers. In the past I would have passed the list of part numbers as a comma or pipe delimited string. Then my stored procedure would have parsed the list and stored it in a table variable or temp table which in turn would be joined to the inventory and cost tables, getting a major performance benefit from doing this for the whole set of part numbers rather than retrieving inventory level and unit cost per part. But if the list got long, I took a performance hit at the beginning while just converting the list into a table…yuck. But those delimited lists are a thing of the past for me.
I have one mission critical sproc that has 10 table-valued parameters. I’ve tested it with up to a million rows per table and the performance is awesome.
Sometimes It Pays to Review Your Approach
[From Stephen Wynkoop] – We’ve been doing some tuning and optimizing of our own here at SSWUG.ORG – some of it by choice, some of it, well, not so much. Like many sites, we have the pleasure of pretty consistent attempts to hack the SSWUG site and servers. One of the side effects of this is that when someone gets it in their brain to go after the site, often it results in extraordinary traffic to the site. It’s great for optimization and load work. We’ve been going through this of late, with the main page of attack being the home page. With all of the diffferent things hapening there, it was clear that if we could really tweak it to handle the load, it could make a big difference.
Side note: we were seeing peak search volumes exceeding something like 250,000/second for a bit. Great fun.
Our solution? Caching. .NET’s great tools for caching to the rescue. We enabled caching, tweaked some connections and voila! No sweat on the queries.
The net suggestion here is that you take a minute from time to time to review your approach. Apply new things you’ve learned, tweak and tune and see if there are refined approaches that can be applied. Sometimes there are small things you can do to make a big difference. In this case, very, very minimal changes resulted in going from 60-70% CPU to less than 20%. I’ll take it!
Featured White Paper(s)
VIDEO WHITEPAPER – How to Avoid Downtime – VIDEO WHITEPAPER
In this Experts & Insights video, we take a close-up look at the data protection challenges and solutions surrounding downtim… (read more)
Featured Article(s)
Tips for using SQL Server 2008 backup and restore
In this article, you can find some helpful tips to perform backup and restore actions in SQL Server 2008.