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 ...
1
vote
1answer
8 views
Query uses Primary Key Index instead of Nonclustered index defined
I have this simple query and I am confused why the index is not working.
SELECT lop.RecordID ProspectusID,
c.c_code CourseCode,
c.c_desc CourseDescription,
COALESCE(c.c_major, '') ...
0
votes
2answers
29 views
Cannot add an account [non domain account ] to SQL Server 2012
Background:
I have a application pool [ABCDEMO] in IIS. The identity of the appool is "NetworkService". I am trying to add IIS APPPOOL\ABCDEMO to my SQL SERVER 2012 on a Windows SERVER 2012.
When I ...
2
votes
4answers
119 views
Which of these queries is best for performance?
Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i ...
0
votes
1answer
10 views
Party Relationship Model in Practice - Insertions
I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community.
Here is a basic representation of what I'm doing (in pseudo sql):
Parties
- ID, ...
0
votes
0answers
22 views
All databases restoring in Suspect Mode in SQL Server 2012
I've just re-installed SQL Server 2012 (removed the evaluation copy and installed a licenced version) and now every database I try to restore, it fails to completely restore and the database sits in ...
0
votes
1answer
18 views
Setting Audit Events in SQL Server 2012
We have to do information assurance remediation on our SQL Server 2012 and came across the following finding: Check to see that all required events are being audited. So I ran these queries:
select ...
0
votes
1answer
44 views
Order by alphabet and then by numbers
I have records like
A5
A4
Z1
B2
C7
C1A
C11A
B1
B4
I want them to be sorted in this manner
A4
A5
B1
B2
B4
C1
C11A
C7
Z1
using the ORDER BY clause.
I want them to be sorted by alphabets ...
1
vote
0answers
23 views
MySQL: What criteria should I base my table column order/arrangement/grouping on?
Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...
0
votes
1answer
65 views
Not exists/Where NOT in
Doing a pie chart in crystal. Items are
Inquiries
Referrals
Followups
A referral has to have an inquiry, A followup has to have a referral.
I want to show pie chart of referrals to ...
0
votes
0answers
33 views
SQL Server JOIN with COUNT [on hold]
I have two tables I'm trying to query across. One contains an entry for every time a user tunes to a particular channel in our middleware. The second is normalized table containing station ...
1
vote
2answers
40 views
mysql: using comma-separated values
I have a simple mysql table which I use as a server booking system. A user can log in to a server, if he has a valid booking for that server. A sample data might look like this:
...
1
vote
2answers
24 views
How to get a list/result of best selling items
I'm trying to get the results of my best selling products using SQL.
My table is order_items which has the following columns.
id|product_id|qty
As there is a QTY field I have to somehow count or ...
12
votes
2answers
149 views
Index not making execution faster, and in some cases is slowing down the query. Why is it so?
I was experimenting with indexes to speed up things, but in case of a join, the index is not improving the query execution time and in some cases it is slowing things down.
The query to create test ...
2
votes
0answers
46 views
Query optimization on a large table in PostgreSQL
I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
0
votes
0answers
48 views
My queries are slow under load
I am performing a load test for my application. All my SQL queries which are fired from my application, individually look better and perform fast.
The same SQL are becoming bottleneck when they are ...
3
votes
1answer
31 views
Restoring with replace going incredibly quickly [duplicate]
So I have a database (on a SQL Server 2008 R2 SP2 instance) that is 2TB uncompressed, with a .bak file that is 500GB compressed. As I have been toying with the fastest way to restore this database ...
3
votes
1answer
121 views
Syntax of INNER JOIN nested inside OUTER JOIN vs. query results
TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.
Following up on my previous ...
1
vote
1answer
12 views
creating 2 database at the same time in db2
Do you know whether it is safe to have in one shell user db2inst1 creating a database A and having another shell the same user creating at the same time database B?
create database A # shell 1
...
2
votes
1answer
17 views
Return ResultSet with column names from postgresql Stored procedures
I have the following postgresql Function:
CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE)
RETURNS SETOF ...
1
vote
2answers
42 views
SQL Server 2000 optimzation job hasn't run for a while
Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database.
We turned it ...
0
votes
1answer
25 views
Calculating time spent on query cache lock
How can I calculate the total time my MySQL database is spending Waiting for query cache lock ?
I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the ...
0
votes
1answer
36 views
Protect SQL Server from hackers
Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me.
EXEC sp_readerrorlog 0, 1, ...
1
vote
3answers
51 views
How to determine if SQL Server will not execute parts of the script in parallel?
Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"?
USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 5);
GO
DBCC SHRINKFILE (DataFile2, ...
0
votes
0answers
26 views
Compare elapsed time for 2 oracle Sql files. (2300 pairs of sql files)
Version ORA 11G
I have some timing comparisons that I want to do on about 2300 Pairs of Oracle SQL statements. I'm trying to convince management that tuning of these 2300 would give them a ...
2
votes
1answer
50 views
Can column name be “Group” in Postgresql or in any databases
I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group".
I tried creating it but it always throw a syntax ...
1
vote
3answers
54 views
Sql server - Rebuilding Indexes - Does it affect foreign keys?
i am looking at speeding up the performance of my database, and thought that Rebuilding some indexes might help. My question is, will this invalidate Foreign Keys from other tables pointing to the ...
1
vote
1answer
41 views
Easier way to change every user in every database to default schema = dbo?
There was recently a domain migration where our SQL Server is hosted.
I have two servers and about 30 DBs on each. Every user was changed to the new domain but in doing so, their default schema ...
0
votes
1answer
35 views
Find nearest values from MySQL Table
I want to fetch nearest rates for a given value from below table.
mysql> select * from rates;
+------+----------+----------+
| ID | Type | Rate |
+------+---------------------+
| 1 | ...
1
vote
1answer
38 views
Using coalesce on an index column
Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way?
I think that it ...
1
vote
1answer
82 views
SQL Server 2008: High CPU historical queries
We have a SQL Server with 15 databases on it. This is used by many users in each database simultaneously. Recently we started getting high CPU alerts from this server. When the alert comes, I can ...
1
vote
0answers
61 views
How to manage primary key while updating [closed]
In the following table primaryKeyColumn is primary key.
To maintain the data history I always uses the values with WHERE condition(WHERE StatusColumn=1) And will set the StatusColumn to 0 if the data ...
0
votes
1answer
14 views
SQL Linked Server Passing Date Parameters
I'm using SQL Server 2008 R2 and have a linked SQL 2012 Express database. On the 2008 box I am creating a stored procedure to be used in a report. I am wanting to pass a date variable so the user ...
0
votes
0answers
20 views
Expanding the database in SQL Server 2008 R2
I have a database server DBA with .mdf and .ldf files on a SAN.
Often one of my database will not list out the tables (unable to extend the db),
Every time I have to take it offline and bring back ...
0
votes
0answers
19 views
SQL SERVER FCI with AVG on shared storage [closed]
We've WSFC.
node1 with SAN
node2 with SAN
node3 Standalone disks
I've installed the SQL FCI on Node1 and Node2 in the primary site. The main purpose of this FCI instance is to provide HA to the ...
0
votes
0answers
16 views
Replace minus symbol across whole document
A program I use has SQL & RTF files to generate reports and I'm wanting to remove all instances of the - (minus) symbol from the resulting document.
I'd tried a SELECT REPLACE but I'm not very ...
0
votes
1answer
35 views
How write mysql query corresponding this tables
Hi database professionals. I need your help
This is my database tables
I want to get this result
How can I write select query to get that result ?
0
votes
1answer
38 views
PostgreSQL 9.2 number of days in a tstzrange?
How can the number of days contained within a range be found?
For example, with these timestamp ranges, get these (integer) number of days:
tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
1
vote
0answers
30 views
How to find what prevented in getting a lock?
Sometimes my update statements fail after 3000 milliseconds with 'Lock request time out period exceeded' error. That 3000 limit is coming from my application through SET LOCK_TIMEOUT 3000 setting, ...
3
votes
2answers
51 views
Restore database to a instance in network
We are creating a application which requires sql database as backend to store data. So when user install my application he chooses a sql instance and its details. There we plan to restore the database ...
0
votes
1answer
45 views
Optimize query with similar fields
I have the below schema, fields owner_id,billing_id,tech_id,admin_id on carts table are pointing to contacts table. As you can see from the query result I have to JOIN for each field .Is there a way ...
1
vote
2answers
23 views
Create users in SQL Server 2012
I created a new login and a new user and I mapped a database in SQL Server 2012. In the login properties in Securables, we selected permissions "deny" for "View any database" for the user to see only ...
17
votes
3answers
285 views
Index on Persisted Computed column needs key lookup to get columns in the computed expression
I have a persisted computed column on a table which is simply made up concatenated columns, e.g.
CREATE TABLE dbo.T
(
ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
A ...
-2
votes
0answers
42 views
How to update a particular field of a excel sheet through pl/sql?
Is there any way to update particular field of a excel sheet column.
I am having some special character and I want to convert that to "UTF8". Here is the code:
CONVERT('мужской', 'WE8MSWIN1252', ...
2
votes
3answers
100 views
Are 2+ Foreign Keys a Bad Idea in any Association / Junction table?
(First time poster, very very long time lurker. I cannot find an answer to this question anywhere, so I now come out from the shadows to ask the experts).
Is a junction table with say 4 foreign keys ...
0
votes
2answers
48 views
Does using LIMIT improve the performance and is it noticeable?
I want to understand the following.
Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by.
Letting aside any optimizations to the query itself ...
1
vote
1answer
32 views
Should I extract / normalize the mailing address despite requirements limiting number / type of addresses?
I currently am working on a contacts application (similar to Outlook contacts combined with eVites and LinkedIn). The requirements for the application are to limit the addresses (and phone / email ...
8
votes
3answers
143 views
Fastest way to change a 600GB table indexed key datatype from INT to BIGINT
I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
0
votes
1answer
41 views
Optimize MySQL Self JOIN Query
I have a query which is running in 15+ seconds
SELECT
t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize`
FROM
`stocks` AS t1,
`stocks` AS t2
WHERE
...
0
votes
1answer
23 views
mysql trigger to insert/update date column from datetime column
probably simple question but can't get it to work:
I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them.
...
0
votes
0answers
18 views
In mySQL possible to change auto-increment ids that have huge gaps and if so how?
I am importing a 50 table relational database (I'm not a dba) with 250,000+ records in most of the tables, yet the auto increment is off in most tables, some for example showing the last ID at 5 ...
