{"id":665,"date":"2005-08-26T07:09:00","date_gmt":"2005-08-26T07:09:00","guid":{"rendered":"\/blogs\/kimberly\/post\/MSDN-Webcast-QA-A-Primer-to-Proper-SQL-Server-Development-Part-3-of-10-.aspx"},"modified":"2013-01-11T23:14:15","modified_gmt":"2013-01-12T07:14:15","slug":"msdn-webcast-qa-a-primer-to-proper-sql-server-development-part-3-of-10","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/msdn-webcast-qa-a-primer-to-proper-sql-server-development-part-3-of-10\/","title":{"rendered":"MSDN Webcast Q&A: A Primer to Proper SQL Server Development, Part 3 of 10"},"content":{"rendered":"

\nMSDN Webcast: A Primer to Proper SQL Server Development
\n<\/span><\/strong>Designing Tables that Scale, Best Practices in Data Types and Initial Table Structures, Part 3 of 10<\/span><\/em><\/strong>\n<\/p>\n

\n<\/span><\/em><\/strong>Presented by<\/span><\/em><\/strong> Kimberly L. Tripp, SQLskills.com<\/span><\/strong>\n<\/p>\n

\n<\/span><\/strong>Q: Can I view a recording of this webcast? <\/span><\/strong>Here’s the specific replay link<\/a>.<\/span>\n<\/p>\n

\n<\/span>Q: Where can we get the demo scripts? <\/span><\/strong>The demo scripts are in this zip (20050819MSDNScripts.zip (3.67 KB)<\/a>); here in this blog entry. However, at the series completion, I will also create an entry under Past Event Resources<\/a> for the entire webcast series.<\/span>\n<\/p>\n

\n<\/span>Q: Does Kimberly L. Tripp have a blog? <\/span><\/strong>Yes, www.SQLskills.com\/blogs\/Kimberly<\/a> and if you’re reading this on my blog…well, then you already know! <\/span>J<\/span><\/span>\n<\/p>\n

\n<\/span><\/span>Q: Where are the links to all prior Webcast Q&As from this series?<\/span><\/strong>\n<\/p>\n

\n

\n\t<\/span><\/strong>Part 1: <\/span>Creating a Recoverable Database
\n\t<\/span>For the MSDN Download for Part 1, click here<\/a>.
\n\t<\/span>For the SQLskills Blog Entry for Part 1, click here<\/a>.<\/span>\n\t<\/p>\n

\n\t<\/span>Part 2: Creating a Reliable and Automated Backup Strategy
\n\t<\/span>For the MSDN Download for Part 2, click here<\/a>.
\n\t<\/span>For the SQLskills Blog Entry for Part 2, click here<\/a>.<\/span>\n\t<\/p>\n<\/blockquote>\n

\n<\/span>Q: <\/span><\/strong>Is this Session 2 of the "Effectively Designing a Scalable and Reliable Database" webcast series? The Title bar here reads "MSDN Webcast: MSDN: A Primer to Proper SQL Server Development (Part…)". A bit misleading don't you think? <\/span><\/strong>So, I finally figured out the mystery here. When I originally submitted my proposal for this series, I titled it like this: Effectively Designing a Scalable and Reliable DatabaseA Database Developer’s Primer to Proper SQL Server Development (Level 200-300) <\/em><\/span>But somehow, through a few edits and since each module also has a more detailed title shown in this following list, then I think the title just got edited down to fit. In fact, with subtitles like these, I can barely fit the entire title, subtitle and session title on a single slide. <\/span>J<\/span><\/span>\n<\/p>\n

\n

\n\t<\/span><\/span>Part 1: Creating a Recoverable Database<\/span>\n\t<\/p>\n

\n\t<\/span>Part 2: Creating a Reliable and Automated Backup Strategy<\/span>\n\t<\/p>\n

\n\t<\/span>Part 3: Designing Tables that Scale – Best Practices in Data Types and Initial Table Structures<\/span>\n\t<\/p>\n<\/blockquote>\n

\nSo, the mystery ends here… It’s all the same content and the general idea is that building a scalable and reliable database takes a variety of best practices that together make it happen; there is no “DBCC MAKEITGOFASTER(‘TRUE’)” option. I'll unveal a new title slide in part 4… oh, the excitement!<\/span>\n<\/p>\n

\n<\/span>\n<\/p>\n

\nQ: How can I replay previous sessions? I thought we were going <\/span><\/strong>to get emails for replaying — but I haven't received any replay emails. <\/span><\/strong>You will receive replay emails ONLY when you register for these sessions through MSDN. We’ve come to find out that there are other ways to register but it’s only through MSDN that we know for sure you will receive the replay information. Regardless, you can always find the “on-demand” version of the sessions here<\/a>.<\/span>\n<\/p>\n

\n<\/span>Q: What does CLR stand for? <\/span><\/strong>Common Language Runtime. Quick WHATIS.COM Definition here<\/a>.<\/span>\n<\/p>\n

\n<\/span>Q: If something uses sp_OA* now in SQL 2000, am I guaranteed it will use SQLCLR in SQL 2005? <\/span><\/strong>Not automatically; however, you should consider rewriting you sp_OA* is used to automate COM objects; these, by definition, are not framework objects. Moving to SQL Server 2005, you can continue to run with your previous sp_OA procedures to access COM objects but if you can write SAFE .NET Frameworks code to do the same thing then you should convert your sp_OA* code where possible. Not everything can be converted but the basic principle is that which you use sp_OA* is a good thing to target for conversion. For some great details on the differences and how to move forward – go here<\/a>. <\/span>Also helpful is this KB Article regarding COM and .NET in SQL Server 2000 <\/span>Using extended stored procedures or SP_OA stored procedures to load CLR in SQL Server is not supported<\/a><\/span>\n<\/p>\n

\n<\/span>Q: I loved how XML data is available interactively in SQL Server Management Studio (SSMS); can HTML data columns be displayed in the same way? <\/span><\/strong>Yes, if you store HTML in a column of type XML then the column data will appear as a link and when clicked, you can edit it in the XML Editor window in SSMS.<\/span>\n<\/p>\n

\n<\/span>Q: Is the "XML" datatype really a "varchar(max)"? <\/span><\/strong>No. In previous releases the XML type really was just a “blob” stored in the database. There were no inherent optimizations and you were not able to define a scheme for an XML column. In SQL Server 2005, XML data is stored natively and offers the ability to put indexes on the columns as well as better access and manipulation. You *can* store XML as varchar(max) but it will be harder to access\/code. There’s a very nice description of the tradeoffs in this whitepaper:<\/span>XML Options in Microsoft SQL Server 2005<\/a><\/span> <\/span><\/strong>\n<\/p>\n

\nQ: What is the difference between numeric and decimal? <\/span><\/strong>In SQL Server they are synonyms. I seem to remember hearing that there was a difference in the standards…somewhere but with a few references and looking around I can’t find any. Regardless, I’d make sure that you standardize on one or the other – just in case they do change, at least all of your data is consistent.<\/span>\n<\/p>\n

\n<\/span>Q: I use decimal datatype for money values. Why it may be suboptimal to use decimal (instead of money datatype)? <\/span><\/strong>I wouldn’t say that it’s suboptimal – except maybe in storage length. However, decimal can be more precise.<\/span>\n<\/p>\n

\n<\/span>Q: When should you use a float? A number of my clients use Float – i.e. Banks, Stocks, etc. What would be your argument against this? <\/span><\/strong>Just the lack of an absolute minimum precision\/scale.<\/span>\n<\/p>\n

\n<\/span>Q: We have a database where datatypes are set. But to use a simple percentage calculation we had to add .0 to the calculation to get the correct percentage. For example 100(5\/9) is truncated to 0, but 100((5+.0)\/9) yields correct result. Is there any standard procedure without massive conversions to assure a correct result? <\/span><\/strong>It’s a matter of data type. In the case of 100, 5 and 9 – these are all integers – without any decimal place. So – the division of 5 divided by 9 yields 0. However, 5 divided by 9.0 or 5.0\/9 – both have a decimal and in that case the other data types are implicitly converted to the higher\/more precise data value. A cool way to find out what the actual “base type” and\/or the level of precision\/scale is to ask these questions as if the data is of type sql_variant:<\/span>\n<\/p>\n

\n

\n\t<\/span>SELECT<\/span> SQL_VARIANT_PROPERTY<\/span>(<\/span>5\/<\/span>9.0,<\/span> 'BaseType'<\/span>)
\n\t<\/span><\/span>SELECT<\/span> SQL_VARIANT_PROPERTY<\/span>(<\/span>5\/<\/span>9.0,<\/span> 'Precision'<\/span>)
\n\t<\/span><\/span>SELECT<\/span> SQL_VARIANT_PROPERTY<\/span>(<\/span>5\/<\/span>9.0,<\/span> 'Scale'<\/span>)<\/span><\/span>   — numeric(8,6)
\n\t<\/span>SELECT<\/span> SQL_VARIANT_PROPERTY<\/span>(<\/span>5\/<\/span>9,<\/span> 'BaseType'<\/span>)
\n\t<\/span><\/span>SELECT<\/span> SQL_VARIANT_PROPERTY<\/span>(<\/span>5\/<\/span>9,<\/span> 'Precision'<\/span>)
\n\t<\/span><\/span>SELECT<\/span> SQL_VARIANT_PROPERTY<\/span>(<\/span>5\/<\/span>9,<\/span> 'Scale'<\/span>)<\/span><\/span>   — int<\/span>\n\t<\/p>\n<\/blockquote>\n

\n<\/span>Q: I am surprised by the nchar\/nvarchar suggestions. I've always been told to NOT use these data types. Can you give the "reasoning" for this suggestion here? <\/span><\/strong>The primary reason that you want to use Unicode data is to avoid a cost in “translation” between the client and the server – when you’re using web-based applications that natively use Unicode. Additionally, with ever growing customer bases – you can more effectively store international data properly by supporting Unicode in the client and in the database. You are correct that these take 2 bytes per character rather than one – but that’s what gives them the larger range of characters to use\/display\/manipulate. Also, if you plan to use the SQLCLR against these columns, you will need to use Unicode as opposed to ASCii as the SQLCLR requires it.<\/span>\n<\/p>\n

\n<\/span>Q: Does Unicode use twice as much space? <\/span><\/strong>Yes, Unicode columns are 2 bytes per character. If you defined a column of type nchar(100), that column will require 200 bytes of storage.<\/span>\n<\/p>\n

\n<\/span>Q: Sometimes, and not always, it becomes a tradeoff in Storage Space v. Performance, doesn’t it? <\/span><\/strong>That’s an excellent point. You always need to look at the trade-offs associated with any decision and in fact, this is yet another. So, I always want the row size to be as optimal and as reasonable as possible. However, there are many compelling reasons – international and\/or web-based applications as well as SQLCLR functionality.<\/span>\n<\/p>\n

\n<\/span>Q: Is the N' keyword usable in MSSQL 2000? <\/span><\/strong>Yes, all Unicode character strings should be preceded with an upper case N in all releases where Unicode is supported (SQL Server 7.0 was the first release that supported Unicode).<\/span>\n<\/p>\n

\n<\/span>Q: Is there any relationship between the OS's code page and nvarchar\/varchar? <\/span><\/strong>Yes. When SQL Server is installed- it chooses the server’s default code page from the Operating System. A code page (or collation) defines the order in which characters are evaluated. The additional attributes a collation can define are: whether or not the characters are case-sensitive; the sensitivity of width and kana-type. This may not seem obvious but if these characters sets are native to you then the order to the data would probably make more sense. <\/span> <\/span>Once the collation is set for the server, the database inherits the server’s collation by default, when tables are created – each character-based column inherits the database’s default collation by default. However, at ANY of these levels the collation can be changed. There are benefits to doing so – in that you can keep data within a specific table ordered for a specific character set. Wow, now that I’m getting started, this could be a session in and of itself.<\/em><\/span> <\/span>So, just to give you a few tips:<\/span>\n<\/p>\n