Unexpected Shutdown affects SQL Server DB start at OS start up I have an application that uses a sql server db (SQL Express 2008 R2) and starts when Windows OS starts. When the pc shutdown unexpectedly and then restart the pc the application run automatically but it' can't query the DB
I looked for the error log and it shows no record for starting the DB and I have
to open the SSMS and query the DB. Only after that the db will start (even after seconds from starting the OS)

2018-02-28 13:12:51.05 Server      Server process ID is 2420.
2018-02-28 13:12:51.50 spid7s      Starting up database 'master'.
2018-02-28 13:12:55.60 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2018-02-28 13:12:58.87 spid52      Starting up database 'ReportServer$SQLEXPRESS2008R2TempDB'.
2018-02-28 13:35:27.38 spid57      Starting up database 'DBName'.

as you can see there are about 23 minutes between starting master and my db.
I also checked the service and it's running fine in the OS start.

I have 2 questions:
1- What SQL Server make to start the db and can I do that programmatically?
2- I noticed that starting 'ReportServer$SQLEXPRESS2008R2TempDB' is the beginning of the delay and it may be the cause of the delay, so can I change the order of starting the databases on the server?

Thanks in advance

Your DB has AUTO_CLOSE ON (which is typical of SQL Express instances, like yours). It means that the database is not opened until first accessed. This is when the first mention of it appears in the SQL ERRORLOG file.

The delay of 23 minutes is because your application is not opening the database. If you think this is a problem, it must be in your app. Are you sure your app, which must be an NT service, is configured to auto-start on OS boot?


SQL Query with Aggregate Count, where at least 1 of the counts has a value in a field SELECT PERSON_ID,COUNT(APPL_ID) COUNTS
FROM APPLICATIONS
GROUP BY PERSON_ID
ORDER BY COUNTS DESC

The above query returns all persons who have applied for all time (essentially a count of the whole table).
I want a clause to limit this to persons who have applied this year, yet still show the count of ALL their applications...
IE, a person may have applied in 2018 once, but they've applied 20 times total thus the results would be similar. Skipping over id's 1,2, 4-11.

ID | COUNT
---+-------
003|     20
---+-------
012|      4

I suspect you can do it using a subquery, something like:

SELECT PERSON_ID, COUNT(APPL_ID) COUNTS
FROM APPLICATIONS
WHERE PERSON_ID IN (
    SELECT DISTINCT PERSON_ID
    FROM APPLICATIONS
    WHERE APPL_DATE > 2018-01-01
)
GROUP BY PERSON_ID
ORDER BY COUNTS DESC


How to remove a table from a FOR ALL TABLES logical replication PUBLICATION in PostgreSQL I created a logical replication PUBLICATION using FOR ALL TABLES. Now I've realized I don't want to replicate all of the tables after all. I tried dropping one of the tables from the publication, but:

mydb=# alter publication replicapublication drop table only migrations;
ERROR:  publication "replicapublication" is defined as FOR ALL TABLES
DETAIL:  Tables cannot be added to or dropped from FOR ALL TABLES publications.

I've done a huge sync of hundreds of GB, and I don't want to start over with a new publication.

Questions:
1. Is there any hope of redeeming this publication?
2. If not, is there any way to create a new publication that's fully synced without starting over with a full sync?

Thanks.

For 1, no, I don't think for.  For 2, possibly but it seems fraught.

I naively thought that creating a new publication listing your tables, and then on the subscribing end disabling the old subscription and creating new one using the same slot, all in one transaction, would work.  But doing an experiment, this does not work if the publisher is under load.  It ends up losing some changes. However, it does appear to work if the publisher is quiescent and the subscriber is caught up at the time of the swap.

-- warning: does not work transactionally!
begin;
alter subscription sub disable;
alter subscription sub set (SLOT_NAME = NONE); -- used to be 'sub'
create subscription sub2 CONNECTION '' PUBLICATION pub2 with (
    copy_data = false,
    slot_name='sub',
    create_slot=false) ;
commit;

I can't decide if this is a bug or not.  I don't see anything in the documentation which excuses this non-transactional behavior, but I did not read the entire thing carefully.


Find objects linked to a PostgreSQL role Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9).

I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ownership too:

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM user1;

REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM user1;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user1;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM user1;

REASSIGN OWNED BY user1 TO postgres;

However it seems that one object remains linked to this user in 2 databases:

postgres=# DROP ROLE user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  1 object in database db1
1 object in database db2

It even seems to be a function:

postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# DROP ROLE user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for function text(boolean)
1 object in database db2

But I can not determine which object is owned or related to user1.

If I pg_dump -s db1 | grep user1 I get no result! Could it be a global object?

How can I identify the missing object?

I have executed the commands in each database (db1 and db2). I do not want to drop objects owned by user1, just want to reassign or remove grants for this user.

Answer to question asked

To look for the function in the error message and its owner:

SELECT oid::regprocedure AS function
     , pg_get_userbyid(proowner) AS owner
FROM   pg_proc
WHERE  oid = 'text(boolean)'::regprocedure;

Related:

DROP FUNCTION without knowing the number/type of parameters?

Actual problem

The error message says:

  DETAIL:  privileges for function text(boolean)

It's not about ownership but about privileges.

The manual for DROP ROLE:

  Before dropping the role, you must drop all the objects it owns (or
  reassign their ownership) and revoke any privileges the role has been
  granted on other objects.

And for ALTER DEFAULT PRIVILEGES:

  If you wish to drop a role for which the default privileges have been
  altered, it is necessary to reverse the changes in its default
  privileges or use DROP OWNED BY to get rid of the default privileges
  entry for the role.

It also looks like you only executed REASSIGN OWNED in one DB, but the manual instructs:

  Because REASSIGN OWNED does not affect objects within other
  databases, it is usually necessary to execute this command in each
  database that contains objects owned by a role that is to be removed.

Bold emphasis mine.

And you restricted your commands with IN SCHEMA public. Drop that clause to target the whole DB. But don't bother, there is a ...

Simple solution with DROP OWNED

REASSIGN OWNED BY user1 TO postgres;
DROP OWNED BY user1;

All the role's objects changed ownership to postgres with the first command and are safe now. The wording of DROP OWNED is a bit misleading, since it also gets rid of all privileges and default privileges. The manual for DROP OWNED:

  DROP OWNED drops all the objects within the current database that are
  owned by one of the specified roles. Any privileges granted to the
  given roles on objects in the current database and on shared objects
  (databases, tablespaces) will also be revoked.

Repeat in all relevant DBs, then you can move in for the kill:

DROP ROLE user1;


Regularly import tab-seperated log file to MySQL I'm looking to keep arpwatch entries in a MySQL database to crossreference with other information I'm storing based on mac addresses. I've manually imported the arpwatch database into my mysql database, but being a novice with databases I'm not sure what the best way to continually update the database with new entries without creating duplicates would be. None of the fields can be unique, as even the time is duplicated frequently.  I'm not interested in the actual arpwatch events like flip flop or new station, just the mac/ip/time pairings.

Would a simple bash (or sql) shell script do the trick?
Would it be possible to make the mac address plus the time be a composite key of some sort?

the database is called utility, table is arpwatch, columns are mac, ip, time
a separate table named 'hosts' with columns mac, ip, type, hostname, location, notes has mac as the primary key. This table will correlate different ip addresses that a mac had over time using the arpwatch column
initial import was done with MySQL Workbench using INSERT INTO commands with creative search and replace on the text file

Sure, a simple script can parse the results of the tool and insert rows into the database.

You can make composite unique keys in mysql:

 CREATE index foo (mac, time);

If you try to insert a dup, it will fail (but will you ever have a duplicate time?).


How can I design a relational database with words that are all related to each other I'm developing a simple language game about antonyms, synonyms and 'similar speaking' words.

If you play in the 'Synonyms screen', the game will show you two synonyms and two unrelated words and you have to guess wich are the synonyms.
If you play in the 'Antonyms screen', the game will show you two antonyms and two unrelated words and you have to guess wich are the antonyms.
If you play in the 'Speaking screen', the game will show you two 'similar speaking' words, will speak one of each and you have to guess wich is.

My problem is that you can have more than one synonyms, like:

angry: enraged, furious, irated, offended, etc..
love: affection, passion, devotion, etc..

My first idea is something like this one:

Table Synonyms:

+-----------+-------------------+
|   word    |     related       |
+-----------+-------------------+
| angry     |   enraged         |
| angry     |   furious         |
| angry     |   irated          |
| angry     |   offended        |
| enraged   |   furious         |
| enraged   |   irated          |
| enraged   |   offended        |
| furious   |   irated          |
| furious   |   offended        |
| irated    |   offended        |
| love      |   affection       |
| love      |   passion         |
| love      |   devotion        |
| affection |   pasion          |
| ....      |   ....            |
+-----------+-------------------+

And build the antonyms and speaking tables in the same way.

But it seems like a lot of work and it's difficult to add/remove one synonym.

Also, I don't know where I can put the information about how to speak every word. Maybe another table like:

+-----------+-------------------+
|   word    |  pronuciation     |
+-----------+-------------------+
| angry     |   ˈaNGgrē         |
| enraged   |   enˈrājd         |
| ....      |   ....            |
+-----------+-------------------+

