sql

Is it faster to access data from files or a database server?

If I have a static database consisting of folders and files, would access and manipulation be faster than SQL server type databases, considering this would be used in a CGI script? When working with files and folders, what are the tricks to better performance? ...

C# to SQL float values conversion

Hello, I'm working with C# and SQL 2005. I've stored a float variable of the value 0.2 into table column of real type. When I opened the table to check the values, I found a value of 0.200000029... It is known problem, real is approximate type. However, I've changed the value using management studio to 0.2 and reloaded the table. The dat...

SQL count many-to-many values or have it counted every time new row is added?

I am using MySQL (MyISAM) 5.0.41 and I have this query: SELECT `x`.`items`.id, `x`.`items`.name, COUNT(*) AS count FROM `x`.`items` INNER JOIN `x`.`user_items` ON `x`.`items`.id = `x`.`user_items`.item_id GROUP BY name HAVING count > 2 ORDER BY count DESC I have about 36,000 users, 175,000 user_items and 60,000 items which...

Syntax for INSERTing into a table with no values?

I have a table created with the following schema: CREATE TABLE [dbo].[Visualizations] ( VisualizationID int identity (1,1) NOT NULL ) Since the table has no settable fields, I'm not sure how to insert a record. I tried: INSERT INTO [Visualizations]; INSERT INTO [Visualizations] () VALUES (); Neither work. What is the p...

SQL Server 2005 : Converting varchar value '1.23E-4' to decimal fails

declare @a varchar(40) set @a='1.23e-4' declare @b decimal(27,12) if isnumeric(@a) =1 begin select @b=cast(@a as decimal(27,12)) end else begin select @b=-1 end select @b when exeucting above sql code under SQL 2005 environment I am getting following error. Error converting data type varchar to numeric anyone knows why? ...

At how many returned records should I begin expecting performance problems?

I have a query that I have been tuning for some time but I can't seem to get the execution time down much. In the execution plan everything looks like it is doing what it is supposed to, no large costs associated with any particular part of the query, everything is using index seek where it is supposed to. When I run the same query aga...

update with inner join, update 2 columns from both tables

this is my query in sql server 2008 - UPDATE a SET a.col2 = 'new', a.col3 = 'www.google.com', b.col1 = '10' FROM table a INNER JOIN table b ON a.col1 = b.col1 WHERE a.col1 = 7 it crashes stating "Invalid column name b.col1." How do i make this work? ...

Setting A Date to End the Use of a Section of a SQL Script

I am using SQL 2005. For one of our customers, we run a script everytime we set-up a new database. The script defines what information remains and what information is deleted from the database....we use a master database to set 'typical' default information. I have been asked to add a delete statement to the script with a 'test' for t...

SQL: Creating new table from data of other tables

Hi, I'm very new to SQL and I hope someone can help me with some SQL syntax. I have a database with these tables and fields, DATA: data_id, person_id, attribute_id, date, value PERSONS: person_id, parent_id, name ATTRIBUTES: attribute_id, attribute_type attribute_type can be "Height" or "Weight" Question 1 Give a person's "Name", ...

counting rows while displaying the data in mySQL

I have two queries select TC,li,it as ee from results where li = "not_applicable" and cpirt = "uu_X1" The above query will output 10 results, but If I put count in the same query, there is only one results. select TC,li,it,COUNT(TC) as ee from results where li = "not_applicable" and cpirt = "uu_X1" How can I make the second quer...

What would cause this query to stop working?

I'm working with a legacy Java app that's pulling data from Oracle. One of the queries appears to no longer work: select {hier.*} from ecadmin.dept_hier_cache hier connect by prior parent_deptid = deptid start with deptid = '1234'; When I remove the '{}' brackets from around hier.*, everything works as normal. Now, as far as I can tel...

SQL datasource from stored proc

Ok, here is the thing, I have a stored proc which returns three sets of data results, i can use the results in code behind to databind a repeater or whatever, something like so... Using cmd As New SqlCommand("TICKET_TECH_S", oConn) cmd.CommandType = CommandType.StoredProcedure Using da As New SqlDataAdap...

Passing database name as variable to SQL from C#. Is it possible?

I've 2 tables having exactly same columns. But they are stored in Database1 and Database2 (on same server). Can i pass database name as variable? For example: SELECT [SomeValue] FROM [Database2].[dbo].[Klienci] SELECT [SomeValue] FROM ...

Is there any simple way to format decimals in T-SQL?

I know it could be done trivially in a non-SQL environment [post-data processing, frontend, what have you], but that's not possible at the moment. Is there a way to take a decimal(5,2) and convert it to a varchar without the trailing zeroes/decimal points? For example: declare @number decimal(5,2) set @number = 123.00 select cast(@numbe...

How to resolve ORA-00937: not a single-group group function when calculating percentage?

I'm trying to get a percentage of the itemid that are available in a certain area. Using my query, I get an error ORA-00937: not a single-group group function All the details: I have these two tables: ALLITEMS --------------- ItemId | Areas --------------- 1 | EAST 2 | EAST 3 | SOUTH 4 | WEST CURRENTITEMS ---...

Help with creating a rather complex sql statement.

Hey everyone, I wasn't really sure how to describe this in the form of a google search, so I've decided to come here and see if you guys can help. Basically, I want to know how I can translate the following pseudo-sql into an actual mysql query: Select one random row from `posts` where (the user's id, which is supplied by the scrip...

What is best aproach to get sql data from C#

I'm trying to find optimal (fast vs easiest) way to access SQL Server code thru code in c#. As i was learning from books I've encountered multiple suggestions usually telling me to do it via drag and drop. However since i wanted to do it in code first aproach was to get data by column numbers, but any reordering in SQL Query (like addi...

Determine if a POINT is between two other POINTs on a LINESTRING (SQL Server 2008 Geography)

I have a SQL Server 2008 GEOGRAPHY data type in my database containing a LINESTRING. The LINESTRING describes a potentially curvy road. I have another table that contains a start point and end point, both GEOGRAPHY POINT's, on the road. I need to know if a third point falls between those two points on the road (LINESTRING). Currently, ...

Writing a SQL generator, What Should I Read Up On?

I am soon going to be writing a component that takes metadata and generates dynamic SQL from it. Mostly we're talking SELECT, INSERT, UPDATE, DELETE stuff but I suppose it's possible to have some CREATE/ALTER TABLE statements in there too. I'm assured that no existing ORM solution fits the bill but otherwise the details on what where a...

Select *Latest* Grade Information for every Student

I have two tables: STUDENT GRADES ---------- ---------- id id name person_id address date city test_name phone grade Each Student will have several entries in the Grades table. I am wondering if it is possible using SQL (Postgres) to select all students along with their lates...