68
I found a very bizarre but informative article about 8 reasons why one should not use ENUM.
Even without the article, I know
there is no easy method for adding new values, some techniques are very high risk
numbers should never be used
only use strings (@DTest already mentioned this in his answer)
49
This seems to work and keep the precision as well:
SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))
The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment* at CAST and CONVERT page at MSDN.)
* ...
46
I always use CITEXT for email, because an email address is (in practice) case insensitive, i.e. John@Example.com is same as john@example.com.
It is also easier to setup an unique index to prevent duplicates, as compared to text:
-- citext
CREATE TABLE address (
id serial primary key,
email citext UNIQUE,
other_stuff json
);
-- text
CREATE TABLE ...
44
I've always used VARCHAR(320). Here's why. The standard dictates the following limitations:
64 characters for the "local part" (username).
1 character for the @ symbol.
255 characters for the domain name.
Now, some folks will say you need to support more than that. Some folks will also say that you need to support Unicode for domain names (meaning you have ...
42
The answer is no.
Don't add a length modifier to varchar if you can avoid it. Most of the time, you don't actually need a length restriction anyway. Just use text for all character data. Make that varchar (no length modifier) if you need to stay compatible with RDBMS which don't have text.
Performance is almost the same - text is a bit faster in rare ...
40
The data type uuid is perfectly suited for the task. It only occupies 16 bytes as opposed to 37 bytes in RAM for the varchar or text representation. (Or 33 bytes on disk, but the odd number would require padding in many cases to make it 40 bytes effectively.) And the uuid type has some more advantages.
Example:
SELECT md5('Store hash for long string, maybe ...
38
Well, first off we have the storage requirements. I'm going to assume you meant a tinyint (instead of int).
ENUM takes 1 byte (if under 255 values) or 2 bytes (up to maximum of 65,535
TinyInt takes 1 byte (maximum of 255 values)
Boolean is a synonym for TinyInt
So, on the surface, they're all the same. ENUM does take up some metadata for the string value ...
35
No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp table.
35
Custom DOMAINs
I don't think using citext (case-insensitive) is enough[1]. Using PostgreSQL we can create a custom domain which is essentially some defined constraints over a type. We can create a domain for instance over the citext type, or over text.
Using HTML5 type=email spec
Currently the most correct answer to the question what is an e-mail address ...
31
Should I always use (n)varchar(max) for text columns?
No.
For SQL Server, the max data types should only be specified when there is no alternative. One should instead choose the correct base type (varchar or nvarchar) and specify an explicit maximum length that is appropriate to the data to be stored.
Physical storage is identical whether the column is ...
30
There are two ways to typecast in Postgres:
You either do it the SQL standard way:
select cast(3.141593 as bigint);
or you could use the Postgres-specific cast operator: ::
select (3.141593 :: bigint);
You might also want to consider the various rounding functions.
27
If you are already talking about splitting and computing, don't store this as an array.
Regardless of the relational theory and traditional normalization rules and dogma, it's simply a design which gives you MINIMAL flexibility.
Make each exam result a row.
I'm not trying to anticipate everything, but there are a very large number of things which this ...
25
Try using the IF function:
SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, TRUE, FALSE)
FROM ...
or
SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 1, 0)
FROM ...
Even without the IF function, running
mysql> select ('rolando' = 'rolando') str_compare;
+-------------+
| str_compare |
+-------------+
| 1 |
+--...
24
Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100.
In my experience it's standard practice.
23
Data-wise, tinyint(1), tinyint(2), tinyint(3) etc. are all exactly the same. They are all in the range -128 to 127 for SIGNED or 0-255 for UNSIGNED. As other answers noted the number in parenthesis is merely a display width hint.
You might want to note, though, that application=wise things may look different. Here, tinyint(1) can take a special meaning. For ...
23
Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around:
Time zone storage in PostgreSQL timestamps
Ignoring timezones altogether in Rails and ...
22
One more advice - never use GUIDs as part of clustered index.
GUIDs are not sequential, thus if they are part of clustered index, every time you insert new record, database would need to rearrange all its memory pages to find the right place for insertion, in case with int(bigint) auto-increment, it would be just last page.
Now if we look to some db ...
22
Instead of meddling with Martin's answer any further, I'll add the rest of my findings regarding POWER() here.
Hold on to your knickers.
Preamble
First, I present to you exhibit A, the MSDN documentation for POWER():
Syntax
POWER ( float_expression , y )
Arguments
float_expression
Is an expression of type float or of a type that can ...
20
You'd typically use tinyint which is 1 byte too
char(1) will be slightly slower because comparing uses collation
confusion: what is S: SUV or Saloon or Sedan or Sports?
using a letter limits you as you add more types. See last point.
every system I've seen has more then one client eg reporting. The logic of changing V, S into "Van", "SUV" etc will need ...
19
I find ENUM is a short form definition of a code table. Its main advantage is that it avoids the code required to join and show the code description. It also eases setting the values if they arrive in string form.
I find it has the following disadvantages:
No capability for additional metadata about the code.
Difficult to add or disable values. (...
19
Information schema vs. system catalogs
We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information.
The information schema provides standardized views which help with ...
19
libphonenumber
When possible always use the canonical form. The more normalized the form the better. If there is a standard, use it. For this problem, let's use Google's libphonenumber, by proxy of pg-libphonenumber.
CREATE EXTENSION pg_libphonenumber;
This currently installs the phone_number type which has comparison operators and functions. It stores ...
19
For values larger than the INT max (2,147,483,647), you'll want to use COUNT_BIG(*).
SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total]
FROM dbo.t1 AS t
WHERE t.Id > 0
AND t.Id < 101;
If it's happening in the SUM, you need to convert Amount to a BIGINT.
SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM ...
18
Understanding Precision and Scale in the context of Arithmetic Operations
Let's break this down and take a close look at the details of the divide arithmetic operator. This is what MSDN has to say about the result types of the divide operator:
Result Types
Returns the data type of the argument with the higher precedence. For more information, see ...
18
SELECT (ctid::text::point)[0]::bigint AS page_number FROM t;
Your fiddle with my solution.
@bma already hinted something similar in a comment. Here is a ...
Rationale for the type
ctid is of type tid (tuple identifier), called ItemPointer in the C code. Per documentation:
This is the data type of the system column ctid. A tuple ID is a
pair (block ...
17
From the POWER documentation:
Syntax
POWER ( float_expression , y )
Arguments
float_expression
Is an expression of type float or of a type that can be implicitly converted to float.
y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for ...
17
How many bit columns do you have defined in the table? I found this on MSDN, it says 8 or less bit columns are stored as one byte.
http://msdn.microsoft.com/en-us/library/ms177603.aspx
17
What are the SQL Server benefits of using proper data types?
If you use the right datatype your database will better match your model and is more likely to be efficient both in terms of space & speed. You are at risk of this question being closed as "too broad" because it can be quite a wide subject.
To touch your more specific points:
SmallInt/...
16
Numeric/Decimal, Float/Real. There's a great answer here, some official documentation here and here, and I also posted a few opinions ages ago, but here is a quick demonstration:
-- overflow error:
DECLARE @x BIGINT = 9999999999999999999;
GO
-- works:
DECLARE @x DECIMAL(38,0) = 99999999999999999999999999999999999999;
15
But the Trademark(™) and Registered(®) symbols are Unicode characters.
Your are wrong here. Your strings contain only ascii characters.
Here is a simple test that shows you that your characters are all ascii (+ some extended ascii with ascii codes between 128 and 255):
declare @VarcharUnicodeCheck table
(
col1 varchar(100)
)
insert into @...
Only top voted, non community-wiki answers of a minimum length are eligible
