 | A community of more than 1,600,000 database professionals and growing |
| | | The Number that shouldn't be a number This editorial was originally published on Feb 2, 2015. It is being republished as Steve is traveling. Sometimes I get really confused by the data types people choose for their tables. Take this example from one of the popular SQL forums:  Why, why, why did they choose an integer for the telephone number? Sure, it's probably ultimately numeric, but does that really mean it should be an integer? Is it likely that the phone number needs to be added to something? How often do need to find the square root of a phone number? Or invert one? Is one ever likely to need to do something like this: log2(sin(TelephoneNumber2)/pi)? If so, I'd certainly love to see the reason. Telephone numbers, despite being composed of numeric digits, are not numbers. They are strings. They are not likely to be manipulated mathematically, and leading zeros are meaningful. I've seen similar strange choices with postal codes, which in South Africa are four digits long, and where leading zeros are, again, meaningful. Cape Town's postal code is 0001, not 1. Storing a postcode in a SMALLINT is possible, but not necessarily a good option. My general rule is that if the column isn't going to be mathematically manipulated, it's probably a string, not a number. I remember the pain of working with a system where a developer had chosen the TIME data type to store the number of hours worked in a day. Anyone want to figure out how you add two TIME columns together? I'll wait… I've also seen BIGINT used for the number of open orders that a customer has (optimistic), INT for dates, FLOAT for dates, NVARCHAR(MAX) for a true/false column. The list is endless. Here is my call to arms: if you're designing a table, think about the domains for each of the attributes, think about what the maximum and minimum values can be, and then think very carefully about the best data type for that attribute. And don't get fancy. For financial data, while it might be possible to convert the value into hex and store it in a Binary column, storing it in a Numeric is probably easier. Finally, what are the weirdest data type choices you've seen? Gail Shaw (Guest Editor). Gail Shaw from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
|
| | ADVERTISEMENT |  | | Year in review with Kendra, Steve, Grant and Kathi: The best of 2018 and our predictions for 2019 Tuesday 18 December, 16.00-17.00 GMT / 10.00-11.00 CST - Join Microsoft Data Platform MVPs Kendra Little, Steve Jones, Kathi Kellenberger and Grant Fritchey live to discuss the highlights they’ve seen in 2018 and what cool things they hope to be surprised with in 2019. Along the way they'll share their own personal moments of glory, and favorite goofy memories as well.
Register now |
|  | | Benchmark your Database DevOps maturity level Get a better understanding of how advanced your current processes are, receive recommendations for improvements, and see how your maturity level compares with that of your peers. Complete the Database DevOps Maturity Assessment |
|
|
|
| | |  | Bill Brightman from SQLServerCentral.com Use native SQL Server backups with Amazon RDS databases to easily import and export data between local server and the AWS cloud. More » |
 | Additional Articles from SimpleTalk Now that SQL Server 2019 is on the way, it’s time to start learning about the new capabilities. In this article, Greg Larson tests the new APPROX_COUNT_DISTINCT function for performance and accuracy. More » |
 | This whitepaper discusses the importance of data catalogs, the benefits they bring to businesses, and the capabilities and features required. It also outlines how data catalogs help organisations comply with data privacy regulations such as HIPAA, SOX and the GDPR More » |
 | From the SQLServerCentral Blogs - TDE and DDMSteve Jones from SQLServerCentral BlogsSomeone asked a question about TDE (Transparent Data Encryption) and DDM (Dynamic Data Masking), which are two different technologies that... More » |
