I am thinking about creating stored procedures on the fly. ie running CREATE PROCEDURE... when the (web) application is running. What are the risks or problems that it can cause? I know that the database account needs to have the extra privileges. It does NOT happen everyday. Only from time to time. I am using sql server and inter...
What exactly does null do performance and storage (space) wise in MySQL? For example: TINYINT: 1 Byte TINYINT w/NULL 1 byte + somehow stores NULL? ...
Is it quicker to make one trip to the database and bring back 3000+ plus rows, then manipulate them in .net & LINQ or quicker to make 6 calls bringing back a couple of 100 rows at a time? ...
I am working on a stored procedure with several optional parameters. Some of these parameters are single values and it's easy enough to use a WHERE clause like: WHERE (@parameter IS NULL OR column = @parameter) However, in some instances, the WHERE condition is more complicated: WHERE (@NewGroupId IS NULL OR si.SiteId IN (SELECT gs....
How would I reset the primary key counter on a sql table and update each row with a new primary key? ...
What's the best data type in SQL to represent Decimal in .NET? We want to store decimal numbers with up to 9 decimal place precision and want to avoid rounding errors etc on the front end. Reading about data types, it appears using Decimal in .NET is the best option because you will not get rounding errors, although it is a bit slower ...
We are moving from regular 2.0 webforms with no testing to TDD MVC I'm looking to find out all the requirements for making the jump from 2.0 to 3.5 MVC. Does SQL Server 2000 work with .net 3.5? Does the MVC Framework come with .net 3.5? How does AJAX work with all this stuff? Is visual studios team suite worth the time/money? ...
I have a table that was imported as all UPPER CASE and I would like to turn it into Proper Case. What script have any of you used to complete this? Thank you. ...
I need write an update statement that used multiple tables to determine which rows to update, since in Oracle, multiple tables aren't allowed. The following query will return a "ORA-00971: Missing SET keyword" error UPDATE TABLE1 a, TABLE2 b SET a.COL1 = 'VALUE' WHERE a.FK = b.PK AND b.COL2 IN ('SET OF VALUES') Looking up t...
I have a table, call it TBL. It has two columns,call them A and B. Now in the query I require one column as A and other column should be a comma seprated list of all B's which are against A in TBL. e.g. TBL is like this 1 Alpha 2 Beta 1 Gamma 1 Delta Result of query should be 1 Alpha,Gamma,Delta 2 Beta This type of ...
Is there a dialect of XML for defining the tables, indexes and relations of a relational database, and a "compiler" or stylesheet to transform that definition into SQL CREATE statements (DDL)? EG, something that might look like: <Table Name="orders"> <Column Name="order_id" Type="varchar" Size="20"/> ... etc ... </Table> I'd ...
Using Vim, I'm trying to pipe visually selected text to a UNIX command and have the output appended to the end of the current file. For example, say we have a SQL command such as: SELECT * FROM mytable; I want to do something like the following: V # select text :'<,'>!mysql -uuser -ppass mydb But instead of having the output overwri...
When I am creating a new database table, what factors should I take into account for selecting the primary key's data type? ...
I have started making a database and I have an 'ordered item' and a 'invoice' table. I would like to find out how I would add up the prices of the items that the customer picks. As well where abouts do you put this 'rule' on the database. ...
On our live/production database I'm trying to add a trigger to a table, but have been unsuccessful. I have tried a few times, but it has taken more than 30 minutes for the create trigger statement to complete and I've cancelled it. The table is one that gets read/written to often by a couple different processes. I have disabled the ...
Our application executes a long, hairy stored procedure with multiple result sets. The users are experiencing long wait times for this query, so I set out to determine what is causing the delay. I put a Stopwatch on executing and reading the data, and it takes 6-7 seconds each time. I timed the execution of the stored procedure, expe...
I have a table that has a column with a default value: create table t ( value varchar(50) default ('something') ) I'm using a stored procedure to insert values into this table: create procedure t_insert ( @value varchar(50) = null ) as insert into t (value) values (@value) The question is, how do I get it to use the defaul...
Do you prefer to use stored procedures or parameterized queries? What is your argument supporting your choice? Is one method really any better than the other in terms of performance, security, maintainability... etc? ...
Edit: I am using SqlDataAdapters to fill the data sets. Sorry--I should have been more clear. I'm working on a project where I need to fill a number of strongly-typed data sets with information from stored procedures. Right now, I have a generic method in my data access layer: public static DataSet FillDataSet(DataSet dataSet, string s...
I'm creating a table that looks something like this. CREATE TABLE packages ( productCode char(2) , name nvarchar(100) , ... ) I want to make sure the productCode is always one of two values: XJ or XD. How do I do that? ...