How to restore output from pg_dump into a new table name

This doesn't sound like the right tool for the job in the first place. Couldn't you just COPY...TO, then COPY...FROM?  or insert into new_table select * from old_table, if they are in the same database?

But given that you already have the dump file, the most straightforward way might be to load to a scratch database, rename the table, and retake the dump from there, then drop the scratch database.

Alternatively, you could use "pg_restore" without a "-d" option to dump the custom format file to a plain text dump file, then edit that file manually to change the name in each location it occurs.  Editing a file with 15 millions rows can be quite a chore, but it should be possible with something like "vi", or perhaps "sed" (but there you have to be careful not to change more than you intended to).


First \COPY isn't postgresql. That's the client psql. From the docs,

  Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

No, psql does not provide filename completion. However there are other wrapper around COPY FROM STDIN that are not psql and they do. For instance pgloader provides FROM [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING

  The same clause can also be applied to several files with using the spelling FROM ALL FILENAMES MATCHING and a regular expression. The whole matching clause must follow the following rule:

And on top of that you can always use bash or whatever shell you're using to launch psql. Example with bash filename expansion.

for i in *.csv;
  do psql -c "\copy scheme.table FROM $$D:\path\to\$i.csv$$ DELIMITER ';' CSV ENCODING 'LATIN1';"
done;

Power-shell certainly also provides filename expansion. Google for it if you're on Windows 8+.


Date conversion and integer truncation during import

I suggest that this is a bit cleaner than Vérace's answer:

LOAD DATA
    INFILE 'apa.txt'
    INTO TABLE Test_Stock_Daily
    FIELDS TERMINATED BY ','
    (symbol, @date, open, high, low, close, volume)
    SET date = STR_TO_DATE(@date, '%m/%d/%y');

(And yes, date is a poor choice for a column name.)


Mysql Replication backup and restoration failed multipal time

The error itself is happening because there is a create view (or procedure) statement and the slave doesn't have the user who created it. In mysqldump you will have this format:

CREATE ALGORITHM=UNDEFINED DEFINER=`lipl_ga_app`@`%` SQL SECURITY DEFINER...

That's not specific to replication. It happens even if you would want to restore it to an empty db.

On the replication side

1) One way to do it is mysqldump but you need to LOCK the tables or make sure you don't get any writes during the process of dump (read_only=1 for example). Otherwise you get your slave out of sync from the start. Also there is certainly an impact on the application which is connected to the master. More on this you can read on the mysql doc: http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

To same or higher major version (these cannot be done if master is 5.6 and slave is 5.5):

2) You can also retrieve the data without impacting the master with hot backup solutions like percona Xtrabackup (https://www.percona.com/doc/percona-xtrabackup/2.3/index.html). This is probably going to be faster as well. Once you applied the logs it behaves as normal mysql binaries and you can do the change master command. You will have an xtrabackup_binlog_info file which contains the master position you need to point the slave to.

3) You can stop the master after acquired the master position, copy the binary to the slave, start mysql and change master to ... with the position you saved. This could be also done by snapshotting the filesystem (LVM, ZFS, BTRFS, etc.) if you want to quickly restart mysql. Usually this is the fastest way.

If your version mismatch you might need to run mysql_upgrade since with the latter two you're copying binary data not logical.

A sidenote:

Why are you replicating from 5.6 to 5.5? Replicating from higher version to lower is not guaranteed to work.


How to monitor flat file for changes then run a SQL import job

Below is a SQL Table-Valued Function that I got from Jeff Moden, that will return the File Attributes of a single file:

/*
    Return File Attributes about a single file.

WARNING: Uses the sp_OACreate method, which should be SA use only.

(Note: much of this is copied from Jeff Moden's routines)

TEST:
  SELECT * FROM pps.fnFileInfo('C:\install.exe')

*/
ALTER FUNCTION [pps].[fnFileInfo]( @FileName As VARCHAR(255) )
RETURNS @FileInfo TABLE
(
    RowNum           INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Name             VARCHAR(128), --File Name and Extension
    Path             VARCHAR(128), --Full path including file name
    ShortName        VARCHAR(12),  --8.3 file name
    ShortPath        VARCHAR(100), --8.3 full path including file name
    DateCreated      DATETIME,     --Date file was created
    DateLastAccessed DATETIME,     --Date file was last read
    DateLastModified DATETIME,     --Date file was last written to
    Attributes       INT,          --Read only, Compressed, Archived
    ArchiveBit       AS CASE WHEN Attributes&  32=32   THEN 1 ELSE 0 END,
    CompressedBit    AS CASE WHEN Attributes&2048=2048 THEN 1 ELSE 0 END,
    ReadOnlyBit      AS CASE WHEN Attributes&   1=1    THEN 1 ELSE 0 END,
    Size             INT,          --File size in bytes
    Type             VARCHAR(100)  --Long Windows file type (eg.'Text Document',etc)
) As
BEGIN
    Declare @CurrentName As Varchar(255); SET @CurrentName = @FileName;
    DECLARE @ObjFile          INT          --File object
    DECLARE @ObjFileSystem    INT          --File System Object

    --===== Create a file system object and remember the "handle"
    EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT

    --===== These variable names match the sp_OAGetProperty options
    DECLARE @Attributes       INT          --Read only, Hidden, Archived, etc, as a bit map
    DECLARE @DateCreated      DATETIME     --Date file was created
    DECLARE @DateLastAccessed DATETIME     --Date file was last read (accessed)
    DECLARE @DateLastModified DATETIME     --Date file was last written to
    DECLARE @Name             VARCHAR(128) --File Name and Extension
    DECLARE @Path             VARCHAR(128) --Full path including file name
    DECLARE @ShortName        VARCHAR(12)  --8.3 file name
    DECLARE @ShortPath        VARCHAR(100) --8.3 full path including file name
    DECLARE @Size             INT          --File size in bytes
    DECLARE @Type             VARCHAR(100) --Long Windows file type (eg.'Text Document',etc)

    --===== Create an object for the path/file and remember the "handle"
    EXEC dbo.sp_OAMethod @ObjFileSystem,'GetFile', @ObjFile OUT, @CurrentName

    --===== Get the all the required attributes for the file itself
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Path',             @Path             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortPath',        @ShortPath        OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Name',             @Name             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortName',        @ShortName        OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateCreated',      @DateCreated      OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastAccessed', @DateLastAccessed OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastModified', @DateLastModified OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Attributes',       @Attributes       OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Size',             @Size             OUT
    EXEC dbo.sp_OAGetProperty @ObjFile, 'Type',             @Type             OUT

    --===== Insert the file details into the return table
    INSERT INTO @FileInfo
           (Path, ShortPath, Name, ShortName, DateCreated,
            DateLastAccessed, DateLastModified, Attributes, Size, Type)
    SELECT @Path,@ShortPath,@Name,@ShortName,@DateCreated,
        @DateLastAccessed,@DateLastModified,@Attributes,@Size,@Type

    --===== House keeping, destroy and drop the file objects to keep memory leaks from happening
    EXEC sp_OADestroy @ObjFileSystem
    EXEC sp_OADestroy @ObjFile

    RETURN
END

The way I would use this is to make a table with a single column and record to keep track of the DateLastModified of your import file.  When that changes, run your import then, if successful, update the time value in the table.

Starting an Agent Job from SQL and monitoring its progress and result is actually something of a headache.  It would probably be easier to modify your job as follows:

Add a new step before the existing import step[s] that retrieves the current DateLastModified value from the file, compares it to the saved value in the table, and if the same (or earlier) exits with an error.  Then set the Job Step to "Exit With Success" in the event of an error.  If the date is greater (i.e., an updated import file) then the proc should exit normally and the Job should proceed to the next (import) step(s).
Add another step at the end of the job that reads the DateLastModified value again and updates it to the table.
Finally set the whole Job to run on some reasonable interval, such as every 5 minutes (or alternatively, define the first step to retry every 5 minutes).


The data structure that maintains the bin information grows monotonically during the life of the process. The only way to remove Bin[s] from Aerospike is to first remove the bin from all records containing it followed by a rolling restart of the cluster.

You can find documentation on using scan here

And see DeleteBin.java in aerospike/examples for how to delete a bin.


Where Database Alerts & Operators Exist In SQL Server?

That's all in msdb.  Note that the new server must be at exactly the same version to restore a system database.

  System databases can be restored only from backups that are created on
  the version of SQL Server that the server instance is currently
  running. For example, to restore a system database on a server
  instance that is running on SQL Server 2012 (11.x) SP1, you must use a
  database backup that was created after the server instance was
  upgraded to SQL Server 2012 (11.x) SP1.

Backup & restore: system databases - Limitations on Restoring System Databases


Your mongod version is too old... For 2.5 BI connector

As said here!

  The MongoDB Connector for BI and associated utilities are compatible
  only with MongoDB server version 3.2 or greater.


Where can I see the exact row count of a table in pgAdmin III?

Two lines below the estimate you highlighted we can see:

Rows (counted)  not counted

Right-click and choose Count from the context-menu to get the current count right there. Be aware that the count is only accurate in the moment it is taken.
pgAdmin does not count rows in big tables (because that can be expensive) unless it's told to do so. There is a setting in the Options:

Count rows if estimated less than [2000]

Replace the default 2000 if you want a different limit. Details in the manual here:

http://www.pgadmin.org/docs/1.20/options-browser.html


Always On log files bigger than database files

So I am not sure why is this happening, is it related to Always On?

It is and also isn't related to Always On. Always On Availability Groups requires that the database be in the full recovery model - which is why you need log management in the first place. So long as your secondary replicas are staying very close to the primary (i.e. no lagging secondary replicas for hours or days) log management shouldn't be affected very much due to AGs.

  I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

Always On Availability Groups does not inherently need larger log files. Generally it ends up being the cause because most databases are in the simple recovery model before the change is made to Availability Groups and thus log managements is a new concept to that database.

