Msg 823, Level 24, State 2 for Clustered Column Store Index in SQL Server 2014 CREATE CLUSTERED COLUMNSTORE INDEX CCI_TempTable1 ON dbo.TempTable1 ON[PRIMARY]

After firing this query I found the error.

Msg 823, Level 24, State 2, Line 20
The operating system returned error 1(Incorrect function.) to SQL Server during a read at
offset 0x0000018dc18000 in file 'D:\SQL2014\Data\TempDB.MDF'. Additional messages in the
SQL Server error log and system event log may provide more detail. This is a severe
system-level error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be
caused by many factors; for more information, see SQL Server Books Online.

Follow the steps in KB2015755: How to troubleshoot a Msg 823 error in SQL Server. Message 823 usually indicates a hardware problem:

  The 823 error message usually indicates that there is a problem with underlying storage system or the hardware or a driver that is in the path of the I/O request. You can encounter this error when there are inconsistencies in the file system or if the database file is damaged.

The typical approach is to use SQLIOSIM to stress the IO subsystem and see if errors occur, see KB231619: How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem.

If the stress does not report errors but the statement continues to consistently reproduce the issue then you should contact product support.


MongoDB: Should I combine fields for performance in limited circumstances I have a really simple MongoDB collection:

{
  "user": 1,
  "domain": "example.com",
  "apiAccess": true
}

There's an index on the "user" and "domain" fields. I always perform the same type of query on this collection and it only ever returns one result.

db.collection.find({"user" 1, "domain": "example.com"});

What I realised is because I always query on "user" and "domain" and because together they're unique I could just combine the field into one field. And I could make this the _id like so:

{
  "_id": "example.com_1",
  "apiAccess": true
}

This way I only query on one field and only need one index. I was thinking this would perform better due to these reasons however due to my limited understanding of MongoDB I'm unsure if this would be the case and I was wondering if someone could shed some light.

Without combining the fields in your data set you could just create a compound index using both.  No changes to your data necessary.  To do so, just create the index like so:

db.collection.ensureIndex({"user" : 1, "domain" : 1})

Docs are here:

http://www.mongodb.org/display/DOCS/Indexes#Indexes-CompoundKeys

Once you have created such a compound key it essentially makes an index on the leftmost element (user in my example above) redundant, and so an index on user (if it exists) could be removed.

Don't forget that the query optimizer only runs every ~1000 queries, so you will have to hint() the index to make sure it is used if you are testing it out.


Join multiple tables for aggregate or create a single table with combined attribes I have the following ER diagram.

Now i have option of going with a single table ARTDATA as mentioned,
or,
I can go with 13 different tables linked with each other with relations.

Problem is for each art i am gonna have to display all the attributes, i.e. For each ART i'll have to fetch TYPE, AUTHOR, TIMEFRAME, FORM, LOCATION AND SCHOOL. And for that i'll have to join all these tables together and that might take time.
However i can always go with the alternative of single table ARTDATA and get everything in one place.

Given my data set is large (50000+ entries) and will grow in future, what would be best solution to go with?

Assuming i am going with 13 tables, what would be the best query to merge all tables? I'll put my pathetic attempt below.

select
t1.ID,
t1.TITLE,
t1.DATE,
t1.TECHNIQUE,
t1.URL,
t3.AUTHOR,
t3.BORN_DIED,
t5.FORM,
t7.LOCATION,
t9.SCHOOL,
t11.TIMEFRAME,
t13.TYPE

from
ART t1 inner join

ART_AUTHOR t2 on t1.id=t2.art_id inner join
AUTHOR t3 on t3.id=t2.author_id inner join

ART_FORM t4 on t1.id=t4.art_id inner join
FORM t5 on t4.form_id=t5.id inner join

ART_LOCATION t6 on t1.id=t6.art_id inner join
LOCATION t7 on t6.location_id=t7.id inner join

ART_SCHOOL t8 on t1.id=t8.art_id inner join
SCHOOL t9 on t8.school_id=t9.id inner join

ART_TIMEFRAME t10 on t1.id=t10.art_id inner join
TIMEFRAME t11 on t10.timeframe_id=t11.id inner join

ART_TYPE t12 on t1.id=t12.art_id inner join
TYPE t13 on t12.type_id=t13.id;

You get the gist, that is barbaric.

UPDATE:

I have updated the tables and created a view called ARTDATA using above query. Now here is the update ER.

If you need to have multiple values (like one ART having several AUTHORs), you need this kind of linking. If that is not needed, just have all the ids in ART table.

I'd recommend you move to ART all attribute ids where a single value is enough and leave only the multivalued ones using the more complex kind of linking via external link table.


MySQL Available Cars With multiple owners I have a complex SQL question. We are building a car rental system. We have a car model table, a owner table and a link_model_owner table. In addition our bookings are kept in another table.

Scheme looks like this:

Now I need a query to search all available cars of a certain model. I've tried the following Query. However, this query will give multiple results if a linkId is added to the booking database and startDate and endDate are empty there.

I just want to have a list of unique available linkId's where the car is not rented out (NOW () between startDate and endDate).

Bear in mind that the booking database will expect to grow with 1000's a day.

SELECT link_model_owner.id,
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON link_book_user.id = bookings.bookLinkId
INNER JOIN owners ON link_book_user.userId = owners.id
WHERE link_model_owner.isActive = '1'
  AND link_model_owner.modelid = '252'
  AND COALESCE(NOW() NOT BETWEEN bookings.startDate AND bookings.endDate, TRUE);

It wasn't completely obvious what you wanted to use all tables for, especially link_book_user, but I focused on your main question where you wanted to get all cars that were not currently being booked, taking into consideration that startTime and endTime can be null.

In the query suggestion below I filter bookings on the date already when they're being linked in the join and then the query becomes a bit easier to construct. If a booking has null values as dates the car will be seen as free.

SELECT link_model_owner.id,
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON (link_model_owner.id = bookings.linkId
                        AND bookings.startDate <= now()
                        AND bookings.endDate >= now())
INNER JOIN owners ON owners.id = link_model_owner.ownerId
WHERE bookings.id IS null;

Note: It's important to have indexes on startDate and endDate since your database will grow quite some (as you wrote).


date aggregation for average temperatures from 2 different tables I have two tables with temperatures,
if I use this query:

SET @odd:='2018-10-28';  SET @doo:='2018-11-03';

( SELECT
    DATE_FORMAT(T1.temp_data_dodania, '%Y-%m-%d') as D1,
    ROUND(avg(T1.temp_t1), 1) as T01_01,
    ROUND(avg(T1.temp_t2), 1) as T01_02,
    ROUND(avg(T1.temp_t3), 1) as T01_03,
    ROUND(avg(T1.temp_t4), 1) as T01_04,
    ROUND(avg(T1.temp_t5), 1) as T01_05,
    '' as T02_01,
    '' as T02_02,
    '' as T02_03,
    '' as T02_04,
    '' as T02_05
FROM temp.temp1 T1
WHERE T1.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D1 )

UNION

( SELECT
    DATE_FORMAT(T2.temp_data_dodania,'%Y-%m-%d') as D2,
    '',
    '',
    '',
    '',
    '',
    ROUND(avg(T2.temp_t1), 1),
    ROUND(avg(T2.temp_t2), 1),
    ROUND(avg(T2.temp_t3), 1),
    ROUND(avg(T2.temp_t4), 1),
    ROUND(avg(T2.temp_t5), 1)
 FROM temp.temp2 T2
WHERE T2.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D2 ) ;

i get the correct data

but ...

how to aggregate this data into one line with a date?

thank you in advance for your help
Tadziu.

Instead of doing a UNION how about just doing a JOIN on them?

SET @odd:='2018-10-28';  SET @doo:='2018-11-03';

SELECT D1, T1_01, T1_02, T1_03, T1_04, T1_05, T2_01, T2_02, T2_03, T2_04, T2_05
FROM
( SELECT
    DATE_FORMAT(T1.temp_data_dodania, '%Y-%m-%d') as D1,
    ROUND(avg(T1.temp_t1), 1) as T01_01,
    ROUND(avg(T1.temp_t2), 1) as T01_02,
    ROUND(avg(T1.temp_t3), 1) as T01_03,
    ROUND(avg(T1.temp_t4), 1) as T01_04,
    ROUND(avg(T1.temp_t5), 1) as T01_05,
FROM temp.temp1 T1
WHERE T1.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D1 ) A

JOIN

( SELECT
    DATE_FORMAT(T2.temp_data_dodania,'%Y-%m-%d') as D2,
    ROUND(avg(T2.temp_t1), 1) AS T2_01,
    ROUND(avg(T2.temp_t2), 1) AS T2_02,
    ROUND(avg(T2.temp_t3), 1) AS T2_03,
    ROUND(avg(T2.temp_t4), 1) AS T2_04,
    ROUND(avg(T2.temp_t5), 1) AS T2_05
 FROM temp.temp2 T2
WHERE T2.temp_data_dodania
   BETWEEN @odd AND @doo
GROUP BY D2 ) B
ON A.D1 = B.D2;


Frequency of SQL DB Full and Differential backups I want to know what would be the best frequency in taking SQL DB -  full and differential backups looking at below particular scenario.

I have seven databases and each needs to be backup.

Currently, I have setup a couple of maintenance plan in SQL as below.

Clean Full Backup every night at 1AM, older than 7 days
Full Backup every night at 2AM
Differential Backup every 1 hour between 5AM to 10PM
Clean Differential Backup Daily 10:30PM, older than 1 day

Considering the full backup size of each Database is 50GB. What would be the optimal way to take backups with 1TB of drive space.

Note that I have different drives for Full and Differential backups of size 1TB and 500GB respectively.

Any suggestions?

The backup policies are actually based on your RPO. But here is my suggestion.
 Use Ola Hallengren's backup script.

If your total size of the DB is < 1 TB

Full Backup - daily 1AM - Retention 7 Days
Differential Backup - Every 12 Hours - Retention 3 days.
Log Backup - Every 15mins - Retention period 1 day.

If your total DB size > 1TB

Full Backup - Weekly once
Differential Backup - Daily once/twice (Based on the database load)
Log Backup - every 15min/30mins


MySQL using AUTO_INCREMENT of secondary coloum of index, and FOREIGN KEY at the same time I want to create a pair of table with a structure along the lines of:

CREATE TABLE tableA (
    ext_id bigint NOT NULL,
    local_id int AUTO_INCREMENT NOT NULL, /* <-- Requires MyISAM (InnoDB doesn't support this) */
    PRIMARY KEY (ext_id, local_id),
    );

CREATE TABLE tableB (
    ext_id bigint NOT NULL,
    local_id int NOT NULL,
    CONSTRAINT FOREIGN KEY(ext_id, local_id) REFERENCES tableA(ext_id, local_id) ON DELETE CASCADE ON UPDATE CASCADE /* <-- Requires InnoDB (MyISAM doesn't support this) */
    );

