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 ...
- 1,148
43
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 ...
- 64.2k
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 ...
- 64.2k
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, ...
- 88.2k
18
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 ...
- 98.5k
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 ...
- 180k
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 -...
- 180k
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 ...
- 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 ...
- 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 ...
- 64.2k
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 ...
- 16.5k
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
...
- 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) ...
- 5,182
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 ...
- 47.3k
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 ...
- 64.2k
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.
...
- 79
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 ...
- 16.5k
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() ...
- 23.1k
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 ...
- 26.6k
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 ...
- 98.5k
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 ...
- 26.6k
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> ::=
<...
- 98.5k
6
votes
Accepted
MOVE DB before restore, syntax error
If you format your command over multiple lines then you will get a more specific error message.
Restore Command
RESTORE DATABASE [dashdb_dev01_2024-04-11 143711]
FROM DISK = 'E:\SQL2019_Data\...
- 18.1k
5
votes
Why should a CTE start with a semi-colon?
Why should a CTE start with a semi-colon?
It shouldn't. The statement before should be correctly terminated instead.
From the documentation for WITH common_table_expression:
When a CTE is used in a ...
- 88.2k
5
votes
Accepted
Is it possible to write (A OR B) IN(X, Y, ..., W)?
No, there isn't a simple solution, unless the values are already in respective (normal or temporary) tables. If they are,
A IN(X, Y, ..., W) OR B IN(X, Y, ..., W)
is easy to rewrite. It's equivalent ...
- 98.5k
Only top scored, non community-wiki answers of a minimum length are eligible