Unpivot avoid cartesian product on empty columns

This is what I came up with the help of reddit :

SELECT DISTINCT reference, libelle, date_naissance, pays_naissance, nationalite, pays_fiscal, nif, us_tin, us_person, type_activite, etat_civil, rac, pep, departement, etat_individu, relation
FROM S2i_individu
LEFT JOIN relation_individu ON S2i_individu.reference = relation_individu.individu
CROSS APPLY (
VALUES
    (pays_fiscal_1,nif_1,departement_1,'t1'),
    (pays_fiscal_2,nif_2,departement_2,'t2'),
    (pays_fiscal_3,nif_3,departement_3,'t3'),
    ('','',departement_4,'t4'),
    ('','',departement_5,'t5'),
    ('','',departement_6,'t6'),
    ('','',departement_7,'t7'),
    ('','',departement_8,'t8'),
    ('','',departement_9,'t9'),
    ('','',departement_10,'t10')
) AS v(pays_fiscal, nif, departement, suffix)
WHERE import_id = (SELECT TOP(1) id FROM import ORDER BY import_date DESC


Rows to EAV structure with row identifier

You do need to include a GUID higher in the query.

This looks like it might do the job:

SELECT N.value('../@GUID', 'nvarchar(max)') as c1,
       N.value('local-name(.)', 'nvarchar(max)') as ColName,
       T2.N.value('text()[1]', 'nvarchar(max)') as ColValue
FROM (SELECT NEWID() as "@GUID", *
      FROM @MyTable
      FOR XML PATH('T'), TYPE) as T1(X)
CROSS APPLY T1.X.nodes('/T/*') as T2(N)


If I understand your question correctly, I think you want OK in the MyTable if you cannot find any row on the joined tables that meets your FAILED conditions.  I'm using NOT EXISTS and also made sure I'm only updating MyTable for the specific table being processed.

Let me know if I have misunderstood your requirements.

drop table if exists [MyTable]
drop table if exists [BICHATP]
drop table if exists [AFUP]

CREATE TABLE [AFUP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);

CREATE TABLE [BICHATP]
(
    [TVF] nvarchar(10),
    [RPPS] nvarchar(10)
);
INSERT INTO [AFUP] ([TVF], [RPPS]) VALUES
    ('M006', '1010'), ('M054','1000'), ('M015', '2324');

INSERT INTO [BICHATP] ([TVF], [RPPS]) VALUES
    ('M000', '1101'), ('M090','1001'), ('M012', '1023');

CREATE TABLE [MyTable]
(
    [Tabs] nvarchar(100),
    [Check_column] nvarchar(10)
);

INSERT INTO [MyTable] ([Tabs]) VALUES
    ('AFUP'), ('BICHATP');

DECLARE @cmd nvarchar(max);
DECLARE @Tabs nvarchar(10);

DECLARE cur1 CURSOR FOR
    SELECT [Tabs] FROM [MyTable];

OPEN cur1;

FETCH NEXT FROM cur1 INTO @Tabs
WHILE(@@FETCH_STATUS = 0)
BEGIN
    SET @cmd = 'update [MyTable]
                set [Check_column] = case when NOT EXISTS
                    (SELECT * FROM ' + @Tabs + ' where
                    left([TVF],4) = ''M006'' or left([RPPS],4) = ''1009'')
                    then ''OK''
                         else ''FAILED''
                    end
               from ' + @Tabs + ' WHERE [MyTable].Tabs = ''' + @Tabs + '''';

               print @cmd
    EXEC sp_executesql @cmd;

    FETCH NEXT FROM cur1 INTO @Tabs
END

CLOSE cur1;
DEALLOCATE cur1;

select *
from MyTable

| Tabs    | Check_column |
|---------|--------------|
| AFUP    | FAILED       |
| BICHATP | OK           |


Backed up MySql folder… upgraded to newer version and cant seem to recover data

At this point, it is probably simplest to use mysqldump to dump the old data and load it into the new path.


How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

Rather than looking at index utilization, I'd look at the plan cache to find your queries with the highest amounts of logical reads. Usually when I'm dealing with partitioning, I find just a handful of queries that are dominating reads - like 50-80% of the servers' reads overall. Check those queries to see if they're successfully doing partition elimination.

If they aren't doing partition elimination, but you think they should (based on your partition scheme), then work with the query writers to get partition elimination.

If they aren't eliminating partitions, and they can't (because of the way the query is written or the partition is designed), then it's time to start asking tough questions.

If the biggest logical read queries don't have anything to do with your partitioned table, then move on and focus on those other queries instead.


Grant a database role in current database with permission to execute a SP in msdb

Microsoft created special roles that allow this sort of thing

"SQL Server Agent Fixed Database Roles"

Granting EXECUTE on sp_start_job won't work, because permissions and the checks made when starting a job are linked to role membership only.

USE msdb
ALTER ROLE SQLAgentOperatorRole ADD MEMBER DR_user;


SQL Server domain name and user name formatting

You are possibly mixing SQL Server Logins and Database Users.

Introduction

A SQL Server Login is an account that allows a user to connect to the SQL Server instance. A SQL Server Login can be either ...

a native SQL Server login which exists in the SQL Server instance
a Windows Account from either the local server or the Active Directory which is linked (mapped) into the SQL Server instance

Examples of such accounts are:

sa (native SQL Server Authenticated login)
NT-Authority\SYSTEM (local Windows Authenticated login)
MHT\sam (Active Directory Windows Authenticated login)

When you create a user in SQL Server Management Studio (SSMS) with the graphical user interface, you are initially creating the SQL Server Login (Windows authentication or SQL Server authentication).

Differences at this stage are:

Windows authenticated account doesn't require a password
SQL Server authenticated account requires a password

When you have filled in the initial information you will normally switch tabs and configure the User Mappings.

In the User Mapping tab, you are mapping the SQL Server login to a database user. In most cases you will use the same name for the database user. In other cases you might be creating a totally different name.

Important: The database user has rights/permissions in the database. The SQL Server Login has access to the SQL Server instance.

In your case...

...you are assigning rights/permissions to the database user sam. However, sam isn't aware that he is linked to the SQL Login MHT\sam and it doesn't really matter, because you assign the database permissions/rights to a database user and not to SQL Server login.

Possible Reason For Discrepancies Between sam and MHT\sam

While you (or somebody else) were creating the MHT\sam SQL Server Windows Authenticated Login and after switching to the User Mappings section, the database user was truncated to sam.

SQL Server Windows Authenticated Login | General

This is where decide if you will be creating a native SQL Server Login or a Windows Authenticated Login.

SQL Server Windows Authenticated Login | User Mappings

This is where you link the SQL Server Login to a newly created database user or where you link the SQL Server Login to an already existing database user.

Solution

exec sp_addrolemember N'RunStoredProcedure', N'sam'
go
exec sp_addrolemember N'MonitoringRole', N'sam'
go

Explanation

The SQL Server Windows Authenticated Login MHT\sam is linked to the database user sam, which has now been granted the database role RunStoredProcedure and MonitoringRole. When MHT\sam logs in to the SQL Server instance and connects to his database, he has the rights/permissions assigned to the database user sam.


Enabling SQL Instant File Initialization when Restoring to a Network Path

This restriction of 'instant file initialization' (aka. SetFileValidData) is documented:

  Note  The file cannot be a network file, or be compressed, sparse, or transacted.


mysql - How to repair & backup archive table, also stop growing .ARN extension file?

This is a very tricky question because of the internals of the ARCHIVE storage engine.
People have asked this same question in the MySQL Forums

Jan 04, 2014 : Huge and growing archive with ARN extension
Jan 28, 2015 : large .arn file filling up whole hard drive

What needs to be understood is the file layout of an ARCHIVE table:

.frm : Every table in MySQL has a .frm regardless of storage engine
.ARZ : Table data
.ARM : Table metadata
.ARN : Optimization File

Let's start with .ARZ. What's the Z stand for ??? zlib
Why zlib ??? MySQL Documentation says

Storage: Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/). You can use OPTIMIZE TABLE to analyze the table and pack it into a smaller format (for a reason to use OPTIMIZE TABLE, see later in this section). The engine also supports CHECK TABLE. There are several types of insertions that are used:
An INSERT statement just pushes rows into a compression buffer, and that buffer flushes as necessary. The insertion into the buffer is protected by a lock. A SELECT forces a flush to occur.
A bulk insert is visible only after it completes, unless other inserts occur at the same time, in which case it can be seen partially. A SELECT never causes a flush of a bulk insert unless a normal insert occurs while it is loading.

Some compression is happening to data that is being inserted. If your .ARN file is growing, it must be doing some compression related work on every INSERT in terms of encoding/encryption.
Note the zlib.net Technical Details says under the subheading Maximum Compression Factor:

Empirically, the deflate method is capable of compression factors exceeding 1000:1. (The test case was a 50MB file filled with zeros; it compressed to roughly 49 KB.) Mark loves to calculate stuff like this and reports that the theoretical limit for the zlib format (as opposed to its implementation in the currently available sources) is 1032:1. To quote him,
The limit comes from the fact that one length/distance pair can represent at most 258 output bytes. A length requires at least one bit and a distance requires at least one bit, so two bits in can give 258 bytes out, or eight bits in give 1032 bytes out. A dynamic block has no length restriction, so you could get arbitrarily close to the limit of 1032:1.
He goes on to note that the current implementation limits its dynamic blocks to about 8 KB (corresponding to 8MB of input data); together with a few bits of overhead, this implies an actual compression limit of about 1030.3:1. Not only that, but the compressed data stream is itself likely to be rather compressible (in this special case only), so running it through deflate again should produce further gains.
By way of comparison, note that a version of run-length encoding optimized for this sort of unusual data file -- that is, by using 32-bit integers for the lengths rather than the more usual 8-bit bytes or 16-bit words -- could encode the test file in five bytes. That would be a compression factor of 10,000,000:1 (or 10.000.000:1 for you Europeans, or 107:1 for all of you engineers and scientists whose browsers support superscripts).
Finally, please note that this level of compression is extremely rare and only occurs with really trivial files (e.g., a megabyte of zeros). More typical zlib compression ratios are on the order of 2:1 to 5:1.

