I store all my data in on XML column in SQL Server 2005. As more and more records are being inserted, I notice the queries are slowing down. I've tried creaeting a Primary XML Index, as well as a Secondary VALUE index and this did not do anything to help the speed. Any tips,thoughts, or tricks that I'm missing? Sample View that I que...
Hello, I was just wondering wether i can do this: SELECT * FROM calendar ORDER BY ((month * 31) + day) ...
I have the below XML <myroot> <scene> <sceneId>983247</sceneId> <item> <coordinates> <coordinate>0</coordinate> <coordinate>1</coordinate> <coordinate>2</coordinate> <coordinate>3</coordinate> </coordinates> <Values> <Value>34</Value> <Value>541</Value> <Value>255</Value> <Value>332</Value> </Values> </item> </scene> </myroot> How can...
Simple concept we are basically doing some auditing, comparing what came in, and what actually happened during processing. I am looking for a better way to execute a query that can do side by side table comparisons with columns that are slightly differnt in name and potentialy type. DB Layout: Table (* is the join condition) Log (Un-a...
I have 2 tables Users and Queries. They are connected via FK(UserId) in Queries table. I need to add queries added, for example, by user with login "Bob" to all users. Here is a chunk of code i'm using: public bool SaveUserQuery(string userName, Query query) { var db = new UserDataClassesDataContext(); Table<User...
My query: SELECT * FROM forum_topics WHERE cat_id IN(1,2,3,4,5,6,7) ORDER BY last_message DESC LIMIT 7 I want to get the biggest and only one value of each cat_id (7 values total). How to correct this query to make it work if it's even possible? There is forum topics and each has value last_message and I want to get the latest to...
I'm working on a webapp with a set of data that users can browse and edit. Users want to customize one of the fields that appear for each item, say "product_name". Each product has a set of default values in a table, which is the same for all users: The product table would be like (skipping syntax cruft, you get the idea): CREATE TA...
I have a filter on my report that is a multivalue list for UnitNumber. The report is set up to show each unit's details on a separate page. If I select unit #3 and unit #4 and unit #4 doesn't have any data, is it possible to show the data for unit #3 and then on the next page say "Unit #4 has no activity"? ...
Hi, I need to round a decimal in a sql query on Oracle 10g to the nearest even number. If the number is even, it should be returned. If the number is odd, the next even number should be returned. This is what I want: 8.05 should return 8.06, 3.48 should return 3.48 How can I do this? Thanks, Andrew ...
I have a select statement and I want to say if this select statement does not return any rows then put a '' in every cell. How do I do this? ...
From another thread that gave me the information on how to add monitoring to an SQL Server... http://stackoverflow.com/questions/3586736/see-sql-from-entityframework-with-collection-like-queries This works well, but I want to take it a step further. I'd like to be able to add comments into the log when Transactions are happening. I w...
For the following (simplified) mysql DB setup, I'd like to copy the applicable guids into the message table. Can this be done with a single SQL update? CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `guid` varchar(13) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; INSERT INTO `user` (`id`, `guid`) VAL...
I want a list of all timezones in the mysql timezone tables, and need to select: 1) Their current offset from GMT 2) Whether DST is used by that timezone (not whether it's currently in use, just whether DST is considered at some point in the year for that timezone) Reason: I need to build a web form and match the users time zone inform...
Our database currently has "CreatedByUser", "CreatedTime", "ModifiedByUser", "Modified Time", "Status" and "Description" for all the tables in our database. Sometimes it is used correctly but for the most part, it is not. Is this a standard practice or is this totally overkill? If it is standard practice, is there an easy way to automate...
In Sqlsever Enterprise manager, there are some default databases are provided like tempdb and etc. What is significance of those databases? ...
I have the following query: SELECT AVG(val) from floatTable WHERE tagindex IN(1,2,3,4) AND DateAndTime > '$first_of_year' It returns the average value for all the values measured for those four tags for the year to date. Since I'm already retrieving this data, how can I get the data since the first of the month, since the first of the...
So I have 5 rows like this userid, col -------------- 1, a 1, b 2, c 2, d 3, e How would I do query so it will look like this userid, combined 1, a b 2, c d 3, e ...
So I am trying to do a somewhat complex query and am having trouble. I have structured it like this as it seems to be the most logical: SELECT (intake.id, s_matters.id, s_calls.cid, s_events.id) AS id, (intake.name, s_matters.casename, s_calls.name, s_events.subject) AS title FROM intake, s_matters, s_calls, s_events WHERE tit...
The title really says it all, a bit more info though for those who bothered to click. Strings will be of variable length typically between 2-5 characters in length, could occasionalyl exceed 5 characters and be upwards of 10. Never more than 10. Will be queried on like such: SELECT ... WHERE ... = 'abcd'; SELECT ... WHERE ... LIKE 'ab...
I am creating a site search feature for my e-shop and am using the following code to order my query: ORDER BY((case when name RLIKE $wholeword then 3 else 0 end) + (case when name RLIKE $partialword then 1 else 0)) DESC This selects both complete and partial matches from the database and orders them giving complete matches priority ove...