Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
465
questions
1
vote
1answer
115 views
Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions
I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
0
votes
1answer
58 views
SQL Datatypes reccomendations [closed]
I have these columns and their datatypes,
Could someone give me any advice whether they are right, and if not, suggest what is more appropriate?
Thanks
-1
votes
1answer
70 views
What is the optimal data type for columns that retain dates, DATE or INTEGER? [closed]
In my experience, old school thinking tells that, for optimal performance, date data points should be stored as an INTEGERs (e.g., as in 20160101), since they are manipulated faster, accelerate ...
2
votes
2answers
99 views
Is there a cutoff when using datetime and date datatypes in the where clause?
I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997.
I then have a where clause that reads as follows:
WHERE achieved_date between '...
0
votes
1answer
302 views
What is the best sql data type for x.x.x
What is the best data type for field with format like this X.X.X.x
Here is the sample field data
15.8.0
15.8.1
15.8.2
15.8.3
15.8.4
1.8.0
2.8.2
9.8.2
9.8.3
9.8.4
10.8.0
13.15.0.1
13.15.1.1
13.15.2.1
...
-1
votes
1answer
2k views
Can we use Varchar for columns that will save only numbers [closed]
I am doing a "user registration project" and I am newbie in database design.
I studied a lot of tutorials, but I didn't get a solution for this question.
Why there is not one particular datatype for ...
1
vote
0answers
53 views
What type of primary key we need to use for specific length of bits in Cassandra?
I want to know in which situations which type of primary key I need to use.
Situations:
1. If I need 64 bit key than is bigint is the best for it? If I will chose text type will performance will be ...
1
vote
0answers
246 views
will vacuumlo find orphaned objects
A Table (mytable) is having 2 columns
id_ | loid_
(integer) | (oid)
Values are
id_ | loid_
1 | 15001
2 | 15002
Value in loid_ column refers the oid from pg_largeobject. And if I ...
2
votes
1answer
1k views
what is the quickest way to check whether a XML column is empty ( @xml = '') in the where clause?
following the conclusion of the question
how to test if XML = '' in sql server?
to cut a long story short there is a procedure which has one of its parameters a XML variable @ListNoteTypeCode.
It ...
1
vote
1answer
3k views
oid vs bytea in postgres
I have a table called tblA and its having two columns
id_ (numeric 50,0), obj_ oid
For example, tblA having 1 entries
id_ | obj_
1 | 1001
In pg_largeobject table, there are three entries ...
3
votes
1answer
1k views
Where to find the size of SQL Server data types?
This question has already been asked here but none of the answers was really helpful in finding out the size for example, of a XML variable.
declare @myVariable XML
I have learned from this question:...
3
votes
1answer
858 views
Why does a table of varchar(255) columns take up less space than an identical table using the correct data types
I have two tables that are identical and hold the same rows of data.
one uses a varchar(255) for every column of the table, the other is correctly typed for each column (using date, int, decimal etc.)...
1
vote
1answer
90 views
Altering column from INT to BIG INT and fill factor trick
I have inherited a script that rebuilds the fill factor sitting at 90 fill factor on the INT primary key. It rebuilds it at a fill factor of 50. Then the script drops the index, alters the column to a ...
2
votes
2answers
7k views
Biggest possible double/float for a column in my MySQL database
I am developing a PHP application in which I am in need to store values bigger than 1 million, but with the flexibility of having floating points. I know the traditional float and double. I am looking ...
1
vote
1answer
618 views
MySQL. Default length for a field
Is there a list of already set standards for some common fields like: firstName, lastName, email, phone?
I see most people just set varchar(300) to all fields, does it affect performance in any way?
9
votes
3answers
4k views
CONCAT used in INDEX causes ERROR: functions in index expression must be marked IMMUTABLE
I am facing the following error:
ERROR: functions in index expression must be marked IMMUTABLE
When trying to create index like this:
CREATE INDEX full_phone_number ON orders_clientphone (concat(...
1
vote
1answer
322 views
What's the advantage of the inet datatype in Cassandra vs a plain text string?
What is the inet datatype really for? The documentation refers to its usage having something to do with Python but I'm not much of a Python guy so that aspect is beyond me. Is there any advantage to ...
6
votes
1answer
4k views
Function that returns table with an additional column
I want to write a PostgreSQL function that returns a table, plus an additional column. Is there a way to do this without manually specifying RETURNS TABLE (col1 type, col2 type, ...)?
For example, ...
0
votes
2answers
72 views
General Database Optimizations - correct data types - why to avoid NULL? [closed]
Oracle recommends:
Choose the correct data types and size to:
– Avoid NULLs
– Improve performance
– Protect data
– Use data compression where appropriate
I don't understand why I need to avoid NULL. ...
0
votes
1answer
61 views
SQLite function to specify exactly how the data is stored internally
I have used the typeof() function within SQLite before (http://sqlfiddle.com/#!7/fcf0e/1), but I would like to go a little further this time.
I know that INTEGER can be stored internally as 1, 2, 3, ...
0
votes
3answers
1k views
What data type for a column which can have three states: yes, no, unknown
I have an application where the user can decide between:
yes
no
unknown
I started to use a boolean column which is nullable.
But things get quite complicated since my framework (django) does not ...
7
votes
2answers
3k views
Does it make sense to store a couple of Boolean values as array?
I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)
Instead of having Boolean columns, I could have a single array ...
1
vote
2answers
227 views
PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)
Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations:
change timestamp to char(19)
tinyint to ENUM (I noticed it's overzealous with ...
11
votes
2answers
34k views
PostgreSQL: Pass table as argument in function
I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example:
CREATE TYPE dataset AS(
ChannelId INTEGER
,GranulityIdIn INTEGER
,GranulityId ...
-2
votes
1answer
85 views
Why do we have Geometric Datatypes in SQL databases? [closed]
What is the purpose of a specific SDI datatypes in SQL tables?
They could be represented as "(3,1)" (in case of point on a plane) VARCHAR and then retrieved by the application and processes them?
What ...
5
votes
2answers
2k views
PL/pgSQL regclass quoting of table named like keyword
I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new ...
1
vote
0answers
54 views
MySQL table filed with text datatype inserting some script value automatically and ignored user input
In my MY-SQL database table having with text datatype. In that field having some unwanted script tag added automatically. If I given that any value also ignored.
and also I tried to edit that value ...
23
votes
2answers
1k views
Using MAX text or more specific, smaller type
Someone was reviewing my DDL code for creating tables and suggested, when they saw I saw using VARCHAR(256) fields for text I expect to be pretty small, like a first name or whatever, that I should ...
1
vote
2answers
96 views
MySQL data types for common columns
I have been a frontend programmer for a while and moving to making some applications on my own and becoming a full stack engineer. I have worked with plenty of databases for applications and sites but ...
0
votes
1answer
2k views
Why does SQL Server truncate my nvarchar(max) string in an expression?
It looks like a 2nd variable in an expression is determining the maximum length of my 1st variable.
An example is easier to explian:
DECLARE @MyDest AS NVARCHAR(MAX) = 'Some long text, for testing';
...
3
votes
1answer
10k views
Handling sql column with mixed varchar and float data types
We have some Excel spreadsheets with parcel numbers for properties that should be in a standard varchar format similar to:
[Parcel # 1] [Building Address]
038-60-171-16-000 1750 W 4th St
...
1
vote
1answer
7k views
MySQL datetime to just date
Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
0
votes
0answers
28 views
SQLite text columns
When having a table in SQLite that has a TEXT data type, is there any implication if the length of the contained string differ significantly?
11
votes
1answer
2k views
CASE expression returns wrong value when using CEILING
I've run into an issue where a CASE expression does not return what I expect.
As a test, I added a decimal variable and ran the same CASE expression against it and it works fine, returning the ...
1
vote
1answer
2k views
Storing mail in database, data type
I've found pretty poor informations about mail databases.
My need is to archive mail messages downloaded from a normal pop3 or imap account: I am an ordinary user and I do not own the server, but ...
10
votes
3answers
22k views
Get Stored Procedure Result Column Types
I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but ...
4
votes
2answers
163 views
MySQL is trimming my numbers and I can't figure out why
I've got a table with a bunch of decimal(12,2) fields that are supposed to hold money amounts. For some reason which I can't figure out one of them is trimming the value when either inserted or ...
10
votes
1answer
15k views
Convert Postgres TIMESTAMP to TIMESTAMPTZ
I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller.
Stupidly, when I initially designed ...
6
votes
2answers
1k views
Is it worth making a separate DATE table or not?
If I have a table like this:
my_table
=======================
id date some_data
Does it make any sense to pull the date out to it's own table? The date field would use the date data type. So ...
3
votes
2answers
5k views
Unsigned Int datatypes
Does Microsoft SQL Server support unsigned integer data types?
I am trying to learn a bit more about the data type choices and helping a friend in his database project, I know that MySQL does.
1
vote
1answer
2k views
In Rails: How is existing data affected by data type change?
I'm working on a Rails app and would like to change the datatype for an existing column. It's currently a DateTime type, and I want to change it to a Date type. I found a way to do this here, but in ...
0
votes
2answers
4k views
Excel Import ID, Data Type Double
I get a file from another department that contains an id column. The id column is mostly numbers, but also contains some letters.
I receive the file in XLS format.
When I try to use the import ...
2
votes
1answer
656 views
date type implicit conversion
When does oracle implicitly converts date type? on what parameters does it depend?
I am running the following:
SELECT SYSDATE - '01-01-01' FROM DUAL
and got :
SELECT SYSDATE - '01-01-01' FROM DUAL
...
3
votes
2answers
10k views
Which data type should I use to store a 11 digit number [closed]
Which data type I should use to store an 11 digit number? It must store '0' as the first digit.
For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
3
votes
1answer
3k views
Find current owner of custom data type?
In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x.
I know this can be solved by running ALTER TYPE x ...
6
votes
2answers
627 views
Precision of NVARCHAR2 column is 1.5 times lower than displayed by desc command
I wanted to update precision of an nvarchar2 column and came across an interesting case. I have tried to replicate it below:
create table test_table1
(col1 nvarchar2(100));
table TEST_TABLE1 ...
1
vote
1answer
63 views
CASE error with certain values in some params
CREATE PROCEDURE [dbo].[UTILS_CITYVALUES_GET_LIST]
@IndexId BIGINT = -1
,@PageSize INT = 15
,@Ascending BIT = '1'
,@SortAttribute NVARCHAR(20) = N'Municipality'
,@Country NVARCHAR(50) = NULL
,@...
4
votes
1answer
9k views
How to convert _id field of all existing documents from type String to Number
At one of my collections I have an _id field that stores numbers. The field was defined originally in the Mongoose model as String. However, all the data concerning this field in the collection ...
13
votes
4answers
82k views
Datatype for phone number: VARCHAR, INT or BIGINT?
So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition:
Take a look at the column from_number ...
3
votes
0answers
77 views
why some one will use approximate data types for sales
I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
