|
I wouldn't optimize it! I'd change the data design. You should create another table called Reg_Number_Vehicle_Class that is used to store all of the the different classes you are using. Then create an insert trigger on your premium table to ensure the appropriate class id is assigned to each row. Create a non-unique index on that column and your query should run faster.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
|
|
|
|
|
Don't bother; it's a waste of time. You're doing an arbitrary query on text in the middle of a bunch of strings. Short of rearranging your data, the only significant optimization you're going to get is by throwing more hardware at it.
What does the schema or the rest of the data in the data set look like? Maybe there's some optimization based on the sequences your're looking for vs. the sequences in the data set. It's unlikely, though.
|
|
|
|
|
Halo,
Which tables in SQL server i need to update to create users for database and assign them to a groups? any articles to share.
planning to use this sql script from vb.net to create users 
thanks
Stephen
---------------------
www.islasolutions.net
|
|
|
|
|
miceisland wrote: Which tables in SQL server i need to update to create users for database and assign them to a groups?
You don't update any tables.
You should use the stored procedures:
* sp_grantdbaccess[^]
* sp_addrolemember[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
many thanks. i will try this. 
---------------------
www.islasolutions.net
|
|
|
|
|
Also see: sp_addlogin[^]
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
In my project, I need write data to Server 2000, I use the following code in different places, in most places it works, but in one class, whenever it runs to "writeroRs.Fields...", I get System.Runtime.InteropServices.COMException. Is it locked? Please help, thank you in advance.
Dim writerconnection As ADODB.Connection
Dim writertable As ADODB.Recordset
Dim arecord As addressrecord
writerconnection = New ADODB.Connection
writerconnection.ConnectionTimeout = 300
While writerconnection.State = ADODB.ObjectStateEnum.adStateClosed
Try
writerconnection.Open(connectionstring)
Catch ex As Exception
End Try
End While
Dim writeroRs As ADODB.Recordset
writeroRs = New ADODB.Recordset
writeroRs.Open("test.address", writerconnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable)
writeroRs.AddNew()
writeroRs.Fields("Address1").Value = address1
writeroRs.Fields("Price").Value = price
|
|
|
|
|
my_btr wrote: In my project, I need write data to Server 2000, I use the following code in different places, in most places it works, but in one class, whenever it runs to "writeroRs.Fields...", I get System.Runtime.InteropServices.COMException. Is it locked? Please help, thank you in advance.
Looks like your trying to use VB.NET and ADO to connect to SQL Server. Read up on ADO.NET it has been completely remodled for the .NET languages.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
i want to make datagrid read from datareader but i don't know to join between them
ma_refay
|
|
|
|
|
....
IDataReader reader = cmd.ExecuteReader();
DataGrid1.DataSource = reader;
DataGrid1.DataBind();
...
The code is something like that or at least very close. This solution only works over ASP.NET, Windows Grids can't read data from a DataReader
Greetings,
|
|
|
|
|
Hi Friends, let's straight to question:
This is the table I have, with 2 fields.
Product | Supplier
_________________________
Stationary | A
Stationary | A
Stationary | A
Stationary | B
Stationary | C
Furniture | A
Furniture | A
Furniture | B
Furniture | B
Furniture | B
Books | A
Books | C
Books | C
.......
.....
...
.
=====================================
Below is the COUNTING RESULT I expected after perform a sql query
Supplier | Stationary | Furniture | Books
---------------------------------------------------------
A | 3 | 2 | 1
B | 1 | 3 | 0
C | 1 | 0 | 2
D.......
E.....
F...
...
This the query I worked so far.
-----------------------------------------------------------
SELECT distinct (
SELECT count (*)
FROM Table1 where Product = 'stationary' and Supplier = 'A'
) AS no_of_Stationary, (
SELECT count (*)
FROM Table1 where Product = 'Furniture' and Supplier = 'A'
) AS no_of_Furniture, (
SELECT count (*)
FROM Table1 where Product = 'Books' and Supplier = 'A'
) AS no_of_Books
FROM Table1
UNION
SELECT distinct (
SELECT count (*)
FROM Table1 where Product = 'stationary' and Supplier = 'B'
) AS no_of_Stationary, (
SELECT count (*)
FROM Table1 where Product = 'Furniture' and Supplier = 'B'
) AS no_of_Furniture, (
SELECT count (*)
FROM Table1 where Product = 'Books' and Supplier = 'B'
) AS no_of_Books
FROM Table1
UNION
...... 'C'
..... 'D'
.... 'E'
...
..
. 'Z'
;
----------------------------------------------------------
Result:
no_of_Stationary | no_of_Furniture | no_of_Books
----------------------------------------------------
3 | 2 | 1
1 | 3 | 0
1 | 0 | 2
....
..
.
-----------------------------------------------------------
I don't know how to make another column for the Supplier appear,
and my SQL query is very tedious. Have to repeat for every supplier after each union.
In future, every time a new supplier added, have to create new query again for it.
Someone please help me to figure out the most simplified statement to perform
the expect table result above.
Thanks.
|
|
|
|
|
I don't have time to play around with this and test it out, but I would suggest that you look into SQL's GROUP BY. Search for that and you may get some answers. Here is one site I always keep handy for help with SQL:
http://www.w3schools.com/sql/default.asp[^]
Hope this helps. Sorry I couldn't do more.
|
|
|
|
|
DECLARE @iColumns INT, @sql VARCHAR(2500),@cProduct VARCHAR(10)
SET @sql = ''
DECLARE curProducts CURSOR FOR SELECT DISTINCT product FROM Table1
OPEN curProducts
FETCH NEXT FROM curProducts INTO @cProduct
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT count(supplier) FROM Table1 t2 WHERE t2.supplier = t1.supplier and t2.product = ''' + @cProduct + ''') as ' + @cProduct
FETCH NEXT FROM curProducts INTO @cProduct
END
close curProducts
DEALLOCATE curProducts
SET @sql = 'SELECT Supplier ' + @sql + ' FROM Table1 t1 GROUP BY supplier'
exec(@sql)
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Does the job for me:
SELECT PRODUCT, SUPPLIERS, COUNT(SUPPLIERS)
FROM MYTABLE
GROUP BY PRODUCT, SUPPLIERS
|
|
|
|
|
A table T contains salary of employees. i want to find out
N th highest salary in sal server 2000
SAM>
|
|
|
|
|
Maybe like this:
-- Create temp table with ID and Salary (ID being identity column, starting at 1 and incrementing by 1)
-- Select all the distinct salaries from employees into temp table, ordered descending.
-- Select from temp table where id = N
Something like that?
|
|
|
|
|
Have you looked at this thread[^] from a few days ago. Though the original poster wanted the fifth highest salary, the query can be done for any number of n, just change the 5 to whatever n needs to be. Probably best to do this as a stored procedure.
Hope this helps,
Paul
|
|
|
|
|
SELECT TOP 1 * FROM (SELECT TOP N salary FROM employees ORDER BY salary DESC) AS EMP ORDER BY salary ASC
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
select max(column_name) from <table_name>
Regards
Preetha
|
|
|
|
|
I have a database running on SQL Server 2005, itself running on XP Pro (32-bit), which I access through ADO within ASP. I set up a System DSN (call it "whatever"), and the ASP code simply uses "DSN=whatever" as the connection string. Straightforward enough. A few months ago I had this running on SQL Server 2000, and migrating it over to 2005 wasn't a problem.
I'm now trying to get this to run on SQL Server 2005 64-bit on XP x64. If I try to use a system DSN, the same ASP code complains about the provider not being found (despite having it redefined through the Data Sources control panel applet on x64). Google searches are leading to believe (though not yet confirmed) the control panel applet can only be used to define 32-bit data sources and, essentially, I'm SOL if I try to go down that route on x64.
So I tried replacing the "DSN=whatever" connection string with a "real" one, based on samples posted on www.connectionstrings.com. I'm now running into access permissions galore.
At this point I can't begin to enumerate what I've tried and what I haven't (defining users in SQL, a custom user for anonymous access on IIS, etc), so instead of trying to figure out where I may have gone wrong...I'd rather start again from a clean slate... So, given:
- XP x64
- SQL Server 2005 x64
- IIS
- classic ASP
- ADO
...can someone point me to some resources explaining the steps needed to connect to this stupid database? Preferably explained in dumb user terms--this is so fundamental, I can't believe I can't get it to work. 
|
|
|
|
|
Too general? I'm just having a hell of a time finding the right keywords on Google.
The system worked on x86. I'm just looking for gotchas for doing the same on x64...anyone?
|
|
|
|
|
Hi all,
I am working for information warehouse application. Here database used is Oracle.Here we are loading data from flat file to oracle table using SQL Loader. Which is getting failed and showing error
ORA 00001 unique constraint violated
I have checked for the corresponding table by using DESC <table_name>;
It is showing NOT NULL constraint for all the columns and not showing any primary key. Is there any other way to check for primary key?
I know there can be a issue of indexes as i am new to database please guide me how can i check for index constraints in a table.
Please help me to resolve this unique constraint voilation.
Please guide me, what can be the expected reason for this failure.
|
|
|
|
|
The error message has nothing to do with indexes, but rather referential integrity. There is a column that has a constraint on it that constrains what data is allowed for the column. The data must exist in some column in another table. It is this other column in the other table that is indexed. The usual method to avoid this error is to disable the contraints while loading data with SQL*Load and to then enable the constraints after all data has been successfully loaded. Alternatively, if you can obtain the schema definition, you may try to rearrange the order of the data in your flat file and ensure that the referred-to tables are loaded prior to loading a table that contains referential integrity constraints.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
|
|
|
|
|
Hi
Thanks for your response.
I am new to database can u please explain me more regarding referential integrity constraint and how to check it.
I need it urgently please help me
|
|
|
|
|
Apologies, but the other thing to check is the data itself. There is probably some index on this table that has been defined as unique, or no duplicates allowed. This index could be on a single column or a group of columns. You should check the schema definition to verify this.
Chris Meech
I am Canadian. [heard in a local bar]
The America I believe in has always understood that natural harmony is only one meal away from monkey burgers. [Stan Shannon]
GOOD DAY FOR: Bean counters, as the Australian Taxation Office said that prostitutes and strippers could claim tax deductions for adult toys and sexy lingerie. [Associated Press]
People vote 1s for espier because there is no zero. [Ed Gadziemski]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
