sql

Is there a way I can send a NULL value to a SQL stored proc through XML?

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 ...

SQL Server 2000: How can I tell how many plans a stored procedure has cached?

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...

T-SQL User defined function overloading?

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? ...

Whats the difference between an in-memory table, temp table and a pivot table?

With regards to SQL and queries, whats the difference between an in-memory table, temp table and a pivot table? ...

Sql Server modify select

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 ...

Help with SQL query

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...

Should I use a Foreign Key to Show Tree Relationship in SQL

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...

How to get the results of WHERE conditional evaluations in the result set?

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...

Why does the sql select statement fail?

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....

SQL: How to Group by Custom Year

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...

Why is this mysql table producing a NULL in the datetime field?

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...

Get the last day of the month in SQL

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? ...

SQL Query for information *not* in database

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 ...

MySQL - fetch rows where first character is not alphanumeric

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...

debug ssis package - reload InnerPackage.dtsx

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...

Retrieve TOP X members of each group using SQL Server 2005

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...

HTML5 Database administration / introspection?

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? ...

SQL To Find Most Popular Category

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...

SqlConnection in .NET -- How can i best harness connection pooling?

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. ...

How to write a simple database engine

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...