Hot answers tagged

104

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


69

EDIT: I am leaving the original accepted answer as it is, but please note that the edit below, as suggested by a_horse_with_no_name, is the preferred method for creating a temporary table using VALUES. If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like: WITH vals (k,v) AS (VALUES (...


52

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 effect on the locking behaviour of the query. The query doesn't fail because despite being a keyword & showing blue in SSMS, NOLOCK is not a reserved word in ...


36

This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms. The reasons people do it: they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code. The reasons it's bad: it's not self-documenting -...


31

create table as needs a select statement: DROP TABLE IF EXISTS lookup; CREATE TEMP TABLE lookup as select * from ( VALUES (0::int,-99999::numeric), (1::int, 100::numeric) ) as t (key, value); You can also re-write this to use a CTE: create temp table lookup as with t (key, value) as ( values (0::int,-99999::numeric), (1::int,...


25

In most RDBMSs, double-quotes are what are used to specify an exact spelling of something.. (single quotes being string delimiters). SELECT tab."This IS My Column EXACTLY" AS col FROM "My TabLE Name Contains Spaces Too!" tab WHERE tab."ANOTHER UGLY COLUMN name" = 'MyFilterString'; Notice that capital/lowercase also matters when using double-quotes. (...


23

The syntax rules require at least one target column to be specified. If you want to let the defaults "kick in" for all columns, you can use the default values clause. INSERT INTO people DEFAULT VALUES; another alternative is to provide one column with the DEFAULT clause: insert into people ("timestamp") values (default);


19

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, which sets the variable to the pre-update value of the column. In your code example, sum is the (unwise) name of a column, not an aggregate. db<>fiddle demo


16

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 use this syntax. CREATE TABLE foo AS WITH w AS ( SELECT * FROM ( VALUES (1) ) AS t(x) ) SELECT * FROM w; Also worth noting that it's not explicit in the ...


15

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 of the first row: CREATE TEMP TABLE lookup (key, val) AS VALUES (0::bigint, -99999::int), (1, 100) ;


13

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. The second plan has a lower estimated cost, so in that limited sense it is 'better'. The data sets are so small that the optimizer did not spend much time looking at alternatives. The first form of ...


13

Test this code: drop table if EXISTS table_social; create table table_social ( sid varchar(250) primary key, auth char(32) not null, social integer, -- to be checked by triggers female boolean, given varchar(250) not null, family varchar(250) null, photo varchar(1000) null, -- to be ...


13

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 Type but there is a cast available to int so you can do x'CC'::int * 5 UTF-8 Byte Sequence If the byte sequences is a valid UTF-8 character, you can also use ...


13

It's documented with the SELECT statement and it's called "TABLE Command" there. In the SQL standard it's called an "explicit table": The <explicit table>    TABLE <table or query name> is equivalent to the    ( SELECT * FROM <table or query name> ) This seems to be part of the standard at least ...


12

group is a reserved word (and by is another reserved word) - it's not GROUP BY that is reserved. Because it is a reserved word, it cannot be used directly as an identifier. To use a reserved word or a name with "illegal" characters (such as a space) for an identifier, you need to quote the identifier. ALTER TABLE test RENAME COLUMN sum TO "...


12

Consider below case: +------------+--------------+-----------+ | date | services | downloads | +------------+--------------+-----------+ | 2016-05-31 | Apps | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Applications | 1 | | 2016-05-31 | Apps | 1 | | 2016-05-31 | Videos | 1 | | ...


11

'#' denotes a temporary table. This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key ...


11

This is just Standard SQL join syntax with the optional parentheses removed: SELECT * FROM tableC LEFT JOIN ( TableB RIGHT JOIN TableA ON TableA.ID = TableB.ID ) ON TableB.TypeID = TableC.TypeID If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using ...


11

It's SQLCMD What you have is a sqlcmd script. The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt. And sqlcmd has some sqlcmd specfic commands that start with a colon. Some general info on sqlcmd below. You can write SQL statements that are executed after you type GO. In the example, first the ...


10

Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result. You can implement FOR XML PATH and STUFF(): SELECT DISTINCT d.AppId, d.AppName, d.AppType, Tags = STUFF((SELECT ', ' + t.TagName FROM AppTags t where d.AppID = t.AppID ...


10

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 only if a qualified object is specified. If a fully qualified name, including a database name, is provided, the database name must be the name of the current ...


9

MySQL allows you to do GROUP BY with aliases (Problems with Column Aliases). This would be far better that doing GROUP BY with numbers. Some people still teach it Some have column number in SQL diagrams. One line says: Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as ...


9

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 -- must be UNIQUE ); CREATE TABLE purchase ( purchase_id serial PRIMARY KEY, product_id int REFERENCES product, when_bought date ); CREATE VIEW ...


9

Yes this is possible. Your query has several syntax errors however. a dash in an identifier is invalid. I used an underscore instead in the below example. If you did create the columns that way, you will need to quote them, e.g. "person-name" the equality operator in SQL is =, not == String constants need to be put in single quotes (') not double quotes ...


9

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 (which SQL pretends to be) vs relational-algebra approach. It an old debate, Having selected a model for the data, our next step was to select a query ...


9

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 data' exec msdb.dbo.sp_send_dbmail @profile_name='some_mail_profile', @recipients=@admin_list, @subject='Mail from sql server', @body=@usage_data, @importance=...


9

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 second thought, you don't need this. You might as well update all columns. Postgres writes a new row version anyway, nothing lost. INSERT INTO test_upsert AS t (...


8

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 lookup(key, val) Then you can get a Cartesian product with a CROSS JOIN (where the other relationship can be, of course, a regular table, view, etc.). e.g.: ...


8

The syntax choice is likely for consistency, albeit redundant. TRUNCATE TABLE follows the same pattern as other DDL statements (CREATE, ALTER, DROP), where the object type immediately follows the action keyword. I should add TRUNCATE TABLE is part of the ANSI SQL Standard as of the 2008 version, although it was implemented in SQL Server long before then.


8

SQL80001 is a SQL Server error message. SQL Server doesn't support the old Oracle (+) syntax for outer joins. Re-write your SQL using LEFT/RIGHT OUTER JOIN .... ON .....


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