Tag Info

Hot answers tagged

32

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 ...


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

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

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 ...


3

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 ...


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

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 ...


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

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

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

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

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