An acronym for "Structured Query Language" (it's not the name of a specific DBMS product), 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 ...

learn more… | top users | synonyms (1)

2
votes
1answer
25 views

Optimizing indexes

I just began optimizing my schemas with indexes (pretty late I guess). I never dealt with huge dataset but I'll be soon coping with about 1 to 5 millions rows. Here is the schema I'm using right now ...
1
vote
0answers
26 views

Possibly tempdb out of space or a system table is inconsistent

While running DBCC CHECKDB I got this error Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system ...
0
votes
1answer
34 views

Merge two SELECT queries with different WHERE clauses

I have one table of services And I need to merge two SELECT queries, both have different where clauses For example SELECT U_REGN as 'Region', COUNT(callID) as 'OpenServices', SUM(CASE WHEN ...
-9
votes
0answers
25 views

Mirroring error #1418 [on hold]

I have configured mirroring in server without witness but I am getting a error no 1418 please suggest how to solve it. Even if I tried to create a endpoint but I couldn't create it.
0
votes
1answer
16 views

Can I estimate the row count of a large mysql table using the disk space?

Sysadmin here (not a developer by trade), so go easy please ;) I have a mysql table, on a linux server that uses innodb_file_per_table. When I try to do select count(foo) from table; It doesn't ...
0
votes
0answers
9 views

Dynamically REVOKE CONNECT FROM guest in SQL Server 2005

I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts: DECLARE @database_id int, @database_name nvarchar(100); DECLARE ...
0
votes
2answers
24 views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
2
votes
1answer
20 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
0
votes
1answer
16 views

Merge left outer join column

I have a query that use left outer join. something like that : select issues.id, journal.notes from issues left outer join on (issues.id = journal.issue_id and journal.notes != '') So far it ...
0
votes
1answer
32 views

What data will be lost with DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)?

I have a corrupt database. The last known good backup was over 2 weeks ago. I've run a CHECKDB and have the page numbers where SQL Server located the corruption. I've looked at the data on the ...
1
vote
1answer
27 views

Avoiding entitized characters when using FOR XML PATH for string concatenation

