Our application provides multiple query interfaces that are basically just text inputs. Is there a best practice on whether the backend logic should be pad the query parameter with wildcards then perform a like or should it just do an equals. Of course another option would be to allow user's to use wildcards and then check and use a "l...
My interviewer asked me Question that i am inserting 10 rows in database table and in some 5th row i find the some trouble then how can i roll back all the records? Please let me know how can i do this ...
Am writing av analytical standard solution register from the doping laboratory that i work for and got stuck on the problem how to get the ipaddress of the client in to the audit table. I have found a straight forward method for actually get the ipaddress on the web and my question is not about that. I got triggers on every table ins...
I have worked on SQL stored procedures and I have noticed that many people use two different approaches - First, to use select queries i.e. something like Select * from TableA where colA = 10 order by colA Second, is to do the same by constructing a query i.e. like Declare @sqlstring varchar(100) Declare @sqlwhereclause varchar(1...
On my MS SQL Server 2008, I've created a temporary table like this : create table #test ( A varchar(50), B int, ) insert into #test select 'a',45 insert into #test select 'b',587 insert into #test select 'a',4 insert into #test select 'b',58 Now the following request take forever select A,SUM(B) from #test group by A -- takes 4 seco...
When I create a new database, by default the files are saved to c:\program files... but I would like them by default to be saved into a different location WITHOUT having to adjust anything. Is there a way to have this done by default? Perhaps there's some stored system procedure that I would have to change? ...
Hi All, I want to do something like this... SELECT DISTINCT T1.* FROM T1 INNER JOIN T2 ON T2.ID1 = T1.ID1 INNER JOIN T3 ON T3.ID2 = T2.ID2 --FOLLOWING CAN BE ADDED MULTIPLE TIMES (LOOPS IN C#?) INNER JOIN T2 AS T2A ON T3.ID2 = T2A.ID2 INNER JOIN T1 AS T1A ON T1A.ID1 = T2A.ID1 --END MULTI WHERE T1.ID1 = 1 AND T3.ID3 = 2 AND T3.ID4 = ...
Though I liked LINQ very much and using in our current project, sometimes we are facing problem to solve the following. Returning multiple results (tables) with single DB call from database and binding the same in ASP.NET. In case of stored procedures, using single SP we can perform multiple operations with single DB call. Using LINQ, ...
Is there a value that used with a LIMIT clause means nothing or no limit? I'd expected that 0 would be that value but it obviously isn't: SELECT * FROM items LIMIT 0 -- returns 0 rows ...
hello.. is there something wrong with this syntax? SELECT * FROM tblcustomer WHERE uName LIKE '%%' AND tMonth = '3' ORDER BY uName ASC i got this error.. Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given.. EDIT include 'settings.php'; $con = connectDB(); $mo = array('Jan','Feb','Mar','Apr','May','Jun'...
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[spGetQualityReport] ( @providerKey INT ) AS -- declare @providerKey INT -- set @providerKey = 1; --Get Database providerId first DECLARE @realProvId INT; SET @realProvId = (SELECT TOP(1) providerId from providerKeyTranslation where keyVa...
I have some tables, Ex.: Car(id, NameCar) and Person(id, PersonName, Address, etc) I need to search for a keyword and say in which columns I found it. Example: Search for Civic, return Person with id 10 have Civic at Car Name, and at Address Name. How can I do this with good performance? ...
When faced with the task of copying a record in a database and modifying just a handful of values, I tend to use a temporary table to avoid writing out all of the unchanged columns. Does anyone know how this would affect performance on a large scale system? A quick example (which also shows why I prefer the temporary table method): Le...
How can this query be optimized for enumerators: SELECT * FROM Customers Table Customers customerId int - has index on it customerName, etc SqlReader that returns a set customers will be read on-demand in an enumerator fashion. While it can return huge datasets that can be read/consumed slowly in a foreach loop, every other query on ...
I have a query that's written dynamically (OO PHP via Joomla) to insert some values into a MySQL database. The form that a user fills out has a field on it for dollar amount, and if they leave that blank I want the value going into the system to be NULL. I've written out the query to the error log as it's running; this is what the query ...
I have a table like id f1 -------------- 1 2000-01-01 1 2001-01-01 1 2002-01-01 1 2003-01-01 And I want to get say the latest 3 dates in one row CREATE TABLE Test ( id INT NOT NULL, f1 DATETIME NOT NULL, ) INSERT INTO Test (id, f1) VALUES (1, '1/1/2000') INSERT INTO Test (id, f1) VALUES (1, '1/1/2001') INSERT INTO Test...
I have a table with over 1 million entries. The problem is with the speed of the SELECT queries. This one is very fast: SELECT * FROM tmp_pages_data WHERE site_id = 14294 Showing rows 0 - 29 (1,273,042 total, Query took 0.0009 sec) And this one is very slow: SELECT * FROM tmp_pages_data WHERE page_status = 0 Showing rows...
Hello, I have a table like the following table: UserID Num1 Num2 Code Name Cat 7293 32:16.0 50:22.0 3 Jim 33 7293 32:16.0 59:28.0 4 Jim 12 7316 32:16.0 55:32.0 4 Karen 33 7316 32:16.0 28:31.0 4 Karen 7 7287 32:16.0 01:27.0 2 Mike 33 7299 32:16.0 18:53.0 4 Sue 16 7302 32:17.0 54:54.0 ...
I am trying to create a select statement that is compatible with all major relational databases (MySQL, PostgreSQL, Derby, SQLite, ...). Here the simple select statement: SELECT * FROM taggings WHERE public IS TRUE The problem is that SQLite for example does not support the boolean data type, so I rewrote my statement to: SELECT * FROM...
I created this select statement I will convert to a view. I need help with this. I need to be able to add the total of Minority that = Yes and No show total on report pages. select ps.BidPackage_ID, ps.Project_ID, SUM (case ps.Minority when 'Yes' then 1 else 0 end) MinorityTotal, SUM (case ps.Gender when 'Female' then 1 ...