If the database was in the Simple recovery model and the log grew to X GBs, then the minimum log size will be X GBs regardless of recovery model, Always On, etc. However, since log management isn't automatic anymore a random number (generally 30 minutes or an hour) is arbitrarily chosen and log management happens on those intervals. Now the log will need to grow to accommodate the changes in log management automation.

Much as @Shawn Melton and @RDFozz have stated in the comments - if you don't want the log growing too large and all of your replicas are properly in sync, then have your log management happen more frequently.

  As this is running SharePoint 2013 databases ...

SharePoint is known for having their own timer jobs which run internal maintenance items and storing blob data. This, on its own, will cause poor log management and there is simply nothing you can do about it. Thus it doesn't surprise me that the log the this database is larger than the data files.

It's never a good sign when the datafiles are smaller than the log, but I do believe in this case - unless you change the log management automation to shorter intervals [And even then it depends on what SharePoint does to the DB] it'll most likely end up staying that way - and that might be normal. In that case we can mark it as normal and go about our day.

  We had SQL RAP and we got warning that Transaction log files larger than the database files.

That's a great step in the right direction (I work for Microsoft) of having a risk and health check of your instance(s). This specific check is something that we us to look for databases that may be having improper or no log management. In some cases (I've personally dealt with) the DBA has said, "Ooops that shouldn't be in bulk-logged..." and we've changed it back to Simple because it didn't need any point in time or extended recovery options. That's ok! The check does, however, give everyone a opportunity to look at the databases in question and make sure that something isn't wrong and that's the key distinction. Like I said above, this might just be normal and that's ok - however we wouldn't be doing our jobs properly if we just left it alone and didn't say anything, it's important.

Anecdotally when I was a DBA for a large clothing company, we had a 3rd party application that used a cursor to loop through all employees every 15 minutes and run a stored procedure which deleted all of their history and then rebuilt it from scratch... in a single transaction. The database was 50 GB and the log file for it was.... 280 GB. That's how the app worked, I logged complaints and possible code changes with the 3rd party vendor and was summarily dismissed. It happens and some application just run that way.


Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report

See this link for an explanation about the why and some ways to work around it.

You can increase the session timeout as some sort of workaround, the script is documented in the article.

