How to create Combination of records (Order does not matter, no repetition allowed) in mySQL tables I've got a table that has hundreds of rows, each row is a recipe with nutritional information, for example:

recipe_table:

id  | calories | protein| carbs | fat

recipe1, 100,    20g,     10g,     2g
recipe2, 110,    10g,     12g,     12g
recipe3, 240,    20g,     1g,      23g
....

I needed to create a new table (recipe_index) that would show every possible combination of every recipe in recipe_table as a set of 3, so it would look something like:

recipe_index:

id1     | id2    | id3    |calories| protein | carbs | fat
recipe1, recipe2, recipe3,   450,     50g,      23g,   37g
....

Basically it allows me to query recipe_index and say "what 3 recipe combinations come to a total value that's between 440 calories and 460 calories"

My current code for doing this works at 3 meals, however I end up with about 450,000 records in recipe_index, I need to do this same thing for 4,5 and 6 meals as well, so I'm calculating billions of records at the end of this. Is there a more efficient way of doing this? Perhaps I need to look into partitioning a table for each range?

My current SQL code:

INSERT INTO recipe_index
SELECT distinct '3' as nummeals, t1.id as id1, t2.id as id2, t3.id as id3, 0 as id4,
t1.calories_ps+t2.calories_ps+t3.calories_ps as calories,
t1.protein_ps+t2.protein_ps+t3.protein_ps as
protein, t1.carbohydrate_ps+t2.carbohydrate_ps+t3.carbohydrate_ps as carbohydrate,
t1.fat_ps+t2.fat_ps+t3.fat_ps as fat from recipes t1 inner join  recipes t2  on t1.Id <
t2.Id inner join  recipes t3  on t2.Id < t3.Id WHERE t1.image <> '' AND t2.image <> ''
AND t3.image <> ''

Like Mike says, this is not something that you should be doing with the database. This is more of an algorithm question.

Your problem looks very similar to the knapsack problem, which is NP-Complete. Fortunately, you are not looking for the set that adds up to exactly some amount of calories, but a range, so it's not NP-Complete. Luckily for you, a few hundred recipes will easily fit in memory, so you can easily write an algorithm for it.

Here's a simple algorithm. I'm sure there's a better way to do it:

orderedRecipes = recipes sorted by calorie asc

def combinations(recipes, lowerBound, upperBound, recipeCount):
    first = recipes[0]
    if first.calorie < upperBound:
        for c in combinations(recipes[1:], lowerBound - first.calorie, upperBound - first.calorie, recipeCount - 1):
            if sum(r.calorie for r in [first] + c) > lowerBound:
                yield [first] + c
        for c in combinations(recipes[1:], lowerBound, upperBound, recipeCount):
            if sum(r.calorie for r in c) > lowerBound:
                yield c

combinationsOf3Between440and460 = list(combinations(orderedRecipes, 440, 460, 3))

The first loop takes the first item in the rest of the list, and the second loop does not. You don't want it to always take the first item, you want both take and no-take.


Is there any other way that we can take auto backup in oracle 11g apart from RMAN? I have tried with RMAN. But is there any other possible way that we can backup database and tables. backup using queries or stored procedures is possible?

The only correct way to back up an Oracle database is to use RMAN. There are alternatives but they are not as reliable as RMAN.

RMAN has a ton of features and this might make it look complicated. But in fact it's not difficult and should always be the choice to back up a database.

RMAN does not do logical backups like tables, indexes, procedures. RMAN does physical backups which means that it backs up the datafiles block wise. It does not care what is inside this block.

I think there are a lot of beginner tutorials on the internet. I personally like the descriptions on http://oracle-base.com/.

To automatically back up a database use the operating system scheduler and run a shell or batch script. This script should do the following tasks:

call RMAN an execute an RMAN script

backup database
backup archivelogs
delete archivelogs

delete old backups (with RMAN commands)
send the logfile to the admin

Some people additionally run an export with datapump after the daily RMAN backup. This can be useful sometimes. But I never use this option. And it should never replace an RMAN backup!


Postgres corrupted DB recovery Is there a way to recover data from partially damaged Postgres DB? My HDD was damaged in transport, I was able to recover most of the files but I cannot connect to the DB or use dump: I'm getting error:

psql: FATAL:  could not read block 0 in file "base/18819/11700": read only 0 of 8192 bytes.

This is one of the missing files though most of them are fine. Is there a way?

Thanks

There is a special option: zero_damaged_pages=on that you can use on postgresql.conf, it is documented here.

This option will allow for a pg_dump (or pg_dump_all) that do not stop on critical errors and get as much data back as possible, but you will loose the data that cannot be read.:

(exceprt from documentation, I added the strong.

  Detection of a damaged page header normally causes PostgreSQL to
  report an error, aborting the current transaction. Setting
  zero_damaged_pages to on causes the system to instead report a
  warning, zero out the damaged page in memory, and continue processing.
  This behavior will destroy data, namely all the rows on the damaged
  page. However, it does allow you to get past the error and retrieve
  rows from any undamaged pages that might be present in the table. It
  is useful for recovering data if corruption has occurred due to a
  hardware or software error. You should generally not set this on until
  you have given up hope of recovering data from the damaged pages of a
  table. Zeroed-out pages are not forced to disk so it is recommended to
  recreate the table or the index before turning this parameter off
  again. The default setting is off, and it can only be changed by a
  superuser.


Foreign Key Settings I will start by mentioning that I am very new to databases. I am working in MySQL.
Can anyone give me a layman's view of when you would use which settings for the foreign key options so when you would use On Delete: Cascade, On Delete: Set Null, On Delete: No Action. Also, how would these work in the sense of foreign keys in a bridging table, does it depend on the context of the database as in what data it contains or is there a general rule of thumb as to which setting is best in this type of relationship?
If you can, please state it simply as I am not totally au fait with the jargon given I am a relative newbie. Thanks so much

It is possible to use databases without knowing anything about FOREIGN KEYs and not using them at all.  I recommend you start that way.
A FOREIGN KEY does three things:

It is a constraint.  It will slap your hand if you try to link from one table to another without there being an appropriate row.  Think of it like filling out a form that calls for your home address, but you mis-type it and there is no house at the address you typed.  If you never make typos, you don't need that check.
It provides an index (aka "key") to make the above check.  But you can create indexes as needed without having FKs.  And if your tables are only a few hundreds of rows each, you might not notice that you are missing the significant performance benefits of indexes.  An index is like a phonebook.  (If you are too young to know what a phonebook is, visit a museum.)
It can "cascade" things.  Don't use that; just do it yourself.  I'm referring to the deletion of a row in one table automatically causing the deletion of a corresponding row in another table.  Analogy:  When a person dies, the DMV, the voter registration, the bank, etc, need to be notified manually so they can delete that person from their lists.  FKs facilitate having that happen automatically.

Since you mentioned a bridging table, I'll ramble on in that direction.  (It sounds like you may have already mastered it.)  There are 3 types of "relations" between "entities":

1:1 -- Don't use that.  Why bother having two tables that march in lock step when one would do.  (There exceptions; but that comes in another lecture.)

1:many -- This is simply implemented with a link ("id") in one table to allow "JOINing" to another table.  Many rows of the first table link to a single row in the second table.

many:many -- This has many names, "bridging" on one of them.  In the college example:  students:classes, classes:teachers, etc.  Bridging requires an extra table.  See this for the optimal indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Any of those mappings can degenerate to 1 or to 0.
Rambling on...
SELECT ... FROM tableA JOIN tableB ON ...

delivers rows that show up in both tables (based on the ON condition).
SELECT ... FROM tableA LEFT JOIN tableB ON ...

delivers all the rows of table A, whether are not there is a matching row in table B.  If none exists, then the columns that should have come from tableB will be NULLs.
The ON says how the tables are 'related'.
The WHERE clause, if present, filters out some of the rows.


SSRS Reports Manager Security Configuration I happen to be setting up a reporting server at work from scrach. Having very little almost no experience of sql server administration I am finding it really difficult to configure the security settings for the Reports Manager.
I do understand all the users who will be accessing Reports Manager will need permissions on Two Levels

1 – Permission to Access Data from sql server. (for which after some research I have decided to make use of datareader role.
2- Permission to Access Reports Manager for which I have choice to choose from Reports Manager Roles such as (Browser, Content Manager etc etc.)
For reports manager I have created a new Role with only permissions to View Reports.

Problem
With all these Security Filters in place yet when I added a Domain User Account to Reports Manager, The user can navigate anywhere on the Reports Manager (Can access folders , DataSources and reports they don’t have permissions on).
I do not want Users to view Data Sources or any other folders or Reports that they don’t have permissions on.
What I have Tried So Far

1- Added a user to One folder on the reports Manager, Yet they can navigate to any other Folder on Reports Manager.
2- Added a user to just one report , again they can navigate to any other Folder on Reports Manager.
3- I was using a virtual account for SSRS Service Account, Removed that and tried on all other 3 built in accounts for service account, LocalService, LocalNetwork & LocalSystem.  (Not sure if this would have made difference).

All these efforts in vain as , whenever a user is added to any one report or folder they can navigate to all the other folders and reports and can actually execute reports.
Users had the datareader role assigned in Sql Server and a Custom Role on Reports Manager who has permissions only to View Report.
I have set the reporting services to use the default windows authentication. It is Sql
Server 2008 R2 Reporting Services. Data Centre Edition.
Please any advice or any direction would be a great help. I have been reading BOL but nothing has helped as yet.

Normally in SSRS you would use the SSRS management interface (/ReportsManager IIRC) to set user roles/perms by folder. They are normally inherited by subfolders though you can override that. The role 'browser' is most appropriate for end users as it allows them to run / view reports but not much more. If you grant a user 'browser' access for folder  but not B then they will not be able to access folder B unless it is either a sub-folder of A (by default granted the same perms as A) or unless B is a sub-folder of another folder the user has 'Browser' access to.

You probably should not be granting domain users any rights directly with-in SQL Server. The various accounts that SQL Server itself uses as well as administrative accounts should be configured with-in SQL Server. End user access to SSRS is controlled by SSRS based on rules entered into SSRS.


How to set password for PostgreSQL database with a script? In a bash script I'm writing, I need to create a database and set a password that is generated automatically by my script.

The problem is I can't find a way to set up the password without user interaction.

I need something like this:

$ createdb mydb -p $(cat password_file)

Any ideas?

From the documentation:

  createdb is a wrapper around the SQL command CREATE DATABASE.
  There is no effective difference between creating databases via this
  utility and via other methods for accessing the server.

This means that you can write a script which issues a command like

psql -f createdb.sql -v passwd=$passwd -v user=$user

Where createdb.sql contains something like

CREATE ROLE :user WITH LOGIN PASSWORD :passwd;
CREATE DATABASE mydb OWNER :user;
GRANT {some privileges on the newly created database and/or its objects};

This means that you will have a user for every database created.

(At the same time I have a mild suspicion that your problem is what riouj answered.)


How to query time series with gaps between a given date range in PostgreSQL? My problem is I don't know how to query time series data between a specified date range where the data has gaps in it and the last known value which should carry forward for each date falls outside the queried date range.
I have the following data in a PostgreSQL table:
DeviceID    State    Date
1           L7       2020-12-05 15:18:00
1           L1       2020-12-10 00:15:00
1           L5       2020-12-11 15:00:00
1           L3       2020-12-14 13:00:00
2           L2       2020-12-08 14:00:00
2           L1       2020-12-14 05:32:00

I would like to query between 2020-12-09 -- 2020-12-12, but my problem is that I don't know the state of DeviceID 1 on 2020-12-09 because the series data has gaps in it.
In the example above DeviceID 1 state is L7 at 2020-12-09, so I would need to query outside the given date ranges to find the L7 state.
My queried table should look like this for the specified date ranges:
DeviceID    State    Date
1           L7       2020-12-09
1           L1       2020-12-10
1           L5       2020-12-11
1           L5       2020-12-12
2           L2       2020-12-09
2           L2       2020-12-10
2           L2       2020-12-11
2           L2       2020-12-12

My end goal is to calculate the time difference between the state changes for a DeviceID, so I could still do that from data with gaps, my only problem is that I don't know the last state change event that falls outside the queried date ranges, in the example above I don't know the state is L7 for DeviceID 1 and L2 for DeviceID 2 at 2020-12-09.

WITH cte AS (
SELECT Dates."Date",
       test.DeviceID,
       test.State,
       ROW_NUMBER() OVER (PARTITION BY test.DeviceID,
                                       Dates."Date"
                          ORDER BY test."Date" DESC) rn
FROM generate_series('2020-12-09'::timestamp, '2020-12-12', '1 DAY') Dates ("Date")
JOIN test ON Dates."Date" >= test."Date"::date
)
SELECT DeviceID, State, "Date"
FROM cte
WHERE rn = 1
ORDER BY 1, 3

fiddle


impdp import fails with object already exists in oracle 10g? I exported the dump using EXPDP command. After exporting I dropped the user and the associated tablespaces by logging is as / AS SYSDBA.

Then I re-created the User and the Tablespaces. Then I imported the dump using IMPDP.
After importing I tried logging in with the re-created user and tried to query a table. And I got the following error.

ORA-01115: IO error reading block from file 5 (block # 326667)

ORA-01110: data file 5: '/data01/test/TBS_MYTBS_01.dbf'

ORA-27091: unable to queue I/O

ORA-27072: File I/O error

Additional information: 4

Additional information: 326666

Machine Details : Sun Solaris
Oracle Version  : Oracle10g

Could someone help me in resolving the error?

Looks like a serious error. What is writte in the alertlog file?
First thought is that the file is damaged.
How exactly did you drop the tablespace[s]?
how exactly did you perform the export?
how exactly did you re-create the tablespace[s]?
how exactly did you re-create the schema?
how exactly did you perform the import?
You should have dropped them using

drop tablespace X including contents and datafiles;

This would have taken care for cleanup of the old tablespaces. I also expect that you would have gotten errors during the import. When messing with databases and tablespaces, a little dba training would be very good. For 10g start here Oracle® Database 2 Day DBA
10g Release 2 (10.2)

and yes, this is more a dba question and task than for a developer.


Can't get update one prompt for same variable over multiple tables I've tried using the following:

select * from TABLE1 where COLUMN= :VAR
union
select * from TABLE2 where COLUMN= :VAR;

I'm getting the following error message back:

[Teradata Database] [TeraJDBC 14.10.00.26] [Error 3654]
[SQLState HY000] Corresponding select-list expressions are incompatible.

If I just run:

select * from TABLE1 where COLUMN= :VAR;

then it works, but I need to be able to answer one prompt for the same variable that is used across multiple tables. I'm using Oracle SQL Developer Version 4.0.3.16

3654   Corresponding select-list expressions are incompatible.

  Explanation:

  If a query contains a UNION, MINUS, or INTERSECT operator, then the
  data types for all select-list expressions must be compatible with the
  corresponding expression in the select-list for the first SELECT in
  the query. All numeric data types are considered compatible. Character
  data types are not compatible with numeric data types. Byte data types
  are only compatible with byte data types. Graphic data types are only
  compatible with graphic data types.

The datatypes of the columns in TABLE1 and TABLE2 do not match.


MariaDB Galera Cluster Replication Does the replication between an async slave connected to a master withing the cluster affect the performance of the master in the cluster?

As mysqld spawns a new thread per connected slave to dump binary logs, I do not think adding a slave would have significant impact on node's performance.


Pagination, check number of selected records I am working on pagination for a complex join query and unfortunately I am not sure what's the best way to go... Right now I am thinking about the following solution.

Imagine that SELECT id... is the complex join query. I copy all id's to the temporary cache.

CREATE TEMPORARY TABLE tmp SELECT id FROM comments WHERE user_id = 123;

Now I will check number of selected records.

SELECT COUNT(*) FROM tmp;

If a number of records is let's say greater than 1000 all records will be moved to a cache table and then selected by e.g 100's (completely all records from a cache are going to be selected ,so it is not as a classic pagination...).

INSERT INTO cache_table SELECT * FROM tmp;
DROP TEMPORARY TABLE tmp; #Temp is not needed anymore

however if number of records is smaller than 1000 then they don't have to be cached and can be immediately selected:

SELECT column_names FROM comments c JOIN tmp t ON c.id = t.id;
DROP TEMPORARY TABLE tmp; #Temp is not needed anymore

OK, now if there are some records in cache, they will be selected by 100 in this case. Records don't have to be selected all at once (10*100), a connection may close and client will ask data later. So we have got 1000 records in cache. To select a data I execute:

SELECT column_names FROM comments c JOIN cache_table ct ON c.id = ct.id LIMIT 100;
DELETE FROM cache_table LIMIT 100; #Records are selected so they can be deleted

This process is repeated until it return 0 or less than 100 records (If there is e.g 1001 rec).

Why generate a temporary that drops on disconnect ? You would have to reload the table of ids, which requires extra code to manage the emptying and reloading. If you create a subquery that limits to 100 ids at a time, you could join to the cache_table:
Just create an inline query for the first 1000 rows, then join
SELECT ct.column_names FROM
(SELECT id FROM comments WHERE user_id = 123 LIMIT 1000) c
LEFT JOIN cache_table ct ON c.id = ct.id;

If you have to paginate, adjust in the inline query to paginate on the IDs then join last
SELECT ct.column_names FROM
(SELECT id FROM comments WHERE user_id = 123 LIMIT 0,100) c
LEFT JOIN cache_table ct ON c.id = ct.id;

SIDE NOTE: I highly advise against creating temporary InnoDB tables. They are much harder to cleanup after a crash because of leftover entries in the ibdata1 file (home of the data dictionary). MyISAM temporary tables are easy to cleanup just by simply deleting the .MYD and .MYI files in the /tmp folder.
UPDATE 2014-12-22 18:19 EST
It sounds like you want Dynamic SQL based on the row count of tmp
CREATE TEMPORARY TABLE IF NOT EXISTS tmp ENGINE=MyISAM
SELECT id FROM comments WHERE user_id = 123;
SELECT COUNT(1) INTO @reccount FROM tmp;

SET @pg_origin = ???;
SET @pg_offset = 100;

SET @q1 = 'SELECT column_names FROM comments c JOIN tmp t ON c.id = t.id';
SET @q2 = CONCAT('SELECT column_names FROM comments c JOIN cache_table ct ON c.id = ct.id LIMIT ',@pg_offset,',',@pg_origin,';');
SET @sql = IF(@reccount < 1000,@q1,@q2);

PREPARE s FROm @sql; EXECUTE s; DEALLOCATE PREPARE s;

I still strongly recommend using MyISAM for the temp table since the row count is written in the .MYD header of the file (O(1) execution time to get the count) whereas an InnoDB temp table required physically counting the rows (O(n) execution time to get the count).
As for the pagination, all you would need is to set @pg_origin to set where to begin retrieval.
As for InnoDB temp tables, sometimes they do not properly drop on disconnect and leave pigeon holes in ibdata1. I wrote about this problem about 2.5 years ago

mysql 5.5 corrupt - missing temporary table
MySQL insists on trying to use InnoDB even when told not to, please help
Table is dropped but ibd file still exists (Recent Post)

UPDATE 2014-12-23 13:08 EST
Your last comment

Can you think about some other suggestions except UPDATE 2014-12-22 18:19 EST? Is it possible to somehow eliminate the use of a temporary table?

If you look at the UPDATE 2014-12-22 18:19 EST, you'll notice that it only creates and loads the table once because I use CREATE TEMPORARY TABLE IF NOT EXISTS tmp instead of CREATE TEMPORARY TABLE tmp.
If you know user_id 123 has new comments you would drop, create and load the temp table.
Even if you don't want the temp table at all, you need to get the count anyway. So, go get count, move it around in a session variable from page to page. Then, apply the Dynamic SQL against that session count rather than having the table.


SQL Server 2017 Enterprise vs Standard Edition? I'm looking for a good resource that gives a breakdown of the feature differences between SQL Server 2017 Enterprise and Standard editions? I want to make sure there's no gotchas or I miss anything truly beneficial by going with Standard.

I've read over the licensing guide from Microsoft, but is there any other notable feature differences it doesn't cover?

What are the vaguely described feature differences like "Advanced Adaptive Query Processing", "Enterprise Data Management", and "Advanced Security", etc?

Asking one question here asking for ALL of the differences is a lot like me asking you, "Well, which features of SQL Server do you use?" It's such a tough question to answer because it's so huge (which is also why this question is likely to get closed, heh.)

You'll be better off asking individual specific questions about each of the feature/edition restrictions that you don't understand.

For example, if you want to know what "Advanced Adaptive Query Processing" is, read the documentation pages on Adaptive Query Processing, and then ask questions specific to that feature.

In my experience, the features that usually cause folks to jump to Enterprise Edition are:

Leveraging more than 24 CPU cores
Leveraging more than 128GB memory
Transparent Database Encryption (TDE) - if you're storing personally identifiable data that could put you on the front page of the paper if someone got a snapshot of your database, then the Enterprise Edition costs just kinda are what they are


Mysql complicated join I have two tables

mysql> select * from emp;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Mari  |
|  3 | Sami  |
|  4 | Kate  |
+----+-------+
mysql> select * from dep;
+----+------+-------+
| id | eid  | name  |
+----+------+-------+
|  1 |    1 | Tom   |
|  2 |    2 | Tom   |
|  3 |    1 | Paul  |
|  4 |    1 | Karl  |
+----+------+-------+

dep(eid) is foreign key references emp(id).

Question

What is the query to get all employees who have a son his name is Tom given that s/he doesn't have a son with name Paul (the result is Mari)
What is the query to get all employees who dont have a son his name is Paul (the result is Mari, Sami, and Kate)

I know it can be handled using sub query very easily but i am looking for the join structure

1 .

select E.* from emp E where id in (
select A.eid from (select distinct eid from dep where name = 'Tom')A
left join (select distinct eid from dep where name = 'Paul')B
on A.eid = B.eid where B.eid is null

)

2 .

select E.* from emp E where id not in (
select distinct eid from dep where name = 'Tom'
)


What are the risks (if any) of sharing a service account on more than one server? I use domain accounts for SQL Server service accounts.  Sometimes, when I have multiple servers that are logically or thematically related, I'll use the same set of domain accounts for the service accounts on all of them.  The user account permissions on each server may be different, but there's usually a lot of overlap.

The gist of my question is this: can a user with access to one instance, but not the other, exploit the shared service accounts to gain access to the other server?

Here's the specific situation I'm trying to address:

I have two servers with default SQL instances, call them org-sql-1 and org-sql-2.
I use a domain account for each service, so I'd have mydomain\orgDBService, mydomain\orgAgentService, etc. (although I'm not sure that's strictly relevant -- I think I'd have the same question if I used a single account for all services).
But I reuse these domain accounts on both servers, so the database service on both org-sql-1 and org-sql-2 runs under mydomain\orgDBService, for example.

To date, I've never particularly questioned the security implications, since these servers have a common purpose and user base, even if the specific user permissions may differ.

Now, we're about to add a third instance, let's call it org-sql-rpt.  This one is a good thematic fit with the others, but there's one key difference: we're going to allow an external partner (i.e. not an employee of our org) access to this server, like so:

Their credentials will own (belong to db_owner) a couple databases.
But they won't belong to any Server Roles (other than public, of course).
They may have admin access to the OS desktop for a limited time, prior to making this server live.
They won't know the passwords for the service accounts.

Given that, should I worry about reusing the domain accounts as service accounts for this new instance?  Is there any risk that this person could use their legitimate credentials on org-sql-rpt to gain access to either org-sql-1 or org-sql-2? (i.e. any risk that could be mitigated by using different service account credentials?)

Or is this just a generally bad idea for other reasons?

EDIT

The new instance will be hosting Database, Integration, and Reporting services (for now, at least).  No Analysis services.

The external user will have elevated database privileges, but no explicit instance permissions.  They won't be able to create logins, jobs, or reports, for example.

Risk mitigation would indicate creating a separate account for each service on each machine.  The level of work required to create the accounts necessary is extremely minimal, but the unknown risks that accompany not doing so are quite high, according to Microsoft's own recommendations.
Microsoft Best Practices recommend using separate service accounts for all services.
See http://msdn.microsoft.com/en-us/library/ms144228.aspx#isolated_services for details.
The salient points being:

Isolate Services
Isolating services reduces the risk that one compromised service could be used to compromise others. To isolate services, consider the following guidelines:
Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service. For more information, see Configure Windows Service Accounts and Permissions.

There is also a KB talking about securing SQL Server that mentions how to configure service accounts properly:
http://support.microsoft.com/kb/2160720

When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported. For more details please refer to Books Online Topic Setting Up Windows Service Accounts.

Technet has an article, titled Configure Windows Service Accounts and Permissions at http://technet.microsoft.com/en-us/library/ms143504.aspx that has this to say:

Security Note:  Always run SQL Server services by using the lowest possible user rights. Use a MSA or virtual account when possible. When MSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported.

"MSA" in the above paragraph refers to "Managed Service Accounts" which is the default for installations on Windows 7 or Windows Server 2008 R2 and above.  Managed Service Accounts are defacto unique to each machine.
As an aside, one issue I think about when configuring multiple servers to run under the same service account is account lockouts.  If you use a single service account for all SQL Servers, and the service account gets locked out, all your servers might be affected.  If you have one account per service, at most one server can be affected by a lockout.


How to verify that a certain commit has been transmitted to standby Scenario:

Oracle Data guard 12c with 1 primary and 1 standby, Max. Av. protection mode
Primary open, standby mounted (all green in OEM)
Perform a dummy CTAS in primary

Question:

Without opening it, how can I be sure the data has been received by standby? Is there a log, a table, anything, that I can consult in Primary?

select sequence#, applied from v$archived_log;

shows a lot of not applied logs but I'm not sure it is relevant. Also, it does not really identify the query.

Thank you

Without opening the standby for read-only, you won't be able to identify specific changes to specific physical objects, instead you'll have to verify that a specific set of changes have been applied to the remote standby. Views including v$standby_apply_snapshot, v$archived_log, v$archive_gap, v$dataguard_stats, v$managed_standby are all ones that I use on a regular basis to verify standby apply health.

Additionally, you can use the dgmgrl command line interface to show the status of your log apply on the standby.


Delete duplicate records with no change in between I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 out of 30 vendors a day. Each vendor has around 35,000 unique products. No product can belong to more than one vendor.

CREATE TABLE vendor_prices (
  id serial PRIMARY KEY,
  vendor integer NOT NULL,
  sku character varying(25) NOT NULL,
  category_name character varying(100) NOT NULL,
  price numeric(8,5) NOT NULL,
  effective_date timestamp without time zone,
  expiration_date timestamp without time zone DEFAULT (now() + '1 year'::interval)
);

I'm trying to delete irrelevant records where there was no price change and it's no longer the last update for said product, eg.:

  effective_date     price
  '2015-05-01'       $1.99
  '2015-05-02'       $1.99 delete
  '2015-05-03'       $1.59
  '2015-05-04'       $1.99
  '2015-05-05'       $1.99 delete
  '2015-05-06'       $1.99 keep for new expiration date

So after each load (I figured it'll be easier for one vendor at a time) I want to do some kind of delete. Here is the long non-performing solution that I came up with.

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor integer)
  RETURNS integer AS
$BODY$
BEGIN
    -- Delete Redundant prices
    delete from vendor_prices
    where id in (
      select id from (
        select vp1.id, vp1.vendor, vp1.sku, vp1.price, vp1.effective_date, vp1.expiration_date
          from vendor_prices vp1
          inner join (
              select vendor, sku, price from vendor_prices
                where vendor = _vendor
                group by vendor, sku, price
          ) vp2
          on vp1.vendor = vp2.vendor and vp1.sku = vp2.sku and vp1.price = vp2.price
          where vp1.vendor = _vendor
      ) dupe

      -- fetch the irrelevant record
      WHERE (select a.effective_date from vendor_prices a
        where vendor = _vendor
        and a.price = dupe.price and a.sku = dupe.sku and dupe.effective_date > a.effective_date

        -- but make sure there's no price change in-between(
        and (select b.effective_date from vendor_prices b
          where vendor = _vendor
          and b.sku = dupe.sku and b.effective_date < dupe.effective_date and b.effective_date > a.effective_date limit 1) IS NULL
          limit 1
      ) IS NOT NULL

      -- and that this is not the last update on said product, otherwise we'll keep it for expiration_date
      and ( select c.effective_date from vendor_prices c
              where vendor = _vendor
              and c.sku = dupe.sku
              and c.effective_date > dupe.effective_date limit 1
          ) IS NOT NULL
    );
 return 0;
END;
$BODY$
LANGUAGE plpgsql

This function ran for a few hours so I killed it. The table has around 5 million records. I tried all kinds of different indexes and combo indexes but nothing seems to help. There may be other inserts and deletes while I'm running this function.

Running PostgreSQL 9.3.4 on Solaris 11.2.
I have plenty of RAM and disk space.

Core feature is the window function lag().
Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!):

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int)
  RETURNS integer AS