However as stated in the comments these features seam to be mutually exclusive based on the database engines that support them i.e. InnoDB doesn't support auto_increment on local_id on tableA, and MyISAM doesn't support tyhe FOREIGN KEY in tableB referencing tableA.

I'm looking for a way to simulate the missing feature on one of the database engines.

I could use a trigger to simulate the ON DELETE/UPDATE CASCADE part of the foreign key, but I don't believe this will work for enforcing the existence of (ext_id, local_id) in tableA when inserted into tableB. Beyond this I have no ideas on how I can solve this problem.

I discuss the problem here, including a couple of workarounds.

The simple workaround is to have local_id be AUTO_INCREMENT and abandon the requirement for the values starting over at 1.

Keep in mind that ROLLBACK may cause you to lose ids.


Postgres 9.5 deadlock with many updates on a table + many "select for update" on another table I have two tables: "machines" and "magictools". "magictools" refers to "machines" with a foreign key.
I face a deadlock problem while executing many of these requests:
//this will produce an "AccessExclusiveLock" of type "tuple" on machines
SELECT FROM * machines FOR UPDATE where id = :id;
//this will produce a "RowExclusiveLock" on magictools and a "RowShareLock" on machines
UPDATE magictools SET collections = "large-json" where id = :id

As far I understood running many of these request produces deadlocks. Maybe it's only the updates who are performing this, I don't know.
How should I avoid the deadlocks in this case?
I have many indexes on these tables, maybe I have too many indexes?
Below is the report of pg_activity when the problem happened. I do not understand the different modes and lock types, and, just, what happens here?
Is it possible that just an update without any transaction causes a deadlock?
deadlock trace link

There is no deterministic ORDER BY in:
SELECT FROM * machines FOR UPDATE;

So multiple transactions might try to obtain row locks in different (arbitrary) order and interlock.
Also, why would you lock the whole table machines for a single update in magictools? If it's indeed a single UPDATE on magictools per transaction, there is no need to lock rows machines manually at all. Just drop the command. (If there are multiple, then your question is lacking.)
If there is a good reason to lock many or all rows in machines, either do it in deterministic order, like:
SELECT FROM * machines ORDER BY id FOR UPDATE;

Or lock the whole table instead, that's also cheaper:
LOCK TABLE machines IN SHARE MODE;

SHARE MODE is enough for the FK case you have. There is even an example in the manual suggesting as much.
Locking the table also disallows INSERT, of course, unlike the row level locks in your example.


Detail of all transactions on a specific row I've a User table and It's data can be added/ updated from a User Screen. I want detail of all transactions on any row.
For example, I have data in table like this

Name - Age -  Salary
John    -  25 - $10
Jazy    -  23 - $10

Then some user update john's Age like this

Name - Age -  Salary
John    -  20 - $10
Jazy    -  23 - $15

and then Update its Salary like this

Name - Age -  Salary
John    -  20 - $20
Jazy    -  23 - $15

I want a query to get all transactions on John's records like this

John    -  25 - $10
John    -  20 - $10
John    -  20 - $20

Is there any way to get data like this??

Edit: Forgot that Change data capture is already in 2008, you could use it to capture the data, or create your own log/history table using triggers or something similar.

If you were in SQL Server 2016, then you could use temporal tables


Cross Join and Left Join within a query I have query which has a cross join and also a left join which produces an error

 Unknown column 'a.hour' in 'on clause'

simplified version of query is like this

SELECT b.*,a.hour,c.datetime
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

but when this query written as below it works.

select f.*,c.datetime
from
(select a.Hour,b.sec FROM a, b)f
LEFT JOIN c ON hour(c.datetime) = f.hour;

even this query works but result is not what I expect

select b.*,a.hour.c.datetime
from
a
LEFT JOIN c ON hour(c.datetime) = hrs.hour,b;

This looks very strange to me. Can anybody explain to me why is this happening? I have tried this on mysql 5.0 and 5.6. both having the same result.

Bellow is my actual query That i am working on. If it looks too large and awkward is because I am trying to achieve full outer join without repeating one of the main queries twice. (Left And Right Join). Hrs table contains 1 to 24 and text like '1:00 to 2:00 Pm'.