Give the presence of .ARN that is growing, it must be a temp table used to determine how row data is compressed and then cached to disk. (ARCHIVE does not cache data in memory).
At this point, your problem is at the compression layer of the storage engine.
WHAT TO DO NEXT
Run FLUSH TABLES; and copy the .frm, .ARM, and .ARZ to another server that has the same OS server. Do not copy from Linux to Windows. Try running REPAIR TABLE there.
If you still cannot read the data, you may have to do some deeper diving. You could try downloading archive_reader.c  (from Twitter), compile it and try to read your data.
Godspeed, Spiderman !!! (My Disclaimer).
ONCE YOU HAVE RECOVERED YOUR DATA
SUGGESTION #1
Do not use the REPLACE command against an ARCHIVE table. Why ???

DELETE is not supported for an ARCHIVE table
REPLACE is supported for an ARCHIVE table, requires INSERT and DELETE privs
REPLACE is a mechanical DELETE and INSERT

If ARCHIVE does not expose the DELETE operation, but allows REPLACE to secretly do so, this could impact performance greatly.
SUGGESTION #2
SELECTs and INSERTs can peacefully coexist within an ARCHIVE table. Of course, the one and only exception would be if you insert a new row and SELECT that same row concurrently.
SUGGESTION #3
Get a bigger data disk and a lot more RAM. This should give zlib a lot more head room for data compression in RAM before resorting to using the .ARN.


SQL Always on availability with failover and mixed authentication

Either create the login on instances hosting the replicas specifying the same SID as on the instance hosting the primary:

  To Resolve an Orphaned User

  In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login,
  providing the SID of the database user obtained in the previous
  section:

CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_here>',
SID = <SID>;

From: Troubleshoot Orphaned Users (SQL Server)

Or use a Contained Database User:

CREATE USER user_name WITH PASSWORD = 'strong_password';


SQL Server WMI Event Alert and BROKER_TASK_STOP waits

These two wait types are excluded from Paul Randal's wait stats analysis query, and he is far more of an authority than me.

I would not be concerned about these, and exclude them from your analysis.


Why are some aggregates treated differently in the RHS of a rule in the model clause?

In the Oracle Database SQL Language Reference one can read

  NULLIF(expr1, expr2)
  ...
  The NULLIF function is logically equivalent to the following CASE expression:
      CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

but Oracle 11.2.0.3 on Linux gives the following

SQL&gtselect *
  2  from (select level k, 100 v from dual connect by level&lt=2)
  3  model return updated rows
  4  dimension by (k)
  5  measures (v, 0 shr)
  6  rules ( shr[any] = CASE WHEN sum(v)[any] = 0 THEN NULL ELSE sum(v)[any] END);

         K          V        SHR
---------- ---------- ----------
         1        100        200
         2        100        200

SQL&gt select *
  2  from (select level k, 100 v from dual connect by level&lt=2)
  3  model return updated rows
  4  dimension by (k)
  5  measures (v, 0 shr)
  6  rules ( shr[any] = nullif(sum(v)[any], 0));

         K          V        SHR
---------- ---------- ----------
         1        100        400
         2        100        400

SQL&gt

Therefor this is (at least a documentation :-) bug in 11r2

One gets the same results in Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production


Conversion failed error - but not converting anything and no date/time data

You didn't post your actual code, but SQL Server isn't throwing that message if there isn't an actual conversion error.

You say you aren't converting anything, but if you insert data of one data type into a column of another data type SQL Server will try to convert it implicitly.

Look at Data Type Conversion for more information:

  Implicit conversions are not visible to the user. SQL Server
  automatically converts the data from one data type to another. For
  example, when a smallint is compared to an int, the smallint is
  implicitly converted to int before the comparison proceeds.

That would happen not only with comparison but also with insert statements


These two variables have nothing to do with each other. One is at InnoDB level, the other is at SQL level (and there are many engines aside from InnoDB). They may seem similar because in the case of a very crowded server they could limit the concurrency, sacrifying if for better latency. A higher value for both values (or 0, which means unlimited, for innodb_thread_concurrency) will often result in a higher number of threads running on a particular node. This is then only thing where I can think they can relate.

innodb_thread_concurrency is used by the innodb scheduler to change the number of active foreground threads (the ones retrieving your data from the buffer pool or writing to it an committing to the transaction log). You can read a more in depth explanation about the InnoDB scheduler here: MySQL Performance Blog. Please note that this blog post is ancient, mysql concurrency has improved a lot in 5.6 and 5.7, and my recommendation is to leave this variable as 0 and, if necessary, control the concurrency at used level with a pool of connections.

slave_parallel_workers is a replication slave-only thing. It tells how many sql threads should be executing at the same time trying to apply in parallel the pending changed brought from the master with the io thread. This will not work in stable releases (5.6 series) very well unless you have a multi-tenant database, as changes will only be applied in parallel for diferent schemas with no cross-database queries (so, if you have 2 databases, concurrency will always be 2 or less).

Of course, theoretically, too many slave workers could end up producing too much innodb concurrency, but the odds of that are very low. It makes no sense, however, to setup innodb_threads_concurrency to 1 and slave_parallel_workers to 100, because you will limit your slave change application.

Typically, you will want to not touch innodb_thread_concurrency (leave it to 0, no maximum concurrency limited) and set slave_parallel_workers to the number of real different schemas you have (not having into account mysql, information_schema, etc.) and can write in parallel (useful, for example, if you had one schema per wordpress installation and lots of writes).


Is there a way to specify the initial size of MySQL File-Per-Table Tablespaces?

No. For ibdata files you have variable innodb_data_file_path=ibdata1:XXXXM:autoextend you can specify one or more but not for individual table spaces.


I believe that your syntax is not correct can you please follow this syntax

DROP PROCEDURE IF EXISTS update_pts;
DELIMITER //

CREATE PROCEDURE `update_pts`()
BEGIN

DECLARE noMore BOOLEAN DEFAULT FALSE;
DECLARE cursorsap VARCHAR(255);
DECLARE cs1 CURSOR FOR SELECT sap FROM participant;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMore = TRUE;

SET @ctr=1;
SELECT count(*) INTO @cnt FROM participant;

OPEN cs1;

