An acronym for "Structured Query Language", SQL can be used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. It can also be used to manage schemas and data access privileges.
3
votes
2answers
125 views
pessimistic locking and client death
We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
1
vote
1answer
122 views
Backup stored procedure for full, differential and log backup
I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure:
Alter PROCEDURE BackupDB
(
@BaseLocation varchar(1024),
@BackupType ...
1
vote
6answers
66 views
Performance about this simplified where-condition query
I have a query in SQL Server 2008 like this:
SELECT
a.type,
a.name,
a.startDate,
a.endDate,
b.key AS key,
b.is_locked AS is_locked,
...
3
votes
1answer
67 views
Avoiding repetition without creating a view
Suppose that I have a query Q1 and I need to run a query like the following:
Q1
union
select *
from (some query that uses Q1 outcome)
I would like to do that:
Without creating any view
Without ...
0
votes
0answers
37 views
Mysql notifier Error
i'm getting error when opening Mysql notifier High Severity Error Element 'UpdateCheck' was not found. Line 3, position 2. My mysql server is running. why it's showing this error.
1
vote
3answers
92 views
help with simple sql queries [closed]
Can I use some help please with the following query
Consider the following schema:
Suppliers(sid,sname,saddress)
Parts(pid,pname,color)
Catalog(sid->Suppliers,pid->Parts,cost)sid and pid ...
0
votes
1answer
106 views
SQL Server 2008 SP2 randomly slow down query
I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query.
It is taking 4 sec to 4 min in execution. Please suggest the possibilities.
There is no scope of query optimization. ...
0
votes
1answer
75 views
Looking for performance improvements for this query
This query is taking a long time to execute. We are expecting results in 0 sec, if possible.
Please help me to do any other following:
Rewrite the query
Suggest any indexes
Any other optimization ...
2
votes
2answers
50 views
How do I write backup procedure
I've read that a decent backup strategy would be:
1) Full backup weekly
2) Differential backups daily
3) Log backups every 5 minutes.
Now question is how do I write this stored procedure and create ...
2
votes
1answer
67 views
How to Model / Quickly test for set membership in SQL?
Each row in a table needs to be protected a list of application specific roles lets say something that looks like this example below
Desired Table Structure
pkey | data | roles
...
1
vote
1answer
14 views
Reserving dates with quantities, table schema wrong?
Okay I have a table like so.
ID Productname Quantity Image QuantityOut LastDateReserved
1 productOne 5 'image.jpg' 3 2013-03-15
2 ...
1
vote
1answer
52 views
Custom sp_who/sp_whoUsers
I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential ...
6
votes
6answers
395 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
1
vote
1answer
76 views
How to generate TPC-DS query for sql-server from templates
I know that dsqgen is used to transform the query templates into executable SQL. But when I type in the command line: "dsqgen /directory query_templates /template query1.tpl /dialect sqlserver /scale ...
1
vote
3answers
146 views
Database Design Relationship for Patient Visit in MS Access
I am designing a database to store records for research purposes, and I am having trouble picturing how I can create a relational database based on the information I have.
There are a total of 6 ...
5
votes
2answers
162 views
Defining constraints in `CREATE TABLE` statements
Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
2
votes
1answer
114 views
Selecting minimum value using a subquery
I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating.
We have a table for prices ...
0
votes
0answers
63 views
Get the count of items related to nested category in sql [closed]
I have two tables: categories and items. i have stored categories using nested set structure. Categories have items. Items can be only added to leaf nodes of a root category.
For eg:
Categories
...
2
votes
1answer
41 views
Search within same ID accross several rows
My table has the following structure:
+----+---------+------------+-------------+
| id | word_id | meaning_id | sentence_id |
+----+---------+------------+-------------+
| 1 | 1 | 15333 | ...
0
votes
2answers
53 views
Changing passphrase for EncryptByPassPhrase
I am using EncryptByPassPhrase to encrypting some columns.
If I want to change the passphrase in the future, how can I change it?
0
votes
1answer
12 views
content types of insertion
I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?
1
vote
1answer
127 views
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server
I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is ...
3
votes
1answer
39 views
Create Scheme that allows for fluidity between entities
We are a small rapidly evolving company and recently investigated switching databases from SQL Server to a graph database. We like the graph approach because of the fluidity and openness that it ...
1
vote
0answers
86 views
Collect CPU, reads and writes when replaying SQL Profiler Trace
I'm trying to replay a trace on a test environment with SQL Profiler in order to benchmark a database prior to making some performance tweaks. I intend to replay the same trace at various stages to ...
1
vote
1answer
565 views
IF NOT NULL then UPDATE else keep the value of the field
I think I am somehow close to get it work, but for some reason I still get errors.
I have the following UPDATE query:
UPDATE DeviceAttribute
SET Details = CASE Name
WHEN 'Accessories' THEN ...
0
votes
3answers
83 views
How to check if database is in mirrored mode?
I have two database servers A and B where I have databases set up in mirroring namely X. There is another database on server A named Y which is only present on database server A. Y database has a ...
0
votes
1answer
103 views
Conditionally INSERTING columns into Table
using SQL Azure and like to insert a data to a table using dynamic Query.
No of columns in select statment is dynamic. It will be based on some conditions.
Ex: Salary, Code.
How can I create a ...
3
votes
1answer
53 views
Adding column from SSMS wizard in SQL Server
Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process:
Create a New ...
2
votes
0answers
80 views
How to speed up SQL Server RIGHT JOIN on 100MM+ Records - use TVF?
I use a table insert to UNION a CTE of converting transactions to visits (converting or not) and then summarize the visit data (no details, just pages visited & txns completed) to get funnel ...
1
vote
1answer
72 views
Join Calender Table
I have a Datatable and a Calendartable.
I do a join on this tables to get sequential dates if there is no data for this day.
My problem now is that there a several id's and i need a count for each ...
4
votes
1answer
50 views
Under what circumstances can a division expression reduce the number of rows in a query's result?
I'm experiencing a problem with a query that I've written.
Given a table with the schema:
create table somevals(division varchar(100),
subdivision varchar(100),
...
1
vote
1answer
70 views
Database Relational Design for Multiple Categories
I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants ...
2
votes
1answer
182 views
SQL Server cluster questions please
We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment.
We did a test on one of the instances, and failover test.
Manual failover from node c ...
1
vote
0answers
44 views
Update table mysql and if value is 10 update another table
I've following sql to update results table:
$mysqli->query("UPDATE results
SET result_value = IF('$logo_value' - result_tries < 0 OR '$logo_value' - result_tries = 0, 1, ...
3
votes
2answers
69 views
SQL Server 2005 Database move and rationalisation - Recommended maintenance processes
We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.
I inherited this database and it came to ...
-2
votes
1answer
24 views
Can I move up rows in the memory itself?
Lets say i have the following table :
id col2 col3
1 1 1
2 2 2
3 3 3
4 4 4
And I'm trying to move rows to different ...
6
votes
1answer
164 views
how do I insert a default row?
If I create a table with an identity column as primary key, and all the other columns have default values, for example
create table rr (id int identity(1,1) primary key, dt datetime default ...
2
votes
2answers
297 views
How do I remove duplicate records in a join table in PostgreSQL?
I have a table that has a schema like this:
create_table "questions_tags", :id => false, :force => true do |t|
t.integer "question_id"
t.integer "tag_id"
end
...
1
vote
0answers
155 views
Is there a Postgres admin GUI that can execute a master file containing multiple sql files
I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file.
example: ...
3
votes
3answers
73 views
Sum Up values in a query based on other information
I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this:
I get these results by running this statement:
select distinct a.eventnum, ...
0
votes
1answer
128 views
SQL update set column = @ parameter
doeas anyone knows how to update a table where column = @parameter WHERE SELECT
Basically I have a table with the following values. Table name is UserAttribute
ID|Name |Properties|
...
1
vote
1answer
77 views
Query to determine a count by rolling interval
I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of ...
1
vote
1answer
44 views
Script to get duration
I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or ...
1
vote
1answer
82 views
How to Reorganize Microsoft Access Table
I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of ...
2
votes
2answers
83 views
Representing SQL constraints on a table
I have this table:
CREATE TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
PRIMARY KEY (`part_ID`, `date`) ,
FOREIGN KEY (`part_ID` )
REFERENCES `part` (`part_ID` );
This ...
0
votes
1answer
40 views
What index to add in case of join by two optional fields
I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional.
...
7
votes
1answer
232 views
Is there an execution difference between a JOIN condition and a WHERE condition?
Is there a performance difference between these two example queries?
Query 1:
select count(*)
from table1 a
join table2 b
on b.key_col=a.key_col
where b.tag = 'Y'
Query 2;
select ...
1
vote
1answer
75 views
Database Designing for a Ecomerce website
I am new to database designing. I am designing a database for an eCommerce website, there is a lot of products to be updated, but while designing for product specification table I do not understand ...
0
votes
3answers
18 views
Mysql - How to optimize retrival time in a table
I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table...
mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;
...
1
vote
3answers
132 views
How to model inheritance of two tables mysql
I have some tables where I store data and depending on the type of person (worker,civil) that did a job I want to store it a table 'event', now these guys rescue an animal (thereis a table animal).
...
