After searching stackoverflow.com I found several questions asking how to remove duplicates, but none of them addressed speed. In my case I have a table with 10 columns that contains 5 million exact row duplicates. In addition, I have at least a million other rows with duplicates in 9 of the 10 columns. My current technique is taking ...
According to http://www.storytotell.org/blog/2008/11/14/literal-tables-and-updates-with-joins-in-sql.html the following is valid: SELECT * FROM VALUES ('Lisp', 50, true), ('Scheme', 30, true), ('Clojure', 1, true) AS languages (name, age, lispy) But it doesn't appear to work. The best i c...
SELECT patron_name, producer.fed_number, tax_number, average_tb_test FROM producer INNER JOIN producer_details ON producer.federal_number = producer_details.federal_number INNER JOIN statement ON producer.patron_number = statement.patron_number WHERE producer.patron_number = @PatronNo (SELECT MAX(statement.statement_number), MAX(p...
Got a table with 1200 rows. Added a new column which will contain incremental values - Candidate1 Candidate2 Candidate3 . . . Candidate1200 What is the best way to insert these values , non manual way. I am using SQL Server 2008 ...
Hello, I have a SQL Server 2008 database with 2 tables. These tables are defined like this: Device ------ ID Name Description TotalApplications Application ----------- ID Name DeviceID I recently added the "TotalApplications" column in an effort to speed up a query that is taking WAY too long. The number of applications associated w...
I've created a compound unique index on my table: CREATE TABLE [dbo].[SearchIndexWord]( [ID] [int] IDENTITY(1,1) NOT NULL, [CatalogID] [int] NOT NULL, [Word] [nvarchar](100) NOT NULL, CONSTRAINT [PK_SearchIndexWord] PRIMARY KEY CLUSTERED ( [ID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY...
Say I have a table (id int, Name varchar) of 1000 rows. Now I wish to delete every nth record (every 2nd, 3rd or 5th) . What is the most efficient way to do this ? ...
I am hoping to add some loging to a particular stored proc that is cross called by about 5000 stored procs in 20 databases. What I'd like to to is add to the top of the called stored proc something like: insert into callLog values (@@caller, getdate()) So after a while I can get a nice list of all the stored procs that are calling th...
I have to write a sql script for comparing the difference's with the indexes of table of SQL Server. ...
This is a bit of a replication newbie question. I have a requirement which is not immediate to replicate a database using transactional replication, is there anything I can do / should do up front to make my life easier for when I need to start publishing in anger, short of setting up the full blown topology for this. ...
Hi, I have a query like the following that returns the correct number of rows that I would expect. (It has to match a similar query that returns the same data sets but with different associated info from other related tables. SELECT * FROM LK INNER JOIN STC ON LK.V = STC.VI LEFT OUTER JOIN BC ON LK.BC = BC.ID LEFT OUTER JOIN K AS LT ON ...
SELECT ROUND(123.4567, 2) gives me 123.4600. But I need 123.46. Data type of field is money. SOLUTION <%#DataBinder.Eval(Container.DataItem, "FieldName","{0:0.00}")%> ...
I'm just wondering what is it faster in sql can have a column of Date and to check it for null or to have a Date and a bit and to check the bit for 1/0 is the bit going to be faster ? ...
Is it possible to get input from a user for a variable? For an example: How do i get the firstname of a user to use it in my script to select it from my employee table. I now how i declare a variable but not how to get it typed in by the user of the script ...
Hi, I have written a simple procedure: CREATE PROCEDURE [dbo].[sp_GetPublishedDocs2] @FromDate as Datetime AS BEGIN DECLARE @strSQL VARCHAR(5000) SET @strSQL='SELECT * From Task WHERE 1=1 ' IF @FromDate <>'1/1/1900' SET @strSQL = @strSQL + ' AND Task.CreatedDate >= '+Cast(@FromDate as Datetime) EXEC(@strSQL) END It run succe...
I have a very simple linq query that gets data from two datatables (orderHeader & OrderDetails) and joins them together. What I would like to do is take the order items for each order and pass them to a method for processing. Here is the query: var results = from orderHeader in dtOrderHeader.AsEnumerable() join orderDetails in dtOrderD...
Ok, so I have a query: select distinct(a) from mytable where b in (0,3) What is going to be faster, the above or select distinct(a) from mytable where b = 0 or b = 3 Is there a general rule? Thanks ...
I am looking for a (simple) test(case) management tool like Speed Test (http://speedtest.codeplex.com/) or Testlink (http://www.teamst.org/), which is based on .NET and SQL-Server. The tool should not be complicated and easy to learn. Can anybody recommend me alternatives of the above mentioned tools? ...
I have a column of data I've imported form a lotus notes database which consists of a contacted address field. The items in the address are separated by ASCII (13), ASCII (10) What the easiest way to split this address up in to separate columns? ...
We have a VB.NET (.NET 2.0) windows service application that uses SQL Server DTS to send data to a central server. To access the DTS, we install Microsoft SQL Server 2000 Desktop Engine (MSDE) and then reference the DTS COM component. We can then use then use DTS from within our service: Dim m_pkg As DTS.Package2 m_pkg = New DTS.Pack...