DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

6
votes
4answers
5k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
6
votes
1answer
2k views

Changing ANSI_NULLS setting on a table

Can we use the following query to update ANSI_NULLS option. sp_configure 'allow updates', 1 reconfigure with override update sysobjects set status = status | 0x20000000 where name = 'tblClient' ...
6
votes
3answers
12k views

Get Job id OR Job name from within executing job

I have a central DB that I write job outcomes on all my servers back to. I pass 3 parameters via powershell in a SQL job back to a sp on the central server that verifies the job should be running at ...
5
votes
2answers
116 views

Algorithmic order of table indexes for table operations

I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations. I am not interested in the nuts & bolts of specific implementations; ...
5
votes
2answers
839 views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
5
votes
4answers
598 views

Group data by non-unique keys by distinct time range

I have data in my table like following... PROJ_NBR STATUS START_DT AL20 AC 1/14/2010 4:31 AL20 AC 1/14/2010 4:32 AL20 AC ...
4
votes
2answers
132 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
4
votes
2answers
6k views

What is the meaning of tinyint(N)?

When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following: mysql> create table boolean_test (var1 boolean, var2 tinyint); ...
4
votes
2answers
6k 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 ...
4
votes
0answers
137 views

How to Audit for Access with Cross Database Ownership Chaining

I've just joined the data team of a new company and we have a large SQL Server environment (100+ instances) and cross database ownership chaining is on by what I gather is a necessity. I'm looking for ...
4
votes
3answers
5k views

Test if any fields are NULL

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column. I need something like SELECT * FROM ...
3
votes
2answers
1k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) ...
3
votes
1answer
10k views

Restore Backup Fails - File 'Db' cannot be restored to '{…}.mdf'. Use WITH MOVE to identify a valid location for the file

I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer. RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak' Any ...
3
votes
3answers
3k views

View foreign key constraints so I can delete a table

I have copied a database I was working on so I could change some key elements of its design for my new version. I want to delete totally some tables from the database however I cannot due to some ...
3
votes
2answers
11k views

SQL query that concatenate values from duplicate rows in a single table

Lets say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with this table of duplicated rows. I will fix it ...
2
votes
2answers
195 views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
2
votes
1answer
325 views

How will the order of operations in this query affect my results?

I've written a query, and I'm kinda sure that it does what it want. I'm thinking, "huh, I wonder if I know how Postgres works?". I'm going to list a specific assumption about this query below, and ...
2
votes
2answers
693 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
2answers
4k views

How do I force a JOIN to use a specific index in MySQL?

I have a query that JOINs 2 tables; lineitem and part, select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem force index for join (l_pk), part where ( ...
1
vote
1answer
5k views

Set permissions on all objects for SQL users

I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
0
votes
2answers
2k views

Check Constraint with Case Statement

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of ...
-2
votes
4answers
2k views

Is there a SELECT statement to subtract rows of the same column?

Column 1 is the mem_id, Column 2 is the Transaction type C and D, and Column 3 is the Amount Owed. So I want to return Column 3 "Amount Owed", but it has to be C-D in Column 2 for each member. ...
23
votes
3answers
7k views

Performance difference for COALESCE versus ISNULL?

I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when ...
6
votes
1answer
3k views

Insert with OUTPUT correlated to sub query table

I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with ...
6
votes
2answers
1k views

MySQL connection doesn't work anymore

I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can ...
5
votes
1answer
407 views

Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently: SET ANSI_NULLS { ON | OFF } SET QUOTED_IDENTIFIER { ON | OFF } SET ANSI_PADDING { ON | OFF } ...
5
votes
1answer
2k views

How to migrate between schemas in bulk in SQL Server?

We currently have multiple databases but would like to combine them and, instead, separate our domain contexts using schemas. In MS SQL Server 2008 R2, how can I relocate all the contents of one ...
5
votes
6answers
2k views

Backup very large table

I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I have to make a backup before performing the changes. We ...
5
votes
1answer
549 views

How does SQL Server order results when joins are used?

How does SQL Server figure out the order of the records in the result set of query execution? I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I ...
5
votes
2answers
617 views

Introducing an 'age' category in a patients database

I want to do the following in my DB related to patients: when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients how ...
4
votes
1answer
3k views

How do I get the aggregate of a window function in Postgres?

I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so: CREATE TABLE foo ( perm integer[] NOT NULL, combo integer[] ...
4
votes
3answers
743 views

MySQL very slow query when changing one WHERE field despite no index/key

This is quite a confusing issue for me. I have a database full of baseball statistics. Running this query: SELECT * FROM hits JOIN stadiums ON stadiums.gameName = hits.gameName JOIN players ON ...
3
votes
1answer
48 views

the <set clause>'s <multiple column assignment>

I just know a little sql basics and need to write a plain sql syntax highlighter/checker So I'm fighting through the standard... I stumbled over somthing I'm not sure I understand it right, as I never ...
3
votes
2answers
15k views

How can I dynamically alias columns?

I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change. The possible ...
3
votes
1answer
3k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
3
votes
2answers
7k views

Query (duration) different between data value change

I am trying to write a query that would get me the duration of a status in a table. This query needs to work in SQL Server 2008. Say I have the following table: Key Value RecordDate 1 1 ...
3
votes
3answers
9k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
3
votes
2answers
3k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
3
votes
1answer
6k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
4answers
2k views

What is the best permissions to set for only creating and managing own databases?

I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of ...
3
votes
2answers
4k views

Query: All employee with sum of salary > 500

In my MYSQL Database COMPANY, I have a Table: Worker : mysql> DESC `Worker`; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
3
votes
2answers
1k views

Running a CTE query in a loop using PL/pgSQL

I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
3
votes
1answer
845 views

Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler?

I'm trying to figure out why one of my SQL server users keeps losing sysadmin role when I uninstall a particular program. Can I use the SQL Server Profiler to track down the action that causes this to ...
3
votes
1answer
3k views

Import CSV File

Currently, we have a DTS package on SQL Server 2000 that imports a file called suicoweb.csv. The DTS csv file properties pages look like this. Here is a sample line from the CSV file ...
3
votes
5answers
776 views

SQL Query with ODBC, Joins and Group Bys

I am having issues with the statement below. The main purpose of the query is to pull a list of customers into an Access db from ODBC (our customers are stored in DB2 on IBM i). The query is ...
3
votes
5answers
616 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
3
votes
5answers
5k views

How to get the total days per month between two dates?

Given two dates like 20120302 and 20120605 I need to get a list of Months and the total days in those months that fall between those two dates, like so: March 28 April 30 May 31 June 03
3
votes
1answer
325 views

SQL database restore failed

I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that ...
2
votes
2answers
70 views

SQL Insert Statement Persists Two Records When I Need Just One

I have altered it to include hardcoded variables when I pulled this chunk of code from the stored procedure. I ran it independent of the application with the same result. I need one insert statement ...
2
votes
3answers
412 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...