Storing vs calculating aggregate values Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?

For example, suppose I have widgets which users can rate (see schema below).  Each time I display a widget I could calculate the average user rating from the Ratings table.  Alternatively I could store the average rating on the Widget table.  This would save me from having to calculate the rating every time I display the widget, but then I'd have to recalculate the average rating each time a user rated a widget.

Ratings       Widgets
---------     -------
widget_id     widget_id
user_id       name
rating        avg_rating  <--- The column in question

It depends. Pre-calculating aggregate values places a larger load on writes, deriving them makes reads more difficult

If you are frequently accessing a derived value, pre-calculation is a valid de-normalization step. However, in this instance, I recommend using a Materialized View (a view, written to disk, linked by trigger to the parent tables). The materialized view is designed to store frequently asked but tedious-to-derive data, and is useful for high numbers of writes and low numbers of reads.

In a high-write, high-read scenario, consider having a task in the background which mimics the effects of a materialized view, but in less than real-time. This will present a "good enough" average while preserving write and read performance.

In no circumstances, should you treat the derived column like a "normal" column: make sure the data presented in the Widgets "view" is present elsewhere in the table, such that the entire tuple can be derived by whatever processes you emplace. This question is also strongly database (and database-version) specific, so I recommend performance testing of the aggregate (with appropriate indexes) against a normal-sized data set and the materialized view.


Will SYSTEM_USER return the unique user id for users authenticated by Active Directory I want to set up Active Directory authentication for my users, who will be assigned to specific database roles. I also need to log activity and ensure I have captured the IDs of the indivudals connected.

I have found simple instructions on how to do it. ( How do I assign an entire Active Directory group security access in SQL Server 2008? )

... But Will SYSTEM_USER return the logon id of the active directory connection or the individual user? (Unfortunately I cannot test this!)

A call to system_user will return the AD user's name in your case.  For instance, if you have a user set up as YourDomain\YourUser1:

select system_user

-- this returns "YourDomain\YourUser1"

If YourDomain\YourUser1 is part of a AD group (i.e. YourDomain\YourGroup1), then it would still have the same output as above (returning "YourDomain\YourUser1").


exporting a sql view to a csv file Whilst I can just about script to pull data via queries, I'm finding it very difficult to figure out how to export the results of a query to a .csv file and save in a network storage location (novice when it comes to the more technical side!).  I've tried various things like the bcp command or using the export wizard or sqlcmd but I can't get anything to work!  I also want to be able to automate the export i.e. run an agent job to create/run this export weekly.
An ex-colleague had set this up but couldn't get it to work.  When I run this:

USE [DataInsight]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

BEGIN

sqlcmd -S londata01\raisers -d DataInsight -E -s, -W -w 65535 -Q "SET NOCOUNT on; select * from dbo.All_Email_Addresses" -s "," -o "C:\Users\scarsen\Documents\all_emails.csv"

END

whereby londata01\raisers is the server name, datainsight the DB and all_email addresses a table.  I get

  Incorrect syntax near 'sqlcmd' when run it.

I turned on SQLCMDMode in SSMS and still get this error.

Tried this:

bcp "select * from datainsight.dbo.All_Email_Addresses" queryout 'C:\Users\scarsen.csv' –c –t , –S londata01\raisers –T

but get Incorrect syntax near 'queryout'.

Because I want to automate for a weekly output, I can use an agent job so need to be able to create export within SSMS (I think)

I also tried something that I can't remember using master..xp_cmdshell but a message came up saying I didn't have security settings enabled (or similar)

I also tried saving an export via the wizard to an SSIS but get a message saying storing or modifying packages requires runtime and db to the the same version.

In a nutshell, tried lots of things with my limited knowledge but wondered if anyone might be able to help?

bcp is a command-line command utility - you can't call it as a T-SQL command.  However, if you enable xp_cmdshell, you can run it via the command-line using this code inside a SQL Server Agent Job (or query window in SQL Server Management Studio):
DECLARE @cmd varchar(1000);
SET @cmd = 'sqlcmd -S londata01\raisers -d DataInsight -E -s, -W -w 65535 -Q "SET NOCOUNT on; select * from dbo.All_Email_Addresses" -s "," -o "C:\Users\scarsen\Documents\all_emails.csv"';
EXEC sys.xp_cmdshell @cmd;

In the example above, we're exporting data into the C:\Users\scarsen\Documents folder.  In order for this to work from inside SQL Server, the SQL Server Service Account must have access to this folder.
You can enable xp_cmdshell at the server using these T-SQL commands, which you'd run via a query window in SQL Server Management Studio (SSMS):
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

You'd only need to enable it once per SQL Server.  In case you want to reverse the above change, simply run this:
EXEC sys.sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

There really is no need to reverse the show advanced options configuration setting.  It doesn't have any bearing at all on anything other than letting you configure things like xp_cmdshell.


Service Packs and CUs for SQL Server, do these patches make any changes to user database files? or just instance level binaries? I am curious about patching on SQL Server.  Does anyone know if SPs and CUs make any changes to user database files or is it just the database engine and instance level binaries that get modified/updated?

Thanks

Service packs and CUs do not change user databases. What is changed is binaries and system databases. Sometimes, database that are not strictly system databases but are shipped by microsoft are changed as well via upgrade scripts (sysutility_mdw, SSISDB, distribution...)


Is it safe to run antivirus software on my database servers? Some implementations of antivirus software are known to Microsoft to cause a variety of problems for SQL Server, and Microsoft has a support article dedicated just to picking the right antivirus software for your SQL Server hosts.

From your experience, is it safe to run antivirus software on your database servers?

Given all the required antivirus exclusions and caveats to consider, is it practical?

For me, personally, I have not found it practical. But for 15+ years I have worked in environments that were dedicated to isolating and protecting the database servers. All of the activity that can lead to virus and malware infestation on a SQL Server machine can be prevented IMHO. Extremely limited access, no browsing from the server, no hosting of file shares, rigorous firewall protection, principle of least privilege, etc. Some of these lines get blurred a bit depending on the functionality of your server, for example filestream/filetable.

In my experience anti-virus is often a false security blanket. It is either responding as a post-mortem correction or, in some cases, doesn't have the signature yet for the new threat - either because it is not kept up to date by the system administrator or by the vendor (or both). Since rules and exceptions can change over time, if sysadmins can't keep anti-virus signatures up to date, how will they keep the rules and exceptions up to date?

Your goal should be to secure SQL Server so that you aren't concerned about anti-virus. If you're not having sex, you don't need to buy condoms. :-)


Timeout expired error while using copy database wizard Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get past some issues and I was able to import small to medium size SQL Server 2000 databases into SQL Server 2012.

However, the wizard constantly fails for a 30GB database with these errors:

  Message: An error occurred while transferring data. See the inner exception for details.
  StackTrace: at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
  at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
  InnerException --> Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
  StackTrace:
  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
  Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
  at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
  BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
  stateObj)

I have double checked, it is not a filepath, permission or disk space issue. I think the CREATE DATABASE step takes 2 minutes or so and the wizard assumes that the operation timed out. I manually created an empty database with same filepath and filesize using SQL, that worked. Interestingly, a 1GB database copy failed with same errors and succeeded on 2nd try.

Please help.

A much more efficient path than messing with that wizard would be to:

Take a full backup of your 2000 database.
Restore it to a SQL 2005, 2008 or 2008 R2 instance, all of which support 2000 databases. You can still get the evaluation edition of 2008 R2 here, if you don't have any applicable instances in place. Note that Express won't work as it has a 4GB / 10GB data file limitation depending on version.
Change the compatibility level to something above 80 (90 or 100, depending on whether you used 2005 or 2008/2008 R2) using ALTER DATABASE.
Take a full backup of this database after it is no longer in 80 compatibility mode.
Restore this new backup to your SQL Server 2012 instance.
Change the compatibility level to 110 and update statistics.
TEST.


Developing an Azure Sql Database backup strategy I know Azure databases are backed up and kept for a retention period, which I believe the default is 30 days.  I'd like to come up with a plan to mimic our current strategy with a physical SQL Server box.  I'm also open to other strategies, but the main goal is if a customer says "I think my project was good on March 3, 2016" I can find the closest possible database quickly if their query was within the past year, and also find a close backup, but less quickly (and more cost effective).

Our largest database is around 4GB and most others are very tiny.  It is quite possible that the "hot" storage, which is quickly accessible might be so cost effective that I need not deal with "cold" storage such as Amazon Glacier.

Right now we have backups that occur every 15 minutes.  But we also do full backups:

Every day
The first day of every week
The first day of every month

I mostly use these .BAK file backups for user error data recovery or I download the file to run on a local instance for development purposes, mostly debugging or feature development.  I probably could also just restore the backup to a temporary database on Azure and use the connectionstring from a development box, but I would like to make sure I can get a .BAK file as well.

I keep the daily, weekly, and monthly backups on a backup drive attached to the Sql Server so I can quickly grab any backup in the prior 30 days, any weekly backup for the prior 3 months, and every monthly backup for the past year.

I also take a monthly backup and upload that to Amazon glacier so I can retain those forever (maybe we will start dropping them after 5 years)

Now I believe (correct me if I'm wrong) that with Azure, if I use 30 day retention I can restore a backup from almost any point within the 30 day window.

I'm looking for a way to replicate this entire strategy on Azure.  Are there built in Azure services for this, or would I need to create a VM and have scheduled scripts?  What about third party products such as Cloudberry that would make managing backups simple?  This includes seeing and retrieving both my "hot" and "cold" backups.

There are a number of ways to schedule jobs for Azure SQL DB such as elastic jobs or azure automation. They can be setup to export your database at the intervals you require so it provide similar coverage as backups. Not the same experience and will be pretty klunky if your databases grow to massive sizes but in the few to tens of GB, you won't miss the super powers of local SQL Server backup (e.g. compression).

The team is trying to provide a solution for the long term retention requirement for backups so if it's important to you, add the request to https://feedback.azure.com/forums/217321-sql-database. I don't know what state that investigation is in but always helpful to get users chiming in with requirements so be very explicit on how long you really need to keep the backups around.


MySQL Replication/Backup I'm a new DBA working with a MySQL(5.6.19) instance I have running on a Mac Pro, I'd like to back it up with the Percona tools, but there isn't a distro for Mac, so my plan is to create a copy of the database on a linux box, make it a slave to the one on the Mac and then use the Percona tool to back it up from there (this will be on a local network). This Linux box will also be running the full system backup software (Bacula) we're setting up. I've looked into how to set this up and I think I get most of it, but I'd love advice on the following issues:

1) Bacula uses MySQL for part of its back end, if I set up the slave server on that machine will Bacula still be able to use MySQL as its back end? (More generally, can there be other schema on a MySQL instance that is running in slave mode?). Or do I need to configure a separate server instance as here: http://dev.mysql.com/doc/refman/5.6/en/multiple-servers.html  ? (or are there better ways that I'm missing?)

