The set of rules that define the combinations of symbols that are considered to be correctly structured for that language.
Questions tagged [syntax]
240 questions
69
votes
2 answers
How to join a table with a table valued function?
I have a user defined function:
create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
-- blah blah
end
Now I want to join this on another table, like so:
select f.ID,…
Shaul Behr
- 2,793
- 8
- 29
- 40
58
votes
3 answers
Performance difference for COALESCE versus ISNULL?
I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when using it. However, ISNULL seems easier to read…
Richard
- 6,223
- 8
- 39
- 62
57
votes
6 answers
How to create a temporary table using VALUES in PostgreSQL
I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes.
I looked at the documentation for CREATE TABLE and it says VALUES can be…
tinlyx
- 2,735
- 4
- 29
- 53
45
votes
6 answers
MySQL Set UTC time as default timestamp
How do I set a timestamp column whose default value is the current UTC time?
MySQL uses UTC_TIMESTAMP() function for UTC timestamp:
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP() |
+---------------------+
| 2012-07-01…
Adam Matan
- 10,129
- 27
- 75
- 94
38
votes
6 answers
Why do we use Group by 1 and Group by 1,2,3 in SQL query?
In SQL queries, we do use Group by clause to apply aggregate functions.
But what is the purpose behind using numeric value instead of column
name with Group by clause? For example: Group by 1.
ursitesion
- 1,851
- 6
- 30
- 45
24
votes
1 answer
(NOLOCK) vs NOLOCK
I was investigating some blocking when I saw a query that looked something like this:
SELECT SomeField FROM SomeTable NOLOCK
I saw the NOLOCK and was curious how it could be blocking other queries, in this case DELETE statements. I took a quick…
Brian
- 353
- 2
- 5
21
votes
3 answers
How do I declare and use variables in Oracle?
My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:
declare @startDate int
select @startDate = 20110501
And I get this error:
declare @startDate int
select @startDate =…
Mark Allison
- 475
- 1
- 3
- 9
16
votes
2 answers
Peculiar Oracle outer join syntax case
I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax:
SELECT ...
FROM A, B, C, D, E
WHERE A.A_ID = B.A_ID
AND B.B_ID = C.A_ID(+)
AND B.B_KEY = C.B_KEY(+)
AND C.C_ID =…
Peter Eisentraut
- 9,473
- 1
- 29
- 33
16
votes
2 answers
Why should a CTE start with a semi-colon?
I was just looking at a post on StackOverflow where Aaron Bertrand proposes using a CTE instead of a numbers table, which is an elegant way of performing the task at hand. My question is, why does the first line of the CTE begin with a semi-colon? …
Hannah Vernon
- 66,859
- 22
- 158
- 297
15
votes
2 answers
What is the easiest way to move data from Oracle to SQL Server?
One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition for us.
What is the easiest way to get all the…
Martin
- 2,381
- 4
- 22
- 34
15
votes
1 answer
What allows SQL Server to trade an object name for a string passed to a system procedure
What causes it to be legal to pass an object name to the system stored procedure sp_helptext?
What mechanism converts the object name to a string?
e.g.
-- works
sp_helptext myproc
sp_helptext [myproc]
sp_helptext [dbo.myproc]
-- and behaves the same…
JJS
- 611
- 1
- 5
- 15
13
votes
1 answer
Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?
There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this..
WITH w AS (
SELECT *
…
NO WAR WITH RUSSIA
- 54,954
- 34
- 200
- 411
13
votes
2 answers
Quoting columns with spaces in PostgreSQL?
I migrated a database from SQL Server to PostgreSQL.
Most column names contain double words, for example:
SELECT [Column Name] FROM table;
...which does not work in PostgreSQL.
What is the correct syntax for PostgreSQL?
A Chang
- 131
- 1
- 1
- 3
11
votes
1 answer
Syntax of INNER JOIN nested inside OUTER JOIN vs. query results
TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.
Following up on my previous question where we found query performance difference…
JeffInCO
- 265
- 1
- 2
- 6
11
votes
1 answer
Can we put an equal sign (=) after aggregate functions in Transact-SQL?
I have encountered a script like this:
set @sum = sum = (case when somecol1 is null then DATEDIFF(d,[somecol2],somecol3) else 0 end)
I can't understand the meaning of the equal sign (=) after the second keyword sum. When I run the query it is not…
igelr
- 2,006
- 1
- 11
- 36