The associative entity idea is a good way to handle this, but it has a drawback that you might want to consider.

Something that hasn't been addressed in some other answers is how you're going to maintain all of this data. It's going to be a lot of work, particularly if you want to be able to get from one word to any of its synonyms (or antonyms) without knowing which of your words you're starting from.  Consider your example of {angry, enraged, furious, irritated, offended}.  That's a set of five synonyms.  If you used associative entities you'd have these pairs: angry=enraged, angry=furious,... etc. You'd also have to do them the other way around: enraged=angry, furious=angry, and so forth.

Five synonyms isn't a lot.  However, if you make an associative table for 5 synonyms you're going to have 10 entries. For 6 synonyms you'll have 15 and for 7 synonyms you'll have 21. You can see how this might get out of hand.

Alternative: Association via a root
The good news is that there's another way to do this.  If you pick one of your synonyms and make it the base or root word (say in your case "angry") then you can have one record for each of these words in one table (five synonyms = five records, include "angry" in your list of words) and then a second table containing just the roots (for example "angry")

The key (pun intended) is to have the list of words point at their base or root word.  This would be via a foreign key.

So in your word table, you might have something like this:

angry = angry
enraged = angry
furious = angry
irritated = angry
offended = angry

Now, you might want to use a meaningless numerical key instead of "angry" in the second column in each of the above.  That might make things easier for a variety of practical reasons.  In that case you'd have a second table with just the base words with their meaningless numerical keys, for example:

123, angry

So your word table looks more like this:

angry, 123
enraged, 123
furious, 123
irritated, 123
offended, 123

So how do you find synonyms for a word?

That's a simple process, but it's a two step* process:

Look up the root key for the given word.  Say you want to find synonyms for "furious". You look it up and find the root is number 123.
Look for all the words that have the same root key as your word, these are your synonyms.

The advantage of doing it this way is that it saves you a ton of data maintenance. You only have to maintain one record for each word, plus one for each group of synonyms.  That's a lot fewer than combinations of all synonyms, which is what you're looking at if you do this with an associative entity.

*Note: you don't actually have to do this in two steps. You can write a SQL which combines all of this into a single statement using a self join.


How to replicate data between two SQL Server 2012? I have two SQL Server 2012 databases running on two different Windows Server instances, I want the Server A (DB1) to be in full sync with the new Server B (DB2).

How can I make this connection between them?

Is there any way to do this through SQL management Studio??

Note: whatever I do on Server B shouldn't replicate to Server A.

Lets take a quick look at the official Replication features page:

Transactional replication. For more information, see Transactional Replication.
Merge replication. For more information, see Merge Replication.
Snapshot replication. For more information, see Snapshot Replication.

In short:

Transaction replication: incremental changes between the databases.
Merge replication: like transactional but with the subsequent changes.
Snapshot replication: means a full copy/overwrite of the given database. Basically this is used for creating the initial replicated instance of your database.

You will also have to overview and setup the SQL server agents for replication.
For a basic technological understanding the SQL Server replication mechanism utilizes the Publisher-subscriber pattern.
EDIT: i was intentionally mentioning the used pattern for replication, let me elaborate on it a bit more.
Architectural overview of the basic transactional replication:

Summarizing simplified the flow of operations:

Publisher (in your case DB-1, the source database server) makes the data ready for replication.
Distributor (the chosen replication agent) is the intermediary between the publisher and subscriber (note the pattern mentioned above).
Subscriber (in your case DB-2, the destination database server) which receives the published data.

This way you replicate your data from DB-1 to DB-2, and not backwards (though you can configure it to do that too, if you may want).


Compilation of T-SQL oddities and pitfalls Like all programming languages, T-SQL has its oddities when compared to a generic programming language.  Some of these have tripped us up.

It would be nice if there were an already-compiled list so that we could be forewarned of these pitfalls before stepping into them.  What are some T-SQL oddities that we need to be aware of?

We don't mean an exhaustive study of all the specific structures, functions, and constructions unique to the SQL language.  We mean a concise list of the places where SQL diverges consequentially from other standard programming languages or from the usual and expected conventions of programming.  Things that a reasonably skilled programmer coming from another language could be expected to stumble over.

Please contribute to the wiki answer.

Trailing spaces are ignored by some functions and operators, such as LEN() and equality.  The first query returns 5, 5, 5, 5, 5, 5.  The second query returns EQUAL.  These results can be quite surprising to a new SQL programmer.

SELECT   LEN('Hello') AS [0 trailing spaces],
         LEN('Hello ') AS [1 trailing spaces],
         LEN('Hello  ') AS [2 trailing spaces],
         LEN('Hello   ') AS [3 trailing spaces],
         LEN('Hello    ') AS [4 trailing spaces],
         LEN('Hello          ') AS [10 trailing spaces]
;

SELECT   CASE
              WHEN 'Hello' = 'Hello    ' THEN 'EQUAL'
              ELSE 'NOT EQUAL'
         END
;

Application of negative and positive signs has lower operator precedence than multiplication and division.  This causes products preceded with negative signs to be evaluated first.  Thus, the first expression in the query below yields 9.0, whereas the second expression yields -1.0.

SELECT   6.0/2.0*3.0,
         6.0/-2.0*3.0
;

Casting a blank string to integer, bigint, or float results in zero, whereas casting a blank string to decimal or numeric produces an error.

SELECT   CAST('' AS INT) AS [INT],
         CAST('' AS BIGINT) AS [BIGINT],
         CAST('' AS FLOAT) AS [FLOAT]
;

SELECT   CAST('' AS DECIMAL(15,6)) AS [DECIMAL],
         CAST('' AS NUMERIC(15,6)) AS [NUMERIC]
;

SQL has one of the most strongly defined NULLs of any programming language.  It nearly constitutes a third logic value (either "missing" or "non-applicable") in a three-valued logic, rather than merely an indicator of "uninitialized variable".  A three-valued logic can produce surprising results to those who are more accustomed to two-valued logic.  The queries below will not necessarily return every row in the table.  Rows in which COL1 is NULL will not be returned.

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    COL1 = 5
         OR COL1 <> 5
;

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    COL1 = 5
         OR NOT(COL1 = 5)
;

The strongly-defined NULL mark and three-valued logic can cause bafflement in more subtle ways.  The first query below will not return any rows, even when COL1 contains a value not in the excluded list (e.g., 6 or 7 or 8).  The second query will return all rows, as expected, but the third query will never return any rows.

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    COL1 NOT IN (1, 2, 3, 4, 5, NULL)
;

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    1 = 1
;

SELECT   *
FROM     EXAMPLE_TABLE
WHERE    NULL = NULL
;

You'd better be sure you understand scale and precision and how they interact.  Otherwise, the query below will make you recoil in horror.

SELECT   CAST(0.0000006 AS DECIMAL(38,22)) * CAST(1.000000 AS DECIMAL(38,22))
;

More of unary arithmetic operator irregularities. When applying multiple sign operators in a row, the standard notation prescribes that you use brackets: -(-(-2)). In Transact-SQL, however, you can omit the brackets:

SELECT  -1,  - -1,  0- - -1;

-- results: -1, 1, -1

Note that you have to put spaces between the - signs, otherwise a -- will be interpreted as a line comment symbol. However, you do not have to put spaces between multiple unary + signs:

SELECT  +1,  ++1,  0+++++++++++++++++++++1;

-- results: 1, 1, 1

Finally, you can also apply a unary + to a string! This will compile just fine:

SELECT
  +'This is a positive note',
  +++'Super+positive note',
  'I'++++++' love'+++++++++' plus(s)es'
;


Is it more efficient to union from sub-query or combine two outer queries? I searched for this but couldn't find the response I needed.  So my apologies if this has already been asked.

I'm asking in regards to DB2 but I suppose this would apply, generally speaking, to just about any database.

Anyway, say I have two tables in the same database:

select ID, EMAIL from TBL1
select ID, EMAIL from TBL2

What I then want to do is search for a field within those two tables.  The field could be in either table.

So, I have tried the following:

select ID, EMAIL from TBL1 where EMAIL = 'blah@aol.com'
union
select ID, EMAIL from TBL2 where EMAIL = 'blah@aol.com'

This works.  But is redundant and ugly.

However, is it more efficient than:

select T.* from
(
    select ID, EMAIL from TBL1
    union
    select ID, EMAIL from TBL2
) T where T.EMAIL = 'blah@aol.com'

Both seem to work.  Unfortunately, I don't have any real profiling tools for DB2 (along with restricted access) so I can't seem to figure out which is better.  They both execute at roughly the same speed in the console but that isn't a good indicator.

Any tips on this (or even how to maybe profile it myself) would be appreciated.

Of course, this is a highly simplified example.  The real queries are quite massive and span 9-18 tables, databases, etc.

Thanks

EDIT

The DB2 is on iSeries and I have no idea what version it is.  Everything I try to use gives an error.  I want to say it's version 8 but I really don't know.

If anyone wants to clue me in on how to give more information I will.  I don't know much about DB2.

You need to use the explain facilities in DB2 to answer this question, it's highly specific to the actual queries you are executing, and you can't get a general answer.

IBM i 7.2 (and previous versions) have Visual Explain.

If you are restricted from doing this, then you need to speak to the system or database administrator for your environment.