2) I can do a mysqldump of the current database, but it will be rather slow as I have a few very large files, will there be a problem transferring the files directly as http://dev.mysql.com/doc/refman/5.6/en/replication-howto-rawdata.html from Mac to Linux?

Thanks everyone! Any responses to either would be much appreciated!

David

There seems that some people used to patch the source code of xtrabackup to make it usable in OSX:

http://sourceforge.net/projects/xtrabackup-osx/

But expect some problems, as some of the functionalities depend on a linux system (but you can try to give it a shot). The other alternative is MySQL Enterprise backup, which has full Mac compatibility.

1) You can run extra databases on the slave, within the same instance, without problem. There is no compatibility problem, it is just that there is some operational overhead on exceptional cases. Let me elaborate– You cannot setup the slave as read-only, which can increase the danger of slave-drifting by writing to the wrong database by accident. If the master fails, you have to separate the databases. And as you are writing to the binary log of the slave, it would be more difficult to use it for rolling forward a backup or using gtid replication (you cannot just switch master and slave roles, as it would try to replay all slave transaction). But in general, nothing will break more than usual, I use several setups like this where I use the slave as the backup source and the monitoring database.

2) Yes, you can clone it easily just by transferring the files. You may have some compatibility issues with the different case handling (Mac is not case sensitive, Linux is, which can affect db and table names), but that can be fixed in configuration in most cases. Aside from that, and the usual "moving the files around" issues (permissions, locations, configuration, etc.), you should be ok for moving the files, that should be endianness and OS independent for the most case. Please remember to completely shutdown the server before trying that, or you will end up with a corrupted set of files.


SSIS Event Handler at Package Level I have a fairly simple SSIS package (handful of Execute Task and a few Data Flow task). The end result I am looking to have:

I would like to get notified by email when an error occurs anywhere
in the package
I would like that email to include: Package name, failed task, and error information.

As I understand I set this up in the following manner:

Create an Event Handler for OnError for the package level (Excutable is set to the package name).
Configured the SMTP connection and added in an Send Mail Task
Within that task I configured an expression for the Subject and MessageSource. Those two expressions are noted below.

Expressions:
Subject - "Error occurred on " +  @[System::PackageName]
MessageSource - (line breaks added for readability)
(DT_WSTR,250)("An error occurred in execution of the package: " +  @[System::PackageName] + "\n" +
"The task that experienced the error: " +  @[System::TaskName] + "\n" +
"The error information returned:\n" +
"Error Code: " +  (DT_WSTR,50)(@[System::ErrorCode]) + "\n" +
"Error Description: " +  @[System::ErrorDescription])

Now what occurred after I forced an error:

Task failed as expected, but the event handler failed as well.
OnError event fired, except not as expected. First issue is truncation, received error A truncation occurred during evaluation of the expression. This is the content of the email received, which I don't see the expected new lines:

An error occurred in execution of the package:  The task that experienced the error: Failure notification The error information returned:
Error Code: 0
Error Description:

Second error for the same OnError: property "MessageSource" cannot be evaluated. Modify the expression to be valid.
I received two emails when the task failed.

Environment Info: SQL Server 2008 R2, running the package through BIDS only at this time.

Questions:

What do I have wrong in the expression for the MessageSource? I can obviously evaluate the expression successfully in the properties window, and understand that is because the variables are not populated. So how do you configure that were it will not truncate no matter what error is passed in, just set it to some large number length?
I am not looking for some custom method or code to perform this as the package is fairly simple and is only run every blue moon. However, would it be best to simply create an OnError event for each task? As I understand it any child task that fails has the error information passed up to the parent package, so it should work right?

Pre 2012, expressions were limited to 4k. That's not a Variable's limit, you can assign stupid-long values to a Variable, it just can't be done with expressions. Use a Script Task to do the concatenation and assignment to your variable and see if that doesn't clear up #1

The thing you're likely to run into, and that I never found a satisfactory resolution, was the double firing of messages. I know, they say set propagate to false and all of that but I never seemed to get it to work as I wanted. In our world, a package would fail if an error raised so the OnTaskFailed event was more useful for only getting the signal once but your mileage my vary.

A better approach we used at a different job was to have a process sweep the sys.ssiserrorlog table every N minutes looking for failures. Since all packages logged to it, it was a cleaner and more reusable approach for us.

I've seen people make mention of always running packages from the context of a parent package to adhere to the DRY (don't repeat yourself) principle so the parent has the error notification and such built into it.

The final bit to be aware of, don't rely on SSIS's error handling exclusively. We had a deploy that went well, or so we thought. When the package fired though, it failed to validate as the metadata for a table didn't match what the data flow expected. A package failing to validate is like a syntax error and so the OnError/OnTaskFailed event's didn't even get a chance to fire and tell us that we were in an awful state. And we went a few days without noticing. Oopsie


Filtering subquery results with group by I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and print_issues.pub_date would be returned.

SELECT
    print_issues.pub_date,
    print_issues.x,
    s.page_size
FROM print_issues,
  ( select pub_date, page_size
    from print_page_size,
          print_issues
    where effective_date < print_issues.pub_date
    order by effective_date desc
  ) as s
WHERE s.pub_date = print_issues.pub_date
group by print_issues.pub_date
order by print_issues.pub_date desc

The query is an artifact of the schema of print_page_size. That table has the columns page_size and effective-date. New entries with a greater effective_date supersede old ones. The goal of the query is to unite the print_issue table with the page_size in effect when each issue was published.

Sample Data:

TABLE: print_page_size
======================
effective_date      page_size
2014-01-01          100
2017-05-01          105

TABLE: print_issues
===================
pub_date      x
2017-04-26    "Random"
2017-05-02    "Data"

OUTPUT
======
print_issues.pub_date          print_issues.x    s.page_size
2017-04-26                     "Random"          100
2017-05-02                     "Data"            105

My questions: Is there a problem with this approach? Is there a more direct way to to achieve the desired result?

Your query is non-deterministic as it abuses GROUP BY.

It may give incorrect/unexpected results in any version of MySQL, depending on the execution plan and available indexes. You may not have seen such behaviour because it hasn't happened yet (you were just lucky or the indexes and size of the table have up to now lead to plans that give correct results) or it happened but you didn't notice.

There are versions (MariaDB 5.3+) where similar queries - not sure about this one but similar - will almost always give incorrect results due to some optimizations that remove the redundant ORDER BY in the subquery.

There are versions of MySQL (5.7) where you'll get an error if you try the  query - exactly because it might give inconsistent results. In that version, you'll have to change the default settings (and get the wrong non-deterministic behaviour) or you'll have to rewrite/correct the query.

In short, don't use it.

I would write the query like this. Much simpler, deterministic and probably more efficient as there is no GROUP BY and doesn't join the pi table twice:

SELECT
         pi.pub_date,
         pi.x,
         ( SELECT   pps.page_size
           FROM     print_page_size AS pps
           WHERE    pps.effective_date < pi.pub_date
           ORDER BY pps.effective_date DESC
           LIMIT    1
         ) AS page_size
FROM
         print_issues AS pi
ORDER BY
         pub_date ;              -- or whatever

An index on print_page_size (effective_date, pagesize) would be further help.


How to remove specials characters, but replace spaces? I have a string with spaces & special characters: ABC%$! DEF*& GHI
I want to remove all the specials characters, but replace the spaces with an underscore:  ABC_DEF_GHI
I have:
REGEXP_REPLACE(c.category_name, '[^\w]+','','g')

But that removes all the characters, including spaces.

Do the space-to-underscore replace() first, and your existing expression will do the rest.
testdb=# select regexp_replace(replace('ABC%$! DEF*& GHI', ' ', '_'), '[^\w]+','','g');
 regexp_replace
----------------
 ABC_DEF_GHI
(1 row)


Duplicate result when I select with distinct values from foreign key constarint I want to select values from two different tables which have one to many relationship between them.
During select it's retrieving duplicate values even when I use distinct and group by. Above table is person table and the beneath one is address.

I am using this query to retrieve matched records.

I want to group address into single record but its working am I missing something?
I want my result to look like this

Here is an example of how that could be achieved

--demo setup
Declare @Table1 table (id int, name varchar(20), lastname varchar(20), imageshortpath varchar(100), imagefullpath varchar(100))
insert into @Table1(id, name, lastname, imageshortpath, imagefullpath) values
(2108,'mudasir', 'khan','imageshortpath','imagefullpath')

Declare @Table2 table (id int, peopleid int, address varchar(20))
insert into @Table2(id, peopleid,address) values
(1,2108,'hyderabad'),(2,2108,'larkana')

--solution
select t1.*,
STUFF((
    SELECT ',' + address
    FROM @Table2 t2
    WHERE t2.peopleid = t1.id
    FOR XML PATH('')
    ), 1, 1, '') AS Address
from
@Table1 t1

| id   | name    | lastname | imageshortpath | imagefullpath | Address           |
|------|---------|----------|----------------|---------------|-------------------|
| 2108 | mudasir | khan     | imageshortpath | imagefullpath | hyderabad,larkana |

Check out this excellent answer about How Stuff and 'For Xml Path' work in Sql Server.


SQL Server : auditing AD Users - how can we identity AD User account from AD Groups I would like to identity which accounts on our servers belong to AD Users.

Currently, I find a plethora of accounts from users that should in fact be part of AD Groups registered on the same SQL Server. In effect these AD User accounts are redundant (or they should be).

Question is: how can we identify these AD users and AD groups in SQL Server?

Thanks,

Below is a script that uses xp_login_info (https://msdn.microsoft.com/en-us/library/ms190369.aspx) to get the AD permission paths for each AD user and group with a SQL Server login.

SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL
     DROP TABLE #results;
CREATE TABLE #results(
      account_name sysname
    , type char(8)
    , privilege char(9)
    , mapped_user_name sysname
    , permission_path sysname NULL
    );

