Tag Info

Hot answers tagged

12

There is no magic solution to this type of problem. To avoid a potentially expensive sort, there has to be an index that can provide the requested order (and the optimizer must choose to use that index). Without a supporting index, the best SQL Server can do natively is to restrict the qualifying rows (based on the WHERE clause) before sorting the resulting ...


11

In SQL Server 2008, converting binary to a character representation became a lot faster and easier: CREATE TABLE dbo.X ( pk integer PRIMARY KEY, c1 integer NOT NULL, rv rowversion NOT NULL, rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1) ); Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to ...


8

There are a couple of ways to go about this, but natively it sounds like Central Management Servers is a pretty good fit for you. If it is just "one" script and "one" functionality to pull the information, consider writing a quick PowerShell script that loops through all of your servers (the list of servers can be contained in a text file or a database ...


6

You can use DBCC CHECKIDENT to reseed the IDENTITY column. Here is a sample you can run: SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.foo(ID INT IDENTITY(1,1)); GO INSERT dbo.foo DEFAULT VALUES; GO 100 -- note: set it to ([the next value you want] - 1) DBCC CHECKIDENT(N'dbo.foo', RESEED, 499); GO INSERT dbo.foo DEFAULT VALUES; GO 3 SELECT ID FROM ...


5

Updated to satisfy the new requirement. Note that you may be tempted to use CONVERT(TIME, but resist - this is brittle, in the event any job runs longer than 24 hours. SELECT job_name = name, avg_sec = rd, avg_hhmm = CONVERT(VARCHAR(11),rd / 60) + ':' + RIGHT('0' + CONVERT(VARCHAR(11),rd % 60), 2) FROM ( SELECT j.name, rd = ...


4

Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially: UPDATE existing rows INSERT missing rows You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted. Beyond that there are different ways to ...


3

Most likely the issue is that the ELSE is returning BirthDate instead of 0. If there is already data in the table that conflicts, then add the WITH NOCHECK clause if you want to keep that data, else first delete any rows that conflict with the new constraint. Also, best to use MONTH instead of M for the date part, for easier readability.


2

In your place I would take a step back and question the requirement. Your square peg will only marginally fit the round whole. Consider filtering and search instead of sort and paging. Is better for the back end and is better for the user. Nobody is really interacting with 10 mil rows by sorting by column Foo and navigating to page 312. Strong search is a ...


2

Listen to your adviser. By restoring a backup, you are essentially replacing the database schema and data. You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY. Once your backup is in place, put the DB back into HA and ...


2

You can use the following MSDN_DBCC CHECKIDENT Hope that helps! DBCC CHECKIDENT (Transact-SQL) You can use DBCC CHECKIDENT to manually set a new current identity value for the identity column. DBCC CHECKIDENT ( table_name [, { NORESEED | { RESEED [, new_reseed_value ] } } ] ) [ WITH NO_INFOMSGS ] Example USE AdventureWorks2012; ...


2

I suspect that there's a database error that you're not seeing here. If you remove the GO statement right before the two final UPDATE statements, I'd imagine that the query crashes with something like "unknown column 'group_id'". This happens because an execution plan is generated for each batch at a time, i.e. every time you pass GO. In my experience, ...


2

You should never try to update system tables directly, in most cases it is not going to let you as you have found. In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query ...


1

There's no answer that applies in all cases. In general, however... If the lookup list is small and you can cache it(or use a cache data source), there's not much performance penalty to doing it in SSIS. If you want crossref a list of 50 location codes to names of cities, go for it. It's nice to see all the process on-screen in one place, rather than buried ...


1

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join? Something like this: SELECT * INTO #temporary FROM ( SELECT CodJob, Quant, TipoCons, Perc FROM tblConsolidadoJob ) AS SourceTable PIVOT ( MAX(Perc) FOR TipoCoins IN ...


1

GO (Transact-SQL) Signals the end of a batch of Transact-SQL statements to the SQL Server utilities. RETURN (Transact-SQL) Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed. When ...


1

I think the easiest thing to do is to create a process that creates a log of how much stock was bought in a day and how much was left at the end of the day. You can then run your analysis on this log. The analysis could look at the last week or however long you want to determine what the average number of items sold were. You could then compare that to ...


1

You cannot, if you are using a generic username to log in to SQL Server. Host name is as close as you can get. It shouldn't be hard to translate it to a user name with a query in Active Directory.


1

Did CTE with RANK/OVER: Rank3PM = RANK() OVER (ORDER BY [3PM per Game]) and in the select I ended with: TotalPoints = Rank3PM + RankREB + RankAST.. Thanks.



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