Hi All, I have two choices when writing a SQL statement with COUNT function. SELECT COUNT(*) FROM SELECT COUNT(some_column_name) FROM In terms of performance what is the beast SQL statement? can I obtain some performance gain by using option: 1 ? Thanks, Upul ...
Hi all, I am currently updating a MS SQL 2000 server to SQL 2008. One of the issues highlighted by the Upgrade advisor is that the undocumented table sysxlogins has been removed. I currently have a procedure that is run by a user 'foo' to determine if the user 'bar' exists in the database blah. If the user exists the user's password is...
As I mention, I'm interested to know what do you (as experienced developers) thinks about the use of DAO pattern, specifically within a Web Application. If possible what advantages have you found, or consequences of it use. Thanks. ...
I need to delete duplicate rows only from the table, like I have 3 duplicate rows in the table, my query will delete 2 rows from 3 duplicated rows. How can I get this? Please help me. Thanks ...
Hi All, I have a problem writing a query. At the moment I can retrieve an hourly recording of percentages for each machine running and I've got a table which shows, on a daily basis, each percentage recorded for each machine per hour. The code looks like this: ....... WHERE (tblCheckResult.DateTime >= @DateFrom) AND (tblCheckResu...
Structure of Example table: Id, Integer (PK) Name, Varchar(100) Description, Text I need to know if exists difference in performance between: SELECT COUNT(*) FROM Example; and SELECT COUNT(Id) FROM Example; Or does not exists differences? ...
Hello, I am trying to find out what my options are for SQL Server hosting. The reason I am asking is because it always seems to be a problem getting the right kind of hosting for SQL Server. I mostly need a few small databases(<3Mb) for testing or to temporary store some data for clients when I am working on their website. I could just...
We have TABLE A partitioned by date and does not contain data from today, it only contains data from prior day and going to year to date. We have TABLE B also partitioned by date which does contain data from today as well as data from prior day going to year to date. On top of TABLE B there is a view, View_B which joins against View_C,...
Hi , i have a script which simply connect to the table in sql*plus and inserts a row in the table. it is throwing an error as below: SP2-0552: Bind variable "BIND" not declared i am not able to figure out exactly what the bind variable is in the query that it is trying to insert. ...
Hi, I am having an issue whereby a column within a table of mine has data which is encoded using the System.Xml.XmlConvert.Encode method. Now I need to manipulate this data within SQL and have not found a way to duplicate the System.Xml.XmlConvert.Decode method. So I've been investigating how I can use the System.XML namespace within S...
Sometimes one when creating SQL queries one assumes that one of the Index should be used by the engine when getting the data. But not always. If some of the necessities are absent the engine will probably process the rows one by one. Is it a way (for sqlite) to know for sure that the index was used? I mean in cases more complex than SELE...
I am creating a classifieds website called 'mySite', and I want whoever searches for honda +mySite in google, to find all ads with the description 'honda' or headline 'honda' from my database. How is this done? (a htm page for every ad? which then loads the 'ad data' when user clicks to open the htm page?) I have an example for yo...
After I copy mdf file (and his log file) I tries to Insert data. I receive the following message: "An attempt to attach an auto-named database for file [fileName].mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. When I copied the file manual everything worked normally....
We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It is sent by one long string command. If one...
I have an sql query pulling a few results from different tables, one of the tables - "orders" - contains customer details with an order_id as a PK, another table - order_products - contains each product ordered, with reference to the order_id and another column as PK. Basically, I need to know how to query the database in such a way tha...
Good afternoon, I have two massive tables with about 100 million records each and I'm afraid I needed to perform an Inner Join between the two. Now, both tables are very simple; here's the description: BioEntity table: BioEntityId (int) Name (nvarchar 4000, although this is an overkill) TypeId (int) EGM table (an auxiliar table, in...
OK So I have this list of products that are in the cart. I am trying to retrieve all the related items to the items in the cart without pulling any items that are in the cart. I wrote this query and it pulls exactly what I want, but its taking like 8 seconds to run. SELECT * FROM cart_product WHERE product_id IN( SELECT product_r...
Several keys in a SQL Server 2005 database I inherited have very high fragmentation percentages. When using the following SQL: select OBJECT_NAME(object_id), avg_fragmentation_in_percent, record_count, * from sys.dm_db_index_physical_stats (DB_ID(N'FragmentedDB'), NULL, NULL, NULL, 'Detailed') s I see that several tables with fra...
I am trying to find out how busy my sql 2000 server in terms of CPU and IO through a T-SQL query. currently I am using the following to find the CPU Usage %: DECLARE @CPU_BUSY int , @IDLE int SELECT @CPU_BUSY = @@CPU_BUSY , @IDLE = @@IDLE WAITFOR DELAY '00:00:00:500' SELECT (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE...
Can SQL table have multiple columns with primary key? ...