REPEAT
FETCH cs1 INTO cursorsap;
    IF NOT noMore AND (@ctr < @cnt) THEN
        SELECT sum(players.points) INTO @points FROM players WHERE pname in
        (
        SELECT player1 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player2 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player3 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player4 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player5 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player6 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player7 FROM team WHERE sap=cursorsap
        UNION ALL
        SELECT player8 FROM team WHERE sap=cursorsap
        );

        SELECT points INTO @pointsc FROM players WHERE sport='cricket' AND dept=(SELECT cdept FROM team WHERE sap=cursorsap) AND year=(SELECT cyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointschess FROM players WHERE sport='chess' AND dept=(SELECT chessdept FROM team WHERE sap=cursorsap) AND year=(SELECT chessyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointstt FROM players WHERE sport='tt' AND dept=(SELECT ttdept FROM team WHERE sap=cursorsap) AND year=(SELECT ttyear FROM team WHERE sap=cursorsap);

        SELECT points INTO @pointst FROM players WHERE sport='throwbALL' AND dept=(SELECT tdept FROM team WHERE sap=cursorsap) AND year=(SELECT tyear FROM team WHERE sap=cursorsap);

        SET @pointsf=@points+@pointsc+@pointschess+@pointstt+@pointst;

        update participant SET points = @pointsf WHERE sap=cursorsap;

        SET @ctr=@ctr+1;
    END IF;
UNTIL noMore
END repeat;
CLOSE cs1;
END //

Please use this updated code let me know if you still have issues.


Database design import massive csv files is there a more efficient way?

LOAD DATA ... INTO staging ...; It has VARCHARs in place of INTs, etc.  It also has some unique id (perhaps auto_increment) for step 3.
Copy "bad" rows to debtor_err in one pass:

INSERT INTO debtor_err
     SELECT ...
         WHERE LENGTH(..) > ..
            OR NOT EXISTS( SELECT * ... )  -- FK check
            OR ... > 12
Copy "good" rows to debtor table -- Use JOIN to see which ones are good

INSERT INTO debtor
    SELECT ...   -- where nec, convert from VARCHAR
        FROM staging s
        LEFT JOIN debtor_err e
            ON s.id = e.id  -- suitable UNIQUE key
        WHERE e.id IS NULL  -- to get the non-error rows
DELETE FROM staging; -- Reset for next time.  (Using DELETE instead of TRUNCATE may avoid id not working 'right' the second time around.)

Suggestion:  Use MyISAM for staging; InnoDB for permanent tables.


I guess you don't want (for every Tracks.prefix) a new row to be inserted but the existing row to be updated (otherwise you would get collisions on the primary key of Tracks). You can this query to check first what will be changed:

SELECT
    tr.prefix,
    tr.label_id AS existing_label_id,
    top.label,
    lbl.label_id AS new_label_id
FROM
    Tracks AS tr
  JOIN
    Top100 AS top  ON top.prefix = tr.prefix
  JOIN
    Label AS lbl  ON lbl.label_name = top.label ;

and then update:

UPDATE
    Tracks AS tr
  JOIN
    Top100 AS top  ON top.prefix = tr.prefix
  JOIN
    Label AS lbl  ON lbl.label_name = top.label
SET
    tr.label_id = lbl.label_id ;


What's the minimum privilege needed to alter a foreign key constraint?

You need to add the "REFERENCES" privilege to your role.


Why does SQL Server table partitioning stop working?

I imagine the slowness (and thus deadlocks, etc), and the perception that partitioning "stop working," are because your queries suddenly stop getting partition elimination.

There are lots of reasons that you can miss out on partition elimination:

data type mismatches and auto-parameterization (see Why Doesn’t Partition Elimination Work?)
not being specific enough with predicates when joining to other tables (see Implied Predicate and Partition Elimination)
limitations related to join types (see why no partition elimination)

  My question is why is this occurring, and is there a way to check to see when or why this is happening.

You should get actual execution plans for the specific slow queries, or maybe just one of them.  Get one when it's working great, and one when the problem is happening.  You should be able to see the difference (likely a difference in the number of partitions being read).  Feel free to post a question about it on the site.

Maybe stats get out of date and you get a hash join with the partitioned table on the probe side.  Or you suddenly get a lower cost for the queries and they're being trivial-plan'd / auto-param'd.

The whole workflow of "dropping and recreating everything makes it work" makes me very suspicious that statistics are involved in the root cause of the issue.

The specific answer to your "why" question will likely be in the execution plans.  Until then, these are just educated guesses, but hopefully they point you in the right direction =)


How to save and then restore permissions after refreshing a database

@AaronBertrand is right. You should consider a different method of refreshing your staging environment.  If for no other reason that any sensitive data that you have in production is now available to a new group of users who may not be as carefully restricted as in production.  However I realize that changing your process can take time (if management will even approve the time).

Essentially you want to create a script that will drop all of your existing permissions from the copy of the prod database and another that will generate all of the permissions that used to be there.  My personal preference is going to be to use a script like sp_dbpermissions.  Run it on your production environment and then copy the drop user script column (second to last column) into a .sql file and save it.  Be very careful not to run it in production of course.

EXEC sp_dbpermissions 'dbName'

You only need the second to last column from the first result set.

Next run the same script on your Staging database.  This time you will want to copy the last column from each of the three result sets.  This will give you the CREATE USER, sp_AddRoleMember and GRANT/DENY commands to add the permissions for this database.  Again save them in a .sql file.  After your restore go ahead and run the first script (to remove the old permissions) then the second (to add the new).

I also highly recommend taking a backup of your Staging database before overwriting it.  This way if there is a problem  you can always restore it back (even if to an alternate location) and confirm any missing permissions.


How to reference primary key when inserting in MySQL

You need to declare the column itself, so the books table should look like:

CREATE TABLE if not exists books
( book_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, title VARCHAR(255) NOT NULL
, genre VARCHAR(30) NOT NULL
, author_id INT UNSIGNED NOT NULL

,    FOREIGN KEY(author_id) REFERENCES authors(author_id)
);

I added NOT NULL on columns title and genre since I believe they are mandatory in most situations.

I would say that you also need something that uniquely identifies an author in real life, let's assume name does:

CREATE TABLE if not exists authors
( author_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
, name VARCHAR(30) NOT NULL UNIQUE
);

Now you can insert a new book like:

INSERT INTO books (title, genre, author_id)
SELECT 'The Shining', 'Horror', author_id
FROM authors
WHERE name = 'Stephen King';


First, patch: make sure you're on 2012 Service Pack 1 Cumulative Update 10 or newer. In SQL 2014, Microsoft changed TempDB to be less eager to write to disk, and they awesomely backported it to 2012 SP1 CU10, so that can alleviate a lot of TempDB write pressure.

Second, get exact numbers on your latency. Check sys.dm_io_virtual_file_stats to see the average write stall for your TempDB files. My favorite way to do this is either:

sp_BlitzFirst @ExpertMode = 1, @Seconds = 30 /* Checks for 30 seconds */
sp_BlitzFirst @SinceStartup = 1 /* Shows data since startup, but includes overnights */

Look at the file stats section, and focus on the physical writes. The SinceStartup data can be a little misleading since it also includes times when CHECKDB is running, and that can really hammer your TempDB.

If your average write latency is over 3ms, then yes, you might have solid state storage in your SAN, but it's still not fast.

Consider local SSDs for TempDB first. Good local SSDs (like Intel's PCIe NVMe cards, which are under $2k USD especially at the sizes you're describing) have extremely low latency, lower than you can achieve with shared storage. However, under virtualization, this comes with a drawback: you can't vMotion the guest from one host to another to react to load or to hardware issues.

Consider a RAM drive last. There are two big gotchas with this approach:

First, if you really do have heavy TempDB write activity, the change rate on memory may be so high that you won't be able to vMotion the guest from one host to another without everyone noticing. During vMotion, you have to copy the contents of RAM from one host to another. If it's really changing that fast, faster than you can copy it over your vMotion network, you can run into issues (especially if this box is involved with mirroring, AGs, or a failover cluster.)

Second, RAM drives are software. In the load testing that I've done, I haven't been all that impressed with their speed under really heavy TempDB activity. If it's so heavy that an enterprise-grade SSD can't keep up, then you're going to be taxing the RAM drive software, too. You'll really want to load test this heavily before going live - try things like lots of simultaneous index rebuilds on different indexes, all using sort-in-tempdb.


SQL Server 2008 Group by 30 minute Time Intervals

Just this...

SELECT
    'DATE' = CONVERT(VARCHAR(12), CAST(x30.ActionDateTime AS date), 101),
    'TIME' = CAST(DATEPART(HOUR, x30.ActionDateTime) AS char(2)) + ':' + CAST(DATEPART(MINUTE, x30.ActionDateTime)/30 AS char(2),
    ...

GROUP BY
    CAST(x30.ActionDateTime AS date),
    DATEPART(HOUR, x30.ActionDateTime),
    DATEPART(MINUTE, x30.ActionDateTime)/30


MySQL Workbench is a client querying and modelling tool and a very good one at that - it's purpose is not to perform server tasks - that's the job of the server. You can perform manual backups using Workbench, but I'm assuming that's not your long term goal.

There are many ways you can perform backups. Take a look here for 10 of them. Some of the solutions are OS dependent - basically, you have more choices if you're on Linux/*nix than on Windows (which are you?). Also, it depends on whether your tables are InnoDB or MyISAM (hope it's InnoDB!).

Two (other Open Source) backup solutions not mentioned in the post above are Percona's XtraBackup which can be cron'd or Zmanda's (which was mentioned in the High Performance MySQL book by Schwartz et al.). Another solution would be MyDumper - again a Percona backed tool (except this one also copes with MyISAM tables).

If you're familiar with *nix and file systems, then maybe an LVM solution might be for you? Try here.

I would advise you to study the solutions available and do some testing and see which one best suits your requirements.


admin and active is just a bunch of 0's and 1's so can I do something
  like this? Since there will be 99% active and only a couple that will
  be inactive.

  admin - id, user_id
  inactive - id, user_id
  social - id, user_id
  facebook_link, twitter_link
  member_forgot - id, user_id, forgot_code

Actually no, I wouldn't do this and keep it in the same table. The flag should be enough to distinguish between these two. The only difference in your queries is a active = true. Personally, I wouldn't consider this a bad design choice (altough your idea is not wrong, I would not use it in this case with - I'm guessing - a small set of data). Since only on set of data belongs to a single user, JOINs would make not that much sense.

  I really want to practice splitting it up in different tables even if
  it is not a huge database, or is that a bad idea? So if I delete a
  user from the users table, it will delete all of the other records in
  the other tables associated with that user_id?

Again, I wouldn't do that personally(!). I don't see a benefit of seperating user centric data from the user. If you fear losing the data on deletion, introduce a deleted flag. This way, you will never lose data and your application just respects the deleted flag in your queries (this would also apply if you split the data accross tables).

  Credit_Cards = 1110 format (visa, mastercard, discover, amex)

Storing credit cards is always very sensitive, according to PCI (as far as i remember) you are not allowed to store the CVV code (hope someone can correct me on that). But since this seems to be a fictional application, that should not be a problem.


Concurrent requests not running in parallel

Think back to the bad old days when everyone's computer had a single CPU with just one core. If you ran a program that took 2 minutes on its own at the same time as another one that took 2 minutes on its own then both of them combined might take 4 minutes. There's only so many CPU cycles to go around after all and the two programs would be competing for resources. The same thing can happen in SQL Server if you throw too much work at it relative to your server size or if you get unlucky.

Based on your description of the problem, the most likely explanation is that session 1 and session 3 are running at MAXDOP 1 and they end up on the same CPU core. Session 2 is on a different CPU core. That could be why performance degrades when you run sessions 1 and 3 at the same time but running 1 and 2 at the same time leads to no degradation. To validate that, the wait type that you want to look for is SOS_SCHEDULER_YIELD. While the queries are running, you can also use the sys.dm_exec_requests DMV:

SELECT session_id, scheduler_id
FROM sys.dm_exec_requests;

If your two sessions end up on the same scheduler_id (a scheduler maps back to a logical core) then it's reasonable to expect that both sessions will take twice as long as they did when you were just running one query at a time. This is a bit more complicated if you have hyperthreading enabled and exposed to SQL Server, but the same kind of behavior can be observed. You would want to look at sys.dm_os_schedulers as well because you could see a similar degradation on different schedulers if those schedules are on the same physical core.

If you want to get into the details for scheduling, you could start by reading this blog post. A small quote:

  Scheduling assignment starts at the NUMA node level.  The basic
  algorithm is a round robin assignment for new connections.    As each
  NEW connection arrives it is assigned to a scheduler based on round
  robin, NUMA node connection assignment.

If your server has NUMA nodes that consist of just one scheduler then you could easily get yourself into a situation where queries get doubled up unnecessarily. The fix would be to change your NUMA configuration or to run your queries in different sessions until you get the desired behavior.


SELECT operation with four joins results in bad performance

--I think this is mandatory for your query performance:
    --Because you do joins using this columns from parent to child

    create index on doer_locations(doer_id);
    create index on job_realizations(job_place_id);
    create index on job_realizations(doer_id);

    --Maybe very big and slowdown other operations...
    --create index on job_realizations(lat);
    --create index on doer_locations(lat);
    --create index on job_realizations(lng);
    --create index on doer_locations(lng);

    --Maybe not mandatory:
    create index on job_realization(realizations_per_place);
    create index on job_realization(realizations_per_place_per_doer);

SELECT DISTINCT doers.id FROM doers
    JOIN doer_locations dl ON dl.doer_id = doers.id
    JOIN job_places jp ON (jp.lat - 0.3147625620715557) < dl.lat AND (jp.lat + 0.3147625620715557) > dl.lat AND (jp.lng - 0.5001626620527362) < dl.lng AND (jp.lng + 0.5001626620527362) > dl.lng
    LEFT JOIN job_realizations jr ON jr.job_place_id = jp.id
    LEFT JOIN job_realizations jrpd ON jrpd.job_place_id = jp.id AND jrpd.doer_id = doers.id
    WHERE (jp.job_id = 1 AND doers.id IS NOT NULL)
    GROUP BY doers.id, jp.id
    HAVING COUNT(DISTINCT jr.id) < jp.realizations_per_place AND COUNT(DISTINCT jrpd.id) < jp.realizations_per_place_per_doer

Please try and if it solve your problem select it as the right answer.


The HTTP/1.1 spec says, through RFC-7230, that:

the status code is a 3-digit number, so an int4 or int2 would do.
for the entire header, see 3.2.4, "Field parsing":

  Historically, HTTP has allowed field content with text in the
     ISO-8859-1 charset [ISO-8859-1], supporting other charsets only
     through use of [RFC2047] encoding.  In practice, most HTTP header
     field values use only a subset of the US-ASCII charset [USASCII].
     Newly defined header fields SHOULD limit their field values to
     US-ASCII octets.  A recipient SHOULD treat other octets in field
     content (obs-text) as opaque data.

"opaque" pretty much implies that bytea is the only safe choice, if you want to handle the responses of any HTTP server out there.
The message body is defined as  message-body = *OCTET, so bytea is also pretty much the only type that fits, unless you prefer the large objects storage and API. bytea is limited to 1Gb so you may want to chunk the value across several smaller rows if you target any size. In practice, very large bytea values tend to be unworkable, personally I wouldn't go over 128Mb per row.


How to improve performance for table JOINS involving millions of rows

What you described is normal behaviour.

create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
delete from t1 where object_id is null;
delete from t2 where object_name = 'T1';
alter table t1 add primary key (object_id);
alter table t2 add constraint t1_fk foreign key (object_id) references t1(object_id);
create index t2_object_id on t2(object_id);
exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');

explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id);
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1838229974

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 23763 |  5337K|       |   516   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |      | 23763 |  5337K|  2952K|   516   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   | 23764 |  2668K|       |   115   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 23763 |  2668K|       |   115   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - this is an adaptive plan

Here the database performs the join for the whole resultset with a HASH JOIN. By default, the optimizer generates a plan to return all rows from a query, which is configured with the optimizer_mode parameter.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

Your query says: return all rows from TBL1 and TBL2 that match on CONFIGURATION_ID. You never told the database you wanted only the first any 10 rows. If you want the optimizer to generate a plan that returns the first rows quicker, tell that to the optimizer: change the optimizer mode, example:

alter session set optimizer_mode=first_rows_10;

explain plan for select * from t1 join t2 on (t1.object_id = t2.object_id);
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 209411957

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |             |    10 |  2300 |    12   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | T2          | 21604 |  2426K|     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C006383 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |   115 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

This time, the database performed a NESTED LOOPS JOIN.

The significant difference between HASH JOIN and NESTED LOOPS JOIN in this case: HASH JOIN has to read one of the tables completely to build the hash table for the join. NESTED LOOPS JOIN can work row by row and do index lookups by ID, then stop reading the tables after finding the 10 rows. So even if you fetch only 10 rows, with a HASH JOIN, the database will read the whole 2.8 or 10 million rows from TBL1 or TBL2, depending on the order the optimizer chose.

To make things even worse, if you want to get the first 10 rows by a speficied order, with a HASH JOIN like above, the database has to read both tables completely, perform the join for all rows, sort the ~10 million rows, and return only the first 10 of them.

With a NESTED LOOPS JOIN, if you have an index on the sort column, the database can read the rows even in the sorting order using that index, can do row by row processing, and stop after finding the first 10 rows.

The optimizer mode can be set with a parameter as above, or it can be defined at statement level as well, with a hint, like:

select /*+ first_rows(10) */ ...


Would using varchar(5000) be bad compared to varchar(255)?

Yes, varchar(5000) can be worse than varchar(255) if all values will fit into the latter. The reason is that SQL Server will estimate data size and, in turn, memory grants based on the declared (not actual) size of the columns in a table. When you have varchar(5000), it will assume that every value is 2,500 characters long, and reserve memory based on that.

Here's a demo from my recent GroupBy presentation on bad habits that makes it easy to prove for yourself (requires SQL Server 2016 for some of the sys.dm_exec_query_stats output columns, but should still be provable with SET STATISTICS TIME ON or other tools on earlier versions); it shows larger memory and longer runtimes for the same query against the same data - the only difference is the declared size of the columns:

-- create three tables with different column sizes
CREATE TABLE dbo.t1(a nvarchar(32),   b nvarchar(32),   c nvarchar(32),   d nvarchar(32));
CREATE TABLE dbo.t2(a nvarchar(4000), b nvarchar(4000), c nvarchar(4000), d nvarchar(4000));
CREATE TABLE dbo.t3(a nvarchar(max),  b nvarchar(max),  c nvarchar(max),  d nvarchar(max));
GO -- that's important

-- Method of sample data pop : irrelevant and unimportant.
INSERT dbo.t1(a,b,c,d)
  SELECT TOP (5000) LEFT(name,1), RIGHT(name,1), ABS(column_id/10), ABS(column_id%10)
  FROM sys.all_columns ORDER BY object_id;
GO 100
INSERT dbo.t2(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
INSERT dbo.t3(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
GO

-- no "primed the cache in advance" tricks
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO

-- Redundancy in query doesn't matter! Just has to create need for sorts etc.
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t1 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t2 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t3 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO

SELECT [table] = N'...' + SUBSTRING(t.[text], CHARINDEX(N'FROM ', t.[text]), 12) + N'...',
s.last_dop, s.last_elapsed_time, s.last_grant_kb, s.max_ideal_grant_kb
FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.[text] LIKE N'%dbo.'+N't[1-3]%' ORDER BY t.[text];

So, yes, right-size your columns, please.

Also, I re-ran the tests with varchar(32), varchar(255), varchar(5000), varchar(8000), and varchar(max). Similar results (click to enlarge), though differences between 32 and 255, and between 5,000 and 8,000, were negligible:

Here's another test with the TOP (5000) change for the more fully reproducible test I was being incessantly badgered about (click to enlarge):

So even with 5,000 rows instead of 10,000 rows (and there are 5,000+ rows in sys.all_columns at least as far back as SQL Server 2008 R2), a relatively linear progression is observed - even with the same data, the larger the defined size of the column, the more memory and time are required to satisfy the exact same query (even if it does have a meaningless DISTINCT).


Does Microsoft Security Bulletin MS11-049 affect SQL Server 2008 SP3?

Scroll down to Security Update Deployment and open that up. Then open SQL Server 2008. You will see that SP3 includes this fix.

If your tool is reporting this problem because it doesn't detect the patch then one of the following is true:

The tool is wrong.
The tool has additional information not listed on that page.

If your tool is reporting the problem because it's using an exploit to test the vulnerability, then I would take that up with Microsoft.


mongodb wont read config file using upstart

It seems as though it was reading the config file but reading it in a different way that it was ignoring a certain directive. I tried setting smallfiles to true which worked when starting manually. But in service mode I got it to work by disabling journaling. Not sure if this is a bug.


Why aggregate query with lookup is extremely slow?

It is slow because it is not using an index.  For each document in the logs collection, it is doing a full collection scan on the graphs collection.
From the $expr documentation page:

$expr only uses indexes on the from collection for equality matches in a $match stage.


SQL Query to select all under certain age using only date of birth

This is totally possible.

SELECT *
FROM <TableName>
WHERE DOB > DATEADD(yy,-20,GETDATE())
  AND Sex = 'F'


In SQL Server parlance, an "Agent" is an external executable that runs outside the SQL Server process. What you see in the job step are the command line parameters for that executable, not a T-SQL script.

The Merge Replication Agent is actually an executable: replmerge.exe. If you want to execute this from outside the SQL Agent job, you would open the command prompt on the server, and run the replmerge.exe command, followed by the contents of the job step:

replmerge -Publisher [Publisher\Instance] -PublisherDB [Database] -Publication [Publication _name] -Subscriber [Subscriber\Instance] -SubscriberDB [SubscriberDB] -Distributor [Distributor \Instance] -DistributorSecurityMode 1

For more info on the replmerge executable, and the available parameters, see the online documentation.


Mysql same DB, same query, different users - different error messages

It looks like this issue is specific to Mysql 8.0.16. I've upgraded to 8.0.21 and I now get 1451 as expected.
Longer answer: 1217 (ER_ROW_IS_REFERENCED) and 1451 (ER_ROW_IS_REFERENCED_2) both describe the same situation. As far as I understand, Mysql will use 1451 if user has a REFERENCES privilege for the table of interest, and 1217 otherwise. And it's the same story about 1216 (ER_NO_REFERENCED_ROW) vs 1452 (ER_NO_REFERENCED_ROW_2).


Deconstruct json and insert/update it into a different table in an updatable view

From the docs on Updatable Views

  Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:

  The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
  The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
  The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
  The view's select list must not contain any aggregates, window functions or set-returning functions.

Your view is twice-over not "automatically" updatable. However,

  You can get the effect of an updatable view by creating INSTEAD OF triggers on the view, which must convert attempted inserts, etc. on the view into appropriate actions on other tables. For more information see CREATE TRIGGER. Another possibility is to create rules (see CREATE RULE), but in practice triggers are easier to understand and use correctly.

I strongly suggest you don't go down that route though and you consider fixing your schema and moving away from jsonb. It's not made to be used in that fashion.

Here is how you can think of the problem, JSON isn't for presentation. You're storing your data in a schema. You're rendering it into JSON and obscuring  that schema entirely. Going back to the schema from JSON is going to require you to create a translation layer as complex as the whole database itself. Instead,

make the queries on the underlying data in your application. Make the JSON in the application when you need to send it to the client. And, make the updates on the underlying schema like you would normally do.
or, use something like PostgREST which defaults to sending out JSON when you write the base query using its API. This method does not collapse your schema. Then it provides you the ability to write against that as well (through other API calls).

Shy of all of that, to proceed start by creating your stored proc, and then set it as a trigger..

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON test_view
    FOR EACH ROW
    EXECUTE PROCEDURE test_trigger_function();


The perhaps obvious solution would be to modify step 3: Don't just delete all rows from the active table. Instead, find a column you can use as an indicator of whether the rows are newer or older than the most recent row that will be copied to the archive table/database.

This column can be e.g. an integer used for the primary key column, or if you don't have that, perhaps a timestamp column.

So instead of deleting every row, you can do something like:

DELETE FROM active_table WHERE id <= @maxIdThatWasArchived;

Or something like:

DELETE FROM active_table WHERE ts <= @maxTimeStampThatWasArchived;


There is nothing wrong with using xpath_table().

Even though some of the functions in the xml2 extension have been replaced with in-core functions, xpath_table is not one of them (I think this will happen for Postgres 10).

Until then, xpath_table() seems to be your only option:

SELECT *
FROM xpath_table('id',
            'documents',
            'xmldata',
            '//test',
            'true') AS t(doc_id integer, data text);

Returns:

doc_id | data
-------+---------
     1 | Red text
     1 | green

In order for this to work you first need to create the extension (as a superuser):

create extension xml2;

Otherwise the function isn't available.


MS Access migration from Oracle to SQL server?

Create new linked tables to the SQL Server, drop the Oracle-linked-tables, create new queries with the names of the original Oracle-linked-tables, and presto.

Having said that, if you do this in the Access user interface, Access will attempt to modify the existing queries automatically.  You can change this behavior by deselecting the following options:

Caveat:  You'd clearly want to do this on a test-copy of the database, then test all the functionality before declaring this a win!


from experience i can say that one large delete is usually better. however, there can be some corner cases with IN, which might invalidate my statement but basically this is true.
Make sure your got enough work_mem around to allow PostgreSQL to nicely hash the IN.


hierarchical retreival of records in postgresql

This is a simple adjacency list traversal problem. You're not even trying to build paths, just find nodes from a root node or set. That's pretty much a textbook example of what a recursive CTE is for.

http://sqlfiddle.com/#!12/8c406/12

WITH RECURSIVE children(product_content_id, product_master_id, content_id) AS (
  SELECT pc.product_content_id, pc.product_master_id, pc.content_id
  FROM product_content pc
  WHERE pc.product_master_id = '1'
  UNION
  SELECT pc.product_content_id, pc.product_master_id, pc.content_id
  FROM product_content pc
  INNER JOIN children c ON (pc.product_master_id = c.content_id)
)
SELECT
  product_master_id AS mainproduct,
  content_id AS subproduct
FROM children;

You could pretty much adapt this from the PostgreSQL manual, which includes an example on edgelist graph traversal.

Database design side-note: Please don't use character fields for numbers. It's also not usually a good idea to use keys like A10 if the A and 10 parts have any kind of separate meaning at all.


difference between MySQL and noSQL databases

That's a very wide question and hard to answer in a short way. These two are hardly comparable because they aren't the same level of categorization/abstraction.

First of all MySQL is a Relational Database product using SQL from Oracle while noSQL is not a product. It's a (set of) type(s) of "products" or better phrase as a concept. As the name suggests it's not SQL. NoSQL types and products are for example:

key-value stores like Redis, Tokio Cabinet or even Memcache can be mentioned
document database like MongoDb and CouchDB
graph databases like OrientDB
columnar datastores like Hadoop, Cassandra or Bigtable

SQL means Structured Query Language and this is related to how the data can be retrieved. This is not equivalent with Relational Databases. You can find noSQL RDBMS products as well.

Let me put it this way SQL is a language which can be used for data retrieval in relational databases (for example: MySQL, Postgres, Oracle) just like it can be used (however rarely used) in other types of databases/datastores.


why have to write c## for user creation in oracle 12c

You have a container database and you are in the CDB$ROOT container.

In the root container, you can create only common users.

Local Users in a CDB

  A local user cannot be created in the root.

The name of common users must start with c##.

Common Users in a CDB

  "The name of every user-created common user must begin with the
  characters c## or C##. (Oracle-supplied common user names do not have
  this restriction.)"

For an overview of users in a container database, read Overview of Commonality in a CDB


You need to compile it yourself.

I downloaded the tarball and read the README-WIN.txt.  It certainly indicates that it can be compiled on Windows using CMake.


Deploying database changes in high availability environment

If you're talking SQL Server, I have some specific advice about making database changes backward compatible. The ideas and concepts are quite similar to other platforms, but the code samples won't help much.

The basic idea is this: make your changes in such a way that they don't require downtime and that they don't break existing code. For example, if you add a new parameter to a stored procedure, don't make it required. You can start out by making it optional, this way you can change the application to support the new parameter later, and it doesn't break in the meantime (and the code in the procedure can use logic to decide what to do, depending on whether the parameter was supplied). This can allow you to update multiple applications in a staggered way instead of all at once, and also allows you to change the application to the old behavior without also having to roll back the database changes. You can then make the parameter required after all the applications have been updated to support it.

Big depends here, because I have no idea what kind of scope your database changes encompass, but here are some ideas about how I accomplished this for specific changes:

Make your SQL Server database changes backward compatible when adding a new column

Make your SQL Server database changes backward compatible when dropping a column

Make your SQL Server database changes backward compatible when renaming an entity

Make your SQL Server database changes backward compatible when changing a relationship

That all said, your deploy scripts for databases should be using the same type of methodology as your code, including repeatable and verifiable scripts stored in source control as opposed to hand-written instructions about where to point and click in some UI to make a change. Why should your mentality about how to deploy database changes really be any different from how to deploy code cha


bcp in of datetime type data results in an "Invalid date / time format"

I have seen this before.  Ensure your datetimes are formatted like this:

yyyy-mm-ddThh:mm:ss

Example:

2011-01-18T17:30:59 would represent January 18th 2011 at 5:30:59PM

It has been a while since I ran into an issue similar to this, but I remember this being a step I took to resolve my problem.


How to pull high-level server information from Mysql

This info comes from server system variables. Look in the Administration secion -> Status and System Variables:

Here I filtered for all variables that contain version. In plain SQL you would use this query: select @@version, select @@innodb_version etc.
Of course, some of the info is just what you specified when you defined the connection (like socket, ip, port, config file etc.).


Can IMPLICIT_TRANSACTIONS set on database level?

Implicit transactions is a connection setting, it'll be set by the client tools and/or in the scripts only.


The MERGE statement has a complex syntax and an even more complex implementation, but essentially the idea is to join two tables, filter down to rows that need to be changed (inserted, updated, or deleted), and then to perform the requested changes. Given the following sample data:

DECLARE @CategoryItem AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL,

    PRIMARY KEY (CategoryId, ItemId),
    UNIQUE (ItemId, CategoryId)
);

DECLARE @DataSource AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL

    PRIMARY KEY (CategoryId, ItemId)
);

