Setting Constraint between two fields in SQL
I have a table, with two columns, Enter_Time and Leave_Time, how to write a CREATE TABLE script that enforces the constraint Leave_Time > Enter_Time? Edit: Microsoft Access is used ...
sql
SQL Performance - Better to Insert and Raise Exception or Check exists?
I'm considering an optimisation in a particularly heavy part of my code. It's task is to insert statistical data into a table. This data is being hit a fair amount by other programs. Otherwise I would consider using SQL Bulk inserts etc. So my question is... Is it ok to try and insert some data knowing that it might (not too often) ...
Is there a reason why I shouldn't use NVARCHAR in Sql Server?
I'm designing a database scheme right now and I figure just to be safe I should use nvarchar for my textual column's datatypes (for unicode support). While I don't expect non-english text I figure it would be better to have support it from the begining just in case. Is there any reason why I should stick with plain varchar? Performance...
Designing a database schema for a Job Listing website?
For a school project I'm making a simple Job Listing website in ASP.NET MVC (we got to choose the framework). I've thought about it awhile and this is my initial schema: JobPostings +---JobPostingID +---UserID +---Company +---JobTitle +---JobTypeID +---JobLocationID +---Description +---HowToApply +---CompanyURL ...
How do I replicate and validate data from remote systems into SQL Server
I have a task at hand of creating some kind of logic for a database. There is a IBM MQ Series setup in place today that replicates data from a couple of remote systems. At the moment it changes in the remote system and dumps the data into a staging table in a SQL Server 2005 database. I want to validate the data according to some simpl...
count VS select in LINQ - which is faster?
I'm using IQueryable<T> interfaces throughout my application and defer execution of SQL on the DB until methods like .ToList() I will need to find the Count of certain lists sometimes -without- needing to use the data in the list being counted. I know from my SQL experience that a SQL COUNT() is far less work for the DB than the equival...
Escape a period character in an SQL query
EDIT: Just realised that the reason for the additional results is down to another line in the query! Don't think I have enough rep to close this question. I'm editing some existing SQL code which is searching a Lotus Notes DB. I have this line: @Contains(Title; "blah blah 1.5") and I want to return only those records which contain e...
MySQL Client / Benchmarking Tool
I have a site hosted on a godaddy.com Linux shared hosting plan. We recently switched to a Joomla CMS so that the sales department could edit the site without breaking any of my css. The problem is now that each page takes about 4-6 seconds to load. I can load large static images just fine, so that only leaves SQL as the other culprit. E...
Basic SQL Question - Data Type Choice
Hello all, I am currently creating a small table in Oracle and am unsure of which data type to choose for a particular column. The column is labelled 'stay' and I would like it to contain only these values 'Short', 'Medium' and 'Long'. The end goal is to have these values in a drop down list within a form. Am I right in picking a data...
SQL Business Logic Wanted
I am looking for hard to follow, large, pure readable and unsupportable SQL scripts with business logic. I need something overblown to illustrate Code vs SQL business logic. May be you have one? Update: I am looking examples of terrible SQL. I am not looking for comparisons for these styles of business logic. May be you start task on in...
Temporary enabling of ON DELETE CASCADE
I'm using SQL Server 2005. Our application almost never deletes without it being a logical delete and therefore we have no need for cascading deletes. In fact, its quite a comfort knowing that the foreign key contraints give us some protection against an accidental delete statement. However, very occasionally I need to delete a top le...
Ranking in MySQL, how do I get the best performance with frequent updates and a large data set?
I want grouped ranking on a very large table, I've found a couple of solutions for this problem e.g. in this post and other places on the web. I am, however, unable to figure out the worst case complexity of these solutions. The specific problem consists of a table where each row has a number of points and a name associated. I want to be...
With MySQL, how do I insert into a table on condition that the value does not exist in another table?
I have a MySQL database and I would like to insert some values into one table, assuming that a particular value that I am inserting does not match a value in a different table. Here is a simplified/example structure: Table: invites id : int (auto-increment index) name : varchar message : varchar Table: donotinvite name...
A fresh SQL sequence per day
What would be a good way to create a fresh sequence of serial numbers on a per-day basis in a SQL database? Something like the auto-increment feature of integer columns in some database systems. I'm creating/recording transactions, and the transaction is identified by the pair 'date,serial no'. ...
converting ms sql "group by" query to linq to sql
i have two tables, products and categories. how do i convert the following sql query to linq format? select c.Name, c.DisplayName, count(p.id) from categories as c LEFT join products as p on p.categoryId = c.id group by c.Name, c.DisplayName some categories will have 0 products in them so the LEFT JOIN is important ...
How to get SQL from Hibernate Criteria API (*not* for logging)
Hi, is there an easy way to get the (to-be-generated) sql from a Hibernate Criteria? Ideally I would have something like: Criteria criteria = session.createCriteria(Operator.class); ... build up the criteria ... ... and then do something like ... String sql = criteria.toSql() (But this of course does not exist) The idea would then...
SQL - improve NOT EXISTS query performance
Hi, Is there a way I can improve this kind of SQL query performance: INSERT INTO ... WHERE NOT EXISTS(Validation...) The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT EXISTS clause if very slow. I have to do this verification because I can't insert duplicated data. I use SQLServ...
Cascading Deletes/Updates using JPA or Inside of Database?
Performance is key: Is it better to cascade deletes/updates inside of the Database or let Hibernate/JPA take care of it? Will this effect the ability to query for the data if cascades are inside of the DBMS? I am using HSQLDB if that matters. ...
How can I read/write from/to files with MSSQL 2000?
In MySQL you can quite simply read a file with SELECT load_file('foo.txt') and write to a file with SELECT 'text' INTO DUMPFILE 'foo.txt'. I am looking for a way to do this in MSSQL 2000 without using any external tools, just with queries or stored procedures. ...
"ambiguous column name" in SQLite INNER JOIN
I have two tables in a SQLite DB, INVITEM and SHOPITEM. Their shared attribute is ItemId and I want to perform an INNER JOIN. Here's the query: SELECT INVITEM.CharId AS CharId, INVITEM.ItemId AS ItemId FROM (INVITEM as INVITEM INNER JOIN SHOPITEM AS SHOPITEM ON SHOPITEM.ItemId = INVITEM.ItemId) ...