Specifies the type of data being stored (string, date, numeric, etc).
20
votes
6answers
1k views
In what data type should I store an email address in database?
I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation ...
17
votes
7answers
5k views
How do long columns impact performance and disk usage?
In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.
In reality, how much does it really matter? How ...
15
votes
3answers
4k views
Advantages and Disadvantages to using ENUM vs Integer types?
Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.
Is it better for this column's data type to be an int/bool (1 or zero) or to ...
15
votes
3answers
6k views
What are the drawbacks with using UUID or GUID as a primary key?
I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
14
votes
5answers
3k views
Performance implications of MySQL VARCHAR sizes
Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
13
votes
2answers
405 views
Does varchar size matter in temporary tables?
There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures. Basically, one camp wants to use 255 because it will always work ...
11
votes
6answers
3k views
Storing IP address
I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column?
Should I support IPv6 as well? If so, what is the ...
11
votes
3answers
696 views
Does a SQL Server bit column really use a whole byte worth of space?
I was poking around SSMS and noticed the "size" of my INT columns were 4 bytes (expected) but I was a bit shocked to see my BIT columns were a whole byte.
Did I misunderstand what I was looking at?
11
votes
4answers
611 views
Does NULL have a type?
Various sources (eg Wikipedia, PSOUG) state that Oracle's null does not have a type. Is this true?
What about other RDBMSs?
11
votes
1answer
7k views
INT(5) vs SMALLINT(5)
In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
11
votes
3answers
11k views
What is the difference between MySQL VARCHAR and TEXT data types?
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?
I was reading the list of ...
10
votes
3answers
7k views
What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?
When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000).
Why does Oracle call this ...
8
votes
2answers
469 views
INT or CHAR for a Type Field
What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema:
create table Car
(
Name varchar(100) not null,
Description varchar(100) not ...
7
votes
3answers
475 views
What should I use? A string or 15 integer fields?
I am developing a student tracking program where I need to store 15 exam marks.
I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical ...
7
votes
2answers
1k views
Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?
I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.
As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
7
votes
1answer
275 views
Why does 10^37 / 1 throw an arithmetic overflow error?
Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code:
DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37);
PRINT ...
7
votes
3answers
3k views
MySQL data type for 128 bit integers
I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers.
Right now, I'm using binary(16) but that involves a lot of conversion ...
7
votes
2answers
350 views
What is an appropriate datatype for an RFID, and is there a standard definition for the format?
I have a database that tracks people based on RFID tags embedded in their shoes. This data arrives in a VARCHAR(MAX) column with other data mixed in, in no particular order. So far, all the RFID ...
7
votes
1answer
1k views
Is there such thing as Custom Data Types?
Does MySQL have any support for custom data types? For example, zip codes might be stored in a varchar(10) field, but they could be compacted in to an int, with options for blank, and a flag as to ...
6
votes
2answers
306 views
What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?
My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month ...
6
votes
2answers
622 views
Get records between time span
I would like to retrieve records between time spans,All i have is this snippet of string indicating the time span
9:00 AM - 10:00 AM
below is the table schema
CREATE TABLE [dbo].[Samples](
[RID] ...
5
votes
3answers
112 views
How to pass a table type with an array field to a function in postgresql
i have a table called book
CREATE TABLE book
(
id smallint NOT NULL DEFAULT 0,
bname text,
btype text,
bprices numeric(11,2)[],
CONSTRAINT key PRIMARY KEY (id )
)
and a ...
5
votes
2answers
802 views
Single data type for imprecise date values, as allowed by ISO 8601
How can I store date and time values with reduced precision in a PostgreSQL type, and have them behave as date and/or time values?
ISO 8601 allows date values with reduced precision. ‘1964’, ...
5
votes
2answers
988 views
Data type for storing an array of flags (a bitmap/bit array)
I need to store a bit array for each record of a table, supporting the following operations:
Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ...
5
votes
3answers
170 views
Determining the optimal Column Widths from a .csv File
I want to import a .CSV file into a MySQL Table. I would like to determine the optimal column widths to use based on the actual data contained in the CSV file. Are there any scripts or utilities to ...
4
votes
1answer
8k views
PLS-00306 Error: How to find the wrong argument?
PLS-00306: wrong number or types of arguments in call to 'string'
Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
4
votes
2answers
1k views
Why do I get leading zeros when converting from decimal to char in a db2 query?
I have a column named length defined as:
TYPE_NAME: DECIMAL
DATA_TYPE: 3
COLUMN_SIZE: 9
BUFFER_LENGTH: 11
DECIMAL_DIGITS: 5
NUM_PREC_RADIX: 10
When I run the following select:
select ...
4
votes
1answer
67 views
Data type for a fuzzy date?
I want to store partial information related to a date. I might know the year and month but not the day. I might know the day and month but not the year. I might know the date lies in an open or closed ...
4
votes
1answer
127 views
How to set constraints on UDT in SQL Server 2005 - 2012
I'm creating my UDT:
CREATE TYPE [dbo].[Code]
FROM VARCHAR(20) NOT NULL
As RULES and DEFAULTS are considered deprecated, what is the recommended way to set constraint on my type to make sure that ...
4
votes
1answer
288 views
PostgreSQL data type text vs varchar without length
In PostgreSQL you can create a column with data type character varying (without length precision) or text like this:
ALTER TABLE test ADD COLUMN c1 varchar;
ALTER TABLE test ADD COLUMN c2 text;
Is ...
4
votes
0answers
138 views
Which SQL datatypes exist since when and in which RDBMS? [closed]
I found a general comparision of different SQL implementations that only covers some data types (BOOLEAN and TIMESTAMP) and a comparision of datat type support in different RDBMS from 2009 that does ...
3
votes
3answers
2k views
How to change the datatype of a column from integer to money?
I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error:
cannot cast type MyColumn to money
I have tried these two statements, but just haven't ...
3
votes
2answers
742 views
Mapping SQL Server integer to Oracle Datatype
Well I know, that an SQL Server integer is best represented by NUMBER(10).
But sometimes I have migrate some maintenance scripts to Oracle and I wonder if I must carefully
map integer to NUMBER(10) ...
3
votes
2answers
111 views
Are mime types only for file storage? [closed]
If I have a plain-text field on a web form (not a file upload) inserting its content into my table, would it be wrong for me to set a mime type 'text' to it, or would it be safer?
3
votes
1answer
402 views
Oracle integer to number conversion
According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38). According to the same document, NUMBER(38) is supposed to be a NUMBER ...
3
votes
1answer
2k views
How to insert an IP-address into an inet column in PostgreSQL?
I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
3
votes
3answers
1k views
Estimate large database size and speed
First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following :
+----------+------------------+------+-----+---------+-------+
| Field | ...
3
votes
1answer
235 views
Why does using a Real data type in a mathematical equation return an incorrect result?
I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is ...
3
votes
2answers
305 views
Change column data type across database
I have a bunch of tables with many columns of data type ntext. I wish to change all such columns in all tables of a particular database to nvarchar(max), because of planned deprecation (EDIT: also ...
3
votes
1answer
1k views
Storing XML in varchar(MAX) or use SQL-XML
I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...
3
votes
1answer
458 views
Designing a user authenication (Roles & Rights) module
I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to ...
3
votes
2answers
1k views
Storing prices in SQLite, what data-type to use?
I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store ...
3
votes
1answer
162 views
Does creating an index on BIT(n) attribute in MySQL make sense?
I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2.
I've seen some mentions on the internet that BIT ...
3
votes
2answers
1k views
changing float->numeric casts from assignment to implicit, dangerous?
In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
3
votes
0answers
528 views
SQL Server Decimal(9, 0) vs INT
One of our customers uses for some columns the datatype DECIMAL(18,0) in his SQL Server 2008R2 database. Because the columns grow quite slowly, he recently proposed to change the datatype to ...
2
votes
3answers
636 views
Should I add an arbitrary length limit to VARCHAR columns?
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.
Should I add an arbitrary length limit to a name or address column?
2
votes
2answers
1k views
A Script to insert dummy Data in all tables of Database
I wanted a Script that can loop through all tables and their columns in the database and insert dummy data based on column type and size, so that i can start using the database for testing, and ...
2
votes
1answer
74 views
Oracle Table Design, Storing Small Values
In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm ...
2
votes
3answers
105 views
How to restrict sequence like ID type to C type like int64_t, uint64_t, int32_t?
In C99 I can be pretty clear about the size/domain of an integer type.
When interfacing a SQL database like Oracle or Postgresql from a C program I want to create a table with integer types that ...
2
votes
4answers
736 views
Best pratice to store dates group by months-year key par value
I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
