Tag Info

Hot answers tagged

19

Without seeing code, it is pretty hard to say conclusively what is happening. Although, most likely the IDENTITY value is being cached, causing gaps in the value after SQL Server is restarted. See http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server for some good answers and info about that. A simple INT ...


10

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT h.TransactionID, h.ProductID, h.TransactionDate FROM Production.TransactionHistory h WHERE h.ProductID = 800; ...


8

APPLY TOP or ROW_NUMBER()? What could there possibly be more to say on that matter? A short recap of the differences and to really keep it short I will only show the plans for option 2 and I have added the index on Production.TransactionHistory. create index IX_TransactionHistoryX on Production.TransactionHistory(ProductID, TransactionDate) The ...


7

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed. As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to ...


7

You have a heap. Heaps don't clear out space with DELETEs in most cases. You could truncate the table, or you could put a clustered index on the table. Heaps are great for insert-heavy systems, but not great if there are lots of deletes.


7

You really need to look at the definition of sp_spaceused to find your answer of why unused is zero. exec sp_helptext 'sp_spaceused'; go Take a look at a snippet here of the stored procedure: begin /* ** Now calculate the summary data. * Note that LOB Data and Row-overflow Data are counted as Data Pages. */ SELECT ...


6

I took a slightly different approach, mainly to see how this technique would compare to the others, because having options is good, right? The Testing Why don't we start by just looking at how the various methods stacked up against each other. I did three sets of tests: The first set ran all queries with no DB modifications The second set ran the queries ...


6

Multiple ways to get this information: SELECT APP_NAME(); SELECT PROGRAM_NAME(); SELECT [program_name] FROM sys.dm_exec_sessions WHERE session_id = @@SPID; Just keep in mind that it can be spoofed in the connection string or in Management Studio's connection properties. If I connect using the following parameter, all three of the above will return ...


6

This is a sort of bin-packing problem, so you'll most likely need to choose from one of the available approximate solutions, rather than attempting an exhaustive search. One very straightforward idea is to pack projects into groups choosing the project with the largest number of employees each time. As soon as the current project no longer fits into the ...


6

No SQL Server does not hold a latch. The storage engine keeps track of the current index scan position using a "cookie". The cookie is revalidated if another process takes a latch (of a type that means the page might have been changed) on the same page since the cookie was acquired. If the cookie (scan position) is no longer valid, the b-tree structure is ...


5

The main task to do is to find the root cause why the current value is that high. The most reasonable explanation for SQL Server versions prior to SQL2012 -assuming you're talking about a test database- would be that there was a load test followed by a cleanup. Starting with SQL2012 the most probable reason is due to several restarts of the SQL Engine (as ...


4

The main disadvantage of high-performance mode is going to be your possibilities of failover. Because the communication between the principal and mirror server is now asynchronous with high-performance mode, the only way to failover is with a forced failover and possible data loss. If you had to do a forced failover to the mirror server, any transactions ...


4

Local table tables should never be shared across sessions. As you've pointed out, they are only visible to the session that created them and are destroyed when the creating session is terminated, or you drop them. Global temporary variables are visible to all sessions, but you'd need to define them with the double hash i.e. ##temp, for them to be defined as ...


4

Per the following article, This information can be found in: Start -> All Programs -> Local Security Policy Then navigate to Account Policies -> Password policy You should see something similar to this: http://www.mssqltips.com/sqlservertip/1088/sql-server-login-properties-to-enforce-password-policies-and-expiration/


4

This isn't an answer to the actual question asked, but commentary too long for a comment. FWIW I don't create SQL Auth logins that obey Windows password policies. I can avoid that simply with: CREATE LOGIN foo WITH PASSWORD = N'bar', CHECK_POLICY = OFF; If I want logins to use Windows-based password policies, I'll make users log in with Windows ...


3

