Avg of float inconsistency

The select returns right at 23,000 rows The except will return between 60 to 200 rows (and not the same rows) The except should return 0 as it is select a except select a PK: [docSVenum1].[enumID], [docSVenum1].[valueID], [FTSindexWordOnce].[wordID] [tf] is a float and and I get float is not exact But I naively thought avg(float) […]

Decent simple SQL Server client

does anyone know of a very simple SQL Server client tool – that does the same basic functions as Management Studio (i.e. choose a database and run a query – doesn’t need an Object Explorer, or anything fancy)? Ideally it would be great to have a single exe or zip file version that I could […]

How to select similar sets in SQL

I have the following tables: Order —- ID (pk) OrderItem —- OrderID (fk -> Order.ID) ItemID (fk -> Item.ID) Quantity Item —- ID (pk) How can I write a query that can select all Orders that are at least 85% similar to a specific Order? I considered using the Jaccard Index statistic to calculate the […]

Performance of COUNT SQL function

I have two choices when writing an SQL statement with the COUNT function. SELECT COUNT(*) FROM <table_name> SELECT COUNT(some_column_name) FROM <table_name> In terms of performance, what is the best SQL statement? Can I obtain some performance gain by using option 1?

How to run a database script file from Delphi?

I want to do the following. 1) Create a database. 2) Run a script when creates tables, stored procedures, etc. (this script is created by SMS ‘generate scripts’ option) I found the following code: http://www.delphipages.com/forum/showthread.php?t=181685 and modified it to this: try ADOQuery.ConnectionString := ‘Provider=SQLOLEDB.1;Password=’ + edtPassword.Text + ‘;Persist Security Info=True;User ID=’ + edtUser.Text + ‘;Initial […]

Set a default row prefetch in SQL Server using JDBC driver

I have an application where I want to define the default number of rows to prefetch for a connection, using for both Oracle and SQL Server drivers. The Oracle driver has a the OracleConnection interface, which provides the setDefaultRowPrefetch method to do it, but I didn’t find anything equivalent for the SQL Server driver. There […]

Return total records from SQL Server when using ROW_NUMBER

I would like to return the total number of records in the database so I can set up pagination. How do I return the total number of records in the DB when using the following paging method in SQL Server 2008? ALTER PROCEDURE [dbo].[Nop_LoadAllOptimized] ( @PageSize int = 20, @PageNumber int = 1, @WarehouseCombinationID int […]

IDENTITY_INSERT is set to OFF – How to turn it ON?

I have a deleted file archive database that stores the ID of the file that was deleted, I want the admin to be able to restore the file (as well as the same ID for linking files). I do not want to take identity_insert off the entire table, as the increment by one works great. […]

Remove numbers from string sql server

I have a large database in which I want to do a part string search. The user will enter characters: JoeBloggs. For arguments sake if I had a name Joe 23 Blo Ggs 4 in the database. I want to remove everything in the name other than A-Z. I have the REPLACE(Name, ‘ ‘,”) function […]

CTE in From clause of SQL Query

I need to use CTE query inside from clause of SQL Query See this example: Drop Table #Temp Drop Table #Temp2 Create Table #Temp(name1 text, name2 text) Insert INTO #Temp Values (‘test’,’test’) Insert INTO #Temp Values (‘test’,’test’) select * into #Temp2 from #Temp Select * from #Temp2 Here, I am just inserting rows into temp […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.