An SQL quesion: Can I group by something that isn't in the SELECT line?
Given a command in SQL; SELECT ... FROM ... GROUP BY ... Can I group by something that isn't in the SELECT line? ...
sql
Execute SQL Script within Solution Explorer in VS 2008 Professional
Does anybody know how I can execute a .sql file from within solution explorer in VS 2008 Professional? I know you can do this in VS 2005 but cannot find this feature in VS 2008 professional ...
Change time of a datetime attribute
I have this query: select id from table where date >= @idate I want to set the time of the date attribute in the where = 00:00 How can I do it? ...
Restrict SQL results by time, MySQL
Hey, I have a table containing events which happen in my application like people logging in and people changing settings. They have a date/time against the event in the following format: 2010-01-29 10:27:29 Is it possible to use SQL to select the events that have only happened in the last 5 mins? So if the date/time was 2010-01-29 ...
Regex to convert a space separated list to a SQL where clause
I'm almost embarassed, but I'm struggling to create a regular expression to change something like cat dog mouse to a SQL where clause: a.cat=b.cat AND a.dog=b.dog AND a.mouse=b.mouse With s/(\w*)\s?/a.$1=b.$1 AND / I get a.cat=b.cat AND a.dog=b.dog AND a.mouse=b.mouse AND a.=b. AND Ouch. Help appreciated. EDIT: I ended up usin...
Can an INNER JOIN offer better performance than EXISTS
I've been investigating making performance improvements on a series of procedures, and recently a colleague mentioned that he had achieved significant performance improvements when utilising an INNER JOIN in place of EXISTS. As part of the investigation as to why this might be I thought I would ask the question here. So: Can an INNER...
Expanding list from SQL column with XML datatype
Given a table with an XML column, how can I expand a list stored therein, into a relational rowset? Here is an example of a similar table. DECLARE @test TABLE ( id int, strings xml ) insert into @test (id, strings) select 1, '<ArrayOfString><string>Alpha</string><string>Bravo</string><string>Charlie</string></ArrayOfString>' union...
Nested Set Model: Inserting Node at the end of SubNodes
Existing Data (name, lft, rgt): Root, 1, 4 Item1, 2, 3 Looks like: - Root --- Item1 How do you insert a new node (Item2) BELOW Item1? My system's current logic follows most examples I've found online but the result is Item2 ABOVE Item1. - Root --- Item1 --- Item2 Thank you for the help. ...
performing a SELECT TOP query with an ORDER BY - should I use an index or a 'sorted view' to increase performance
This query (part of a sproc) will be executed quite a lot: SELECT TOP (@numRecords) BlogPost.postId,BlogPost.creationDate, BlogPost.header,BlogPost.markedupContentAbstract FROM dbo.BlogPost ORDER BY BlogPost.creationDate DESC Should I put an index on the 'creationDate' field in the BlogPost table? Should I have a view wh...
Maintaining application/system accounts for database access.
(rephrased...) How do you manage 'application' database accounts that have to follow the same policy as regular login-capable user accounts. We have many processes in our system that run, unattended all the time, or part of scheduled jobs that need to access the database (Informix). These have been, up until now, nologin/noexpire acco...
Check if stored proc exists in DB?
i am trying to grant execute privs in stored proc in more than one database. The problem is this stored proc might not be in some of the databases. So how can i write a script which checks if stored proc exists in database and if does then provide execute privs for user? ...
Using a database table as a queue
I want to use a database table as a queue. I want to insert in it and take elements from it in the inserted order (FIFO). My main consideration is performance because I have thousands of these transactions each second. So I want to use a SQL query that gives me the first element without searching the whole table. I do not remove a row wh...
Server Server 'In' Statement Item Order for Performance
Given the SQL Statement: SELECT * FROM MY_TABLE WHERE SomeNumberField in (0,99999) If I can guarantee that the majority of rows in MY_TABLE have SomeNumberField set to 99999, and can project that this will remain the case indefinately, is it better to write the above query like this: SELECT * FROM MY_TABLE WHERE SomeNumberField in ...
What does the colon sign ":" do in a SQL query?
What does ":" stand for in a query ? INSERT INTO MyTable (ID) VALUES (:myId) How does it fetch the desired value? Edit: Also what is that sign called? I wanted to search on google, but what's the name for ":"? Chosen Answer: Chose the answer for the link and the upvotes. But also check Jeffrey Kemp's answer ...
Is this a spurious warning when using LINQ to SQL?
Per the many examples of LINQ I've seen, I'm creating my own data context and tables using code similar to the one below: class MyDatabase : DataContext { public Table<Widget> Widgets; public Table<Car> Cars; public MyDatabase (string connection) : base(connection) { } } But for every table (Widgets, Cars, etc), I get the warn...
Get top first record from duplicate records having no unique identity.
I need to fetch top first row out of each duplicate set of records from table given below. I need to use this query in view please no temp table as I have already done it by adding identity column and min function and group by. I need solution without temp table or table variable This is just sample data. Original has 1000s of records...
Why does a SqlException thrown by SqlCommand.ExecuteNonQuery contain all the PRINTs as errors?
Hello! When I run the following snippet try { using (SqlConnection conn = new SqlConnection("I'm shy")) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "PRINT 'A';PRINT 'B';PRINT 'C';RAISERROR('SQL_Error', 18, 1)"; cmd.ExecuteNonQuery(); } } } catch (SqlException ex) { MessageBox.Show...
SQL Server 2008 fulltext catalogs: more than one language?
I have one table and I want to create two fulltext catalogues, one in German, and one in English. My problem is: I can only create one fulltext catalog per table, but I want to create a fulltext catalog for English language and German language. I cannot use an indexed view. Is there any way to fix my problem? ...
Is there a tool to check the minimum privileges required for a set of sql?
If I have a set of SQL (ie. a script containing arbitrary SQL statements), is there any way of finding out what the minimum permissions are required to execute the SQL? (I'm thinking of something similar to the "Permissions required by the application" area in Visual Studio when viewing the Security tab on the project properties page of...
SQL update undo
Is there a way we can undo a SQL update query? ...