Tag Info

Hot answers tagged

12

The performance will be the same. The optimizer will recognize this and create the same plan. On the other hand I wouldn't say they are equal. The first form is far more readable and generally expected. For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query. You should be able to ...


10

I'm not familiar with SQL Server's brand of SQL, but it must be something like: SELECT ID FROM My_Table WHERE (A0 = A1) AND (A1 = A2) AND (A2 = A3) AND (A3 = A4);


9

They are semantically identical and the optimiser should have no trouble recognising this fact and generating identical plans. I tend to put conditions referencing both tables in the ON and conditions referencing just one table in the WHERE. For OUTER JOINS moving the conditions can affect the semantics however.


8

Given the sample data: CREATE TABLE dbo.Data ( ID integer PRIMARY KEY, A0 character(1) NULL, A1 character(1) NULL, A2 character(1) NULL, A3 character(1) NULL, A4 character(1) NULL ); INSERT dbo.Data (ID, A0, A1, A2, A3, A4) VALUES (14, 'A', 'B', 'A', 'C', 'A'), (15, 'A', 'A', 'A', 'A', 'A'); An alternative way to ...


8

Using another left join: select a.dbname , coalesce(b.msgval, d.msgval) as msgval from list a left join msg b on a.dbname = b.dbname left join msg d on b.dbname is null and d.dbname = 'alldbs' ; or a cross join (this requires an 'alldbs' row to be present, otherwise it will return 0 results.): select a.dbname ...


7

Another way (not as pretty looking to me after seeing @ypercube's suggestion, but still...): SELECT l.dbname, x.msgval FROM dbo.list AS l CROSS APPLY ( SELECT TOP (1) m.msgval FROM dbo.msg AS m WHERE m.dbname IN (l.dbname, 'alldbs') ORDER BY CASE m.dbname WHEN 'alldbs' THEN 2 ELSE 1 END ASC ...


5

EXEC msdb.sys.sp_executesql N'ALTER ROLE ...';


5

Using a subselect to get the missing msg: select a.dbname , coalesce(b.msgval, (select msgval from msg c where dbname = 'alldbs')) as msgval from list a left join msg b on a.dbname = b.dbname


5

Two subqueries? SELECT Q1.A, Q1.B, Q2.C, Q2.D FROM (SELECT SUM(A) A, SUM(B) B FROM @X) Q1(A, B) CROSS APPLY (SELECT SUM(C) C, SUM(D) D FROM @Y) Q2(C, D);


4

DECLARE @Name varchar(100) = 'Doe, Jane' SELECT SUBSTRING(@Name,CHARINDEX(',',@Name)+1,LEN(@Name) - CHARINDEX(',',@Name) )+ ' ' + SUBSTRING(@Name,1,CHARINDEX(',',@Name)-1)


4

SQL Server does NOT have guaranteed short circuiting in that scenario. The point of this construct is so that businessLogicCondition is only evaluated as part of the WHERE clause in the case where @term1 is populated. It may still be evaluated as part of query processing, because you can only control order of evaluation in a few special cases (no pun ...


4

I can suggest another solution. You can use COMPUTED COLUMN. Of course this is if you can modify table structure and if you will do that comparison very often. Inside the COMPUTED COLUMN you can calculate if all columns that you need to compare contains the same data with boolean value. After that you only compare that value in your queries. Also if you ...


4

I often generate T-SQL like this: select 'ALTER DATABASE ' + quotename(name) + ' MODIFY FILE (...)' from sys.databases where databaseIsMisconfigured = 1 Select the entire column, past it into a new window and F5.


4

EXEC SQL2.master.sys.sp_executesql N'BACKUP DATABASE dbname TO DISK = N''D:\path_on_remote_server\...'';';


3

You can use sqlmigration - Start-SqlServerMigration.ps1 This script provides the ability to migrate databases using detach/copy/attach or backup/restore. SQL Server logins, including passwords, SID and database/server roles can also be migrated. In addition, job server objects can be migrated and server configuration settings can be exported or ...


3

You can use SQLCMD Mode in SSMS with :CONNECT and servername\instance to connect to particular server and run your command. Navigate to SQLCOMD Mode:Open New Query > SQLCMD Mode just type in query in following format if you have multiple servers to connect and take backup. :CONNECT SQL2 BACKUP DATABASE DATABASENAME TO DISK ='C:\' :CONNECT SQL3 BACKUP ...


3

sp_executesql creates a new execution context which ceases to exist when the executed statements return. The USE only has effect within the sp_executesql. Try this SET @SQL02 = 'USE ['+ convert(nvarchar(50),@dbName) +']; SELECT DB_NAME();' and you'll get what you're after.


2

As the discussion in the comments revealed, an fcontainer can be related to many products, due to the 1-to-many relationship between FContainer and Stämplingar. Try this query, which will choose only one product per fcontainer. The missing fcontainers are due to the INNER join. Changing it to LEFT join will show all fcontainers, even those not related to ...


2

If the NULL value is explicitly specified, you'll need to use a DML trigger to update NULL values to 0 after insertion. This can't be done with a default constraint.


2

CREATE TYPE [tvf_id] AS TABLE ( [id] [int] NULL ,[OrdCol] [INT] NOT NULL ) GO CREATE PROCEDURE [sp_Test] @pID tvf_id READONLY as begin set nocount on SELECT a.* FROM datatable a inner join @pID b on a.id = b.id ORDER BY b.OrdCol ASC end GO -------------------------------------------------------- DECLARE @data tvf_id ...


2

Your friend is cmdline tools SQLCMD in SSMS : :connect sql1\instance1 BACKUP DATABASE DATABASENAME TO DISK ='BACKUP SERVER' :connect sql2\instance2 BACKUP DATABASE DATABASENAME TO DISK ='BACKUP SERVER' PowerShell


2

I assume you are talking about the same query, executing with different indexes. You can't just arbitrarily compare 2 totally different queries that do 2 different things. As Aaron stated in the comments, I can compare my 2001 VW to a 2015 Camaro and there will be no correlation, the Camaro may go faster but that's because it has a bigger engine. ...


2

Here's one way: SELECT MAX(A), MAX(B), MAX(C), MAX(D) FROM ( SELECT SUM(A) AS A, SUM(B) AS B, NULL AS C, NULL AS D FROM X UNION ALL SELECT NULL AS A, NULL AS B, SUM(C) AS C, SUM(D) AS D FROM Y ) AS T You may have to cast NULL to whatever type A, B, C and D have


1

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.) DECLARE @i INT = 30; -- interval in minutes DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME; SELECT ...


1

You need to create the type inside the database where you will declare it - data types can not be reached across databases or servers. This is no different from creating a local variable using an alias type - it has to exist in the context of the current database (or your code has to execute in the context of the database where the type exists, e.g. EXEC ...


1

you could script out all settings. make the changes you want in the the GUI but instead of click OK to apply the change, click script at the top of the window and the SQL for your changes will be scripted out. You will now just have to change database name/file names/ sizes/ etc for each database. USE [master] GO ALTER DATABASE [test] MODIFY FILE ( NAME = ...


1

It is not neccesary to modify Jeff Moden's function. I have devised a little bit sophisticated way of achieving the result just using the original function. First Here it is the original Moden's function: (Source: http://www.sqlservercentral.com/articles/Tally+Table/72993/) CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters ...



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