WITH KeepLatest AS ( SELECT *, RANK() OVER ( PARTITION BY CPT4,CPT4Mod ORDER BY Eff_Date DESC, ItemCharge) as Rnk FROM dbo.YourTable ) DELETE KeepLatest WHERE Rnk > 1 ; By using RANK() instead of ROW_NUMBER() you will avoid deleting anything if you have a row which is identical across all four columns - although I'm not sure ...


3

Your index should be on (TrackerId ASC, [Date] DESC) INCLUDE (Position) so that it can easily locate the most recent one for each Tracker. But I really don't like the query from EF. Edit: ...because "most recent" should mean "latest datetime", not "latest identity value"


3

You need to create a multiplication first - every combination of Property + RoomType. In SQL Server we use a CROSS JOIN for that. Then you need to outer join to the junction table, and conditionally display Yes/No based on whether the junction table had a matching row. This should provide the output you want: SELECT p.Property, RoomTypeID = t.Id, -- ...


3

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item. Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first. Matches against these simplified polygons could then be run ...


3

The lifespan of a login with Enforce Password Expiration enabled is set by Windows. I understand that in a Domain, the property is set at the domain level then propogated to the Windows account. I do not believe that you can directly change it.


3

There is nothing native in SQL Server that is going to track that level of information. Based on what version of SQL Server you are using there is SQL Server Audit (SQL Server 2008 or higher) that can track database level access, but it does require Enterprise Edition. You could also roll out your own method of logging using Profiler or Extended Events that ...


3

Overall, the database metadata and the user data that you insert/select/update/delete are in general protected by Transactions, Isolation Levels, Latches, and Locks. Since your question is about metadata, a simple example: Locks queue up as necessary, so that they can be processed in 'order' according what ever constraints exist at the moment. Of course, ...


2

Can anyone tell how exactly apply works and how will it effect the performance in very large data APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically ...


2

An operation which is blocking must wait until all rows have been seen and handled before it can start populating buffers. An operations which is partially-blocking writes data onto new buffers, which only get handled by the next operation once each buffer (typically just under 10,000 rows) is populated. An operation which is non-blocking can have the ...


2

It looks as though you have a problem with your storage subsystem (somewhere from the drivers down to the actual disks, but it could be anywhere in that stack). The good news: Most of the corruption is involved in non-clustered indexes. This means that if the underlying table is clean, the indexes can be rebuilt to fix the corruption. Object IDs ...


2

A better index would be (assuming ORDER BY IdTrackerPosition DESC is correct, and the query should not specify ORDER BY [Date] DESC instead): CREATE UNIQUE INDEX i -- Choose a better name! ON dbo.TrackerPositions (TrackerId, IdTrackerPosition DESC) INCLUDE (Position, [Date], Speed, NbSatellites, Direction, HDOP); The execution plan should look ...


2

You're looking for a pivot. Either of these queries will work: SqlFiddle /* case */ select CustomerId , FirstName =max(case when [Key]= 'FirstName' then Value end) , LastName =max(case when [Key]= 'SecondName' then Value end) , Age =max(case when [Key]= 'Age' then Value end) , Gender =max(case when [Key]= 'Gender' ...


2

After you enable the CDC on a database 2 jobs are created for that database: cdc.DBName_capture (which will start the change data capture collection agent) cdc.DBName_cleanup (which will clean up the change tables periodically) the default retention is 3 days (data older than 3 days is removed) the default schedule for this job is daily at 02:00 AM The ...


2

That's a lot of NTEXT columns. You should know NTEXT is deprecated and you should consider refactoring to the newer datatypes, eg NVARCHAR(MAX). Regarding your specific problem, depending on what version and edition ( eg select @@version ) of SQL Server you are using, you could consider partitioning. Here's a simple demo of this might work for you and my ...


2

Rewriting NOT EXISTS as DISTINCT over an inequality join does allow the view to be indexed, but there are good reasons this is not commonly done. The execution plan generated to build the index on the view is unavoidably horrible. The inequality forces a nested loops physical join, which with the exception of one value, is a cross join. Collapsing the ...



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