Postgresql: Storing a table index on a separate disk to the data I have development a postgresql 9.5 database which I originally set up on an SSD. However, as this project will grow to ~200TB (and likely more), I have moved my current test of ~200GB on to a HDD, which will better represent the hardware that will likely be used in the real database, when it is built.

The main tables that are queried are currently 110 million and 785 million rows, and almost all the time is spent on table index scans. Therefore, I would like to know if it is possible (and if so how) to store all the table indexes on an SSD, and the actual raw data on the HDD? I believe this would allow me to keep the improved read performance of the SSD on the index scans, and be able to keep the large volume of data on cheaper HDDs.

You can change the tablespace of the index explicitly with alter index command.

CREATE TABLESPACE superfastssdtablespace LOCATION '/path/to/super/fast/ssds';
ALTER INDEX name_of_the_index SET TABLESPACE superfastssdtablespace;

Before you do that you may (also) want to look at partitioning your table or using BRIN indexes depending on the use case.


Can we use innodb to store latitutude and logitude and retrieve 20 closest points well? I've heard that innodb Why innodb doesn't store geospatial data? store geospatial data but cannot index it.

Not really sure what it means. Is it a good idea to ask 20 closest businesses if point is stored in innodb?

An efficient, but complex, solution:
http://mysql.rjweb.org/doc.php/latlng
(The design actually prefers InnoDB.)


PostgreSQL Trigger to Update Inserted Row With Data From Another Table I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data).
I am trying to create a PostgreSQL trigger to update the sensor data as they are inserted into SENSORS. Each row of sensor data will be updated with the corresponding label name from LABELS. Unable to get the row update to work. Sample shown below:
Sample Dataset
CREATE TABLE sensor (
   datavalue integer,
   created timestamp,
   label_id integer,
   label_name text);

CREATE TABLE labels (
   id integer,
   label_id integer,
   label_name text);

Insert into labels values (1, 215, 'Home'), (2, 216, 'Shop'), (3, 217, 'Flat'), (4, 218, 'Street');
Insert into sensor values (67, '2020-09-02 08:40:07.354', 215),(67, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

Trigger Function
CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$func$
BEGIN
UPDATE sensor
    SET label_name = b.label_name
    from labels b
    where new.label_id = b.label_id;
     RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
AFTER INSERT OR UPDATE ON sensor
FOR EACH ROW EXECUTE PROCEDURE update_name();

Test row insertion
Insert into sensor values (78, '2020-09-02 08:40:07.354', 215),(77, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

I have a very large number of rows flowing in, and would like to update each row as it is inserted.
Any assistance greatly appreciated. Thanks!

Most likely you're going in wrong direction but I have only gut feelings for that.
But, you can play with triggers - maybe it will do a work. However it seems that you will have massive updates in each trigger call because you are updating all rows from sensor with new label, which seems just wrong.
As I'm not sure about your specific needs, I will just push you in most proper way (I hope) with these changes:

CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO sensor(datavalue, created, label_id, label_name)
        SELECT NEW.datavalue, NEW.created, NEW.label_id, b.label_name
        from labels b
        where new.label_id = b.label_id;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE sensor
        SET label_name = b.label_name
        from labels b
        where new.label_id = b.label_id;

    END IF;
    RETURN NULL;

END
$$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
BEFORE INSERT OR UPDATE ON sensor
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE update_name();

Here, you do these changes instead (before) insert or update. Insert will be proper, update needs to be adjusted or removed as from your description it seems like you will be making only inserts here.
And please note that there is limiter as proposed here https://dba.stackexchange.com/a/103661/213360.


Domain group as single login, but still many different database users? We want to use Windows Authentification on SQL Server 2008 within Active Directory.

Is it possible to use an AD Group as a LOGIN to the server, but then to map the actual AD USER to a database user?

The idea is to have one AD group which has the basic login access, but then to fine-granulate roles and access rights to a list of single database users.

We also are using row-level-security such that every single user will have access to one single country.

First of all, the way you are wanting to do it won't really work.  You can't have one login and multiple users on a single database.  If you try you get the following error:

Msg 15063, Level 16, State 1, Line 1
The login already has an account under a different user name.

What you really should do is create your generic group and create a login and users for it.  This will provide the basic permissions for everyone.  Next create an AD group for each country you are dealing with.  Add your individuals to these groups.  Then create logins and users for those groups providing them the individual access you want.

The benefit here is that if someone leaves the company all you have to do is move them out of the AD group and move someone else in.  Or if a second person (or more) has to be added to a country it's a trivial thing.

EDIT:
For example:

AD Group : DOMAIN\Sales

Users: Bob and Joe

AD Group: DOMAIN\Sales_US

User: Bob

AD Group: DOMAIN\Sales_Canada

User: Joe

All three groups have SQL Logins created
All three groups also have a user created on the Sales database.

The DOMAIN\Sales group is granted SELECT access to tables Inventory and InventoryPricing
The DOMAIN\Sales_US group is granted SELECT permission on the view vw_Sales_US and execute on the stored procedures usp_Add_US_Sale and usp_Edit_US_Sale
The DOMAIN\Sales_Canada group is granted SELECT permission on the view vw_Sales_Canada and execute on the stored procedures usp_Add_Canada_Sale and usp_Edit_Canada_Sale

This gives Bob access to the US information and the shared information and Joe access to the Canada information and the shared information.

Later on down the line Mary replaces Bob on US sales and since Canada sales have increased dramatically Bob has joined Joe on Canada sales.

The only changes that need to be made are removing Bob from DOMAIN\Sales_US and adding Mary and adding Bob to DOMAIN\Sales_Canada


Change datatype from DATE to VARCHAR() CREATE OR REPLACE procedure create_table_dateWise27()

LANGUAGE plpgsql

as $$

declare

d_date date;

begin

    select to_char(current_timestamp :: timestamp - interval '1 hour','yyyy-mm-
dd')into d_date;

EXECUTE format('
   CREATE TABLE IF NOT EXISTS %I(
    nodeip text,
    interface text,
    nodeip_interface text
   )','test_'|| d_date-1);

commit;

end;
$$;

call create_table_dateWise27();

I want to perform the same operation where d_date is a VARCHAR().

If you want to put yesterday's date into the variable then do it during assignment. And if you want a varchar, then declare it as one - your current code does an implicit and useless conversion back a varchar back to a date anyway.

So something like:

CREATE OR REPLACE procedure create_table_dateWise27()
LANGUAGE plpgsql
as $$
declare
  d_date varchar;
begin

  d_date := to_char(current_date - 1, 'yyyy_mm_dd'); --<< yesterday

  EXECUTE format('
    CREATE TABLE IF NOT EXISTS %I(
      nodeip text,
      interface text,
      nodeip_interface text
    )','test_'|| d_date);

  commit;
end;
$$;

Note that I changed the format mask for the to_char() call to use underscores (_)  rather than - because a dash is invalid in an SQL identifier (table name) and thus would require the use of a quoted identifier which you should really avoid.


NULL values on MDX query (Total Column has Values) I'm a novice with MDX, so sorry if I've missed something. (and sorry about my english too!)

The following query:

WITH
 SET [ROWMEMBERS] AS '{([BIN_SCH_INV_GRO].&[1]) , ([BIN_SCH_INV_GRO].&[2]) , ([BIN_SCH_INV_GRO].&[3]) , ([BIN_SCH_INV_GRO].&[4]) , ([BIN_SCH_INV_GRO].&[5]) , ([BIN_SCH_INV_GRO].&[6]) , ([BIN_SCH_INV_GRO].&[7]) , ([BIN_SCH_INV_GRO].&[8]) , ([BIN_SCH_INV_GRO].&[9])}'
SELECT {([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year].&[2010])
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year].&[2010])
 , ([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year].&[2011])
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year].&[2011])
 , ([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year])
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year])
 } ON COLUMNS, [ROWMEMBERS] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS FROM [ProductsManagement]

Returns this:

BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP    BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP    BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP
    2010    2010    2011    2011    ALL ALL
ALIMENTACAO (nulo)  (nulo)  (nulo)  (nulo)  2,663,657.70    1,650,353.73
ALIMENTACAO PARENTAL E ENTERAL  (nulo)  (nulo)  (nulo)  (nulo)  1,028,281.08    1,033,596.66
BRINDES (nulo)  (nulo)  (nulo)  (nulo)  163,867.06  187,183.59
EQUIPAMENTOS DE SEGURANCA   (nulo)  (nulo)  (nulo)  (nulo)  497,320.63  372,173.12
FIOS CIRURGICOS (nulo)  (nulo)  (nulo)  (nulo)  1,280,577.64    1,288,735.81
GASES MEDICINAIS    (nulo)  (nulo)  (nulo)  (nulo)  144,900.20  141,914.26
HIGIENE E LIMPEZA   (nulo)  (nulo)  (nulo)  (nulo)  1,895,881.03    1,949,619.64
IMPRESSOS   (nulo)  (nulo)  (nulo)  (nulo)  1,617,042.25    1,652,345.87
LABORATORIO (nulo)  (nulo)  (nulo)  (nulo)  6,672,048.90    6,725,001.30

I'm pretty sure that those rowmembers contain values on the specified years...

Any Ideas?

Thank you!

If you are an MDX novice, you should start simple and layer on complexity as you get the simple queries to work. Try getting just one of the values you're expecting. Once you get that, all the others should fall into place.

