I have a History Table in SQL Server that basically tracks an item through a process. The item has some fixed fields that don't change throughout the process, but has a few other fields including status and Id which increment as the steps of the process increase. Basically I want to retrieve the last step for each item given a Batch Ref...
I am writing a query in which I have to get the data for only the last year What is the best way to do this SELECT ... From ... WHERE date > '8/27/2007 12:00:00 AM' ????? ...
I have a simple webform that will allow unauthenticated users to input their information, including name. I gave the name field a limit of 50 characters to coincide with my database table where the field is varchar(50), but then I started to wonder. Is it more appropriate to use something like the Text column type or should I limit the ...
I have one field that I need to sum lets say named items However that field can be part of group a or b In the end I need to have all of the items summed for group a and group b when I say grouped I mean there is a LEFT OUTER JOIN to another table the previous table has a type for the items and the one being joined has a group assigned ...
I recently had to solve this problem and find I've needed this info many times in the past so I thought I would post it. Assuming the following table def, how would you write a query to find all differences between the two? table def: CREATE TABLE feed_tbl ( code varchar(15), name varchar(40), status char(1), update char(1) CONSTRAINT...
I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same question. ...
If I have a query like: Select EmployeeId From Employee Where EmployeeTypeId IN (1,2,3) and I have an index on the EmployeeTypeId field, does SQL server still use that index? ...
Right now I am working on a solution to archive older data from a big working database to a separate archive database with the same schema. I move the data using SQL scripts and SQL Server Management Objects (SMO) from a .Net executable written in C#. The archived data should still be accessible and even (occassionally) changeable, we j...
I'm looking for a good and short book to learn SQL in general, and good modern SQL practices. Any suggestions? Thanks! ...
Before someone gets in a tizzy because this was answered elsewhere (not sure if it was,but...), the 5 "related questions" shown right now do not match what I'm looking for, and I searched the string "sql insert" and did not find quite what I was looking for. So I'm basically trying to figure out the simplest way to perform your basic in...
Let say i have four tables PAGE, USER, TAG and PAGE-TAG PAGE has fields ID, CONTENT ... TAG has fields ID, NAME ... USER has fields ID, NAME ... PAGE-TAG has fields ID, PAGE-ID, TAG-ID, USER-ID Let say i have four pages PAGE#1 'Content page 1' tagged with tag#1 by user1, tagged with tag#1 by user2 PAGE#2 'Content page 2' tagged w...
What is the simplest SQL query to find the second largest integer value in a specific column? Of course there may be duplicate values in the column. ...
How do you specify that a foreign key constraint should be a 1:1 relationship in transact sql? Is declaring the column UNIQUE enough? Below is my existing code. Thanks in advance for the help! CREATE TABLE [dbo].MyTable( [MyTablekey] INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [OtherTableKey] INT NOT NULL UNIQUE CONS...
I've been tasked with the the maintenance of a nonprofit website that recently fell victim to a SQL injection attack. Someone exploited a form on the site to add text to every available text-like field in the database (varchar, nvarchar, etc.) which, when rendered as HTML, includes and executes a JavaScript file. A Google search of the...
Currently, if I want to output a SQL script for a table in my database, in Management Studio, I can right click and output a create script. Is there an equivalent to output an SQL script for a database's maintenance plan?# Edit The company I work for has 4 servers, 3 servers and no sign of integration, each one running about 500,00...
Using oracle 10g , accessed via perl DBI , I have a table with a few tens of million rows being updated a few times per second while being read from much more frequently form another process. Soon the update frequency will increase by an order of magnitude (maybe two). Someone suggested that committing every N updates instead of after e...
I currently work with an Oracle database and we use stored procedures for all our SQL queries. The problem I have is that we do not really having a coding standard for our packages. So what happens is that every developer has a different style (or in some cases no sense of style) in how they format there packages. Making them difficult t...
Will limiting a query to one result record, improve performance in a large(ish) MySQL table if the table only has one matching result? for example select * from people where name = "Re0sless" limit 1 if there is only one record with that name? and what about if name was the primary key/ set to unique? and is it worth updating the qu...
I'm working with a SQL Server 2000 database that likely has a few dozen tables that are no longer accessed. I'd like to clear out the data that we no longer need to be maintaining, but I'm not sure how to identify which tables to remove. The database is shared by several different applications, so I can't be 100% confident that reviewi...
I'm working on a side project that would be a simple web application to maintain a list of classes and their upcoming schedules. I would really like to use Linq to SQL for this project, but unfortunately the server environment I'm developing for only has MySql available. I've dabbled briefly with Subsonic but it just doesn't get the job...