$func$
DECLARE
   del_ct int;
BEGIN
   -- this may or may not be necessary:
   -- lock rows to avoid race conditions with concurrent deletes
   PERFORM 1
   FROM   vendor_prices
   WHERE  vendor = _vendor
   ORDER  BY sku, effective_date, id  -- guarantee row locks in consistent order
   FOR    UPDATE;

   -- delete redundant prices
   DELETE FROM vendor_prices v
   USING (
      SELECT id
           , price = lag(price) OVER w  -- same as last row
             AND (lead(id) OVER w) IS NOT NULL AS del  -- not last row
      FROM   vendor_prices
      WHERE  vendor = _vendor
      WINDOW w AS (PARTITION BY sku ORDER BY effective_date, id)
      ) d
   WHERE v.id = d.id
   AND   d.del;

   GET DIAGNOSTICS del_ct = ROW_COUNT;  -- optional:
   RETURN del_ct;  -- return number of deleted rows
END
$func$  LANGUAGE plpgsql;

Call:

SELECT remove_vendor_price_dupes(1);

Notes

The current version of the 9.3 major release is 9.3.6. The project recommends that ...

  all users run the latest available minor release for whatever major version is in use.

A multicolumn index on (vendor, sku, effective_date, id) would be perfect for this - in this particular order. But Postgres can combine indexes rather efficiently, too.
It might pay to add the otherwise irrelevant price as last item ot the index to get index-only scans out of this. You'll have to test.
Since you have concurrent deletes it may be a good idea to run a separate delete per vendor to reduce the potential for race conditions and deadlocks. Since there are only a few vendors, this seems like a reasonable partitioning. (Many tiny calls would be comparatively slow.)
I am running a separate SELECT (PERFORM in plpgsql, since we do not use the result) because the row locking clause FOR UPDATE cannot be used together with window functions. Don't let the keyword mislead you, this is not just for updates. I am locking all rows for the given vendor, since the result depends on all rows. Concurrent reads are not impaired, only concurrent writes have to wait until we are done. That's another reason why deleting rows for one vendor at a time in a separate transaction should be best.
sku is unique per product, so we can PARTITION BY it.
ORDER BY effective_date, id: your first version of the question included code for duplicate rows, so I added id to ORDER BY as additional tie breaker. This way it works for duplicates on (sku, effective_date) as well.
To preserve the last row for each set: AND (lead(id) OVER w) IS NOT NULL. Reusing the same window for lead() is cheap - independent of the added explicit WINDOW clause - that's just syntax shorthand for convenience.
I am locking rows in the same order: ORDER BY sku, effective_date, id. Make sure that concurrent DELETEs operate in the same order to avoid deadlocks. If all other transactions delete no more than a single row within the same transaction, there cannot be deadlocks and you don't need the row locking at all.
If concurrent INSERTs could lead to a different result (make different rows obsolete), you have to lock the whole table in EXCLUSIVE mode instead to avoid race conditions:

LOCK TABLE vendor_prices IN EXCLUSIVE MODE;

Do that only if it's necessary. It blocks all concurrent write access.
I am returning the number of rows deleted, but that's totally optional. You might as well return nothing and declare the function as RETURNS void.


How to create Relationship between multiple databases? I am working with multiple databases which have the same structure and are on the same server. All of them are MySQL databases and I'm using MySQL Workbench (community version!) to connect to them.

I need to retrieve data from all (or some) of them and compare the results. sometimes I need to store the retrieved data from those databases in another database.

How can I manage that?

Because all databases (schemas) on same server You can operate with data easy - just add schema name before table, like:

SELECT t1.* FROM database1.table1 t1 LEFT JOIN database2.table1 t2 ON t1.id = t2.id

same for compare and save result, like

INSERT INTO database2.table3
SELECT * FROM database1.table1 t1 WHERE NOT EXISTS (SELECT id FROM database2.table1 t2 WHERE t2.id = t1.id)

and etc

MySQL workbench can help You with compare schema

You can also use - mysqldbcompare


SQL Server Standard Basic Availability Group - only 10 Listeners limit? Is there a limit to the number of listeners that can be created using SQL Server Standard?

I'm working with a DBA who has set up a 2017 Standard server, with CU3. We have 12 databases, and he was able to set up 12 basic availability groups. He was able to set up 10 listeners, but on the 11th, is getting the error below:

  Create failed for Availability Group Listener 'test12300'.  (Microsoft.SqlServer.Smo)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  The WSFC cluster could not bring the Network Name resource with DNS name 'test12300' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.
  The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider. (Microsoft SQL Server, Error: 19471)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3015&EvtSrc=MSSQLServer&EvtID=19471&LinkId=20476

We created 11 empty databases, backed up each, and then through the AG Wizard created an AG and a Listener using DHCP. It worked 10 times, but on the 11th it failed. For the 11th listener, we chose name BAG_L11, there is nothing else that uses that DNS name.

The limitation is only number of replicas.

Limit of two replicas (primary and secondary).
he was able to set up 12 basic availability groups. He was able to set up 10 listeners

You can create many AGs and listeners. The error you are getting is self explanatory

The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

Also refer to Top 7 Questions about Basic Availability Groups and Troubleshooting AlwaysOn availability group listener creation in SQL Server 2012.


Restoring an SQL Server 2012 MDF to SQL Server 2008 Possible Duplicate:
  Restore SQL Server 2012 backup to a SQL Server 2008 database?

I have created a database on SQL server 2012 with compatibility mode for SQL server 2008 (100).
When I do a backup and try to restore it on an SQL 2008 I get an error. Same goes for attach/detach.

Is there a way to move/restore/migrate it to SQL Server 2008?

Thanks

Tomer

Your only avenue is to export the database and then import it back on SQL 2008. SSMS has an Export/Import Wizard you can give a shot. Other tools exists from 3rd party vendors like Red Gate's SQL Compare to copy the schema and Data Compare to copy the data. I'm sure there are more. The gist of it that you need to go through some sort of export/import, SQL Server does not support any downgrade path for the MDF format. Obviously, you must not had rely on new SQL Server 2012 features like columnstores.


Using Oracle Datapump when target table has additional column Table 1 has 5 columns. Table 2 has the same 5 columns, plus an additional non-nullable column. I would like to use datapump to copy the data from Table 1 to Table 2. In Sql Server, I can use bcp and specify a query to be used on export, in which I can "SELECT *, 'New Data'" from Table 1. This has the effect of putting the string 'New Data' into my new column in Table 2 when I import.

How would I accomplish this using Oracle's Datapump? From what I've read, I can only specify a WHERE clause, which means I can't add 'New Data' to my new column.

So far the only idea I've come up with is to disable the not null constraint on the new column in Table 2, datapump the data over from Table 1, and then update all rows to add 'New Data' into the new column. Is that the only option I have?

Just import your data into a a staging table at the target database. Then write a SQL staement to copy the rows from the staging table into the target table while giving the two new columns an explicit value.
So something like this;

The following syntax should work on 11g

impdp user/password directory=import_directory dumpfile=data.dmp  REMAP_TABLE=schema.source_table_name:staging_table_name

Then, simply do something like this;

INSERT INTO target_table (column1, column2, column3, column4, column5)
SELECT column1, column2, column3, 'New Data', 'New Data2'
FROM staging_table_name

After which you can drop the staging table.


How to format the output of this query into year...second? I have this query that given @secs a number of seconds, it returns a table with year,months,weeks,days,hours,minutes and seconds.

take for example:

declare @secs int
select @secs = 19475298

                                SELECT      FLOOR ( UpTime / 31207680 ) AS YEARS
                                        ,   FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680  )  * 12 ) AS MONTHS
                                        ,   FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 )  * 4.3 ) AS WEEKS
                                        ,   FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 ) AS DAYS
                                        ,   FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS
                                        ,   FLOOR( ( UpTime / 60 )   - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES
                                        ,   UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS

                                FROM        (

                                                   select @secs as Uptime

                                             ) AS RadheX

this gives me:

how can I have all these columns in a single string with 2 digits for each column?

on the example above the result would be
00:07:01:01:09:48:18

you can try Right('0'+ Cast(expression as varChar(3)), 2) like this:

SELECT Right('0' + Cast( FLOOR ( UpTime / 31207680 ) as varChar(3)), 2) + ':' -- YEARS
  + Right('0' + Cast( FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680  )  * 12 )  as varChar(3)), 2) + ':' --  MONTHS
  + Right('0' + Cast( FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 )  * 4.3 )  as varChar(3)), 2) + ':' --  WEEKS
  + Right('0' + Cast( FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 )  as varChar(3)), 2) + ':' --  DAYS
  + Right('0' + Cast( FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 )  as varChar(3)), 2) + ':' --  HOURS
  + Right('0' + Cast( FLOOR( ( UpTime / 60 )   - FLOOR( UpTime / 3600 ) * 60 )  as varChar(3)), 2) + ':' --  MINUTES
  + Right('0' + Cast( UpTime - FLOOR( UpTime / 60 ) * 60  as varChar(3)), 2)  --  SECONDS
FROM (
    select @secs as Uptime
   ) AS RadheX


SQL Server : Restoring database mirroring once its turned off I was running security updates on the sql server mirror setup and I encountered split brain scenario as mentioned here

