Hot answers tagged

92

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


55

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


51

To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC. By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may ...


31

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


28

I always do it when posting here or on StackOverflow because for WITH - since the keyword is overloaded - the previous command requires a terminating semi-colon. If I paste a code sample that uses a CTE, inevitably some user will paste it into their existing code, and the previous statement won't have the semi-colon. So the code breaks, and I get complaints ...


27

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


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);


21

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


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


12

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


12

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) ;


12

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


12

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


11

This is to ensure that it is not included in any previous statements since WITH can serve a variety of purposes in T-SQL. If it's the first statement in the batch, I don't think you need it.


11

I actually used the "Microsoft SQL Server Migration Assistant (SSMA)" from MS once for this and it actually did what it promised to do: SQL Server Migration Assistant for Oracle (documentation) Microsoft SQL Server Migration Assistant v6.0 for Oracle (download) SQL Server Migration Assistant (SSMA) Team's Blog However in my case it was not as fast as I ...


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

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

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


8

In the context of your query, USING helps satisfy a JOIN so long as the two tables involved in the JOIN have the same column names to join with. It is like doing a NATURAL JOIN. Your query SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 works the same as SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.bar_id WHERE foo_id = 1 ...


8

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


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


8

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


8

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


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