Tag Info

Hot answers tagged

38

To quote Joe Celko (not only can you find this reference all over the web and in his Wikipedia entry, but you will even see it on T-shirts at some conferences): Rows are not records. A lot of people point him out as a pedantic jerk who just likes to humble and verbally abuse newbies, and I will admit that is how he comes across. But I have also met him ...


27

Microsoft has in several places in their organization provided that the official name for tabular data storage per table-entry (to coin a taxonomic definition that serves my own purpose) is called a "ROW". I submit as evidence ROW_NUMBER, ROWCOUNT, ROWVERSION and the DataTable.Rows property, where a DataTable is a C# representation of a TSQL "table" object. ...


19

I've just searched through the document "Information technology — Database languages — SQL Part 2: Foundation (SQL/Foundation)", which defines the ANSI standard for SQL as implemented by all major RDBMSes. The word row is used primarily throughout the document several hundred times, as expected. The word record was only used to describe a record that is ...


10

Because relational databases are rarely used in isolation, in order to avoid confusion between other parts of systems, I always refer to tables and rows and columns. In a client applications, we typically have other constructs, including datareaders, datasets, datarows, datatables, etc - for instance "field" is often used for on-screen data entry and Pascal ...


7

The language keeps evolving. A few decades ago the literate people used "indices" instead of simpler "indexes". As we switched to "indexes", we eliminated an unnecessary complication and made the language more useful. The need to memorize a plural for "index" was pure overhead - it did not in any way help us communicate. Make no mistake, there used to be ...


6

Every individual transaction (statement) in SQL Server is atomic, meaning it passes or fails as a unit. If the 999,999th record fails on a constraint violation in a 1,000,000 row insert, all of the other rows get rolled back and the table is exactly as it was before the failed statement was attempted. The same applies for updates and deletes, as well as ...


6

You can do this. WITH T AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID, ID FROM StagingTable WHERE ID IS NULL) UPDATE T SET ID = New_ID So the windowed function is used in the SELECT list but you can still use the result of it to ...


4

Unfortunately, there is no provision in SQL syntax to say "all columns except this one column". You can achieve your goal by spelling out the remaining list of columns in a row-type expression: SELECT a.id, a.name , json_agg((b.col1, b.col2, b.col3)) AS item FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; That's short for the more ...


4

You have essentially no guarantees about the sequence of ROWIDs. From the ROWID Pseudocolumn docs: If you delete a row, then Oracle may reassign its rowid to a new row inserted later. So the delete scenario has a potential for not being sequential. The ROWID encodes a relative file number and block number. There is no guarantee that these will ...


3

I think you could try the following: set @balance := 0; SELECT stmnt_date, debit, credit, (@balance := @balance + (debit - credit)) as Balance FROM statements ORDER BY stmnt_date;


2

Although your question is already answered very well. I would like to add my points too. May be you find it helpful upto some extend. Also my answer is not specific to SQL Server These words are used interchangeably. 1 2 3 4 -------------------------------------------------------------------- Row = Record = Tuple ...


2

I see no COMMIT up there in your code. By default SQL*Plus autocommit is off. Data changes are not visible to other sessions until they are not commited.


2

INSERT INTO mytable (ID,`key`,`value`) VALUES (1106,'_views',1) ON DUPLICATE KEY UPDATE `value` = `value` + 1; and INSERT INTO mytable (ID,`key`,`value`) VALUES (1107,'_views',1) ON DUPLICATE KEY UPDATE `value` = `value` + 1; If your table has value defined as CREATE TABLE mytable ( ... value INT DEFAULT 1, ... ); then the INSERTs would be ...


2

Supposing you have no foreign keys referencing that table, you could do something like create table some_table as select min(id), name, age, x from t group by name, age, x then you can drop the old table, rename the new table so that it has the same name as the old one before, and create indexes and other things you need on that table.


1

Assuming that stmnt_date has a UNIQUE constraint, this would be fairly easy with window/analytic functions: SELECT s.stmnt_date, s.debit, s.credit, SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance FROM ...


1

This is standard sql server emulation of GROUP_CONCAT in mysql select SalesOrderNumber,Ids=Stuff((SELECT ',' + CAST(SalesOrderDetailID as VARCHAR(255)) FROM t t1 WHERE t1.SalesOrderNumber=t.SalesOrderNumber FOR XML PATH ('')) , 1, 1, '' ) from t GROUP BY SalesOrderNumber Fiddle Also,you might be intrested in ...


1

It's right to use Entity name for prefix/suffix to represent a table schema as your class Entity is a mapping to table schema not the data of the table. And in terms of data mapping a single instance of the Entity will always represent the row as the row will have all those columns which are attributes in entity and a List of the Entity will represent a ...


1

There is a better option with json_build_object() in Postgres 9.4+ SELECT id, json_build_object('name', name, 'addr', addr) AS data FROM myt; But there is also a simpler and faster way with row_to_json() in pg 9.3: SELECT id, row_to_json((SELECT d FROM (SELECT name, addr) d)) AS data FROM myt; SQL Fiddle. Cast to ::text is only for a sanitized ...


1

If the objective is to be able to sort the rows according to row_number then don't bother updating values on a delete. The rows will come out in the same order after a row is deleted as they would before. It is not worth the effort to close up the gaps. In the worst case, if you delete the lowest-numbered row, you will have to update every remaining row ...


1

The best way for that is to use the sequence way which is auto increment facility, example: CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM; so in that case you don't need to mention that id in the insert like: INSERT INTO animals (name) VALUES ...


1

No. It would be true if all the rows were of exact the same size in single user mode. Imagine situation when row wont' fit into particular block because it does not fit in it. So this row will be put into the "next" block. Then you insert some smaller row, this one will be put into the previous block. In most cases when doing full table scans the rows are ...


1

I have found that it's best to create the JSON, then aggregate it. e.g. with base as ( select a, b, ('{"ecks":"' || x || '","wai":"' || y || '","zee":"' || z || '"}"')::json c ) select (a, b, array_to_json(array_agg(c)) as c) Note this can be done as a subquery if you don't like CTEs


1

Please read this but report: http://bugs.mysql.com/bug.php?id=69027 The workaround ("solution", really) to this is to change the password for the affected user to a post-4.1 hash. This is really a recommended best practice, regardless - password hashing and authorization process prior to 4.1 has notable security limitations (discussed in documentation at ...


1

The only information you have about 'when' a row entered the table is the ID of the inserting transaction. The sad news is that this is in no way connected to any real timestamp. So you either add the new column (where you can't figure out when the existing rows were inserted) - or probably you can create triggers on all tables which populate a transaction ...



Only top voted, non community-wiki answers of a minimum length are eligible