70
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 ...
45
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 ...
44
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 ...
answered Jul 25 '12 at 16:10
Erwin Brandstetter
105k11 gold badges223 silver badges354 bronze badges
41
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 ...
39
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 ...
37
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 ...
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.
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 ...
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 ...
26
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
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 ...
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 ...
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 ...
20
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 ...
answered May 27 '14 at 19:19
Erwin Brandstetter
105k11 gold badges223 silver badges354 bronze badges
20
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 ...
answered Aug 27 '14 at 17:36
Erwin Brandstetter
105k11 gold badges223 silver badges354 bronze badges
20
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 ...
20
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 ...
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. (...
18
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 ...
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 ...
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 @...
14
Yes.
If you only have one bit column in the table then storage uses a byte but up to 8 bit columns can be stored in the same byte so the next 7 are "free" in that respect.
There is also a 1 bit per column storage need for the NULL_BITMAP (again rounded up to the next byte). In the data pages this contains a bit for all columns irrespective of whether or ...
Only top voted, non community-wiki answers of a minimum length are eligible
