Tag Info

Hot answers tagged

6

;WITH x AS ( SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID) FROM dbo.your_table ) SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL FROM x WHERE VAL < Prev_VAL ORDER BY ID; If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is ...


5

Making the assumption that you are using SQL Server (because the RDBMS is going to matter here) you can do the following ALTER TABLE tablename REBUILD That being said you can read this article by Paul Randal as to why you shouldn't. Unless you are using your table as a staging table where you want a quick import but then clean the table out later anyway ...


4

This should be good up to about 2,500 values (depending on version): ;WITH x(n) AS ( SELECT TOP (@variableNumberOfIdsNeeded) (ROW_NUMBER() OVER (ORDER BY number)-1) * CONVERT(BIGINT, @SeqIncr) + CONVERT(BIGINT, @FirstSeqNum) FROM master.dbo.spt_values ORDER BY number ) --INSERT @newIds([NewId]) SELECT n FROM x; If you need more, or ...


3

Oh Boy! System Databases on C Drive are recipe for disaster ! Change the default database location away from C:\ drive. You can do it using ALTER DATABASE .. MODIFY FILE Change the db file location ALTER DATABASE db_name MODIFY FILE (NAME = logical_data_file, FILENAME = 'D:\data\physical_file.mdf'); ALTER DATABASE db_name MODIFY FILE (NAME = ...


3

When comparing values of different datatypes SQL Server follow the Data Type Precedence rules. Since nvarchar has higher precedence than varchar SQL Server has to convert the column data to nvarchar before comparing values. That means applying a function on the column and that would make the query non-sargable. SQL Server does however do it's best to ...


3

Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway. Example: IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL DROP TABLE ...


2

You could try an indexed view. I know you said you couldn't add an index to the table but this would be creating a view and then indexing it. CREATE VIEW vw_Message WITH SCHEMABINDING AS SELECT month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType, COUNT_BIG(*) AS MESSAGE_COUNT FROM dbo.Message where ...


1

Had a situation where a production environment had DB schema updated The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why. Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ...


1

I did this once as an exercise in dynamic SQL. It's NOT a good idea for any production system. Primarily you add a huge risk of SQL Injection. This is where someone would pass an actual SQL command in as part of one of the parameters and you end up running it without realizing it. You can minimize this but even so there is the risk. Also there is a ...



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