From the article:

  However, there is one scenario where the ping doesn't work, and that is during the inital report execution.  The problem is that while the report is being executed, the user's session is locked (as we are populating the user's temporary snapshot) and the keepalive from the viewer control will be blocked.  Normally, this is not a problem because report executions aren't supposed to take a long time and quite often they finish before the session timeout hits.  Unfortunately, there are some cases where reports (for whatever reason) take an incredibly long time to execute.  What happens in this case is that the user's session is aged out while the report is being executed, resulting in all sorts of strange behavior.


PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off

There are multiple levels where you configuration options are set in PostgreSQL:

system defaults
values specified in postgresql.conf or the startup command line
values preset for a database, user (maybe others, too) using, for example, ALTER DATABASE ... SET {option} ...
values set for the current session

Going down the list from 2., you have fewer and fewer options to set.  Furthermore, there are some that can only be set by a superuser (log_statement_stats) being one of them.

At the same time, the lower in the list an option is set, the higher its precedence: a session-level setting will override all the upper levels.

Speaking specifically about log_statement_stats, it is possible to set it from a session - but only a superuser can do it.  If your application connects with a superuser role (which is not really recommended), it can set this as one of the first things after obtaining the connection.  Look for it in your sources.

Ways of changing settings

The numbers refer to the levels listed above.

Supplying defaults at compile time
ALTER SYSTEM - this will store the settings in a postgresql.auto.conf file, which will be read in addition to postgresql.conf.
ALTER DATABASE ... SET ... or ALTER ROLE ... [IN DATABASE database_name] SET ....  Some settings can be set on database objects (like tables, functions, etc.), too.
SET \[SESSION\] ... - to change things for the current connection (often dubbed 'session').  There is even a way to change things until the end of the current transaction: SET LOCAL ...


Should I use a subquery to help SQL Server find the correct plan

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments.  I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it.

From what I can see, the advantage of using LEFT JOIN in this case is that it removes the attraction for the plan builder to consider this a good thing (which it isn't, in this case), while still letting the plan builder choose from all the other choices.  The advantage for the subquery is that you still get the INNER JOIN ability to remove any (rare) invalid rows, but you do not let the plan builder include that fact in the critical plan (because the INNER JOIN is in the outside query, not the subquery).  In essence, you are restricting what joins the plan builder can use by placing them in the subquery, and doing the other joins in the main query.

Of course, anytime you restrict the plan builder, you are taking a chance that you restrict it too far and it comes up with a non-optimal plan.  Going forward, I won't necessarily use either technique until I find I need to.  Note that I also tried to use hints or explicitly tell SQL Server to use a particular type of join, but those were never as fast as when I let it choose everything itself.

In the end, it turned out that, due to the ugliness of the code that built this query (on the fly, even), the LEFT JOIN was far easier to implement and have some confidence that I wouldn't break anything, so I went that way.  But that decision was made by factors external to the database.


How concat two sql query in a single result

I assume you can join the queries one way or another (I see no correlation so I'll assume a cross join):

SELECT ...
FROM (
    SELECT ticketing_ticket.email,
    CONCAT(
          TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
          MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
          MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
    ) AS 'temps de traitement '
    FROM ticketing_ticket
    WHERE DATE(date_close) = CURDATE()
) AS X
CROSS JOIN (
    SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM (
            SELECT @r := 38, @l := 0) vars, ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) y;

Mixing old "," joins and ANSI joins makes it difficult to read the code. so I'll suggest that you remove the ";" joins:

SELECT ...
FROM (
    SELECT ticketing_ticket.email,
    CONCAT(
          TIMESTAMPDIFF(day,date_create,date_close) , ' jours ',
          MOD( TIMESTAMPDIFF(hour,date_create,date_close), 24), ' heures ',
          MOD( TIMESTAMPDIFF(minute,date_create,date_close), 60), ' miinutes '
    ) AS 'temps de traitement '
    FROM ticketing_ticket
    WHERE DATE(date_close) = CURDATE()
) AS X
CROSS JOIN (
    SELECT GROUP_CONCAT(T2.label SEPARATOR ';') AS 'Domaines'
    FROM (
        SELECT @r AS _id,
             (SELECT @r := id_parent
              FROM ticketing_category
              WHERE id_category = _id) AS parent_id,
              @l := @l + 1 AS lvl
        FROM ( SELECT @r := 38, @l := 0) vars
        CROSS JOIN ticketing_category m
        WHERE @r <> 0
    ) T1
    JOIN ticketing_category T2
        ON T1._id = T2.id_category
) y;


convert 1 row as column and concatenate the results to the corresponding column

PROPOSED QUERY

SELECT
    A.id,
    IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
FROM apps A
LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
GROUP BY A.id;

SAMPLE DATA

DROP DATABASE IF EXISTS kumar;
CREATE DATABASE kumar;
USE kumar
CREATE TABLE apps
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE contacts
(
    id INT NOT NULL,
    cnt_type VARCHAR(20) NOT NULL,
    cnt_id INT NOT NULL
);
INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
(1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
(1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS kumar;
Query OK, 2 rows affected (0.05 sec)

mysql> CREATE DATABASE kumar;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar
Database changed
mysql> CREATE TABLE apps
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE contacts
    -> (
    ->     id INT NOT NULL,
    ->     cnt_type VARCHAR(20) NOT NULL,
    ->     cnt_id INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
    -> (1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
    -> (1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

SAMPLE DATA IN TABLES

mysql> select * from apps;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | sdf  |
|  3 | dfg  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from contacts;
+----+------------+--------+
| id | cnt_type   | cnt_id |
+----+------------+--------+
|  1 | manager    |     20 |
|  1 | supervisor |     32 |
|  1 | teamlead   |     45 |
|  1 | teamlead   |     54 |
|  2 | manager    |     20 |
|  2 | supervisor |     32 |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT
    ->     A.id,
    ->     IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
    ->     IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
    ->     IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
    -> FROM apps A
    -> LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
    -> LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
    -> LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
    -> WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
    -> GROUP BY A.id;
+----+---------+------------+----------+
| id | manager | supervisor | teamlead |
+----+---------+------------+----------+
|  1 | 20      | 32         | 45,54    |
|  2 | 20      | 32         |          |
+----+---------+------------+----------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

Assuming you are talking about data that is encrypted with SQL Server keys, there is way to find these columns.

The Key_name() Function will return the name of the key used for the encryption for that particular value and will return NULL if there isn't anything encrypted with a "known" key (3rd party, or simple not encrypted).

With that knowlegde we can test every column to see if it contains at least one row which has a varbinary value that returns a key name

functionality of key_name()

--create a test database
CREATE DATABASE [Test_ENCR]
GO

--change context
USE [Test_ENCR]
GO

--because it's possible to encrypt different rows with different keys I'll create 2 keys for this demo
-- Create a symmetric key
CREATE SYMMETRIC KEY symmetricKey1
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password01!';
GO

-- Create a second key
CREATE SYMMETRIC KEY symmetricKey2
   WITH ALGORITHM = AES_128
   ENCRYPTION BY PASSWORD = 'password02!';
GO

--create a table that will have a column holding:
--1: encrypted row with key1
--2: encrypted row with key2
--3: a non encrypted just varbinary value

CREATE TABLE encryptedTable
(ID int IDENTITY PRIMARY KEY,
EncryptedCol varbinary(256) NOT NULL);
GO

-- open key1
OPEN SYMMETRIC KEY symmetricKey1
    DECRYPTION BY PASSWORD = 'password01!';
GO

-- open key2
OPEN SYMMETRIC KEY symmetricKey2
    DECRYPTION BY PASSWORD = 'password02!';
GO

--insert encrypted data with key1
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey1'), 'EncryptedText1'));
GO

--insert encrypted data with key2
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey2'), 'EncryptedText2'));
GO

--insert just varbinary data
INSERT INTO encryptedTable(encryptedCol)
VALUES (CONVERT(varbinary(256),'NotEncryptedTextJustVarBinary'))

--have a look, without the key, all varbinary for you.
SELECT * FROM encryptedTable
GO

results:

--Return all key_names
SELECT DISTINCT     key_name(encryptedcol),
                    EncryptedCol
FROM encryptedTable;

results:

How to implement it to find encrypted columns

--How do we dynamically find all the columns that have at least one row with a encrypted value?

-- first we will find all tables and column with a varbinary datatype
-- then we will test all those columns with a simple select
-- If the key_name() function returns a value, the column and table name are stored together with the keyname

--create a table to hold all varbinary columns and tables
CREATE TABLE #TablesWithVarbinCols (    ID int IDENTITY,
                                TableName nvarchar(128),
                                ColumnName nvarchar(128)
                                );

--create a table to hold the end result
CREATE TABLE #TablesWithEncryption (
                                TableName nvarchar(128),
                                ColumnName nvarchar(128),
                                KeyName varchar(128)
                                );

--find and store all table and column names of user tables containing a varbinary column
INSERT INTO #TablesWithVarbinCols (TableName,ColumnName)
SELECT      o.[name] as TableName,
            c.[name] as ColumnName
FROM        sys.objects o
INNER JOIN  sys.columns c
ON          o.[object_id]=c.[object_id]
INNER JOIN  sys.types t
ON          c.system_type_id=t.system_type_id
WHERE       o.[type]='U'
AND         t.name=N'varbinary'
AND         c.max_length > -1;

DECLARE @col nvarchar(256)
DECLARE @tab nvarchar(256)
DECLARE @c int = 1
DECLARE @MaxC int
DECLARE @SQL varchar(max)

SELECT @MaxC=MAX(ID)
FROM #TablesWithVarbinCols

--loop the previous result and create a simple select statement with a key_name() is not null where clause.
--If you have a result, store the details
WHILE @c <= @MaxC
BEGIN
    SELECT  @Tab=TableName,
         @col=ColumnName
    FROM    #TablesWithVarbinCols
    WHERE   ID=@c

    SET @SQL='  INSERT INTO #TablesWithEncryption (TableName, ColumnName, KeyName)
                SELECT DISTINCT '''+@Tab +''',''' +@col +''', key_name('+@Col +') from '+ @tab +'
                WHERE key_name('+@Col +') is not null;'
    exec (@SQL)

    DELETE
    FROM #TablesWithVarbinCols
    WHERE id=@c;
    SET @c=@c+1
END

--select the result
SELECT * FROM #TablesWithEncryption;

results:

--cleanup
DROP TABLE #TablesWithVarbinCols;
DROP TABLE #TablesWithEncryption;


How to secure SQL Server in an enterprise environment?

Here are couple of comments / advice regarding performance / network:

1) Those 40 guys that currently download data daily, they obviously using "Import" mode, and they added this table as a data source, and Power BI pulls this whole table (33 mln row) every time report refreshes.
What you can do is to work with them to refactor how they query data. If they need an aggregate values from that table, they can use "Import" mode and run an aggregate query instead of pulling whole table each time. This will be MUCH lighter in terms of performance and network consumption

2) why all 40 users each need to have personal gateways ? If those guys really need this data, 1 user should be enough, this user can then share his report to his colleagues so others 39 won't be hitting your SQL Server - they would just read report data from the 1 guy's PC


You can edit your port in your my.cnf file.

port = 3306

As per your edit:

MariaDB is slightly different. Your config file is in this location:

/etc/mysql/mariadb.conf.d/

And it's called *.cnf (something that's not usually my.cnf). For example, 50-server.cnf.


Which of these two methods is standard when creating a 1 to many database relationship?

Alternatively I can create multiple addresses and give them all the same AddressId.

This is not a good idea at all. The AddressId supposes to be a primary key in your Address table.

Foreign Key

Use foreign Key relationship between customer and address tables, if customer may have multiple addresses associated to him/her.

Do enforce foreign key relationship between customer and its addresses by CustomerId being a primary key in Customer table and foreign key in Address table. This will ensure that no orphan records will be left, when customer record is deleted or in-activated.

One-to-many relationship diagram:

In the case if same address can be assigned to different customers, aka many-to-many between customer and addresses, do use a third table CustomerAddress (junction table). Junction table should save pair of (CusomerId, AddressId) and any other related information specific for this combination.

For example, in below diagram we have many to many relationship between Orders and products.

For more references on DB design patterns look here.

Many-to-many relationship diagram:


Why does TokuDB say to remove innodb_buffer_pool_%?

The reason is that you don't want to have heavyweight tables both for the TokuDB engine and the InnoDB engine.

These two engines both need their resources. They are both very smart about managing resources inside their own scope, and yet they are completely ignorant about one another.

So while InnoDB can coordinate its I/O between multiple query executions, it is unaware that TokuDB may at the same time use the disk in some way.

In particular, the  "After converting your tables to TokuDB, you should comment out the global options related to memory caches (ie: innodb_buffer_pool%)." relates to the convention that you provide InnoDB with most of the OS available memory. In such case little to no memory remains for TokuDB to operate with.

A situation where you would have large tables in both engines is undesired and would very probably lead to poor performance for both engines. It's just fine to leave some small, non-frequently accessed InnoDB tables with a small allocation of memory.


SQL Server 2017 - Extracting source and target column names within a string containing column mappings

You did not specify your RDBMS - my solution uses SQL Server.

Your question states that you have a formatted string stored in text

col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;...

You further state

  I'm trying to come up with an elegant way of extracting and isolating
  all the xxx_source column names and all the xxx_target column names so
  I could store them in variables and get the following end result:

  @Source_Columns = 'col1_source,col2_source,col3_source'
  @Target_Columns = 'col1_target,col2_target,col3_target'

My solution uses SQL Server 2016 STRING_SPLIT. If you're not up to that version, there are other methods to split the string.

drop table if exists #temp
Declare @String varchar(100) = 'col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;'
----------------------------
Declare @SourceColumns varchar(max)
Declare @TargetColumns varchar(max)

--Split on semicolon and use PARSENAME to extract the source and target column information
SELECT @SourceColumns = isnull(@SourceColumns + ',', '') + SourceColumn
    ,@TargetColumns = isnull(@TargetColumns + ',', '') + TargetColumn
FROM (
    SELECT parsename(replace(value, '|', '.'), 2) AS SourceColumn
        ,parsename(replace(value, '|', '.'), 1) AS TargetColumn
    FROM (
        SELECT *
        FROM String_split(@string, ';')
        ) a
    WHERE value <> ' '
    ) a

SELECT @SourceColumns

col1_source,col2_source,col3_source

SELECT @TargetColumns

col1_target,col2_target,col3_target


Amazon RDS Promote Db Read Replica to RDS Db Instance

When I promote a read replica to a db instance, does it replace the main db instance from which it was a read replica or does it create an entirely new Db Instance?

It does neither of those things.¹

Promoting an RDS replica converts the replica, in place, into a new and independent master.  The action leaves both the master and the replica instances running, exactly as they were, except that the replication connection between the two servers is permanently severed, and the former replica becomes an independent, writable master.

It cannot be rejoined to its former master as an RDS replica again after promotion, and it contains a copy of the data as it existed when it was promoted.

¹ This isn't true for Aurora, but the phrasing of the question implies that you aren't using Aurora.


Should I move to InnoDB during a planned migration?

To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable.

MyISAM is probably easier to manage from a DBA standpoint with recovery and repair.

And in 5.1, I think InnoDB's benchmarks aren't really up to par. I could be wrong on this, so I'm going to look it up again!

Edit: Quick google turned up this article in 2009 using 5.1 http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/

Gives some nice talking points, specifically the section on InnoDB needing tuning.

HTH


Is it possible to develop a SSIS with a SQL Server Evaluation Version

For SQL Server 2005 and 2008 R2, the only way to get the Integration Services development environment, Business Intelligence Design Studio a.k.a. BIDS, was through the installation media aka you already bought Developer, Standard or Enterprise Edition. However, if you're looking to purchase SQL Server for the first time, it'd be insane to spend that level of money on an older version that is due to be retired in less than four years

2012 allowed you to install the design studio, rebranded as SQL Server Data Tools-BI edition or simply SSDT-BI, either from the installation media or you could download it directly from MS for free. See also

2014 and presumably 2016, the only way to get the SSIS development tools is to download directly. Again, no payment required, nothing. Just download and develop awesome business intelligence items.

But you're only licensed for development. You can't do things that are supporting production as that's part of the click through license agreement no one reads.

That should get SSIS set up and running via Visual Studio/SSDT/BIDS for nothing in your environment. There's no need to install an actual database, beyond wanting to evaluate it. Honestly, for that purpose, install SQL Express. It's free, handles up to 10GB as of current version and never expires.

My two bits on the Evaluation Edition is to never install that monstrosity unless you are planning on paving over the operating system at a later date. At a client, they have a licensed 2008 install and some one had the bright idea to install 2008 R2 evaluation edition on the same physical box. Due to the shared nature of 2008 and 2008 R2 libraries, the only thing that works on that box is the database engine itself. Attempt to access any of the tooling results in an expired license error message. The uninstaller can't uninstall 2008 R2 components because they're also shared by 2008... While a bit of rant, be cautious with the expiry nature of the evaluation edition. For SSIS, it will be overkill.


How to use OR in IIF statement in row visibility?

=IIF(Fields!txtCurrentSubjectReportName.Value="",
     Fields!txtSubjectArchivedName.Value,
     Fields!txtCurrentSubjectReportName.Value) = "School Life"
 OR
 IIF(Fields!txtCurrentSubjectReportName.Value="",
     Fields!txtSubjectArchivedName.Value,
     Fields!txtCurrentSubjectReportName.Value) = "My goal for this term..."
 OR
 IIF(Fields!txtCurrentSubjectReportName.Value="",
     Fields!txtSubjectArchivedName.Value,
     Fields!txtCurrentSubjectReportName.Value) = "Student Reflective Comment"

The result is boolean itself and does not need in additional IIF().


mysql innodb_buffer_pool_size let me understand

If your mysql is on dedicated server then you can set the buffer pool to the value 4GB. Initially the queries will run little slow because they require disk seeks. As your data set is very small so you can keep most of the data in memory. Once the data is present in memory performance will increase.

As your server is having huge number of reads and less number of writes good buffer pool will give better results. You can allocate around 70% memory to mysql in dedicated setup.

In crease you Key_buffer_size if you have any myisam tables. Increase the value for the below variable to avoid disk based temporary tables.

max_heap_table_size = 64M
tmp_table_size = 64M

Dont change any on session variables they will consume more memory and may decrease the performance. Below are the session level variables for which defaults works better to most work loads.

sort_buffer_size
join_buffer_size
read_buffer_size
read_rnd_buffer_size

If you data not change frequently then enable query cache.

query_cache_size = 256M
query_cache_type = ON
query_cache_wlock_invalidate = ON ( to get accurate data )
query_cache_limit = 10M

Increasing innodb_log_buffer_size and innodb_log_file_size may help to gain the performance. But be careful if you increase the file size, it will increase the recovery time.

  Last but not lease if you are observing performance hit with 4GB
  buffer pool try setting innodb_buffer_pool_instances=2

give it a try!

Mysql has to fetch the data from the disk and keep it in memory for the first time you have accessed the data. As buffer pool contains both index and data inside it, your query performance will increase.

You can set the log file size to the best value as described HERE.

The best further description is given in rollando.


getting error incorrect syntax near @certificate

I don't know if you can pass a paramater in this situation. Please try to create your query in a variable, like:

declare @query as nvarchar(255);
set @query = 'drop signature from Report_Manager by certificate ' + @certificate;

and then execute the query like:

EXECUTE sp_executesql @query


Need a function to run different queries based on parameters sent subquery must return one column error

So I finally got this to work with this function:

returns table(  ctype int, cvalue char )
    LANGUAGE sql
     STABLE PARALLEL SAFE
AS $$

   select c.cttype,
     case
       when const_scopefk = 1 then --local
       ( select scu.constant_value
        from s0constants_user scu

        where (scu.constant_name = c.constant_name
           and scu.empfkey = empid
           and scu.locality_name = locality)
       limit 1)

       when const_scopefk  = 2 then --user
        (select scu.constant_value
        from s0constants_user scu

        where (scu.constant_name = c.constant_name
           and scu.empfkey = empid
           and scu.locality_name = '1')
           limit 1)

      when const_scopefk  = 1 then --global
        ( select scu.constant_value
        from s0constants_user scu

        where (scu.constant_name = c.constant_name
         and scu.empfkey = 0
        and scu.locality_name = '1')
        limit 1 )
    end
   from public.s0constants c
   where  c.constant_name = UPPER(stname);

$$;````


Delete one in two rows in an ordered result set

The easiest way is to join the table with one combined with sequential numbers:

DELETE w.*
  FROM table AS w
  NATURAL JOIN ( SELECT z.*
                      , IF(@cnt <> 0, @cnt:=0, @cnt:=1) AS cnt
                   FROM table AS z
                  ORDER BY z.date ASC
               ) AS q
 WHERE MOD(q.cnt, 2) = 1

Also the same can be done by windowed function ROW_NUMBER() if mysql is  version 8+.


How to implement High Availability for all nodes in a sharded database?

There's no difference in HA solutions when you break up a large database into multiple smaller ones.  Either Availability Groups or Failover Cluster Instances will work.
AGs have the benefit that you can perform cross-shard queries on any instance that has readable replicas of all of the shards.  So, for instance you can have a shared database that's always present on every node, and the app can always perform cross-database queries from.  Just note that cross-database transactions (including all writes) are not supported except between databases in the same AG (not blocked though).


What you are looking for is not really possible, at least not in the spirit of what you are ultimately trying to accomplish. As I explained in my answer on what is pretty much a duplicate question -- Query last time a view, funcion or sp was last used/read -- this approach doesn't take into account infrequently accessed code paths in the app code and "support" code that is executed manually, usually in SSMS. It sucks, but you really just need to do an exhaustive review of all code that could be executing Stored Procedures, as well as support tickets that include instructions on what to run in case of X, or even internal wiki pages that might have instructions indicating to execute Stored Procedures (or use UDFs, etc).

Also, you can create a table to log usage, and for those Stored Procedures that you believe are "unused", add a line at the top to insert into this log table. Check that log table periodically, and set a cut-off time with your team / department where everyone feels comfortable getting rid of that code if it hasn't been accessed within that amount of time.

P.S. I have gone through this process before. And I have had support people call me (or my manager) shortly after a release because I had dropped some code that was not referenced in any other code or internal wiki page, etc (and believe you me, I was quite thorough in my research!). BUT, it was being used in a Microsoft Access app that a support engineer created years prior to make certain support tasks / requests take less time (i.e. not needing to wait 2 weeks for a DBA to get around to running the Stored Proc so that a customer could do what they needed to do). And since none of us database engineers was aware of this app even existing (it was only on a few desktops in the support area, and certainly not in SVN), it made it a bit difficult to take into account.

P.P.S. regarding this statement:

  I'm aware that this will only show procedures which have been used post restart.

That is the most that can be shown. But, a restart is not the only way to clear cached system data. Either of the following commands will also clear that data:

DBCC FREEPROCCACHE;

DBCC FREESYSTEMCACHE('ALL');

AND, SQL Server will periodically dump the cached data when there is memory pressure (i.e. low available physical memory).

Hence, you cannot use the output of any of the DMVs / DMFs for this purpose.

P.P.P.S. The sys.dm_exec_query_stats DMV works at the server / instance level, not the database level. Meaning, not only do you not need to wrap such queries in sp_MSforeachdb, but you don't want to do that anyway since the same result set will get repeated per each of your databases.

P.P.P.P.S. The sys.dm_exec_sql_text DMF that you are already using conveniently returns columns for objectid and dbid. The dbid column can be used along with the DB_NAME() built-in function that you are already using to get the "current" database name for that query. And, both the dbid and objectid columns can be used along with the OBJECT_NAME() built-in function to get the name of the stored procedure, relative to that "current" database (as opposed to the database that you are running this query in, which can return erroneous values since the objects are potentially not in that database). And then both of those functions can be used again in the ORDER BY clause.

Taking all of that info into account, the re-written query is:

SELECT  DB_NAME(qt.[dbid]) AS [DatabaseName],
        OBJECT_NAME(qt.[objectid], qt.[dbid]) AS [SP Name],
        qs.execution_count AS [Execution Count],
        qs.max_logical_reads,
        qs.max_logical_writes,
        qs.creation_time,
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
ORDER BY DB_NAME(qt.[dbid]) DESC,
         OBJECT_NAME(qt.[objectid], qt.[dbid]) DESC;


How should I interpret the "lock time" within mysql slow query log?

The lock_time in the slow query log is actually the amount of time the query spent waiting to acquire the lock it needs to run. For example, UPDATES queries need a write lock.

The locking also depends on the storage engine you are using in the table. When writing, InnoDB will use row-level locking and only lock the rows that are being changed. MyISAM will lock the entire table until the update/insert/delete is complete.

The Locking of the entire table for MyISAM is a big reason SELECT queries will have a lock_time in your slow query log.


insert into all possible combination of arrays of strings

You just need to do a cartesian product:

INSERT INTO mytable("First Name", "Last Name", "Email")
    SELECT * FROM (
        SELECT 'a1' AS firstname
        UNION SELECT 'a2'
        UNION SELECT 'a3'
        UNION SELECT 'a4'
    ) AS t1, (
        SELECT 'b1' AS lastname
        UNION SELECT 'b2'
        UNION SELECT 'b3'
        UNION SELECT 'b4'
    ) AS t2, (
        SELECT 'e1' AS email
        UNION SELECT 'e2'
        UNION SELECT 'e3'
        UNION SELECT 'e4'
    ) AS t3
;

(for mysql, replace " by ` or set sql_mode to include ANSI_QUOTES)

Alternate shorter syntax for PostgreSQL:

INSERT INTO table("First Name", "Last Name", "Email")
    SELECT * FROM (
        VALUES('a1'), ('a2'), ('a3'), ('a4')
    ) AS t1(firstname), (
        VALUES('b1'), ('b2'), ('b3'), ('b4')
    ) AS t2(lastname), (
        VALUES('e1'), ('e2'), ('e3'), ('e4')
    ) AS t3(email)
;

For PostgreSQL and from arrays:

INSERT INTO table("First Name", "Last Name", "Email")
    SELECT * FROM (
        SELECT unnest(ARRAY['a1', 'a2', 'a3', 'a4'])
    ) AS t1(firstname), (
        SELECT unnest(ARRAY['b1', 'b2', 'b3', 'b4'])
    ) AS t2(lastname), (
        SELECT unnest(ARRAY['e1', 'e2', 'e3', 'e4'])
    ) AS t3(email)
;


Can RMAN duplicate be used to "replace" an existing database?

The changes required to setup a duplicate from A (target) to B (auxiliary) in RMAN are pretty trivial.

1) db_file_name_convert AND log_file_name_convert parameters in the auxiliary database. If the file paths are exactly the same, simple name replacement should suffice:

db_file_name_convert='PROD','TEST'
log_file_name_convert='PROD','TEST'

IF your path changes are more complicated, you can specify full path replacement:

db_file_name_convert='/u01/oradata/PROD/system_PROD_01.dbf','/export/home/oracle/oradata/TEST/system_01.dbf','/u01/oradata/PROD/sysaux_PROD_01.dbf','/export/home/oracle/oradata/TEST/sysaux_01.dbf'
log_file_name_convert='/u02/oradata/PROD/redo_PROD_01.log','/export/home/oracle/oradata/TEST/redo_01.log','/u02/oradata/PROD/redo_PROD_02.log','/export/home/oracle/oradata/TEST/redo_02.log'

2) Make sure to setup an oracle password file for the instance and configured for remote login.

3) Make sure the target and auxiliary instances are configured in TNSNAMES.ORA on both instance servers (if they are different).

4) Shutdown B (auxiliary), remove all control files, redo logs and data files.

