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)

1
vote
1answer
92 views

Count duplicate versions of databases (SQL Server 2008 R2)

I wish to pull statistics on the number of duplicate database versions we have running in our environment - most databases run with version control in the database name, so I built this query: SELECT ...
3
votes
0answers
547 views

Getting distinct list of records based on the MAX of a column

I have a status table (tblTestActionStatus) which has three columns ID_TestAction: references some test action ID_Status: a look up table reference for the variety of possible statuses for the ...
0
votes
0answers
42 views

How to optimize this query more?

First at all I am nood into SQL thing, Now I am working on a class project where I have some tables like Table user user_id | username | name 1 | nihan | Nihan Dip 2 | dip ...
3
votes
2answers
2k views

Row count from a certain table in multiple databases

I need to know the row count of a certain table that exists (or don't) in multiple databases. Here's what I came with: DECLARE @NomeBase as char(60) declare @base as char(100) declare @select as ...
-3
votes
1answer
653 views

How to retrieve a query from 3000 tables in SQL? [closed]

I have plan to create 3000 tables for my database. But I am afraid how will I retrieve some data from those many tables. Eg: Select * from table1, table2, table3.... table3000 where ...
2
votes
1answer
346 views

Why would a query in code be slower than a query in a SQL tool

When we face performance issues with out application, one of the first things we do is try to isolate if the problem is the code, the queries or the database itself. The most frustrating part of this ...
-3
votes
1answer
95 views

Sproc - Adding columns with matching ID column [closed]

I currently have a sproc that is used for reporting on a table similar to the one below: ID Weight QTY OtherData 1 20 12 1234 1 44 4 1234 ...
-2
votes
1answer
134 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...
3
votes
2answers
183 views

Can I run concurrent backups of multiple read only filegroups?

I have 62 readonly filegroups in a SQL server 2008 enterprise database. Can I backup multiple read only filegroups at the same time? I would assume this to be the case but given this database is over ...
1
vote
2answers
448 views

ROWNUM equivalent in Centura SQLBase (aka Gupta)

Clarification: This is NOT MS SQL server. [As there is no suitable tag for centura-database and I am not allowed creating tags I had to use this generic one - sorry about that] I have a Centura ...
0
votes
1answer
95 views

Maximum granularity of user permissions (certain rows within table?) [closed]

Our current system architecture is fairly simple. There's a global list of devices, which move between gateways. The idea is such that the global list of devices is stored in one table, such that any ...
0
votes
1answer
2k views

Getting multiplication of column values on a joined table based on condition

The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables: CREATE TABLE YEARLY_RATES ( RATE_YEAR NUMBER(4,0), RATE NUMBER(6,4) ); ...
2
votes
4answers
1k views

Is there any rule of thumb to optimize sql queries like this

this is my first question here. Although I've been helped out from this forum over a hundred times. I was having difficulties in optimizing sql query. It takes hours to execute. Record set is also ...
1
vote
1answer
2k views

How to find column with all same repeating character

I have a char(15) NOT NULL column. I need to see if it is empty or contains all zeros. I can obviously do= '000000000000000' But is there a way using LIKE so its not hard coded to 15? I don't see why ...
1
vote
1answer
570 views

Using like over charindex to search for pattern

We where using charindex to search for a string as shown below SELECT * FROM tablename WHERE ( Charindex('47a%$.abc',CAST(columnname AS VARCHAR(MAX)))>0 ) I am planning to replace charindex with ...
2
votes
2answers
95 views

Read committed snapshot:Finding lock on table

IF update command is run on a table and commit is pending then another user cannot be able to update the table. Is there any query to get the user or PC which caused lock on the table.
0
votes
1answer
127 views

Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications

We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts ...
0
votes
2answers
1k views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
-1
votes
0answers
80 views

ERROR 1136 Need help mysql [closed]

I am working on my sql via mysql. I need help with a code. I am able to create the table; however, I am having trouble installing the data into the table with out getting the ERROR 1136. CREATE ...
3
votes
2answers
2k views

SQL Query too slow on SUM function

I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is SELECT f.RetailerID , TypeID ...
2
votes
2answers
1k views

How to use OR in query with priority?

In a simple query like SELECT * FROM table WHERE id='x' OR id='y' How to give a priority to get only one of the WHERE clauses? I mean getting rows with id='x'. Only if there is no row, get row ...
24
votes
2answers
12k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
1
vote
1answer
209 views

How do I get value from parent row

I have two tables. One contains a hierarchical structure of topics and another table that contains optional configuration of that topic, that controls it's visibility in several contexts (one column ...
1
vote
1answer
2k views

A network-related or instance-specific error occurred

I have been using a local database on my laptop for the last view weeks. But today I cannot access it. I get the error: A network-related or instance-specific error occurred while establishing ...
0
votes
1answer
88 views

how to Update Value in local tabel when job Finished

I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs. In other words, I want do something like status when the all jobs ...
3
votes
2answers
509 views

Configure Email to be sent on restoring a database

Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.
1
vote
1answer
815 views

The ordinal position for a column changed without my knowledge

I have a column that is out of order (ordinally, not in terms of select statement), and I just can't figure out how it got that way. I've been researching this for quite some time and am coming up ...
0
votes
1answer
113 views

Grouping rows by looking at two columns without considering the order

I have a table looks like this one below: +----+---------+---------+ | ID | VALUE 1 | VALUE 2 | +----+---------+---------+ | 01 | A | B | | 01 | B | A | | 02 | C | D ...
1
vote
1answer
2k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
0
votes
1answer
168 views

Can I design this database better? [closed]

I have the following database design: Script to reproduce: /* DROP TABLE [dbo].[feedback] DROP TABLE [dbo].[action] DROP TABLE [dbo].[download] DROP TABLE [dbo].[user] DROP ...
0
votes
1answer
621 views

Return all possible combinations for each group

I have got a table looks like this one here shows the student ID and all classes this student took: +-------------+ | ID | Class | +-------------+ | 01 | A | | 01 | B | | 01 | C | | ...
1
vote
1answer
59 views

Conditional Counting

I'm analyzing a database of login records for a university and I'm trying to write a query that will give me an idea as to how many users are using the labs for short bursts, verse a more extended ...
0
votes
1answer
184 views

Optimizing a query with ORDER BY in a derived table

The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it? ...
1
vote
0answers
146 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
0answers
106 views

MySQL Replication fails - commands received in wrong order?

The other day, I had this issue with one of our MySQL 5.1.60 master and 5.1.61 slave setups: Last_Error: Error 'Can't create table 'foobar.#sql-4b87_2' (errno: 150)' on query. Default database: ...
1
vote
4answers
781 views

Pick one single row each time from either table?

I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime ...
3
votes
1answer
301 views

Combine - Select Into, with Insert Into Select

I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables. I'm automating a coworker's sql scripts. ...
2
votes
1answer
3k views

SQL Server select with regex

Can we use regular expression to select the item from database? The table item is like below Table column|name| 10.01.02 | a | 100.2.03 | b | 1021.10.04 | c | Now my problem is that i ...
1
vote
2answers
195 views

Using max for each sub group does not seem to work.

I have a table that looks like this in Oracle 11g: +----------------------------------------------------------+ | ACCT_NBR | MAIL_TY | ORGA | PERS | RUN_DATE | ...
3
votes
2answers
148 views

How do I deal with items that I need to repeat monthly?

I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every ...
1
vote
1answer
296 views

Data modeling membership and profiles

Anyone have an idea on how to solve this issue. Im modeling my membership and user profile DB and run into one issue I have to solve. I have this layout of tables. Membership (where all basic info ...
0
votes
1answer
451 views

Why would running a SQL query overnight break my database for the following day?

I apologise in advance if this is a duplicate question (which I bet it will be). I had a good search through and found similar questions but nothing that seemed an exact match. I wrote a report that ...
0
votes
1answer
285 views

PostgreSQL indeterminate ordering of results [closed]

When I have this query: SELECT DISTINCT "spree_products".id FROM "spree_products" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" AND ...
2
votes
1answer
2k views

Postgres nested hstore select query

I have a carts table with items hstore column. An example entry in this column is: carts.items row#1 = { "1614" => {:quantity=>"100", :price_cents=>1655}, ...
2
votes
2answers
463 views

SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname ...
0
votes
1answer
43 views

SELECT to only see Offsetting Transactions in table?

I have a temp table like this I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos. ...
3
votes
2answers
545 views

Efficiently query MAX over multiple ranges

When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria ...
3
votes
1answer
761 views

Dynamic file name for file import

I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found. DECLARE @fileName ...
2
votes
1answer
85 views

Correct database solution?

So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes ...
2
votes
1answer
68 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...