天道酬勤,学无止境

sql-server

how to find who locks who in SQL Server 2005

I am working on SQL Server 2005 SP3. From Activity monitor, I notice a lot of processors were locked in suspended mode. do you know how to find who locks my processes?

2021-07-13 17:27:55    分类:问答    sql-server

Updating 4 million records in SQL server using list of record-ids as input

During a migration project, I'm faced with an update of 4 millions records in our SQL Server. The update is very simple ; a boolean field needs to be set to true/1 and the input I have is a list of all the id's for which this field must be filled.(one id per line) I'm not exactly an expert when it comes to sql tasks of this size, so I started out trying 1 UPDATE statement containing a "WHERE xxx IN ( {list of ids, separated by comma} )". First, I tried this with a million records. On a small dataset on a test-server, this worked like a charm, but in the production environment this gave an

2021-07-13 17:15:37    分类:问答    sql   sql-server   sql-server-2008   tsql   data-migration

What version of .Net framework ships with SQL Server 2008?

Does SQL Server 2008 ship with the .NET 3.5 CLR, so that stored procedures written in CLR can use 3.5 features?

2021-07-13 17:15:28    分类:问答    sql-server   sql-server-2008

Conditional sort order in SQL Server windowed function clauses

So, this is not your average 'conditional sort by' question... I have a rather tricky problem here. :-) I want to allow my stored procedure to offer a conditional sort order for the results. Normally this can be done in the following manner: SELECT * INTO #ResultsBeforeSubset FROM MyTable ORDER BY CASE WHEN @SortAscending=1 THEN 'SortColumn' END ASC, CASE WHEN @SortAscending=0 THEN 'SortColumn' END DESC I'd like to do a CASE statement around the actual ASC/DESC, but that doesn't work. The reason the above method works is because, when @SortAscending isn't equal to the given value, SQL server

2021-07-13 16:44:19    分类:问答    sql   sql-server   tsql

WHERE IN (SELECT NonExistingColumnName) causes unexpected behaviour

I recently had a situation where I needed to delete some rows from a table and mis-spelt a column name. There was no error thrown and ALL the rows were deleted from the table. Here is a script that reproduces the issue. There is an Order table with four orders with OrderID. There is a LIST_TO_DELETE table with one ItemID. I should have used DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT ItemID FROM #LIST_TO_DELETE ) instead I used DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT OrderID FROM #LIST_TO_DELETE ) This caused all rows in the #Orders table to be deleted when I only meant to

2021-07-13 16:24:35    分类:问答    sql-server   sql-server-2008   tsql

CASE statement in where clause in tsql query

I'm trying to write a case statement in the where clause for a query I am working on. I am importing the code in Crystal reports and I am basically trying to say if the variable 'type' is set to 'create' run for this date range in the where clause else run for a different date range. It keeps giving me an error. I can't seem to identify what is wrong with my syntax here. Help anyone? DECLARE @Date1 DATETIME DECLARE @Date2 DATETIME DECLARE @type VARCHAR(20) SET @Date1 = '2010-1-1' SET @Date2 = '2010-2-1' SET @type = '{?DateType}' select * from filled WHERE (CASE WHEN @type = 'create' THEN

2021-07-13 15:52:13    分类:问答    sql   sql-server   tsql   crystal-reports

Assign single variable a multiple rows using Select query in stored procedure

My query is to assign single variable a multiple rows using Select query in stored procedure For example: I get 10(say 1 to 10) employee ids from from Employee table declare @id int select @id =EmpId from Employee select @id This will return 10 rows(i.e.Employee id , suppose there are 10 rows in a table) now my question is how i will get this all rows in a single variable and use that employee id one by one to perform some calculation.

2021-07-13 15:39:41    分类:问答    sql-server

How to check if change tracking is enabled on a specific table

For some reason our build system gives me an error saying change tracking is ALREADY enabled on a table I want to enable it on. ALTER TABLE [dbo].[MyTable] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF) I did a search but could't find something like an IF EXISTS to say first see if it is enabled or not, if it is not enabled then run that ALTER command.

2021-07-13 15:27:17    分类:问答    sql   sql-server

TSQL - make a literal float value

I understand the host of issues in comparing floats, and lament their use in this case - but I'm not the table author and have only a small hurdle to climb... Someone has decided to use floats as you'd expect GUIDs to be used. I need to retrieve all the records with a specific float value. sp_help MyTable -- Column_name Type Computed Length Prec -- RandomGrouping float no 8 53 Here's my naive attempt: --yields no results SELECT RandomGrouping FROM MyTable WHERE RandomGrouping = 0.867153569942739 And here's an approximately working attempt: --yields 2 records SELECT RandomGrouping FROM MyTable

2021-07-13 15:24:32    分类:问答    sql   sql-server   tsql   floating-point   literals

What is the most correct way to store a “list” in a SQL Database?

So, I've read a lot about how stashing multiple values into one column is a bad idea and violates the first rule of data normalisation (which, surprisingly, is not "Do Not Talk About Data Normalisation") so I need some help. At the moment I'm designing an ASP .NET webpage for the place I work for. I want to display data on a web page depending on what Active Directory groups the person belongs to. The first way of doing this that comes to mind is to have a table with, essentially, a column containing the AD group and the second column containing what list of computers belong to that list. I've

2021-07-13 15:17:10    分类:问答    sql   sql-server   database   database-design