DECLARE @SQL nvarchar(MAX) = (
    SELECT 'INSERT INTO #results EXECUTE xp_logininfo ''' + name + ''', ''all'';'
    FROM sys.server_principals
    WHERE type_desc IN(N'WINDOWS_LOGIN', N'WINDOWS_GROUP')
    FOR XML PATH(''));

EXECUTE(@SQL);

SELECT
      account_name
    , type
    , privilege
    , mapped_user_name
    , permission_path
FROM #results
ORDER BY account_name, mapped_user_name;


How to avoid a cyclic dependency (circular reference) between 3 tables? I have 3 tables:

People
Post
Likes

When I design the ER model it has a cyclic dependency:

         1:N
People --------< Post

         1:N
Post ----------< Likes

         1:N
People --------< Likes

The logic is:

1 people can have many posts.
1 post has many likes.
1 people can like many posts (created person cannot like his own post).

How can I remove this kind of cyclic design? Or is my db design wrong?

Business rules
Let us make some rewordings to the business rules you have presented:

A Person creates zero-one-or-many Posts.
A Post receives zero-one-or-many Likes.
A Person manifests zero-one-or-many Likes, each of wich pertains to one specific Post.

Logical models
Then, from such set of assertions, I have derived the two logical level IDEF1X[1] data models that are shown in Figure 1.

Option A
As you can see in the Option A model, PersonId migrates[2] from Person to Post as a FOREIGN KEY (FK), but it receives the role name[3] of AuthorId, and this attribute makes up, together with PostNumber, the PRIMARY KEY (PK) of the the Post entity type.
I assume that a Like can only exist in connection with a particular Post, so I have set up a Like PK that comprises of three different attributes: PostAuthorId, PostNumber and LikerId. The combination of PostAuthorId and PostNumber is a FK that makes the proper reference to the Post PK. LikerId is, in turn, a FK that establishes the suitable association with Person.PersonId.
With the aid of this structure, you ensure that a determined person can only manifest a single Like occurrence to the same Post instance.
Methods to prevent a Post Author from liking his own Post
Since you do not want to allow the possibility that a person can like his/her authored posts, once in the implementation phase, you should establish a method that compares the value of Like.PostAuthorId with the value of Like.LikerId in every INSERT attempt. If said values match, (a) you reject the insertion, if they do not match (b) you let the process continue.
In order to accomplish this task in your database, you can make use of:

A CHECK CONSTRAINT but, of course, this method excludes MySQL, since it has not been implemented in this platform so far, as you can see here and here.

Code lines inside an ACID Transaction.

Code lines within a TRIGGER, which could return a custom message indicating the rule violation attempt.

Option B
If the author is not an attribute that identifies in a primary way a post in your business domain, you could go with a structure similar to the one depicted in Option B.
This approach also makes sure that a post can only be liked by the same person one single time.

Notes
1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST).
2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.
3. A role name is a denotation assigned to a foreign key attribute in order to express the meaning of such attribute in the context of its corresponding entity type. Role naming is recommended since 1970 by Dr. E. F. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates this procedure.


How to set up the SQL Server to read a database from external hard disk or a different hard disk from my PC Is it possible for SQL Server to access a database from a different location? My Primary Hard Disk (C:) is almost full, however I have a second Hard Disk in my computer.

When I try to restore the database, SQL Server says

  There is insufficient free space on Disk C:// to create Database

Is there any way to move from C:\ to D:\?

You will want to use the MOVE option in the restore command to tell the restore process where to put each of the files in the backup set. This is done for each logical file name specifying the new physical location.

See the details in this technet article

It also shows how to get the logical file names from the backup set.


How to speed up mysql query against a 204 million record table For some reason my server has been finicky lately and for the life of me I can't figure out why. I monitor the memory usage by running

shell# watch -n 5 free -m

without running any activities memory used is anywhere from 4-5 GB. Once I run a query it starts increasing all the way up to 35 GB. For example, see output below

Every 5.0s: free -m                                     Fri Mar 13 10:17:28 2015

             total       used       free     shared    buffers     cached
Mem:         36196      34429       1767          0        124       3172
-/+ buffers/cache:      31132       5064
Swap:        24563          0      24563

the query I am running is

SELECT DISTINCT(c1),c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12
FROM db1.tb1
WHERE NOT EXISTS (SELECT * FROM db2.tb2 WHERE db2.tb2.c1 = db1.tb1.c1)
and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N'
GROUP BY c1
LIMIT 5000

All columns on both tables are indexed. Normally this query only takes me a couple of minutes. But last couple days, it has been taking 4+ hours

The db1.t1 has 360 columns and 204 million records. So it is quite large
It uses the InnoDB engine

Ubuntu  v. 12.04

MySQL 5.5

I have 36 GB RAM installed

4 core Intel Xeon processor 2.93 GHz with hyper threading enabled.

Below is my my.cnf file (im only going to post the un-commented portions of the file)

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 50.253.189.57
key_buffer      = 5G
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 50
myisam-recover         = BACKUP
max_connections        = 500
table_cache            = 3200
thread_concurrency     = 10
bulk_insert_buffer_size = 1G
join_buffer_size = 500M
sort_buffer_size = 5G
query_cache_limit   = 0
query_cache_size        = 0
open-files-limit = 65535
tmp_table_size = 3G
max_heap_table_size = 1G
table_definition_cache = 1024
table_open_cache = 2048
thread_cache_size = 50
max_connect_errors = 1000000
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 26G
innodb_additional_mem_pool_size = 1G
innodb_buffer_pool_instances = 2
innodb_log_buffer_size = 1G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[isamchk]
key_buffer      = 16

All columns on both tables are indexed.

Enough said.  That is not the right way to go about indexing.

  WHERE db2.tb2.c1 = db1.tb1.c1

db2.tb2 needs INDEX(c1) -- Keep in mind that a PRIMARY KEY is a UNIQUE key is an INDEX, so do not redundantly add INDEX if you already have PRIMARY KEY(c1).

  and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N'

Needs a compound index; recommending adding these three, since there are no clues of which would be best:

INDEX(c6, c9)
INDEX(c6, c10)
INDEX(c6, c12)

At the same time, remove INDEX(c6) because it is now redundant.  For further discussion, Cookbook on making an INDEX, given a SELECT.

  tmp_table_size = 3G
  max_heap_table_size = 1G

Very dangerous.  Are you swapping?  Swapping is much worse on performance than lowering some of the settings.  Each SELECT that needs a tmp table will allocate 1GB; if you have lots of such queries running at the same time, you will rapidly chew up RAM.  Lower both of those to their defaults.

  key_buffer = 5G

That is for MyISAM indexes.  If you are not using MyISAM, set it to 20M.  InnoDB is the preferred engine; 26G (out of 36GB) is reasonable if all of your tables are InnoDB.

There may be more suggestions; see how far you get with these.

Edit

  DISTINCT(c1),c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12

May not mean what you think -- DISTINCT is not a function, and the parens are irrelevant.  That de-dups the entire set of 12 expressions.  Also, it is probably irrelevant, unless you actually have duplicate rows.

Is c1 your PRIMARY KEY?  Hence UNIQUE?  So the GROUP BY is also irrelevant.  Removing it may help speed.


Why is truncate DDL? I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ?

Q. Why Truncate is DDL Where as Delete is DML ? Both do almost same job (removing rows)

Ans. When we are using Truncate, we are de-allocating the whole space allocated by the data without saving into the undo-table-space. But, in case of Delete, we are putting all the data into undo table-space and then we are deleting all the data.

Please, if anyone knows best answer for the above, please explain.

The DML versus DDL distinction isn't as clear as their names imply, so things get a bit muddy sometimes.

Oracle clearly classifies TRUNCATE as DDL in the Concepts Guide, but DELETE as DML.

The main points that put TRUNCATE in the DDL camp on Oracle, as I understand it, are:

TRUNCATE can change storage parameters (the NEXT parameter), and those are part of the object definition - that's in the DDL camp.
TRUNCATE does an implicit commit, and cannot be rolled back (flashback aside) - most (all?) DDL operations in Oracle do this, no DML does.

The fact that TRUNCATE doesn't run ON DELETE triggers also sets it apart from normal DML operations (but some direct path DML operations also skip triggers, so that's not a clear indicator).

That same documentation notes that DELETE generates UNDO, but TRUNCATE doesn't, so your statement is correct in this respects. (Note that TRUNCATE does generate some REDO so that the truncation can be replayed in case of restore/recovery.) But some NOLOGGING operations can also produce reduced UNDO (not sure about none at all), so that's not a clear indicator either in my opinion.

So I'd sum it up as:

truncate is not "transactional" in the sense that it commits and can't be rolled back, and can modify object storage attributes. So it's not ordinary DML - Oracle classifies it as DDL.
delete is an ordinary DML statement.


How to check actual execution plan of a query after it has been executed In SQL Server, how can we see actual execution plan of a query, after it has been executed?

As per my knowledge, only estimated execution plan (not actual execution plan) are stored in plan cache. is that correct?

Note: We do not have any third party monitoring tools.

Prior to SQL Server 2019, there was NO possibility to get the actual execution plan after the query has been executed.

Started with SQL Server 2019+, that option exists.

First you should ALTER DATABASE SCOPE CONFIGURATION.

This option enables the equivalent of the last actual execution plan in sys.dm_exec_query_plan_stats ( the same could be accomplished by enabling TF 2451 ).

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

See more on ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

And than you can execute query like this one in order to get the actual execution plan.

SELECT qps.query_plan,
       st.text,
       DB_NAME(st.dbid) DBName,
       OBJECT_NAME(st.objectid) ObjectName,
       cp.usecounts,
       cp.size_in_bytes /1024 [Size in kB],
       cp.objtype,
   cp.cacheobjtype
FROM sys.dm_exec_cached_plans AS cp
     CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
     CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.encrypted = 0
      AND DB_NAME(st.dbid) = 'AdventureWorks2016'
ORDER BY [Size in kB] DESC;

And the result is shown in the image below.


What's the best way to backup DB files via network on Linux and Solaris machines? I wonder what the best way to backup files via the network.

I have Solaris machines with Oracle 10i on a Veritas cluster. Machines are connected to EMC storage.

The /data/oracle directory is mounted on the EMC storage.

What I want is to backup the /data/oracle directory (70G) on some backup machine via the network (include soft-linked files). The reliability of copying files is very important.

I checked and found some ideas to do that.

For example the first option is to use rsync:

rsync   -WavH –progress   /data/oracle $backup_server_ip:/Backup_dir

The second option is transfering the files with tar and ssh, dealing with compression on both sides of the pipe:

cd /directory_that_we_want_to_backup
tar cpf - . | bzip2 -c | \
  ssh  $backup_server_ip  "cd /Backup_dir && bzip2 -d | tar xpf -"

and so on.

I need advice on what is more reliability from the options above, and maybe on what other good options there are for this.

Well theoretically you can issue the command "alter tablespace begin/end backup". Before and after you execute rsync. You will also need a backup copy of the control file. This approach was used many years ago before RMAN was introduced. The restore really requires Oracle experience. This approach is for example described in this book: "Unix Backup and Recovery".

A really recommend you to get familiar with RMAN. Even if you do not use backup agent like TSM/Netbackup you can still create local disk backups using RMAN and then you can synchronize this storage using rsync.


Restoring an entire SQL Server database instance I have a server that we will be completely reformatting then rebuilding.  Everything will be rebuilt exactly as it is now - same filenames, same directories, etc.

After SQL Server is installed, I could reattach each of the databases on the server instance one by one from the full backups I've taken.

However, will the following also work to make the job faster?

Shut down the SQL Server instance
Copy all databases, including system databases, to another disk
Reformat the server and reinstall SQL Server
Shut down the SQL Server instance
Copy all files back to their original locations, overwriting existing files (would I also need the hidden Resource database?)
Start the SQL Server instance again

As long as everything is in the correct location, shouldn't the master database fire right up without issue, and the full server will be restored to the same way it looked pre-reformat?  I'm just wondering if this could save me some time.

Personally I advocate quite strongly against the detach / attach method. You're already taking proper backups, right? Why not continue that plan and use backup / restore?

Detach / attach is dangerous, for one simple reason: if something happens during the detach or during the copy, you now have ZERO copies of your database.

With RESTOREs you will be able to proceed even if your new server gets hosed and you need to use a different server, or you're not able to re-use the same server name, or any other host of things could go wrong.


CASE ORDER BY with multiple columns, and different sort options I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT.

@Orderby = 1 Then Date column should be ASC
@Orderby = 2 Then Date column should be DESC

My question is: How can I get the date column to sort desc when I am passed a 2 for that parameter, and have the a string column sort asc in the same CASE ORDER BY statement?

This is what I have now for the CASE ORDER BY

    ORDER BY
    CASE WHEN @OrderBy = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END ,
    CASE WHEN @OrderBy = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) + tp.LastName + tp.FirstName END DESC

This codes parses and returns a result set without error, but the 2nd CASE ORDER BY is all in DESC sort order, when I would prefer to have ccd.CertEndDate DESC , tp.LastName ASC , tp.FirstName ASC

Thanks in advance.

Break it out a little more:

ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
         CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
         tp.lastname ASC,
         tp.firstname ASC

You only need the sort order to change on the first field, so don't enclose the others in the CASE.

It should be noted that we don't include an ELSE for each CASE, which means any other value will return NULL and be discarded from the ORDER BY.


mongodb schema design advice I have a users index which has fields like id, email, password, etc. A user can see all other users except the ones who had blocked him. I want to design a database schema for this problem.

So I'm using MongoDB and I have these two tables.

Users

id   email   password
01   a@b.co  123
02   b@c.co  XYZ

now, I need a way to store the block relations information. If x has blocked y then y can't see x on the user's list.

If I add an array in user y which contains all user id who has blocked them so would it be a good solution? or is there any other alternative?

Users

id   email   password   blockedBy
01   a@b.co  123        []
02   b@c.co  XYZ        [01, 07]

now, I'll show all the users to user 02 except 01 and 07.

If the information fits inside the record, go for it.

But remember that there is a limit to it, if you limit the block to something like 1000 users, should be fine.

When you are not in the position to define limits, then you should create another collection to handle this relation.


changing a range into discrete values for a field in oracle I have a data table that contains records like this:

ZIP       Ground  Air
010-041   005     305
150-163   004     304

As I have it right now, it's actually an excel file but I eventually need to convert it into a table in Oracle.  The problem is that the format of the first column is a range but I need it as discrete values i.e.

ZIP

10    005     305
11    005     305
12    005     305
13    005     305
14....

What's the easiest way to accomplish this in excel and/or oracle?

Assuming you have inserted the data into Oracle perhaps using an external table, you could use the connect by clause of a hierarchical query to generate the additional rows you need.

Setup to simulate the source data:

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (Zip Varchar2(7), Ground Varchar2(3), Air Varchar2(3));
INSERT INTO t1 VALUES ('010-041','005','305');
INSERT INTO t1 VALUES ('150-163','004','304');

Solution:

CREATE TABLE t2 AS (
  SELECT substr(zip,1,3) + Level - 1 Zip, Ground, Air
  FROM t1 a CONNECT BY level <= substr(zip,5,3)-substr(zip,1,3)
  );

If you'd rather do more work on the Excel side, then you could create the following table in Oracle:

CREATE TABLE t1 (Zip Number(3), Ground Varchar2(3), Air Varchar2(3));

Then run a formula like this for each row in Excel:

=CONCATENATE("INSERT INTO t1 (SELECT ",MID(A2,1,3),
    "+Level-1,'",B2,"','",C2,"' FROM dual connect by level <=",MID(A2,5,3)-MID(A2,1,3),");")

This will produce insert statements that you can then run in Oracle.  They will look like this:

INSERT INTO t1 (SELECT 010+Level-1,'005','305' FROM dual connect by level <=31);


Unicode support of PostgreSQL and its performance How is the Unicode support of PostgreSQL?

If I want to save Unicode data does it have to go through the conversion process while storing and retrieving? If yes then what is the performance of such conversion?

If there is such a conversion, will it come into play when storing simple ASCII data?

Unicode encoding support in PostgreSQL is stellar and is implemented using the UTF-8 character encoding.

If the client uses a different encoding than the server, automatic character set conversion occurs, paying a negligible performance penalty.

ASCII data does not require conversion to the UTF-8 character encoding, so no performance penalty there.

Please refer to the PostgreSQL Localization documentation for further information.


Setting up an alert to monitor where/when your databases are backed up I was reading Who’s Backing Up That Database? by Kendra little, specifically the section, "IF THE BACKUP IS Succeeding, YOU CAN TRY ANOTHER TRICK".

I have been looking into monitoring my databases to send an email when the databases are backed up to another location outside of my backup path. So far what I have come up with has been is to set up a job that would fire off every 5 minutes which contained the following script:

BEGIN
IF (SELECT COUNT (bmf.physical_device_name)
FROM [msdb].[dbo].[backupset] bs inner join [msdb].[dbo].[backupmediafamily] bmf
on bs.media_set_id = bmf.media_set_id
where bs.[database_name] = 'logbackuptest'
and bmf.physical_device_name not like 'D:\backups\%') > 0

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBA Profile',
    @recipients = 'IT@ITCORP.com',
    @body = 'The database was backed up to a different location',
    @subject = 'A database was backed up to a different location' ;
END

Does anyone else have a good blog post or any other ideas? I'm not in a situation where backups are happening to non-default locations without my knowledge; the question is purely academic.

You can use a BackupRestore Event Notification to monitor backup events and this solution even works on Sql Express (where the Sql Agent doesn't exist). This link shows you how to set up SQL Mail for SQL Server Express.

I created a database called Test.

I modified this example from Jonathan Kehayias, which was tracking autogrowth, to instead audit the backup/restore event.

Here is my example that was tweaked from Jonathan's:

-- Using msdb prevents the need for certificate signing the
-- activation procedure to execute sp_send_dbmail across
-- databases
USE [msdb];
GO

-- Drop the notification if it exists
IF EXISTS (
        SELECT *
        FROM sys.server_event_notifications
        WHERE NAME = N'CaptureDatabaseBackupEvent'
        )
BEGIN
    DROP EVENT NOTIFICATION CaptureDatabaseBackupEvent ON SERVER;
END

-- Drop the route if it exists
IF EXISTS (
        SELECT *
        FROM sys.routes
        WHERE NAME = N'DatabaseBackupEventRoute'
        )
BEGIN
    DROP ROUTE DatabaseBackupEventRoute;
END

-- Drop the service if it exists
IF EXISTS (
        SELECT *
        FROM sys.services
        WHERE NAME = N'DatabaseBackupEventService'
        )
BEGIN
    DROP SERVICE DatabaseBackupEventService;
END

-- Drop the queue if it exists
IF EXISTS (
        SELECT *
        FROM sys.service_queues
        WHERE NAME = N'DatabaseBackupEventQueue'
        )
BEGIN
    DROP QUEUE DatabaseBackupEventQueue;
END

--  Create a service broker queue to hold the events
CREATE QUEUE [DatabaseBackupEventQueue]
    WITH STATUS = ON;
GO

--  Create a service broker service receive the events
CREATE SERVICE [DatabaseBackupEventService] ON QUEUE [DatabaseBackupEventQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

-- Create a service broker route to the service
CREATE ROUTE [DatabaseBackupEventRoute]
    WITH SERVICE_NAME = 'DatabaseBackupEventService'
        ,ADDRESS = 'LOCAL';
GO

-- Create the event notification to capture the events
CREATE EVENT NOTIFICATION [CaptureDatabaseBackupEvent] ON SERVER
    WITH FAN_IN
FOR AUDIT_BACKUP_RESTORE_EVENT TO SERVICE 'DatabaseBackupEventService'
    ,'current database';
GO

--  Alter the queue to use the activation procedure
ALTER QUEUE [DatabaseBackupEventQueue]
   WITH STATUS=ON,
      ACTIVATION
         (STATUS=ON,
          PROCEDURE_NAME = [ProcessDatabaseBackupEvents],
          MAX_QUEUE_READERS = 1,
          EXECUTE AS OWNER);
GO

I then tweaked his example stored procedure that consumes the event.

Make sure you change this line in the stored procedure to be your email address

@recipients = 'YourEmailAddress'

DROP PROCEDURE [dbo].[ProcessDatabaseBackupEvents];
GO

CREATE PROCEDURE [dbo].[ProcessDatabaseBackupEvents]
    WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML;
DECLARE @message_sequence_number INT;
DECLARE @dialog UNIQUEIDENTIFIER;
DECLARE @email_message NVARCHAR(MAX);

WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION;

    -- Receive the next available message FROM the queue
    WAITFOR (
            RECEIVE TOP (1) -- just handle one message at a time
            @message_body = CAST(message_body AS XML) FROM dbo.DatabaseBackupEventQueue
            )
        ,TIMEOUT 1000;-- if queue empty for 1 sec, give UPDATE AND GO away

    -- If we didn't get anything, bail out
    IF (@@ROWCOUNT = 0)
    BEGIN
        ROLLBACK TRANSACTION;

        BREAK;
    END

    DECLARE @EventType VARCHAR(128);
    DECLARE @ServerName VARCHAR(128);
    DECLARE @PostTime VARCHAR(128);
    DECLARE @DatabaseName VARCHAR(128);
    DECLARE @TextData VARCHAR(max);
    DECLARE @GrowthPages INT;

    SELECT @EventType = @message_body.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)')
        ,@TextData = @message_body.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)')
        ,@ServerName = @message_body.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)')
        ,@PostTime = CAST(@message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS VARCHAR)
        ,@DatabaseName = @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)');

    -- Generate formatted email message
    SELECT @email_message = 'The following DatabaseBackup event                                      occurred:' + CHAR(10) + CAST('ServerName: ' AS CHAR(25)) + @ServerName + CHAR(10) + CAST('PostTime: ' AS CHAR(25)) + @PostTime + CHAR(10) + CAST('DatabaseName: ' AS CHAR(25)) + @DatabaseName + @TextData

    -- Send email using Database Mail
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,
        -- your defined email profile
        @recipients = 'YourEmailAddress'
        ,-- your email
        @subject = 'DatabaseBackup Event Notification'
        ,@body = @email_message;

    --  Commit the transaction.  At any point before this, we could roll
    --  back. The received message would be back on the queue AND the
    --  response wouldn't be sent.
    COMMIT TRANSACTION;
END
GO

Then, I ran a simple backup

BACKUP DATABASE [Test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT
    ,INIT
    ,NAME = N'Test-Full Database Backup'
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10
GO

After a minute or so, I received the email with the event data in it. You can modify the event consuming stored procedure to capture whatever you need and send that information along in the email.


Is there a standard language/interface for programmatic ETL in SQL Server? I'm currently in the process of creating ETLs for our data warehouse. We're using SSIS 2008, but we're running into issues, the biggest of which is the difficulty in re-using components. We have separate packages for each table and each package takes as input a number of variables from a parent package. As we make changes to these input variables, we're required to go into each package (we have 15 or so now, but this number is going to grow significantly) and modify the package to deal with those changes. There are also other issues, including the inability to run arbitrary SQL for our extraction, poor logging capabilities, etc.

This entire process would be much more robust if there were a way develop our ETLs in code, enabling code reuse, common libraries, better unit tests, etc. Is there a de facto standard ETL language / API for SQL Server? I'm looking to avoid GUI tools as much as possible.

Edit: I should mention my background. I'm not a DBA and have no formal (or informal) DBA training, I've basically figured this stuff out as I went along, so there is every likelihood that I'm attempting to do inappropriate things with SSIS or approaching this ETL project from the wrong angle. Also, I'm currently employed in state government, so any solutions that require the purchase of a new software package aren't within the realm of possibility.

Here's one of our tasks. We are using a single SSIS Package to load each table in our warehouse. Each Fact package and Dimension package are generally the same, they only differ in

Extractions from the source database
Manipulations in a Data Flow
Merges into the destination table

What I would like to be able to do (that I'm finding to be difficult to do in SSIS)

Load the extraction query from a text file. When developers are writing and testing their extraction queries, I should not have to manipulate their query in any way before SSIS runs it and I should not have to cut and paste the query into a DB Source object.
Test each component individually. I should be able to test the complete ETL process for an individual table in isolation, independent of other table loads.
Make modifications to the shared logic in one place, not have to edit each individual package. Every package loads data into the audit tables the same way, if I want to change the data that is loaded audited, I don't want to have to edit all 15 packages (this number is going to get much much larger over time).

The entire process feels like it would be much easier to implement and more robust if done programatically with proper use of shared code.

There is a tool that enables this - http://www.varigence.com/products/biml.html

There's a commerical version, but we also include some of the BIML functionality in BIDS Helper, a free tool. http://bidshelper.codeplex.com/

I'm happy to answer any questions that you might have about it.

This is a tool that my company provides.


Select number of times 2 specific values show up for one record id I'm not sure my title above makes sense, so I'll try to clarify.  I'm using SQL Server.

I have a table called Orders that looks like this (truncated):

Order_Number | SKU
           1 | abc
           1 | def
           1 | ghi
           2 | abc
           2 | ghi
           3 | def
           4 | abc
           4 | def
           4 | ghi
           5 | abc

I need to find all Order Numbers that include SKUs abc and ghi.  So if I ran the query on the above table, it would return:

Order_Number
           1
           2
           4

If the Order Number only includes abc or ghi, not both, I don't want to return the Order Number.  If it includes extra SKUs, such as def, that is OK, I still want to return the record.  I don't need to see all the SKUs in the output, just the Order Number.  Does that make sense?

Thanks in advance!

You can do this in two different ways:

1) Use EXISTS

SELECT
    Order_Number
FROM
    (-- Get the list of Order_Number
    SELECT DISTINCT
        Order_Number
    FROM
        Orders
    ) AS onr
WHERE
    -- Check that there is an 'abc' associated
    EXISTS
    (
    SELECT *
    FROM Orders os1
    WHERE os1.Order_Number = onr.Order_Number and os1.SKU = 'abc'
    )
    AND
    -- and also Check that there is an 'ghi' associated
    EXISTS
    (
    SELECT *
    FROM Orders os2
    WHERE os2.Order_Number = onr.Order_Number and os2.SKU = 'ghi'
  )
ORDER BY
    onr.Order_Number
GO

| Order_Number |
| -----------: |
|            1 |
|            2 |
|            4 |

2) Substitute the EXISTS by equivalent JOINS with join condition including the desired value of SKU:

SELECT
    onr.Order_Number
FROM
    (SELECT DISTINCT
        Order_Number
    FROM
        Orders
    ) AS onr
    JOIN Orders AS sk1 ON sk1.Order_Number = onr.Order_Number AND sk1.SKU = 'abc'
    JOIN Orders AS sk2 ON sk2.Order_Number = onr.Order_Number AND sk2.SKU = 'ghi'
ORDER BY
    onr.Order_Number
GO

You get exactly the same result. Which version you use depends on which one you find clearer to understand. If your tables are really big, test whether one gives a better execution plan.

You can check all the statments at dbfiddle here


Inventory management in Rails I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot of types of paper, pens and other things, and every user may want a diferent pen, but a pen I think that it will be better to have a type of article and have in the Recipe table the Quantity of articles needed. So when an user checks his list of articles there will be Pens: 2  and maybe some options and a search for more info of an specific pen. Like a ToDo list. But I having trouble to figure out the relationships of the tables for this purpose. Right now im designing the database. I was thinking in use MySQL in Rails.

Try to put 'on paper' what you 'fields' that you need. Once you have that then you must group them based on what fields have a same identifier. In you case you might have 3 entities:

User with an ID and a name
Group with an ID and a name (with values like like pen and paper)
Material with an ID, the GroupID and a name

For the ToDo you can try yourself to figure it out.

Google on database normalisation to find some more information. You do not need to go down all the way but try to figure out the first 3 forms.


High write IOPS on MySQL instance I posted this first in the aws forums before i learned that there's a stackoverflow for DBs but i would ask this here as well.

We recently took over management of a wordpress site using a MySQL DB instance. a few days ago, it ran out of space which we found unusual because the site itself isn't a super busy site and it doesn't really have a lot of stuff in it when checked through wp-admin. To get the site to load, we increased the allocated storage from 185GB to 250GB. We then deleted the transients then optimized the tables and we got almost all the 250GB back. We then observed the monitoring windows in aws and we noticed that the free space was going down even if the site isn't that active at that time (loses around 2.5 GB every 24 hours). There are no running cron jobs since cron is disabled and checking show processlist isn't showing any running queries which we found strange.

We then realized that the write iops was pretty high at around 150-200 as reported through aws rds monitoring and is not going down considering that there's very little activity on the site. This was probably what caused the space to fill up before.

Going through some of the suggested links for this issue here in dba.stackexchange, i found this link which suggested to run the command SHOW ENGINE INNODB STATUS and i found this

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=3876, Main thread ID=47504970233600 , state=sleeping
Number of rows inserted 1695335, updated 24452885, deleted 684983, read 11973811927
0.00 inserts/s, 2.39 updates/s, 0.00 deletes/s, 586.80 reads/s

Which doesn't seem to be a great number of updates. We're not sure where to check next exactly. If it would help, the instance is a t3.large instance

Thanks

I learned how to enable general logging on the db and i found a possible issue with the wordpress theme being used. We will be replacing this soon anyway so hopefully there won't be any issues moving forward


Why is replication stopped? Got the following nagios alert this morning for our MySQL replica:

  14:49 PROBLEM: mysql2/MySQL replication check is CRITICAL, 10.1.0.133 is 72870 seconds behind, Aug 09, 21:49 UTC

When I look at SHOW SLAVE STATUS on the replica, the places I normally look at are fine:

Slave_IO_State: Waiting for master to send event
Seconds_Behind_Master: 73698
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_SQL_Errno: 0

Where else should I be looking to diagnose and fix replication?

Has anything been written to the Master in the last 72K seconds (about a day)?

Sometimes this helps:

STOP SLAVE;
START SLAVE;

I have seen the need for this on and off over the last decade; never figured out why it might be needed.

Another mystery is that the Seconds_behind_master sometimes mysteriously decides to flap between zero and some large value.  Eventually that problem clears itself up.


To trigger or not to trigger- That is the question I have a mysql table that tracks page views. Each row includes a date stamp. I want to remove all rows that are over 30 days old on a regular basis. Could, or should, I trigger an action on each insertion of a new row that would delete the rows that are over 30 days old? Or would I be better off adding a separate delete action along with each new insertion?

I don't think it's critical to have the old rows at the very moment they turn 30 days old - a few seconds/minutes/hours delay will probably not hurt you at all. Therefore I'd remove old rows with some scheduled task/query run at some fixed interval.

Otherwise you'll end up running additional query for every page view that you get.


Investigating an Oracle 12c outage after the fact, while limited to SQL*Plus Warning up front: unfortunately I'm limited to using SQL*Plus on remote site, with no other tools available. This is Oracle 12c (12.2.0.1.0) Standard Edition running on Windows Server 2012R2.

Over the weekend there was an outage where Oracle stopped responding for approx 15 minutes. Looking at application logs and Oracle's alert log, it was clear that something tied up Oracle until it hit an ORA-04031 e.g.

ORA-04031: unable to allocate 64 bytes of shared memory ("shared pool","unknown object","KGLH0^b3e62c9b","kkocsBeElem: kkoMergeBindEqvCtx")

My problem is that I don't know the cause of this issue. My question is, what tools are available to me at this stage 48 hours later, to figure out what happened? As mentioned I've been through the alert log, and the application logs.

You can inspect V$SQL for statements that don't use binds, eg.

select substr(sql_text,1,80), count(*)
   from v$sql
   group by substr(sql_text,1,80)
   order by count(*) desc

Then inspect the full text of the statements that come on top, see where they differ.


How do you manually change the 'log_reuse_wait' status in SQL Server? I'm in the process of moving several databases to a new server. All the databases have SIMPLE recovery mode, as they are backed up once/day.

It seems that the easiest way to move the databases is to just restore from the .bak files on the new server. I've tested that this works, aside from the fact the logs of the restored databases are locked waiting for a replication that no longer exists.

This query:

USE master;
GO

SELECT
    DB.name [DATABASE],
    DB.recovery_model_desc [RECOVERY MODE],
    DB.log_reuse_wait_desc [LOG WAITING ON],
    CAST(CAST(MF.size AS decimal) * 8 / 1024 / 1024 AS DECIMAL(10,2)) [FILE SIZE GB],
    CASE UPPER(REVERSE(SUBSTRING(REVERSE(MF.physical_name), 0, 4))) WHEN 'MDF' THEN 'DATA' WHEN 'LDF' THEN 'LOG' ELSE NULL END [FILE TYPE],
    UPPER(REVERSE(SUBSTRING(REVERSE(MF.physical_name), 0, 4))) [FILE EXTENSION]
FROM sys.databases DB
INNER JOIN sys.master_files MF ON MF.database_id = DB.database_id
ORDER BY
    [FILE TYPE] DESC,
    [FILE SIZE GB] DESC

produces LOG WAITING ON = REPLICATION for the restored databases.

Since the replication no longer exists, I can't just delete the publications on the new server.

Question. Do I need to delete the snapshot replication on the databases before moving them across? Or can I change this log_reuse_wait status to NOTHING manually?

Some more detail:

Server A has replication enabled. DB A.a is marked for snapshot replication. This rep is run occasionally. Database A.a is in SIMPLE recovery mode, with a full backup taken nightly.

Server B is new and doesn't have replication enabled yet. I have found when I restore A.a to B.a, the restoration works. But the log file is still being held for replication.

I would like the log file on B.a to NOT be marked for replication...

On the Q & A Transaction Log won't shrink, DB thinks it is replicating I found the answer by Matthias Elflein to be the most correct, though first I had to enable a distributor for it to work (I just enabled the default local distributor and deleted it afterwards).

Just in case that answer disappears for some reason, the code to remove a publication from restored database is:

USE DBname
GO
-- 1.) enable publication for MyDatabase
EXEC sp_replicationdboption
  @dbname = 'DBname',
  @optname = N'publish',
  @value = N'true';
GO
-- 2.) remove publication from database. Use the PUBLICATION-name (not database name)
sp_removedbreplication 'Publ_DBname','both'

-- 3.) disable publication for MyDatabase
EXEC sp_replicationdboption
  @dbname = 'DBname',
  @optname = N'publish',
  @value = N'false';
GO

I had to create a distribution database on server B before running the code above otherwise the query errored with the following message:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 76
The Distributor has not been installed correctly. Could not enable database for publishing.
The replication option 'publish' of database 'DBname' has been set to false.
The replication option 'publish' of database 'DBname' has been set to false.

The above error only applied to SQL Server 2012. I found that when restoring these databases to SQL Server 2016 that there was no log wait status (since replication didn't seem to be installed on the machine).


Data Backup and Restoring the same if requried in SQL SERVER 2008 In Multi-tenancy , Shared Database Shared Schema.
How to take Tenant level backup and restored the tenant data when required? Can any one guide me or point me to the right direction.

Thanks!!!

NB: Database : Microsoft SQL SERVER 2008

You can't without export/import.

A single, shared database implies all data is linked and requires full transactional/referential integrity. So the standard backup/restore won't work because you can't cleanly (or at all?) do row-level partial actions.

You're export/import needs to be transactional safe too, and foreign keys need considered

To make things simpler, you really need a database per tenant.
See Decision criteria on when to use a non-dbo schema vs a new Database for more discussion
If you can't, then you have to live to export/import


how do I work out why my stored procedure won't compile? I have a stored procedure whose sql file is nearly 6000 lines in length.

It's part of a series of DB build scripts which are used in lots of environments to build a standard DB setup.

When I attempt to run it in one environment, it doesn't compile.

show errors;

gives me the following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5552/10  PL/SQL: SQL Statement ignored
5552/22  PL/SQL: ORA-00942: table or view does not exist
5556/10  PL/SQL: SQL Statement ignored
5556/24  PL/SQL: ORA-00942: table or view does not exist
5566/12  PL/SQL: SQL Statement ignored
5566/26  PL/SQL: ORA-00942: table or view does not exist

In order to work out what's wrong, I need to figure out which table or view it can't see.  However, line 5552 of my sql file is empty, and line 5566 has no table name on it.

How can I figure out which tables it can't see?

The database provided you the exact location of the error. All you need to do is check what is there, in the database, and not in your file.

select text from all_source where name = 'PROCEDURE_NAME' and line = 5552;
select text from all_source where name = 'PROCEDURE_NAME' and line = 5556;


User databases inaccessible after physical restart We have an unusual error happening on the SQL Server.

After restarting the physical server where the SQL Server instance is installed, the user databases are not accessible. System databases are accessible.

Please see screenshot (After right clicking on DB and choosing properties) as example and error at bottom of the page.

I did find this note, however the owner field was already populated.
http://blogs.msdn.com/b/suhde/archive/2009/04/05/property-owner-is-not-available-for-database-dbname.aspx

However, the most interesting part is that when I restart the SQL Server instance, the user databases become accessible and no error is returned anymore. Everything functions as normal. This is done by logging into SSMS as 'sa' user.

I have tried to add additional permissions to the MDF, LDF and database location files, but haven't had much luck.
I will mention that the User Databases (D:\Data | D:\Logs) are in a different location to the System Databases (C:\ Default Location), although on the same server/network.

The SQL Server (MSSQLSERVER) service logs on as NT Service\MSSQLSERVER.

SQL Server version is:

  Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  Feb 10 2012 19:39:15

  Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on

  Windows NT 6.2  (Build 9200: ) (Hypervisor)

Error:

  Property Size is not available for Database '[xxxxx]'. This property
  may not exist for this object, or may not be retrievable due to
  insufficient access rights.  (Microsoft.SqlServer.Smo)

It sounds to me as if the D: drive isn't accessible when the SQL service starts. Consider telling the service to start on a delayed start, or create a process which checks for a file on D: before doing a "net start mssqlserver".


PhpMyAdmin Import Error-MySQL server has gone away/Unrecognized Keyword So I've exported a MySQL database from PHPMyAdmin (which I access from within IIS Express on a Windows 8 machine) and I'm trying to import this database onto another machine, however on that machine I receive the following errors:

Please help. I need to be able to restore this database and I have no idea how to fix this.

I tried going into the sql file and changing the foreign key line in the sql file (it was set to 0), but that didn't make a difference. Where is it set to ON? I can't figure that out.

Changing the MySQL settings worked. This is what I changed, exactly:

In php.ini

Find:

post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
max_input_time = 60
memory_limit = 8M

Change to:

post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 5000
max_input_time = 5000
memory_limit = 1000M

In my.ini (MySQL File)

Under mysqld add: max_allowed_packet = 500M


Does changing the length limit (type-modifier) of varchar() result in a table or index rewrite? Following on the heels of this question does changing the length limit (type-modifier) of varchar() result in a table rewrite or a lock that takes more time than changing the CHECK constraint? From that question you can see that claim by Brandur,

  If you ever want to change the length, ALTER TABLE requires an exclusive lock (see https://www.postgresql.org/docs/current/static/sql-altertable.html …). Changing CHECK is instant. When answering a question that called into question text CHECK (char_length(email) <= 255) vs varchar(255)

It seems this may have originated with a post by Depesz, in his post from 2010, "CHAR(X) VS. VARCHAR(X) VS. VARCHAR VS. TEXT – UPDATED 2010-03-03"

  So, what happens with when you make the limit larger [with varchar]?

  PostgreSQL has to rewrite the table. Which has 2 very important drawbacks:
  1. Requires exclusive lock on the table for the time of operation
  2. In case of non-trivial tables, will take considerable amount of time

You can see this again in a comment here (2017),

  Still, I'd take a sub millisecond diff on every INSERT over a possible table lock when I'll want to convert my VARCHAR(50) to VARCHAR(250).

And again in here (2012),

  As an aside: I never use varchar if I can avoid it - especially not with length modifier. It offers hardly anything that the type text couldn't do. If I need a length restriction, I use a column constraint which can be changed without rewriting the whole table.

And clearly there are others that doubt the claim.

It may be worth just addressing this single claim.

Essentially, this is out of date information. It hasn't been relevant since 9.2. Now, the only drawback, that I can see, is that the index gets rewritten if the length constraint gets more restrictive and it has to be rechecked.

In the release notes for 9.1, as discovered by Erwin it states

    Allow ALTER TABLE ... SET DATA TYPE to avoid table rewrites in appropriate cases (Noah Misch, Robert Haas)

    For example, converting a varchar column to text no longer requires a rewrite of the table. However, increasing the length constraint on a varchar column still requires a table rewrite.

In the release notes in  9.2, as discovered by @a_horse_with_no_name found,

  Reduce need to rebuild tables and indexes for certain ALTER TABLE ... ALTER COLUMN TYPE operations (Noah Misch)

  Increasing the length limit for a varchar or varbit column, or removing the limit altogether, no longer requires a table rewrite. Similarly, increasing the allowable precision of a numeric column, or changing a column from constrained numeric to unconstrained numeric, no longer requires a table rewrite. Table rewrites are also avoided in similar cases involving the interval, timestamp, and timestamptz types.

The docs on ALTER have this to say

  Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt. Adding or removing a system oid column also requires rewriting the entire table. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

Following with the test case provided by a_horse_with_no_name that I modified, let's see this in action.

\timing 1

CREATE TABLE alter_test (id int primary key, some_data varchar(50));
INSERT INTO alter_test
  SELECT i, md5(i::text)
  FROM generate_series(1,1e7) AS gs(i);

ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(55);
Time: 5.671 ms

So we have no slow down without an index. Then we add an index, and try it again,

CREATE INDEX ON alter_test (some_data);
ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(55);
Time: 6.423 ms

I tried VACUUM FULL ANALYZE on the table and making the length on varchar again longer, and it still didn't take any more time. Not saying it's well debunked in all cases, but at least in the simple cases even when indexed this seems to be not-a-concern if you're going to make the constraint less restrictive. However, making the length constraint more restrictive seems to be doing something.

ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(50);
ALTER TABLE
Time: 59690.885 ms

Dropping the index and trying that again is substantially faster,

DROP INDEX alter_test_some_data_idx ;
DROP INDEX
Time: 85.978 ms
test=# ALTER TABLE alter_test
  ALTER COLUMN some_data
  TYPE varchar(49);
ALTER TABLE
Time: 9297.271 ms

So it seems only if the length constraint is made more restrictive and has to be revalidated does the index get rewritten.


What is different between fixed-size disk pages and disk-based? As per my knowledge, i understand fixed page size as  size of page which used for read and write data but i am not able to understand what does mean of disk based ?

I read these words from this paper which compared about sql and noSQL

http://www.labouseur.com/courses/db/Stonebraker-SQL-vs-NoSQL-2010.pdf

What Stonebraker is talking about here is essentially his latest baby, VoltDB. This is what Stonebraker (a huge hitter in the database world) calls "NewSQL".

He says that OldSQL (The Oracles, MySQLs, SQL Servers &c. of this world) waste too much of their resources Logging, Latching, Locking and on Buffer Management. These result (largely) from the fact that the data moves back and forth between disk and memory as transactions are processed.

He also says that NoSQL throws the baby out with the bathwater in that it gets rid of something he regards as critical for any database system worthy of the name, i.e. ACID transactions.

He sees VoltDB (and similar NewSQL) as the future of OLTP database systems - this is an in-memory shared-nothing system where transactions are in the form of app-specific customised stored procedures which always run to completion. This architecture eliminates all the logging, latching, locking and buffer management that Stonebraker says is essentially useless work. He famously said that OldSQL should go to the home for "tired software"! You might want to check out this paper. He sees the future of OLAP systems as columnar stores - and was a founder of Vertica (subsequently sold to HP).

Finally, now the background is out of the way, to address your specific question. He is saying that because many of the NoSQL architectures are disk-based (i.e. they don't run in memory), these much-vaunted systems will in fact suffer from the same disadvantages as OldSQL (which they were meant to replace), and so not only will they not be faster, but they won't be consistent either - a double-whammy loss as far as Stonebraker is concerned.

Google such terms as "VoltDB architecture", "NewSQL" and "columnar store". That should give you a few leads in this (very interesting) area.


Improving the query standards and performance run time For one of our Vendor based SQL server database, they have created certain views for users to query. Since i am new to SQL querying, please help me understand the below format of SQL and how can i tune it up further as it runs over hours. I will try to get a repro, but for now, let me know if below can help:-

Query

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM
       TABLE1,TABLE2,TABLE3

WHERE TABLE2.ID=TABLE1.ID
AND   TABLE2.COLUMN1='Data'
AND   TABLE3.ID=TABLE1.ID
AND   TABLE3.COLUMN2='Value'
AND   TABLE1.COL1=CAST('20190914' as DATE)
AND   DATEPART(hour,TABLE1.COL1) BETWEEEN 1 AND 2

For Table1 there is no CI, we just have Unique NON CI as PK

TABLE 2 and TABLE 3 also does not have any CI, they both have Unique non CI on both ID and datetime column
TABLE1.COL1 is a datetime column

Above query is run multiple times in 12 batches a day for different date range in between clause to collect data between 24 hours, like 0-1, 1-2, 2-3 , 3-4 .... 21-22,23-24

Is there a better way to perform above run?

Thanks

Clustered Index is must.Unless we do not have table structure and their description we cannot say which column should be CI.

Provide volume of data in each table and expected volume of output.

DATEPART(hour,TABLE1.COL1) BETWEEEN 1 AND 2 is not SARGAble.

So you can rewrite the query as below.Always check the performance with parameter instead of HARD coded value.

I mean that in real life scenario, we use local variable or Proc's parameter.So test query performance using using same.
Check that Parameter Sniffing problem is tolerable or not and how it can be minimized.If Parameter Sniffing is tolerable then ignore it.
Option Recompile should be rarely use .

declare @FromDate Datetime=cast(getdate() as date)
declare @FromDatetime=dateadd(hour,1,@FromDate)
declare @ToDatetime=dateadd(hour,2,@FromDate)

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM
       TABLE1 T1
     inner join  TABLE2 T2 on T2.ID=T1.ID
     inner join  TABLE3 T3 on  T3.ID=T1.ID -- try once T3.ID=T2.ID
WHERE
   T2.COLUMN1='Data'
AND   T3.COLUMN2='Value'
AND   T1.COL1>=@FromDatetime
AND   T1.COL1<=@ToDatetime

Alternatively,

It appear that you don't need Table2 and Table3 column in your result set,

So you can rewrite in this manner,

SELECT TABLE1.ID   ABC
       TABLE1.COL1 XYZ
       TABLE1.COL2 ASD
       TABLE1.COL3
       TABLE1.COL4
FROM dbo.TABLE1 T1
WHERE
exists(select 1 from dbo.TABLE2 T2
     inner join  dbo.TABLE3 T3 on T3.ID=T2.ID
     where T3.ID=T1.ID and T2.COLUMN1='Data' AND   T3.COLUMN2='Value')
AND   T1.COL1>=@FromDatetime
AND   T1.COL1<=@ToDatetime


truncate table with foreign keys I have to truncate a table which gave me the below error.

  ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

I fixed that by checking the below,

SELECT p.table_name "Parent Table", c.table_name "Child Table",
    p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
    FROM dba_constraints p
    JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
    WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
    AND c.constraint_type = 'R' AND p.table_name = UPPER('TABLE_NAME')
    AND p.owner='SCHEMA_NAME'
/

alter table schema.table_name disable constraint SYS_constraint_name;

truncated the table.

Now I am trying to enable the constraint but getting the below,

enable constraint ora-02298 cannot validate - parent keys not found

So I use the below query to identify the records in child table,

SELECT DISTINCT column_name FROM schema.table_name2 WHERE column_name NOT IN
(
SELECT column_name FROM schema.table_name1
)

I am unable to delete the above as they have child tables which are acting as foreign keys.

Now my question - instead of going through loops deleting and disabling keys. How can I truncate a given table and not disable constraints

Oracle 12c introduced TRUNCATE ... CASCADE:

TRUNCATE TABLE

  CASCADE

  If you specify CASCADE, then Oracle Database truncates all child
  tables that reference table with an enabled ON DELETE CASCADE
  referential constraint. This is a recursive operation that will
  truncate all child tables, granchild tables, and so on, using the
  specified options.

Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.

In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.


Partitioning MySQL vs PostgreSQL For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which partitioning fits better our needs.
Our case
The IS is backend for selling tickets. We sell thousands of tickets daily so the table scales quite fast and seat reservations scale even faster.
+--------------+            +-------------------+
| tickets      |    1:N     | seat_reservations |
+--------------+            +-------------------+

We want to use date partitioning for both of these tables and some others but this is the core.
We need to force preserve db integrity via foreign keys or some similar solution. I know that InnoDB doesn't support FK with partitioning.
The questions

Does PostgreSQL support FK and Partitioning? Probably for rules based partitioning.
Is another way how to force preserve DB integrity?
Which SQL server do you recomend? (based on partitioning)

I plan to partition both tables. I want partition even more than that (log,...) but these are the main tables.
We are just building the system so we want to use the newest versions; MySQL 5.6 or PostgreSQL 9.3. The r/w ratio will be about 3:1 (r:w), and the line count will be 700k growing about 100k per month so we have to be able to have more than 7m lines.

Ok, PostgreSQL does not directly support foreign keys and partitions. You can create a foreign key on each partition, but there is no way to have a foreign key against a set of partitions without either coding the constraint triggers yourself or using a key management table.

There are several ways to resolve this, but they require some understanding of underlying db implementation concepts to make perform well.

The first is to do your referential integrity enforcement manually through triggers.  You can write constraint triggers which can be deferred (if needed) that check the existence of foreign key information.  Note this is subject to ALTER TABLE DISABLE TRIGGER ALL issues but it should generally work.

The second is to have a separate table which stores keys and is maintained through a combination of triggers and bulk data operations.  This can help a great deal.

So it is possible to enforce things but it requires a bit more work.

I don't know enough about MySQL's table partitioning capabilities to comment on it though.


SQL Server 2005 Restore Backup Status Recovery Pending So at work something dreadful happened a database server went down and I am having an issue recovering one of our databases from our backups.  When I try to restore from a backup using SQL Management Studio the backup proceeds to 100% then fails at the end with the following:

An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8

Error Number: 5243
Severity: 22
State: 1
Line Number: 1

There were some articles stating that a hotfix or servicepack update solves issues similar to this but it did not in my case.  From what I've read, error 5243 and 5242 usually mean possible corruption.  I ran:

RESTORE VERIFYONLY
FROM DISK=N'C:\DataBaseName.bak'
WITH CHECKSUM;

and it indicated that no checksums were in use so I then ran:

RESTORE VERIFYONLY
FROM DISK=N'C:\DataBaseName.bak'

and it completed successfully with:

Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
Directory lookup for the file "M:\Data\DataBaseName.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Directory lookup for the file "N:\Logs\DataBaseName_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.

Sense no checksums are used this only checks the header, this I know.  Now I try to force the restore to complete using CONTINUE_AFTER_ERROR:

RESTORE DATABASE DataBaseName
FROM DISK=N'C:\DataBaseName.bak'
WITH CONTINUE_AFTER_ERROR;

This completes successfully with:

Processed 1453208 pages for database 'DataBaseName', file 'DEFAULTDB' on file 1.
Processed 3 pages for database 'DataBaseName', file 'DEFAULTDB_log' on file 1.
Restore was successful but deferred transactions remain. These transactions can not be resolved because there are data that is unavailable. Either use RESTORE to make that data available or drop the filegroups if you never need this data again. Dropping the filegroup results in a defunct filegroup.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 1453211 pages in 457.277 seconds (26.033 MB/sec).

Here is where things get frustrating :) At this point the status of the database is "Recovery Pending".  I have checked this by using this command:

SELECT
    [state_desc]
FROM
    sys.databases
WHERE
    [name] = N'DataBaseName';

I can take the database off line using

ALTER DATABASE DataBaseName SET OFFLINE;

and the status shows offline. I then try bringing it back online with

ALTER DATABASE DataBaseName SET ONLINE;

and it fails with:

Msg 945, Level 14, State 2, Line 1
Database 'DataBaseName' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5243, Level 22, State 1, Line 1
An inconsistency was detected during an internal operation. Please contact technical support. Reference number 8.

but when I check the state it is in "Pending Restore"

I then issue

ALTER DATABASE DataBaseName SET SINGLE_USER;

and this completes sucessfully.  I then issue

ALTER DATABASE DataBaseName SET EMERGENCY;

and this completes sucessfully...good right? I then try

USE DataBaseName

and recieve, Msg 922, Level 14, State 1, Line 1
Database 'DataBaseName' is being recovered. Waiting until recovery is finished.

I am pretty much stuck here.  If I run

DBCC CHECKDB (DataBaseTable, REPAIR_ALLOW_DATA_LOSS)

and still recieve:

Msg 922, Level 14, State 1, Line 1
Database 'DataBaseName' is being recovered. Waiting until recovery is finished.

I can't seem to get any further than this.  Can anyone give me their two cents?  Thanks a ton! I definitely know I am going to learn something here.

You can restore the database files to other locations using T-SQL commands to do the restore.

I did a test, something like:

CREATE DATABASE TestDB
ON (FILENAME='C:\test\TestDB.mdf', NAME='TestDB')
LOG ON (FILENAME='C:\test\TestDBLog.ldf', NAME='TestLog');
GO

BACKUP DATABASE TestDB TO DISK='C:\Test\TestDB.BAK';
GO

DROP DATABASE TestDB;
GO

RESTORE DATABASE TestDB FROM DISK='C:\Test\TestDB.BAK'
WITH MOVE 'TestDB' TO 'C:\SomeOtherFolder\TestDB.mdf'
    , MOVE 'TestLog' TO 'C:\SomeOtherFolder\TestLog.ldf'
    , RECOVERY;

This creates a database in one folder, takes a backup of the database, drops the database, then restores it from backup into a new folder.  You should be able to modify the RESTORE DATABASE command to suit your needs.


Ruby on Rails cannot access production RDS on AWS I have done rake db:create sucessfully. But rails db:migrate has access errors on the AWS RDS:
Mysql2::Error: Can't create table `llpdn9i88kz3y837`.`comments` (errno: 150 "Foreign key constraint is incorrectly formed")

and
SHOW ENGINE INNODB STATUS;

Error: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
SQLState:  42000
ErrorCode: 1227

and
GRANT PROCESS ON llpdn9i88kz3y837.* TO 'n8p1kzg1v9chfcny'@'localhost';

Error: Access denied for user 'n8p1kzg1v9chfcny'@'%' (using password: YES)
SQLState:  28000
ErrorCode: 1045

The @'localhost' bit is certainly an issue as you’re connecting to a remote RDS instance, not one located in the same instance as your Rails code.
Instead you can do something like this:
GRANT PROCESS ON llpdn9i88kz3y837.* TO 'n8p1kzg1v9chfcny'@'%';

However, depending on what the Rails code may be doing during the setup phase, you may want to consider granting all privileges:
GRANT ALL ON llpdn9i88kz3y837.* TO 'n8p1kzg1v9chfcny'@'%';

After the system is installed and running, then you can reduce the privileges to just what is needed.


Can I use an index to optimize select clause, versus where clause? I'm not sure if this question has been answered, because I'm not sure of the proper wording to describe what I need. So here it is:

I am trying to optimize a database that is running very slowly, hinged on one central table, with 128k records (set to accept about 10x that many in the next couple months), and 24 columns. 90+% of the time a query only needs to return a particular subset of 4 of those columns, and that query needs to be as fast as possible, because it happens constantly.

My understanding is that given enough RAM (our db server has plenty of extra right now), indexes all will be kept in RAM, and any query that only requires fields present in the index will be able to complete without performing a disk read at all, thereby speeding the query significantly.

Is this assumption accurate? And if so, must they all be in the same multi-column index in order for that to work, or would separate per-column indices work just as well? Is there a different method I should be using, since I really want to optimize the selecting and not the filtering part of the query?

The 4 crucial columns are id, person_type, person_id (a foreign key, used in conjunction with person_type), and full_name. The typical use cases are:

The where clause only specifies one or more values for id.
The where clause specifies person_id then person_type.
The where clause specifies person_type then a LIKE condition for full_name.

In each of the above cases, all 4 columns are specified in the SELECT clause.
A few sample queries:

SELECT contacts.full_name, contacts.person_type, contacts.person_id
FROM `contacts`
WHERE `contacts`.`person_type` = 'Usage::User'
AND `contacts`.`person_id` IN (388,389)

SELECT `contacts`.* FROM `contacts`
WHERE (person_type="Usage::Profile" AND full_name LIKE "%Foo%")

SELECT `crm_connections`.*, `contacts`.`full_name`, `contacts`.`person_type`, `contacts`.`person_id`
FROM `crm_connections`
INNER JOIN `contacts` ON `contacts`.`person_id` = `crm_connections`.`id`
AND `contacts`.`person_type` = 'Crm::Connection'
WHERE `crm_connections`.`active` = 1
AND (contacts.full_name LIKE '%Foo%')
LIMIT 1

Query 1:

INDEX(person_type, person_id) -- in that order
INDEX(person_type, person_id, full_name) -- to be "covering"

Query 2 ("covering" is not practical because of *):

INDEX(person_type, full_name) -- in that order

Query 3:  Before making suggestions here, please explain why you have a LIMIT without an ORDER BY.

See also my cookbook on making indexes.


Inserting into subtype table values from supertype table in oracle I had created two object types and tables based on that types in oracle:

create or replace type TEMPLOYE as object(
num_emp integer,
nom_emp varchar2(40),
prenom_emp varchar2(50),
adresse_emp varchar2(200),
tel_emp varchar2(15)
) not final;
/
create or replace type TMEDECIN under TEmploye (
num_med integer,
specialite Varchar2(30)
);
/
CREATE TABLE EMPLOYEES OF TEMPLOYE;
CREATE TABLE MEDECINS OF TMEDECIN;

and I had inserted some values into EMPLOYEES like :

INSERT INTO EMPLOYEES VALUES (
TEMPLOYE (10,'BOUCHEMLA','Elias','6, hai sidi serhane -Khemis El Khechna-Boumerdes',024873549));

Finally I need to insert a record into MEDECINS table with num_med=10 and specialite='Cardiologue' and the others attributes from the parent table (EMPLOYEES), how to do that using select * statement or a reference?

Simply INSERT ... SELECT.

insert into
  medecins
select
  e.*,
  10,
  'Cardiologue'
from
  employees e
where
  e.num_emp = 10
;


One other way of doing this would be to aggregate the status column by ip_addr, where the total is equal to zero.

The test-bed:

CREATE TABLE `ports`
(
    `id` INT NOT NULL
    , `ip_add` VARCHAR(15) NOT NULL
    , `port` INT NOT NULL
    , `status` BIT NOT NULL
    , `probe_meta` DATETIME
);

INSERT INTO `ports`
VALUES (1, '192.168.0.1', 22, 1, '2016-03-21 00:00:00');

INSERT INTO `ports`
VALUES (1, '192.168.0.2', 22, 0, '2016-03-21 00:01:00');

INSERT INTO `ports`
VALUES (1, '192.168.0.3', 22, 1, '2016-03-21 00:02:00');

INSERT INTO `ports`
VALUES (1, '192.168.0.3', 22, 0, '2016-03-21 00:03:00');

The query:

SELECT ip_add
FROM `ports`
GROUP BY ip_add
HAVING SUM(status) = 0;

The results:

SQLFiddle


Does openquery use distributed transactions? Does openquery in SQL Server 2005 use distributed transactions? If so, in which cases does it?

It can use distributed transactions if the OLE-DB provider supports it. Otherwise it tries to limit the commands to read-only operations.

Reference: http://msdn.microsoft.com/en-us/library/ms177403%28v=sql.90%29.aspx


Cross Database Queries using IBM Data Studio I'm currently using IBM Data Studio 4.1 to run queries against DB2 LUW database(s).
My understanding is that I can do cross database queries by prefixing the database(s) names.
For example, database.schema.table.
However, when I open a SQL Query window, there is a dropdown that requires the user to select the database connection. Since it only allows one database connection, how do we run cross database queries in IBM Data Studio?

For you to execute any SQL statement you have to be connected to some database server; Data Studio does not execute SQL statements. Subsequently, for the three part name (<server>.<schema>.<object>) to work the server you're connected to has to know what the <server> part is.
In the simple case of accessing objects in a database that belongs to the same Db2 for LUW database instance, <server> is the other database name, and no additional setup is required.
However, if the other table is in a database managed by a different instance, or if it belongs to a different DBMS (Db2 for z/OS, Oracle, etc.), you will need to set up a federated data source, whose name you will then use for <server>.