5) Startup B in NOMOUNT mode.

IF YOU ARE RUNNING 11G

6) Connect to rman like so:

rman target sys/change_on_install@A auxiliary sys/change_on_install@B

duplicate target database to "B" from active database;

Oracle will handle backup mode, compression, transport and unpack of the A database to replace the B database. When this completes, your database will be done.

IF YOU ARE RUNNING 10G

6) Configure flash recovery area in the target instance:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/oraflash' SCOPE=BOTH;

7) Backup database using rman. If your A (target) is in archive log mode, this can be done hot. If not, cold. Compressed below is optional. I always use it. The backupset ends up around 10-20% of the original instance size.

rman target sys/change_on_install@A

backup database as compressed backupset;

8) Using the location you chose above for the recovery area, copy the database to the server auxiliary is on (this is better if you have clustered/shared storage between the two servers). The date of course is the date you run the rman backup. Make sure the files end up in the same place. If there isn't room, you have options, but the easiest way on Unix/Linux is a symbolic link, or on Windows (NTFS only) a junction.

/u01/oraflash/PROD/backupset/2012_06_29

9) Using rman, duplicate the target database:

rman target sys/change_on_instance@A auxiliary sys/change_on_install@B

duplicate target database to "B";

AND, you are up.

IF you really want to do a new clone with "less" effort, then you can setup a very shell database (an init.ora file with only 1 parameter), and use the SPFILE parameter in the rman duplicate to copy the parameter file over to the new instance, converting every name based parameter on the way. The can be good, except when the test machine may have more than one instance, and memory tuning may need tweaking.


