The only viable solution in my opinion is to use an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value a computed, persisted column to convert that numeric value to the value you need So try this: CREATE TABLE dbo.tblUsers (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY … Read more
The window functions in SQL 2008 are rather limited compared to later versions and if I remember correct you can only partition and you can’t use any rows/range frame limit but I think this might be what you want: ;WITH cte (rn, transactionid, value) AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY transactionid), transactionid, … Read more
I think the geography method STIntersects() will do what you want: DECLARE @g geography; DECLARE @h geography; SET @g = geography::STGeomFromText(‘POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’, 4326); SET @h = geography::Point(47.653, -122.358, 4326) SELECT @g.STIntersects(@h)
The reason is that the query optimizer does not do a very good job at costing user-defined functions. It decides, in some cases, that it would be cheaper to completely re-evaluate the function for each row, rather than incur the disk reads that might be necessary otherwise. SQL Server’s costing model does not inspect the … Read more
Any number of columns for a given tablename; If you need column names wrapped with <text> DECLARE @s VARCHAR(500) SELECT @s = ISNULL(@s+’, ‘,”) + c.name FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id WHERE t.name=”YourTableName” SELECT ‘<text>’ + @s + ‘</text>’ SQL Fiddle Example here — RESULTS <text>col1, col2, col3,…</text> If you … Read more
I know this is a late answer, but for anyone searching on this like I was, I found this to be the simplest answer: In the lookup connection, use a SQL query to retrieve your data and add UNION SELECT NULL, NULL to the bottom. For example: SELECT CarId, CarName FROM Cars UNION SELECT NULL, … Read more
You can try the following: * Updated ** — Test Data DECLARE @YourTable TABLE(Product INT, Timestamp DATETIME, Price NUMERIC(16,4)) INSERT INTO @YourTable SELECT 5678, ‘20080101 12:00:00’, 12.34 UNION ALL SELECT 5678, ‘20080101 12:01:00’, NULL UNION ALL SELECT 5678, ‘20080101 12:02:00’, NULL UNION ALL SELECT 5678, ‘20080101 12:03:00’, 23.45 UNION ALL SELECT 5678, ‘20080101 12:04:00’, NULL … Read more
Assuming the name is not nullable and that Id is unique so can match at most one row. SELECT ISNULL(MAX(Name),’John Doe’) FROM Users WHERE Id = @UserId
It depends on whether default path is set for data and log files or not. If the path is set explicitly at Properties => Database Settings => Database default locations then SQL server stores it at Software\Microsoft\MSSQLServer\MSSQLServer in DefaultData and DefaultLog values. However, if these parameters aren’t set explicitly, SQL server uses Data and Log … Read more
Something like this IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]’) AND type in (N’U’)) BEGIN CREATE TABLE [dbo].[YourTable]( …. …. …. ) END