| | SQLEspresso from SQLServerCentral Blogs SQL Server 2019 brings a very exciting new feature that, is long overdue. Resumable online index create is one of... More » |
|
|
| | | Today's Question (by Kendra.Little): Which of the following is TRUE about temporary tables used in stored procedures? |
Think you know the answer? Click here, and find out if you are right.
We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Temporary Tables. We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center. |
|
|
| |
| ADVERTISEMENT |  Extract, Transform, and Load with SQL Server Integration Services: With Microsoft SQL Server, Oracle, and IBM DB2 Readers will learn by example how to use Microsoft SQL Server 2016 Integration Services (SSIS) as they design and implement extract, transform, and load (ETL) solutions to support a business application that integrates with a data warehouse and an online Web store across a heterogeneous system. This volume describes proven methods to support common ETL needs, such as databases communicating directly with each other, using files to exchange data, or performing database operations using Web services. Pick up your copy of this great book today at Amazon today. |
|
|
|
|
|
| Yesterday's Question of the Day |
| Yesterday's Question (by Steve Jones): In Python, the range() function is used to create a list of number progressions. There are three arguments. The frist is the starting value, the second is the ending value, and last is the step progression. We can set a range like this: ctr = range(1, 10, 2) Which of these arguments are optional?
Answer: The 1st and 3rd arguments are optional Explanation: In this function, the end (2nd) argument is required. The first and third are optional. Ref: Range() - click here
» Discuss this question and answer on the forums |
|
|
| Database Pros Who Need Your Help |
| Here's a few of the new posts today on the forums. To see more, visit the forums.
Very strange execution plan estimated rows was really large - Just curios about following execution plan, one nested loop with two input (one estimated rows:3381 and another estimated rows:1) comes... Login sa failed - Hi All, Please advise, Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. . Is some...
SQL Quiz - Here's the challenge: Let's say we had a table of all US Presidents, with the date they took office and...
Looking to downgrade our SQL Server editions from Enterprise to Standard Edition - We're looking to downgrade our SQL Server editions from Enterprise to Standard. Contacted our vendors, they all signed off they... Logical Drives on a Solid State Platter - In the olden days we meticulously separated the system files from data files from log files and from tempdb. So...
Stumped trying to shred XML data into table rows - Hello experts, I have the following table: MyTable ( MyKey varchar(100) not null primary key, ColXml varchar(max) null ) The ColXml column has xml data...
SQL Server Log history of shrink events - Is there a way to look at the complete history of shrink events for the last month on SQL Server... Stuck Parallel Query - I've never seen this before, and I have no idea what to do (other then kill the SPID). Third party vendor...
Case when inside of partition by? - Hi, I have the following situation: CREATE TABLE #TAB1( ID INT ,MY_STATE VARCHAR(10) ,EVENT_TS DATETIME ) INSERT INTO #TAB1(ID, MY_STATE, EVENT_TS) VALUES (1, 'STATE_1', GETDATE()) ...
Case statement in where condtiions - is it possible to use CASE statement or any other option in below where conditions should be execute when the variables(@LAN,@SUBFORMAT,@CASNUM)... Basic Question: Do you have to restart the instance if you change database or log file growth - Hi All, I have changed the default autogrowth to 300000MB. Do I need to restart the server instance in order...
How to split comma separated values stored in XML node and display as individual records Without using function- SQL Server 2012 - I hav How can I combine the three queries to one and possibly improve performance - I wish I can get help from this forum, any suggestion is appreciated. I have a project with some existing sql...
Need help using a variable in the following statement - I have the following statement and I need to use a variable like so: declare @WhereClause varchar(max) set @WhereClause = 'customer_code = ' + CHAR(39) + 'XXX12365'... Script Out Database Mail Settings? - Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings? I set up a...
How to break multiple pages in ssrs charts - Hi All, My line chart is getting big when user enters data.so how it can be split multiple pages dynamical(like...
Get all Attributes from this XML - Hi, how can i get a Resultlist (see below) from this XML? ------------------------------------------------------ DECLARE @x XML; SET @x = N' <SessionStart xsi:noNamespaceSchemaLocation="http://www.itx.cc/psstart.xsd" sessionId="200" dateTime="2009-11-25T10:04:13.160" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> ...
SSIS Design Patterns and examples - Dear Forum, I was looking for a source / reference for SSIS Design Patterns and examples. e.g. some common designs for clone / re-use...
Using Hash values as Dimension Keys in Datawarehouse - All We are exploring the idea of using a hash value as the surrogate key in Dimensions. Eg , Lets say we...
Do recruiters primarily recruit for contract jobs? - I live in New Mexico (yes, we're part of the USA, despite opinions to the contrary). New Mexico is primarily... |
|
| This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
| This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
| This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com |
|
|