Specifies the type of data being stored (string, date, numeric, etc).
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 ...
2
votes
1answer
64 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
967 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
1k 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 ...
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 ...
15
votes
3answers
5k 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 ...
7
votes
2answers
342 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 ...
2
votes
1answer
2k 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 ...
0
votes
1answer
2k 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
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 | ...
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 ...
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 ...
1
vote
1answer
248 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 ...
2
votes
4answers
733 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
84 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 ...
2
votes
1answer
3k 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 ...
11
votes
3answers
693 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?
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 ...
3
votes
1answer
994 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 ...
0
votes
1answer
2k 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 ...
1
vote
1answer
159 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
1k 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 ...
2
votes
1answer
296 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 ...
8
votes
2answers
461 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 ...
4
votes
1answer
7k 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 ...
3
votes
2answers
109 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?
2
votes
2answers
465 views
PostgreSQL: Separate tables vs single table to perserve disk space?
I have 2 tables with the below schemas with equal number of rows. When I run the SELECT relname, relpages FROM pg_class ORDER BY relpages DESC command, they show up as 23GB each even though the data ...
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 ...
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 ...
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 ...
2
votes
1answer
342 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 ...
4
votes
0answers
137 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 ...
11
votes
4answers
608 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?
1
vote
1answer
3k views
How can I convert from Double Precision to Bigint with PostgreSQL?
I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that?
I have tried with to_bigint(myvalue) but that function didn't exist.
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
2answers
732 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) ...
1
vote
1answer
614 views
How can I alter the type of a money-column to decimal in PostgreSQL?
I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead.
How can I do it?
I tried with:
alter table my_table alter column vat type ...
2
votes
1answer
960 views
Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error)
I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java ...
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 ...
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] ...