I have this query: SELECT DISTINCT f1.CourseEventKey, STUFF ( ( SELECT '; ' + Title FROM ( SELECT DISTINCT ...
1
vote
0answers
28 views

Profiling parameters

I was trying to fine the parameters of a query I suspect to be causing unnecessary logical read and eventually captured it in profiler, however parameters @p3 and @p4 are not listed. Is anyone able ...
1
vote
3answers
30 views

New SQL Server 2008 instance, load old instance DB's

BACK STORY: I'm a developer with a local instance of SQL Server 2008 on my workstation. I just recently upgraded my motherboard and found out (much to my dismay) the old motherboard's RAID drivers ...
4
votes
1answer
51 views

sql indexing and performance

I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities. There are 8 columns in this ...
-3
votes
0answers
32 views

Is it possible to get the queries executed in the past? [on hold]

Is it possible to get the queries executed in the past? the server is restarted.
1
vote
0answers
32 views

Any idea why this seemingly-simple query is really slow?

Here's the query SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL WHERE r.event_id = "102577"; It seems pretty ...
0
votes
1answer
24 views

How do I use a temporary table to generate a template of results and then fill it?

So let's say I have a table, call it 'orders' that looks like this: fields: customer|order_type|price data: "Bob" |"Delivery"|100 "Nancy" |"Delivery"|200 Where customer can be any name, ...
0
votes
1answer
17 views

How can I apply heuristics to optimise an expression tree?

I've converted the following SQL query: SELECT name FROM dog d, works_at w WHERE (d.owner_name = w.person_name) AND (w.kennal_name = "Breeders"); into the following expression tree: My main ...
1
vote
1answer
39 views

Optimistic Paging and separate query for COUNT?

I am using SQL SERVER 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a Total count. There are 2 ways to achieve this. 1) Paging ...
0
votes
4answers
30 views

SQL Server 2005 database log size increasing drastically? [duplicate]

My friend works in a company (pretty small in size) that is running SagePro ERP which has a SQL server 2005 database. Now the problem is, the database size (over the period of 7 years) has increased ...
2
votes
1answer
66 views

Can comments in stored procedures cause problems

We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state ...
0
votes
0answers
16 views

Adding a new partition to the partitioned table is skipping the last filegroup

I have created one partitioned table and trying to add a new partition to that. I ran the following scripts to create a new filegroup and add a new partition to the table. But when I run the alter ...
1
vote
1answer
41 views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
0
votes
0answers
19 views

how to use joins in hierarchical queries

how to use joins in hierarchical queries my try SELECT cust_id AS child_id FROM customer c,cust_assoc_dnorm d on d.dsend_id=c.cust_id WHERE connect_by_isleaf=1 and ...
1
vote
2answers
39 views

Loop through all databases and remove all users

We are doing a conversion from one system to another which is requiring backup/restores. I am trying to create a process that will remove all users after the restore on the new servers as no user ...
1
vote
1answer
27 views

Translating a query from MS Access to SQL Server

I have a legacy query from years ago that was written for an MS Access backend: DELETE category.* FROM category LEFT JOIN product_category ON category.id = product_category.categoryid ...
0
votes
0answers
22 views

Multiple login mappings for linked server

Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB. ServerB has two databases on it and both using ...
0
votes
0answers
23 views

MySQL Query for combobox

I have two tables in my database.namely books and bookidofid. where bookidofid from books table is referenced to bookidofid from bookidofid table Table books ...
0
votes
1answer
24 views

How can I rewrite the following removing the date_format function?

I have a table with a column when that is of type datetime. I am doing the following filter which can not use and index: AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) ...
0
votes
0answers
24 views

How to use Select query just right after Insert on master in a query.?

I have a question on mysql replication. As usual, I deployed my db structure like the Update on master db, the select on slave. My questions. If I use SELECT query just right after INSERT in a query, ...
0
votes
1answer
30 views

Postgresql condition on subquery results

This is a new question based on the solution to my previous question I have the following tables: users int (pk) user_id date last_visit int (fk) local_site sites int (pk) site_id site_stats ...
2
votes
1answer
36 views

Tuning Postgres for Single connection use? Or is postgres wrong tool?

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions? I'm a social ...
0
votes
1answer
53 views

Select one single row from table b for each row from table a

I have two tables: A(name VARCHAR2(20), type VARCHAR2(20)) and B (type VARCHAR2(20), ... (20 fields more)) I need to select a single row from B for each distinct name,type from A taking into ...
2
votes
1answer
29 views

How do I sort the results of a recursive query in an expanded tree-like fashion?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It ...
-3
votes
1answer
28 views

How to avoid repeating values in a column in this case [closed]

Dear Friends help to solve this problem The query is given bellow SELECT inv_val,ind_invno , ind_invdte, ind_csm_cd, csm_name, ind_fgm_cd FROM soh_invoice, sod_invoice, som_party WHERE inh_orm_cd = ...
-3
votes
0answers
43 views

SQL Server 2014 v/s NoSQL(MongoDb, RavenDb, CouchDb) [closed]

Now SQL Server 2014 is out and there is lot of excitement regarding SQL Server 2014. How can we compare SQL Server 2014 v/s NoSQL(MongoDb, RavenDb, CouchDb)?
2
votes
2answers
92 views

How slow is slow in postgres?

I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now ...
1
vote
1answer
77 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
0
votes
0answers
22 views

Can't connect SQL Server through firewall [duplicate]

I know this has been asked before however I've tried all the solutions presented and it still does not work. I am attempting to connect to a SQL Server 2008 running on a Windows8.1 machine through ...
1
vote
2answers
33 views

Can I get OSQL if I install SQL Server Client Tools?

We have some legacy .bat scripts full of osql commands to existing SQL Server instances / dbs on our network. We've tried running the .bat scripts from our app server (Win Server 2008) but get the ...
2
votes
1answer
47 views

Making a recursive CTE like query, but for many records

I have a table with a hierarchy ( with a recursive parentID column), for one record, I know how to get the last parent record, with the following code : declare @Id integer ;WITH CTE AS ( SELECT ...
0
votes
1answer
28 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
1
vote
1answer
37 views

Select query for returning different data based on creation time

I have a view, which will Select data from table reports the select will filter the data based on multiple dates. reports table: id, key_id, position, created_at My select: pr.key_id, ...
-3
votes
0answers
56 views

Fastest way to Restore 50TB DB [closed]

I am trying to find out if there other ideas as what the fastest way is to restore a 50TB DB.
2
votes
2answers
577 views

“Cannot insert the value NULL”, but there is no NULL values!

This question follows the answer of my other question there: Insert with OUTPUT correlated to sub query table The Person.LastName column has a NOT NULL constraint. When I execute this code: CREATE ...
0
votes
0answers
31 views

Retrieve result set with proportion of some column value

I have a table called PORTFOLIOS in which I have PORTFOLIO_ID, PORTFOLIO_NAME and SCORING_RULE columns. The SCORING_RULE column can have two different values (like double_scoring or single_scoring). ...
8
votes
1answer
164 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
0
votes
1answer
67 views

How can I force a bushy tree join in Oracle SQL?

As far as I know the default join is the deep left join. How can I force a bushy tree join on 4 tables?
0
votes
1answer
33 views

How to optimize query

using SQLite3 and the following query, it seems like there should be a better performing way to write this query? Is there? Select Name, Sum(Case When Used Is ...
0
votes
0answers
32 views

SQL query to return a column per date in date range with sum of contract utilisations (value/duration)

My question is similar but more elaborate than another already in stackexchange.com "SQL query to return a column per date in date range?" I have the following data: Customer Start_date ...