Hot answers tagged

15

GLFiscalYearPeriods is a table and this comma implies a cross join. (Cartesian product) It seems that the query returns some values for each Fiscal Year. Given this tables: create table a (id int, foo int); create table c (id int); insert into a values (1,1),(2,2),(3,3); insert into c values (10),(20); select * from a, c; select * from a cross join c; ...


4

As mentioned by @Tibor, unless you set up logging or trace you cannot retrieve that information natively. You do not need to use trigger. Using SQL Server Management Studio Configure Login Auditing (SQL Server Management Studio) Once you set up above you can query the error log and retrieve the information. You can also save the date in a table. I did ...


4

You know this join approach? SELECT * FROM table1, table2 WHERE table1.pk = table2.fk If you leave the WHERE clause out of that query, you get the Cartesian product, which is the same as a CROSS JOIN: SELECT * FROM table1 CROSS JOIN table2 Your query looks like it combines that join approach with INNER JOIN syntax. It's logically the same as this: ...


4

The comma syntax for the join is an older syntax in the ANSI SQL standard (SQL-89 I think but I could be wrong) that was later updated (SQL-92 I think, again could be wrong) to use a more explicit syntax that was also more readable. It is known as the 'implicit join notation', contrasted with actual JOIN clauses that are 'explicit join notation'. The comma ...


3

My initial thoughts are that the view itself is the major issue, in that it is ranking over the entire data set. That is exactly it, the window function is applied to the whole dataset to then be filtered by CustomerId and rn=1. See my previous answer here for more information. With the CustomerId filter predicate applied only when the data gets to the ...


3

Here's where the problem creeps in. The plan contains a Constant Scan defining the output. CASE WHEN CASE WHEN (1) = (1) THEN (1) ELSE (0) END = (0) THEN '' ELSE CONVERT_IMPLICIT(varchar(1),CASE WHEN (1) = (1) THEN 'asdfg' ELSE NULL END,0) END Why does it down-cast to varchar(1)? I guess it takes the length of the first string it encounters during ...


3

Found a related answer from this post: https://stackoverflow.com/questions/13331621/sql-query-to-get-sql-year-version. Below code will provide year only. Credit goes to that post. Declare @SQLVersion varchar(100) Declare @TargetServer Varchar (4) --Extract SQL Server Year set @SQLVersion = (SELECT CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('...


2

You can use CONCAT and rely on implicit conversion to do it less verbosely. Both queries use 0 CPU, so you may have to run a lot of iterations to see if one is significantly more efficient than the other. SELECT [load_date], [load_time], CONCAT([load_date], ' ', CAST([load_time] AS TIME)) FROM [test_table_1] You aren't required to ...


2

I have some experience with high throughput tables, partitioning and Availability Groups, so I feel confident enough to post this as an answer, although the best I can really do is raise points for you to consider. First - your 3 synchronous replicas are going to be the slowest links in your chain. Every commit is going to have to wait for all three to ...


2

OK, so no logging turned on at this point. First step is to turn on the type of logging that suits you. Ones that come to mind are: Turning on the logging of also successful logins in the registry, using SSMS. As described by @SqlWorldWide in another answer. This goes to the EventLog, and also the errorlog file. Using XE trace. Again, already explained by @...


2

You do need to include a GUID higher in the query. This looks like it might do the job: SELECT N.value('../@GUID', 'nvarchar(max)') as c1, N.value('local-name(.)', 'nvarchar(max)') as ColName, T2.N.value('text()[1]', 'nvarchar(max)') as ColValue FROM (SELECT NEWID() as "@GUID", * FROM @MyTable FOR XML PATH('T'), TYPE) as T1(X) ...


1

To do what you want first step is generate a list of Dates then join that reservation table to the generate list of dates. Here is a thread on how to Generate list of Dates in MSSQL From this result set select where List of dates table failed to joined to the reservation table. The reservation columns will be filled with NULLs To write a sample ...


1

Since you already have the SID — a value that does not change based on culture — you might as well use it: DECLARE @Login sysname, @DelimitedLogin NVARCHAR(258), @SID VARBINARY(85); SET @SID = 0x010100000000000514000000; SELECT @Login = sp.[name] FROM sys.server_principals sp WHERE sp.[sid] = @SID; SET @DelimitedLogin = ...


1

Using (QUERYTRACEON 460) did not work for me when putting it at the end of my query. I turned it on at the DB level and it worked: DBCC TRACEON(460, -1); GO But, make sure to turn it back off once you've found and fixed the issue, do not leave it on! DBCC TRACEOFF(460, -1); GO


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