Start off with something like this:

SELECT
[BIN_SCH_Year].&[2010]  ON COLUMNS,
[BIN_SCH_INV_GRO].&[1]  ON ROWS
FROM [ProductsManagement]
WHERE [Measures].[BIN_SCH_REC_VAL]

If you find values, build on this query. If you don't, take a look at your relational database and make sure the data is loaded correctly.

Boa sorte!


Why is postgresql still consuming space on my other drive? I am running postgreSQL 8.4 on Ubuntu 10.0.4.

I recently needed to import a large amount of data (approx 300G) into a database. I purchased an additional drive, prepared it for use on my machine, and turned off logging altogether by setting:

log_destination='stderr'

in my postgresql.conf file

I then stopped the current run postgresql service, and restarted it, specifying the data directory. I did that using the following steps:

sudo su - postgres
/usr/lib/postgresql/8.4/bin/pg_ctl -D /path/to/dir/on/new/drive -l logfile start &

I run the psql command line utility and run SHOW DATA_DIRECTORY to confirm that the running postgres service was indeed pointing to the right directory.

I then started my data import, but periodically run df -h to check disk usage on my machine. Although data was being stored to my new drive (as expected), disk space was being consumed at a much faster rate on my old drive.

I don't understand why that would be, since postgreSQL should (AFAIK) have no reason to write anything to my old drive.

Does anyone know why this may be happening, and how to fix this? (i.e. force PG to save data in the specified location only?)

Note:

I did NOT change the data_directory setting in my postgresql.conf. It was still pointing to my old drive - however, I had explicitly restarted the postgres service and specified a new data directory location.

I do not have all information for pointing to the source of your problem, but I think you should create your postgresql cluster in a different way. Instead of using the postgresql generic command pg_ctl, you should use the Debian/Ubuntu provided command pg_createcluster. This way you will have a working setup that survives reboot, you may specify your data directory and your log directory, you may also specify the start policy in order to activate it automatically on boot. And you may use all cluster commands: pg_lsclusters, pg_upgradecluster, and so on.

Every cluster uses three directories:

/etc/postgresql/8.4/CLUSTERNAME, for its configuration
your data directory, for all your data and WAL (default /var/lib/postgres/8.4/CLUSTERNAME)
your log directory, for your logs (default /var/log/postgresql)


How can I best combine "get categories" and "has user access"? I was wondering if it's possible to create a query for the following problem. I only want to select categories from the (MySQL) database (if the user has access).

A user has multiple roles;
A category has multiple roles.

