Tag Info

New answers tagged

0

From what I read, disabling guest account in master, msdb and tempdb is not advisable. For references and additional information; please read the below linked articles. From SQL*Server BOL The guest user cannot be dropped, but guest user can be disabled by revoking its CONNECT permission by executing REVOKE CONNECT FROM GUEST within any database other ...


0

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this. The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on ...


0

You may not use temporary table to achieve the result you need. Please try this query: with joined as ( select em.*, e.* from [Event] e inner join [EventEmployee] eem on (eem.EventID = e.EventID) inner join [Employee] em on (em.EmployeeID = eem.EmployeeID) ) select distinct EventID, EventDate from joined where EventDate = (select max(e.EventDate) ...


5

SMS suggested adding a covered index to one of my tables that indexes on the column I believe makes perfect sense. However, it also recommended including (covering) every other column in the table. The suggestions shown in SSMS are actually generated by the query optimizer while it searches for an efficient physical execution plan for the query. The ...


1

There are probably a thousand good answers here and only some of them will apply to your learning style and situation. If you were able to read the 70-461 book and then use it to immediately pass the test, then you've already got a pretty good foundation. The question really becomes, what is your personal learning style and which methods apply to your ...


2

Use following query if you want to count the number of occurrence of the string 'first' in the TextColumn each row. SELECT (LEN(TextColumn) - LEN(REPLACE(TextColumn, 'first', '')))/LEN('first') as NumOfOccurrence, TextColumn FROM dbo.SampleTable; Update: To cover the trailing space case (as caught by Twinkles in the following comment), I modified the ...


0

In the absence of any set word for this behaviour, may I suggest it should be called: SELECSERT


2

Nobody can really tell you exactly why this person coded the WHERE clause this way. I can tell you that I can't think of a reason that form is better than: r.roomprovider_id <> br.roomprovider_id (And while I can interpret how the code is currently written, I am not sure of the expected/intended behavior - should a row be included or excluded when ...


0

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of. The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor ...


1

The easy solution is to check every single column that you care about to see if it is NULL. select Col1, Col2, Col3 from MyBigTable where Col1 IS NOT NULL and Col2 IS NOT NULL and Col3 IS NOT NULL A trickier and possibly performance-killing solution would be to add a computed bit column to the table that gets set to true if any column is not ...


0

Have you tried to compare the column to Null ? SELECT * FROM table WHERE col IS NULL If does not work, try to disable the ANSI_NULLS option.


8

By using the ROW_NUMBER() ranking function, you could achieve that. Here's an example below. The top two queries are just for data verification. I'm a firm believer that you should see what you will be deleting (and what will remain) before actually deleting data. (Verification) Rows that will be deleted ;with cte as ( select *, ...


0

Could also use 'Between' as a simpler syntax in this case. Execution plan would probably still be the same.


2

Here is a way of How to calculate the Result Set data size If using Management Studio: Go to Menu >> Query >> Include Client Statistics Once you enable the client statistics, it will display another tab in the result span; when clicked, we obtain the following: As shown in the image, it will capture details for different trials and they are compared ...


1

I have tested Binaya Regmi's answer and it worked but I also ran across a blog posting that helped me develop, what I think, is a better solution. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ I added AND (do.DlvryDt >= @StartDate OR @StartDate IS NULL) AND (do.DlvryDt <= @EndDate OR @EndDate IS NULL) to the where clause and ...


3

Assuming that your start date and end date are compared against DlvryDt in DeliveryOrder table, I suggest to use a very low date value for start date and a very high date value for end date in cases where these date variables are null. Please see following query. SELECT do.OrdrNmbr AS 'Order Number', mb.BOLNmbr AS 'BOL Number', do.DlvryDt ...


2

If you are trying to insert into a new table that has already been created, you can say: ;WITH Nbrs ( n ) AS ( SELECT CAST(1 as BIGINT) UNION ALL SELECT 1 + n FROM Nbrs WHERE n < 480000) INSERT dbo.ExistingTable(First,Second,Third,Fourth,Fifth,Sixth) ----^^^^^^ this line SELECT tblOrderRandomNumbers.[1] AS First, ...


4

MERGE doesn't seem to be what you want here. You're only inserting new rows with a CREATION_DATE from the past 24 hours, right? SET NOCOUNT ON; DECLARE @Yesterday DATE = DATEADD(DAY, -1, GETDATE()); INSERT dbo.ANNOUNCEMENTS(column list) SELECT column list FROM [LINKEDSERVER].[DATABASE].[dbo].[ANNOUNCEMENTS] AS r WHERE CREATION_DATE >= @Yesterday ...


1

It depends on the targeted SQL engine. Mainly because each engine has different approaches when it comes to scanning full tables, and using indexes. If we talk about a non-indexed table or a hint that forces a full-table scan, there is not really a difference between the two examples, because at a lower level they're both filters, and will be treated in the ...


1

I solved it myself. In case someone runs into a similar problem: [dbo].[MemoCount] also has an ID Column. so i changed AND ([Reference] COLLATE SQL_Latin1_General_CP1_CI_AS = [ID])) to: AND ([Reference] = [Foobar].[dbo].[Expense].[ID])) AS [MemoCount] Apparently SQL took the column [dbo].[MemoUnion].[ID] to compare it with [Reference]. That problem ...


0

It depends, but options include (and are not limited to): Restore a backup of the linked database on the local instance (for better uptime, restore to a new name, then DROP DATABASE the old one and then sp_rename the fresh restore), and either create synonyms to the restored copy from your current local database so as to allow current queries to continue ...


0

Microsoft SQL Servers do not allow the use of User Principle Name (UPN) addresses for service accounts. There is a post from a few years ago in Microsoft Connect and a more recent post: https://connect.microsoft.com/SQLServer/feedback/details/519008/upn-user-principal-name-as-services-account-name#details ...


0

For 10 values where you have no index, this is such a micro-optimization that you shouldn't worry about it. The database has to read all the rows, and those reads are going to be less efficient that doing a few comparison operations. The first can more efficiently be written as: where KeyColumn in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) This should have the ...



Top 50 recent answers are included