INSERT @CategoryItem
    (CategoryId, ItemId)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 3),
    (3, 5),
    (3, 6),
    (4, 5);

INSERT @DataSource
    (CategoryId, ItemId)
VALUES
    (2, 2);

Target

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          2 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      3 ║
║          3 ║      5 ║
║          4 ║      5 ║
║          3 ║      6 ║
╚════════════╩════════╝

Source

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

The desired outcome is to replace data in the target with data from the source, but only for CategoryId = 2. Following the description of MERGE given above, we should write a query that joins the source and target on the keys only, and filter rows only in the WHEN clauses:

MERGE INTO @CategoryItem AS TARGET
USING @DataSource AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY SOURCE
    AND TARGET.CategoryId = 2
    THEN DELETE
WHEN NOT MATCHED BY TARGET
    AND SOURCE.CategoryId = 2
    THEN INSERT (CategoryId, ItemId)
        VALUES (CategoryId, ItemId)
OUTPUT
    $ACTION,
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

This gives the following results:

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
╚═════════╩════════════╩════════╝
╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      2 ║
║          3 ║      5 ║
║          3 ║      6 ║
║          4 ║      5 ║
╚════════════╩════════╝

The execution plan is:

Notice both tables are scanned fully. We might think this inefficient, because only rows where CategoryId = 2 will be affected in the target table. This is where the warnings in Books Online come in. One misguided attempt to optimize to touch only necessary rows in the target is:

MERGE INTO @CategoryItem AS TARGET
USING
(
    SELECT CategoryId, ItemId
    FROM @DataSource AS ds
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $ACTION,
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

The logic in the ON clause is applied as part of the join. In this case, the join is a full outer join (see this Books Online entry for why). Applying the check for category 2 on the target rows as part of an outer join ultimately results in rows with a different value being deleted (because they do not match the source):

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          1 ║      1 ║
║ DELETE  ║          1 ║      2 ║
║ DELETE  ║          1 ║      3 ║
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
║ DELETE  ║          3 ║      5 ║
║ DELETE  ║          3 ║      6 ║
║ DELETE  ║          4 ║      5 ║
╚═════════╩════════════╩════════╝

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

The root cause is the same reason predicates behave differently in an outer join ON clause than they do if specified in the WHERE clause. The MERGE syntax (and the join implementation depending on the clauses specified) just make it harder to see that this is so.

The guidance in Books Online (expanded in the Optimizing Performance entry) offers guidance that will ensure the correct semantic is expressed using MERGE syntax, without the user necessarily having to understand all the implementation details, or account for the ways in which the optimizer might legitimately rearrange things for execution efficiency reasons.

The documentation offers three potential ways to implement early filtering:

Specifying a filtering condition in the WHEN clause guarantees correct results, but may mean that more rows are read and processed from the source and target tables than is strictly necessary (as seen in the first example).

Updating through a view that contains the filtering condition also guarantees correct results (since changed rows must be accessible for update through the view) but this does require a dedicated view, and one that follows the odd conditions for updating views.

Using a common table expression carries similar risks to adding predicates to the ON clause, but for slightly different reasons. In many cases it will be safe, but it requires expert analysis of the execution plan to confirm this (and extensive practical testing). For example:

WITH TARGET AS
(
    SELECT *
    FROM @CategoryItem
    WHERE CategoryId = 2
)
MERGE INTO TARGET
USING
(
    SELECT CategoryId, ItemId
    FROM @DataSource
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $ACTION,
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

This produces correct results (not repeated) with a more optimal plan:

The plan only reads rows for category 2 from the target table. This might be an important performance consideration if the target table is large, but it is all too easy to get this wrong using MERGE syntax.

Sometimes, it is easier to write the MERGE as separate DML operations. This approach can even perform better than a single MERGE, a fact which often surprises people.

DELETE ci
FROM @CategoryItem AS ci
WHERE ci.CategoryId = 2
AND NOT EXISTS
(
    SELECT 1
    FROM @DataSource AS ds
    WHERE
        ds.ItemId = ci.ItemId
        AND ds.CategoryId = ci.CategoryId
);

INSERT @CategoryItem
SELECT
    ds.CategoryId,
    ds.ItemId
FROM @DataSource AS ds
WHERE
    ds.CategoryId = 2;


Why are there no indexes contained in the information_schema database?

Also the way the information schema tables is generated internally means that the entire table is generated before a single attempt at filtering is done on the table.
An index has no meaningful effect on information_schema tables.
So for performance considerations, don't overly use information_schema tables, there could be performance impacts on the entire system.
note: above refers to MySQL-5.7 and MariaDB information_schema. I haven't looked at the MySQL-8.0 implementatoin.


DB2 LOAD: Poor performance while loading table with large BLOBs

As Chris already mentioned LOAD is a utility and therefore the UTIL_HEAP_SZ must be set high enough (but don't ask too much, always monitor the memory free space with top). You need a lot of memory for a fast load (see DATA BUFFER below).

Here are things that speed up the load:

More containers for the tablespace where the table is (best on separate luns / disks)
DATA BUFFER parameter (default 25% of UTIL_HEAP_SZ), make it bigger 400-800 (4K) per CPU core available.
ANYORDER (allows LOAD to abandon the input order, bad if you load in clustering order) can give 30% boost
FASTPARSE (use only if data is clean e.g. unloaded from other table) can give 15% boost
SAVECOUNT - the higher the better I use #input records/10, so for 100M records the savecount is 10000000 - in your case 1 the LOAD has to update the restart data with every record = SLOW!

Note: always collect statistics with STATISTICS YES during load. It is faster than LOAD + RUNSTATS

Good luck and let us know your improvements!


Why is Postgres on RDS maxing out on CPU every few hours?

It was a combination of making too many transactions and too many functions. After combining statements into the same transactions and removing the functions, the CPU hasn't risen past an average of 5%.


How to become a DBA...the non-standard route (not beginning as sysadmin or dev)?

You can add a lot of value to your career at little or no cost except for some of your  time.  You need a desktop computer or server with at least 4 GB of ram. Download Oracle virtual box or pick a pre configured image here.

Install and configure.I learned more by breaking things than by reading in books or videos so don't be afraid to break your database repeatedly as you figure how to use the tools of the trade. With virtual machines you can take a snapshot, break the image and revert to the saved snapshot.

Once you have installed the database you can try adding a schema of your design or play with Oracle APEX to get a quick idea about web applications.

All the Fusion applications are available for download like BEA weblogic and you can play with some Hello world apps in Java.

Then try and duplicate some of the things you need to do at work like backup, tuning, RMAN and anything else you need to practice on.  It won't be easy but it will reward you with hands on experience which will give you confidence for work tasks.


There is no "default" order in the RDBMS. Just because tables are not lists but sets. Imagine records as a cards in a big bag, not in a stack. There is no guarantee that cards will be fetched from the bag in some specific order.

Yes, cards are enumerated by primary key, but you have to request the fetching in that specific order. It is not the default order when order is not specified.


Why public is not mentioned as a fixed role in system database in SQL Server?

Public is just an odd duck.

But to apply some retroactive continuity to the documentation you might say that all these built in roles are "fixed" or unchangeable in ways that normal roles are not.  The roles marked as is_fixed_role have fixed permissions and changeable membership.  Public has fixed membership (everyone) and changeable permissions.

The column in sys.server_principals and sys.database_principals should be documented to clarify this.


PostgreSQL - how to list all UNIQUE indexes for specific database?

You need to use pg_index and limit to those that have indisunique. To get the actual index and table names, you need to join that table to pg_class and pg_namespace:

select idx.relname as index_name,
       insp.nspname as index_schema,
       tbl.relname as table_name,
       tnsp.nspname as table_schema
from pg_index pgi
  join pg_class idx on idx.oid = pgi.indexrelid
  join pg_namespace insp on insp.oid = idx.relnamespace
  join pg_class tbl on tbl.oid = pgi.indrelid
  join pg_namespace tnsp on tnsp.oid = tbl.relnamespace
where pgi.indisunique --<< only unique indexes
  and tnsp.nspname = 'public' --<< only for tables from the public schema


Is it possible to have 2 backup plans for a single database?

You can have sysadmins doing COPY_ONLY backups and DBA's doing regular FULL, DIFF and/or Transaction log backups.

The key is COPY_ONLY backups for allowing ad-hoc backups. But they cannot be used for point in time recovery.

Note that taking a random Transactional log backup (without using COPY_ONLY) will break the log chain and you wont be able to do a point in time recovery.

I dont understand the reasons for taking separate backups, as your entire goal should be to be able to restore your backups and bring up your system (RTO and RPO) thereby meeting your SLAs.

Alternatively, if only full backups are taken by your sysadmins, then you can Restrict users to COPY ONLY backups.


How to fix issue uuid_generate_v1() does not exist in postgresql , actually i have the function under public schema

Apparently, the CREATE FUNCTION statement used a wrong search path. (This is not shown by \df.)

You can either tell the function to use the value in the environment, or set it explicitly to a correct value:

ALTER FUNCTION da.test_get RESET search_path;

ALTER FUNCTION da.test_get SET search_path = da, public;


Per BOL:

blocking_session_id
ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
-2 = The blocking resource is owned by an orphaned distributed transaction.
-3 = The blocking resource is owned by a deferred recovery transaction.
-4 = Session ID of the blocking latch owner could not be determined at this time because of internal latch state transitions.

I did a write up on it here.  However the simple way to get rid of it

I wasn't able to find a whole lot of information on -4 spids.  Everything I was able to find sounded like a tempdb issue.  This is the solution I use to get rid of -2s but it should work for you:

 SELECT request_owner_guid
 FROM sys.dm_tran_locks
 WHERE request_session_id = -4

Take each row from the result and use it to create a KILL statement.

KILL 'D21D8DAB-C75C-411A-879B-FFDE10E3F397'

Be warned however we do have a system that gets -2s (I know that's different but even so) on a regular basis and if you kill them it can cause problems with the system.  If this is the only time you've seen it I think you'll be fine though.


Things affected globally in a postgresql transaction

alter table mytable enable trigger all; cannot affect concurrent transactions directly because the command takes an exclusive lock on the table before the change is applied. And the lock stays until the end of the transaction. (Of course, the lock itself can have indirect effects on concurrent transactions.)

Some effects that are visible to concurrent transactions before COMMIT (or even after ROLLBACK):

Locks, obviously (this is how other conflicts are avoided).
Sequences (like you already mentioned).
Entries in unique indexes (conflict with dupes even when still uncommitted).
Data written to log files (or anything else written to files).
Effects of dblink calls (emulating autonomous transactions, effectively).
Changes to prepared statements (only matters in case of a ROLLBACK, since prepared statements are only visible within the same session).
Long running transactions also can keep VACUUM from doing it's job, since rows still potentially visible in the snapshot cannot be removed. Can even lead to transaction ID wraparound in very extreme cases. (Well, other security measures kick in before that actually happens.)

I am sure there is more ...

Some commands cannot be executed inside a transaction at all. Like CREATE DATABASE, CREATE INDEX CONCURRENTLY or VACUUM.

Related:

Do stored procedures run in database transaction in Postgres?
How to use RETURNING with ON CONFLICT in PostgreSQL?
In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?


The included field order doesn't matter since there's no sorting involved - those fields are available at the leaf level of the index for every row.

There's not really a disadvantage to consolidating them like in your second example, except for page density on that index (more page reads per number of rows since there's more data stored for each row).

There ARE measurable advantages, though - for updates/inserts on Col1 you only need to update one index instead of two, and you will save a lot of space by not keeping duplicate key entries for col1 in two separate indexes.

The only scenario I can think of that would make sense to have the two narrower indexes is if one of  those was designed specifically for a mission critical procedure or view.  If you have an index that includes only what you need for a certain query that gets run with high frequency it would make sense to keep it as streamlined as possible.


Replace long GROUP BY list with a subquery

If you change the left join with dbo.Haul to a subquery, it will calculate these distinct values of ID_DestinationAddress (Stream Aggregate) and Count them (Compute scalar) directly after getting the data from the scan.

This is what you are seeing in the execution plan:

While, when using the GROUP BY method it is only doing the grouping after data passed through the left join between dbo.Haul and dbo.[Address].

How much better it is will depend on the unique value ratio of dbo.Haul. Less unique values means a better outcome for the second execution plan, since the left join has to process less values.

The other positive result of the second query is that only the uniqueness of ID_DestinationAddress is calculated, not the uniqueness of all the columns as a whole in the group by.

Again, you should test & validate the results for your query, dataset & indexes. One of the ways to test if you are not familiar with execution plans is setting SET STATISTICS IO, TIME ON; before executing the queries and making these runtime stats more readable by pasting them in a tool such as statisticsparser.

Testing

A small test to show what differences in data can do for these queries.

If the dbo.Haul table does not have many matches with the 5 records returned by the FULLTEXT index filtering, the difference is not so big:

Group by query plan

Subquery Query plan

1000 rows could get filtered earlier, but the execution time is around 15ms for both queries anyway on my machine.

Now, if I change my data so these 5 records have many more matches with dbo.Haul on the left join:

The difference between the group by query

    <QueryTimeStats CpuTime="1564" ElapsedTime="1566" />

And the Subquery becomes more clear

and the stats:

<QueryTimeStats CpuTime="680" ElapsedTime="690"/>


Search and compare list of ever-growing IPs

I'm doing this at work. I use INET_ATON() to IPv4 and INET6_ATON() to IPv6. This search is faster than VARCHAR fields search. But, one important point:

This function was added in MySQL 5.6.3.
INET_ATON()   Return the numeric value of an IP address.
INET_NTOA()   Return the IP address from a numeric value.
INET6_ATON()  Return the numeric value of an IPv6 address.
INET6_NTOA()  Return the IPv6 address from a numeric value.

Documentation: MySQL Misc


Why does adding a transactional replication publication change all the tables and columns to "not for replication"?

Not sure if you have checked this; but there is an option in sp_addarticle called @identityrangemanagementoption. If set to manual, then it "Marks the identity column using NOT FOR REPLICATION". Pls check if this is the case.

Ref : https://msdn.microsoft.com/en-us/library/ms173857(v=sql.110).aspx


Standard Edition is limited to a 64 GB buffer pool.

To use all the available memory of the server, you'll need to upgrade to either Enterprise or Datacenter Edition, or scale out to multiple instances of Standard Edition.


I'm having this exact same problem right now.
I have written a sample java Application to demonstrate the problem:
MariaDBTestUUID.java:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MariaDBTestUUID
{
    private Connection connect;
    private Statement statement;
    private ResultSet resultSet;

    public void updateUUID() throws Exception
    {
        try
        {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("org.mariadb.jdbc.Driver");
            // Setup the connection with the DB
            connect = DriverManager
                    // AWS Aurora (MySQL server 5.6.10) - found with SQL "show variables like '%version%';"
                    .getConnection("jdbc:mariadb://<hostname>:3306/<DB name>?user=<user>&password=<password>");
//                     // local MySQL server (5.7.22)
//                    .getConnection("jdbc:mariadb://localhost:3306/licenceserver4?user=root&password=<password>");
//                     // docker container MySQL server (5.7.30)
//                    .getConnection("jdbc:mariadb://localhost:3307/testDB?user=root&password=<password>");

            // PreparedStatements can use variables and are more efficient
            PreparedStatement preparedStatement =
                connect.prepareStatement("UPDATE testUUID SET new_id = uuid(), new_id2 = REPLACE(uuid(), '-', ''), new_id3 = UNHEX(REPLACE(uuid(), '-', ''))");
            preparedStatement.executeUpdate();

            queryStrings("select ID, new_ID, new_ID2, hex(new_ID3) as new_ID3 from testUUID");
            queryStrings("select count(distinct(new_ID)) as distinct_rows from testUUID");
            queryStrings("select count(distinct(new_ID2)) as distinct_rows2 from testUUID");
            queryStrings("select count(distinct(new_ID3)) as distinct_rows3 from testUUID");
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            close();
        }
    }

    private void queryStrings(String sql) throws SQLException
    {
        System.out.println("Running \"" + sql + "\"... ");
        statement = connect.createStatement();
        // Result set get the result of the SQL query
        resultSet = statement
            .executeQuery(sql);
        writeResultSet(resultSet);
    }

    private void writeResultSet(ResultSet resultSet) throws SQLException
    {
        // ResultSet is initially before the first data set
        while (resultSet.next())
        {
            // we can only do this because we know they are all strings
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++)
            {
                String value = resultSet.getString(i);
                System.out.print(resultSet.getMetaData().getColumnName(i) + ": " + value + ", ");
            }
            System.out.println();
        }
    }

    // You need to close the resultSet
    private void close()
    {
        try
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connect != null)
            {
                connect.close();
            }
        }
        catch (Exception e)
        {
            System.out.println("Error closing:");
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception
    {
        new MariaDBTestUUID().updateUUID();
    }
}

The version of the client library is set here:
build.gradle:
plugins {
    id 'java'
}

sourceCompatibility = 11

repositories {
    mavenCentral()
}

dependencies {
    // DB driver
    runtimeOnly 'org.mariadb.jdbc:mariadb-java-client:2.2.4'
}

To run the test application, just:
1 - connect to each of the DBs and run the following queries:
create table testUUID (ID int, new_ID varchar(100), new_ID2 varchar(100), new_ID3 BINARY(16));
insert into testUUID (id) values (1),(2),(3);

2 - replace the connection info masked for security reasons and run it on each of the DBs.
3 - set the version of mariadb client to use in the test in build.gradle
4 - run main()
If the output shows 3 for each of the distinct queries, the update stmt created distinct values in each column.
If it shows 1, the update set the same value to all rows for that column.
The queries should all return 3.
What I saw, for all DBs:

With mariadb client 2.2.3, it returns 3 for all queries.
With mariadb client 2.2.4, it returns 1 for the second and third queries (new_ID2 and new_ID3).

So, my findings so far:

Everything worked without issues (all columns in every row were set with distinct values) until version 2.2.3 of the mariadb client library.
From version 2.2.4, it started setting identical values on every row for columns we were setting to replace(uuid(),'-', '') using a single update statement.
This happens with several further mariadb client versions as well, including the latest (2.7.0).

Either the issue is related to the REPLACE() function, or to the nesting of function calls in the update. It is definitely not caused by the function UUID().

The issue happens equally in MySQL 5.7.22 (locally installed in my env), MySQL 5.7.30 (test docker container), and more importantly, Aurora DB (MySQL 5.6.10).

----------------------------------------------------
EDIT
----------------------------------------------------
For me, this was caused by a conjunction of:

the database charset
a change in MariaDB J/Connector starting at version 2.2.4
the combination of UUID() and String processing functions (like REPLACE())

Adding CONVERT() to the picture solved it:
UPDATE sometable SET uuid=UNHEX(REPLACE(CONVERT(UUID() using utf8mb4), '-', '')) WHERE uuid IS NULL;
For more details (and an upvote): https://stackoverflow.com/a/51393124/5154619


Enforcing existence of records while avoiding circular references

I think that your problem could be solved by imposing a new Foreign Key constraints in your tables:

User (UserId, UserOther) PK(UserId)
Customer (CustomerId, CustomerOther, UserAdminId) PK(CustomerId)
Customer_User (CustomerId, UserId), PK(CustomerId, UserId)

In Customer_User:
  FK(CustomerId) REFERENCES Customer
  FK(UserID) REFERENCES User

In Customer:
  FK(UserAdminId) REFERENCES User
  FK(CustomerId, UseAdminId) REFERENCES PK(Customer_User)

In this way you are guaranteed that a Customer has a valid User as admin, and that there is a tuple in Customer_User that corresponds to a the admin of a certain Customer.


I would start looking at repmgr from 2ndQuadrant. It is an open source replication management framework for PostgreSQL and takes care of things like failovers.

Otherwise you need to make sure the other node is dead (shoot the other node in the head), then remove the recovery.conf and restart.  Note that at this point you will have irrevocably failed over and you cannot fail back until you set up the former master as a slave to the new master and then fail over again.


SQL Server Management Studio SSMS 18.0 General Availability release (GA) install fails

From the command line, I was able to uninstall a different 18.0 preview (I don't have 6 anywhere):

msiexec /x "{2051D5D4-618D-4B9F-BA64-C1AE19003F2E}"

But I did not have the error about the shell msi, and I'm not sure if the CLSID changed, so I'm not sure that will work for you. (I'm not convinced the GUID in the dialog you're getting is the one you need for SSMS.)

Stolen shamelessly from my own blog post about uninstalling Evaluation Edition which fails if the product has expired.


How to view a list of databases under server in SQL Anywhere like we do in SQL Server

You have to create a database and then start the database server.

On installation of SQL Anywhere it does not automatically setup a database server/service.

Once it is running, you can connect as DBA to it with SQL Central or other tools.


I maybe found two solution for you:

HIDE

Hide Table "Person.Address"
EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

Show Table "Person.Address"
EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

DENY

`DENY VIEW DEFINITION ON Schema.Table TO UserName;`

Now UserName won’t be abe to see Table in Object Explorer. In Fact, they won’t be able to see the table in sys.tables or INFORMATION_SCHEMA.TABLES.
You can Use the DENY keyword to deny certain Users and REVOKE to Remove the existing permission.


Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server

Microsoft reserves the right to change the binary representation of the build-in data types like DATETIME. So if you do an

INSERT dbo.table(binaryColumn)VALUES(CAST(GETDATE() AS VARBINARY(8)));

and then you upgrade SQL Server or even do an SP apply

SELECT CAST(binaryColumn AS DATETIME) FROM dbo.table;

might get you a different date then the one originally stored.

However, if you store you own binary data that was generated outside of SQL Server (e.g. a pdf file), that data will not be affected by such a change.

This by the way is not new. Microsoft always said that they might change the binary representation at any time. They might just have made it more obvious in the new BOL version.

That all being said, it is still very unlikely that Microsoft is going to change the binary representation of say an integer. However, something like a geospatial, which is implemented as a CLR type, is very likely to change between versions.

As per Paul's comment: SQL Server does indeed guarantee that you can convert a build-in data type to binary and back within the same statement. To extend that to the same session when storing the intermediate value in tempdb however I would already consider risky - a very small risk, but a risk. Storing the intermediate value in a real table to pick it back up at a later time is a definite no-no.


First of all, NOT IN is not the same as NOT EXISTS.

create table customer (customerid number);
create table purchasebill (customerid number, retailoutletid number);
create table retailoutlet (retailoutletid number);

insert into customer values (1);
insert into customer values (2);

insert into purchasebill values (1, 1);
insert into purchasebill values (null, 3);

insert into retailoutlet values (1);
insert into retailoutlet values (3);

commit;

Let's find customers without purchasebill (customerid = 2). FIrst with a naive NOT IN:

SQL> select * from customer c where c.customerid not in (select p.customerid from purchasebill p);

no rows selected

Now with NOT EXISTS:

SQL> select * from customer c where not exists (select 1 from purchasebill p where p.customerid = c.customerid);

CUSTOMERID
----------
         2

NOT IN did not provide the result we expected. It's because the way NOT IN works. If any row of the subquery returns NULL, NOT IN will return FALSE, so no result in this case.

Now your queries:

SQL> select * from customer c where not exists (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

no rows selected

SQL> select * from customer c where customerid not in (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2

The problem with the NOT EXISTS query is that you have customer c both in the outer query and the subquery. The subquery uses customer c from its inside, not the customer c from the outer query. Because of this, the subquery always returns data, so NOT EXISTS will always evaluate to FALSE, hence no result.

The NOT IN version works, because the inner query is not a correlated subquery, it does not use customer c from the outside, but the inside. The inner query returns customers who made purchases, without NULLs, because the join to purchasebill p eliminates them. With no NULLs to worry anymore, NOT IN works as naively expected.

If you want to make NOT EXISTS work, remove customer c from the subquery, so it becomes a correlated subquery:

SQL> select * from customer c where not exists (select 1 from purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2


FileStream files won't deleted after records deleteion

Unneeded filestream container files are physically deleted by a separate background garbage collection process. The files will eventually be deleted to reclaim the storage, just not in real time.

You can force garbage collection to occur immediately using sp_filestream_force_garbage_collection.


Restoring PostgreSQL 9.1.9 backup with 9.3.0 results in error

Update 10/28

Using pg_dump it is possible to backup a database as a tar archive

pg_dump -v -o -c -Ft -f test_backup.tar testdb

Here are the contents of the test_backup.tar file

-bash-4.1$ tar -tvf test_backup.tar
-rw------- 2048/1024      1605 2013-10-28 14:50 toc.dat
-rw------- 2048/1024         5 2013-10-28 14:50 1769.dat
-rw------- 2048/1024      1654 2013-10-28 14:50 restore.sql

To restore the backup.tar file, I used this command

pg_restore -c -Ft test_backup.tar -d testdb

-c is to drop objects being restored from the database

-Ft is to use the tar archive format

-d is the name of the database

-v is for verbose

So in conclusion, psql can't be used to restore or create files in the tar format. You must use pg_dump to create them using the -Ft option and then again with pg_restore to restore using the same file.

Craig


Access 2010 Need help connecting/updating database data

Alright guys, here's what I did.  I basically exported what I had, brought it into Excel, and wrote some code to restructure the data.  Now I have more records, but far less columns.  Now each Fitting ID will easily correspond to its respective description in the second table.  and here's the code for anyone who's interested:

Private Sub restructure()
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = ThisWorkbook.Worksheets("tblMaster")
Set sh2 = ThisWorkbook.Worksheets("tblNew")

Dim lastRow As Long
Dim lastCol As Long

Dim sourceRow As Long
Dim sourceCol As Long
Dim destRow As Long
Dim destCol As Long
Dim initRow As Long
Dim initCol As Long

Dim n As Long
Dim m As Long
Dim coolerID As String

lastRow = sh1.Range("A2", sh1.Range("A2").End(xlDown)).Rows.Count
lastCol = 20

sourceRow = 2
sourceCol = 2
destRow = 2
destCol = 2
initRow = 2
initCol = 2

For n = 2 To lastRow

    coolerID = sh1.Cells(n, sourceCol - 1).Value

    For m = 2 To lastCol

        sh2.Cells(destRow, destCol - 1).Value = sh1.Cells(sourceRow, initCol - 1).Value

        If IsEmpty(sh1.Cells(sourceRow, sourceCol)) = False Then

            If sourceCol <= 14 Then
                sh2.Cells(destRow, destCol).Value = sh1.Cells(sourceRow, sourceCol).Value
                sh2.Cells(destRow, destCol + 1).Value = sh1.Cells(sourceRow, sourceCol + 13).Value
                sh2.Cells(destRow, destCol + 2).Value = sh1.Cells(sourceRow, initCol + 26).Value
                sh2.Cells(destRow, destCol + 3).Value = sh1.Cells(sourceRow, initCol + 27).Value
                destRow = destRow + 1
                sourceCol = sourceCol + 1
            ElseIf sourceCol > 14 Then
                destRow = destRow + 1
                destCol = initCol
                sourceRow = sourceRow + 1
                sourceCol = initCol
            End If
        End If
        If IsEmpty(sh1.Cells(sourceRow, sourceCol)) = True Then
            destRow = destRow + 1
            destCol = initCol
            sourceRow = sourceRow + 1
            sourceCol = initCol
        End If
    Next m
Next n

End Sub

So glad I got this figured out!  Hope this helps somebody in the future


It can't do an index-only scan in this case.

It needs the actual table row, not just the index entry, of the parent row.  If you do an EXPLAIN VERBOSE, you will see the p.ctid getting exposed.  I believe that is because if the delete encounters a locked row in the child table, it will block until the transaction locking that row finishes.  But once that happens, it has to re-check the parent row to check if it was changed by the same transaction that had just changed the child row.

And of course it can't do an index-only scan on the child, because how would you delete a row in the table without accessing the row in the table?

But anyway, why would doing one index-only scan plus 10,000 individual index scans be faster than one hash join?  Hash joins are pretty awesome.


Why can't we perform joins in a distributed database like Bigtable?

Bigtable doesn't use SQL (a query language) so SQL can't be used directly to query the database. And Bigtable doesn't have "relations" in the same way as relational databases, it's more like bare tables.

If you want to get data from two tables, you have to do two lookups, and combine the result set in the application code. In other words the "join" operation is not built-in in the database, so you have to do that kind of operations in the application, if needed.

The fact that Bigtable doesn't support SQL and JOIN operations has nothing to do with that it is a distributed database system. There are distributed databases that has support for SQL. E.g. VoltDB and MySQL Cluster. There are also many databases that aren't distributed and doesn't use SQL e.g. Kyoto Cabinet.


I have been troubled in this problem so long,and sorry to say that this does not recur all the time.

First, according to official documents, connections are usually established and authorized in the initiation.Then I traced the program to ensure it. Only one random server id is needed in an query, if no error.So liner ascending latency shall never happen as server number increase.

I wrote a test this week, and the result is that no matter how many mongos server are listed in the uri, the latency of the query are randomly distributed in between 3ms to 30ms. Extreme cases happened when network got worse.

This is totally different to the case when I asked this question.So I have to blame it on these changes:

1. More mongos server are available now ,and queries are separated.
2. Most clients are using single mongos server now, so less tcp connections exist and network is better.
3. We improved our program and queries from clients are less and lighter.

Actually , except from the liner ascending latency, some queries returned in above 16 second two weeks ago.

I guess that liner increased latency is just a form of performance degradation.


Is splitting a 'users' table for authentication purposes a good idea?

IMHO You do not need to physically split it up. Yet, it would be nice to cache it.

If the users table uses the MyISAM Storage Engine, you have a nice advantage.

Since MyISAM only caches indexes, you could do two things

You could create a custom key cache just to load MyISAM index for the users table only
You could index the username and password to force the query to hit that custom key cache only

Make sure the following indexes exist for users

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

There are two(2) major reasons for the two indexes

REASON for index #1

The index username_ndx prevents a username from having multiple passwords, as well as prevents multiple users with the same name

REASON for index #2

The index username_password_ndx provides a covering index. Thus, your query will  lookup the username and password in the custom MyISAM cache only, instead of checking the table.

More Links on the Principles of Covering Indexes

http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/
http://shallop.com/2011/04/covering-indexes-in-mysql/
http://www.mysqlconf.com/mysql2009/public/schedule/detail/6796

Next thing is to actually create that custom key cache. Here are the commands to create an 8MB key cache and load that dedicated key cache (Example: If the table is mydb.users):

SET GLOBAL authentication_cache.key_buffer_size = 1024 * 1024 * 8;
CACHE INDEX mydb.users IN authentication_cache;
LOAD INDEX INTO CACHE mydb.users;

You should place these three lines in the file /var/lib/mysql/startup.sql

Add this to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/startup.sql

This will load the cache every time mysql is started up

Give it a Try !!!

UPDATE 2011-12-30 17:25 EDT

If you would like to get the exact size to set the cache, use the following query:

SELECT CONCAT('1024 * 1024 * ',ROUND(index_length/power(1024,2))) RecommendedCacheSize
FROM information_schema.tables WHERE table_name='users';

UPDATE 2011-12-30 23:21 EDT

Here is a method based on InnoDB

You still need the indexes

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

You have to make sure the InnoDB Buffer Pool has the usernames and passwords available. You may have to resort to doing a full index scan upon mysql startup:

Step 1) Create ReadUserPass.sql

echo "select username,password from users;" > /var/lib/mysql/ReadUserPass.sql

Step 2) Add that script to /etc/my.cnf

[mysqld]
init-file=/var/lib/mysql/ReadUserPass.sql

Step 3) Perform one of the following

$ service mysql restart
mysql> source /var/lib/mysql/ReadUserPass.sql

Because both of these columns (username and password) reside in the username_password_ndx, all the index pages making up this index are reloaded into the InnoDB Buffer Pool. This is necessary because there is the possiblility of the index pages being flushed out. To minimize that happening, increase the Buffer Pool Size and restart mysql (one time).