sql

Optimizing deleting a large number of rows (over a few millions) - SQL Server 2005

First we get the max id from the ProductFileLocalName and then - 1000 (as we don't want to delete the most recent additions as they might not be inserted in ProductFileInfo yet) Then we pass the max id to this stored procedure: DELETE TOP (10000) FROM ProductFileLocalName WITH (ROWLOCK) FROM ProductFileLocalName LEFT OUTER JOIN Pr...

Create db Synonyms that include login credentials

Hey there I'd like to create a synonym on a local db that points to a db on the web that's only accessible with a user name and password. Is there a way to set up a synonym for that kind of situation? I would be very grateful for an example if this is even possible... Thanks ...

SQL: Any straightforward way to order results FIRST, THEN group by another column?

I see that in SQL, the GROUP BY has to precede ORDER BY expression. Does this imply that ordering is done after grouping discards identical rows/columns? Because I seem to need to order rows by a timestamp column A first, THEN discarding rows with identical value in column A. Not sure how to accomplish this... I am using MySQL 5.1.41 ...

Best way to create SQL indices?

What is the best way to create an SQL index in SQL database? CREATE INDEX idx ON sometable (col1, col2, col3); Or CREATE INDEX idx1 ON sometable (col1); CREATE INDEX idx2 ON sometable (col2); CREATE INDEX idx3 ON sometable (col3); Which is the difference between these 2 methods? Does it depends on the SQL implementation? (SQLite, M...

LIMIT ignored in query with GROUP_CONCAT

Hello, I need to select some rows from second table and concatenate them in comma-separated string. Query works well except one problem - It always selects all rows and ignores LIMIT. This is part of my query which gets that string and ignores LIMIT: select group_concat(value order by `order` asc SEPARATOR ', ') from slud_data ...

Query select a bulk of IDs from a table - SQL

I have a table which holds ~1M rows. My application has a list of ~100K IDs which belong to that table (the list being generated by the application layer). Is there a common-method of how to query all of these IDs? ~100K Select queries? A temporary table which I insert the ~100K IDs to, and Select query via join the required table? Tha...

including parameters in OPENQUERY...

How can I use a parameter inside sql openquery, such as: SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME where field1=@someParameter') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK ...

"Unknown column" because of subquery-in-subquery.

I need to do subquery in subquery what causes "Unknown column 't1.product_id' in 'where clause'". It's on line 7. in my example. How to solve this problem? SELECT *,product_id id, (SELECT GROUP_CONCAT (value ORDER By `order` ASC SEPARATOR ', ') FROM ( SELECT `order`,value FROM slud_data LEFT JOIN slud_...

SELECT JOIN in same table ON multiple rows...

I have a table with an itemID and a categoryID columns. Both columns are primary keys because each item can have more than 1 category: itemID | catID ----------------- 1 | 2 1 | 3 1 | 4 2 | 2 2 | 3 2 | 4 I want to select items with the same categories (based on all the item categories,...

SubSonic SimpleRepository storing member class

Hi! I'm new to C# and Subsonic. I'm trying to solve the following case: public class UnknownInt { public int val; public bool known; } public class Record { public int ID; public UnknownInt data; } I'm using SimpleRepository. Is there a way I can get UnknownInt serialized before storing it in the SQL database (perhaps as ...

[SQL] Related rows based on text columns

Given that I have a table with a column of TEXT in it (MySQL or SQlite) is it possible to use the value of that column in a way that I could find similar rows with somewhat related text values? For example, I if I wanted to find related rows to row_3 - both 1 & 2 would match: row_1 = this is about sports row_2 = this is about study row...

Writing blob from SQLite to file using Python

Hello, A clueless Python newbie needs help. I muddled through creating a simple script that inserts a binary file into a blog field in a SQLite database: import sqlite3 conn = sqlite3.connect('database.db') cursor = conn.cursor() input_note = raw_input(_(u'Note: ')) input_type = 'A' input_file = raw_input(_(u'Enter path to file...

SQL Data Type to store Product versions

What data type should i use for a SQL column to store Product version eg. Version 0.1 0.1.1 0.2 1.1 1.1.647 2.0 ..... In query i should be able to sort them based on version number and i want an optimal query to find highest number. Thanks ...

Difference between login and user in sql server

CREATE USER [anc] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[anc] GO what is the difference between the user and the login. i am not clear as to y do we create these kind of users?? ...

Reordering an ordered list

I have a following SQL table with data ProductList id order productname 79 1 name1 42 2 name2 67 3 somename 88 4 othername 99 5 XYZ 66 6 ABC Display order is very volatile, it will change frequently, users will add or remove items and reorder the items. How should i handle this situation without ...

Getting a sql varbinary record using microsoft enterprise library

I have this line of code byte[] field1 = (reader.GetSqlBinary(reader.GetOrdinal("Field1")).Value; which is a SqlDataReader I am trying to convert the code to use the enterprise library data access block but cant figure out how to get a byte[] using the IDataReader. I've had a good look through the MS docs but failed to find anythin...

What type is used in sql for percentage ?

I know char, int, numeric, but i don't know what is used for percentage ? ...

How to produce rank in Oracle

Need to rank the below by salary, with highest salary having rank 1. RANK column shown is what I'm after. Empname sal address RANK Ram 3411 45,east road 2 Anirban 2311 34,west wind 4 Sagor 10000 34,south 1 Manisha 3111 12,d.h road 3 ...

How do i optimize this query?

Hello, I have a very specific query. I tried lots of ways but i couldn't reach the performance i want. SELECT * FROM items WHERE user_id=1 AND (item_start < 20000 AND item_end > 30000) i created and index on user_id, item_start, item_end this didn't work and i dropped all indexes and create new indexes user_id, (item_sta...

Combine 2 result sets in SQL?

How do I combine the resultsets to return a single result in SQL? For example - SELECT * FROM Table1 SELECT * FROM Table2 I want to combine the two resultsets with the columns from the second resultset appended to the first. Table 1 and Table 2 are not related to each other in any way. If Table 1 has 2 columns and Table 2 has 4 colum...