SELECT DnT.text,IFNULL(BrowsingTime.username,upAppUsage.username) as  username,dDate,
IFNULL(BrowsingTime.wDay,upAppUsage.wDay) as wDay,
productive,
unProductive,
Total,
Game,
Chat,
Video
FROM
(
    SELECT hrs.hour,hrs.text,dates.dDate
    FROM
    hrs,
    -- ALL THE user,DATE WITH REPORT
    (
        SELECT date(surfDateTime) as dDate,userid
        FROM browsingtime
        WHERE
        DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
        UNION
        SELECT date(snapshotDateTime)as dDate,userid
        FROM AppUsage_user_hour
        WHERE
        DATE(snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
    )dates
) AS DnT
LEFT JOIN
-- USER BROWSING TIME PER HOUR PER DATE
(
    SELECT rb.userId,CONCAT('(',user.id,') ',user.username) AS username,
    date(surfDateTime) surfdate, HOUR(surfDateTime)as hr,DAYNAME(surfDateTime) AS wDay,
    SUM(IF(up.isProductive = 1,rb.duration,0)) as productive ,
    SUM(IF(up.isProductive = 0,rb.duration,0)) as unProductive,
    SUM(rb.duration) as Total
    FROM `user`
    INNER JOIN usergroup ON usergroup.id = `user`.userGroupId
    INNER JOIN browsingtime rb ON `user`.id =  rb.userId
    LEFT OUTER JOIN url_productive up ON up.urlshortid = rb.urlShortId
    WHERE
    DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
    AND user.userGroupId IN (1,2,3,4,5,6,7,8,9,10)
    GROUP BY surfdate, hr,rb.userId
)as BrowsingTime ON DnT.hour = BrowsingTime.hr AND BrowsingTime.surfdate = DnT.dDate AND DnT.userId = BrowsingTime.userId
LEFT JOIN
-- APPLICATION USAGE
(
    SELECT r.userId,CONCAT('(',u.id,') ',u.username) AS username,
    date(r.snapshotDateTime)as usageDate, HOUR(r.snapshotDateTime)as hr,DAYNAME(r.snapshotDateTime) AS wDay,
    SUM(IF(sl.categoryId = 2 , softwareActiveTime,0)) as Game ,
    SUM(IF(sl.categoryId = 9 , softwareActiveTime,0)) as chat ,
    SUM(IF(sl.categoryId = 8 , softwareActiveTime,0)) as video
    FROM AppUsage_user_hour r
    INNER JOIN USER u ON u.id = r.userId
    INNER JOIN usergroup ON usergroup.id = u.userGroupId
    INNER JOIN clientpc cp ON cp.id = r.pcId
    INNER JOIN softwares sl ON sl.id = r.softwareId
    WHERE sl.categoryId IN (2,8,9)
    AND  DATE(r.snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
    GROUP BY usageDate,r.userId
)as upAppUsage ON DnT.hour = upAppUsage.hr AND upAppUsage.usageDate = DnT.dDate AND DnT.userId = upAppUsage.userid
WHERE
IFNULL(BrowsingTime.userId,upAppUsage.userId) IS NOT NULL;

Your query

SELECT b.*
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

means (parentheses added)

SELECT b.*
FROM
a,
(b LEFT JOIN c ON hour(c.datetime) = a.hour);

and that means a is not visible in the join. You may need to shuffle it around a bit

SELECT b.*
FROM
(a LEFT JOIN c ON hour(c.datetime) = a.hour), b;

http://sqlfiddle.com/#!9/4114a/2

That said, such cross-join seems weird, so make sure you actually need it that way.

Modified after the comment http://sqlfiddle.com/#!9/4114a/3

SELECT b.*
FROM
(a,b)
LEFT JOIN c ON hour(c.datetime) = a.hour;


How we take dump with batch of records option? we have 30 GB database.we want to take backup of this database.

here the question is we want to take the backup batch of every 10k records.

How we can take batch of records backup using mysqldump

Here is something you may find intriguing
SUGGESTION #1 : Let mysqldump create the batches
mysqldump already uses an option called --extended-insert. This will group a series of records that can be loaded in a single INSERT command. While you cannot use mysqldump to control the number of rows to be batched together, you can parse a mysqldump. Of course, these means you will have to script it in the language of your choice (Python, Perl, C++, etc).
For any given table in a mysqldump of a single database, you will see the following paradigm:
CREATE TABLE
LOCK TABLE
INSERT INTO ...
INSERT INTO ...
INSERT INTO ...
...
...
INSERT INTO ...
INSERT INTO ...
UNLOCK TABLES;

You could parse the mysqldump as follows:

Step01 : Get the next table name in the mysqldump
Step02 : Locate next INSERT INTO or UNLOCK TABLES;
Step03 : If you encounter UNLOCK TABLES;, goto Step01
Step04 : If you have no more tables, you are done
Step05 : Goto Step02

Each INSERT command you read has a certain number of rows. This number you cannot control unless you script to put them together in the number of rows you choose. I would advise against that be mysqld decided the best number of rows for that particular batch given the current configuration settings (perhaps the max_allowed_packet)
SUGGESTION #2 : Use --skip-extended-insert and batch them your way
When you deactivate --extended-insert, each INSERT command will have one row. You can collect the rows using a script and collect as many rows as you like.
CAVEATS

Please be a little conservative on the number of rows you choose to batches together.
You will take much longer to parse the mysqldump because of the mysqldump will be much longer.


how auto_increment work in mysql im confused here , i create a table and have 2 columns id and name.

Now id is primary key , not null and auto increment.

Suppose i fire: insert into table (name) values('test');

this will give me:

1  | test

Now if i fire : alter table table_name auto_increment = 5;

this will populate id with 5 on next insert.

now if i delete value of id =5 and again re-insert : insert into table (name) values ('test2');

it gives me :

 6  | test2

why it doesn't give me 5 again?

Also is it not possible that we can't reset the existing auto_increment values to desired one in MySql?

I think you will find the answer in MySQL documentation:
http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html

Note in particular how the 'innodb_autoinc_lock_mode' option/variable affects this. In MySQL 5.1+ this variable is "1" as default - in 5.0 and before it was "0". The reason to set it to "1" is that it improves performance of replication in some scenarios. If you don't use replication it makes no difference and setting it to "0" should avoid the 'gaps' in the autoincrement counter in most cases.

But there is not - and never was - any guarantee (in MySQL or any RDBMS) that auto_increment values will always be sequential without gaps. The purpose of auto_increment values is - and ONLY is - to ensure a unique identifcation of each row. The value itself has no importance.


Cosmos DB ODBC driver installation problem We are trying to install Cosmos DB ODBC Driver following the instructions provided in this Microsoft Azure resource and are running into the following error:

  The setup routines for Microsoft DocumentDB ODBC Driver ODBC driver could not be loaded due to a system error code 126: The specified module could not be found. (C:\Program Files\Microsoft DocumentDB ODBC Driver\lib\DocumentDBODBC_sb64.dll).

We've tried unsuccessfully on Windows Server 2008R2 and Windows Server 2012R2.  We have it working on several Windows 10 desktops, just not server OS's unfortunately.  We tried several things like installing using run as administrator, from command line, with both 32 and 64 bit installed, and then one or the other.  We opened the location of the DLL to 'Everyone/full control' We checked that the Visual C++ redistributables are installed although not sure which specific ones are needed.

Any advice would be greatly appreciated.

Try installing Microsoft Visual C++ 2012, 2013 and 2015. We had the exact same issue with Cosmos ODBC Drivers and this resolved it in Windows 2012 R2 server.


Entering command 'sqlplus' results in 'sqlplus: command not found' I've recently installed Fedora 19 on an older PC, to serve as a test server of sorts. After a lot of hiccups, I was able to install Oracle 10g XE.

Because I'm using Linux - which I, admittedly, have little experience of - I used the command line (rpm) to install the file.

Everything proceeded without a hitch, though I didn't understand much of it.

When I try to log in using sqlplus, though, the command line returns:

sqlplus: command not found

Now, I did some digging around. First thing I did was to check if the server is up and running. So I entered the following:

sudo etc/init.d/oracle-xe start

Take note that being a Linux noob, I actually have no idea if this command holds true to every Linux installation. All I know is that it works on my friend's PC. My PC, on the other hand, produces some interesting results:

Starting oracle-xe (via systemctl): Job for oracle-xe.service failed

Tried the following:

sudo etc/init.d/oracle-xe start

With the following results:

  /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/lsnrctl:
  /lib/ld-linux.so.2: bad ELF interpreter: no such file or directory

I don't know who this ELF guy is, but at this point, I assumed I'm screwed. After more research on the internet, I found out something about Oracle variables and paths not setting themselves correctly, but none of them were of any help.

sqlplus: command not found

Go To Oracle Home

Any ideas?

Additional Info:

Oracle Database Installed: Oracle XE 10.2.0.1.1 i386 (rpm file)
Operating System: Fedora x86-84, Release 19

The Oracle Installation Guide contains a list of packages that need to be installed prior to Oracle. A common cause of this problem might also be if you are trying to install a 32bit version of Oracle on a 64bit version of Linux.


create additional oracle 11g database in linux I have already created a database XE on my linux desktop.Please find the below information about the database.

  ORACLE_SID=XE

  ORACLE_BASE=/u01/app/oracle

  ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe

I have to create another database,eg: database name is DB1. My question is how i can set ORACLE_HOME for DB1 and other parameters in pfile ?

Also i can avoid the error

ORA-45301: XE Edition single instance violation error

Oracle Database XE Installation and Execution Restrictions

  Only one installation of Oracle Database XE can be performed on a
  single computer. This does not affect any existing installation or new
  installations of Oracle Database 11g Standard Edition, Oracle Database
  11g Standard Edition One, or Oracle Database 11g Enterprise Edition.
  In addition, users can run only one instance of the Oracle Database XE
  database on each individual computer. To run more than one Oracle
  Database server instance or install more than one copy of the database
  software, upgrade to Oracle Database 11g Standard Edition, Oracle
  Database 11g Standard Edition One, or Oracle Database 11g Enterprise
  Edition.


T SQL/ Reporting Question I have a view that is pulling sales from multiple companies and I need to basically 'combine' two rows so they display as one.  Here is a picture of what the data looks like:

I need to take all rows for Wine Group and add them into Other/Us Direct and then delete Wine Group or Filter it out so it doesn't show after adding it's values.   I tried writing a case statement and also just tried to aggregate the sum of values and union it to the existing view but it still displays them as separate entities.  Any ideas or tips would be much appreciated!

Here is current view query that pulls from the table that has this information:

SELECT  [LoadDate]
      ,[Company]
      ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
      ,[ShipToCountryCode]
      ,[LLCCustName]
      ,[TodayGrossSalesUSD]
      ,[TodayGrossSalesEURO]
      ,[CurrMonthInvoiceUSD]
      ,[CurrMonthInvoiceEURO]
      ,[CurrMonthShipUSD]
      ,[CurrMonthShipEURO]
      ,[CurrMonthBudgetUSD]
      ,[CurrMonthBudgetEURO]
      ,[CurrMonthLinearUSD]
      ,[CurrMonthLinearEURO]
      ,[LastYearCurrMonthUSD]
      ,[LastYearCurrMonthEURO]
      ,[LastYearCurrMonthLinearUSD]
      ,[LastYearCurrMonthLinearEURO]
      ,[TodayOrderIntakeUSD]
      ,[TodayOrderIntakeEUR]
      ,[TodayVolume]
      ,[CurrMonthInvoiceVolume]
      ,[CurrMonthShipVolume]
      ,[CurrMonthBudgetVolume]
      ,[CurrMonthLinearVolume]
      ,[LastYearCurrMonthVolume]
      ,[LastYearCurrMonthLinearVolume]
      ,[TodayOrderIntakeVolume]
      ,[NextMonthVolume]
      ,[NextMonthGrossSalesUSD]
      ,[NextMonthGrossSalesEURO]
      ,[LastYearCurrMonthAsOfVolume]
      ,[LastYearCurrMonthAsOfUSD]
      ,[LastYearCurrMonthAsOfEURO]
      ,[CurrMonthForecastVolume]
      ,[CurrMonthForecastUSD]
      ,[CurrMonthForecastEURO]
      ,DayType
  FROM [BI_DB].[dbo].[DailySummary]

UNION

SELECT
DISTINCT
LoadDate,
Company,
CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup,
[ShipToCountryCode],
[LLCCustName],
NULL AS [TodayGrossSalesUSD],
NULL AS [TodayGrossSalesEURO],
NULL AS [CurrMonthInvoiceUSD],
NULL AS [CurrMonthInvoiceEURO],
NULL AS [CurrMonthShipUSD],
NULL AS [CurrMonthShipEURO],
[CurrMonthBudgetUSD],
[CurrMonthBudgetEURO],
[CurrMonthLinearUSD],
[CurrMonthLinearEURO],
NULL AS [LastYearCurrMonthUSD],
NULL AS [LastYearCurrMonthEURO],
NULL AS [LastYearCurrMonthLinearUSD],
NULL AS [LastYearCurrMonthLinearEURO],
NULL AS [TodayOrderIntakeUSD],
NULL AS [TodayOrderIntakeEUR],
NULL AS [TodayVolume],
NULL AS [CurrMonthInvoiceVolume],
NULL AS [CurrMonthShipVolume],
[CurrMonthBudgetVolume],
[CurrMonthLinearVolume],
NULL AS [LastYearCurrMonthVolume],
[LastYearCurrMonthLinearVolume],
NULL AS [TodayOrderIntakeVolume],
NULL AS [NextMonthVolume],
NULL AS [NextMonthGrossSalesUSD],
NULL AS [NextMonthGrossSalesEURO],
NULL AS [LastYearCurrMonthAsOfVolume],
NULL AS [LastYearCurrMonthAsOfUSD],
NULL AS [LastYearCurrMonthAsOfEURO],
[CurrMonthForecastVolume],
[CurrMonthForecastUSD],
[CurrMonthForecastEURO],
DayType
  FROM [BI_DB].[dbo].[DailySummaryFullMonth]
WHERE LoadDate > (SELECT MAX(LoadDate) FROM [BI_DB].[dbo].[DailySummary])

This should address the jist of your question.  You will need to check the aggregation to make sure it is appropriate for the data, though at a quick glance everything seemed to be good with SUM or Group.  You may want to replace the CTE with a real table as well.

;WITH
LLCCustNameMapping AS (
    SELECT * FROM (VALUES
        ('Wine Group', 'Other/Us Direct')
    )V(From, To)
)

  SELECT  [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,SUM([TodayGrossSalesUSD]) [TodayGrossSalesUSD]
       ,SUM([TodayGrossSalesEURO]) [TodayGrossSalesEURO]
       ,SUM([CurrMonthInvoiceUSD]) [CurrMonthInvoiceUSD]
       ,SUM([CurrMonthInvoiceEURO]) [CurrMonthInvoiceEURO]
       ,SUM([CurrMonthShipUSD]) [CurrMonthShipUSD]
       ,SUM([CurrMonthShipEURO]) [CurrMonthShipEURO]
       ,SUM([CurrMonthBudgetUSD]) [CurrMonthBudgetUSD]
       ,SUM([CurrMonthBudgetEURO]) [CurrMonthBudgetEURO]
       ,SUM([CurrMonthLinearUSD]) [CurrMonthLinearUSD]
       ,SUM([CurrMonthLinearEURO]) [CurrMonthLinearEURO]
       ,SUM([LastYearCurrMonthUSD]) [LastYearCurrMonthUSD]
       ,SUM([LastYearCurrMonthEURO]) [LastYearCurrMonthEURO]
       ,SUM([LastYearCurrMonthLinearUSD]) [LastYearCurrMonthLinearUSD]
       ,SUM([LastYearCurrMonthLinearEURO]) [LastYearCurrMonthLinearEURO]
       ,SUM([TodayOrderIntakeUSD]) [TodayOrderIntakeUSD]
       ,SUM([TodayOrderIntakeEUR]) [TodayOrderIntakeEUR]
       ,SUM([TodayVolume]) [TodayVolume]
       ,SUM([CurrMonthInvoiceVolume]) [CurrMonthInvoiceVolume]
       ,SUM([CurrMonthShipVolume]) [CurrMonthShipVolume]
       ,SUM([CurrMonthBudgetVolume]) [CurrMonthBudgetVolume]
       ,SUM([CurrMonthLinearVolume]) [CurrMonthLinearVolume]
       ,SUM([LastYearCurrMonthVolume]) [LastYearCurrMonthVolume]
       ,SUM([LastYearCurrMonthLinearVolume]) [LastYearCurrMonthLinearVolume]
       ,SUM([TodayOrderIntakeVolume]) [TodayOrderIntakeVolume]
       ,SUM([NextMonthVolume]) [NextMonthVolume]
       ,SUM([NextMonthGrossSalesUSD]) [NextMonthGrossSalesUSD]
       ,SUM([NextMonthGrossSalesEURO]) [NextMonthGrossSalesEURO]
       ,SUM([LastYearCurrMonthAsOfVolume]) [LastYearCurrMonthAsOfVolume]
       ,SUM([LastYearCurrMonthAsOfUSD]) [LastYearCurrMonthAsOfUSD]
       ,SUM([LastYearCurrMonthAsOfEURO]) [LastYearCurrMonthAsOfEURO]
       ,SUM([CurrMonthForecastVolume]) [CurrMonthForecastVolume]
       ,SUM([CurrMonthForecastUSD]) [CurrMonthForecastUSD]
       ,SUM([CurrMonthForecastEURO]) [CurrMonthForecastEURO]
       ,DayType
  FROM [BI_DB].[dbo].[DailySummary] s
    LEFT JOIN LLCCustNameMapping map ON s.LLCCustName = map.From
  WHERE [LLCCustName] IN ('Wine Group', 'Other/Us Direct')
  GROUP BY [LoadDate]
       ,[Company]
       ,CASE WHEN [ReportGroup] = 'Germany' THEN 'Direct Germany' ELSE ReportGroup END AS ReportGroup
       ,[ShipToCountryCode]
       ,IsNull(map.To,  [LLCCustName]) [LLCCustName]
       ,DayType


How to execute sqlplus by root using sudo in linux? Scenario:

*oracle xe11g r2 is installed( and runs properly when logged in as oracle).
*logged in as user, root
*added root to groups oinstall and dba as follows:

#useradd -G oinstall root
#useradd -G dba root

*environment variables ORACLE_HOME and ORACLE_SID are set as follows:

#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
#export ORACLE_SID=XE
#export PATH=$ORACLE_HOME/bin:$PATH

and executed:

#sudo -u oracle sqlplus / as sysdba

Output:

sudo: sqlplus: command not found

Please guide me in running sqlplus by root using sudo.

Not an Oracle error.

Sure you can put the above variables in .bash_profile of oracle, but when you use sudo like above, that will not be executed.

[root@o71 ~]# tail -3 /home/oracle/.bash_profile
export ORACLE_HOME=/u01/app/oracle/product/dbhome_1
export ORACLE_SID=ETALON
export PATH=$ORACLE_HOME/bin:$PATH
[root@o71 ~]# sudo -u oracle sqlplus / as sysdba
sudo: sqlplus: command not found

One of the many ways to work around this:

[root@o71 ~]# sudo -u oracle bash -c '. ~/.bash_profile; sqlplus / as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 28 10:52:43 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>

I am not a fan of using .bash_profile for setting the Oracle environment, I suggest using oraenv or a custom script that is not executed automatically.


Getting "BadImageFormatException" when attempting to connect through Visual Studio to Oracle I get the following message: “Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.” when I attempt to create a connection from both Visual Studio 2008 and 2012 to Oracle from my Windows 8 64bit workstation.

I’m not running a web app when this message happens. I’m only trying to establish an Oracle connection with Visual Studio.
Any solutions posted on the internet that I have seen that resolve this issue with others, doesn’t seem to apply to my circumstances.

I did not have this issue when I was using Widows 7 64bit.
I have set the Active Solution Platform to both Any PC and x86 when I have a solution open, but I still get the same message with either setting.
I have the latest Oracle 32 bit Client since 64 bit Oracle client won’t work with 32 bit Visual Studio.
At this point, I am only trying to connect to Oracle from Visual Studio and not run a web application. So IIS settings have nothing to do with this issue.

My environment is

Windows 8 64bit
Visual Studio 2012 and 2008
Oracle 32 bit Client. But also tried 64 Bit Client.
Oracle 11.2.0.3 database.
Active Solution Platform was tried with both Any PC and x86.

Any suggestions would be appreciated.

I discovered the solution myself. I found that apparently when I had installed Oracle Client, it had installed on my external drive E the first time and became Oracle Home #1 in the Windows registry. I don't believe it was "looking" at the Oracle client I had installed on the C drive even though it was first in the path. I uninstalled the Oracle clients on both the C and E drive and reinstalled the client on the C drive. I am now able to connect to Oracle with Visual Studio.


How to find all positions of a string within another string How can I find all the positions with patindex in a table or variable?

declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek  '
  + 'dokhtare khoshkel be disco raft va ali baraye'
  + ' 1 saat anja bud va sepas... ali...'
select patindex('%ali%',@name) as pos

This returns 1 but I want all results, e.g.:

pos
===
  1
 74
113

I think this will be slightly more efficient than the looping method you've chosen (some evidence here), and definitely more efficient than the recursive CTE:

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN
    (
      SELECT pos = Number - LEN(@term)
      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number,
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

Sample usage:

DECLARE @name NVARCHAR(MAX);

SET @name = N'ali reza dar yek shabe barani ba yek'
    + '  dokhtare khoshkel be disco raft va ali baraye '
    + '1 saat anja bud va sepas... ali...';

SELECT pos FROM dbo.FindPatternLocation(@name, 'ali');

Results:

pos
---
  1
 74
113

If your strings will be longer than 2K then use sys.all_columns instead of sys.all_objects. If longer than 8K then add a cross join.


Select the same field based on different foreign keys I have two tables:

Containing a list of products with ids and names
A table linking several products with parent-child relationship based on ids using foreign keys.

I want to create a view that will display the product name for both parent and child but both derives from the same table.

Each product can be a parent for multiple children
Each product can be a child for multiple parents
The parenting relationship goes only one level without the need for recursion (no grandchildren)

Here is a sample of the construction of tables:

create table plugins_t
(
  rid          int auto_increment,
  product_name varchar(156) not null,
  constraint plugins_t_rid_uindex
    unique (rid)
);

alter table plugins_t
  add primary key (rid);

create table parent_2_childs_t
(
  rid              int auto_increment,
  parent_plugin_id int not null,
  child_plugin_id  int not null,
  constraint parent_2_childs_t_rid_uindex
    unique (rid),
  constraint parent_2_childs_t_plugins_t_rid_fk
    foreign key (parent_plugin_id) references plugins_t (rid),
  constraint parent_2_childs_t_plugins_t_rid_fk_2
    foreign key (child_plugin_id) references plugins_t (rid)
);

alter table parent_2_childs_t
  add primary key (rid);

INSERT INTO plugins_t
  (product_name)
values
  ('product1'),
  ('product2'),
  ('product3'),
  ('product4');

INSERT INTO parent_2_childs_t
  (parent_plugin_id, child_plugin_id)
VALUES
  (1, 3),
  (2, 3),
  (2, 4);

INSERT INTO plugins_t
  (product_name)
values
  ('product1'),
  ('product2'),
  ('product3'),
  ('product4');

INSERT INTO parent_2_childs_t
  (parent_plugin_id, child_plugin_id)
VALUES
  (1, 3),
  (2, 3),
  (2, 4);

I used a two select using a subquery but I get duplicate values.

select
       pt.product_name as parent_product_name,
       c.product_name as child_product_name
from plugins_t as pt
right join parent_2_childs_t p2ct on pt.rid = p2ct.parent_plugin_id

  join (
    select pt2.product_name from plugins_t as pt2
    join parent_2_childs_t p2ct2 on pt2.rid = p2ct2.child_plugin_id
    ) as c

This is the desired output:

| Parent name  | child name  |
------------------------------
| product1     | product3    |
| product2     | product3    |
| product2     | product4    |

The query worked

  select parent.product_name as parent_product_name, child.product_name as child_product_name
  from plugins_t as parent inner join parent_2_childs_t p2ct on parent.rid = p2ct.parent_plugin_id
  inner join plugins_t as child on child.rid = p2ct.child_plugin_id


Lock escalation problem on a trigger I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day.

I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger.

The trigger consists of 10 updates to another table for slightly different criteria.  The deadlock occurs in the trigger.

When a person makes an application and the job is running that's when the deadlock occurs.  The application inserts into the same table as the scheduled job.

Looking at the trace it seems to happen when process 1 obtains a key lock, process 2 obtains a page lock, then process 1 escalates the key lock to a page lock and process 2 tries to obtain a key lock.

I've added missing indexes which seems to have helped but its still happening.  I'm not a DBA so any advice on an approach to solving this would be appreciated.

I've added a link to the deadlock xml - this is from a test I did to duplicate the problem.

deadlock xml

A create script for tableA including all indexes and the execution plans for the two UPDATE statements involved are required for a definitive solution.  That being said, we can still combine the deadlock graph provided with knowledge of how SQL Server performs updates and have a very good chance of resolving this issue.

This deadlock involves two spids performing UPDATES on tableA, 58 & 59.

Spid 58's Query:

Update b set  syd_id=d.syd_id
        from tableA b
        inner join tableC d with (nolock) on b.syd_pers_id=d.syd_pers_id
        --and b.game='es_lotto'
        and b.game=d.game  and isnull(b.syd_id,0)=0

Spid 59's Query:

Update b set Draw_Date=d.draw_date
        from tableA b
        inner join tableB d with (nolock) on b.draw_no=d.draw_no
        and left(b.game,2)='UK' and b.Draw_Date is null

In this deadlock, there are two resources involved:

a "Key Lock" on the index IX_tableA_Draw_Date.

Owned by spid 58, mode = 'X' (Exclusive)
Requested by spid 59, mode = 'U' (Update)

a "Page" lock for a different, unspecified index on tableA

Owned by spid 59, mode = 'IX' (Intent Exclusive)
Requsted by spid 58, mode = 'U' (Update)

There are also 3 different lock ownership modes in use (U, X, IX):

"U" or Update Lock.  Conceptually, the sql plan for an UPDATE statement has two parts: A "select" part in which the data to modify is identified, and a "modification" part where the modification actually happens.  Update locks are taken during the "select" part to protect the rows from modification until we get around to actually modifying them (upon which time an X lock is acquired).  Update locks are compatible with "S" (Shared) locks, so readers don't get blocked, however, they are incompatible with X, IX and other U locks.
"X" or Exclusive Lock. Exclusive locks are used to protect data being modified during inserts, updates and deletes.  They are incompatible with all other lock types, and ensure that only one writer can access a resource at the same time.
"IX" or Intent Exclusive Lock.  Intent Exclusive locks are taken on higher level resources before lower level resources get X locks.  For example, before an X lock on a row is taken, an IX lock is taken on both the associated page and the entire table. IX locks indicate that X locks exist at a lower level.  They can also be changed into X locks as part of lock escalation. IX locks are incompatible with X locks, so if two transactions have IX locks on a table, then neither transaction can escalate their page or row locks to the table level.  Intent locks (including IX) are an optimization that allow SQL to check one higher level lock instead of checking thousands of lower level locks.

Now, let's examine spid 58's UPDATE query in detail.  We know from the deadlock graph that spid 58 has an exclusive lock on a row in IX_tableA_Draw_Date.  Therefore, we know that syd_id is either in the index key, in the clustered index key, or is an included column (I'm going to ignore the possibility that spid 58 already owned the lock from a previous statement in the trigger).

Spid 58 is also attempting to acquire an update lock on a page in a different index.  This tells two things:

We know that the select portion of the update is still reading and locking data after the modification portion has started.  This implies that there are no blocking operators  affecting the rows from tableA in the plan. For example, if rows were read from tableA, sorted, and used in a MERGE join; then all the rows would be read (and U-locked) before any exclusive locks are taken.  That isn't the case.
We're attempting to take an Update lock on a PAGE, not a ROW, so it's likely that we're doing an index scan.

We can also assume that a relatively small number of rows are expected to be updated (Due to the multi-column join combined with updating nulls or 0's in an insert trigger, this doesn't feel like a query that updates a large percentage of rows).  Taking all this into consideration, it is likely that the UPDATE is reading rows from tableA using a scan on our mystery index (don't know if it's clustered or non-clustered ) and performing a LOOP JOIN against tableC to get the  tableC.syd_id.

Performing a similiar analysis for spid 59, it is likely that we're seeking into IX_tableA_Draw_Date where "Draw_Date is null", reading (and taking update locks on ROWS), LOOP JOIN against tableB, then taking a X ROW lock and updating our "mystery" index (the IX lock on the page implies that there are X locks on rows).

Where are we? And what do we do next?
We know we've got two update statements.  Both are reading and writing to the same pair of indexes (with the reading and writing swapped).  Both are waiting for an update lock after they've already updated some rows.  Spid 58 is reading pages, 59 is reading rows.  Both are writing and locking rows.  Lock escalation isn't a factor, as row locks -> table locks ( not page locks).

If Spid 58 was taking row locks instead of page locks on the mystery index, then you would only have deadlocks if both queries updated the same row.  You could use a ROWLOCK query hint to make that happen, however you run the risk of increased lock escalation to table locks, which could be worse.

Another approach is to separate the update into two parts.

select id, --Not sure what the PK is...
    syd_id
into #tmp
from tableA inner join tableB (nolock) on b.syd_pers_id=d.syd_pers_id
    --and b.game='es_lotto'
    and b.game=d.game  and isnull(b.syd_id,0)=0

UPDATE tableA SET syd_id = t.syd_id from tableA d inner join #tmp t on t.id = d.id

This would change the order of locks taken as the non-clustered indexes will only be locked for writes, not reads.

Recommendation

Deadlocks are a natural by-product of RDBMS's. Even if you do everything "right" you can't always eliminate them.  At 3-4 Deadlocks per day, the correct approach is probably to just wrap the statement in some error handling so that it is re-tried if a deadlock occurs.

http://msdn.microsoft.com/en-us/library/aa175791(v=sql.80).aspx has an example for SQL 2005.  However, since this is in a trigger, you may need to add the error handling outside of the INSERT statement that fires the trigger.


Query to map a particular database to a syslogin I need to map a database to a syslogin.

Following is the query I have used to create login and make it a db_owner but after the execution, I can see a tick as db_owner for that database in UserMapping tab ,but there's no tick on the database itself that means it's not mapped with the database

CREATE LOGIN [TRAINW7X64CA04\Test8] from Windows with default_database = Eclipse;
Use Eclipse
EXEC sp_addrolemember N'db_owner', N'TRAINW7X64CA04\Test8'

Can some help me with the mapping of the database as well?

You're missing a CREATE USER to add them in as a database principal.

CREATE LOGIN [TRAINW7X64CA04\Test8] from Windows with default_database = Eclipse;
Use Eclipse;
CREATE USER [TRAINW7X64CA04\Test8];
EXEC sp_addrolemember N'db_owner', N'TRAINW7X64CA04\Test8';


How does SQL Server choose an index key for a foreign key reference? I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade.

Many of these tables also have unique, non-clustered indexes that are duplicates of the primary key.

I am attempting to clean this up.

But what I have found is after I recreate the primary keys as clustered indexes, and then try to rebuild the foreign key, the foreign key is referencing the old, duplicate index (which was unique).

I know this because it won't let me drop the duplicate indexes.

I would think SQL Server would always choose a primary key if one existed. Does SQL Server have a method of choosing between a unique index and a primary key?

To duplicate the problem (on SQL Server 2008 R2):

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Child') DROP TABLE Child
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Parent') DROP TABLE Parent
GO

-- Create the parent table
CREATE TABLE Parent (ParentID INT NOT NULL IDENTITY(1,1))

-- Make the parent table a heap
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY NONCLUSTERED (ParentID)

-- Create the duplicate index on the parent table
CREATE UNIQUE NONCLUSTERED INDEX IX_Parent ON Parent (ParentID)

-- Create the child table
CREATE TABLE Child  (ChildID  INT NOT NULL IDENTITY(1,1), ParentID INT NOT NULL )

-- Give the child table a normal PKey
ALTER TABLE Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ChildID)

-- Create a foreign key relationship with the Parent table on ParentID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID)
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to clean this up
-- Drop the foreign key constraint on the Child table
ALTER TABLE Child DROP CONSTRAINT FK_Child

-- Drop the primary key constraint on the Parent table
ALTER TABLE Parent DROP CONSTRAINT PK_Parent

-- Recreate the primary key on Parent as a clustered index
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentID)

-- Recreate the foreign key in Child pointing to parent ID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID)
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to drop the duplicate index on Parent
DROP INDEX IX_Parent ON Parent

Error msg:

  Msg 3723, Level 16, State 6, Line 36 An explicit DROP INDEX is not
  allowed on index 'Parent.IX_Parent'.  It is being used for FOREIGN KEY
  constraint enforcement.

The (lack of) documentation suggests that this behaviour is an implementation detail, and is therefore undefined and subject to change at any time.

This is in stark contrast to CREATE FULLTEXT INDEX, where you have to specify the name of an index to attach to -- AFAIK, there is no undocumented FOREIGN KEY syntax to do the equivalent (though theoretically, there could be in the future).

As mentioned, it does make sense that SQL Server chooses the smallest physical index with which to associate the foreign key. If you change the script to create the unique constraint as CLUSTERED, the script "works" on 2008 R2. But that behaviour is still undefined and should not be relied upon.

As with most legacy applications, you'll just have to get down to the nitty-gritty and clean things up.


Consolidating indexes I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a column of the big table.

Currently I have one index for each 'select distinct' and 16 indexes in total. Is it better to merge some of those indexes? My thinking is that the cube needs to process all rows anyway, and the indexes speed up the process because it is much smaller than the entire table, and if I put two or three columns in one index the index size will not grow so much. Maybe the good candidates are columns that have low specificity.

Am I going in the right direction? Can the query make use of the second column (or third, fourth, ...) of the index?

I'm using SQL Server 2005 Standard Edition.

If the "cube" needs to process all rows, indexes won't speed up the process as the querying engine would just do a table scan.

I think the best advice I could give is to familiarize yourself with query plan output so that you can see how the database is satisfying the query requests. This will allow you to see what access methods are used to retrieve data. It would also allow you to answer you're own question: if you create the proposed index, does sql server use it to fulfill your query?

I think the answer to your question is going to be "No". If your "cube" is being materialized by executing "SELECT DISTINCT columnB FROM table", and you currently have an indexes on "columnA" and "columnB". "Consolidating" those separate indexes into a single index on "(columnA, columnB)" is unlikely to help your "cube" queries on "columnB". Your mileage may vary, not having access to the schema nor query source makes these kinds of questions difficult to answer definitively.

Having said that, I think the correct response is to point you towards the SQL Server data warehousing functionality instead of rolling your own solution.


does character ' and ` equal in mysql command? mysql> select 'user_url' from wp_users;
mysql> select `user_url` from wp_users;

It can get the same result ,does  character ' and ` equal in mysql command?

' or " for strings

` (backtic) for column/table/database names.

The first should give you user_url; the second should look up the value in the user_url column.


out of all columns in a table, how can I find the one datatype that is cheapest and I could convert all other columns datatypes into? I sometimes have to unpivot tables, that means, converting all columns into rows.

Generally that becomes a table with 2 columns (just to simplify things) - the first is the column name and the second is the column value.
.
In the example below I found out manually by looking at the column types in the original table, that the best column to store the value is nvarchar(256).

Questions:

How could I find this "the best value to use as the values column type is nvarchar(256)" out dynamically?

I know I could use sql_variant for the values column type? In this case can I ignore the collate database_default?

That would be an expensive overkill - and I want to avoid that:

  sql_variant can have a maximum length of 8016 bytes. This includes
  both the base type information and the base type value. The maximum
  length of the actual base type value is 8,000 bytes.

--=============================================================================
-- Unpivot the table - SUCCESSFUL
--=============================================================================

IF OBJECT_ID('TEMPDB.DBO.#UNPIVOT') IS NOT NULL
   DROP TABLE #UNPIVOT

CREATE TABLE #UNPIVOT(
    [ColumnNames] [nvarchar](128) NULL,
    [AllCol] [nvarchar](256) NULL
)

INSERT INTO #UNPIVOT([ColumnNames],[AllCol] )
SELECT
     ColumnNames
    ,AllCol
    --INTO TABLEBACKUPS.DBO.UNPIVOT_
FROM (
        select
                [host_platform]                   = CAST (host_platform collate database_default as [nvarchar](256)),
                [host_distribution]               = CAST (host_distribution collate database_default as [nvarchar](256)),
                [host_release]                    = CAST (host_release collate database_default as [nvarchar](256)),
                [host_service_pack_level]         = CAST ([host_service_pack_level] collate database_default as [nvarchar](256)),
                [host_sku]                        = CAST ([host_sku]  as [nvarchar](256)),
                [os_language_version]             = CAST ([os_language_version]  as [nvarchar](256)),
                [host_architecture]               = CAST ([host_architecture] collate database_default as [nvarchar](256))
       from
       sys.dm_os_host_info
      ) Radhe
UNPIVOT
(
AllCol
FOR ColumnNames IN (
                      host_platform
                     ,host_distribution
                     ,host_release
                     ,host_service_pack_level
                     ,host_sku
                     ,os_language_version
                    ,host_architecture
                  )
) unpvt

SELECT [ColumnNames]
      ,[AllCol]
FROM #UNPIVOT

Perhaps something like this will suit you:

SELECT
    Unpivoted.*
INTO #Unpivoted
FROM sys.dm_os_host_info AS HI
CROSS APPLY
(
    VALUES
        (N'host_platform', CONCAT(HI.host_platform, NULL)),
        (N'host_distribution', CONCAT(HI.host_distribution, NULL)),
        (N'host_release', CONCAT(HI.host_release, NULL)),
        (N'host_service_pack_level', CONCAT(HI.host_service_pack_level, NULL)),
        (N'host_sku', CONCAT(HI.host_sku, NULL)),
        (N'os_language_version', CONCAT(HI.os_language_version, NULL)),
        (N'host_architecture', CONCAT(HI.host_architecture, NULL))
) AS Unpivoted
    (ColumnName, TheValue);

SQL Server will automatically work out the minimal length and string type of the CONCAT results.

If you need to preserve the underlying type information, including collation, and can live with the more limited set of data types that can be held in sql_variant, use that:

SELECT
    Unpivoted.*
INTO #Unpivoted
FROM
(
    SELECT
        host_platform = CONVERT(sql_variant, HI.host_platform),
        host_distribution = CONVERT(sql_variant, HI.host_distribution),
        host_release = CONVERT(sql_variant, HI.host_release),
        host_service_pack_level = CONVERT(sql_variant, HI.host_service_pack_level),
        host_sku = CONVERT(sql_variant, HI.host_sku),
        os_language_version = CONVERT(sql_variant, HI.os_language_version),
        host_architecture = CONVERT(sql_variant, HI.host_architecture)
    FROM sys.dm_os_host_info AS HI
) AS HI
UNPIVOT
(
    TheValue FOR ColumnName IN
    (
        host_platform,
        host_distribution,
        host_release,
        host_service_pack_level,
        host_sku,
        os_language_version,
        host_architecture
    )
) AS Unpivoted;


what would be the primary disadvantages of splitting my tables into table spaces and/or separate schemata I would like to organize the MySQL tables of a complex application into some groups, to avoid looking at a one big bin of tables. One big bin is harder to administrate when e.g. I would like to reset certain groups of related tables, or if I'd later wish to move a group of related tables to a different MySQL server altogether, and it is also more error prone when developing new DDL code.

I can apply a sense of grouping with my own table name prefixing "convention", but thought about using table spaces or even separate schemata. What would be the primary disadvantages of using separate table spaces or schemata for applying a sense of grouping upon my tables?

DISADVANTAGE #1
InnoDB only has one Buffer Pool. The benefits of partitioning or compartmentalizing database tables for different clients can still be bottlenecked by database buffers (log buffer, InnoDB Buffer Pool)
DISADVANTAGE #2
Scaling becomes dependent on manual operation. This applies to scaling up disks, hardware (disk controllers), and memory.
DISADVANTAGE #3
More tables means more open file handles to cache distinct tables. Requires more flush to disk.
DISADVANTAGE #4
Introducing new groups of related tables regurgitates the first three(3) disadvantages and  exacerbates the overall design.
See my other posts on such setups and other drawbacks

Nov 15, 2011 : One Big Database vs. Several Smaller Ones
Mar 11, 2011 : What problems will I get creating a database per customer?

SUGGESTIONS
You might have to compensate these deficiencies with lots of RAM with the InnoDB Buffer Pool and the Log Buffer getting the lion's share.
You could create a hybrid setup where InnoDB Log Files are on one disk and InnoDB System Tablespace is on another, and all the data on a third disk. This concept is well expressed in YOSHINORI MATSUNOBU'S BLOG (FaceBook DB Engineer).


Why does it use temporary? (MySQL) MySQL 5.

I have the following table (filled with many rows):

CREATE TABLE `SectorGraphs2` (
  `Kind` tinyint(3) UNSIGNED NOT NULL COMMENT '1 - продюсер, 2 - жанр, 3 - регион',
  `Criterion` tinyint(3) UNSIGNED NOT NULL,
  `Period` tinyint(3) UNSIGNED NOT NULL,
  `PeriodStart` date NOT NULL,
  `SectorID` int(10) UNSIGNED NOT NULL,
  `Value` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `SectorGraphs2`
  ADD UNIQUE KEY `Producer2` (`Kind`,`Criterion`,`Period`,`PeriodStart`,`SectorID`) USING BTREE,
  ADD KEY `SectorID` (`SectorID`);

then I run:

EXPLAIN
    SELECT SectorID, SUM(Value)
    FROM SectorGraphs2
    WHERE Kind = 1 AND Criterion = 7
      AND Period = 1
      AND PeriodStart >= ? AND PeriodStart < ? + INTERVAL 1 WEEK
    GROUP BY SectorID

and it produces:

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | SectorGraphs2 | range | Producer2,SectorID | Producer2 | 6 | NULL | 1 | Using index condition; Using temporary; Using filesort |
+---+---+---+---+---+---+---+---+---+---+

I wonder why it uses a temporary while all keys Kind, Criterion, Period and PeriodStart are in the key Producer2 and it can be calculate by just enumerating the last field PeriodStart of the key. (Accordingly to what I learned, MySQL can enumerate the last field of a key without consulting data, if all previous fields have a fixed value.) Please help if possible to rewrite this without using a temporary.

Note that the above code is intended to store and calculate some statistics.

I need to ensure we do not run heavy queries which may overload our MySQL server. Please help to make proper use of indexes and avoid big temporary tables.

Step 1: Filter (WHERE) to get just the desired rows.  Producer2 is excellent for that.

Step 2: Now do the GROUP BY.  Well, the rows from Step 1 are not ordered by SectorID (because of the "range" on PeriodStart left things shuffled.  This could be done in one of two ways:  Via some hash in memory, or by sorting (probably in RAM) and doing a simple pass through the data.  It is hard to say which is faster.

You should move away from MyISAM.  This particular query will probably run faster with InnoDB because of the way indexes are handled between the two engines.

Your query is an example of one thing I talk about in my index cookbook -- "an index cannot handle both a range (PeriodStart...) and a GROUP BY.

I am suspicious of "Using index condition".  That is a synonym for "ICP" or "Index Condition Pushdown", wherein the high level handler lets the Engine do some of the index work (more efficiently).  However, I thought that MyISAM was not updated to do any ICP work.  If I am correct, then this is another reason for moving to InnoDB; it does benefit from ICP.

If this is a big table, we can talk about another way to speed up the query (perhaps 10x) -- Summary Tables .


Oracle: deleting data from tables leads to data file fragmentation? When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments.  Is there a way to know about the disk state and any query to defragment or shrink the space?

Can you explain exactly what problem you are concerned with?  People have come up with lots of different definitions of "fragmentation" some of which are impossible, some of which are possible but don't create any problems, and a few of which might be worth thinking about.

If you delete at random 1 out of every 2 rows in a table, that will generally mean that you'll have lots of half-empty blocks in the table.  That space will be reused by subsequent inserts into the table, though, so it is generally not create any problems unless you are stating that you are permanently reducing the size of the table (i.e. it will never again grow past 5 GB in size) or you have queries that rely on doing full table scans of the table that you want to tune.  Depending on the Oracle version, you can probably do a SHRINK SPACE on the table to reduce the size of the segment in that case.


Steps to setup mongo replica set I am having problems setting up a mongo replica set.  I have 2 servers and want one to be primary and one to be secondary.

On primary machine in mongod.conf:

replSet=rs1

Then in the console I type:

rs.initiate()

Which works great.  rs.config() shows:

{
  _id: "rs1",
  version: 1,
  members: [
    {
      _id: 0,
      host: "myhost1:27017"
    }
  ]
}

This is where things get messy.  What am I supposed to do on the second machine?  I have modified the mongod.conf to point to the same replica set name 'rs1'.  I start the sever and all is well.

Am I supposed to do an rs.initiate() on the second machine?  If so both instances now think that they are the 'PRIMARY'.

If I try to do an rs.add(...) on the first server to add the second server:

rs.add("myhost2:27017")

I get an error:

{
    "errmsg" : "need most members up to reconfigure, not ok : myhost2:27017",
    "code" : 13144,
    "ok" : 0
}

if I do an rs.initiate() on the second machine and then an rs.config() I get:

{
  _id: "rs1",
  version: 1,
  members: [
    {
      _id: 0,
      host: "myhost2:27017"
    }
  ]
}

Any ideas?

EDIT:

Here is my /etc/hosts file in case I messed something up there:

host1:

127.0.0.1   myhost1
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost4.localdomain6

host2:

127.0.0.1   myhost2
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost4.localdomain6

tl;dr

Your hosts were unable to resolve the hostnames you gave to actual IPs, since those were missing in the /etc/hosts file.

How to solve

The hosts files on the all the machines must resolve the IPs of the respective machines to their respective hostnames.

For example

192.168.0.1 myhost1
192.168.0.2 myhost2
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

Make sure all hostnames are resolved correctly by pinging each other host from each host, eg.

myhost1 $ ping myhost2

Please read the docs thoroughly and make sure you understood them.
Proceed to step 2 until you really got it.
You only have to call rs.initiate() once and only on one machine, then use rs.add() on the same machine to add replica set members.

With your setup, you need an additional node or an arbiter

Personal note

Please do not take this personal, but you lack basic system administration skills, and that's put politely.

For your own sake: Do not run MongoDB for production until you acquired basic system and MongoDB administration skills!

I'd look for RHCSA first (works for CentOS as well) and  M102, M202 and maybe even MongoDB DBA at MongoU later (without basic sysadmin skills, the latter won't help you much).


Group By With Rollup results table has totals at the top I have the following query that returns a set of counts by weeks beginning with Monday, with a row for the aggregate totals.

select distinct
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) as B2B_Week,
    count (distinct bm.MISSION_ID) as B2B_Mission,
    count (distinct dl.DRIVER) as Distinct_B2B_Drivers
from
    DD.B2B_MISSIONS bm
    inner join dd.IN_GATE ig on bm.MISSION_ID = ig.IN_MISSION_ID
    inner join dd.XNS_DRIVER_LOGIN dl on ig.IN_DRIVER_ID = dl.driver
group by
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM)
  with rollup
order by
    DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM)
;

My Results Table looks like this:

B2B_Week    B2B_Mission Distinct_B2B_Drivers
            1647        357
44          717         264
45          930         301

I want it to look like this:

B2B_Week    B2B_Mission Distinct_B2B_Drivers
44          717         264
45          930         301
TOTAL       1647        357

ISSUES

The totals row appears at the top rather than the bottom. This is
probably related to the second problem.
I have been unable to use a CASE statement to return 'TOTAL' with
the date column is NULL. The primary problem is that the DATE is an
INT and 'TOTAL' is not. I have tried various methods of using CAST
on either the entire CASE statement or just pieces of it. I've tried putting the Case statement in the SELECT or in the GROUP BY statement.

Here is an example of what happens when I try to get TOTAL to appear.

case when DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) is null
then 'TOTAL' else DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) end

  "Conversion failed when converting the varchar value 'TOTAL' to data type int."

What am I doing wrong?

You should use the GROUPING function

  GROUPING returns 1 for aggregated or 0 for not aggregated in the
  result set.

The advantage of this is that it will deal correctly with the case that B2B_Week is itself NULL and not incorrectly treat it as a total row.

WITH T
     AS (SELECT DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) AS B2B_Week,
                bm.MISSION_ID,
                dl.DRIVER
         FROM   DD.B2B_MISSIONS bm
                INNER JOIN dd.IN_GATE ig
                  ON bm.MISSION_ID = ig.IN_MISSION_ID
                INNER JOIN dd.XNS_DRIVER_LOGIN dl
                  ON ig.IN_DRIVER_ID = dl.driver)
SELECT B2B_Week = CASE WHEN GROUPING(B2B_Week) = 0 THEN CAST(B2B_Week AS VARCHAR(5)) ELSE 'Total' END,
       B2B_Mission = COUNT (DISTINCT MISSION_ID),
       Distinct_B2B_Drivers = COUNT (DISTINCT DRIVER)
FROM   T
GROUP  BY B2B_Week WITH ROLLUP
ORDER  BY GROUPING(B2B_Week),
          T.B2B_Week


Restructuring table - start with PK or not? I am restructuring a bunch of tables in my database (in this case using a smaller data type for some columns - in most cases cutting those by ~50%, and they tend to be large columns).

I've gone through the process of creating a new table with the wanted structure, copying all the data over, dropping/renaming old table and then applying all constraints, indexes, reseeding etc...

My question is - should I create the new table with the primary key (clustered index) or should I be createing it without?

My understanding is that without means faster copying at the cost of restructuring later on, but with means no restructuring later on.

Considering that the data source should already be sorted by the same PK (the PK doesn't usually change type on these tables), what's the best option?

Data sizes vary between the different tables - some have a few million rows, most have far less.

The fastest mode in your case is to use

INSERT INTO..WITH(TABLOCK)
SELECT..
ORDER BY PK_KEYS

With the new table having the same PK defined on it.

This way you don't sort data even if insert in clustered table, and it will be minimally logged in simple and bulk logged recovery models.

Note this will work only if your PK is not identity where you try to preserve the old values by using set identity_insert on, when the rows will be sorted anyway.

In any case you can create your new table and ask server for estimated query plan when trying to insert in it. If you see there is no sort this will be the fastest way.

When your PK is on identity you'll get the sort in the plan and you may use select..cast(), cast(),...into without creating your new table and you still will have minimal logging, but then you create your clustered PK and have sort. Note in this case or you want to use sort_in_tempdb option otherwise your db may increase it size and if autogrouth is not set properly you'll be wait for it autogrow.


Use Dark Theme on SSMS Results Grid, Object Explorer and other windows There are several ways to use a Dark Theme on SSMS main coding window, either importing a vsettings file, applying Dark Theme that is disabled on a config file or doing it manually. But all those options do not affect Grid Results, Object Explorer and other windows. Those 2 are the main ones I use other than the coding one.

I tried the usual Tools>Options>Environment>Fonts and Colors then selected on the combo box Show settings for: the option Grid Results and using White for Item Foreground and Black for Item Background. Saved, restarted SSMS but just the text is white, background is still white.

Any idea on what is happening or how to do it? I can't find how to do it for the Object Explorer.

These options are ignored as of SQL Server 2005 SP2 and still not fixed.

See the relevant connect item: SP2: Results Grid Background color property ignored by Management Studio. archive link

The status is closed as fixed, but only because it's been converted into an internal issue

  Thanks for this feedback. I have converted this request to either a
  defect (something is broken) or a Design Change Request (something new
  to add to the product) and so I'll close this ticket out. We will
  consider your information for SQL Server 2008 or a subsequent release.
  Thanks!
  - Buck Woody, SQL Server Program Manager

There is a second connect item: SSMS 2012 Grid Results background color does not work (unfortunately not archived) which is closed as wontfix.

  Hello Chris. Thanks for the feedback. We triaged this issue and at
  this time do not plan to address this in a future version of SQL
  Server.
  -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)

There is however one user commenting

  I found the answer! This is actually controlled by the tooltip color
  you set up in your windows environment. Man, That was a PAIN to find!

But unless that works I fear you are out of luck


Return list of values in conditional where clause I'm trying to filter a table based on a parameter that determines which list of values are allowed for a certain column. For @Parameter = 1 we want MyColumn to be either FirstValue or SecondValue; for @Parameter = 2 we want MyColumn to be either ThirdValue or FourthValue; otherwise we want all values of MyColumn.

This syntax clearly does not work:

declare @Parameter int = 1; -- can also be 2 or 3

select *
from dbo.MyTable
where MyColumn in
(
    case @Parameter
        when 1 then (N'FirstValue', N'SecondValue')
        when 2 then (N'ThirdValue', N'FourthValue')
        else (N'FirstValue', N'SecondValue', N'ThirdValue', N'FourthValue')
    end
);

What would be the best way to achieve this?

Edit: This works but is really clunky, so I would like to find a more elegant solution:

select *
from dbo.MyTable
    join
    (
        values
        (1, N'FirstValue'),
        (1, N'SecondValue'),
        (2, N'ThirdValue'),
        (2, N'FourthValue'),
        (3, N'FirstValue'),
        (3, N'SecondValue'),
        (3, N'ThirdValue'),
        (3, N'FourthValue')
    ) as temp(Option, Value)
    on MyTable.MyColumn = temp.Value
where temp.Option = @Parameter;

Maybe something like that could work:

declare @values table(id int, value nvarchar(100))
insert into @values
values(1, N'FirstValue'), (1, N'SecondValue'), (2, N'ThirdValue'), (2, N'FourthValue')

select *
from dbo.MyTable t
join @values v on t.MyColumn = v.value
                and (v.id = @Parameter or @Parameter not in (1,2))


Identity column value falling behind randomly We are having a strange intermittent issue where the identity column value falls behind the MAX(ID) for the table.

Each customer gets their own table that stores statistics to the tune of [dbo].[Stat_customer1], [dbo].[Stat_customer2], etc.

Randomly, one of these tables' identity values will fall behind, stopping any inserts from happening and we have no idea why.

Here's one example:

DBCC CHECKIDENT ('[dbo].[Stat_customer1]', NORESEED);
GO

Checking identity information: current identity value '43487', current column value '52012'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SELECT MAX(ID) FROM [dbo].[Stat_customer1]

52012

To repair this we then do:

DBCC CHECKIDENT ('[dbo].[Stat_customer1]', RESEED, 52012);
GO

Checking identity information: current identity value '52228', current column value '52228'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We have about a thousand of these tables on average per SQL server.  This will only occur on one random table, not all of them.  It does not happen every day.  Sometimes a week goes by with no issue.

Data is imported to these tables every five minutes by an application that performs a bulk insert.  Every night, a SQL job is executed that rolls up statistics by date.  E.g. anything over a month old is summed up by month, anything over a year old is summed up by year.

During this time, the application pauses importing data so no new data is imported during the roll-up.  Data is read out of table into a temp table and is aggregated as it is being read out.  We then delete the old data from the table and insert the new aggregated data from the temp table.

We are not sure if it is the bulk insert or the statistics roll-up that is causing the issue.

This issue started popping up after we upgraded from SQL 2008 R2 to SQL 2012 and virtualized the server.

Does anyone have any clues as to why this is happening?

Randomly, one of these tables' identity values will fall behind, stopping any inserts from happening and we have no idea why.

Inserts are probably stopping because of an attempt to reuse an already existing unique value in the PRIMARY KEY, thus triggering the error like:

Msg 2627, Level 14, State 1, Line 27
Violation of PRIMARY KEY constraint 'PK__ID__1234'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (8).
The statement has been terminated.

Note that if your IDENTITY column does not have a UNIQUE index or constraint, it is possible to reseed repeatedly and have many identical ID values. You do not want to do that, of course.

I have not personally found an error that, in itself, would reseed the IDENTITY value.  Of course, it is possible to reset the SEED to a range where there will soon be a conflict by running a reseed that is lower than the current seed:

DBCC CHECKIDENT( MyTable,RESEED, 7) WITH NO_INFOMSGS

It could be that code somewhere in one of the processes actually does a RESEED on the table under some unusual circumstances.

(For example, this could be from a merge of two data sets, where the code reads the high value from one data set and after the import RESEEDs to the lower of the two high values that were merged.)

You should also read Martin Smith's post at: https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database


MySQL 5.5 accepts password but then closes connection Apologies in advance for lack of information. I have installed MySQL 5.5 x64 on a Windows XP Professional x64 development machine. Today, upon trying to connect with any tool(MySQL workbench, PHP script, PhpMyAdmin, or command-line utility), the password is accepted but the connection is immediately closed:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -u root -p
Enter password: ***************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.5-m8 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Bye

C:\Program Files\MySQL\MySQL Server 5.6\bin>

I have attempted removing all MySQL server-related files by deleting the installation directory and C:\Documents and Settings\All Users\MySQL\MySQL server 5.6, as well as D:\InnoDB Datafiles, where my InnoDB Tablespace lies. There is no important data, but I am unable to get MySQL server to operate properly. If any more info is needed, please do tell.

I have installed MySQL with the MSI installer and the instance config wizard. I have also tried the 5.6 development version, to no avail.

A) Try with

mysql -h[your IP] -u[username] -P[]port -p
mysql -h127.0.0.1 -u[username] -p
mysql -hlocalhost -u[username] -p

Which one works and which does not?

B) Set Log_warnings=2 in my.ini and restart MySQL server and see error log entry when connection gets closed abruptly.


How to get count of an object, through 3 different tables in postgres with ID's stored in each table I'm currently using Postgres 9.6.16.
I am currently using 3 different tables to store a hypothetical users details.
The first table, called contact, this contains:
ID, Preferred_Contact_Method

The second table, called orders, This contains:
ID, UserID, Contact_ID (the id of a row, in the contact table that relates to this order)

The Third Table, Called order_details
ID, Orders_ID (the id in the orders table that relates to this order details)

The tables contain other data as well, but for minimal reproduction, these are the columns that are relevant to this question.
I am trying to return some data so that i can generate a graph, in this hypothetical store, There's only three ways we can contact a user: Email, SMS, or Physical Mail.
The graph is supposed to be 3 numbers, how many mails, emails, and SMS we've sent to the user; since in this hypothetical store whenever you purchase something you get notified of the successful shipment, these methods are 1:1 to the order_details, so if there's 10 order_detail rows for the same user, then we sent 10 tracking numbers, and since there can be multiple order_details (each item has a different row in order_details) in an order, we can get the count by counting the total rows of order details belonging to a single user/contact, then attributing to what kind of contact method that user preferred at the time of making that order.
To represent this better: If a new user makes a new order, and orders 1 apple, 1 banana, and 1 orange. For the apple, the user set preferred tracking number delivery as SMS, for the banana, they set it to EMAIL, for the orange, they thought it would be funny to set the tracking number delivery via MAIL. Now, i want to generate a graph to this users preferred delivery method. So i'd like to query all those rows and obtain:
SMS, 1
EMAIL, 1
MAIL, 1

Here's a SQL Fiddle link with the schema and test data: http://sqlfiddle.com/#!17/eb8c0
the response with the above dataset should look like this:
method | count
SMS,     4
EMAIL,   4
MAIL,    4

select c.prefered_contact_method,count(*)
from orders  o
inner join contact c on o.contact_id=c.id
inner join order_details od on od.orders_id=o.id
group by c.prefered_contact_method


What's the best way to list groups of values that are contained in other groups For example, starting with a table like this:

create table t as
select 'A' as x, level as y from dual connect by level<=5
union all
select 'B' as x, level+2 as y from dual connect by level<=5
union all
select 'C' as x, level as y from dual connect by level<=3
union all
select 'D' as x, level+2 as y from dual connect by level<=3;

alter table t add primary key (x, y);

select * from t;

X Y
- -
A 1
A 2
A 3
A 4
A 5
B 3
B 4
B 5
B 6
B 7
C 1
C 2
C 3
D 3
D 4
D 5

How do I get this:

SUBSET_X SUPERSET_X
-------- ----------
D        A
C        A
D        B

I'm posting my own effort as an answer but wondering if there is some other fancy way, perhaps with analytics or a set operator I don't know about

--edit

My test data unintentionally implies that the sets always consist of consecutive integers - unfortunately this is not true with my real data.

This does the trick but is not terribly fast:

select t1.x as subset_x, t2.x as superset_x
from t t1 join t t2 on(t1.y=t2.y and t1.x<>t2.x)
group by t1.x, t2.x
having count(*)=(select count(*) from t where x=t1.x);


Superuser is not permitted to login I created a superuser portal with the following command

create role portal with superuser password 'portal'

When I tried to login into postgres with portal user I am getting the following error in rails.

FATAL:  role "portal" is not permitted to log in

I can't follow what is going on.

of course I can alter user with login command to enable portal user to login. I would like to understand why super user can't login.

You didn't specify the LOGIN right:

ALTER ROLE portal WITH LOGIN;

If you use CREATE USER instead of CREATE ROLE the LOGIN right is granted automatically; otherwise you must specify it in the WITH clause of the CREATE statement.

Superusers do not inherently have the LOGIN right; a permissions check is still performed for superuser for this right. The reason for this is so that you can have a superuser role that you can SET ROLE to, but cannot be connected as directly.


Design the big data with mongo db I'm working now on the implementation of the following feature:
Say I have a collection in mongodb with millions of entries (very big say 100M), each entry is of type

{"uniqId", "properties"}

for example:

 {"uniqId" : "u1" , "properties" : ["p1", "p2"]}

I have a runtime component that should read the data by uniqId, and some batch process that should insert the data into this collection.
There is huge amount of reads and very rare updates (once in a week)

The problem is that i'll get always the full file (that could be very big milions of lines) that i should insert.

The solution i think about is as follwing:

There will be unique index on "uniqId" field.
I'll have a timestamp field on each document
I can use upsert operation to override the existed entries
Each operation will be a bulk of some valuable amount (say 1000 documents)
There will be a batch process that will remove the documents with 'old' timestamp

There is another solution:

Each time create new collection and then swap between the new and the old one
(but if i'll use sharding i saw that mongo does not support rename in that case)

Is this design seems to be OK?
How it can influence on the mongo performance?

I have the mongo with replica set configuration, and i did not used sharding yet.

Appreciate any valuable ideas,

If the files are sorted you can just find the id or timestamp of the last inserted document in the database, then only insert the records in the files which have ids that come after that id.

If they are sorted by _id or timestamp then doing this should be trivial.

The solutions you came up with are great as well.

However, be aware that creating entirely new collections will be slower than what i proposed, and upserting will be even slower than that.


How to set a parent from one-to-many relationship? I have tables as

CREATE TABLE articles
(
id varchar(10),
parent_id varchar(10),
Text text,
FOREIGN KEY(parent_id) REFERENCES articles(id) ON DELETE CASCADE,
PRIMARY KEY(id)
) ENGINE=InnoDB

CREATE TABLE article_map
(
id varchar(10),
unified_id int(11),
FOREIGN KEY(id) REFERENCES articles(id) ON DELETE CASCADE,
PRIMARY KEY(id,unified_id)
) ENGINE=InnoDB

For each unified_id, there are several ids. The aim is to save all ids in the table article to avoid being added again, but set one of them as the parent, and the reset as children. In this case, the content (Text) is set to NULL for all children, and any request to a child redirects to the parent.

How can I INSERT from table article_map to table articles to set one parent and the reset children of the assigned parent for each unified_id? There is no preference for choosing the parent.

You could use a CASE an correlated subqueries.

In the subquery select the minimum id for the row's unified_id.

If the row's id is equal to this minimum, parent is null, making the article a parent. If it isn't equal to the minimum it's a child of the article with the minimum id. Set the minimum id as parent then.

INSERT INTO articles
            (id,
             parent_id)
            SELECT amo.id id,
                   CASE
                     WHEN amo.id = (SELECT min(ami.id)
                                           FROM article_map ami
                                           WHERE ami.unified_id = amo.unified_id)
                       THEN null
                     ELSE
                       (SELECT min(ami.id)
                               FROM article_map ami
                               WHERE ami.unified_id = amo.unified_id)
                   END parent
                   FROM article_map amo;

Or you can use a union. First select all parents using the minimum and a GROUP BY unified_id. Unify that with an INNER JOIN of article_map an the parents.

INSERT INTO articles
       SELECT min(am.id) id,
              null
              FROM article_map am
              GROUP BY am.unified_id
       UNION ALL
       SELECT amo.id id,
              p.id parent
              FROM article_map amo
                   INNER JOIN (SELECT min(ami.id) id,
                                      ami.unified_id
                                      FROM article_map ami
                                      GROUP BY ami.unified_id) p
                              ON p.unified_id = amo.unified_id
                                 AND p.id <> amo.id;

(Minimum and maximum (and possibly others) are interchangeable here of course.)


Informix create procedure to query records and drop if value does not exist using select I am trying to create a procedure that will query a table column to retrieve table names and using this in proc to run a query to do a select if it exists. If it does not exist, delete row(s) from systables_growth table.

Below is what I have so far:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
      FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
        BEGIN
            SELECT * FROM v_tab;
            ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
            DELETE from systables_growth WHERE tabname = 'v_tab'
            END EXCEPTION WITH RESUME;
        END
    END FOREACH
END function;

I am receiving the below syntax error when creating:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
BEGIN
SELECT * FROM v_tab;
ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
#^
#  201: A syntax error has occurred.
#
DELETE from systables_growth WHERE tabname = 'v_tab'
END EXCEPTION WITH RESUME;
END
END FOREACH
END function;

I am trying to define some logic to query the records pulled from table to run a query doing a select from the table names pulled. If the select says table does not exist then I want the row to be removed from the table. I don't know if I am using the correct logic.

I don't want the procedure to generate any output, I want it to be silent.

I found an alternative solution using a stored proc in informix that will also work for me:

create procedure systables_growth_tblclnup()
DELETE FROM systables_growth WHERE tabname NOT IN
(SELECT unique(tabname) FROM systables WHERE systables_growth.tabname = systables.tabname);
end procedure ;


PostgreSQL Trigger to override insert on table with inheritance We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the correct child table and then return NULL to abort the pipeline so it is only inserted into the child table, though (you guessed it), this makes inserting documents a pain as you have to re-query for the created ids. We've looked into using an INSTEAD OF trigger type, but they cannot be applied to tables, only views.

Here is our current function used by the trigger:

CREATE OR REPLACE FUNCTION create_article_partition_and_insert() RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    partition_date TEXT;
    partition      TEXT;
    resultId       BIGINT;
BEGIN
    partition_date := to_char(NEW.updated, 'YYYY_MM');
    partition := TG_RELNAME || '_' || partition_date;
    IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition)
    THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' (LIKE ' || TG_RELNAME || ' INCLUDING ALL) INHERITS (' || TG_RELNAME ||
                '); ';
    END IF;
    RAISE NOTICE 'Inserting into %',partition;
    EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;'
        INTO resultId;
    NEW.id := resultId;
    RETURN NEW;
END;
$$;

Is there a better way to partition inserts by time and return the inserted row of the child table without also inserting into the parent table?

Using PG version 9.6.11.

Thank you!

Cross-posting from SO

You could possibly use lastval() or currval() to get the last value from the sequence. or use nextval() to get the id before the insert and then insert that value explicitly (instead of allowing the default value).

The third option is to ignore that column and instead use a natural key.  That you are able to get the ID by doing a select suggests that there is a natural key for this table.

Lastval and currval are private to your connection, so must be issued on the same connection that issued the insert.


Enterprise Manager Cloud Control 13c - Email notification should send only once I have an alert message with tablespace usage that should recheck the status in Cloud Control 13c and send the email only once, but it sends every 30 minutes. It shows me the TS_USAGE error of the tablespaces when it grows over 95%.
Can I have something configured to send only once when it happens and then some kind of clear notifications to send back that everything is ok, when I resize manually the tablespace and fix the issue?
Kind regards.

What you described is the default setting (for repeated notifications). I guess you have a customized environment and someone configured repeated notifications on purpose.
Repeated notifications can be configured globally in Setup / Notifications / Global Repeat settings, or specifically for metric alerts in Setup / Incidents / Incident Rules / "your rule set" / Rules / Create incident for critical metric alerts / Actions / Repeat Notifications.


Report based on MV: Deliberately make the report fail if MV is out of date? I have a query that is used in a BIRT report (in IBM's Maximo Asset Management platform).
I'm considering storing the query in a materialized view in order to improve performance and reduce the load on the system.
Question:
Is a way to deliberately make the report fail  if the MV is out-of-date?

In other words, we'd rather have no data in the report, than wrong data.

Edit:
I removed the excessive details from the question.

Why bother at all with a new column, when you can just check LAST_REFRESH_DATE in ALL_MVIEWS before executing the actual query.
ALL_MVIEWS
And there is basic/advanced query rewrite as well.


Is it enough to have only full backup and transaction log backup only for a good backup plan? If Full backup is scheduled for every Sunday at 12 pm and differential back up is scheduled for for every 6 hours and transaction log backup is scheduled for every 15 minutes.

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes? Is it enough to have only full backup and transaction log backup only ?

Thank you

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes?

Differential backup is used to decrease the Recovery time object(RTO), it gives you facility of restoring backups quickly. Differential backup includes all the changes made since last full backup, or full backup which started the differential chain. Differential backup is not affected by log backup so in event of disaster you would have to restore(in your case)

12 PM Sunday full backup
Differential backup taken before the disaster strike. Note this is just one diff backup you have to restore no matter how many you have taken because its cumulative
The log backups after diff backup (in point 2) stopping just before the disaster struck.


How to disable HTML output in mysql console? I've used several mysql instances on many servers, but this one had a suprise for me tonight when I tried to use its console for the first time :

me@MYSERVER:~$ mysql -H my_base (....)
(...)
Server version: 5.5.37-0ubuntu0.12.10.1 (Ubuntu)
(...)

mysql> show tables;

Tables_in_crocowords

T_BEST_LEVEL

T_CONNECTED

T_CONTEST

T_MONEY

T_MONEY_CREDIT

T_MONEY_DEBIT

T_PARTICIPATION

T_PERFECT

T_PLAYER

T_RANK

T_REPLAY

T_REPLAY_DATA

T_REPLAY_LEVEL

13 rows in set (0.00 sec)

mysql> select * from T_BEST_LEVEL LIMIT 3;

fid_contest	best_level	best_level_score

72	1	13

73	1	60

73	2	44

3 rows in set (0.00 sec)

All outputs are in HTML format !

I've tried to look and browse the documentations about this, but couldn't figure how to remove this html output.

Can you help me ?

:~$ mysql --version
mysql  Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (i686) using readline 6.2

You told it to generate html.

$ mysql -H my_base

The -H option is an alias for the --html option.

I assume you were thinking of -h ... which is the shortcut for --host.

http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_html