Questions tagged [datatypes]
Specifies the type of data being stored (string, date, numeric, etc).
8
votes
2answers
670 views
how to create a data type and make it available in all Databases?
If I create a stored procedure in the master database, and I want to execute it from any of my databases I just follow this link:
Making a Procedure Available in all Databases
that give me this code ...
1
vote
1answer
68 views
How is TINYINT(1) converted to BOOL/BOOLEAN?
I can understand that if you UPDATE a TINYINT(1) column and pass a value of 1 or 0 it will convert it to TRUE or FALSE, respectively.
I'm just confused on what happens when you UPDATE the column ...
5
votes
2answers
275 views
Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries?
Consider this table:
create table Books
(
Id bigint not null primary key identity(1, 1),
UniqueToken varchar(100) not null,
[Text] nvarchar(max) not null
)
Let's imagine that we have ...
2
votes
1answer
46 views
Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan
Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan
-- create objects
CREATE DATABASE ConvertTest
GO
USE ConvertTest
GO
CREATE ...
-1
votes
1answer
43 views
MySQL - Optimize a table which has mostly text data type
I recently faced some performance issue on a CMS based application. I can somehow fix other tables. But Im not able to optimize the table which has 40+ columns and 10 columns are having text as the ...
3
votes
1answer
42 views
Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes
I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash.
For example the hash of test is
...
0
votes
1answer
54 views
Appropriate column type(s) for dates and dynamic pricing data
What is the appropriate column(s) type(s) (trying to be database agnostic, but realize that could play into the answer) to store a months worth of pricing data (i.e. could be the daily hotel rate, etc....
1
vote
1answer
37 views
Datetime hourly precision
What would be the best to store date + hour + timezone in SQL? i dont need a higher precision (minutes, seconds, ms..) the primary reason for this is to reduce memory use and calculation time on big ...
1
vote
1answer
28 views
Different categories of missings
Most database systems can represent missing values, typically as "Null" or something similar. But what if I want to represent different categories of missing data?
For categorical data, this is ...
4
votes
2answers
167 views
Different syntax to add/substract interval
Till now I used the following syntax to add an interval to a timestamp:
select now() + '5 year';
This worked fine till I tried to subtract the interval which results in a syntax error.
invalid ...
0
votes
2answers
189 views
Postgres jsonb vs composite type performance differences
What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?
For example, consider a column like that used in the Postgres documentation:
...
0
votes
2answers
100 views
best column type for hashed data in mysql
In my use case I have a table with a column containing hashed (sha256) IDs. I wonder which data type is better considering that It must be unique and I need to search on it.
The sha algorithm ...
2
votes
0answers
63 views
Non-standard number data types in Oracle
I'm not sure that this question belongs to this StackExchange.
I've asked this question on StackOverflow:
Besides the "usual" number data type where precision is greater then scale there are ...
-1
votes
1answer
21 views
Creating blog fresh tables from old tables [closed]
I am re-creating a database structure from old tables. New tables should be optimized and easy to understand.
In old database the developer used video table name for storing video blogs. However I ...
2
votes
0answers
35 views
How costly is changing column datatype in PostgreSQL?
I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
1
vote
1answer
50 views
Why does ENCODE on BYTEA not seem to have any performance impact?
I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64).
Initial thought was to enforce a valid hex string storing it ...
1
vote
3answers
93 views
Find max content length of column of particular table from entire SQL server
I have a 3 SQL server: Server1,Server2 and Server3.
Each search has approx 50-60 databases.
In a particular database there is a table name table1 and it has a column named column1 with datatype ...
3
votes
1answer
96 views
Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?
What is the Datatype for a Persisted Computed Column?
(a) Does SQL Server automatically infer it?
(b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...
1
vote
0answers
40 views
Used a CAST function ,still IMPLICIT Conversion warning in Execution Plan
I know that IMPLICIT Conversion warnings occur when the SQL server has to convert a value to a another datatype to do some processing.
I am getting this warning in an execution plan and when i checked ...
2
votes
1answer
45 views
Converting a string to an object reference
Sometimes I end up in a position where I have a string version of my object reference like so:
'server.database.schema.table'
And I need to use it as an actual object reference:
'server.database....
5
votes
1answer
110 views
Question regarding decimal arithmetic
I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while ...
9
votes
2answers
947 views
When `nvarchar/nchar` is going to be used with SQL Server 2019?
With SQL Server 2019 Microsoft introduces UTF-8 support for CHAR and VARCHAR data types and says:
This feature may provide significant storage savings, depending on the
character set in use. For ...
6
votes
2answers
130 views
Is there a way to partially initialize a TYPE?
I have created a custom type:
CREATE TYPE my_type as(name text, street text, location text);
Initializing such a type is easy:
SELECT ROW('a', 'b', 'c')::my_type;
I am wondering if there is an ...
4
votes
1answer
745 views
What is the data type 'name' in PostgreSQL
I can not find any references to the data type 'name' in the postgres documentation but I am seeing it as a data type on the pgagent.pga_jobstep table for the column 'jstdbname'. The udt_name is also ...
0
votes
2answers
3k views
Converting NVARCHAR to VARCHAR
I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns so I wanted to look ...
0
votes
1answer
30 views
Which column type for “direction”?
I want to create a table which has a column named "direction".
This column is either "in" or "out". It is not nullable and it can only contain these two values. Never other values.
Which data type ...
1
vote
0answers
172 views
Update BIT field with non-BIT value
I was trying (by a mistake) to update bit typed field with non-bit value. It took me some time to realize the cause for not getting this field updated (non-bit value). I was supprised that no native ...
0
votes
1answer
60 views
Comparing SQL binary data types
I am curious if there are any major differences with performance and/or conventionality regarding which binary data types one chooses. Let's say I have a table of metallic objects and I am considering ...
1
vote
0answers
63 views
Access Field Data Type Changed
I have several MS Access databases with tables containing fields that were set to the Data Type "Double" with decimal places set to "Auto"
Access somehow changed all these fields to the Data Type "...
1
vote
2answers
109 views
What is the best smallest data type size for 19 digit number?
I'm using this code to get a mix of Date and Time to use it like a unique ID
DateTime.Now.ToString("yyyyMMddHHmmssfffff")
In SQL Server 2017 I'm confused as to which best smallest size of data type ...
2
votes
1answer
84 views
Aggregate additional values to min and max
This is a follow-up question to:
Make custom aggregate function easier to use (accept more input types without creating variants)
This SQL statement is given:
SELECT
c.name AS commodity_name,
...
3
votes
1answer
517 views
Convert varchar column to inet
I have a table column containing IP addresses, datatype is varchar(255). I want to switch to inet datatype now. Is there a way to convert existing addresses in IP column to inet datatype? Tried:
...
3
votes
2answers
63 views
Make custom aggregate function easier to use (accept more input types without creating variants)
Recently I wrote a custom aggregate function in postgres that would return a specific column for the row that matches the max/min aggregate using a different column.
While the code in itself works ...
3
votes
2answers
124 views
Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle
I've been working with Oracle 11g for about 7 years and I was asked for a consulting job on a small project.
The problem they had was high CPU usage (more than 90%) on a 11gR2 with Windows2008 as ...
1
vote
1answer
1k views
Assigning empty string to INT NULL field in MySQL
Point me a reference what happens if empty string ('') is assigned to a INT NULL field.
Does it become NULL or 0?
Do all versions (starting from 5) of MySQL do the same?
2
votes
2answers
1k views
How to use default value of data type as column default?
How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago:
CREATE TABLE test
(
col_1 CHAR(12) NOT NULL,
col_2 INTEGER NOT NULL ...
3
votes
1answer
676 views
Use array_position() function to get the most frequent value for column from pg_stats
I'm trying to execute this simple query in order to check whether some value (1000) belongs to MCV list used by Postgres query optimizer:
SELECT array_position(most_common_vals, 1000)
FROM pg_stats
...
2
votes
1answer
54 views
Return both one value and one column from pl/pgsql function
I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection:
one vector containing data whose type is unknown at call time (an array)
one string value ...
-2
votes
1answer
39 views
What is TRANSFORM FOR TYPE?
PostgreSQL 11 introduces TRANSFORM FOR TYPE, from these docs by HP, PostgreSQL 11 New_Features
postgres=# CREATE EXTENSION jsonb_plperl CASCADE ;
NOTICE: installing required extension "plperl"
CREATE ...
2
votes
1answer
185 views
Use of ANY function for multiple values
I am trying to use the ANY function of PostgreSQL to search the value from array interger type column.
My SQL:
SELECT
*
FROM
company_employee_contacts
WHERE
corporate_complaint_type_ids ...
-1
votes
1answer
155 views
'INTERVAL' is not a recognized ODBC date/time extension option
According to this docs on Interval Literals, this should be a valid INTERVAL,
SELECT {INTERVAL '326' YEAR(4)};
But when I try that on dbfiddle, I get,
Msg 155 Level 15 State 1 Line 1
'INTERVAL' is ...
1
vote
1answer
4k views
How do you index a text column in MySQL?
If I create a table with a text column
CREATE TABLE foo ( bar text );
I can't index the bar column.
MariaDB [test]> CREATE INDEX foo_bar_idx ON foo ( bar );
ERROR 1170 (42000): BLOB/TEXT column ...
-1
votes
1answer
38 views
Text storage for mysql
So I want to create a command like first_name VARCHAR(30) NOT NULL, ... however I want the database to hold more characters like a text storage type. How would I set up the command for the text type?
1
vote
1answer
82 views
Latitude & longitude universal format [duplicate]
I don't know yet what database I'll use in the future - MySql, Postgresql, Sqlite3 or anything else. At the moment I'm using a database N.
What's more or less universal approach for storing latitude &...
0
votes
1answer
47 views
Does large VARCHAR take up more room in the transaction log
On SQL Server, would VARCHAR(500) take up more space in the transaction log than VARCHAR(100) when all the rows have no more than 100 characters?
1
vote
1answer
71 views
How does the SQL Spec specify literals with a decimal should be typed?
Different databases implicitly type numeric literals differently,
MySQL types 1.0 as decimal(2,1)
PostgreSQL types 1.0 as numeric
Which one of these is correct?
0
votes
1answer
215 views
Does MySQL or MariaDB support double literals (double precision)?
MySQL seems to support double precision
The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision ...
0
votes
2answers
427 views
Best SQL datatype for JSON
In SQL Server 2016, I believe the best datatype for handling JSON is nvarchar.
Most of our JSON strings will not use more than 100 characters.
Should I use nvarchar(100) or nvarchar(max)?
We prefer ...
1
vote
2answers
685 views
Optimizing query with type casting
I had a query that was taking way too much time despite having all the indexes I could think of.
Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
3
votes
2answers
757 views
Passing value of datatype interval in parametrized query
The context is connecting to a Postgres db from a rest server.
To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is ...