Steve is discussing how to copy a user and that user's grants from one database to another.  That wouldn't copy the objects the user owns or the data in those objects.

Why don't you want to use the export and import utilities?  Whatever restrictions you have that prevent the use of export and import might prevent you from using other alternatives.  You could potentially copy the data over a database link, for example, but that wouldn't generally be preferred to just using export and import.  If you want to do this on an ongoing basis, Oracle provides various tools for replicating data-- Streams, materialized views, etc.


How to script a table with dynamic data masking via SSMS

You can use Powershell and SMO.

Create a new folder ('Scripts') on your C-Drive for use in the following example

In SSMS, right-click on the 'Tables' node and select 'Start Powershell'.  Paste the following code into the Powershell window

Remove-Item C:\scripts\z.sql
$scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
#$scriptingOptions.ScriptDrops = $true
$scriptingOptions.NoCollation=$True;
$scriptingOptions.ScriptBatchTerminator = $true
$scriptingOptions.NoCommandTerminator=$false
$ScriptingOptions.FileName = "C:\scripts\z.sql"
$ScriptingOptions.ToFileOnly=$true
$ScriptingOptions.AppendToFile=$true
dir | Where-Object {$_.name -like "*test*"} | foreach {$_.Script($scriptingOptions)}

Check for the z.sql file in the C:\Scripts folder

If it's any consolation - I too cannot get SSMS to natively script the masking details and I'm on the latest release.


Is there a quantitative method to follow to determine FILLFACTOR for NONCLUSTERED INDEXes?

No generalised quantitative method exists to be followed.

One would'e come up by now (1+ years since question), nor have i come across one on the internet searches.


What is a good guideline for sampling for statistics updates

If you always query the tail, you can give a shot to filtered indexes. Index 10 ways with, say,  WHERE datetimecolumn > @INDEXBUILDDAY-30. Periodically (daily, weekly?) you can move this filter forward by adding (online) a new set of filterd indexes and then dropping the old ones. With a much smaller size and a much more recent build (implying recent stats), they would be 'fresh' and very appetizing for the optimizer. The periodic rebuild doesn't even need to scan the base table (no huge IO, no BP cache pollution), the old filtered index should always be usable as a source to build the next filtered index (it covers it).

For those queries that land outside the covered filtered tail, you can keep some (less than the 10 ways hopefully) full, unfiltered indexes. With the right voodoo invocation the optimizer might get your point and use the filtered ones when appropriate and only resort to the full ones when necessary...


Must data be evenly distributed among Mongo shards?

So even if you have 1M users, only 10 articles must be kept? I have a strong feeling that your data model has this or that flaw.

Does sharding a collection with 10 documents (give and take) make sense?

If you only need 10 documents in a collection, you don't need to shard it, since it is not going to be balanced anyway, unless the documents are exceeding around 4.8Mb in size. This size computes like this:

