You can use the Row_Number() function. Its used as follows: SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName FROM Users From which it will yield a result set with a RowID field which you can use to page between. SELECT * FROM ( SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName FROM Users … Read more
select convert(nvarchar(255), 4343) Should do the trick.
Run the following and see for yourself: CREATE PROCEDURE dbo.KeepsIsolation AS BEGIN PRINT ‘Inside sproc that does not change isolation level’; DBCC USEROPTIONS; END GO CREATE PROCEDURE dbo.ChangesIsolation AS BEGIN PRINT ‘Inside sproc that changes isolation level’; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; DBCC USEROPTIONS; END GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; DBCC USEROPTIONS; … Read more
You can create a non-nullable DATETIME column on your table, and create a DEFAULT constraint on it to auto populate when a row is added. e.g. CREATE TABLE Example ( SomeField INTEGER, DateCreated DATETIME NOT NULL DEFAULT(GETDATE()) )
I believe this subquery will do it (not tested). select *, (select top 1 Discount from table2 where table2.Date <= t.Date order by table2.Date desc) as Discount from Table1 t Perhaps not the most performant however. Edit: Test code: create table #table1 ([date] datetime, val int) create table #table2 ([date] datetime, discount int) insert into … Read more
Drop the clustered index, then recreate the primary key as non-clustered: ALTER TABLE dbo.Config DROP CONSTRAINT PK_Config go ALTER TABLE dbo.Config ADD CONSTRAINT PK_Config PRIMARY KEY NONCLUSTERED (ConfigID)
I just encountered the same problem. Make sure there is a user mapping for your login in the master table. No database role memberships are required, just tick ‘Map’ for the database master under ‘User Mapping’ in the properties for the login ‘UserName’.
use @@trancount or sys.dm_tran_active_transactions DMV in sql 2005, 2008
I have sometimes been using this “cast” statement to find “strange” chars select * from <Table> where <Field> != cast(<Field> as varchar(1000))
Just discovered this: SELECT * FROM OPENROWSET(BULK N'<PATH_TO_FILE>’, SINGLE_CLOB) AS Contents It’ll pull in the contents of the file as varchar(max). Replace SINGLE_CLOB with: SINGLE_NCLOB for nvarchar(max) SINGLE_BLOB for varbinary(max) Thanks to http://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/ for this!