Specifies the type of data being stored (string, date, numeric, etc).

learn more… | top users | synonyms

3
votes
1answer
933 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 ...
8
votes
3answers
499 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 ...
5
votes
2answers
285 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 ...
2
votes
3answers
135 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 ...
4
votes
1answer
1k 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 ...
0
votes
1answer
43 views

Does a numeric field always use the same disk space?

When you have a numeric field in MySQL(MYISAM) (E.g. Integer) will it always use the same disk space, no matter what number is actually stored?
4
votes
1answer
176 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 ...
3
votes
2answers
2k 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 ...
4
votes
2answers
450 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 ...
4
votes
3answers
2k 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?
7
votes
2answers
2k 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 ...
6
votes
1answer
510 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 ...
6
votes
2answers
467 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 ...
3
votes
2answers
3k 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 ...
0
votes
1answer
279 views

What are commonly used database types? [closed]

There are a lot of database types. Can you list a list of the top classification of databases commonly found in schools and offices? Any help is appreciated. Hope to see compressed and summarized type ...
1
vote
1answer
404 views

Change in datatype?

I have a table on which rows are inserted on hourly basis. The row contains id, installation, product_id and hit_allowed. all of them were mediumint in the beginning. but few days back id reached it ...
8
votes
1answer
409 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 ...
1
vote
3answers
104 views

Is there such thing as automatic ENUMs?

There are some tables that I manage which contain a string value (for example, a 4 word "Type" field). There are only a few DISTINCT strings, however the application may insert new strings that have ...
2
votes
1answer
89 views

Create a Function in sql server without specifying return_data_type

I am trying to create a Scalar-valued function in SQL server, without specifying the return_data_type i want to be able to return any datatype based on a parameter sent by the function caller e.g. ...
5
votes
2answers
2k 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 ...
1
vote
2answers
3k views

In MySQL, how to manually edit values of a BLOB column in few tuples?

In MySQL, how to manually edit values of a BLOB column in few tuples? For convenience of manual curation of database like this, should the column be created as BLOB or TEXT, or something else at the ...
2
votes
2answers
3k views

what is bigger than a longblob?

the longblob is the biggest data type of Mysql. If I want to save something bigger than 4gb (longblob) how can I do?. I'm talking about Mysql v-5.+
1
vote
1answer
3k views

How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?

I'm trying to understand how to compare a DATETIME value (ostensibly inserted as GMT) and a TIMESTAMP value (automatically generated at INSERT time). As I understand it, the DATETIME is set to ...
3
votes
2answers
928 views

What is the best identifier for a UserID? 64 bit integers, UUID V5, or 64 character SHA256 UID?

I wish to future-proof my application. What is the best type of identifier to use? I am considering: 64 bit Integer UUID 64 character SHA256 I am not developing a distributed system, so is ...
2
votes
1answer
4k views

Compare a date and a timestamp with time zone with now() in the same query?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
5
votes
2answers
3k 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 ...
1
vote
1answer
393 views

Rounding of SMALLMONEY Data Type in SQL Server 2005

I'm on SQL Server 2005, and I am trying to figure out a rounding issue with smallmoney data types. For example, the value 1.2849, when rounded to 2 decimal places, produces 1.28, but shouldn't it ...
7
votes
2answers
621 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 ...
3
votes
4answers
912 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 ...
2
votes
1answer
111 views

Can I use a Clob as a table key (from Java)?

Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)? I know it is probably a ...
4
votes
1answer
6k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
10
votes
3answers
1k 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?
4
votes
2answers
2k 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 ...
3
votes
1answer
2k 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 ...
1
vote
1answer
322 views

How to create an UTC index from a 'DATE' typed column with Oracle?

I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone. How do I create an index on the column ...
2
votes
2answers
3k views

How can I define the datatype of a view in MySQL?

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side. Here's a simplified query for the sake ...
1
vote
1answer
3k views

Column size with datatype decimal

According to BOL for SQL Server 2008 R2 the data type decimal requires the following storage bytes: Precision Storage bytes 1 - 9 => 5 10-19 => 9 20-28 => 13 29-38 ...
2
votes
1answer
368 views

MySQL unclearness about DataType TIMESTAMP with DefaultValue CURRENT_TIMESTAMP

In MySQL InnDB engine I created TableColumn with data type TIMESTAMP and put there as default value CURRENT_TIMESTAMP, but this value are always changed to CURRENT_TIMESTAMP when I update that by ...
0
votes
1answer
3k views

work with json in oracle

Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
3
votes
2answers
162 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?
9
votes
2answers
843 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 ...
23
votes
3answers
8k 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 ...
8
votes
2answers
2k 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 ...
4
votes
3answers
4k 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 ...
4
votes
1answer
11k 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 ...
5
votes
2answers
966 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’, ...
1
vote
1answer
324 views

Is it possible to fetch or update table data by table field type?

Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.
2
votes
1answer
436 views

What are the pro's and con's of using PostgreSQL vs MySQL for binary data?

I read that MySQL has binary safe blobs, but PostgreSQL does not. So far what I've found suggests that "binary safe" is something that really relates to functions and handling of data, not the data ...
7
votes
1answer
2k 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 ...
4
votes
0answers
152 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 ...