Tag Info

Hot answers tagged

16

Like you said. It's typically for a hierarchy structure. A good example is a table of employees EmployeeId Int Primary Key EmployeeName String ManagerId Int Foreign key going back to the EmployeeId So in this case there is a foreign key from the table back to itself. All managers are also employees so the ManagerId is actually the ...


11

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion: WHERE [serialNumber] = 137802 According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of ...


10

There is a setting if you right click on "SQL Server Logs" and select "Configure". You can tell it how many log files you want to keep. Once it reaches that number it will start removing the old ones.


8

Yes but not with row_number(). You can use either the window aggregate min(): select id, code, descriptor from ( select id, code, descriptor, min_code = min(code) over (partition by id) from @t ) as t where code = min_code ; Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check): ...


7

SQL Server recycles error logs automatically, as long as you configure it correctly. See http://msdn.microsoft.com/en-us/library/ms177285.aspx . I've copied the salient points below: In Object Explorer, expand the instance of SQL Server, expand Management, right-click SQL Server Logs, and then click Configure. In the Configure SQL Server Error Logs ...


6

Of course you could also write this using a CTE: ;WITH t AS ( SELECT id, code, descriptor, min_code = MIN(code) OVER (PARTITION BY id) FROM @t ) SELECT id, code, descriptor FROM t WHERE code = min_code; You cannot say: SELECT id, code, descriptor FROM @t WHERE code = MIN(code) OVER (PARTITION BY id); Because: Msg 4108, Level 15, State 1, ...


6

If you are using SQL Server 2008 R2 RTM or older then there is a CMD Command "SYSTEMINFO" which has information about System model. OR If cmdshell is enabled on instance then you can execute it from SQL Server Management Studio. DECLARE @result int EXEC @result = xp_cmdshell 'SYSTEMINFO' System Manufacturer: VMware, Inc. System Model: VMware ...


6

Using TOP(n) without ORDER BY is going to return you n rows in whatever way Sql Server cares. As per the documentation: If the query has no ORDER BY clause, the order of the rows is arbitrary. In addition, there is no WITH TIES. Consider using it too, lest you might miss some rows too: WITH TIES Specifies that additional rows be returned from the ...


5

You need to baseline a whole workload and measure before/after. With just the numbers above I have no idea what they mean. The questions I would have include: There were 124,000 updates but did they take 10 times longer than they used to? Did they block users? Is there more activity because people can do more because it's faster, or just a natural ...


5

First thing never set Max Number of Concurrent Connections on server properties page to 1, as this option can even stop administrators to login but still in any case Dedicated Administrative Connection can have access. But if you have changed Max Number of Concurrent Connections value to 1 and now unable to login 1-Restart services using SQL Server ...


5

Assuming you can get access to the box (if you need to recover access, see the link Kin already pointed out), you need to: Drop all user databases. Please run the PRINT first and make sure you're not dropping anything you may regret. You may want to take backups of all databases first just in case. DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N' ...


5

Another way (that meets the requirements and is compatible with 2005+ but should likely not ever be used in practice) is SELECT TOP 1 WITH ties id, code, descriptor FROM t WHERE id IS NOT NULL AND code IS NOT NULL ORDER BY RANK() OVER (PARTITION BY id ORDER BY code); ...


5

Restart SQL Server 7 times. You will see you still have only 7 ERRORLOG* files (depending on version). This is the current log file and the 6 most recent log files. Note: You may need to change 7 to something else, in the event you changed the number of error logs SQL Server keeps. The max, I believe, is 99. But no matter what your current setting is, it ...


5

Add brackets with table, as Group is a reserved word for Group by clause in SQL Server. SELECT GroupID, GroupName FROM [dbo].[Group]


4

Just to be clear at the start: fragmentation refers to when the next logical data page (i.e. the values in the fields for 1 or more rows), is not the next physical data page (the location of page in the data file). Non-fragmented pages (in Physical Page order): Logical Page 1: A1, A2, A3, A4 Logical Page 2: B1, B2, B3, B4 Logical Page 3: C1, C2, C3, C4 ...


