Tag Info

Hot answers tagged

11

The following explanation is given in this Microsoft Technical Article: Why does the first index on a view have to be CLUSTERED and UNIQUE? It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must ...


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


4

As of SQL Server 2008, IIS is no longer used by SQL Server Reporting Services. It utilizes the HTTP.sys API now. So you will not see SSRS show up as a site or application pool. The tip you linked to deals with SQL Server 2005, which required IIS in order to use SSRS. This tip speaks of the slowness you might be seeing and how to address it in SQL Server ...


4

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control. The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential. Only after an index has more than eight pages will it be allocated additional pages from uniform extents. At fewer than 1,000 rows, your clustered index ...


4

In your MS SQL Server Management Studio TOOL -> SQL Profiler There are some minor details about choosing rows/commands and columns but a quick read about it on MSDN and you can start to see what is being sent to the engine to be executed. Obs:. It ill sniff ANY command sent to the engine from any user and application for any DB on that server.


3

That error is simply telling you that the volume(s) you have tempdb on is/are full. Unless you have explicitly altered the file layout of tempdb it will all be on C:. You must be pretty low on space where tempdb is unless the amount of rows for that 24 hour period is massive. Anyway, spooling large amounts of data into a temporary table onyl to delete most ...


3

Assuming the original table design is somewhat like this: CREATE TABLE dbo.Area ( RowID integer PRIMARY KEY, GroupID integer NOT NULL, ParentID integer NULL, ); Sample data: INSERT dbo.Area (RowID, GroupID, ParentID) VALUES (1, 1, NULL), -- Root (2, 1, 1), (3, 1, 2), (4, 2, NULL), -- Root (5, 2, 4), (6, 2, 5), ...


3

You have to specify that you are inserting wide character data: CREATE TABLE #t (id INT,c1 VARCHAR(MAX),c2 NVARCHAR(MAX)); INSERT INTO #t VALUES(1,'žđšćč žđćčžđšćčŽĐŠĆČ','žđšćč žđćčžđšćčŽĐŠĆČ'); INSERT INTO #t VALUES(2,N'žđšćč žđćčžđšćčŽĐŠĆČ',N'žđšćč žđćčžđšćčŽĐŠĆČ'); SELECT * FROM #t; DROP TABLE #t; Result:


3

I played around a bit to figure out what these settings do... when I commented, I only mentioned what our settings are without understanding them; I'm not the project lead for our migration to Database Projects, so I wasn't familiar with the minutiae of this stuff. The Model Aware property needs to be set to True. This property is poorly documented, but ...


3

Is there some table in MS SQL 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going? The simplest way to do this is the query the management views associated with the currently-executing request, the session, or the connection: sys.dm_exec_requests sys.dm_exec_sessions ...


3

The main recommendations appear to be: If you have the install discs / files, then you should be able to install just the Client Tools. Download SQL Server 2012 (SP1) Express (I would try the "SQL Server Management Studio Express (Tools only)" version first) Check out: http://www.mssqltips.com/sqlservertip/1807/sql-server-2008-client-tools-installation/ If ...


2

This is a known issue when using SQL Server 2008 to search for a number with more than nine digits. When using certain word-breakers, the number is stored in its scientific form e.g. nn5d68952e+009, leading to false-positive matches. One of the word-breakers affected is the neutral language (lcid 0): -- Result includes nn5d68952e+009 SELECT display_term ...


2

When trying to return the column back to NOT NULL SQL Server immediately checks the constraint, which fails because the rows for that column were set to NULL. For an existing column, you have to update the table to remove any NULL values before you can set it as NOT NULL.


2

As the error message suggests, OPENQUERY looks for a linked server. If you don't have that set up, and don't have the rights to get that set up, then perhaps using an OPENROWSET query might be the way to go. This will allow you to connect to a datasource on a more ad-hoc basis, by writing a query which contains a full connection string. In your case, you ...


2

