Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [table]

A data structure that organizes information into rows and columns.

Filter by
Sorted by
Tagged with
103 votes
18 answers
43k views

Is adding the ‘tbl’ prefix to table names really a problem?

I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’. On the internet I found some blogs saying it adds nothing to documentation,...
Racer SQL's user avatar
  • 7,386
41 votes
1 answer
38k views

Changing mysql table comment

I know that mysql table comment can be defined at the creation with: create table (...)comment='table_comment'; And you can display comments by: show table status where name='table_name'; How do ...
v14t's user avatar
  • 513
40 votes
6 answers
78k views

Error Code 1117 Too many columns; MySQL column-limit on table

I have a table with 1699 columns and when I'm trying to insert more columns I get, Error Code: 1117. Too many columns In this table I have only 1000 rows. For me the most important thing is the ...
Mokus's user avatar
  • 1,017
39 votes
7 answers
224k views

How to determine the size of my tables in the SQL Server database

Is there any built-in function/stored procedure/query which is helpful to retrieve information about the size of MyTable in the SQL Server database?
Heisenberg's user avatar
  • 1,505
26 votes
6 answers
3k views

Index performance on ON versus WHERE

I have two tables @T1 TABLE ( Id INT, Date DATETIME ) @T2 TABLE ( Id INT, Date DATETIME ) These tables have a non-clustered index on (Id, Date) And I join these tables SELECT * ...
Erik Bergstedt's user avatar
23 votes
6 answers
3k views

Is there a generic term for tables and views?

I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso. As far as I understand, 'entity' is ...
Pinke Helga's user avatar
20 votes
4 answers
38k views

How to handle table design with variable columns

I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable. Say you are asked to record information on houses for a metro area, starting with a small ...
Schmitty23's user avatar
19 votes
2 answers
7k views

What is a “transition table" in Postgres?

The page describing what's new in Postgres 10 mentions “Transition Tables for Triggers”. Transition Tables for Triggers This feature makes AFTER STATEMENT triggers both useful and performant by ...
Basil Bourque's user avatar
16 votes
2 answers
42k views

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

