Tag Info

Hot answers tagged

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 ...


22

No, they don't represent the same size that is allocated for the field type. An int can be between -2147483648 and 2147483647 signed, or 0 and 4294967295 unsigned. A smallint is between -32768 and 32767 signed, or 0 and 65535 unsigned. The (5) represents the display width of the field. From the manual, it states: The display width does not constrain ...


21

Currently, the two are synonymous. VARCHAR is an ANSI standard data type but Oracle's implementation of the VARCHAR data type violates the ANSI standard by considering the empty string to be NULL (Oracle's implementation predates the ANSI standard). As Leigh points out, Oracle has stated that the semantics of the VARCHAR data type may change in the future ...


21

It would seem that Oracle at one time had plans to give a different definition to VARCHAR than to VARCHAR2. It has told customers this and recommends against using VARCHAR. Whatever their plans were, as of 11.2.0.2 VARCHAR is identical to VARCHAR2. Here is what the SQL Language Reference 11g Release 2 says: Do not use the VARCHAR data type. Use the ...


19

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)


19

Don't store as a string. Use an int unsigned column and store/retrieve with INET_ATON() and INET_NTOA() respectively. AFAIK mysql doesn't support INET_* for ipv6. EDIT as per comment Using built in function to converto IPs to/from integers (and so storing those integers in the database) has the side effect of automatically validate those IPs. Say you store ...


19

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 ...


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 ...


16

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 ...


15

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 ...


13

It depends on your generation function and size of the final tables GUIDs are intended to be globally unique identifiers. As discussed in the Postgres 8.3 documentation there are no methodologies that are universally appropriate to generate these identifiers, but postgreSQL does ship with a few more useful candidates. From the scope of your problem, and ...


12

For reporting purposes splitting the field out into date and time has some benefits. Some possible benefits you could realise include: You can make a date reference table (much the same as a date dimension in a data warehouse) with your breakdown into weeks, months etc. This can be keyed on the date and used with a join. Analysis by time of day is easier ...


12

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.


12

For scores, performance-wise, the clear winner is storing it numerically something like this; create table test_scores ( student_id int, test_id int, score int ); Its easy to query, easy to update and add on, and super easy and fast to perform aggregates on. Given the choice of "store this information as a string that I have to split up" or "store ...


12

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 ...


12

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.) ...


11

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.


11

divided linked to some info that explains the basic issue (there's performance differences), but it's not simple enough to say that one's always better than the other. (otherwise, there'd be no reason to have both.) Also, in MyISM, the 64k max size for VARCHAR isn't per field -- it's per record. Basically, there's 4 ways to store strings in database ...


10

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 ...


10

It seems that despite the implication in BOL that the left hand operand will be implicitly cast to float that this is not the case. The output of POWER() is cast to the type of the left hand operand, which is DECIMAL if you use 10.0. Using an explicit float works fine. SELECT POWER(1e1, 38); SELECT POWER(CAST(10 as float), 38.0);


9

I don't know what the best way necessarily is to store it -- but there's at least a better option than using a varchar(39) (or varchar(40) if you needed it signed) ; instead use a decimal(39,0). From the mysql docs: Fixed-Point (Exact-Value) Types The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is ...


9

Here is a good starting point for you. http://www.sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx I may have misunderstood your original question. Let me see if I can find you a few other links for reference. Here is good reference on data type selections: http://sqlfool.com/2009/05/performance-considerations-of-data-types/ Changing from ...


9

You must realize the tradeoffs of using CHAR vs VARCHAR With CHAR fields, what you allocate is exactly what you get. For example, CHAR(15) allocates and stores 15 bytes, no matter how characters you place in the field. String manipulation is simple and straightforward since the size of the data field is totally predictable. With VARCHAR fields, you get a ...


9

The specific answer to your question (at least for Oracle and probably other databases) is that the length of the field doesn’t matter, only the length of the data. However, this shouldn’t be used as a determining factor concerning whether to set the field to its maximum allowable length or not. Here are some other issues you should consider before maxing ...


9

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


9

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 ...


8

The answer to this is actually rather complex. The short version: there is a difference. When creating temporary tables to filter results (e.g. GROUP BY statements), the full length will be allocated. The wire protocol (sending rows to the client) will likely allocate the larger length. The storage engine may/may not implement a proper varchar. For (2) ...


8

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.


8

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 ...


8

There are lots of cases that @AmmarR's solution doesn't handle - ROWVERSION/TIMESTAMP, computed columns, columns with check constraints, foreign keys, UNIQUEIDENTIFIER columns that default to NEWSEQUENTIALID(), date/time columns that default to GETDATE(), sparse columns, etc. etc. Why reinvent the wheel? There are tools out there that can generate data for ...



Only top voted, non community-wiki answers of a minimum length are eligible