I have a products table with a column that contains a space separated list of ids (like: "23 42 365"), the column is called "categories". The numbers refer to rows ids in another table (categories). I need to extract all product rows where all of the ids in the space separated list point to rows on the categories table that no longer ex...
How do I compare an SQL Server date column against the current week? For instance: WHERE [Order].SubmittedDate = *THIS WEEK* ...
I'm in the early stages of my database design so nothing is final yet, and I'm using the "TOXI" 3-table design for my threads which have optional tags, but I can't help but feel that the joining is not really necessary and perhaps I need to just rely on a simple tags column in my posts table where I can just store a varchar of something ...
Is there a way to query users table like this: | id | username | ----------------- | 1 | user1 | | 2 | user2 | | 3 | user3 | and user_roles table: | id_user | id_role | --------------------- | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 2 | | 3 | 1 | assuming that role w...
Hi. I use Visual Studio 2008, C#, MS SQL Server 2005. I have 2 tables that called "record" and "Estatetypes". I have some records in "record" with their estatetype IDs. I have to list my records in a repeater. When i directly databind to repeater, i normally see record's estatetype IDs. But i want to get estatetype's name that is in "Es...
I am creating a custom reporting app in C# and need to aggregate some data. Let's make it simple. Say I have 10,000 sales for a year, I need a generic way to bin the data by say, month, day or hour. Each bin would therefore sum all the sales within that period. Has anyone ever written a query like this in either SQL or Linq? ...
Hi, I was using Microsoft SQL server 2005 and was able to concatenate row values based on the following query: SELECT e1.EMP_ID, ( SELECT cast(Sector_ID as varchar(10)) + ';' FROM Employee_Sector_relationship e2 WHERE e2.Emp_ID = e1.Emp_ID ORDER BY Sector_ID FOR XML PATH('') ) AS Sectors FROM Employee_Sector_Relationship e1 GROUP BY Emp...
I found one big issue. I have added the Lower function to indexed column of one of the table to fetch the data. The table contains more then 1 lac records. While fetching the records, the cpu usage goes to 100%. I could not understand, how this much drastic change can happen just because of Lower() function. Please Help! ...
Using the Entity Framework, when one executes a query on lets say 2000 records requiring a groupby and some other calculations, does the query get executed on the server and only the results sent over to the client or is it all sent over to the client and then executed? This using SQL Server. I'm looking into this, as I'm going to be s...
I'm struggling to return a SQL version using C#, I'm new to SQL programming so any help would be great. I'm getting various errors but below is my latest attempt. private void buttonOK_Click(object sender, System.EventArgs e) { string strSqlVersion = SQLVersion(); MessageBox.Show(strSqlVersion); } private void sqlversion(string...
1)Are SQL query execution times O(n) compared to the number of joins, if indexes are not used? If not, what kind of relationship are we likely to expect? And can indexing improve the actual big-O time-complexity, or does it only reduce the entire query time by some constant factor? Slightly vague question, I'm sure it varies a lot but I...
I'd like to cast a VARCHAR to a SQL INTEGER, supplying a default value if some value in the field would not convert properly. Something like: sql> SELECT str FROM tbl; -- CREATE TABLE tbl (str VARCHAR(12), ...) str ======== 12345 -1 foo sql> SELECT CAST((CASE WHEN ... THEN str ELSE '-9999' END) AS INTEGER) AS "int" FRO...
Are there any limitations in the functionality of SQL Views for MySQL? ex: Can you create a table view using 'JOIN' commands? ...
I have a table which does versioning with an ID and a GUID. The ID increases, but the GUID is static allowing for versioning control. For example: ID GUID Data 01 ASD Something 02 ASD Something Changed 03 ASD Something Changed Again 04 DSA Something Else 05 DSA Some...
Is it possible to add a identity column to a GROUP BY so that each duplicate has a identity number? My original data looks like this: 1 AAA [timestamp] 2 AAA [timestamp] 3 BBB [timestamp] 4 CCC [timestamp] 5 CCC [timestamp] 6 CCC [timestamp] 7 DDD [timestamp] 8 DDD [timestamp] 9 EEE [timestamp] .... ...
I like to think I know enought theory, but I have little experience optimizing DB in real world. I would like to know points of view, thoughts or experiences. Let's imagine a scenario like: Table A Key: c1, c2, c3, c4 Index: c7, c3, c2 Table B Key: c1, c2, c3, c4 Index: c1, c5 All are non-clustered. The tables have 40+ fields. They a...
I don't have too much experience with SQL. Most of the queries I have written have been very small. Whenever I see a very large query, I always kinda assume it needs to be optimized. But is this true? or is there situations where really large queries are just whats needed? BTW when I say large queries I mean queries that exceed 1000+ ch...
In my database I have a column named 'description' in this field There is an age at the beginning of the description ex: "Age 4+ This is a description for this row in the table". I created a column called 'age' and I have been trying to figure out a way to move the age from the description in one row to its corresponding age column. Ho...
I have a SP reading a .txt file from File System and using Bulk Insert. I just wanna make sure if file exists before executing bulk insert command. How do i do that? ...
Perhaps this is a stupid question but we've had some debate recently. Which of the two (semantically equivalent) ways is preferable to test for inequality: 'foo' != 'bar' 'foo' <> 'bar' The mysql documentation clearly indicates that there is no difference between them and yet some people seem to be attached to only doing it one way ...