I have the following tables:

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  email VARCHAR(127) NULL ,
  username VARCHAR(16) NULL ,
  password VARCHAR(64) NULL ,
  PRIMARY KEY (id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE roles_users (
  user_id INT(11) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (user_id, role_id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE forum_categories (
  id INT(4) NOT NULL AUTO_INCREMENT ,
  name VARCHAR(30) NULL ,
  description VARCHAR(70) NULL ,
  image VARCHAR(20) NULL ,
  parent_id INT(4) NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE forum_categories_roles (
  category_id INT(4) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (category_id, role_id) ,
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

I removed the relations

Example:

User A has role_id A
User B has role_id B
User C has role_id A and C

category A has role_id A
category B has role_id B and  C
category C has role_id C and B

user A has access to category A
user B has access to category B and C
user C has access to category B and C

If you have the user id upfront, say id 12, then your query is

SELECT u.name username,GROUP_CONCAT('[',fc.name,']') categories
FROM (SELECT * FROM users WHERE id=12) u
INNER JOIN roles_users            ru  ON u.id = ru.user_id
INNER JOIN forum_categories_roles fcr ON ru.rol_id = fcr.rol_id
INNER JOIN forum_categories       fc  ON fcr.category_id = fc.id
GROUP BY u.name;

If you want to see all the categories for every user, then here is that query:

SELECT u.name username,GROUP_CONCAT('[',fc.name,']') categories
FROM users u
INNER JOIN roles_users            ru  ON u.id = ru.user_id
INNER JOIN forum_categories_roles fcr ON ru.rol_id = fcr.rol_id
INNER JOIN forum_categories       fc  ON fcr.category_id = fc.id
GROUP BY u.name;

Both of these queries return a comma-separated list of categories

I would recommend you also add an additional unique index

CREATE TABLE forum_categories_roles (
  category_id INT(4) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (category_id, role_id) ,
  UNIQUE KEY (role_id,category_id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Give it a Try !!!


Purge ARCHIVELOG automatically I have an Oracle 10g XE installation (exclusively used for development) which has accumulated so far 14 GB of files at ...\oraclexe\app\oracle\flash_recovery_area\XE\ARCHIVELOG. I do need that space and I'm tempted to just remove everything using Windows explorer but surely Oracle won't be amused.

Is it possible to configure Oracle XE to purge that stuff automatically as soon as it's no longer needed?

What does it mean "no longer needed"? Archive redologs have "only one" usage, they can be applied onto the last full/incremental database backup to recover the database.

So you either backup your development database and then you can delete them via RMAN during database backup. Or you do not backup your database and then you can run in NO ARCHIVELOG mode.


Are there relations that cannot be normalized to second normal form (2NF)? I came across this example that I can't normalize to 2NF, so I was wondering if relations that could not be normalized existed.

Here is the example relation:

R(A, B, C, D, E)

with the functional dependencies:

{A, B} → D
C → B
E → A

or, diagrammatically:

The primary key of that relation is {C, E}, and if we suppose that all the attributes are atomic, then R is in first normal form (1NF).

But how can we normalize it to 2NF?

Since A and B are not fully dependent on {C, E}, I feel like they should be set aside in two different relations, but then D isn't dependent on anything in R. What am I missing?

Note that the normalization of a relation is an operation that in general is performed to eliminate or reduce anomalies like redundancy, while in general preserving the meaning of the data, and more formally, preserving two essential properties: the dependencies holding on the original data, and the data itself (through the concept of lossless decomposition).

So, the normalization decomposes a relation in a set of relations that are in a so-called Normal Form, and the normal forms used in a decomposition are usually the Boyce-Codd Normal Form, the Third Normal Form, or even higher level ones. For these decompositions, there are formal algorithms that can be used, and that guarantees a set of properties, like being lossless and  preserving the dependency (not in all cases, for instance it is known that certain relations cannot be decomposed in BCNF without the loss of some dependency).

Note that the second normal form is not used in practice, has only an historical interest, and there are no published formal algorithm since it is more weaker that the 3NF and the BCNF.

All these topics are discussed in detail in any good book on databases, and you can find even some of them free on line.

In your example, assuming that the dependencies that you shown are a cover of all the dependencies of the relation, the decomposition of R in the the relations R1(CE) and R2(ABD) does not preserves the functional dependencies (E->A and C->B are lost), and is not lossless.

A decomposition which preserves data and dependencies, and that can be found both with the synthesis algorithm to produce the 3NF, and with the analysis algorithm to produce the BCNF is the following (each relation schema is followed by a cover of the dependencies holding in it):

R1(A B D) {AB -> D}

R2(B C) {C -> B}

R3(A E) {E -> A}

R4(C E) {}

where each schema is in BCNF.

Of course, since a schema in BCNF is also in 2NF, the previous decomposition also respects the Second Normal Form. This answers to your original question: since each relation can be normalized at least in 3NF without loss of data or dependencies, each relation can be normalized at least in 2NF.


Very high I/O on database during a backup maintenance plan I've recently been having some hellacious problems with a specific maintenance plan.  I found that part of the problem was that I had Shrink and Rebuild Index in my nightly plans, those have since been removed.  All that is left is Tlog backups, Integrity check, and database backups in that order.

One of my database developers came in this morning and logged in as usual and found that our ServiceDesk software wouldn't load via webpage.  Upon looking in Spotlight, the Max I/O Wait on the ServiceDesk database was almost 55k ms.  I went in SSMS and cancelled the job manually and everything returned to "normal" and we were able to log in.

Now to my quesions....

Is this normal?
If it is normal, should I start my backups much earlier?
Should I break up the maintenance plan into smaller chunks and then stagger?

I'm an accidental DBA who is on the homegrown path of being a production DBA, so I'm relatively new.  I'm finding a passion for this stuff as late and heard this community rocks, so anything you can suggest I'd appreciate greatly. Also, if I've missed anything or you need more detail, I'll be happy to clarify.

Depending on your database size, checkdb can take hours to finish. It is heavy on the disk subsystem as well incurring lots of I/O's.

Check out Aaron's excellent article on : Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts

Backups are also I/O intensive operation.

I would suggest to look for a maintenance window - when activity is low or minimal on the server.

Now if you want to dig deeper(prove it), then you can use sys.dm_io_virtual_file_stats. It will show you where the hot-spots are and you can ask your SAN admin to move them away.

 ---Ref: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/

SELECT
    --virtual file latency
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    --avg bytes per IOP
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [io_stall_write_ms] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    --[vfs].*,
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

As a side note, highly recommend you to look into Ola's : SQL Server Maintenance Solution - backup and index maintenance


DENY overrides GRANT in SQL Server? I'm studying for exam 70-462 and I'm practicing with some exercises I find around the web. In the site "Microsoft Questions and Answers" there's one solution I don't understand.

The solution's last step is to "Deny SELECT access to the SalaryDetails table to the Employees role." However, all company employees were added to this role and because "HR Administrators are also company employees," then HR Administrators should end up without any access to the SalaryDetails table, which is not what's being requested.

Is the solution wrong, or am I missing something?

Thanks in advance for any help!

It has to do with the hierarchy of permissions and using role membership over granting permissions directly to each login. This SQLMag article shows a good image of the hierachy in SQL Server. [In the event the article ever goes dead I have copied the image below.]

You are granting DENY to the role Employees so the HR Administrators being a member of a higher level role should mean they do not directly inherit the DENY. At least that is how I understand it.

The particular section of the article referenced speaks on DENY:

  The third permission statement is DENY. A DENY statement also revokes a permission, but does it in such a way that the principal cannot inherit the permission through role membership. When you deny a permission, that principal doesn't have it, period. Denying permissions can let you get very granular with permissions. A common example is to grant everyone in a department a set of permissions through their membership in a role. But then you can fine tune the scheme by denying permissions either to certain users or to another role that only a subset of users belong to, taking advantage of the ability of users to belong to multiple roles.

I stand corrected

Let me first state I apoligize for not first testing what I was saying as I ended up proving myself wrong. I should have known better.

First lesson to learn: Test everything.

Second lesson to learn: Not all Microsoft Exam questions are perfect. I would suggest sticking with those test engines that Microsoft supports (e.g. MeasureUp.com)

Answer before the details

I will first give you what I discovered, then show you how I tested it. So my finding is that basically option D can be correct if you use REVOKE instead of DENY. This is based on the statement from Bob Beauchemin's whitepaper on SQL Server 2005 Security Best Practices:

  In SQL Server, authorization is accomplished via Data Access Language (DAL) rather than DDL or DML. In addition to the two DAL verbs, GRANT and REVOKE, mandated by the ISO-ANSI standard, SQL Server also contains a DENY DAL verb. DENY differs from REVOKE when a user is a member of more than one database principal. If a user Fred is a member of three database roles A, B, and C and roles A and B are GRANTed permission to a securable, if the permission is REVOKEd from role C, Fred still can access the securable. If the securable is DENYed to role C, Fred cannot access the securable. This makes managing SQL Server similar to managing other parts of the Windows family of operating systems.

So basically REVOKE removes the permissions and basically would be not applying any permissions to the table itself. You are not explicitly denying anyone access to the table. So if you really wanted to deny permissions you would have to remove the HRAdmins group from the Employees roles for it to work correctly.

I do not have access to Azure SQL so I could only test this on a local SQL Server 2014 instance. The script below is what I went through, included comments as I go.

USE [master];
GO
--Create a database to work with
CREATE DATABASE [Database_WorkingWithPermissions];
GO

--Create the logins (I can't create Windows Groups but should work the same way)
CREATE LOGIN HRAdmin1 WITH PASSWORD = N'Apple123';

CREATE LOGIN Employee1 WITH PASSWORD = N'Apple123';

--Create the users in the database
USE [Database_WorkingWithPermissions];
GO

CREATE USER Employee1 FROM LOGIN Employee1;
CREATE USER HRAdmin1 FROM LOGIN HRAdmin1;
GO

--Create the role
CREATE ROLE Employees

--Create the tables noted in the question
CREATE TABLE [Employees] (employeeID int IDENTITY(1,1), employeeName varchar(15));
CREATE TABLE [SalaryDetails] (employeeID int, salaryAmount decimal(12,3));
CREATE TABLE [OtherTable] (column1 int);

--Grant permissions to the roles according to requirements
GRANT SELECT ON [dbo].[Employees] TO [Employees];
GO
GRANT SELECT ON [dbo].[OtherTable] TO [Employees];
GO
DENY SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Assign membership to the role based on requirements
ALTER ROLE [Employees] ADD MEMBER [Employee1];
GO
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [HRAdmin1];
GO

--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--So HRAdmin1 does not have permissions as it should
-- So they need to be removed from the Employee role
ALTER ROLE [Employees] DROP MEMBER [HRAdmin1]
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--According to a SQL Server 2005 whitepaper Bob Beauchemin wrote on Security Best Practices
-- Revoke should be used
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
REVOKE SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;


Transfer SQL Server 2017 Standard license to a new server We recently purchased a license for SQL Server 2017 Standard (16 cores) in behalf of a client from an online reseller (not directly from Microsoft), and installed it without problem: we downloaded and installed the evaluation version of SQL Server from the MS web site and then used the edition upgrade of the installation center to enter the serial number (since we didn't have the license at the installation time).

But now the client want to reinstall SQL Server in another (more powerful) server and decommission the current one. So the question is: can we just use the serial number in the new server, or previously we have to "remove" the license of the current installation in some way?

Look under the section “ Licensing SQL Server for application mobility” the first bullet details on what you are asking about.  There is no need to remove the license from the first machine prior to installation of the replacement machine.

  License Mobility is a use right that is available for all editions of SQL Server 2017 software licenses with active Software Assurance (SA) coverage. With this SA benefit, customers can reassign SQL Server licenses to different servers within a server farm as often as needed. Customers can also reassign licenses to third party shared servers. License Mobility is available for licenses under both the Per Core and Server+CAL license models.

  SQL Server licenses that are not covered with active SA can only be reassigned to a different server within a server farm once every 90 days, and they cannot be reassigned to a third-party web hoster or non-private cloud at any time. (In the event of permanent hardware failure, the 90-day reassignment limit is waived.)
  All SQL Server licenses with active SA can be reassigned to another server within the server farm as often as needed; however, they can only be reassigned to another server in another server farm, or to a non-private cloud, once every 90 days.

  A server farm may consist of up to two data centers located in time zones that are within four hours of one another and/or with the European Union (EU) and/or European Free Trade Association (EFTA).
  A given data center may only be part of one server farm.
  License Mobility use rights do not apply to SQL Server PDW software.
  License Mobility can benefit customers who license individual virtual machines (VMs) or containers and then want to reassign those licenses to different servers within a server farm—as workloads move dynamically—or to VMs in cloud environments.

2017 licensing doc


Creating an Index based on Execution Plan I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the performance.
But as I am looking at the execution plan to see where I could possible gain more performance, I noticing this - SQL is basically having to read 5 times the number of records that are being returned here from this one particular table and Plan Explorer is telling me this is where 24% of the query cost is coming from.

But when I look at the index that is mentioned, these are the columns that are included in the index ( no columns are included ) :

So my question is - as I try to understand what exactly I am looking at here - is there anything I can do to the existing index to help performance here?
I tried to use Brent Ozar's Paste the Plan but the XML was too large for that and since the TSQL itself is about 800 lines, I have created a folder in my Google Drive that contains the .sql file, .sqlplan file and a .pesession file ( for Sentry One Plan Explorer ).
Link to files.

You may find this answer to be helpful. The index contains all needed columns for the query so it can be used by the query. However, the order of the columns does not allow for as much filtering as possible using the index keys. About 14.3 million rows are located in the index using an index seek but almost 12 million of those rows are discarded after the predicate filter is applied. It's also worth mentioning that the table only has 10.7 million rows. Using nested loops and an index to read 14 million rows from a table with 10.7 million rows may not be the best choice. With that said, you can make the index seek more efficient by changing the index key order to the following:

PolicyNumber
CovPerilId
TransactionId
All other columns

Changing the key order of the index may impact other queries in positive or negative ways. I can't say if it's the right decision to make overall for the server's workload.
I noticed a few other things while looking at your query plan:

About 164 seconds (38% of total execution time) was spent during query execution waiting to send results to the client. Returning almost a million rows to the SSMS results grid can take a while. Be sure to run your query in a way most similar to what the application does to get the most accurate tuning results. You may find it useful to discard to query result set in SSMS while doing performance testing.
About 42 seconds (10% of total execution time) was spent waiting on CPU. The query will complete faster if it can be changed to use less CPU, if other queries on the server can be used to use less CPU, or if the total core count of the server is increased.
There are other index seeks in the plan where more rows are read from the index than are present in the corresponding table. '[AfterShock].[dbo].[LineOfCoverage].[LineOfCoverage11]' in node 53 is one example. A different index or join type may improve performance of the query.
There are some poor cardinality estimates that lead to tempdb spills. Explaining why is outside the scope of this Q&A. The query is fairly complex so perhaps splitting it into multiple queries that use temp tables will result in better performance.


MariaDB indices do not start at 1 after removing database and re-creating it I am currently experimenting with a DB-Driven app. It automatically sets up its database-tables if its database does not exist, so it is very easy for me to drop the database and just start from scratch.
During those experiments I noticed that after such a fresh start the db-indices do not start at 1 again, but continue counting as if the DB had never been removed.
So it seems just deleting the DB is not sufficient. Are the last indices cached somewhere or kept in memory - do I need to restart MariaDB to get "truly fresh" indices after a reset?
(It won't affect the software, but I'm concerned I may not be cleaning up well enough...)
Here's an example: table migrations is created using
CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL,
  `exec_ts` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Looking at the current content, there are 4 records with id from 24 to 27.
After dropping the db and going through the creation, I see the same values. Ok, so somehow their base hasn't changed - but why don't the autogenerated indices start at 1?
Browsing around, I came across information_schema.TABLES which has a record for this table and AUTO_INCREMENT indeed shows the value that the next record got when I manually created one. I have verified that dropping the DB also deleted that record, but it must have "hidden" somewhere else...

Looking at the current content, there are 4 records with id from 24 to 27. After dropping the db and going through the creation, I see the same values. Ok, so somehow their base hasn't changed - but why don't the autogenerated indices start at 1?

As the database is recreated by he application, and the application is inserting those rows as part of the recreation, it's completely in the hands of the application. It's possible the application does recreate the tables with auto_increment=1, but then inserts those rows and deliberately specifying the PK values. Or, it's recreating the table, but with auto_increment=24, so when it inserts the rows, they start at that value. You could try to recreate the table manually yourself without any rows and auto_increment=1.

Browsing around, I came across information_schema.TABLES which has a record for this table and AUTO_INCREMENT indeed shows the value that the next record got when I manually created one. I have verified that dropping the DB also deleted that record, but it must have "hidden" somewhere else...

Yes, it seems the start values are hidden inside the application.


Postgres : Executing SELECT within a transaction does not return most recent rows Given a table layout that looks like this

inv_items
=========
* id bigserial
sku character varying(22)
name character varying(32)
...

inv_items_stock
===============
item_id bigint (inv_items)
qty int
...

inv_items_group
===============
group_id bigint (inv_items)
item_id bigint (inv_items)

where an item may be a "stock item" or a "group item" (with compound items...) I have a trigger that checks before insert / update on each inv_items_stock and inv_items_group to make sure that some conditions are met; for example, a stock item cannot be a group item.

The problem is that, in my program, I run a batch of inserts inside a transaction; first I insert into inv_items, then into one of the other table. Unfortunately, the SELECT inside my trigger function  does not find any item that I just inserted within the transaction, thus always fails.

For example, here is the group item check trigger

CREATE FUNCTION inv_check_set_group_items() RETURNS trigger AS $INV_GRP_SET$
DECLARE found_item bigint;
BEGIN
   SELECT id INTO found_item FROM inv_groups_vw WHERE id = NEW.group_id;
   IF found_item IS NULL THEN
       RAISE EXCEPTION 'Invalid group item';
   END IF;
   RETURN NEW;
END;
$INV_GRP_SET$ LANGUAGE plpgsql;

CREATE TRIGGER inv_check_set_group_items BEFORE INSERT OR UPDATE ON inv_items_group
FOR EACH ROW EXECUTE PROCEDURE inv_check_set_group_items();

Where inv_groups_vw is a VIEW with the predefined SELECT to return all the item groups inside inv_items.

Is the problem with the VIEW or the FUNCTION? What can I do to fix this and be able to select rows of newly inserted items within the transaction? ... am I missing anything else?

Could it be that my condition IF found_item IS NULL THEN is flawed?

I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering Craig's suggestion) you are done.


Recommended mirroring alternative? SETUP

2 Amazon EC2 SQL 2014 servers in different availability regions.
Synchronous mirroring
S3 for archiving full backups & logs at night
Right now for simplicity, nightly full compressed backups
Tran Logs every 15 mins (would do more if can reduce the runtime)
Ola Hallengren backups running on both principal and mirror servers

SLA

My SLA terms to meet don't have heavy restrictions at this point so (odd I know!):

"Opps Deleted Something" restores would be a nice to have, but I don't a hard delivery requirement on this right now. I do log backups every 15 mins right now.
High-Availability: not fully required, downtime of an hour would be acceptable if needed
Point in time recovery: I'm able to provide due to the transaction log backups every 15 mins, however 2 hours would be acceptable for now.

Network Bottleneck

It looks like one of the biggest bottlenecks is the approx 300mb network pipe that seems to get overloaded by the need to synchronously mirror to another EC2 instance. I could maintain a lot more databases on the same instance, reducing costs likely if I removed mirroring.

Mirroring or Alternative Approach For performance with reasonable availability

I'm looking for the best value while still delivering a reasonable uptime. Since I'm on SQL standard, this means that asynchronous mirroring is not an option (we are running sql 2014).

As I've evaluated options I'd appreciate another perspective on basic disaster recovery. The overhead of mirroring seems to be a big bottleneck, but if I remove it then I'm concerned about how to provide better availability. Ideally, I'd run mirroring asynchronously, but we'd need to move backwards in sql server editions, and not sure if that would be the best approach

always on high availability groups

Not proceeding with this due to the increased complexity and licensing cost at this time. Open to exploring in future, but at this time looking to avoid Enterprise licensing cost.

It sounds like you may benefit from a Failover Cluster if all you're looking for is availability. You can run them on standard edition and if you take an N+1 route, you can have two nodes for HA and have one in a DR site ready to take over. You'll need to work on your storage availability for the DR side, but that's a whole other can of worms.

Another option may be Log Shipping since you're already taking log backups every minute. You can ship the logs off to your HA/DR server.

You can get more information on how to do it and the implicatioons for AWS here:

Implementing Microsoft Windows Server Failover Clustering and SQL Server AlwaysOn Availability Groups in the AWS Cloud


Does using composite keys violate 2NF? From all of the database related reference books that I have come across, there is a universal rule that state a database table should be at least 3NF.

From what I understand, a table is considered 2NF if it satisfies 1NF and it has single column primary key. Correct me if I am wrong.

So I couldn't understand why there is so much argument of having composite key especially for entities that relate M:N tables; eg:

A Product_Customer table, with the columns:

CustomerProductID
CustomerID
ProductID

Some say that primary key (CustomerProductID) is not needed and it should use composite key (CustomerID, ProductID) instead.

But doesn't that violate the universal rule of a table should have at least 3NF since using a composite key will not even satisfy 2NF?

Correct me if I am wrong, especially the part where I understand that if a table has composite keys, it will not be in 2NF.

Your definition of 2NF is not quite correct.

2NF is when a relation is in 1NF and it has no partial dependencies, meaning there are no predicates (columns) that depend on only part of a multi-part key.

What that means is that a table where all the (one or more) keys are single column ones cannot violate 2NF by definition.  However, it's also possible to have a table with a multi column (composite) key which also doesn't violate 2NF.  It's not about how many columns are in your key, its about whether there are non-key columns which depend on only part of any candidate key.

Since it's possible for a table to have a multi column key be in 2NF, it's also possible for a table with a multi column key to be in 3NF.  You just have to make sure there are no transitive dependencies as well.


SQL Server backup devices I'm trying to find out how to detect if a backup device exists using T-SQL.

This is how I create my backup device:

EXEC master.dbo.sp_addumpdevice
    @devtype = N'disk',
    @logicalname = N'TestTest',
    @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PTC_SQLPROD\MSSQL\Backup\TestTest.bak'

but I can't find how to detect if it already exists first.

The sys.backup_devices table should contain details on existing backup devices, so try checking to see if there's a row there first:

IF NOT EXISTS (SELECT NULL
               FROM   sys.backup_devices
               WHERE  [Name] = N'TestTest'
                      AND [Type] = 2)            -- 2 = 'Disk'; see MSDN
  EXEC master.dbo.Sp_addumpdevice
    @devtype = N'disk',
    @logicalname = N'TestTest',
    @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PTC_SQLPROD\MSSQL\Backup\TestTest.bak'

The documentation for the sys.backup_devices table can be found here: http://msdn.microsoft.com/en-us/library/ms178018%28v=sql.100%29.aspx


Remove Redundant Data I want to create queries to remove/extract redundant rows having similar versions. For example 13.0, 13.0.0, 13.0.0.0 are the same for SQL Server. So I want to delete the rows having less refined version of product - 13.0, 13.0.0 - and be left only with versions 13.0.0.0 and 13.0.0.275.

Is there any way to do this using some SQL query?

As versions 13.0, 13, 13.0.0 and 13.0.0.0 are the same in all sense... so I want to keep the most detailed version out of them, that is 13.0.0.0, and delete other versions. But version 13.0.0.725 and 13.0.0.0 are different so I want to keep this record as it is but for particular product say SQL Server.

The output that I want from the graphical example:

SQL Server with 13.0.0.0 version
SQL Server with 13.0.0.275 version
Visual Studio with 13.0.0.0 version
Visual Studio with 13.0.0.275 version

Another example is-- If there are versions like 2, 2.0, 2.1.0, then in this case, the record having version 2 and 2.0 are the same so I want to keep the more detailed version of same version and delete the rest. The record will show having version 2.0 and 2.1.0.

Is there any way to achieve the same using SQL queries?

You can convert the version string to XML and then extract the four parts replacing no value (nulls) with a 0. Put the version back together and compare to your original table.

-- Sample table
declare @T table
(
  Title varchar(20),
  Version varchar(10)
);

-- Sample data
insert into @T(Title, Version) values
('SQL Server', '13'),
('SQL Server', '13.0.0'),
('SQL Server', '13.0.0.0'),
('SQL Server', '13.0.0.175'),
('Visual Studio', '13'),
('Visual Studio', '13.0.0'),
('Visual Studio', '13.0.0.0'),
('Visual Studio', '13.0.0.175');

-- Get the required versions
select *
from @T as T1
where exists (
             select *
             from @T as T2
               -- Convert version to XML
               cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

               -- Extract the values from the XML and build a new version string with 0 instead of null.
               cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                     isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
             where  T1.Title = T2.Title and
                    T1.Version = V.Version
             );

-- Or if you want to delete from your original table ...
delete from @T
where not exists (
                 select *
                 from @T as T2
                   -- Convert version to XML
                   cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

                   -- Extract the values from the XML and build a new version string with 0 instead of null.
                   cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                         isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
                 where  [@T].Title = T2.Title and
                        [@T].Version = V.Version
                 );

select *
from @T;


SQL Server : replication between two different locations I have problem setting my replication between two servers on different locations (different network).

Steps I have done:

setup aliases in SQL Server Native Client configuration on both servers
setup ports on both server
created Windows accounts on both server and run SQL Server and SQL Agent under this account
created SQL Server users for replication with sysadmin permissions
checked that ports are opened and reachable
connected on both sides from Management Studio and connection works
I am almost sure that I have done this correct, because I have done it 100 times....

Then I made replication on publisher server using exact procedure as described here: http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/2/

In Replication Monitor I get error:

  Error messages:
  The process could not connect to Subscriber 'XXX'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)
  Named Pipes Provider: Could not open a connection to SQL Server [53].  (Source: MSSQLServer, Error number: 53)
  A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (Source: MSSQLServer, Error number: 53)
  Login timeout expired (Source: MSSQLServer, Error number: HYT00)

As I can see I have the same problem than in this post:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5095a7f-e015-4086-a8cc-c31abb022aa4/transactional-replications-problem?forum=sqlreplication

But there were no solutions to it...

Ok it was problem that Windows block cross domain connections.

Error in error log:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I had to make new local Windows account and used it for replicaton snapshot agent.


Postgres top-k with ties Assume I have the following DB table with 3 integer fields.

A | B | C
1 | 2 | 3
1 | 2 | 4
1 | 3 | 1
2 | 4 | 2
2 | 4 | 3

When I do:

SELECT * FROM dbTable ORDER BY A,B LIMIT 1;

I get

1 | 2 | 3

which is expected. But the 2nd record:

 1 | 2 | 4

also has the same values for dbFields A and B. Is there any efficient way to actually retrieve all records that have the same value as the top-k records? For example, when I search for the first 100 records to get instead 102 records if the last two have the same values as the 100th record? Is there any index to accelerate such queries? I do not mind if it has to be done with pl/pgsql (and not plain SQL) if the implementation is efficient.

You can use a window function for this:

select a,b,c
from (
  select a,b,c,
         dense_rank() over (order by a,b) as rnk
  from dbTable
) t
where rnk = 1;

For the "first" rows, it doesn't matter if you use rank() or dense_rank(). When you e.g. want the "second" ones, the rank() and dense_rank() would return different results in case of ties. Because rank() will have "gaps" in the numbers, but dense_rank() will not.

A possible speedup might be achieved by doing this in two steps and of course having an index on (a,b)

with ranked as (
  select *
  from (
    select a,b,
           dense_rank() over (order by a,b) as rnk
    from dbTable
  ) t
  where r.rnk = 1  -- (or <= for "top-k")
)
select t.a, t.b, t.c
from dbTable t
   join ranked r on r.a = t.a and r.b = t.b;

The idea is to give Postgres the chance to do an index-only scan for the ranking part and then join only the result of that to the base table to get the remaining column(s). The filtering on the rank is done inside the CTE as Postgres doesn't push down conditions from the outer query into the CTE itself (that's why I have the derived table inside the CTE)

I'm not sure if this really improves the performance, but I guess it would be worth trying and have a look a the execution plan with the real tables (and data).


Is B-Tree re-balanced during deletion of data from SQL Server table with a clustered index? I have a table in a SQL Server database with a clustered index on the primary key. The table has 1 million rows. If I delete 10K rows from table, do index get restructured during deletion operation is performed ?

The deletion operation is part of stored procedure. At a time, more than one client can be executing the stored procedure, however each individual run will delete it's own set of rows (uniquely identified by primary key). I'm getting blocking on Key lock (of type U) when multiple clients executes the procedure. The blocker lock belongs to a row from the same table and it is not part of any of the concurrently running transaction. There should not be any blocking as each run is trying to delete it's own set of rows. Lock escalation is not happening as it is turned off.

I suspect, deletion operation must be causing index to re-balance and hence during restructuring process it can take key lock on any row of the table.

I would really appreciate any opinion on this.

To answer the question in the title, whether the B-tree rebalanced during a delete, the answer appears to be no, at least in the following minimal test case.

The following demo runs commands that are best left for a test environment.

--create table and fill it
DROP TABLE IF EXISTS bunchesofints
CREATE TABLE bunchesofints (
thisisanint INT PRIMARY KEY CLUSTERED,
junkrow CHAR(1000) NOT NULL
)

INSERT dbo.bunchesofints
SELECT TOP 5000
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS thisisanint,
REPLICATE('a',1000) AS junkrow
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2

--with this query we can see all the non-leaf pages of the b-tree, plus the IAM
SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type != 1
GO

--Ok, let's delete most of the rows
;WITH CTE AS (
    SELECT TOP (4500) *
    FROM dbo.bunchesofints
    ORDER BY thisisanint DESC
)

DELETE
FROM CTE
GO

--Hmm, still have 3 non-leaf index pages
SELECT allocated_page_page_id, page_type_desc, page_level, is_allocated, next_page_page_id, previous_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type != 1

--So, where are the rows?
--please note the assumption that your test database has a single file.
DECLARE @firstindexpage INT, @lastindexpage INT, @db INT = DB_ID()
SELECT @firstindexpage = MIN(previous_page_page_id), @lastindexpage = MAX(next_page_page_id)
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.bunchesofints'),NULL,NULL,'DETAILED')
WHERE page_type = 2 AND page_level = 1

DBCC PAGE(@db,1,@firstindexpage,3) WITH TABLERESULTS
DBCC PAGE(@db,1,@lastindexpage,3) WITH TABLERESULTS

This demo shows that a delete can produce a very unbalanced b-tree, with practically all data on one side.


Does SQL Server have a reference partitioning equivalent? Oracle 11g has introduced a new feature that is called Reference Partition.

Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table.

http://www.orafaq.com/wiki/Reference_partitioning

Is there an equivalent feature in SQL Server?

Not exactly.

In SQL Server, you create a partition function and a partition scheme, which are objects separate from the tables themselves, which define the partitioning characteristics. Then, tables are created referencing the partition scheme.

So, yes, there is a central definition of the partitioning characteristics, but the reference is made to the partition scheme (which could be anything), not to any particular table or relationship.


Select rows where 'A1' is followed by 'A2' I have a set of entries like:

Id ColA

1 A1
2 A2
3 B
4 A2
5 A1
6 C
7 A1
8 A1
9 A2
10 D

In pairs 1, 2 and 8, 9 the first line has A1 in colA and the following line has A2 in colA. How can I select these two pairs?

Since you don't mention any specific DBMS I'll assume first that there are no window functions:

select A.Id, A.ColA, B.Id, B.ColA
from T as A
join T as B
    on A.Id + 1 = B.Id
where A.ColA = 'A1'
  and B.ColA = 'A2'

Assuming that window functions do exists:

select Id, ColA, Lead_id, LeadColA
from (
    select Id, ColA
         , Lead(id) over ( order by Id ) as Lead_Id
         , Lead(ColA) over ( order by Id ) as Lead_ColA
    from T
)
where Id + 1 = Lead_id
  and ColA = 'A1'
  and Lead_ColA = 'A2'

All untested, but you should get the idea

An alternative to @Vérace suggestion for a more tabular format is to use a lateral cross join that transpose the result. I.e.:

select X.Id, X.ColA
from T as A
join T as B
    on A.Id + 1 = B.Id
cross join lateral ( values (A.Id, A.ColA)
                          , (B.Id, B.ColA)
                   ) X (id, ColA)
where A.ColA = 'A1'
  and B.ColA = 'A2'

For SQL server I believe cross apply can be used in the same way. DB2, Oracle, Postgres all implement LATERAL, MySQL does not. The same technique can be applied with window functions as well:

select x.Id, x.ColA
from (
    select Id, ColA
         , Lead(id) over ( order by Id ) as Lead_Id
         , Lead(ColA) over ( order by Id ) as Lead_ColA
    from T
) as Y cross join lateral (
               values (y.id, y.cola)
                    , (y.lead_id, y.lead_cola)
) as X (id, cola)
where y.Id + 1 = y.Lead_id
  and y.ColA = 'A1'
  and y.Lead_ColA = 'A2'


Clustered composite key high fragmentation and fill factor I've recently made a table to hold the language preferences of my users as follow:

CREATE TABLE [dbo].[systemUserLangPreference](
  [systemUserID] [int] NOT NULL,
  [langID] [int] NOT NULL,
  [preferredOrder] [int] NOT NULL,
  [createdBy] [int] NOT NULL,
  [createdOn] [datetime] NOT NULL,
  [lastActionBy] [int] NOT NULL,
  [lastActionOn] [datetime] NOT NULL,
CONSTRAINT [PK_systemUserLangPreference] PRIMARY KEY CLUSTERED
([systemUserID] ASC, [langID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

There's less than 1,000 rows in there now so performance isn't much of an issue at this time however when I run an index fragmentation report this index and more of my compound keys always comes up as high fragmentation no matter what I do.

Using

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()

This one index will return 80% avg fragmentation even moment after I rebuild it.  I've tried playing with the fill factor with the following results:

Fill 100%; Fragmentation 75%
Fill 80%; Fragmentation 80%
Fill 50%; Fragmentation 62.5%
Fill 10%; Fragmentation 19.44%
Fill 1%; Fragmentation 1.90%

So it seems the lower I go the better? I'm not sure I'm interpreting this right. Since the table is mostly reads (insert on new user and I guess when they learn a language) wouldn't a higher fill factor be better? Why does it lead to immediate fragmentation and is it a bad thing?

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control.  The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential.  Only after an index has more than eight pages will it be allocated additional pages from uniform extents.

At fewer than 1,000 rows, your clustered index would be well under 64K and entirely allocated out of mixed extents (unless you start forcing the issue by adjusting the fill factor, as you have seen).

You can get an idea of how this clustered index's pages are allocated by issuing this command:

DBCC EXTENTINFO( DB_NAME, systemUserLangPreference, PK_systemUserLangPreference)

Once the index is large enough that it is allocated more than eight pages, you can ensure that they are all allocated out of uniform extents by dropping the index and then creating it again.  Again, though, for such a small table, fragmentation is not going to have a performance impact, and you probably shouldn't try to solve it by fiddling with fill factor.

Lowering the fill factor might actually have detrimental results, as fewer records fit on each page, so more pages needed to be loaded into memory.  Here's more on why to be careful with the fill factor.

For more details on pages and extents, see this article.


Migration of MySQL to SQL Server I need to migrate a MySQL database to SQL Server 2012. Found enough information about it -- looks like it can be done either with SSMA tool or via Data Import utility in SQL Server Management Studio. What I'm still confused about is whether I have to perform this directly from the server that hosts my SQL Server database or I can do it from my client PC running SQL Server Management Studio.

MySQL is my area of expertise and SQL Server isn't... but it looks like the answer is you definitely can manage the migration with SSMA on a client PC, but it also appears that both your client machine and the SQL Server need connectivity to the MySQL server.  Reading between the lines, it sounds as if the client machine might use its connection to MySQL to discover the schemata and then something from the server-side components installed on the SQL Server would subsequently connect directly to MySQL to fetch the actual data rather than the data being actually piped through the client machine where you're using SSMA.

To use SSMA, you first must install the SSMA client program on a computer that can access both the source MySQL database and the target instance of SQL Server or SQL Azure. Then, install the MySQL providers (MySQL ODBC 5.1 Driver (trusted)) on the computer that is running SSMA Client Program.
— http://technet.microsoft.com/en-us/library/hh313129.aspx
In addition to installing SSMA, you must also install components on the computer that is running SQL Server. These components include the SSMA extension pack, which supports data migration, and MySQL providers to enable server-to-server connectivity.
— http://technet.microsoft.com/en-us/library/hh313121.aspx


How do I limit dynamic data masking with a where clause? I am interested in using SQL Server dynamic data masking on a subset of rows within a table. Is it possible to do this?
As an example, I have a table of users with email addresses. I do not want to mask records for my company's internal users. I would like to use a where clause such as where email not like '%acme.com'.
After reviewing the responses, I realized that I need to clarify further. I would like for all non-administrative users to see the same query results. When they query the users table, the resulting rows with an email of acme.com should not be masked and the resulting rows with a different email domain should be masked.

I don't think that this (masking only certain rows in the table) can be done with Dynamic Data Masking.
You could create a view that will do what you want to do by doing something like this:
create table email (id int identity, email varchar(100));
insert into email values ('mail1@hotmail.com'),('email2@gmail.com'),('email3@mycmp.com');
go

create view email_filter as
select case when email like '%@mycmp.com' then email
else left(email,charindex('@', email,0)) end "email"
from email
go

select * from email_filter;

drop table email
drop view email_filter

Note that this will not mask the data from the "email" table so if you want to make sure the users cannot access it, you will have to restrict the access.
Note also that this have some limitation. If you are expecting the view to return result for a query like :

Select * from email_filter where email like '%hotmail.com'

it will not return any row.


Services don't have MySQL Server. How to run it? I need to run MySQL Server on a new for me laptop. There's Windows 7. And MySQL have been installed on it. In order to verify this, I opened the Programs and Features window and saw it there:

Programs and Features screen

But when I go to the Services to run it, I don't find it. It's absent there:

Services screen

It's very strange for me. Why MySQL is not among the services? How can I run it? It's installed on this station. Any ideas?

You may have not installed MySQL using the wizard or skipped that part.

You have full installation instructions in order to add MySQL server as a Windows service on the reference manual, basically:

Ensure that MySQL is stopped (mysqladmin shutdown)
Install the server as a service (mysqld --install), optionally setting a name for it

More details on the aforementioned manual page.


Does simple recovery model not log bulk inserts? I have a db in full recovery model.
There is a planned insert of a million rows that needs to be done next week.
In order to not increase the log size, I am told there are 2 options.
Option 1 is switch to bulk model, perform the insert and then switch to full model. This makes perfect sense.
Option 2 is switch to simple recovery model, perform the insert and then switch to full model. Is this really true? Does simple recovery model not increase the log size during bulk insert? I know that log is cleared at each checkpoint, but during bulk insert does the checkpoint occur?

INSERT can be minimally logged in some cases.  See: Prerequisites for Minimal Logging in Bulk Import.
Note if you switch to SIMPLE recovery your log chain is broken, and you'll need to start with a new full backup after switching back to the FULL recovery model.
If you switch to BULK_LOGGED then inserted pages are copied into the next log backup. Switching to BULK_LOGGED does not break the chain — that's why it exists.
See Bulk Import Optimizations (Minimal Logging) by Sunil Agarwal for more details. Some quotes:

Under FULL recovery model, as the name implies, all operations are fully logged. Under minimal logging, the SQL Server does not log individual rows and logs only the page and extent allocations. So if each page can fit 100 rows, we are trading 100 log records for 1 log record, a significant improvement.

When the bulk import transaction commits ... the SQL Server flushes all the data pages (a subset of these pages are likely to have been flushed as part of checkpoint) to disk to guarantee the atomicity of the transaction.

When the [next] transaction log backup is taken, the SQL Server also backs up the data pages, including index pages when applicable, as part of the transaction log. This enables a database restore by restoring the database backup followed by zero or more transaction log backups. The only drawback of minimal logging is that it does not support point-in-time recovery ... If this is of concern, you can do transaction log backup before/after bulk operations.

So you typically switch back to FULL recovery model, and take a log backup immediately after performing the minimally logged operation.


Using Transactions with PostgresQL dependency issue I am trying to use transactions when inserting into a PostgresQL DB, however due to dependencies, I ran into some issues:
During the transaction I am inserting into multiple tables. The first table into which data is inserted is a table that saves company profile information (name, etc.). One of the other tables has a foreign key column that references to the key of the company which I am inserting in the same step. The company key is a BigInt auto-increment key, which leads to the following problem.

If I use a transaction to insert into all the tables at once, I cannot query for the company key (the insert-command has not come into effect yet), which is needed to insert into the other tables. On the other hand using multiple transactions might lead to an inconsistent state if something fails...

Is there a way to query for what the next assigned BigInt key in the company table would be, so that I can put everything into one transaction? How would you suggest resolving this issue?

Any help is highly appreciated!
Thank you in advance!

Sam

You don't need to query the table, to get the generated id, you can get that using the lastval() function:

insert into company_profile (....)
values (...);
insert into related_table(company_id, ...)
values (lastval(), ...);


MongoDB unrecoverable replication error One of member of my MongoDB replica set decided it would not restart, with the following error (reformatted for readability):

Starting rollback due to OplogStartMissing:
our last op time fetched: (term: 30, timestamp: Jul 28 07:45:11:6)
source's GTE:             (term: 31, timestamp: Jul 28 07:45:11:7)

Fatal assertion 18750 UnrecoverableRollbackError
                          (term: 31, timestamp: Jul 28 07:45:12:2) > our last optime:
                          (term: 30, timestamp: Jul 28 07:45:11:6)

Let's call the instance where this happens M1, and the source its trying to sync M2. M1 used to be primary, then the primary switched to M2, and M1 restarted.

The naive interpretation of these log messages is that the first operation from M2's oplog is exactly the next operation after what we have applied in M1. So, we should just happily apply operations from M2, but MongoDB tries to rollback some operations, finds an operation in future relative to both what we've applied and what's next on M2, and dies.

I have two questions: first, why is MongoDB trying rollback in the first place, and second, where is operation with timestamp of Jul 28 07:45:12:2 is coming from?

let me share you my understanding on this.

question 1:****why is MongoDB trying rollback in the first place
When there is a node switch, documents which are not yet written on secondaries needs to be rollback. As M1 was primary and at the time of switching some documents were not sync they must be in your oplog but as your server was restarted in the mean time M2 become primary and rest of replica members starts syncing with new primary. Now your M1 came up and found that his last operation was not written on other members so it needs to be rollback.

Question 2:****where is operation with timestamp of Jul 28 07:45:12:2 is coming from
check this timestamp in oplog.rs you will find your answer as this would be the same document(rollback)