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

(NOLOCK) vs NOLOCK

SELECT SomeField FROM SomeTable NOLOCK means you've just aliased SomeTable AS NOLOCK. Try the below to see this clearly: SELECT NOLOCK.SomeField FROM SomeTable NOLOCK This obviously has no ...
Gareth Lyons's user avatar
  • 1,148
39 votes
Accepted

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would ...
Evan Carroll's user avatar
  • 63.5k
23 votes
Accepted

How do I write a hex literal in PostgreSQL?

Arbitrary Data / Integers (int) You can write the hexidemical byte using Bit-string constants SELECT x'CC'; -- same as b'11001100' Which is essentially the same as bit x'CC' returning a Bit String ...
Evan Carroll's user avatar
  • 63.5k
22 votes

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

Consider below case: +------------+--------------+-----------+ | date | services | downloads | +------------+--------------+-----------+ | 2016-05-31 | Apps | 1 | | 2016-05-...
imVJ's user avatar
  • 321
19 votes
Accepted

Can we put an equal sign (=) after aggregate functions in Transact-SQL?

This is documented in UPDATE (Transact-SQL): SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, ...
Paul White's user avatar
  • 85.5k
17 votes

How to create a temporary table using VALUES in PostgreSQL

The issue is the datatypes. If you remove them, the statement will work: CREATE TEMP TABLE lookup (key, val) AS VALUES (0, -99999), (1, 100) ; You can define the types by casting the values ...
ypercubeᵀᴹ's user avatar
16 votes
Accepted

Syntax shorthand for updating only changed rows in UPSERT

There is no provision in SQL to say "all columns except this one". (You can do something like that with jsonb or hstore.) Related answer with details: Select columns inside json_agg On ...
Erwin Brandstetter's user avatar
14 votes
Accepted

PostgreSQL use NEW in query for INSTEAD OF trigger

NEW is a record, not a table. Basics: Use NEW in FROM clause in Postgres trigger? Slightly modified setup CREATE TABLE product ( product_id serial PRIMARY KEY , product_name text UNIQUE NOT NULL -...
Erwin Brandstetter's user avatar
12 votes

MySQL Set UTC time as default timestamp

With MySQL 8.0.13, you can use UTC_TIMESTAMP as the default value if you surround it with parentheses like so: CREATE TABLE `blah` ( id int NOT NULL AUTO_INCREMENT, creation_time TIMESTAMP NOT ...
John C's user avatar
  • 233
12 votes
Accepted

What allows SQL Server to trade an object name for a string passed to a system procedure

The first argument to the system stored procedure sp_helptext is: [@objname= ] 'name' Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required ...
11 votes

How to create a temporary table using VALUES in PostgreSQL

You really don't need to create a table nor use a CTE, if all you need is to use a few values in your queries. You can inline them: SELECT * FROM (VALUES(0::INT, -99999::NUMERIC), (1, 100)) AS ...
isapir's user avatar
  • 259
9 votes
Accepted

Do any SQL dialects permit the logical sequence of SELECT clauses?

implementations of SQL No, SQL is standardized. There aren't vastly different implementations of a standard. That kind of defeats the purpose. There was/is always a fight over the relational-calculus ...
Evan Carroll's user avatar
  • 63.5k
9 votes
Accepted

Incorrect syntax near '+' in stored procedure sql server

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value. Consider changing the execution of sp_send_dbmail to this: SET @usage_data += ' end of ...
Randi Vertongen's user avatar
8 votes
Accepted

PL/pgSQL regclass quoting of table named like keyword

Explanation I'm going to step through the multiple layers of misunderstandings one by one - arriving at a simple, secure solution. 0. The reason why overlay is quoted is not because it's a function ...
Erwin Brandstetter's user avatar
8 votes
Accepted

How to add a named constraint in Oracle SQL?

Oracle 12c: Table create table clookup ( clookup_col varchar2( 64 ) ); Add a NOT NULL constraint alter table clookup modify ( clookup_col constraint lookup_9 not null ) ; Optional select ...
stefan's user avatar
  • 2,507
8 votes
Accepted

Use square brackets on extended events filter

This was challenging. WHERE [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%[[]demo]%') When testing it, even the opening bracket is regarded as a [ ] (Wildcard - Character(s) ...
Zikato's user avatar
  • 4,737
8 votes
Accepted

Is this valid SQL Server Update syntax?

The syntax: ... SET (shipcountry, shipregion, shipcity) = ( SELECT country, region, city ... ) ... is valid SQL syntax but not valid SQL Server syntax. It has not yet been implemented ...
8 votes

Why are unquoted identifiers upper-cased per SQL-92?

But making it dependent on the identifier being quoted doesn't look rational Case-sensitive identifiers suck. Imagine the chaos of having tables named Foo, FOO, and foo, and all the frustrating ...
David Browne - Microsoft's user avatar
7 votes

Quoting columns with spaces in PostgreSQL?

Moving to PostgreSQL convention For more information on this see the post How do I adopt the PostgreSQL naming convention in legacy database? In PostgreSQL by convention and with good reason, we ...
Evan Carroll's user avatar
  • 63.5k
7 votes

Reference column alias in same SELECT list

I hit something like this migrating a 500+ line Netezza query (aka modified Postgres) to SQL Server. In Netezza the computed column alias was allowed to be used as a value in downstream references. ...
D Turpin's user avatar
7 votes
Accepted

Unfamiliar syntax - Query with Parameters in Braces at the beggining

You are right, the (@be int) shown applies to parameterized queries. Applications often parameterize the queries using sp_executesql, and then sent them to the sql server. The query will be cached ...
Randi Vertongen's user avatar
7 votes

Can’t run a function against EXEC

The LEFT() function in your example is not part of the EXEC statement. It is inside the string representing part of the dynamic SQL to be executed by your EXEC statement. In contrast, the CAST() ...
Andriy M's user avatar
  • 23k
7 votes

How do I identify the SQL server brand via SQL queries

Go by elimination. Failing select @@version will eliminate both SQL Server and MySQL; failing select version() will eliminate both Postgres and MySQL; etc. Examples of such queries have been helpfully ...
mustaccio's user avatar
  • 26k
6 votes
Accepted

Syntax Error Help

Glad you got the truncation issue figured out, but may I offer an alternative that won't be prone to that, eliminates the need for cursor scaffolding, etc.? DECLARE @src nvarchar(max) = N'', @sql ...
Aaron Bertrand's user avatar
6 votes
Accepted

Comma-separated join (cross join)

The comma is the implicit join operator, ANSI SQL-89 syntax. No different than the comma in: select ... from a , b -- <-- this comma where a.aid = b.aid; I think you can replace it ...
ypercubeᵀᴹ's user avatar
6 votes

Why is the ROW keyword needed when constructing a row with one element, but not two?

As is often the case, the answer is to be found in documentation. The UPDATE reference says, in particular: According to the standard, the source value for a parenthesized sub-list of target column ...
mustaccio's user avatar
  • 26k
6 votes
Accepted

What are these single letter SQL keywords?

Checking my copy (of a draft) of the SQL standard (version 2011), I see that K, M, G, T, P are mentioned in section 5.2 <token> and <separator>: <large object length token> ::= <...
ypercubeᵀᴹ's user avatar

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