I'm wondering if there is a way I can send a NULL or DBNull.Value from my C# data service to a stored proc through some configuration xml parameter. In the proc, I want to pull some values out of the xml as a bit but because the UI allows for a third state, the bit value coming in from the xml can be NULL in which case I want to ignore ...
Sometimes when diagnosing issues with our SQL Server 2000 database it might be helpful to know that a stored procedure is using a bad plan or is having trouble coming up with a good plan at the time I'm running into problems. I'm wondering if there is a query or command I can run to tell me how many execution plans are cached currently f...
I understand that T-SQL is not object oriented. I need to write a set of functions that mimics method overloading in C#. Is function overloading supported in T-SQL in any way? If there is a hack to do this, is it recommended? ...
With regards to SQL and queries, whats the difference between an in-memory table, temp table and a pivot table? ...
I'm pretty sure there is no way, but i'm putting this out there for those expert beyond my knowledge. What i am looking to do is to somehow alter SELECT statements before they are executed, at the database level. For a seriously pared-down example, i'd like to do something like the following... when someone executes the following SQL ...
Hello all, Lets say I have one table called "REVIEWS" This table has Reviews that customers have written for various products. I would like to be able to get a "count" for all the reviews that each customer has written, so i write: SELECT count(*) AS counter FROM reviews WHERE customers_id = 12345 Now, my problem is that I wish t...
I am trying to model a tree relationship in a table. For instance, there are "Categories" and categories can themselves be inside a Parent category. My schema is: id int PRIMARY KEY, parent_id int, name My question is, should I label the parent_id column as a Foreign key? Foreign implies "outside" and not self-referencing. Is ther...
The PROBLEM I have a query like this: select a.id from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND (( /* conditional #1 */ ) OR ( /* conditional #2 */ ) OR ( /* conditional #3 */)) I would like to have the query return something like: select a.id, co...
In the scenario below, the final select from the Combine view fails, any ideas why? The Subset table does not have a row that corresponds to the one in MasterCodes that wouldn't cast to an integer value. CREATE TABLE MasterCodes ( ID INT , Code VARCHAR(10) ) GO CREATE TABLE Subset ( ID INT ) GO CREATE VIEW Combine AS SELECT S....
For simplicities sake, I'll make up a similar example to what I have: Let's say a db has a table of orders with an OrderDate field and a Company field. Then there's a table of Companies and each record has a YearEndingDate (which signifies that the year ends on that date each year, e.g. 6/6). I need to add up all of the orders for eac...
I've got this table. The table has a bunch of char fields, but the field in question (expiredate) contains a char on the test_tmp table, and a datetime on the test table. (Table names have been changed to protect the clients.) In the test_tmp table, the field contains a Month-Date pair, like 'Aug 10' or 'Feb 20'. The code I'm using t...
I need to get the last day of the month given a date in sql. If I have the first day of the month I can do something like this: dateadd(day, dateadd(month,'2009-05-01',1), -1) But does anyone know how to generalize it so I can find the last day of the month for any given date? ...
I have a large list of email addresses, and I need to determine which of them are not already in my database. Can I construct a query that will tell me this information? I am using SQL Server 2000, if it requires non-standard extensions ...
I am adding a few new features to a small project i'm working on and one of them is alpha pagination which looks like # 0-9 A B C D E ... X Y Z I can easily fetch items by their first letter using something like SELECT * FROM ... WHERE name LIKE 'A%' ... Grouping everything that starts with a number and all other characters is a...
Hi all, I used the wizard to build a package that loops through my tables does whatever. It has worked forever and now I have to change it. How on earth do I debug this thing? When I try to run it from bids, it opens InnerPackage.dtsx for editing and then asks me if I want to reload it (since it has changed since the last iteration o...
I've seen this question asked a couple of times, and I've written my own query, but it's quite slow, and I would be extremely grateful if someone could offer advice on how to speed it up. In a simplified scenario, I have the following two tables: Group - GroupID (primary key) Member - MemberID (primary key) - GroupID (foreign key) L...
I'm playing around with html client side storage and wanted to know if there are any facilities for introspection of the database - is there an equivalent of sqlite ".tables" or ".schema" ? Also, I'm not seeing my tables show up in AppData\Local\Apple Computer\Safari\LocalStorage . Is there another place tables are stored? ...
I have 3 tables in my DB (MySQL). categories (name:string) items (name:string, category_id:int) votes (value:int, item_id:int, created_at:datetime) So a category has many items, and an item has many votes. I want to write a query to get the most popular categories, which means getting the categories whose items have gotten the most n...
I read that .NET uses connection pooling. And, for example, if I instantiate a bunch of SqlConnection objects with the same connection string, then internally .NET will know to use the same connection. Is this correct? And, in a big web-based application, any tips on the best way to harness this "power" ? Thanks. ...
I am interested in learning how a database engine works (i.e. the internals of it). I know most of the basic data structures taught in CS (trees, hash tables, lists, etc.) as well as a pretty good understanding of compiler theory (and have implemented a very simple interpreter) but I don't understand how to go about writing a database e...