Should I pause Mirroring and stop SQL Server services before running windows updates?

So to fix this issue I turned off database mirroring.

Now I am trying to find a way to restore database mirroring.

Would simply turning on database mirroring solve the issue? , Would the transaction logs automatically update?
Or Should I take a full back and transaction log and restore the database. I would prefer option 1 if that is possible , because in option I have to setup the logins again .
what's the best practice to resolve a split brain issue?

Incase you are already in a situation where db's on principal and mirror are in recovery mode. (This could occur due to many reasons like certificate expired network failures) etc, what's the best way to resolve such a situation, do you need pause mirroring and fix the issue?

ALTER DATABASE dbname SET PARTNER OFF will break mirroring. See this answer. You will have to reconfigure mirroring again. So option 2 is your only option at this moment.

The answer that I linked has an automated script that will help you.

  Would simply turning on database mirroring solve the issue? , Would the transaction logs automatically update?

No, since you issued SET PARTNER OFF, mirroring is broken. Now you have to initialize using a full backup and latest transaction log backup from the primary server to the mirror server.

  what's the best practice to resolve a split brain issue?

Next time, first turn off (remove) the witness and then do SET PARTNER FAILOVER as I have detailed in my answer.


Is an explicit KEY redundant if it is covered by a UNIQUE KEY? I am auditing an application which I've been developing over the course of several weeks. I've noticed this in CREATE TABLE:

UNIQUE KEY `baz_3` (`baz`,`foo`,`bar`),
KEY `baz` (`baz`),

Is the explicit KEY redundant, as I think it is? I just want to make sure before I DROP it. From what I've read I do believe that the  UNIQUE KEY will act as an Index when the application performs WHERE baz='something' but MySQL seems so intricate that I find it prudent to ask here first.

Yes, you can drop it because baz and baz_3 have identical leading columns. This scenario is known as having redundant indexes.

However, be careful that the key baz is not being used to support a foreign key constraint if the table is InnoDB. See my post Do I have duplicate key indexes?

If the table is MyISAM, go ahead and drop it with confidence. The MySQL Query Optimizer will use the UNIQUE KEY baz_3 for query optimization in its execution plans.

If you want to locate all tables with redundant indexes, see this post : MySql - find redundant indexes


Threads_cached 0 while thread_cache_size > 0 I'm trying to little bit understand with some MySQL (in my case - MariaDB) performance.

One point I found - "threads caching".

So - I set:

thread_cache_size = 4

in my.cnf and restart server.

In status I see:

MariaDB [mysql]> show status where variable_name like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 23    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Next - I run mysqlslap:

$ mysqlslap -v -u user -p --auto-generate-sql --concurrency=10 --number-of-queries=100
...
Number of clients running queries: 10
Average number of queries per client: 10

Thus, if I correct uderstood, after clients disconnected - server must save 4 thread in cache to use it later, but:

MariaDB [mysql]> show status where variable_name='Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 0     |
+----------------+-------+
1 row in set (0.00 sec)

Threads_cached still have 0.

When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there.

In your case, it would read

  When a client disconnects, the client's threads are put in the cache if there are fewer than 4 threads there.

Also, note the following from the MySQL Documentation

  Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads.

Given this statement, 4 would seem way too low. You should set it much higher or don't set it at all (letting MariaDB set it for you).


Finding a good DB design for my project I've posted this on stackOverflow but this might be a more suitable place.

I'm really struggling to find a good DB design for my project using SQL server.

I've already implemented a few models which worked great till this point, but now that I need to add something extra I just can't find a good option and I'm stuck with it ATM.

I've supplied below 2 very simplified models (class diagrams) I've tried, but both models are not working well.

1st model: which I also prefer if it's possible to fix

I should explain first that msg and action can have the same basic id (i.e 1) but when used with tabID or groupID as a composite primary key, it becomes unique.

Here you can see that UserInput is created using only the basic ID which creates a problem to save both a Msg and Action with id 1 for example.
Is there anyway around this? maybe a way to say that ya Action and Msg are extending UserInput but they define all the keys themselves?

2nd model:

Each Critical Point is related to either a Msg or Action, but how can I define it since they have a different set of PK? I would like to keep referral integrity.

I would REALLY appreciate help on this issue.

Update

Maybe my only option is to use this?

UserInput
  UserInputID:   PK (autogenerated)

Action
  ActionID:      PK
  TabID:         PK, FK
  UserInputID:   FK

Message
  MessageID      PK
  GroupID:       PK, FK
  UserInputID:   FK

The question is can I make the Msg and Action extend from UserInput without taking defining his PK as part of their PK...

In the comments you mention that a UserInput is either a Msg or an Action and not related to many actions or to many messages. The first model does not accurately describe this relationship.

You correctly identify that the problem is how to make Action and Msg "extend" UserInput. This is often called Supertype/Subtype pattern in database design.

I would make (UserInputID) as the PRIMARY KEY in both Action and Msg:

UserInput
  UserInputID   PK

Action
  UserInputID   PK, FK1
  TabID         FK2

Message
  UserInputID   PK, FK1
  GroupID       FK2

The rest of the tables would stay unchanged. This is the easiest thing to do, assuming that you have the freedom to define how the IDs are populated (for every new Action, a new UnserInput is inserted and the ID is used for the Action, too. Similarly for the Msg table.)
It still does not address all potential problems. A malicious or wrong update can have the database into some weird status, where a UserInput is both an Action and a Msg.

An altered design can overcome the issue by adding a Type column and some constraints:

UserInput
  UserInputType  PK
  UserInputID    PK
  CHECK (UserInputType IN ('A', 'M'))

Action
  UserInputType  PK, FK1
  UserInputID    PK, FK1
  TabID          FK2
  CHECK (UserInputType = 'A')

Message
  UserInputType  PK, FK1
  UserInputID    PK, FK1
  GroupID:       FK2
  CHECK (UserInputType = 'M')

CriticalPoint
  UserInputType  PK, FK1
  UserInputID    PK, FK1


Space not reclaimed after dropping MyISAM table I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I get Error 28 (out of disk space) when I try to do pretty much anything with my database.

I have heard that due to the way that InnoDB manages tables, you have to do some extra work to reclaim space in certain situations after doing deletes/drops; however I did not think this was the case with MyISAM.

I have read that in some situations, after doing a massive delete from a table, optimizing the table can help free space, however since I have dropped the table, that is not an option.

What am I missing? What can I do to free the space?

I am using Centos 6.4 Final with MySql 5.5.25

EDIT: I learned what the problem was - Our servers are monitored by our hosting provider, and due to this, the result of df is not live, but rather reflects an infrequent snapshot of our server taken by the monitor. its insanely annoying.

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root.

Here is what you can do to start investigating: Go to the OS and do this:

cd /var/lib/mysql
df -h .

If the root partition is 100% despite dropping 4GB,

your datadir is not var/lib/mysql
another table consumed up the space quickly
something else clogged the root partition

Who are the usual suspects ?

Binary Logs : You cannot run PURGE BINARY LOGS with a 100% full disk. You will have to locate the folder with the binary logs, delete the oldest ones, and mysqld will get moving.
Error Log : Just a text file. Suppose your error log is /var/log/mysqld.log. You run echo -n > /var/log/mysqld.log and the space from that file is reclaimed. I don't use rm since mysqld has an open file handle to the error log.
Slow Log : Locate the Slow Log and do the same thing as you did with the Error Log
Temp Tables : These are usually created in /tmp. If you see .MYD file in /tmp with old timestamps, you can manually delete them.

Whatever it is, find the offending files and delete/truncate them because mysqld will not crash on Error 28 (Out of Disk Space). The mysqld will pause and wait patiently for diskspace to be available (See my post "Site Offline" MySQL server failing to start and stop).