maxChunkSize/number of your documents is the size pre document when you chunk gets split latest, equalling 6.4Mb per document
In order to cross the migration threshold, which is only 2 for collections with less than 20 documents, we need to have your chunk split at least once to trigger the migration
Since the chunk split may be already triggered when the chunk reaches half of it's max size resulting in a split when all documents are slightly over 3.2Mb, we add the median of the difference between half of the chunks max size and and it's max size (silently assuming that a chunk is guaranteed to be split at it's max size)

But sharding this collection would not make sense the first place, as , assuming the 10 documents are a hard limit, the collection's max size can only be 160Mb as per MongoDB's BSON size limit

Do I need to have my data balanced?

Let us find out wether it is a good idea to have a bad shard key and a disabled balancer. First, if you disable the cluster balancer, this affects all sharded collections. Let us take the user collection as an example:

We have 1M users
Their user id is stored in _id, starts with 1 and each new user's _id is a simple increment
We have two shards
We disable the balancer.

Now, you shard the collection from the start. What happens internally is that there are two chunks created. In the first chunk, created on the first shard, the _id from -∞ to _id < 0 are stored. In the second chunk, created on the second shard, the _ids from 0 to +∞ are stored. Now here comes the thing: since our _id increments from 1 for each user, there is never a single user stored in the first chunk and subsequently not on the first shard. No disk space of the first chunks utilized and – with more immediate importance – no RAM, too. Since indices are (tried to be) kept in RAM along with the recently used data (called working set) amongst other things to speed operations up, sooner or later we will have the situation that the RAM on the first shard is rather empty, while the second shard will start to evict data set items or even indices out of RAM. Bad idea, huh? Now things get worse: Since we have disabled the balancer, the cluster can do nothing to mitigate the situation.

Now let's assume we were slightly smarter and have pre-split our chunks so that the _id for our users collection are distributed in a way where the _ids ranging from -∞ to 500,000 are stored on the first shard and the rest on the second. It is obvious that this is only a temporary solution, since when we exceed 1M users, the whole problem starts again. And without the balancer running, the cluster still can not mitigate this situation.

Taking this a step further: We have found out that we can use a hash sum of our _id as our shard key. Jay! Problem solved! Except it isn't.
In theory, the hash algorithm should cause our users to be evenly distributed among our shards. But there is a little thing called variance.

It can be easily demonstrated like this (heavily simplified for the sake of shortness): When tossing a coin 20 times, in theory you should get an equal number of heads and tails, since the probability of either of them is 50% right? Try it. Now. The odds are very low that you will have an equal amount of heads and tails.

How does this translate to our problem? Well, chances are high that either the first or the second shard get's slightly more documents than the other over time. And this sooner or later will add up to a point where it becomes a problem – RAM and disk space is underutilized on one shard and over utilized on the other. Again. And again, the balancer can not help us to mitigate the problem.

So this should have made it crytsal clear that you should have your data balanced and that you should never have the balancer disabled by default (there are some administrative tasks during which you should or must have the balancer disabled).

Conclusion

For some 10 documents, no matter how big they are, you don't even need to shard the collection.

Disabling the balancer might cause severe problems with your cluster. Unless you absolutely have to or you are absolutely positively sure that you can live with the consequences, do not disable it.

Please note that I left out some more complicated topics, like hard drive IO bandwidth bottlenecks, network bandwidth distribution and alike for the sake of readability and – as funny as this might sound – shortness.


MySQL Using Group By, Need to add fields where answer is reliant on another fields data

Your question is not clear enough, but the following query would provide you with the results you specify as "AFTER :)"

SELECT  inventory.`CODE` AS `Item No.`,
        inventory.INV_DESCRIPTION AS Description,
        SUM(CASE WHEN inventory.WHSE = '00' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (LA)`,
        SUM(CASE WHEN inventory.WHSE = '10' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (SF)`,
        SUM(CASE WHEN inventory.WHSE = '20' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (HP)`,
        SUM(CASE WHEN inventory.WHSE = '50' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (CHINA)`,
        SUM(CASE WHEN inventory.WHSE = '70' THEN inventory.ONHAND ELSE 0 END) AS `On Hand (INDO)`
FROM inventory
WHERE inventory.WHSE IN ('00', '10', '20', '50', '70') AND
      inventory.PROD IN ('A', 'A-70', 'B', 'C', 'S', 'KIT', 'SET')
GROUP BY inventory.`CODE`, inventory.INV_DESCRIPTION
ORDER BY 1


SQL database backups methods-tools-procedures

The first thing I generally recommend doing is introducing differential backups to the backup strategy. If the amount of data churn is moderate or low, this can work extremely well for saving space and time because you only back up changes rather than everything. This does add some complexity to restore scenarios, and you must make sure you know what COPY_ONLY backups are.

The second thing is leveraging native backup compression. SQL Server 2008 Enterprise and SQL Server 2008 R2 Standard editions support backup compression out of the box -- no need for 3rd-party tools. If you aren't taking advantage of this, you're really missing out. It is off by default for backwards compatibility. You can turn it on server-wide by default in the Server Properties | Database Settings page, or per-backup using the WITH COMPRESSION option. This will still have a small CPU hit, but the cost will be amortized over the duration of the backup instead of all at once (assuming backups are I/O bound, which they usually are).

Some other random thoughts that may or may not apply to your environment: stagger the backups to reduce I/O contention; back up to multiple locations simultaneously; back up to LUN(s) dedicated to backups; tweak the backup buffer settings.

For the 2005 backups, what you can consider doing is limiting the amount of CPU used by the compression utility by using Windows Server Resource Manager. Or, since the file has to be written somewhere before that step happens anyways, you could offload that process to another box.

Encrypting backups is not something I'm too familiar with, but it will depend on how you plan to encrypt the data and backups. If you use Transparent Data Encryption in SQL Server, you'll likely want to turn off backup compression because there won't be anything to gain because of the randomness of the encryption. If you use a 3rd-party encryption strategy, see the previous point about using WSRM or offloading the process to a different box, because encryption will also require CPU.


retrun multiple functions from dynamic query

It looks like indzone is a varchar(20), not a text - you could either specify the returned record using the former, or cast indzone inside the function.  (This applies to the solution suggested below, too.)

But:

As it looks like, you don't need any dynamic SQL in your function, as you never use any object (table, column, etc.) name specified on the fly.  Your query could look like

SELECT indzone, dstr, nmindar, plno, pltar, nmalt
  FROM plotboundary
 WHERE indzone = ind_zone AND [...]
   AND implst = COALESCE(_implst, implst) -- changed the `IN` parameter name to avoid confusion
   AND rsvr = COALESCE(_rsvr, rsvr)
   AND [...]

In certain (I'd say rare) cases, this can be less efficient than you version, but that you can find out easily on your data.  Most probably a well chosen index on the 'fix' columns (that are always compared to the incoming parameters) saves you worrying about it.


Why is my MERGE query deadlocking while in a serialized isolation level transaction?

If you want to ensure that only one process is allowed to run this MERGE query (stored procedure) at any given time, then
sp_getapplock
is a good fit. It is very straight-forward, easy to understand and maintain, as opposed to obscure query hints. I'm not saying that it is impossible to achieve the same effect with hints. It is easier for me to understand the simple mutex.

This is a template of a stored procedure that I use. Adjust the timeout as needed. The caller should be aware of possible timeouts and retry if needed.

CREATE PROCEDURE MergeIt
    @dataToMerge MyTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION
    BEGIN TRY

        DECLARE @VarLockResult int;
        EXEC @VarLockResult = sp_getapplock
            @Resource = 'MergeIt_app_lock',
            @LockMode = 'Exclusive',
            @LockOwner = 'Transaction',
            @LockTimeout = 60000,
            @DbPrincipal = 'public';

        IF @VarLockResult >= 0
        BEGIN
            -- Acquired the lock
            MERGE INTO TargetTable WITH(HOLDLOCK) AS [target]
            USING @dataToMerge AS [source]
            ON [source].KeyPart_1 = [target].KeyPart_1 AND
               [source].KeyPart_2 = [target].KeyPart_2
            WHEN NOT MATCHED THEN
                INSERT(Data, KeyPart_1, KeyPart_2)
                VALUES([source].Data, [source].KeyPart_1, [source].KeyPart_2)
            WHEN MATCHED THEN
                UPDATE SET [target].Data = [source].Data
            ;

        END ELSE BEGIN
            -- timeout waiting for the lock
            -- TODO: handle the problem, e.g. return some error code,
            -- indicating that the caller should retry.
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- TODO: handle the problem. Return some error code?
    END CATCH;

    RETURN <the error code>
END


mysql trigger to trigger only if record doesn't exist

Change the inserts to

INSERT IGNORE ...

That should avoid the problem as I read it.  If I misread, please clarify.


Creating bacpac failed (DoAllowRowLocks / DoAllowPageLocks)

I have fixed the problems using these script:

[Rebuild all indexes]

USE AdventureWorks;
declare @table_name varchar(200)
declare @SQL varchar(300)

DECLARE vendor_cursor cursor
        FOR SELECT sys.tables.name
        FROM sys.tables WHERE sys.tables.name NOT LIKE '%_dss'
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @SQL =  'ALTER INDEX ALL ON '+@table_name+' REBUILD';
 EXEC(@SQL);
 print 'INDEX Rebuild on all indexes in table '+@table_name+' is done.';
 FETCH NEXT FROM vendor_cursor  INTO @table_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
GO

I also needed to change the IsNotForReplication property.
This script turns it YES for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

This script turns it NO for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = ‘
declare @int int
set @int =object_id(“?”)
EXEC sys.sp_identitycolumnforreplication @int, 0′


It turns out that the problem was cause by a bad full text search index. It had to be rebuild with do not track changes, and the correct word breaker for the given language.


The first error is on Line 5, telling you that you cannot use Functions in your parameter's default values.

Pass in NULL as the default value, then sort the parameters out inside the Stored Procedure:

CREATE PROCEDURE dbo.Test
(
  @StartDate As datetime = NULL
  ,@EndDate As datetime = NULL
)
AS

BEGIN

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -8, CAST(GETDATE() AS DATE))
IF @StartDate IS NULL
  SET @StartDate = DATEADD(DAY, DATEDIFF(DAY, 0, getdate()-8), 0);

-- If you're using SQL Server 2008 or greater use DATEADD(DAY, -1, @StartDate)
IF @EndDate IS NULL
  SET @EndDate = DATEADD(DAY, DATEDIFF(DAY, 0, @startdate -1), 0);

END

Once you have fixed this issue, and capitalise the @startDate parameter to read @StartDate, you should be fine.

For reference, there's an open Connect Item (currently 7 years old) that is Microsoft acknowledging it's on their 'ToDo' list.


How to store user selected value, single choice or all choices

The "All options" should be part of the option list with an ID (-1 is OK to be recorded in the options master table).
I wouldn't include a NULL value, because it is commonly used to note lack of data.

Your second option is totally worthless from my point of view: you can easily calculate the 2nd field using the data in the 3rd field.


Replicate data of some tables of a local sql server to a database in Azure

My answer is assuming "we need some of these tables replicated on to a database on Azure" you are referring to Azure SQL Database.

First of all you need to make sure that you requirement can tolerate the generic restrictions with Azure SQL DB.

Azure SQL Database resource limits

I did deploy these 2 methods with similar requirement you have.  Again you need to remember some of the on prem feature will not be supported.

Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

  General limitations

  A table cannot have an identity column that is not the primary key.
  A primary key cannot have the datetime data type.
  The names of objects (databases, tables, and columns) cannot contain the printable characters period (.), left square bracket ([),
  or right square bracket (]).

  Azure Active Directory authentication is not supported.

  Unsupported data types FileStream
  SQL/CLR UDT
  XMLSchemaCollection (XML supported)
  Cursor, Timestamp, Hierarchyid

Transactional Replication.

  You can also use transactional replication to migrate a subset of your
  source database. The publication that you replicate to Azure SQL
  Database can be limited to a subset of the tables in the database
  being replicated. For each table being replicated, you can limit the
  data to a subset of the rows and/or a subset of the columns.


MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains non aggregated column fixed bug reappear every restart

That's not a bug. That's 'move to standard'.

Do not fix this problem by change server's SQL Mode. Fix it by altering the queries which uses partial GROUP BY expressions.

Details can be read in MySQL Handling of GROUP BY.


CLR Procedure with XML parameter returns Data at the root level is invalid. Line 1, position 1

The problem is that you made a bad assumption about what value is returned by the SqlXml.ToString() method, and then in testing in Visual Studio, you injected a value but did not check to see what ToString() was actually returning (which is: "System.Data.SqlTypes.SqlXml"). Though you probably would have seen this had you placed the SqlContext.Pipe.Send(inputXml); line above the xmlDoc.LoadXml(inputXml); line ;-).

When working with the Sql* types, you want to almost always access them via the Value property, which they all have. The Value property does return the string version of the XML as you were wanting to get. However, this is less than ideal since you will be converted the XML to a string, only to convert that back to XML again when you call XmlDocument.LoadXml().

A better way is to use the SqlXml.CreateReader() method which returns an XmlReader, and that can be used with the XmlDocument.Load() method.

I reworked your test code (below) to be an example showing all 3 of these variations:

using System.Data.SqlTypes;
using System.Xml;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static int spGetTaxOfficeXML(SqlXml InputXml)
    {
        // this procedure rename Row elements name with NodeName attribute value
        XmlDocument _XmlDoc = new XmlDocument();
        _XmlDoc.Load(InputXml.CreateReader());

        // procedure Logic

        SqlContext.Pipe.Send("Incorrect:\n" + InputXml.ToString());

        SqlContext.Pipe.Send("\nBetter, but not an XmlDocument:\n" + InputXml.Value);

        SqlContext.Pipe.Send("\nCorrect:\n" + _XmlDoc.OuterXml);

        return 0;
    }
}

Running the code above with the original test SQL returns the following in the "Messages" tab:

Incorrect:
System.Data.SqlTypes.SqlXml

Better, but not an XmlDocument:
<NodeA><NodeB /><NodeC AttributeX=""><Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" /><Row NodeName="RowB" AttributeA="wxyz" /><Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" /><Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" /></NodeC></NodeA>

Correct:
<NodeA><NodeB /><NodeC AttributeX=""><Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" /><Row NodeName="RowB" AttributeA="wxyz" /><Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" /><Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" /></NodeC></NodeA>

P.S.  You don't need to worry about UTF-8, especially for NCHAR / NVARCHAR / XML data generated from within SQL Server, since SQL Server is UTF-16 LE.


Replace column by LEFT OUTER join to save space

This approach is basically trading storage for compute.  While you are reducing the amount of space on disk, you are doing so at the cost of the additional join; more specifically the compute power to make that join happen.  Also, the space savings is purely dependent upon the size of the datatype of the column in question as well as the key for which you'll be using to join the tables.  The more complex your key, the more compute required and the less space saved.

In the best case scenario, using your parameters (e.g. 100M record table where column A is an INT), we'll assume the Primary Key is also a simple INT, we get a new table that is about 10% the size of the data footprint if compared to keeping this as a column on the main table:

Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
New Table T2 consumes ~38.1MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 4 Bytes for the key on which we'll join)
Space savings of 90%

However, what if our key is a composite key that is a combination of an INT and DATETIME columns (4 byte and 8 byte sizes, respectively), we see the new table has doubled in size to about about 20% the size of the data footprint if compared to keeping this as a column on the main table:

Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
New Table T2 consumes ~76.3MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 12 Bytes for the key on which we'll join)
Space savings of 80%

Still not bad you say, but would you just leave it at the one column or would you extend this pattern to other fields?  Because we're talking design paradigm, I'd expect to see this extended to more tables/fields/etc.

So the pro is a savings in space, but what are the cons?

Obviously the excessive amount of joins you now have to manage within your SQL statements as well as the compute that will be required, but deletes now also require extra statements (since we now have multiple tables to deal with and don't forget the extra compute this requires as well).  Let's not forget what could happen if that 95% data distribution changed, as data tends to do over time.

If you've got excessive compute available and are limited on disk, this approach may be viable, but more often than not, compute is much pricier than storage, especially after factoring in licensing as most products are licensed based off of core count and not size.  MySQL Community Edition is free but the other editions are not, and what about the CPUs on your server (or cloud service provider)?  Often storage is a fraction of the cost of CPU, so unless you're limited on the storage side the cost/benefit ratio doesn't work well after you factor in the additional man-hours to write the code and the potential cost to handle the extra compute needed for the additional operations.

So, to answer your questions:

  Is this a common approach?

No, not in my experience.

  Is there a name for this pattern?

This is database normalization in it's most extreme.  Most RDBMS systems are designed with 3NF/BCNF in mind, so likely that level of normalization is where your system is most cost effective.

  Are there any downsides except that I have to handle two tables now in my application code?

Yes, if this design is utilized whenever possible, at minimum, the cost of the extra compute needed to handle the join and the extra man hours required to deal with the extra code logic (e.g. extra joins, additional deletes, etc.) will be a downside.

That being said, it may make perfect sense to do this for one or two columns within a database where the disk consumption is excessive.  The design of your database, the type of data it stores, and the system it's supporting all play into when this makes sense or not.  However in those cases, I'd say you should look into table or page compression first as data has a funny way of changing such that it finds ways to break even the most well of intentioned design patterns.

UPDATE: A quick edit to further explain my answer about Normalization as people seem to be passionate this isn't accurate.  At the root of things, this approach is creating a separate table with a one-to-one relationship and overloading the meaning of NULL to mean whatever our most common value is, which would be 0 in our example scenario.  When I say overloading the meaning of NULL, we are implying that the absence of a record means said record has a default value.  Again, this design approach isn't different than any other one-to-one or one-to-many relationship between two tables, but in this case we're just implying that the absence of a record means something.  Again, this isn't a revolutionary design approach, it's just a way to overload functionality which at the end of the day is just another flavor of normalizing the data.


Granting a user a permission to restore a database

Yes, this is actually fairly easy to accomplish using Module Signing. With module signing, you create a module (in this case, a stored procedure) and grant the required permission to the module, not to user or login. You only grant the user permission to execute the module. And, by doing this, not only can you be granular enough to effectively only grant the ability to restore, by hard-coding the RESTORE statement, you are essentially granting permission to only restore this particular database.

Detailed instructions, explanation, and walk-through demo can be found here:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

But to simplify, here are the basic steps:

(we will assume that the stored procedure will be created in the [master] DB, which means we don't need to copy the certificate to another DB. the stored procedure could just as easily be created in a utility database, in which case you just need to copy the certificate to [master] as shown in the instructions in the linked blog post)

Create a stored procedure. This can be as simple as just the RESTORE statement for a specific DB, or it can accept an input paramater for @DBName sysname and construct Dynamic SQL using that parameter value, this allowing for restore of any particular DB, or maybe a @DbToRestore TINYINT parameter that is used in a CASE statement allowing the user to select from a limited set of pre-defined database names to restore.
Create a certificate specifying a password (do not rely on the Database Master Key / DMK). Do not give the end-user / customers this password (including a script containing the password).
Sign the stored procedure (i.e. the "module") using that certificate and its password.
Create a login (not user) from the certificate.
Add the login to the dbcreator fixed server role.
If the end-user / customer does not already have a user in [master] for their login: create a user in [master] for the customer's login.
Grant the customer's user EXECUTE permission on the stored procedure.

For added security: it might be a good idea to prevent them from signing another module, or re-signing this one, if they somehow manage to get the password. This is not always necessary, but is probably a good idea here since you distribute the system to the customer.

Backup the certificate to a file
Remove the private key from the certificate

Without the private key, the certificate can only validate that a module was signed with it, but it can no longer sign anything. Since signing a module applies the permissions of the associated certificate-based login (i.e. the login that was added to dbcreator), this prevents the customer from applying those permissions to their own modules, if they ever managed to create any. ALSO, if the customer manages to alter this stored procedure to do something that you are trying to prevent, the signature is dropped, meaning that the module no longer has the elevated permissions (even if they put the stored procedure back to the original code).

Please also take a look at the following answer of mine, also here on DBA.StackExchange, to a similar question:

Execute Permissions for a Store Procedure that creates databases


How to safely eject a tablespace oracle and move it to another machine

If there are no connections to it, is it safe to eject the disk and plug it into a new machine?

No.  Absolutely not.

Database != File

Unless you shutdown the entire database, there is no guarantee that changes have been written from the Buffer Cache into the data files that make up that Tablespace.

Making a Tablespace transportable does forces that write activity to happen (and then prevents any more) but without the downtime.  But then, as you say, you have to muck about "mapping" that Tablespace into the new database.  Don't make work for yourself.

Your best option, though (i.e. easiest, most reliable, best supported but, perhaps, not fastest to execute), is to dump (datapump) the data out of one database and into the new one.  Depending on your network connectivity, you might be able to do this over a database link, or might have to resort to moving the dump file between machines.


Here is one case where it would be hard to write it without:

DELETE /* FROM */ t1    -- this FROM is optional
  FROM dbo.t1           -- this FROM is mandatory
  INNER JOIN dbo.t2 AS t2
  ON t1.key = t2.key
  WHERE t2.key IN (1,2,3);

Or:

DELETE /* FROM */ t1    -- this FROM is optional
  FROM dbo.t1           -- this FROM is mandatory
  WHERE EXISTS
  (
    SELECT 1 FROM dbo.t2
    WHERE key IN (1,2,3)
    AND key = t1.key
  );

The FROM in the initial spot (e.g. DELETE **FROM** t1 FROM dbo.t1 ...) is optional, just as Books Online says. Why do you think it ever should be mandatory? Borrowing from Thomas' answer, which he gracefully deleted, the following are identical:

DELETE Sales.SalesOrderDetail WHERE SalesOrderID = -1;

DELETE FROM Sales.SalesOrderDetail WHERE SalesOrderID = -1;

This is similar to INSERT - the following are absolutely 100% equivalent, and you don't ever have to write INTO:

INSERT dbo.t1(key) VALUES(1),(2),(3);

INSERT INTO dbo.t1(key) VALUES(1),(2),(3);


Howto specify my own id value when upload a stream into a gridfs file, using the GridFSBucket API?

The solution is the openUploadStreamWithId method.It has this signature:

openUploadStreamWithId(id: GridFSBucketWriteStreamId, filename: string, options?: GridFSBucketOpenUploadStreamOptions): GridFSBucketWriteStream;

The GridFSBucketWriteStreamId type is like this:

type GridFSBucketWriteStreamId = string | number | Object | ObjectID;

So you can call it like this:

openUploadStreamWithId(your_custom_file_id, your_file_name)

If the custom file id is uniformly distributed and chunks are sharded on (files_id, chunk_id) then this will prevent hot shards, and allow range queries on the chunks at the same time.


T-SQL script doesn't delete files when run as an agent job - does if run manually

The account that you use to run the job WILL need permissions to delete those files. To test your process, login as that specific user and test your job--specifically the deletion of files-- with that user.

That being said, I've had issues purging SQL Server backups the way I'd like so I created this CmdExec script do so. To delete files and get error information back, consider running this as one of your job steps (should/has to be a one liner for CmdExec to work right):

cmd /e:on /c "forfiles /p "c:\MYDATABASEBACKUPS" /s /m *.BAK /d -2 /c "cmd /c del @path" 2>&1 |  findstr /V /O /C:"ERROR: No files found with the specified search criteria."2>&1 | findstr ERROR&&EXIT 1||EXIT 0"&exit %errorlevel%

This script purges backups based on how old they are in number of days. The /d -2 tells the script to keep two days worth. Change 2 to whatever number you'd like to keep.

Also, change c:\MYDATABASEBACKUPS to your backup location. Change *.BAK to the backup extension of your backup files. The "fanciness" of this script is because I get rid of the false positive "No files found"--because sometimes there won't be files to find.

Good luck with your scripting!


SQL Server Extend dynamic SQL query with where part fetched from Stored Procedure

Typical things you can make SQL injection safe:

Values

DECLARE @UserSuppliedValue varchar(32) = 'foo';

DECLARE @sql nvarchar(max) = N'SELECT ... FROM dbo.bar
  WHERE value = @value;';
EXEC sys.sp_executesql @sql, N'@value', @UserSuppliedValue;

Entities, like table/column names

DECLARE @UserSuppliedTable  = N'dbo.bar',
        @UserSuppliedColumn = N'foo';

IF EXISTS -- make sure table exists
(
  SELECT 1 FROM sys.tables
    WHERE [object_id] = OBJECT_ID(@UserSuppliedTable)
)
AND EXISTS -- or make sure both exist
(
  SELECT 1 FROM sys.columns
    WHERE [object_id] = OBJECT_ID(@UserSuppliedTable)
      AND name = @UserSuppliedColumn
)
BEGIN
  DECLARE @sql nvarchar(max) = N'SELECT '
    + QUOTENAME(@UserSuppliedColumn) + N' FROM '
    + @UserSuppliedTable + N';';
  EXEC sys.sp_executesql @sql;
END

I listed both there for illustration but you would only need one or the other in most scenarios.

If you're trying to pass an entire WHERE clause to a dynamic SQL statement, there's little you can do to make that injection safe, except maybe try to execute it with PARSEONLY to make sure it's not garbage. That doesn't help prevent valid SQL injection commands but it does at least help prevent utter garbage.

DECLARE @where nvarchar(max) = N' WHERE junk;'

DECLARE @sql  nvarchar(max) = N'SELECT * FROM sys.objects ' + @where;
DECLARE @test nvarchar(max) = N'SET PARSEONLY ON;' + @sql;

DECLARE @hr int;
BEGIN TRY
  EXEC @hr = sys.sp_executesql @test;
  EXEC sys.sp_executesql @sql;
END TRY
BEGIN CATCH
  PRINT N'Statement was garbage.';
END CATCH;

If @where were the following...

DECLARE @where nvarchar(max) = N' WHERE 1=1; DROP TABLE dbo.bobby;';

...then this parsing trick doesn't work (since parsing just validates syntax, not object existence or permissions).

So you need to put proper logic into the stored procedure that produces @where and make sure there are appropriate controls, permissions, and auditing around that stored procedure, as well as reducing the permissions of the user/group executing the final @sql - this should not be sa, for example.

I've written a little about this topic:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2


Is there any way to find information of index fragmentation, Statistics information with the help of system tables in SQL Server

Is there anyway to find information for Index Fragmentation and statistics in SQL Server with the help of system tables?

The more appropriate way of finding fragmentation details for SQL Server is by using DMV sys.dm_db_index_physical_stats. Please note since DMV's came from SQL Server 2005 onward below script will work for 2005 onward only

select
                    o.name AS objectName,
                    i.name AS indexName,
     s.name as Schemaname
                ,p.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID (), NULL, NULL , NULL, null) AS p
                    INNER JOIN sys.objects as o
                        ON p.object_id = o.object_id
                    INNER JOIN sys.schemas as s
                        ON s.schema_id = o.schema_id
                    INNER JOIN sys.indexes i
                        ON p.object_id = i.object_id
                        AND i.index_id = p.index_id
                WHERE p.page_count > 2000 and p.avg_fragmentation_in_percent >5
                AND p.index_id > 0 and s.name <> 'sys'

For statistics you also have sys.dm_db_stats_properties IF you are using SQL Server 2008 R2 Sp2 and above you can use below script. Source

-- Script - Find Details for Statistics of Whole Database
-- (c) Pinal Dave
-- Download Script from - https://blog.sqlauthority.com/contact-me/sign-up/
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,
DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,
dsp.modification_counter,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id
FROM sys.stats s
JOIN sys.stats_columns sc
ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1)
ORDER BY DaysOld;


If it were me I would create a SQL Agent job for cycling the error log based on size or date. Run it a few times a day and you can even set up alerts to notify if the error log was cycled.

CREATE TABLE ##Temptable
(
[Archive #] tinyint,
[Date] datetime,
[Log File Size (Byte)] INT
)
insert into ##Temptable exec xp_enumerrorlogs
IF (
    SELECT [Log File Size (Byte)] from ##Temptable where [Archive #] = 0
    ) > 5242880 --5MB
    BEGIN
    Exec sp_cycle_errorlog
    END
drop table ##Temptable

You could also change the IF statement or add an OR statement to look at the date and cycle if it is older than 2 weeks or however long you want.

IF (
    SELECT [Date] from ##Temptable where [Archive #] = 1
   ) < GETDATE() -14

EDIT: Archive for the date based cycle needs to be the previous error log. Log 0 will always return current date, since it is active, duh!

As for moving them, you can keep up to 99 log files. Do you think your extended information needs will exceed 100 logs? If not, I would move the error log location to a disk that can hold 100 full logs and change the maximum number of logs.


Difference between oracle database desktop and server class

May the reason be why I installed Oracle as desktop class instead of server class?

No, 'Desktop Class' just means you only get basic choices from the installer1, according to the docs:

  Desktop Class—This installation class is most appropriate for laptop or desktop computers. It includes a starter database and
  requires minimal configuration.
  Server Class—This installation class is for servers, such as you would find in a data center, or used to support enterprise-level
  applications. Choose this installation class if you need access to
  advanced configuration options.

  During a Desktop Class installation, you make only basic choices. For
  a Server Class installation, you choose either typical installation
  (where you make only basic choices) or advanced installation.

  During a Desktop Class or a typical installation, Oracle Database
  automatically installs the sample schemas.

You'll need to go through the kind of troubleshooting steps suggested in the comments to find out what is causing the ORA-12545 error:

  An ORA-12545 error occurs when the client is not able to contact the
  listener on the node specified. For example: when the listener has not
  been started on the node, if the node address has been specified
  incorrectly, or a firewall blocks access.

1 So 'Desktop Class' and 'Server Class' are poor names for the installation types. 'Basic' and 'Custom' might be better?


Relational Model Design - Entity with one attribute

Yes, but.

If an entity only has one attribute, then that attribute must serve as an identifier for the entity.  Typically, if only one attribute is found for an entity in the given data stream, then another attribute is "invented" for purposes of identifying.  It's often called "id", it's often an integer, and it's often populated using the autonumber feature of the database system.

When you are doing analysis, it's important to distinguish between the features that were given to you in the problem statement, and the features you added in order to make sense out of the problem.


Error 2003: Setting up MySQL Replication

I found the problem. The slave was trying to connect to port 7 instead of port 3306 for some reason. I had to manually specify that as part of CHANGE MASTER TO command and add MASTER_PORT=3306 and it works perfectly now.


But before changing that I want to know can we do this change in working hours or do we require down time for it

Yes you can change the autogrowth setting when database is being accessed, for next autogrowth instead of percentage the value in MB would be taken into account. Its almost always a good idea to keep autogrowth in MB. You can get tentative value for autogrowth from This Article use the query mentioned to set the tentative value. Keep monitoring using the query in future this value may change then accordingly you have to change yours

I suggest you to read Considerations for the "autogrow" and "autoshrink" settings in SQL Server


Moving data from PostgreSQL 32bit to 64bit

OK, first, you shouldn't need a heads-up that an upgrade is coming because you should have backups anyway. All the time. A disk failure, power surge or fire doesn't come with a few days advance warning either. (Ironically, you posted on world backup day).

32-bit PostgreSQL data files are not compatible with 64-bit binaries or vice versa. So you must, somehow, get the 32-bit binaries if you wish to read your data and dump it.

If you cannot do that on the NAS its self, you will need to:

Copy the data directory to another location ( you should do this anyway, for safekeeping )
Make another copy somewhere safe and read-only. Don't touch it again.
Install the same major version of PostgreSQL for the same operating system and architecture on a test system or VM. For example, if the NAS  runs on an x86_64 CPU but was running 32-bit i386 PostgreSQL 9.3.1 binaries, you could install Ubuntu 32-bit x86 and PostgreSQL 9.3.10 and expect it to read your data. Probably. There are some possible differences if the PostgreSQL on the NAS was compiled with special non-standard settings, but hopefully it wasn't.
Start 32-bit PostgreSQL to use your copied directory
Use pg_dumpall --globals-only to dump users, etc. Then for each database use pg_dump -Fc to dump the database.
Restore the dumps to your new database. Or, preferably, set up a database server somewhere safe that you have reasonable control over.

Now go yell at the people who made this incredibly stupid change without telling anyone.

If you find that you can't load the data files on a regular Ubuntu 32-bit VM with the same PostgreSQL major version (x.y, e.g. if you had 9.4.1 you need 9.4.x and can't use 9.3.x or 9.5.x; yes, PostgreSQL's version numbering is stupid) then you might need to find an old version of the NAS's firmware and extract the PostgreSQL binaries from it, then get them to run on a VM. Or compile your own PostgreSQL with the same non-standard options the NAS people used. Thankfully this isn't likely, as few people change the block size, wal segment size, etc... and I really wish they weren't ./configure options at all.


What is the correct way of creating a one-to-one relationship?

The most important thing is to word constraints precisely and not allow for logical errors. Options 1, 2, 3 contain logical errors. Option 4 is close, but may not represent reality very good -- in general one person may have more than one passport (dual citizenship).

Option 1 is not realistic, because it states that there can not exist a person without a passport.

Option 2 essentially allows a passport to exist without a person..

Option 3 states that passport is a person.

Option 4 is the closest and can be worded as:

[P1] Person (PERSON_ID) named (NAME) exists.

(c1.1) Person is identified by PERSON_ID.

(c1.2) Each person has exactly one name; for each name, more than one person can have that name.

[P2] Passport (PASSPORT_ID) issued by country (COUNTRY) is owned by person (PERSON_ID)

(c2.1) Passport is Identified by PASSPORT_ID.

(c2.2) Each passport is issued by exactly one country; for each country, more than one passport can be issued by that country.

(c2.3) Each passport is owned by exactly one person; for each person that person may own at most one passport.

(c2.4) If a passport issued by a country is owned by a person then that person must exist.

person {PERSON_ID, NAME}  -- p1
   KEY {PERSON_ID}        -- c1.1

passport {PASSPORT_ID, COUNTRY, PERSON_ID}  -- p2
     KEY {PASSPORT_ID}                      -- c2.1
     KEY {PERSON_ID}                        -- c2.3

FOREIGN KEY {PERSON_ID} REFERENCES person {PERSON_ID} -- c2.4

Note:

[Px]   = predicate x
[cx.y] = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

All attributes NOT NULL

EDIT

Just to be clear, option 3 would have been the correct choice had you chosen a different example. Say, Person & Employee or Employee & Accountant instead of Person & Passport. Because an employee is a person, and an accountant is an employee. The is-a relationship implies a proper subset.

This is a great example of how focusing on technical details may introduce logical errors, and there is no such a thing as a small logical error.


Why would someone store text in an SQL Image field?

The only options to store data over 8000 bytes was text, ntext, or image back in the days of SQL Server 2000. The appropriate choice for long character data strings would be text or ntext, with text appropriate for single-byte characters tied to the column collations code page and ntext for Unicode (UCS-2).

I can only speculate as the why developers used the binary image type to store character data but it could be in order to store characters as UTF-8, which is more compact that ntext UCS-2. Although this method saves space, it does come with the cost of not being able to easily evaluate the character data in T-SQL; encoding/decoding of UTF-8 stored as binary needs would need to be done in the application code.