I have a table with CreateDate datetime field default(getdate()) that does not have any identity column. I would like to add identity(1,1) field that would reflect same order of existing records as CreateDate field (order by would give same results). How can I do that ? I guess if I create clustered key on CreateDate field and then add...
I have several tables: CREATE TABLE [dbo].[Tracks]( [Id] [uniqueidentifier] NOT NULL, [Artist_Id] [uniqueidentifier] NOT NULL, [Album_Id] [uniqueidentifier] NOT NULL, [Title] [nvarchar](255) NOT NULL, [Length] [int] NOT NULL, CONSTRAINT [PK_Tracks_1] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STA...
I need a sequence of numbers for an application, and I am hoping to leverage the abilities of SQL Server to do it. I have created the following table and procedure (in SQL Server 2005): CREATE TABLE sequences ( seq_name varchar(50) NOT NULL, seq_value int NOT NULL ) CREATE PROCEDURE nextval @seq_name varchar(50) AS BEGIN DECLAR...
i'm running a select query in Query Analyzer. While running SQL Profiler i see that the query took 2,027 reads: EventClass: SQL:BatchCompleted TextData: SELECT Transactions.... CPU: 422 Reads: 2027 Writes: 0 Duration: 466 i ran the query with the SET STATISTICS IO ON option, and i get nowhere clos...
When measuring performance on my query I came up with a dependency between isolation level and elapsed time that was surprising to me READUNCOMMITTED - 409024 READCOMMITTED - 368021 REPEATABLEREAD - 358019 SERIALIZABLE - 348019 Left column is table hint, and the right column is elapsed time in microseconds (sys.dm_exec_query_stats.tot...
I have a SQL query in my ASP.net web app that looks like this: SELECT * FROM [Records] WHERE ([title] LIKE '%' + @title + '%') @title, of course, is the value of a text box on the page. My question is, why, when the text box is empty, does this return nothing? And how can I make it return everything, like logic tells me it ought to? ...
Can I write the same queries (select, insert, update etc.) in LINQ to Entites that will be validate for SQL SERVER and Oracle database? I thinking that if I write now query for SQL SERVER, it will be ok for future Oracle db...? It's exist pattern which provide interface for something like that ? ...
How can I return a identity column with a standard SQL CLR Split UDF for example the code below will return a table with the string value split by delimiter, I need to somehow return an identity column as well. <SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="value nvarchar(4000)")> _ Public Shared Function GetStrings(ByVal ...
In reference to this question that I just asked, http://stackoverflow.com/questions/2451579/sql-select-all-when-filter-value-is-empty, it appears that for some reason, an empty text box's value is not being fed to SQL Server as NULL, as it ought to be. Any ideas on how to fix this? ...
I have MSSQL 2008 installed on my local PC, and my Java application needs to connect to a MSSQL database. I am a new to MSSQL and I would like get some help on creating user login for my Java application and getting connection via JDBC. So far I tried to create a user login for my app and used following connection string, but I doesn't w...
An application that I support has recently begun experiencing extended periods of time required to execute a report in SQL Server Reporting Services. The reports that are being executed are not terribly complex. There are multiple stored procedures (between 5 and 8) which return anywhere from a handful to 8000 records total. Reports ...
Using Access Database Table ID Time 001 100000 001 100005 001 103000 001 102500 001 110000 001 120000 001 113000 ..., From the above table, i want to take first four time Query like Select id, min(time) from table group by id I want to take first four min(time) for each person Expected Output ID Time 001 100000 001 10...
Is it be possible to update two tables writing a single query? So that i do not have to execute two queries and to track whether both are successful? ...
Hello, I have a table called Table with columns: ID (int, primary key, clustered, unique index) TEXT (varchar 15) on a MSSQL linked server called LS. Linked server is on the same server computer. And: When I call: SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM Table') It takes 400 ms. When I call: SELECT ID, TEXT F...
Using MS SQL Server.I have made a Stored Procedure named SP_Get_CallsLogged. I have a table named TRN_Call, and it has one column named CallTime which is a DateTime. I have a web-page in my application where the User enters:- StartDate (DateTime) EndDate (DateTime) Period (Daily/Weekly/Monthly) (varchar) (from DropDownList) I want ...
When getting SQL DateTime Resharper suggests to use new DateTime() when value is DBNull.Value. I've always used DateTime.MinValue. Which is the proper way? DateTime varData = sqlQueryResult["Data"] is DateTime ? (DateTime) sqlQueryResult["Data"] : new DateTime(); ...
Hi there, I am doing some parallel SQL Server 2005 database restores in powershell. The way I have done it is to use cmd.exe and start so that powershell doesn't wait for it to complete. What I need to do is to pipe the output into a log file with append. If I use Add-Content, then powershell waits, which is not what I want. My code sn...
I have the following scenario: 2 DB servers (Linked to each other) DB1 has a (large) table with transaction records DB2 has a (not quite as large yet) table with transaction records (of a similar nature but without some of the data as it is a different system) There are a bunch of reports that pull records out of the transaction table ...
I've got a function written by another developer which I am trying to modify for a slightly different use. It is used by a SP to check if a certain phrase exists in a text document stored in the DB, and returns 1 if the value is found or 0 if its not. This is the query: SELECT @mres=1 from documents where id=@DocumentID and contains(...
I'd like to be able to convert SQL Server query plans from XML to images. Ideally a vector format, but a bitmap would do. Is there an open source library to do this? Or can I use one of the SQL Server Management Studio DLLs? Thanks. ...