I have created the table donor in the schema reference as per: CREATE TABLE reference.donor ( donor_code smallint PRIMARY KEY, donor_name character varying NOT NULL, donor_type smallint ...
dw8547's user avatar
  • 937
14 votes
2 answers
23k views

How to list all table columns in sorted order on Postgres?

I know that \d table_name lists all columns for that table, but is there a way to list them sorted in alphabetical order?
oxfist's user avatar
  • 361
10 votes
3 answers
19k views

Delete rows older than x days without locking table

we have a quite big MS SQL database with millions of rows. I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application. The ...
user2992455's user avatar
9 votes
3 answers
12k views

Two tables with same name in MySQL

I got this weird issue today when I dropped a temporary table. I dropped the temporary table and desc the table just to verify. But, the table was not dropped. After some searching I found that: ...
BALA's user avatar
  • 155
8 votes
5 answers
19k views

How to retrieve closest value based on look-up table?

I'm attempting to create a query that will find the closest value from one table and return its ID into the resulting table. Below is an example that should describe the situation better. Sample ...
pjbollinger's user avatar
8 votes
2 answers
33k views

What should I set my table_definition_cache to?

I just discovered table_definition_cache and I am trying to decide what to set it to. I am messing with my config because of performance issues. On one server I have 36599 tables and when I run SHOW ...
ODelibalta's user avatar
8 votes
1 answer
149k views

ERROR 1146 (42S02): Table 'db.tablename' doesn't exist [closed]

I have a two databases, when I prompt SHOW DATABASES I can see a list of the databases, including mysql, performance_schema and informations_schema. I can prompt 'SELECT DATABASE_NAME' on both of them,...
Elad Edri's user avatar
  • 181
8 votes
1 answer
20k views

Ternary relationship: what's the difference between having single table and having multiple tables?

Consider the following ternary relationship: Suppose that all entities have only two attributes (PK and Name). Here are the tables I derived (5 tables): Sector ------------------------- ID_Sector ...
feelthhis's user avatar
  • 265
8 votes
4 answers
19k views

In a one-to-one relationship, where should the foreign key be placed?

I am currently designing an ERD for a project management system. I am unable to choose between two workable solutions as the best solution as I wish to embrace the best standards. The two entities ...
Xegara's user avatar
  • 239
7 votes
2 answers
40k views

column {table_name} of relation {table_name} does not exist SQL state: 42703

I am getting an error while running an update query with table name specified along with column name: UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245' This is the error: ERROR: column "...
user avatar
7 votes
2 answers
6k views

Perform TABLESAMPLE with WHERE clause in PostgreSQL

I want to use TABLESAMPLE to randomly sample from rows that meet a certain condition with PostgreSQL. This runs fine: select * from customers tablesample bernoulli (1); But I cannot figure out how ...
Reveille's user avatar
  • 179
7 votes
3 answers
8k views

SQL - Many-to-Many relationship with same table and with relationship constraint

I have a SellerProduct table. Each row within the table represents product information as offered by a seller. The SellerProduct table has the following columns: id (serial, pk) productName (nvarchar(...
user4321's user avatar
  • 215
6 votes
1 answer
112 views

At what point is a (disk-based) table removed from memory after data is read from it?

Are (disk-based, so no Hekaton) tables persisted in memory for longer than the lifespan of the query that is reading data from them? If so, what determines how long they stay in memory for? Is there ...
J.D.'s user avatar
  • 37.8k
6 votes
1 answer
10k views

Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint

How come that statement don't copy constraints if I explicitly ask for it? CREATE TABLE backup_schema.permissions (LIKE public.permissions INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES ...
lese's user avatar
  • 283
6 votes
1 answer
92k views

Column name or number of supplied values does not match table definition error

I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get the following error: Column name or number of ...
codeOholic's user avatar
6 votes
1 answer
443 views

How to organize database for translations, performance?

I am working on a new project for an international website. The task is to get this available in 16 languages. On the database structure, I was wondering if there is any performance advantage or ...
Kolja's user avatar
  • 363
5 votes
1 answer
3k views

Sqlite Create Table with Check column from other table

I have two tables: position CREATE TABLE IF NOT EXISTS position( id_position INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, min REAL NOT NULL CHECK (min > 0), max REAL CHECK (max &...
ronek22's user avatar
  • 107
5 votes
2 answers
2k views

Table size grows indefinitely after deleting and then inserting the same data

I create a table and populate it with data: create table tab as select 'value' || level val from dual connect by level <= 10000; If I then delete all rows and insert the same data: delete from ...
Adamantium's user avatar
5 votes
2 answers
7k views

Unable to output data from tables in pgAdmin4 due to missing oid operator

I am currently running Postgresql 10.6 locally which I interface with using PgAdmin 4.12, up until today everything was running fine. However today I ran the following query in the pgAdmin query ...
Trashmonk's user avatar
  • 153
5 votes
1 answer
2k views

Same table alias for two different tables (Oracle)

This example has the same table alias used for two different tables. I don't understand why this is allowed by Oracle, and if allowed, how the results make any sense. create table Table_A (x number);...
Tom Warfield's user avatar
5 votes
1 answer
4k views

One generic mapping table VS many specific mapping tables

I'm trying to design database tables to track files uploaded by end users. Files can be uploaded under different contexts. Each context is a different table. As an example: An employee can upload ...
Jason's user avatar
  • 51
4 votes
3 answers
318 views

SQLServer - treat table as index

I have table that is more or less as follows: table Sample ( location bigint, device bigint, timestamp datetime, type bigint, value bigint, ) For performance reasons we also ...
Jędrzej Dudkiewicz's user avatar
4 votes
1 answer
22k views

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:- Email address Telephone number with extension, such as +44 ...
john Gu's user avatar
4 votes
2 answers
32k views

What does GRANT CONTROL on a schema do in MSSQL?

I'm using SQL Server 2016 for these tests. The following did not allow me to create a table in schema S for user U USE [D]; GRANT CONTROL ON SCHEMA :: [S] TO [U]; But this did: USE [D] GRANT ALTER ...
JustBeingHelpful's user avatar
4 votes
3 answers
1k views

Is there a good way to link tables and sequences?

I'm pretty sure you can't do this like you can with foreign keys but I'd like to document at the database level (even if it's just as a bit of free text somewhere) these relationships. Is there some ...
JonnyRaa's user avatar
  • 279
4 votes
2 answers
31k views

MySQL LOAD DATA INFILE Error Code 1366. Incorrect integer value: ' '

I've been trying to upload a CSV table into my remote server database but I find an error. I use MySQL in a RedHat Linux distrib. My mycsv.csv file looks like this: aa,ProductDescription,country,...
Salami's user avatar
  • 151
4 votes
2 answers
610 views

Should I set document version field to be part of a primary key

Should I set document version as a primary key I am working on a document management system, and I have these two tables representing part of the whole tables. On the DocumentVersion table I have ...
john Gu's user avatar
4 votes
3 answers
1k views

Unable to create / Move data to New Partitions from the existing partition SQL Server 2014

We have an SQL Server 2014 Enterprise Edition and a database around 6 TB in size. Just to give quick background of infra as Server is in Azure and having premium disk of 9 TB for datafiles. Have a ...
AzimL's user avatar
  • 89
4 votes
2 answers
1k views

Managing and Removing data from huge table

I have the following scenario: A huge table, that is also the most use table by far in the system. By huge I mean it is huge in number of rows and also huge in actual disk size, because it has 2 byte ...
Luiz Eduardo Simões's user avatar
4 votes
1 answer
895 views

Do I have to (sometimes) sacrifice normalization for data integrity?

I often come up against the following challenge, and end up de-normalizing my table design in order to enforce data integrity. I would be interested to hear if there is a different way of modelling ...
Laurence's user avatar
  • 315
4 votes
1 answer
2k views

How can I reuse a table name after deletion (in DynamoDB)?

I am working through a tutorial which requires creating a DynamoDB table with a specific name. On my first pass at creating the table I did something out sync with the tutorial, so I deleted the table ...
Lucas Gonze's user avatar
4 votes
1 answer
624 views

Designing Contacts

I've to create a DB structure which involves users, contacts, organizations and vendors. To break it down: We have a system with users (employees who use the the system). then these users add ...
theo's user avatar
  • 43
4 votes
1 answer
9k views

Sorting Table and get Position of row

I have a Table with 3 rows. Player, Time, Server. Now, I want 2 things and I don't know how. For Server there are 3 worths available. 1, 2 and 3. Server and Player are a Primary Key together. User ...
java4ever's user avatar
4 votes
1 answer
4k views

postgresql replace table without losing dependancies

i want to replace an existing table with a new one, without losing foreign keys or inheritance relations. But I feel lazy to alter it to make it look like the new one. Is there a way to replace the ...
Ben's user avatar
  • 257
4 votes
1 answer
1k views

Table Data Space Used Confusion

I have a heap table with around 700k rows. The rowsize is 34 bytes: (1x BIGINT, 2x SMALLINT, 2x BIT, 4X INT, 1x SMALLDATETIME). Why does Data Space Used say this table is taking up nearly 4000000KB (...
Brad's user avatar
  • 943
4 votes
1 answer
1k views

Returning table name and schema name along with column values

I have table in SQL: public.employee name age abc 20 xyz 30 I want a query which would return the column values along with schema name and table name. Input: select * from public.employee Output: ...
CoderAkki's user avatar
4 votes
1 answer
8k views

Check if table exists on a linked server

I've had a search, but can't find a post relating to this instance specifically. We have a linked production database that I'm trying to reference from our 'sandbox' server. I want to check if a ...
Jon's user avatar
  • 143
4 votes
2 answers
361 views

Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has ...
Rahul J's user avatar
  • 41
4 votes
2 answers
247 views

Alert on Table size

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system ...
Sakshi Sharma's user avatar
4 votes
2 answers
67 views

Snooping-safe Vote tables

I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to ...
aacom's user avatar
  • 57
4 votes
0 answers
1k views

Dynamic MySQL partitioning based on Unix Time

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing Unix time. I am designing an ...
Michael's user avatar
  • 173
3 votes
1 answer
2k views

Ordering of table is based on Clustered index or non clustered primary key?

I have created a table with primary key on name column, the ordering of the table is based on name column create table TestPrimaryKey (Name varchar(20) primary key,id int identity) insert into ...
mehmood shah's user avatar

1
2 3 4 5
11