I have a Delphi application running on SQL Server 2000, but it's taking awfully long to connect to the database! But when I run this application on my development server it connects pretty fast! I am running on Windows 2003 server, SQL Server 2k personal edition, when I look on my MDAC version in the registry, I see version 2.8 alread...
I have been wondering what the best practices or ramifications are in setting up the PK in a M2M table in SQL Server. For instance: I have 2 tables Users Roles I am making a new table UserRole Which has 2 fields RoleId & UserID now should I create a UserRoleID as the PK and make UserID and RoleID the FKs make the PK UserID ...
Which query would run faster? SELECT * FROM topic_info LEFT JOIN topic_data ON topic_info.id = topic_data.id WHERE id = ? or SELECT * FROM topic_info LEFT JOIN topic_data ON topic_data.id = topic_info.id WHERE id = ? The difference is the order of expressions on the "ON" clause: the first query is checking topic_info.id aga...
I am encountering an issue where Oracle is very slow when I attempt to delete rows from a table which contains two CLOB fields. The table has millions of rows, no constraints, and the deletes are based on the Primary Key. I have rebuilt indexes and recomputed statistics, to no avail. What can I do to improve the performance of delete...
What is the command to drop all tables in SQLite? Similarly I'd like to drop all indexes. ...
I need to remove diagramming support tables, stored procs, views, etc from SQL Servrer using TSQL script. Is there such a script available? SQL 2005 and 2008. ...
Best way to allow the User to define a Table’s Order? We are using SQL Server 2005 and DevExpress controls. We have a table that contains the following: Process A Process B Process C Report A Report B Report C We want to allow the user to change the order to anything they want. Here is one example: Process C Process A Proce...
Is it a good idea to store large amounts of text (eg html pages) inside your SQL database? Or is it a better idea to store it as html files in the filesystem? The same goes for images - is it a good idea to store image data in the database or better to put them on disk? Will storing large amounts of data cause me performance problems f...
So I have a controller set up as follows: using NonStockSystem.Models; namespace NonStockSystem.Controllers { [Authorize(Users = "DOMAIN\\rburke")] public class AdminController : Controller { private NonStockSystemDataContext db = new NonStockSystemDataContext(); public ActionResult Index() { ...
Say I have table with two columns: Name, Age. I want to design a query that will return the names and ages sorted by age, but to also have an additional column which will run from 1 to N (the last row). So for a table with the following rows: John, 28 Jim, 30 Mike, 28 The following rows will be returned John, 28, 1 Mike, 28, 2 Jim,...
I've got a problem that keeps coming up with normalized databases and was looking for the best solution. Suppose I've got an album information database. I want to setup the schema in a normalized fashion, so I setup two tables - albums, which has one listing for each album, and songs, which lists all songs contained by albums. albums ...
SELECT tblIssue.SYMB, tblIssue.[PRCE], tblIssue.[Shareholder] FROM tblIssue I am trying to pull the symb and price for the maximum number of shareholder per symb. For example, I would have only 1 line for ASN where the price would be $60.62. SYMB Price Shareholder ASN $0.00 0 ASN $0.00 51 ASN $25.18 0 ASN $25.26 0 ASN $36.00 0 ASN...
In MS Sql Server is easy create autoincrement fields. In my systems I stopped to use autoincrement fields for primary keys, and now I use Guid's. It was awesome, I've got a lot of advantages with that change. But in another non-primary key fields, I really was needing implement a "soft autoincrement". It's because my system is DB indepen...
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g. CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record...
Hi I'm not good in framing questions. I will try my best. I'm creating a website and my question is related to the queries related to the site.This is the current query that I have. SELECT GPS_modem.vehicle_no, vehicle_log.longitude, vehicle_log.latitude, vehicle_log.timestamp FROM vehicle_log, GPS_modem WHERE GPS_mode...
Is it true that MS SQL restrict self-referencing constraints with ON DELETE CASCADE option? I have a table with parent-child relation, PARENT_ID column is foreign key for ID. Creating it with ON DELETE CASCADE option causes error "Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. Specify ON DELETE N...
Hi I'm trying to pull user data from 2 tables, one locally and one on a linked server, but I get the wrong results when querying the remote server. I've cut my query down to select * from SQL2.USER.dbo.people where persId = 475785 for testing and found that when I run it I get no results even though I know the person exists. (persId...
Hi, I have a table with 10 columns as col_1,col_2,.... col_10. I want to write a select statement that will select a value of one of the row and from one of these 10 columns. I have a variable that will decide which column to select from. Can such query be written where the column name is dynamically decided from a variable. ...
As a part of our build process we want to execute SQL Scripts consisting of DDL and DML statements against a fresh database instance. ADO.NET Connection/Command can't handle this without parsing and splitting the scripts. The sqlplus command line utility can execute scripts only interactively, and isn't suited for batch usage. What a...
I'm working on an ASP.NET application which is supposed to output detector data for a chosen interstate, sorted by mile marker. We determined the best way to do this would be sorting it by longitude (West and East) or latitude(North and South) depending on which direction it goes. Here's the query that populates it. SELECT [ShortWebID],...