4

Your code is valid SQL but not in SQL-Server. They haven't yet implemented the syntax (a,b) IN ((1,11), (2,12), ...) or (a,b) IN (SELECT expa, expb....). You can work around with either an EXISTS subquery: DELETE FROM tableA AS a WHERE EXISTS ( SELECT 1 FROM tableA AS b WHERE b.IDA = a.IDA AND b.IDB = a.IDB HAVING COUNT(*) ...


4

Code gets backup history for all databases on a server and generates HTML report Step 1: DECLARE @Body VARCHAR(MAX), @TableHead VARCHAR(MAX), @TableTail VARCHAR(MAX) SET NoCount ON ; SET @TableTail = '</body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: ...


4

I have to admit I expected this to require a lock on the database which would mean you couldn't do it while someone else was in the database. But just to be certain I did some testing and was able to update the setting while I had an open transaction on a table in the database. So technically yes you could make the change during the day. However, just ...


3

There is no inconsistency: 1048576 * 8 = 8388608 KB => 8 GB; Both Perfmon and sys.dm_os_sys_info show 8 GB; [bpool_visible], [bpool_commit_target] and [bpool_committed] - in SQL 2005 all these are defined as "Number of 8-KB buffers ..." (http://msdn.microsoft.com/en-us/library/ms175048(v=sql.90).aspx), so you have to multiply the result by 8 to get ...


3

You can either use Uri.UnescapeDataString (in System.Net), in which case you will also need to do a Replace('+', ' ') on the string before passing it to `Uri.UnescapeDataString_, or if you would rather not bother with it, this function is available in the Free version of SQL# (which I am the author of). Importing System.Web is probably more work than it's ...


3

As you have noted, System.Web is an unsupported library. In order to reference System.Web you will need to make a call to CREATE ASSEMBLY. It seems like you tried that, but how did you reference the location of System.Web.dll? Did you copy/paste it to a different location? SQL Server will try to locate dependent assemblies in the same location. In other ...


3

For single table you can use sp_spaceused MyTable For all tables in a database you can use it with sp_msforeachtable as follwoing CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ...


3

update a set a.CHK = 1 from tableA a join tableB b on b.IDA = a.IDA and b.IDB = a.IDB


3

It looks like you have an application that isn't properly closing or disposing of the SqlConnection objects. By default, SqlConnection has a max pool size of 100. The fix here would be to work with the application to find out why connections aren't being cleaned up, as they are still "active" in the particular connection pool, which is why you are unable ...


3

While this looks like more code, I think it's more straightforward because it mimics what is asked in English more closely (find the tables without a clustered index, not the tables with an index_id of 0) it doesn't need to apply distinct it avoids pesky metadata functions and awkward functions like objectproperty/ex it includes the schema SELECT [table] ...


3

First I'm assuming since you didn't specify that you want your primary key to be clustered. It's the default and most common usage. If not then the order of your column will depend more on the selectivity of the two columns and your usage. However, if you are making your primary key clustered then if you look in this Technet article on the aligning ...


3

Aaron has answered your question perfectly. I am just adding the full script that I wrote to test in my environment. Below is the script : /************************************************************************************ Author : Kin Shah Purpose : Written for dba.stackexchange.com http://dba.stackexchange.com/q/81505/8783 ...


2

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my ...


2

Most efficient is a fuzzy term. You're talking about updating about 122 million rows. If you start running naive updates now, it will probably finish before you can create a useful estimate. (I'd expect it to take about half an hour on my development machine. Updating 50 million rows takes 12 minutes.) Dropping indexes beforehand is fine if you have your ...


2

I don't need any of users other than having the ability to set an sa password and don't need any of the databases currently on the box. There are ways that you can use to reset sa password or refer to Aaron's article on MSSQLTips. For dropping all the databases, you can just use select 'DROP DATABASE '+ name + char(10) + 'go' from sys.databases where ...



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