Managing Access for DBA and IT Support We are currently in the process of moving our development, test and live databases on to separate servers and instances and I am looking at how best to manage access for DBA tasks and also IT Support tasks (permission to read/write data but not execute SP's, create tables, etc), mainly in the live environment.

What is the recommended way to provide access for a DBA?  Is it good practice to create a windows AD account specifically for the DBA and then add it as a login to SQL Server with sysadmin server role?

What about managing access for IT Support team?  We really want to log/audit individual team members so we know who did what, etc.  Would this be best done using individual SQL Server Logins with each login mapped to a corresponding user in each database as needed (seems like a lot of maintenance would be required adding/removing users)?  Or is there a way to manage access using say a windows AD group with each team member using their own AD account but use the AD group in SQL Server to manage permissions (but still being able to log an individuals actions)?

Also, should IT Support team members login to SQL Server Management Studio (SSMS) from a server in the same domain as the SQL instance is running or can they install SSMS on their laptops and login remotely?  Issue here being their laptops are on different domain so windows auth may not work (although I did find a way to launch SSMS from command line and have it login using specified domain credentials), just not sure what best practice is here.

Regarding providing DBA access, you might be better-off creating an Active Directory group and adding the DBAs into that group.  That way, you can provide group-level permissions and give sysadmin to the group rather than individual users, meaning that when a DBA leaves (or a new one gets hired), you just need to adjust Active Directory rights rather than going into each SQL Server and altering logins.  The same applies (though without granting sysadmin!) for other groups of internal users.  Windows authentication is more secure than SQL authentication, and if you can use it, you probably should.

Regarding auditing, you can still have individual-level auditing when users are in a group.  You can use SUSER_NAME() to get the username in whatever process you're going to use to log activity.  Note that somebody could do an EXECUTE AS to switch their user context, but you should be able to log that as well as the IP address from which the query came, so you'd still be able to correlate activity to a person.

The idea of using a jumpstation (in your case, you mentioned a server) is possible.  It would make administration a bit easier, especially if the laptop domain does not have a full trust relationship with the domain upon which the SQL Server instances are located.  It would probably be a bit annoying for the support personnel, honestly, but if you go that route, I'd recommend a virtualized desktop for each IT support team member.  That way, they don't have to deal with server contention issues and you can minimize the pain.  If you do go down that route, then the IT support team members could have domain accounts and you'd create relevant Windows groups the same way as the DBA group.


Data modelling concepts Let´s say I have 3 entities. Can I have them all linked to each other "forming a circle" where each one is linked to the other trough a many to many relationship.

Cheers

Sometimes this is called "a cycle" in database design, and while it is not forbidden (because as the commenters point out, you must take into account your purpose and business problem), it is considered a red flag for many designers.

Here is a related Stack Overflow question that attempts to point out why this can be a red flag: https://stackoverflow.com/questions/8115748/why-should-i-avoid-loops-when-designing-relationships-for-a-database


Outer self join filters vs subqueries I have a table storing information about tickets. There is  one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' to mark which one it is. There are other columns in the tables and some of them contains values when it is sales but not when it is used and vice versa. The table is actually a fact-table in a data warehouse style.

Amoung other things I am calculating time difference between sales and used so I am joining the table to itself in order to get one record for each ticket to be able to have timestamps for both event in the same record to calculate.

I need to include all sold tickets so an outer join should solve that.

First I ran this query

select x.*
from factI as x
left join factI as y on x.tickedId = y.tickedId
where x.TransType = 'sold'
and y.TransType = 'used'

When I run it the filter x.TransType = 'sold' is not working and the query return results for all records in factI, regardless of TransType. If I use inner join this works but obviously not returning tickets which haven't been used.

so I changed the query, to this one which gives me correct results.

select * from (
   select * from factI where TransType = 'sold'
) as x
left join (
   select * from factI where TransType = 'used'
) as y on x.ticketId = y.ticketId

Why is it that the where clause in the first query isn't filtering out correctly when I use an outer (left) join?

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause.

Your second query can be rewritten without derived tables by simply moving that condition to the on clause:

select x.*, y.*
from factI as x
left join factI as y on  x.tickedId = y.tickedId
                     and y.TransType = 'used'
where x.TransType = 'sold' ;


Why use asymmetric keys in SQL Server? I've been reading multiple resources on SQL Server Encryption, and everyone does it basically the same way, but no one explains why we use asymmetric keys in SQL Server. And by this I mean when we follow "proper" procedure and encrypt data symmetrically first, followed by an asymmetric key or certificate as in this MSDN heirarchy:

So if I have data, first I encrypt it with a symmetric key for speed and avoidance of re-encryption if compromised. We then secure that key with a second-tier of encryption.

Now lets assume I secure the second key with a password. What advantage does a Certificate, Asymmetric Key, or Symmetric Key have in this scenario? It seems to me we never utilize the public aspect of a Certificate or Asymmetric key using this strategy since we are only using the decryption aspect. So shouldn't be be agnostic to which of the three methods we choose? We can revoke access to all three key types and grant permissions to use all three keys. Why not use another symmetric key?

The only difference I see here is that you can protect a Cert or Asym key with the DMK so users need not enter a password. This makes access easier at the expense of losing the security of forcing users to enter that second password.

Is this a correct understanding?

The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.

The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.

All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.


xp_cmdshell in a transaction I need to run multiple scripts in a certain folder in a transaction and rollback if there are any errors. At this point I'm just trying to get the rollback to work without a try catch.

Currently when I run the script, it gets the proper list of sql files recursively, but does not rollback the changes if there is an error. If there are 10 files and 3 complete but there is an error on the 4th, the first 3 files do not rollback.

Here is my script:

BEGIN TRANSACTION

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO

DECLARE @folderPath VARCHAR(100) = 'folder\path'
DECLARE @cmd VARCHAR(8000) = 'dir /b /s "' + @folderPath + '\*.sql"'

INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell @cmd
GO

DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT
        [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]

DECLARE @folderPath         VARCHAR(100) = 'folder\path'
DECLARE @vFileName          VARCHAR(200)
DECLARE @vSQLStmt           VARCHAR(4000)

SELECT * FROM ##SQLFiles

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ServerName -U UserName -P "password" -d dbname -i "' + @folderPath + @vFileName + '"'''
    PRINT @vSQLStmt
    EXECUTE (@vSQLStmt)

    FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO

ROLLBACK TRANSACTION

Is there any way to get the changes made by the files to be rolled back?

No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions.

Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.


I cannot access local database on terminal (MAC) I have access to my databases on phpmyadmin and mysql Workbench on my local computer but I have a problem when I try to access the server using Terminal.

Originally, I had uploaded the databases on phpmyadmin. When I utilized Workbench after running new server instance localhost, I had automatic access to my databases. However, when I run mysql command in terminal and show databases, only the default information-schema and test database show up.

Furthermore, when I try to execute the .sql files to import the database on terminal, I get the following error:

  ERROR 1046 (3D000) at line 11: No database selected.

After reading through the documentation, I thought the issue was that I didn't start off the script with USE Database db_name. I then implemented that in the file and then I got this message in terminal:

  ERROR 1044 (42000) at line 7: Access denied for user ''@'localhost' to database 'database'.

If I have access to my databases on phpmyadmin and on Workbench, shouldn't it be the same in terminal?

Also when I first run in terminal mysql -u admin -p < text-file, it asks me for my password. If I simply press enter, then I receive the aforementioned ERROR 1046 or ERROR 1044.

If I put in my password, I then receive the following error:

  ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES).

Can someone please help?

It could be your admin user is connecting from admin@127.0.0.1.

Assuming admin is the actual user you have privileges on, try connecting through:

> mysql -h 127.0.0.1 -u admin -p

The way MySQL handles users, the host is very important, and in this case 127.0.0.1 is different than localhost.

If your phpMyAdmin user has access to the mysql database, you can the following statement to see what user/host combinations exist on your install.

 SELECT User, Host FROM mysql.user


Existing data to new table, how to set up the PK & Identity I am moving an Access app to SQL Server.  I have confirmed the data in the existing PK is unique so I want to copy it into the new PK Identity field.  The field is currently set as IDENTITY(1,1).  I know I can set the IDENTITY_INSERT for the insert period, but do I then need to adjust the IDENTITY start point so the next insert does not start at 1?  Example, the newest row in the existing data is id 4132, so do I insert the data then alter the column to IDENTITY (4133,1) ?

This is simple enough to test on your own. Here's how:

Simple table:

CREATE TABLE dbo.whatever
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Whatever VARCHAR(10)
);

Simple insert:

INSERT dbo.whatever ( Whatever )
SELECT x.Whatever
FROM   ( SELECT TOP 100 'A' AS Whatever FROM sys.messages AS m ) AS x;

Check on the max Id and current identity value:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 100.

Do an out of band insert:

SET IDENTITY_INSERT dbo.whatever ON;

DECLARE @maxid INT = (SELECT MAX(w.Id) FROM dbo.whatever AS w);

INSERT dbo.whatever ( Id, Whatever )
SELECT x.Id + @maxid, x.Whatever
FROM   (   SELECT TOP 100 ROW_NUMBER() OVER ( ORDER BY @@SPID ) AS Id, 'A' AS Whatever
           FROM   sys.messages AS m ) AS x;

SET IDENTITY_INSERT dbo.whatever OFF;

Check on stuff:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 200.

Do another insert:

INSERT dbo.whatever ( Whatever )
SELECT x.Whatever
FROM   ( SELECT TOP 100 'A' AS Whatever FROM sys.messages AS m ) AS x;

Check on stuff:

SELECT MAX(w.Id) AS [max_id], IDENT_CURRENT( 'whatever' ) AS [ident_current]
FROM dbo.whatever AS w;

Both 300.

Hope this helps!


Order column data in a tablix in SSRS I have created a report with following data

Age_Category  Count
31-40          10
41-50          20
51-60          30
61 and Over    40
Under 30       50

Now I want to order "Age_Category" column in SSRS like below, what is the best approach

Age_Category  Count
Under 30       50
31-40          10
41-50          20
51-60          30
61 and Over    40

create table #MyCategory ( Category varchar(20), SortOrder int )
create table #MyResultData ( Category varchar(20), Count int )

-- in no particular order...
insert into #MyCategory ( Category, SortOrder ) values
     ('51-60',       4 )
    ,('61 and Over', 5 )
    ,('31-40',       2 )
    ,('Under 30',    1 )
    ,('41-50',       3 )

-- in no particular order...
insert into #MyResultData ( Category, Count ) values
     ('61 and Over', 50 )
    ,('41-50',       30 )
    ,('31-40',       20 )
    ,('51-60',       40 )
    ,('Under 30',    10 )

-- sorted
select t1.Category, t1.Count
from #MyResultData as t1 join #MyCategory as t2 on t1.Category = t2.Category
order by t2.SortOrder


Does restoring a database break external synonyms targeting objects in that database? If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms?

To be specific, imagine this situation:

Database Synonym_Targ on a SQL 2008 R2 server has some database objects in it, e.g. a table called dbo.foo
Database Synonym_Home has a synonym dbo.foo referring to the table dbo.foo in the database Synomym_Targ.
A backup of the database normally resident in Synonym_Targ is restored into it.  This contains an object dbo.foo.

Should one expect this process to invalidate the dbo.foo synonym on Synonym_Host?

This process should not invalidate the synonym. As per the docs:

  The binding between a synonym and its base object is by name only. All existence, type, and permissions checking on the base object is deferred until run time. Therefore, the base object can be modified, dropped, or dropped and replaced by another object that has the same name as the original base object.

You can test this behavior with the following.

use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go
create database Synonym_Targ
create database Synonym_Home
go
alter authorization on database ::Synonym_Targ to sa
alter authorization on database ::Synonym_Home to sa
go
use Synonym_Targ
go
create table dbo.foo ( i int default 1);
go
insert dbo.foo default values
go
use Synonym_Home
go
create synonym dbo.foo for Synonym_Targ.dbo.foo
go
create or alter proc p
as
select * from dbo.foo
go
exec p
go
backup database Synonym_Targ to disk = 'c:\temp\Synonym_Targ.bak'
go
drop database Synonym_Targ
go
exec p
/*
Msg 5313, Level 16, State 1, Procedure p, Line 3 [Batch Start Line 30]
Synonym 'dbo.foo' refers to an invalid object.
*/
go
restore database Synonym_Targ from disk = 'c:\temp\Synonym_Targ.bak'
go
exec p
go
use [master]
go
drop database if exists Synonym_Targ, Synonym_Home
go


Should you encrypt the system databases? A finding from a STIG evaluation, CCE-19832-5 reports that the [master] database should be encrypted with a Database Master Key using an encrypted password that meets DoD standards.

This a result of an AppDetective scan; it states:

  Resource key 'database_containing_encrypted_key' not found.=[master].

I am not deliberately storing anything in the master database.

I've encrypted all user databases but my question is: do you encrypt the system databases, specifically master?  I am using TDE with SQL Server 2012.

The answer is "NO".

When you attempt encrypt a system database, SQL Server complains:

  Cannot encrypt a system database.  Database encryption operations
  cannot be performed for 'master', 'model', 'tempdb', 'msdb' or
  'resource' databases.

However, it's important to realize that any successful encryption of a non-system database will cause TempDB to be encrypted automatically, to protect temporary objects. In fact, even if TDE is later removed from the user database, TempDB will remain encrypted.


Multiple criteria inside CASE WHEN statement Here UserFlag, QtyAvailable and OrderStatus are three different columns and Im trying to create a two additional columns named "BackOrder Status" and "Shipped Status" in another view.
for BackOrder Status the condition would be
UserFlag =”On Order”
,UserFlag =”Sold Out”
, UserFlag =”On Order
, Sold Out”
, QtyAvailable=0
, OrderStatus =“Pending Fulfillment“
then it's a "BackOrder" ELSE "Not a BackOrder"

also, for Shipped Status the condition would be
Orders Shipped: OrderStatus =”Billed”
                , OrderStatus =”Partially Fulfilled”
                ,OrderStatus =”Pending Billing”
                , OrderStatus =”Pending Billing/ Partially Fulfilled”
 then it's "Shipped" ELSE "Not Shipped"

Please let me know if you come up with the query for this.Thanks in advance.
Attempt 1:
CASE
  WHEN Userflag = 'On Order' AND QtyAvailable = '0' THEN 'BackOrder'
  WHEN Userflag = 'Sold Out' AND QtyAvailable = '0' THEN 'BackOrder'
  WHEN Userflag = 'On Order, Sold Out' AND QtyAvailable = '0' THEN 'BackOrder'
  ELSE 'Not a Backorder'
END

Attempt 2:
CASE
  WHEN OrderStatus = 'Billed' THEN 'Order Shipped'
  WHEN OrderStatus = 'Partially Fulfilled' THEN 'Order Shipped'
  WHEN OrderStatus = 'Pending Billing' THEN 'Order Shipped'
  WHEN OrderStatus = 'Pending Billing/Partially Fulfilled' THEN 'Order Shipped'
  ELSE 'Not Shipped'
END

You can use following:

CREATE tABLE test(UserFlag varchar(100),QtyAvailable INT,OrderStatus VARCHAR(100))
GO

SELECT
CASE WHEN
UserFlag IN ( 'On Order', 'Sold Out', 'On Order,Sold Out')
AND
QtyAvailable = 0
AND
OrderStatus = 'Pending Fulfillment'
then 'BackOrder'
ELSE 'Not a BackOrder'
END AS [Orders Backorder]
,
 CASE WHEN
      OrderStatus in ('Billed','Partially Fulfilled','Pending Billing','Pending Billing/ Partially Fulfilled')
 THEN 'Shipped' ELSE 'Not Shipped' END AS [Orders Shipped]
FROM test;
GO

Orders Backorder | Orders Shipped
:--------------- | :-------------

db<>fiddle here


Select different dates from mysql I have a database structure like this

ID | title | startTime | endTime

And what I need is to get 3 records from the database where the first recorder is following this structure DAY(NOW())-1 the second record has to be like DAY(NOW()) or the first upcoming record and the third has to be like DAY(NOW())+1
What I was able to do is this:

SELECT DISTINCT title, FROM_UNIXTIME(startTime) as start FROM tl_calendar_events
WHERE MONTH(FROM_UNIXTIME(startTime)) = MONTH(NOW())
AND DAY(FROM_UNIXTIME(startTime)) >= DAY(NOW())
ORDER BY DAY(FROM_UNIXTIME(startTime)) ASC LIMIT 3;

But this will return the first 3 upcoming records. The startTime and endTime are all unix timestamp fields. So the final output of this query would look something like this if I take that the date is: 06-12-2015:

Record #1 (startTime - 10-11-2015)
Record #2 (startTime - 06-12-2015)
Record #3 (startTime - 12-01-2016)

Also I need some sort of a DISTINCT select so that if it happens I have like 3 records with the on the same day I only select one. I figured I could do this with 3 joins but just ended up creating a lot of mess. I hope you understood my question and can provide some help.

SAMPLE DATA:

Event #1 (04-11-2015)
Event #2 (06-12-2015)
Event #3 (10-12-2015)
Event #4 (12-03-2016)
Event #5 (17-04-2016)

SAMPLE OUTPUT

EVENT #2 (as it is the first one before the current date)
EVENT #3 (as it is the current date or the first closest match)
EVENT #4 (as it is the first one to come after event #3)

I hope this will help explain my situation much better.

Please use yyyy-mm-dd to avoid confusion (mm-dd-yyyy vs dd-mm-yyyy) and be consistent with MySQL.

Does this give you the first item you want?

SELECT *
    FROM tbl
    WHERE ts < CURRENT_DATE()
    ORDER BY ts DESC
    LIMIT 1;

If so, does this give you the other two:

SELECT *
    FROM tbl
    WHERE ts > $foo
    ORDER BY ts
    LIMIT 2;

given that $foo is the timestamp you got from the first query.

I assume your ts is of datatype TIMESTAMP, not INT.  DATETIME would also do.

If you need to display dd-mm-yyyy, use DATE_FORMAT in the SELECT, but not for internal calculations.


SQL Server 2008 R2 - Error 18456 State 12 - nothing I find helps me First off let me state that I'm not a DBA, I don't have access to a DBA, I'm just a programmer who has to get a CMS working. I'm generally pretty good at getting random servers working, but this is doing my head in.

I have a trail of SQL Server 2008 installed, with a database that I imported from a backup. The CMS starts up, connects to the DB but tells me

  [SqlException (0x80131904): Login failed for user 'kentico'.]

Checking the logs shows me

  Error: 18456, Severity: 14, State: 12.

I googled it, and every answer has state 11 and 12 in lumped together, and I think I've done what is mentioned in the posts. but really im just stumbling in the dark. I don't even know what other information I can provide that will help people help me!

I have created a user in in the \security\logins folder, and this login is mapped to a user of the same name in the \databases\<db_name>\security\ folder. What else do I have to do?!

I should say that changing the connection string to the sa account gets me in to the cms, but then I'm only allowed one connection on the sa account so the CMS randomly spits the dummy when the connection is refused.

Any help would be greatly appreciated, and please remember, this is my first time I've even looked at SQL Server, so the assumed knowledge is just not there :(

SQL Server operates in 2 security modes: "Windows" or "Mixed"

Ensure you are set to "Mixed" as per this MSDN article: How to: Change Server Authentication Mode

The clue is in the error:

Login failed for user 'kentico' is not the same as Login failed for user 'DOMAIN\kentico' which means a SQL login (not Windows)
Error 18456 is for Windows logins

Ergo, your mode is wrong...

Our very own Aaron Bertrand has a good article on the error 18456 states


MySQL index on column comparison How do you go about setting an index to be used for a column comparison like WHERE col1 > col2?

col1 and col2 are of DATETIME type.

Suppose the table's layout is as follows:
CREATE TABLE mytable
(
    id int not null auto_increment,
    col1 datetime,
    col2 datetime,
    primary key (id)
);

You may want to try one of two things
IDEA #1 : Create a column to store difference in seconds
Create the column and populate as follows
ALTER TABLE mytable ADD COLUMN dtdiff INT NOT NULL;
ALTER TABLE mytable ADD INDEX (dtdiff);
UPDATE mytable SET dtdiff = UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)l

If your table does not have foreign keys or constraints, do this instead:
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new ADD COLUMN dtdiff INT NOT NULL;
ALTER TABLE mytable_new ADD INDEX (dtdiff);
INSERT INTO mytable_new
    SELECT id,col1,col2,
    UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)
    FROM mytable
;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

Once done, your query would essentially

SELECT * FROM mytable WHERE dtdiff > 0 for col1 > col2
SELECT * FROM mytable WHERE dtdiff < 0 for col1 < col2

IDEA #2 : Create a separate table to store difference in seconds
CREATE TABLE mytable_dtdiff
(
    id int not null,
    dtdiff int not null,
    primary key (id),
    key dtdiff (dtdiff)
);
INSERT INTO mytable_dtdiff
SELECT id,UNIX_TIMESTAMP(col1) - UNIX_TIMESTAMP(col2)
FROM mytable;

Once done, your query would essentially
SELECT B.* FROM
(SELECT id FROM mytable_dtdiff WHERE dtdiff > 0) A
LEFT JOIN mytable B USING (id);

for col1 > col2 and
SELECT B.* FROM
(SELECT id FROM mytable_dtdiff WHERE dtdiff < 0) A
LEFT JOIN mytable B USING (id);

for col1 < col2
Give it a Try !!!


How To Profile Stored Procedures I am using SQL Server 2012 and was wondering how to profile stored procedures

For instance can profiler capture each individual SQL statement in a stored procedure, what it is, and how long it takes to run etc?

I am trying to diagnose merge replication stored procedures, and this must be captured a part of a full run of the merge agent. It doesn't seem possible to grab the stored procedure with performance issues and run it again because at that point it is not slow.

Kevin's answer describes what events to capture in SQL Trace/SQL Profiler. To expand on that answer a bit - SP:StmtCompleted will show you each statement within a stored procedure being completed, as it sounds.

Also if you are on a busy system and trying to diagnose a performance issue you should be careful with SQL Profiler. SQL Profiler is much slower than tracing to a file or using Extended Events. This blog post by Jonathan Kehayias shows about a 90% overhead on a system's performance from using SQL Profiler and about a 10% overhead from tracing to file. Less for Extended Events. That is why it is typically recommended to not run SQL Profiler itself whil

While this information is available through Extended Events, I would suggest still using SQL Trace (the technology behind SQL Profiler) but tracing to a file instead (if you wanted to invest in learning and using Extended Events this would be the way to go, in a future version of SQL Server SQL Trace will be gone and all we'll have is Extended Events) . I would also suggest you filter through the Column Filters button out as much background noise as possible to make sure you are only capturing what is necessary. You can setup your trace with the Profiler tool using the steps Kevin describes in his good answer and then add a filter from that same GUI. Then you can export the trace as a script and run that script on SQL Server tracing to a file on a folder that doesn't contain database or transaction log files. To export, you would simply setup your trace, run it for a few seconds to just make sure you are capturing what you want, stop it and then go to the menu bar and File -> Export -> Script Trace Definition and save the file. Then open that file in a new query window on the server you intend to trace. You can see more about the options and definitions of this script you created by looking at the help articles for the various stored procedures used in that script you just created by starting here.

If you have time and want to learn, you can also read some articles on Extended Events and see how to capture the information as well. Jonathan Kehayias is a great resource for blog posts when you are ready to start there.


What is the apgcc schema in Amazon RDS Postgresql? Recently a mysterious new schema called apgcc appeared in every database in our testing Aurora Postgresql cluster (eu-west region). It is owned by the rdsadmin account. What is it, and where did it come from?

I am aware of a reference to apgcc in a launch announcement for Tokyo region. Twitter reveals that this has happened to others.

I received the answer from Kevin Jernigan of Amazon:

  A recent change in the Aurora PostgreSQL infrastructure made some internal schemas visible to customers. The “apgcc” schema is used for internal maintenance, and we are happy to drop it for you if it is causing an issue. We will address this in a future release.


Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking:
One important query which takes 100ms on version 11 (same VM) now takes about 36s on Postgres 12. That's more than 300 times slower.

My suspicion is, that the new handling of CTEs, namely the MATERIALIZED, NOT MATERIALIZED thing is responsible for that.

If I change every CTE to MATERIALIZED, the query goes down from 36s to 6s. Significantly better, but still more than 50 times slower than in version 11.

If I get it right, in PostgreSQL 12 you have two alternative options:

with MATERIALIZED the CTE is just executed once but you lose the benefit of indexes
with NOT MATERIALIZED you get the benefit of indexes, but your CTE gets executed each time its results are accessed.

Is that correct?

Is there any trick, e.g. a special setting to go back to the Postgres 11 behavior?
Or is the only way to handle this manually evaluating each and every CTE if MATERIALIZED or NOT MATERIALIZED is better?

Quite often, I guess, it is not clear which way is better. My application contains hundreds of CTEs, many of which do both table queries and expensive function calls (the example in the docs where they say that NOT MATERIALIZED is better).

Edit:
What I have checked for making results comparable:

Same Virtual Machine
Same and Very small dataset
Same postgresql.conf
Re-indexed
vacuum analyze

Results of EXPLAIN ANALYZE:
Postgres 11
Postgres 12

As pointed out in the answer to my other, more specific question, the reason is Just-in-time compilation.

SET jit = false; solves all performance problems in my case.


What does N'[Uncategorized (Local)] mean? The following code is seen in several Q&A code snippets here (and elsewhere),  I am trying to translate the section N'[Uncategorized (Local)]
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',
       @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

Examples

SQL Job in idle state
Copy files using script to a network share

I have looked at Microsoft EXECUTE (Transact-SQL)

'name'
Is a valid user or login name. name must be a member of the sysadmin fixed server role or exist as a principal in sys.database_principals or sys.server_principals, respectively.
name cannot be a built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

and

[N] 'command_string'
Is a constant string that contains the command to be passed through to the linked server. If the N is included, the string is interpreted as nvarchar data type.

And I am guessing the section N'[Uncategorized (Local)] goes and gets the name of the sysadmin and passes it to execute the job as the sysadmin, but I am not finding a good reference that indicates how all three segments are working together.

The @name parameter is a parameter of msdb.dbo.sp_add_category and not EXEC.

See the sp_add_category documentation:

  [ @name = ] 'name'

  The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.

[Uncategorized (Local)] just indicates a default category when you didn't specify a category on the SQL Agent Job. The leading N just indicates the string is nvarchar and not varchar.

Please read through Organize Jobs to learn more about job categories

The code you are looking at is most likely just a scripted out job. For example when I right-click a random job on the SQL Server I'm looking at and script job as > create > new query editor window this is the result:

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Warmup_SSRS_EP',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=3,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Warmup_SSRS_EP',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'domain\user',
        @notify_email_operator_name=N'monitor operator', @job_id = @jobId OUTPUT

Where the creation of the category is just included in the script to make sure it exists because otherwise the following procedures would error out.


wild card for entry id mysql So I have a blog and some of my entries are set to "members", but I want to set them to "everyone".  I originally thought it was only a few entries and so I thought to change them manually using PhpMyAdmin, but quickly realized I had more than I thought.

When using PhpMyAdmin I noticed when making the change manually (point and click) this was the command it was using (see below).  I'd like to repeat this but for ALL entries.

Is there a wild card I can use for the entry_id value?

UPDATE `database_name`.`blog_entry`
SET `allow_view_entry` = 'everyone'
WHERE `blog_entry`.`entry_id` = 4624;

Well, if you want to update ALL entries, why don't you just get rid of the condition, and execute:

UPDATE blog_entry SET allow_view_entry = 'everyone'

Otherwise, you can use a wildcard indeed, and build your statement like this:

UPDATE blog_entry SET allow_view_entry = 'everyone'
WHERE entry_id LIKE '%245%'

using the common wildcards that go with the LIKE operator.


duplicate key error creating unique index on temp, SQL Server 12.0.5589 bug? I have a job that runs a stored procedure, that seems to intermittently fail with a duplicate key error when creating a unique clustered index on a temp table.   How is it possible for me to get this error based on the code below.

It seems to happen when the source table is being updated.  Transaction Isolation Level is set to read uncommitted, and there ARE some triggers on "SomeTableWithPrimaryKey". It doesn't happen every time, just three times in the last 100 runs. There are about 3 million rows in the source table "SomeTableWithPrimaryKey", and about 500k of them are being selected after the filters are applied.

Is this a SQL Server Bug?  Some race condition?

This statement is based on a true query, the names have been changed to protect the innocent...and my job. :)

--on SQL Server 12.0.5589
SELECT  p.PrimaryKeyColumn, p.SomeOtherColumn
INTO    #Temp
FROM    dbo.SomeTableWithPrimaryKey p
WHERE   p.SomeCondition = 1
        AND (p.SomeCondition2 = 2
            OR EXISTS(
                SELECT 1
                FROM    dbo.SomeTable st
                        INNER JOIN dbo.SomeOtherTable sot
                            ON st.Column1 = sot.Column1
                WHERE   st.SomeCondition1 = 1
                        AND st.PrimaryKeyColumn = p.PrimaryKeyColumn
                )
            )
;

--productid index
CREATE UNIQUE CLUSTERED INDEX CX_Temp ON #Temp(PrimaryKeyColumn)
;

^this is where we get the error:

  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.#Temp______...000000D5A436' and the index name 'CX_Temp'. The duplicate key value is (123456789).
  [SQLSTATE 23000] (Error 1505)  The statement has been terminated.
  [SQLSTATE 01000] (Error 3621).  The step failed.

When using the READ UNCOMMITTED isolation level, it is possible for SQL Server to read the same data twice - causing you to get duplicate results, even if there is a unique constraint ensuring there are no duplicates of that data in the table.

Looking at some of the comments on the question:

  Thanks, the general consensus seems to be the iso level, but keep in mind, i've probably used that same basic query above in hundreds of places, and have never seen this behavior before

This won't happen all the time, and can depend a lot on whether the source table is being updated, the physical operations SQL Server chooses to use to read the data, and the nature of the query being run.

  I will change the iso level and watch it (for possibly a year) to see if we get the same error in the future

That's great, changing the isolation level is your best option for preventing this problem!

When you have time, there is some great information out there on the issues around using the READ UNCOMMITTED isolation level:

Paul White's post (in a series about isolation levels): The Read Uncommitted Isolation Level
A blog post from Brent Ozar with a very clear demo of how results can change under READ UNCOMMITTED: Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.