Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [table]

A data structure that organizes information into rows and columns.

Tables are used to organize data into a structured format defined by a fixed number of columns, and an unlimited number of rows. This allows a specific value to be retrieved for an individual column/row cell. In addition to storing data, a table can have associated metadata which may constrain/restrict the data it contains.

To CREATE a table, use the CREATE TABLE command,

To ALTER a table, use the ALTER TABLE command,

To delete or DROP a table, use the DROP TABLE command,

See: Table (database)

552 questions
108
votes
18 answers

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, and also that “it takes longer to read…
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
42
votes
1 answer

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 you change (alter?) table comment after it's been…
v14t
  • 523
  • 1
  • 4
  • 6
40
votes
6 answers

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 number of columns. Are there any limitations on the…
Mokus
  • 997
  • 4
  • 15
  • 18
39
votes
7 answers

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
  • 1,505
  • 5
  • 18
  • 31
26
votes
6 answers

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 * FROM T1 AS t1 INNER JOIN T2 AS t2 ON t1.Id =…
Erik Bergstedt
  • 387
  • 3
  • 9
23
votes
6 answers

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 not the proper term since this would correspond…
Pinke Helga
  • 341
  • 2
  • 5
20
votes
4 answers

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 neighborhood (200 houses) but eventually growing to…
Schmitty23
  • 301
  • 1
  • 2
  • 4
19
votes
2 answers

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 exposing, as appropriate, the old and new rows to…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
16
votes
2 answers

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 REFERENCES reference.donor_type (type_id), …
dw8547
  • 947
  • 3
  • 11
  • 24
14
votes
2 answers

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
  • 361
  • 1
  • 5
  • 14
10
votes
3 answers

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 table has an indexed "ID" PK, and also a "date"…
DataViking
  • 101
  • 1
  • 1
  • 4
9
votes
3 answers

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: MySQL allows to create a temporary table with the same…
BALA
  • 155
  • 2
  • 2
  • 7
9
votes
2 answers

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 GLOBAL STATUS the value for Opened tables is…
ODelibalta
  • 289
  • 2
  • 3
  • 12
9
votes
4 answers

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 that can access the system are the clients and…
Xegara
  • 249
  • 1
  • 2
  • 9
8
votes
5 answers

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 Data These two tables will exist in the SQL…
pjbollinger
  • 81
  • 1
  • 1
  • 3
1
2 3
…
36 37