Here is the sample for DDL trigger that captures SQL query too: CREATE TRIGGER Audit_DDL ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS DECLARE @event xml; SET @event = EVENTDATA(); INSERT INTO Audit_DDL_Events VALUES ( REPLACE(CONVERT(varchar(50), @event.query('data(/EVENT_INSTANCE/PostTime)')), ...


2

How can I be seeing shared locks? Is it because of foreign keys? Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of validating foreign key constraints. This is required for correctness, and cannot be disabled. The behaviour applies only to data-modification statements. ...


2

I don't think collation is your problem as that is related to sorting and ordering (although you might get a problem later using ORDER BY and so on), but you mention you have some VARCHAR fields. Those will not accept Unicode characters (which Cyrillic certainly are): They will need to be NVARCHAR throughout to do that. Next, how are you inserting your ...


2

Although you had SORT_IN_TEMPDB = OFF, that is not the only thing that uses tempdb. You also had set ONLINE = ON. Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb with row versioning data. This is described here: http://technet.microsoft.com/en-us/library/ms179542.aspx ...


2

Does it mean that former primary replica (A) will become primary replica automatically? No, all that means is when your replica comes back into the picture, and when the Availability Group database gets back in a SYNCHRONIZED state that it would be failover ready. That operation will not happen automatically. You indeed would have to either do this ...


2

I don't think you'll be able to find that out. SQL Server doesn't track this information by default (e.g. in the default trace or system health extended event session), and in fact if you go through the UI and click the Script button instead of saying OK, you will see it just generates a script like this, which writes directly to the registry: USE [master] ...


2

Try changing you statement to match this: FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)') This uses the .value xml method to solve your entitization problem.


1

I believe you will see this symptom if you have a LOT of large query plans that are fighting for memory in order to compile (this has very little to do with running the query itself). To hit this, I suspect you are using an ORM or some kind of application that generates many unique but relatively complex queries. SQL Server could be under memory pressure ...


1

I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join. So in your case change the top row from DELETE category.* to DELETE category


1

The interesting point here is not that TempDB is full, it is why it is filling up. The reason being the GROUP BY UserID needs to do a sort and that's spilling out of memory and into TempDB. With 1.8 Billion rows I'm not surprised. The fix is to reduce the date range you query to what will fit comfortably in TempDB. Let's say this turns out to be a week. ...


1

There is no point in updating your statistics if you are rebuilding them. The statistics for an index are updated if the index is rebuilt. Another problem is that the rebuild index job in the Microsoft maintenance plans are not intelligent so they just rebuild all indexes whether they need it or not. Brent Ozar talks about this here ...


1

You don't say but I'm guessing that your log files are on your D: drive and what you're seeing here is writes to the transaction log files, not to the database files i.e. to ldf not to mdf. You won't see the mdf files written until the DB hits a CHECKPOINT, which could be some minutes later. The linear increase is good and what you'd hope for. That you ...


1

It appears that this was added in the 2008 time period. You can see the Connect item with the title "MSIT-MSO: Use NOLOCK while querying the cdc._CT table in CDC TVF" at: http://connect.microsoft.com/SQLServer/feedback/details/331486/msit-mso-use-nolock-while-querying-the-cdc-captureinstance-ct-table-in-cdc-tvf It is interesting that the request was made ...


1

There's no specific support for this in Transact-SQL. You could write your own function using CHARINDEX. Alternatively, you could use a library that someone else has already written, for example the SQLsharp SQLCLR extension library. Among the over 240 functions and procedures is SQL#.String_NthIndexOf: -- Result: 13 SELECT SQL#.String_NthIndexOf ( ...


1

These two queries may help you. The first will list all of the tables and indexes on those tables in your database. If the table does not appear in the list is does not have any indexes defined on it. These queries assume SQL Server version 2005 or newer. SELECT I.name AS IndexName , T.name AS TableName , I.is_primary_key AS IsPrimaryKey ...


1

I am not sure if you're interested in all constraints but INFORMATION_SCHEMA.TABLE_CONSTRAINTS doesn't seem to return the DEFAULT constraints -- TABLE_CONSTRAINTS (Transact-SQL) CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY This query will do a simple count against the sys.objects DMV: select COUNT(*) from sys.objects o where o.type_desc like ...



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