I have 2 columns: "string" "int" I set both as primary keys for unique combination. Would searching based on the string column be poorer than normalizing further the string column and having 2 int columns instead? ...
We're using SQL Server 2005 to track a fair amount of constantly incoming data (5-15 updates per second). We noticed after it has been in production for a couple months that one of the tables has started to take an obscene amount of time to query. The table has 3 columns: id -- autonumber (clustered) typeUUID -- GUID generated before ...
I have an SQL 2005 database and I know that in the database there is a table which has got some xml strings in it. How can I find this table(s)? ...
hi guys, I have 2 dates. I want to get number of days between 2 dates in storedprocedure. ...
I need to import data from Excel into a SQL 2000 db. I need to import 6 fields from the worksheet and increment a string field containing an integer padded to 5 characters with leading zeros. This field is not the primary key and the db does not automatically populate this. Also the DB will allow this field to be entered as NULL if this...
I have the following tables: Country: Country_ID, CountryName Regions: Region_ID, RegionName, Country_ID Areas: Area_ID, AreaName, RegionID I am not using foreign keys and not planning to do so, all I wanna do now is to be able in one query in the tableAdapter of country table to delete Country and all related regions, areas... How? ...
I want to retrieve the next sequence number via an adhoc query in Entity Framework. I'm using: LogEntities db = new LogEntities(); ObjectQuery<Int64> seq = db.CreateQuery<Int64>("SELECT AUDITLOG.EVENTID_SEQ.NEXTVAL from sysibm.sysdummy1"); This returns the following error: ErrorDescription = "'sysibm.sysdummy1' could not be resolved...
select 00.0004 ||' ' || 'USD/MT' from dual this gives the o/p as .0004 USD/MT I also want a 0 before the decimal so that i would get the result as 0.0004 USD/MT Can anyone help me out? ...
Anyone have a t-sql function that takes a querystring from a url and returns a table of name/value pairs? eg I have a value like this stored in my database: foo=bar;baz=qux;x=y and I want to produce a 2-column (key and val) table (with 3 rows in this example), like this: name | value ------------- foo | bar baz | qux x | ...
OK I have a table like this: ID Signal Station OwnerID 111 -120 Home 1 111 -130 Car 1 111 -135 Work 2 222 -98 Home 2 222 -95 Work 1 222 -103 Work 2 This is all for the same day. I just need the Query to return the max signal for each...
Hi all, I'm facing a problem which I've spent a great deal of time trying to address, and although I have a solution, it's clunky and involves pl/sql processing, and I was wondering what others might come up with. I'm working with a dataset that creates a new row every time a record is changed, thus maintaining a history. The most up-to...
I'm using SQL Server 2008. Say I create a temporary table like this one: create table #MyTempTable (col1 int,col2 varchar(10)) How can I retrieve the list of fields dynamically? I would like to see something like this: Fields: col1 col2 I was thinking of querying sys.columns but it doesn't seem to store any info about temporary ...
I am trying to join 3 tables with the following sql statement Select Sum(OrderDetail_table.Price * orderDetail_table.quantity) as totalPrice, item_table.GLacct from OrderDetail_table left outer join order_table on orderDetail_table.orderID = order_table.orderid left outer join item_table on it...
I have a table with column ID that is identity one. Next I create new non-identity column new_ID and update it with values from ID column + 1. Like this: new_ID = ID + 1 Next I drop ID column and rename new_ID to name 'ID'. And how to set Identity on this new column 'ID'? I would like to do this programmatically! ...
I'm trying to use the TSql100Parser.ParseStatementList method to programatically parse sql statements and pull out object names. This is from the Microsoft.Data.Schema.ScriptDom.Sql namespace. Here's the code: string sql = "CREATE VIEW testView AS SELECT * from testTable"; var parser = new TSql100Parser(false); StatementList parsedSta...
OK, I'm trying to make an "empty" version of a database on another instance of SQL Server 2005. To do this, I use the Scripting wizard to make a big script that CREATEs all the tables, views, SPs, etc., adds some users, and grants permissions for them. Now, in the permission section of the script, I have (among other things) use [mast...
Sorry everyone. It appears that I am a dumbass! The query works fine when I run it against the live database versus my test database...gotta pay more attention to that stuff! I am having a problem with the statement shown below. It works fine if I run it against one database (sans the DBCATALOG.dbo), so no problem there. When I try ...
Hey guys. I have a column of data containing a string with the following format: blablablabla(XYZ) and I would like to discard everything outside the () - and the parenthesis themselves - and to set update that field with the value inside the (). This implies that, in this particular case, the "blablabla" would be discarded and that ...
I was writing a Stored procedure today, and I ran into a problem where if one of the values is null (whether it be from a SELECT statement or whether it be a passed in parameter) my expression would evaluate to false where it ought to evaluate to true. SET ANSI_NULLS ON; DECLARE @1 INT; DECLARE @2 INT; DECLARE @3 INT; DECLARE @4 INT; S...
In postgresql, you can use the && operator to return t (true) if two arrays have common members, i.e. they overlap. Is there a function/operator that will return what those common members are? i.e. something like this select arrray_intersection(ARRAY[1, 4, 2], ARRAY[2, 3]); ARRAY[2] ...