Power BI uses Power Query (M) to extract and transform the data that is loaded into the data model.  A Power Query statement is broken into individual steps that are reevaluated each time the report dataset is refreshed.  This fluidity can be leveraged to return dynamically structured datasets.  Power Query can consume unstructured data, such as JSON or XML, and programmatically expand each element into separate columns as part of its sequence of applied steps.

Once the Power BI report is published, each time it is refreshed, it will retrieve the current unstructured base data from the source JSON column.  Then, it will parse & update the column structure in the resulting dataset automatically, without needing to manually refresh the data & republish using Power BI Desktop.

If the source data is not already in an unstructured format, you will need to prepare the raw tabular data for Power Query ingestion.  The desired columns will need to be rendered into key/pairs in a valid JSON string in the source table that you will reference in Power Query Editor.  Make sure you put these columns into a single JSON object for each row, as opposed to putting the key/pair values for every row into nested objects within a single element.  SQL Server 2016 and above have some JSON formatting tools that can assist with this transformation.

The required Power Query functions are as follows:

Table.TransformColumns to return the contents of a valid JSON string into a Json.Document object.  This applied step can be easily inserted by selecting "Transform -> JSON" from the context menu of the column header in Power Query Editor.

JSONTableName = Table.TransformColumns(sourceTableName,{{JSONStringColumnName, Json.Document}})

Table.ExpandRecordColumn to parse the JSON object key into columns.  In order to have the columns dynamically rendered you will also need to use a combination of Record.FieldNames, Record.Combine, & Table.Column.

DynamicTableName = Table.ExpandRecordColumn(JSONTableName, JSONStringColumnName, Record.FieldNames(Record.Combine(Table.Column(JSONTableName, JSONStringColumnName))))

One consideration for this dynamic approach, if you remove a key/value pair from the JSON object, it will, by design, remove the column from the refreshed dataset.  Even though any visualizations that are using that particular column will then show an error, it is not a critical issue and can automatically be fixed by clicking the "Fix This" button.  However, to avoid this altogether, it is best to avoid removing columns from the structure.


select
rank() over (partition by jurisdiction_id order by year desc) AS rank,
*
from your_table
order by rank,total desc;

rank() over (...) is a window function, see: window functions in postgresql


Could not find a log backup file that could be applied to secondary database

Run the following from the primary node.  It will output a Recovery Chain for each database hosted on the Instance.  It should show you not only the files you need to recover, but the order in which you need to do so.  You should be able copy the commands from the query output, paste them into a query window connected to your secondary, and so long as you also brought over the backups files, run the commands in the order supplied and get the database up to a point where you can join it to the AG.  This script supports striped backups, differentials, COPY_ONLY, etc.

WITH BackupHist
AS
(
        SELECT
                s.server_name
            ,   d.name AS database_name
            ,   STUFF(( SELECT  ''', DISK = ''' + physical_device_name
                    FROM    msdb.dbo.backupmediafamily
                    WHERE   media_set_id = s.media_set_id
                    ORDER BY family_sequence_number
                    FOR XML PATH('')),
                1 ,
                3 ,
                '') + '''' AS physical_device_name
            ,   (   SELECT  TOP 1
                        CASE device_type
                            WHEN 2 THEN 'Disk'
                            WHEN 102 THEN 'Backup Device (Disk)'
                            WHEN 5 THEN 'Tape'
                            WHEN 105 THEN 'Backup Device (Tape)'
                            WHEN 7 THEN 'Virtual Device'
                        END AS device_type
                    FROM    msdb.dbo.backupmediafamily
                    WHERE   media_set_id = s.media_set_id) AS device_type
            ,   CAST (s.backup_size / 1048576.0 AS FLOAT) AS backup_size_mb
            ,   CAST (s.compressed_backup_size / 1048576.0 AS FLOAT) AS compressed_backup_size_mb
            ,   s.backup_start_date
            ,   s.first_lsn
            ,   s.last_lsn
            ,   s.checkpoint_lsn
            ,   s.backup_finish_date
            ,   s.database_backup_lsn
            ,   s.is_copy_only
            ,   CASE s.[type]
                    WHEN 'D' THEN 'Database (Full)'
                    WHEN 'I' THEN 'Database (Differential)'
                    WHEN 'L' THEN 'Transaction Log'
                    WHEN 'F' THEN 'File or Filegroup (Full)'
                    WHEN 'G' THEN 'File or Filegroup (DIfferential)'
                    WHEN 'P' THEN 'Partial (Full)'
                    WHEN 'Q' THEN 'Partial (Differential)'
                END AS backup_type
            ,   s.recovery_model
        FROM    msdb.dbo.backupset s RIGHT OUTER JOIN sys.databases d
                ON s.database_name = d.name
                AND s.recovery_model = d.recovery_model_desc
        COLLATE SQL_Latin1_General_CP1_CI_AS
), BackupHistFullIterations AS
(
    SELECT
            server_name
        ,   database_name
        ,   backup_finish_date
        ,   backup_type
        ,   database_backup_lsn
        ,   is_copy_only
        ,   first_lsn
        ,   last_lsn
        ,   checkpoint_lsn
        ,   physical_device_name
        ,   ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM    BackupHist
    WHERE   backup_type = 'Database (Full)'
), BackupHistDiffIterations AS
(
    SELECT
            server_name
        ,   database_name
        ,   backup_finish_date
        ,   backup_type
        ,   database_backup_lsn
        ,   is_copy_only
        ,   first_lsn
        ,   last_lsn
        ,   checkpoint_lsn
        ,   physical_device_name
        ,   ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_finish_date DESC) AS BackupIteration
    FROM    BackupHist
    WHERE   backup_type = 'Database (Differential)'
), BackupHistFullDiffRestores AS
(
        SELECT  *
            ,   MAX(last_lsn) OVER (PARTITION BY base.database_name ORDER BY backup_finish_date DESC) AS recent_last_lsn
            ,   ROW_NUMBER() OVER (PARTITION BY base.database_name ORDER BY backup_finish_date DESC) AS recent_bak_num
        FROM (
                SELECT  *
                FROM    BackupHistFullIterations
                WHERE   BackupIteration = 1 -- Show the most recent iteration

                UNION ALL

                -- Get most recent Differential based on Most Recent Full Backup if exists and most recent full is NOT a COPY-ONLY
                SELECT  *
                FROM    BackupHistDiffIterations d
                WHERE   d.BackupIteration = 1
                    AND d.database_backup_lsn = (   SELECT MAX(checkpoint_lsn)
                                                FROM BackupHistFullIterations
                                                WHERE BackupIteration = 1
                                                    AND database_name = d.database_name
                                                )
            ) base
)
SELECT
            server_name
        ,   database_name
        ,   backup_finish_date
        ,   backup_type
        ,   first_lsn
        ,   last_lsn
        ,   checkpoint_lsn
        ,   is_copy_only
        ,   CASE backup_type WHEN 'Database (Full)' THEN 'RESTORE DATABASE [' + database_name + '] FROM  ' + physical_device_name + ' WITH  FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5'
                WHEN 'Database (Differential)' THEN 'RESTORE DATABASE [' + database_name + '] FROM  ' + physical_device_name + ' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5'
                WHEN 'Transaction Log' THEN 'RESTORE LOG [' + database_name + '] FROM  ' + physical_device_name + ' WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5'
                ELSE ''
            END AS RestoreStatement
FROM (
    SELECT  fdr.server_name
        ,   fdr.database_name
        ,   fdr.backup_finish_date
        ,   fdr.backup_type
        ,   fdr.physical_device_name
        ,   fdr.first_lsn
        ,   fdr.last_lsn
        ,   fdr.checkpoint_lsn
        ,   fdr.database_backup_lsn
        ,   fdr.is_copy_only
    FROM    BackupHistFullDiffRestores fdr

    UNION ALL

    SELECT  bhlr.server_name
        ,   bhlr.database_name
        ,   bhlr.backup_finish_date
        ,   bhlr.backup_type
        ,   bhlr.physical_device_name
        ,   bhlr.first_lsn
        ,   bhlr.last_lsn
        ,   bhlr.checkpoint_lsn
        ,   bhlr.database_backup_lsn
        ,   bhlr.is_copy_only
    FROM    BackupHistFullDiffRestores bhfdr
            INNER JOIN BackupHist bhlr
                ON bhfdr.database_name = bhlr.database_name
                AND bhlr.last_lsn >= bhfdr.recent_last_lsn
                AND bhfdr.recent_bak_num = 1
    WHERE   bhlr.backup_type = 'Transaction Log'
) restore_cmd
ORDER BY 1, 2, 6


Problem with pattern matching on unicode characters

See if doing a binary collate fits what you need.  Here is a quick test.

USE Tempdb
GO

IF OBJECT_ID('PattMatch') IS NOT NULL
BEGIN
  DROP TABLE PattMatch
END
GO

CREATE TABLE PattMatch (COL1 NVARCHAR(50))
GO

INSERT INTO PattMatch
VALUES (nchar(46797)),(nchar(14843)),('ddddddd*'),('lettersand9999')
GO

DECLARE @Pattern nvarchar(50) = N'%[^a-z]%'
SELECT PatIndex(@Pattern, COL1 COLLATE Latin1_General_BIN2), COL1 FROM PattMatch
GO

DROP TABLE PattMatch
GO

--your test
DECLARE @Pattern nvarchar(50) = N'%[^a-z]%'
SELECT PatIndex(@Pattern, nchar(46797) COLLATE Latin1_General_BIN2)
SELECT PatIndex(@Pattern, nchar(14843) COLLATE Latin1_General_BIN2)


If you want to list all the roles of a particular user:

select granted_role,admin_option,default_role from dba_role_privs where grantee='&user';

If you want to list all users with all roles assigned:

select b.username,a.granted_role,a.admin_option,a.default_role from dba_role_privs a join dba_users b on (a.grantee=b.username);


Going by your other questions, it sounds like you have a short archive_timeout so your WAL archives are mostly empty, but are still the full pre-allocated 16MB file.

On most platforms you just gzip them, e.g.

archive_command = 'gzip -c < "%p" > /archive/path/%f"'

On Windows this won't work natively due to the lack of the gzip command. You'll need to install a compression utility that's usable from the command line, since the Windows zip support isn't directly usable from the commandline.

I suggest installing 7-zip, then using it in your archive_command, with something like (untested):

archive_command = '%PROGRAMFILES%\7zip\7z.exe a "C:\\TheWALArchive\%f" "%p"'

Note that your restore_command will need a corresponding decompression step.


Query - Match Name to Multiple Unique Types

-- this should work for a starter
select distinct MyTable.Name, MyTable.Type
from MyTable
where Name in (
        select t1.Name
        from (
            select t0.Name, t0.Type
            from (
                select distinct MyTable.Name, MyTable.Type
                from MyTable
                ) as t0
            group by t0.Name, t0.Type
            ) as t1
        group by t1.Name
        having count(*) > 1
        );


Database schema for all objects is stored in the PRIMARY filegroup although user data may optionally be stored in a different filegroup.  A piecemeal of individual filegroups is not intended to be used to create a new database, but rather to restore critical data before less frequently used data.  That technique allows a database to be partial available for use during a restore sequence before the entire restore is complete.

  if no, what is the purpose of the file groups then ?

In addition to piecemeal restores, user-defined filegroups allow you to control placement of individual tables, indexes, and partitions.  This allows the underlying files to be placed on different underlying storage devices such as in a scenario where isolated storage provides a performance improvement (e.g. segregate random and sequential access) or with tiered storage (e.g. archive data on slower, less expensive media).


How to create identity field in MySQL without using auto_increment column?

You have to use UUID() function that guarantee that returned string is unique not only within your table or database but all over the world.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid

  A UUID is designed as a number that is globally unique in space and
  time. Two calls to UUID() are expected to generate two different
  values, even if these calls are performed on two separate computers
  that are not connected to each other.

Also there is UUID_SHORT() function that returns 64-bit INT instead of string, that is way faster for indexing and search.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid-short


SQL Server Grant CRUD to Users on One Schema

Is this the right approach?

This is the correct approach, users with that role will only be able to perform CRUD operations on objects in the training schema and nothing else. This is the least privilege approach and is correct.

True least privilege would actually be assigning permissions to only the specific objects in that schema, but as you've indicated users should have access to all objects, then applying permissions schema-wide is appropriate.

  I do not want them to have any other permissions on any other object
  outside that schema

You may not actually want this. Consider the public server role. For any object that does not have explicit permissions (GRANT or DENY), permissions are inherited from the public role for a user (any user, all users).

Since you cannot revoke public membership from a user, you would need to explicitly DENY permission on every single object just to prevent users from seeing the object listed in Management Studio - is this really necessary? They have no access beyond seeing the object exists, users still cannot interact with them.

Public access grants users, by default, nothing more than access to a few system catalog views to allow all users to view the name and definition of basic objects and some basic DB/Server info (see this article for more info).


In Oracle, can we restore a database onto the same instance with a different name?

Easily - just use RMAN to clone it. I think you mean in Oracle terminology, clone the database into a new instance on the same server. If you really do mean clone the schema into the same Oracle instance, then the easiest way is Datapump.


"Can't find file mysql/plugin.frm" when starting up MySQL

If you have mysql data directory at non-standard location and your system has apparmor, ensure that /etc/apparmor.d/usr.sbin.mysqld has your data directory whitelisted. The configuration should look like:

/mysql/data/dir/ r, #trailing slash is mandatory
/mysql/data/dir/** rwk,

Apparmor daemon has to be restarted after you make any changes to config.


string or binary data would be truncated in table '******', column '******'. Truncated value: '******'

It looks like this table was defined with dynamic data masking, and the user that the application uses to access the database doesn't have permission to view masked data (which is good!).
This is why the behavior differs between the application and SSMS: I expect you're using a higher-privileged user when running the query from SSMS
Here's a demo:
CREATE TABLE dbo.Test
(
    Id int IDENTITY PRIMARY KEY,
    Filler varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL
);
GO

CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON dbo.Test TO TestUser;
GRANT INSERT ON dbo.Test TO TestUser;
GO

EXECUTE AS USER = 'TestUser';
INSERT dbo.Test
    (Filler)
VALUES
    (REPLICATE(N'A', 101));
REVERT;

The result of which is:
Msg 2628, Level 16, State 1, Line 12
String or binary data would be truncated in table '******', column '******'. Truncated value: '******'.

To get the full message in the application, you'll need to grant the user the UNMASK permission:
GRANT UNMASK TO TestUser;

Running the INSERT code again results in the full error text:
Msg 2628, Level 16, State 1, Line 14
String or binary data would be truncated in table 'tempdb.dbo.Test', column 'Filler'. Truncated value: 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'.

When using Azure SQL Database, Dynamic Data Masking can also be configured in the Azure Portal user interface.  You may want to check there (although as far as I can tell, masks set up this way still update the sys.columns metadata):

I would also check if there are other databases that are part of the same logical SQL Server instance in Azure - maybe there are some kind of strange, cross-database queries going on.


Wise to use trigger to update another table?

Mostly Copy/Pasting my response from this question on stackoverflow

Triggers can be very alluring, when you first start using them they seem like a magic bullet to all kinds of problems. But, they make "magic" stuff happen, if you don't know the database inside out, it can seem like really strange things happen (such as inserts into other tables, input data changing, etc). Before implementing things as a trigger I'd seriously consider instead enforcing the use of an API around the schema (preferably in the database, but outside if you can't).

Some things I'd still use triggers for

Keeping track of "date_created" and "date_last_edited" fields
Inserting "ID"'s (in oracle, where there is no auto id field)
Keeping change history

Things you wouldn't want to use triggers for

business rules/logic
anything which connects outside of the database (eg a webservice call)
Access control
Anything which isn't transactional ( anything you do in the trigger MUST be able to rollback with the transaction )


mysql - query which provides additional column with iterative number by condition

For MySQL 8+

SELECT user_id, activity_date, COUNT(*) OVER (PARTITION BY user_id ORDER BY activity_date ASC) additional_counter
FROM tablename;

For MySQL 5+

SELECT t1.user_id, t1.activity_date, COUNT(t2.activity_date) additional_counter
FROM tablename t1, tablename t2
WHERE t1.activity_date >= t2.activity_date
  AND t1.user_id = t2.user_id
GROUP BY t1.user_id, t1.activity_date;

fiddle


The type DOUBLE is a floating point type represented in binary internally. Binary floating point numbers will produce "rounding errors" when converted to base 10. If you want precise numeric calculations with a decimal point, you'll have to use the NUMERIC or DECIMAL type.

To help you understand why floating point types have rounding errors when converting to base 10, please see this article:

https://stackoverflow.com/questions/2100490/floating-point-inaccuracy-examples

Also, just for your reference, the NUMERIC or DECIMAL types are going to be orders of magnitude slower at calculations compared to the DOUBLE type.

Also see Data Types in SQLite.


Oracle 12c connection configuration errors

You can use the following link to access Database Express through the browser:

  https://yourhostname:5500/em

Or

  https://youripaddress:5500/em


Is there a more efficient way to run this query for first/max transaction?

This does it in a single table scan:

SELECT Account_ID
  , MIN(Min_Date) AS DateFirst
  , MIN(DATEDIFF(day, Min_Date, NULLIF(Tran_Date, Min_Date))) AS Days_Passed
  , MAX(Tran_Amount) AS Max_Amount
FROM (
  SELECT Account_ID
     , Tran_Date
     , Tran_Amount
     , MIN(Tran_Date) OVER ( PARTITION BY Account_ID) AS Min_Date
     , MAX(Tran_Amount) OVER (PARTITION BY Account_ID) AS Max_Amount
  FROM trans
  GROUP BY Account_ID, Tran_Date, Tran_Amount
) I
WHERE I.Tran_Amount = I.Max_Amount
  OR I.Tran_Date = I.Min_Date
GROUP BY Account_ID

This query assumes that only a few rows are found per Account_ID. You can improve the speed of the query (which does a sort) by adding Tran_Amount to the index. While your question does say you cannot edit the schema, at least you can have a discussion with the DBA if you see an improvement on experimental data.


Domain group as single login, but still many different database users?

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

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

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

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

EDIT:
For example:

AD Group : DOMAIN\Sales

Users: Bob and Joe

AD Group: DOMAIN\Sales_US

User: Bob

AD Group: DOMAIN\Sales_Canada

User: Joe

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

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

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

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

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


you can use link to create index on MS-Access.

Hope this query work ok in MS-Access ,

select employees.id, employees.name, last_time_id
,(select top 1 time_notes.id from time_notes where time_notes.time_id=last_time_id)
time_note_present
from
(
select employees.id, employees.name,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC)
as last_time_id
from employees
)t4

Thanks @Perkins.I have edited my answer.


How to copy database schema in the same database for testing purposes?

If using Toad: Database -> Export -> Generate Schema Script

You can choose to copy an entire schema (SC1, in your case) and then ensure that it has

Then log in as the SC2 schema user (or as DBA/SYS) and deploy the SC2 schema objects using the "deploy all" script that comes with the export.  If any of the objects use functions/procedures from other schemas, you'll need to ensure that either the correct permissions are set up, or you run this as DBA/SYS.

If using SQL Developer:  Tools -> Database export

Choose the source Database and Ensure "Export DDL" is checked (if you want to copy the objects as well).

Then, select which objects you want to copy.

In the "Specify objects" area, Click "More" and choose the source schema.  Then select the objects and click the right arrow, or click the double right arrow to select all objects.

Keep moving through the wizard and it should export multiple scripts to run.  Run them all and you've got yourself a copy.

NOTE!  I highly recommend you don't do this in production.  I'm hoping you're not trying to create SC2 in a production Database.


Query perfomance with multiple JOINs on MyISAM-based DB

Foreign keys are not required in order to execute a JOIN. Foreign keys are restrictions that are there for security reasons (so that you cannot change the value of a column, or delete/insert a row ending up with an inconsistent state between tables). It is true that in most cases foreign key are used as JOIN matching conditions.

The reasons why InnoDB is recommended is because its support of data integrity thanks to these foreign keys and database transactions. It can also support better concurrency as writes do not need to lock the whole table in order to proceed, only the rows modified. That is why it is recommended for write-loads. If your tables are going to be read-only, transactions, integrity and concurrency are not so important (because you are not going to modify the data), that is why in those cases the simpler MyISAM format can work fine.

InnoDB also has its own buffering management, which can be fine-tuned better than with MyISAM, where its cache works only for indexes, leaving data caching management to the filesystem cache.

There is one case in which foreign key creation can improve your performance: on creation, InnoDB creates a secondary index on the column if it didn't have one beforehand. This functionality is as easy to overcome as creating one on any column that may be referenced on the JOIN.

In summary, InnoDB has many advantages, but in a read-only workload MyISAM can work properly. If you want the data security, however, you will have to implement that on a software layer, easily leading to errors, that is why foreign keys are always recommended.


mysqldump not escaping single quotes properly

That output of mysqldump is working as designed, and it is properly escaped, unless you try to restore the dump on a MySQL instance with SQL_MODE=NO_BACKSLASH_ESCAPES set.

There's an outstanding feature request to make mysqldump use a pair of single-quotes to escape literal single-quotes, as per ANSI SQL.  See http://bugs.mysql.com/bug.php?id=65941

In the meantime you might be able to convert from backslash-singlequote to pair-of-singlequotes with a command line this:

mysqldump test | sed -e "s/\\\'/''/g" > test-dump.sql

I tried that out briefly by creating a dummy table in my test database and inserting the string "O'Hare" into the table.  But that's hardly a comprehensive test -- I take no responsibility for this suggestion working in all cases.


Excluding a specific table from a SSDT deploy

You can use the AgileSqlClub SqlPackage Deployment Filter.

Brief instructions reproduced from the original article by Ed Elliott:

Download the filter from agilesqlclub.codeplex.com
Put the DLL into the same folder as sqlpackage.exe
Add these command line parameters to your deployment:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(BLAH)"

This will neither deploy, drop or alter anything in the BLAH schema.

See the original article for complete details.


Why scanning an index backwards is slower?

The author, Chaithra Gopalareddy,  of the related article, MySQL 8.0 Labs – Descending Indexes in MySQL, explains in a comment why backwards index scans are slightly less efficient than forward scans:

Thanks for showing interest in the new feature. The ~15% cost benefit in forward scans can be attributed to the optimizations done in innodb to favor forward scans over backward scans.
For ex: W.r.t a scan within the page – The records in a page form a singly linked list. To get the next record, a forward scan just follows the link where as the backward scan need to start from the beginning (first slot) till the current slot/record to identify the previous record.
Along with the above, there are some more contributing factors like, during page switch – page latching rules currently defined in innodb favor forward scans over backward scans.

So there are two factors:

the single linked list structure of records inside a page
page latching rules regarding page switches


parse rs.status() output to display one secondary node for mongodump destination

It's worth noting that if you pass in a replicaset string into mongodump it defaults to reading from a secondary from the set (note: in 3.0.5+ this is not the case if you connect to a mongos in a sharded cluster).  If your replica set name was repl1 it would be something like:

./mongodump --host repl1/example-1.domain:27200,example-2.domain:27200

For reference, in the more recent versions (since the tools were re-written in Go), this is done by setting Monotonic mode when not connected to a mongos (source from 3.0.6 here).

To answer your specific question, a really simple way to parse rs.status() is with a filter, and it will fit on one line:

rs.status().members.filter(function(rsStatus) { return rsStatus.state === 2;})[0].name

That will return the name field of the first (hence [0]) secondary (state === 2) found in the array of members returned by rs.status()


Postgres pg_basebackup reports directory exists but is not empty

In my opinion it is not related to mklink or Windows issues.

Per pg_basebackup docs, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server.

-F format
--format=format

    Selects the format for the output. format can be one of the following:

    p
    plain

    Write the output as plain files, with the same layout as the current data directory and tablespaces. When the cluster has no
    additional tablespaces, the whole database will be placed in the
    target directory. If the cluster contains additional tablespaces, the
    main data directory will be placed in the target directory, but all
    other tablespaces will be placed in the same absolute path as they
    have on the server.

    This is the default format.

Solution: do not run it on the same server, or change backup format.


SQL Server 2008 R2 Service Broker Troubleshooting

The way you run ssbdiagnose in the post it would connect to the database MyDatabase on localhost and search for the initiator and target service. You mention that you've set up for two different instances, so the correct command line should be something like:

ssbdiagnose CONFIGURATION\
  FROM SERVICE /test/initiator -E -S <instance1> -d <db1>\
  TO SERVICE /test/target -E -S <instance2> -d <db2>

(I used the unix style command line terminators to prettify the code. You should use a single line).

When the connection params are pulled out ahead of the CONFIGURATION keyword they apply to every connection (FROM, TO).


seeking a confirmation -- SQL Server backup performance

Yes, I tested this with a 2TB database.  The backup time dropped from 5 hours to roughly 4 hours 15 minutes after breaking it up into 4 files and putting them in the same drive.  Another advantage is you could back them up to separate drives if your drives IOPS is your bottleneck.

Also, I believe Paul Randall goes through this in his "Microsoft Certified Master Backup and Restore internals" modules.

/edit:
Furthermore, I've been testing something similar with RoboCopy's /MT command (Multi-Threading) and we get similar performance gains doing multi threaded copies of backup files.  During our next backup, I'll test to see if I see multiple SPIDs as well.


Cannot delete MySQL trigger. Appears in information_schema, but no .TRN file

Based on the suggestion I gave in the chat room, here is my answer

CREATE TABLE cash_op LIKE cash_operation;
Goto the datadir in your Windows server
rename cash_op.MYD cash_op.MYX
copy cash_operation.MYD cash_op.MYD
login to mysql and run

REPAIR TABLE cash_op;
ALTER TABLE cash_operation RENAME cash_op_old;
ALTER TABLE cash_op RENAME cash_operation;

Go create the two triggers on the new cash_operation table

You said in the chat room : Awesome, that worked! You can post it as the answer.

I am glad you gave it a try and it worked for you !!!

BTW you can drop the old table with DROP TABLE cash_op_old;


There is Oracle bug. When sometimes opening librarys in Oracle Builder 11g which is compiled with Oracle Builder 10g The 11g Builder crashes. I solved this problem by converting librarys to pld in Oracle Builder 10g and then converting them back to pll with Oracle Builder 11g.


How to select max value faster from large size table

You have two options:

as TomBom Mentioned, you need to create an index on ParameterID column using this statement

create index idx_modelparam_ems_parameterid on modelparam_ems(ParameterID);

If your ParameterID column is AUTO_INCREMENT,

you can get your max id by getting the next AUTO_INCREMENT value from the following query

SELECT AUTO_INCREMENT-1
FROM information_schema.tables
WHERE table_name = 'modelparam_ems'
AND table_schema = 'YourDBName' ;

If the engine is MyISAM and you are sure that there is no gap between two ids, you can find your max id by getting number of rows in the table as below. ROWS column contains the value for number of rows.

EXPLAIN SELECT * FROM modelparam_ems;

Note that In Innodb the value returned in rows is an estimation of total rows. So it is not as accurate as MyISAM.

Disclaimer

Option 2 may be both unreliable, and subjected to failure. Use it only if option 1 is not possible and you can tolerate the failure.


Prevent SSMS changing tab to space when copying text from Result Tab

Go to Options and check Retain CR/LF on copy or save.


Oracle also tracks the number of blocks that will be retrieved using the index values.  It may be your data is clustered by group_id.  In that case, it may make sense to use the index when retrieving more than 5% of the rows.  If the data is sufficiently clustered for values 5 and 6, then using the index may be roughly equivalent to doing full table scan of a few blocks.

The relevant calculation will involve the number of index blocks needing to be retrieved, the number of data blocks needing to be retrieved, and the CPU required to access the data.  There are tuning parameters which adjust the relative costs of retrieving index versus data blocks.  This can change the plan that is being used.


SQL Server 2012 is database name mandatory for logon?

I think you are confusing database name and instance name. SQL Server can be installed as a default instance (in which case connecting to SERVERNAME is sufficient), or as a named instance (in which case you need to use SERVERNAME\INSTANCENAME or in some cases SERVERNAME,PORT_NUMBER). You would never connect to SQL Server using SERVERNAME\DBNAME, but you may wish to explicitly set the database name in the connection string (or in the Options >> section of the connection dialog) anyway, so that your user does not execute things in the wrong database by mistake (I suspect this is how a lot of user objects end up in master, model, tempdb, etc).

In SQL Server 2012, database name is only mandatory in the connection string when you are connecting to a partially contained database with a contained database user. For example:

EXEC sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
CREATE DATABASE contained;
GO
ALTER DATABASE contained SET CONTAINMENT = PARTIAL;
GO
USE contained;
GO
CREATE USER contained WITH PASSWORD = 'contained';
GO

If I try to connect to the instance with those credentials, but without specifying a database name explicitly under Options >>:

I get the following error:

  Cannot connect to SHELDON\SQL2014CTPn.
  Login failed for user 'contained'. (Microsoft SQL Server, Error: 18456)

In the error log:

  Error: 18456, Severity: 14, State: 5.
  Login failed for user 'contained'. Reason: Could not find a matching login matching the name provided.

I get the same error message if I use the <Browse server...> option; I need to type the database manually:

When I do, I can connect. Of course the Object Explorer tree is much less useful when I connect as a contained database user; by design, I only have access to that database:

Contained databases came about to help make migration of databases to different servers easier. There is no way to just say "well, I'm a contained database user, but why don't you tell me which database I'm allowed to connect to?" You need to be explicit, especially because different contained databases could have different users with the same username (and even the same password); nothing in the system prevents this (because it would make migration more difficult).

If you are not using contained databases (and, specifically, contained database users), then nothing is different from previous versions.


Need a way to redesign tables such that there is minimal redundancy

Use "Normal Forms" to resolve your tables into a precise table which will contain both table data information, there are many Normal Forms, so please read about Normal Forms based on your requirements. Other way is that you can have a foreign key from first table in second table, so that you may not require to store first table data again in second table, while accessing in query u can put condition on that foreign key and access both table data rather than using first table data again in second table. I hope you got it, if not please let me know!!


Does MySQL Replication hamper the performance of my DB?

There are two major things a Master has to handle because of a Replication Setup

DISK I/O

The Master has to write every completed SQL transaction to the binary logs

NETWORK I/O

When a Slave is connected, the Master must play traffic cop.

Master writes SQL statement to the most recent Binary Log
Master polls for DB Connections emanating from Slaves
For Each Slave DB Connection, the Master does the Following:

Master receives request for the oldest SQL Statement from the Binary Log since the the Slave's last entry in its Relay Logs
Master sends the following to the Slave's IO Thread

Master Log Filename
Master Log Position
SQL Statement at the Master's Log Filename and Position

If a busy Master has Slaves with Seconds_Behind_Master : 0, simply marvelous.

If either of these metrics become noticeable, you may need to do certain things:

Scale up DB Server

Place Binary Logs on a Separate Disk
Faster Disk I/O
RAID10 (rather than RAID5)

Is raid 5 suitable for a mysql installation?
Master - Slave MySQL setup on a VMWare Cloud — Is it needed? (ServerFault)

More RAM

Scale out the Topology

Add More Slaves
Make Each a Slave Master to Other Slaves
Create Distribution Masters

Here are my other posts about setting up replication topologies

Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Aug 20, 2012 : How to use MySQL Server 5.6 for GeoIP replication?
Jun 25, 2012 : Mysql database replication on different vlan/subnet/another site
Dec 19, 2011 : Best way to setup master to multi master replication
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines
Aug 18, 2011 : With MySQL Replication, what level of resilience is possible?
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Mar 29, 2011 : MySQL high availability, failover and replication with Latency


SQL Server certificate for backups: Does expiration date matter?

I have tested this on SQL Server 2016 (Developer Edition, though that shouldn't matter), and can confirm:

No, after the Certificate expires, using it to encrypt a backup will cause the following error:

  Msg 3096, Level 16, State 1, Line xx
  The Certificate specified for backup encryption has expired.
  Msg 3013, Level 16, State 1, Line xx
  BACKUP DATABASE is terminating abnormally.

Yes, after a Certificate expires, backups encrypted from that Certificate can still be restored. BUT, you still need to have the Certificate loaded into master in order to do the RESTORE.

Just create a new Certificate, and be sure to specify the EXPIRY_DATE = 'utc_date' clause, else the expiration date will default to 1 year from the moment it was created.

As to why Certificates have expiration dates, that is probably to give more control over the security mechanism, similar to how you can set a password policy for Logins to expire. Certificates can be backed-up and used in multiple places, so it can help ensure that you don't have a piece of information sitting out there, somewhere, forever that can be used to compromise your system.


In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx .

Add:
Here is the explain for the test table

explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1;
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | t2    | index | from1,to1,from_to | from_to | 12      | NULL |    2 | Using index                                    |
|  1 | SIMPLE      | t1    | ALL   | idx               | NULL    | NULL    | NULL |    5 | Range checked for each record (index map: 0x1) |


PostgreSQL, what is the hostname address of my default database?

If you're connecting from the same machine, use localhost

A given computer can run multiple instances of PostgreSQL on different sockets (address:port pairs). The default port is 5432

A given PostgreSQL server can run multiple databases on the same instance. The postgres database is always there. There's no "default database" though really.

Are you sure the service is enabled and running? Did you set listen_addresses in postgresql.conf and set up authentication in pg_hba.conf?

Is the service actually running?


Depending on your privilege level, you'd want to use DBA_TABLES, ALL_TABLES, or USER_TABLES.  There is a StackOverflow thread that walks through this in some detail.

By default, Oracle identifiers are going to be stored in upper case in the data dictionary and queries are case-sensitive.  Most likely, if you have a table whose name contains the string "abc", you'd need to search in upper case, i.e.

SELECT *
  FROM dba_tables
 WHERE table_name LIKE '%ABC%'


I have made a script that digs through the error log to find the shutdown and startup times for a MySQL server:

######################
##BY: Michael       ##
##Created: 4-1-2016 ##
##Version: 1.01     ##
######################

#####################
######Variables######
#############################################################

#LogFile is where the ErrorLog dumps its filtered error log data
LogFile=/sw/pkg/mysql/admin/logs/shutdowns.log
#Error log holds the raw information from the MySQL Error log filtered by grep.
ErrorLog='grep 'Starting' /var/lib/mysql/'`hostname`'.err'
#OutputFile holds a comma delimited set of values of hostname,shutdown date, shutdown time, startup time
OutputFile=/sw/pkg/mysql/admin/logs/shutdowns_`date +"%Y-%m-%d"`.csv

#############################################################
######Variables######
#####################

##################
######Header######
#############################################################

#Write (Not append) to $LogFile
$ErrorLog > $LogFile
#Writes (Not append) the table headers
echo "hostname,Shutdown_date,Shutdown_time,Startup_time" > $OutputFile

#############################################################
######Header######
##################

################
######Body######
#############################################################

#Do not change variables!
q=0
intholder=-1

#Go line by line in $LogFile. '$i' is the current line (string).
while read i; do
        ((q++))

        #If the line ($i) is a line containing shut-down information then gathers in shut-down information
        if [[ $i =~ "Starting shutdown..." ]]
        then
                shutdowndate=`echo "$i" | grep -Eo '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}'`
                shutdowntime=`echo "$i" | grep -Eo '[[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}'`
                ((intholder=$q + 1))
        fi

        #If the line ($i) is a line containing start-up information then gather that information
        if [[ "$intholder" == "$q" ]]
        then
                startuptime=`echo "$i" | grep -Eo '[[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}'`
                echo "MySQL instance:`hostname` went down on $shutdowndate at $shutdowntime and started at $startuptime"

                echo "`hostname`,$shutdowndate,$shutdowntime,$startuptime" >> $OutputFile
        fi

#End loop
done <$LogFile

#############################################################
######Body######
################

##################
######Footer######
#############################################################
#       Nada
#############################################################
######Footer######
##################


High Availibility Solution to send data to multiple locations

Depending on your level of expertise, I would suggest you - Logshipping or AlwaysON AG.

  1) log shipping because we need to be able to show log backups to auditors and store them for years. Please correct me if I'm wrong but log shipping wouldn't let us create separate log backups.

Logshipping. It allows you to logship your databases to as many secondaries as you want.

You can choose standby option on secondary that will allow you to read data when the logs are not being restored. Think of it as reporting ONLY.

You can take COPY_ONLY log backups that wont break your log chain (or restrict users to COPY ONLY backups) and you can archive your log backups (depending on the frequency you have set up) - so that your auditors can store them. Just dont delete them or have a higher retention period in clean up job of logshipping.

  4) I never worked with other HA techniques so for example AlwaysOn or clusters is a one big unknown for me. I am happy to learn though as long as someone points me in the right direction.

You can implement AlwaysON availablity groups and happily take your Full, diffs and T-Log backups.

The important thing is that there is a learning curve for you. All the servers should be part of the same windows cluster, there is DNS dependency, you application should be able to use the new provider and connection string, etc. Since you are using Enterprise editon, this is something that you can try as a Proof of Concept and once you are confident, you can transition from logshipping to AlwaysON.

Note: We are currently transitioning from Logshipping to AlwaysON for our huge environment of 1000+ databases being hosted in different continents of the world.

Some references to get you started on ALwaysON part :

SQL Server 2012 AlwaysOn
Getting Started with AlwaysOn Availability Groups (SQL Server)
AlwaysOn Availability Groups
for SQL Server 2012, 2014, and 2016


Why have SQL databases like PostgresSQL and MySQL added JSON data types?

Anywhere you have or need EAV table, it's another way to solve the problem. Or anywhere you accept JSON, especially if the JSON is arbitrary.


Always Encrypted - certificate cannot be used as a column master key

Please refer to Create a self-signed certificate using PowerShell section of this article.

To be used as Always encrypted CMKs, certificates require a specific configuration.

You should be able to create a certificate to be used as CMK using the following commands

New-SelfSignedCertificate is a Windows PowerShell cmdlet that creates a self-signed certificate. The below examples show how to generate a certificate that can be used as a column master key for Always Encrypted.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUserMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

# To create a certificate in the local machine certificate store location you need to run the cmdlet as an administrator.
$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:LocalMachineMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

This should work with Server 2012, you will have to install makecert utility if it does not exist:

makecert.exe -n "CN=Always Encrypted Certificate - exported" -pe -sr CurrentUser -r -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -ss my -sky exchange -sp "Microsoft Strong Cryptographic Provider" -sy 1 -len 2048 -a sha256


UPDATE-SELECT Consistency Issue In Read Committed

Your SELECT is running in the same process (spid) as your UPDATE, so you can freely select the updated rows - as long as you're in the same process.

Here is a classic deadlock example that you can play with:

--Classic deadlock example:

Create table dbo.Test1 (col1 int);
Create table dbo.Test2 (col1 int);

Insert into dbo.test1 values(1)
Insert into dbo.test1 values(2)
Insert into dbo.test1 values(3)

Insert into dbo.test2 values(1)
Insert into dbo.test2 values(2)
Insert Into dbo.test2 values(3)

--Open a SSMS query session 1 and run these commands
begin tran
update  dbo.test1 set col1 = 2

--Open another SSMS query session 2 and run these commands
begin tran
update dbo.test2 set col1 = 1
update  dbo.test1 set col1 = 3

--Go to SSMS query session 1 and run these commands
update dbo.test2 set col1 = 4

rollback


Best way to import and/or upgrade Oracle database

That is possible.
Not really needed. You can just simply transfer the database to the new server, and run the PSU postinstall script there, that takes like 1 minute.
Depends on some factors:

platform of the old and new systems
size of the database + hardware environment (network/disk transfer speed and CPU power)
downtime allowed
Standard or Enterprise edition
archivelog mode enabled
have backups
need the current state of the database on the new server, or yesterday evening is good enough
can use licensed extra products

Some other methods:

RMAN restore from backup (and run the PSU postinstall script)
RMAN duplicate from active database or backup (and run the PSU postinstall script)
RMAN transportable tablespace/database
configure Streams replication and switch to new database
configure GoldenGate replication and switch to new database
configure the new host as a Data Guard standby then perform a role transition (and run the PSU postinstall script)

There is no best method. It depends on your actual task/environment.


Trigger on 2 tables : issue with INSERT and UPDATE

Your trigger function should work and cannot cause the described problem on its own. Moreover, this does not make any sense at all:

  If the ON UPDATE trigger is active then when a record is inserted into
  emprise, the field contributor is overwritten by the current_user
  value for all records.

UPDATE triggers are not fired on INSERT. Maybe just wrong terms?

And you mentioned primary keys before, but I don't see them here. Anything else "simplified"? You need to be precise to pin down the problem ...

Only a couple of simplifications, optimizations and a minor fix:

CREATE OR REPLACE FUNCTION activite.tbl_emprise_metadonnees()
  RETURNS trigger AS
$func$
BEGIN
   CASE TG_OP
   WHEN 'INSERT' THEN
      INSERT INTO activite.metadonnees (gid, date_publication, contributor, code_tranche)
      VALUES (NEW.gid, current_date, current_user, NEW.code_tranche);

   WHEN 'DELETE' THEN
      DELETE FROM activite.metadonnees
      WHERE  gid = OLD.gid

   WHEN 'UPDATE' THEN
      UPDATE activite.metadonnees
      SET   (gid, contributor, code_tranche) = (NEW.gid, current_user, NEW.code_tranche)
      WHERE  metadonnees.gid = OLD.gid
      AND   (gid, contributor, code_tranche)  -- only update if anything changes
            IS DISTINCT FROM (NEW.gid, current_user, NEW.code_tranche);
   ELSE
      -- do nothing (should not occur)
   END CASE;

   RETURN NULL;  -- for AFTER trigger
END
$func$  LANGUAGE plpgsql;

Note how I base the UPDATE on OLD.gid. It would be slightly incorrect to base it on NEW.gid and possibly miss changes to that column.

You can simplify RETURN because, quoting the manual:

  The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

And CASE is a bit shorter and faster.

The root of your problem is still burried elsewhere.

Aside from that, a clean solution would be to remove redundant storage (if at all possible). Use a FK constraint with ON UPDATE CASCADE ON DELETE CASCADE. Code examples:

Cascade deletes from multiple tables
How to implement a many-to-many relationship in PostgreSQL?

And store contributor and code_tranche in table emprise once per entity. Then you need no trigger on UPDATE or DELETE, just on INSERT. Or even merge the two tables (which seem to be in 1:1 relation). Then you need neither triggers nor FK constraints. NULL storage is cheap - in case you are worried about many null columns in the one table.

Do nullable columns occupy additional space in PostgreSQL?


What are the benefits of storing columns in JSON instead of traditional tables?

If you really want to be able to add as many fields as you want with no limitation (other than an arbitrary document size limit), consider a NoSQL solution such as MongoDB.

For relational databases: use one column per value. Putting a JSON blob in a column makes it virtually impossible to query (and painfully slow when you actually find a query that works).

Relational databases take advantage of data types when indexing, and are intended to be implemented with a normalized structure.

As a side note: this isn't to say you should never store JSON in a relational database. If you're adding true metadata, or if your JSON is describing the information that does not need to be queried and is only used for display, it may be overkill to create a separate column for all of the data points.
Check this one for detailed explanation


Database Design: New Table versus New Columns

What you are wrestling with is vertical partitioning.  This is a physical database design technique to improve performance.  As with any physical database design technique, its applicability depends on the specific queries you are trying to optimize and if this technique will optimize them.  From a logical standpoint, if these new fields depend upon the candidate key for your entity then they are facts about it that belong with it.  First you should make sure you fully understand the functional dependence of these new fields on your candidate keys to verify they really are facts about daily page views.  If they are, deciding to partition them into another table is a performance optimization that should only be done if it achieves your performance goals.

In general, vertical partitioning is useful if you will query these new columns infrequently and distinctly from the other columns in the original table.  By placing those columns in another table that shares the same PK as your existing table, you can query it directly when you want those new columns and get much greater through-put as you will have many more rows per page on disk for this new table as all the columns from the original table won't be sitting on those rows.  However, if you will always query these columns along with the columns in the original table then a vertical partition wouldn't make much sense as you will always have to outer join to get them.  Pages from tables on disk come into the buffer pool of a DBMS independently, never pre-joined, and so that join will have to happen with every query execution even if the data is pinned in the buffer pool.  In this scenario making them NULLABLE columns on the original table would enable the DBMS storage engine to store them efficiently when NULL and eliminate the need to join on retrieval.

It sounds to me like your use case is the latter and adding them as NULLABLE to your original table is the way to go.  But as with everything else in database design, it depends, and in order to make the right decision you need to know your expected workload and what making a good choice depends on.  One good example of a proper use case for vertical partitioning would be a person search panel, where your application has some very rarely populated information about a person that someone might want to search on but rarely does.  If you put that information into a different table you have some good options for performance.  You can write the search so that you have 2 queries - one that uses the main, always populated information to search (like last name or ssn) only, and one that outer joins the very infrequently populated information only when it is requested for search.  Or
you could take advantage of the DBMS optimizer if it is smart enough to recognize for a given set of host variables that the outer join is not needed and won't perform it, and thus you only have to create 1 query.

What DBMS platform are you using?  The way in which the platform handles NULL column storage, optimizes your query, as well as the availability of sparse column support (SQL Server has this) will impact the decision.  Ultimately I would recommend trying out both designs in a test environment with production sized data and workload and seeing which better achieves your performance objectives.


Your query is looking for the token = 'Baha'. You need a wildcard search for 'Baha*'.

Please try the following

SELECT
MATCH (`e`.`firstname`, `e`.`lastname`, `e`.`tc_identity`) AGAINST ('Baha*') AS `Score`,
`e`.*

FROM
`employees` AS `e`

WHERE
MATCH (`e`.`firstname`, `e`.`lastname`, `e`.`tc_identity`) AGAINST ('Baha*' IN BOOLEAN MODE)

ORDER BY `Score` DESC


Retrieve one of two possible values from 1 table in INFORMIX

This answer assumes that your table Ex1 has columns:

key_col — the key column.
flag_col — the flag column, containing L or P (with a check constraint).
data_col — a proxy for the unidentified set of columns containing the other data.
Primary key on key_col and flag_col.

A view like this should work (I didn't say it would work fast):

CREATE VIEW Relevant_Ex1(key_col, flag_col, data_col) AS
    SELECT A.Key_Col, A.Flag_Col, A.Data_Col
      FROM Ex1 AS A
      JOIN (SELECT B.Key_Col, MIN(Flag_Col) AS Flag_Col
              FROM Ex1 AS B
             GROUP BY B.Key_Col
           ) AS C
        ON A.Key_Col = C.Key_Col AND A.Flag_Col = C.Flag_Col;

This uses the fact that the flag column contains either L or P and L sorts before P.  So, if there are two rows for a given key, the value L will be selected, else the value P will be selected.


ntwdblib.dll : How to configure it to connect over tcpip ? (SQL Server, legacy)

Try <server name>, <port that instance is listening on> as explained here.

In most cases, a named instance is not going to be listening on port 1433. In order to connect over TCP/IP, you need to explicitly specify the port number. You can find this in SQL Server Configuration Manager - under SQL Server Network Configuration, Protocols for <Instance Name>, right-click TCP/IP and hit Properties. The port number is near the bottom under IPAll:

In my case, I'm using dynamic ports, which means a different port could be assigned next time the service starts. You can force a specific, static port in the box below - hit Apply, then OK, then restart the service. You can try to use 1433 here, which might work if this is the only instance on the machine, but I haven't tried to do this for app compatibility reasons - I've always tried to make changes to the app configuration rather than SQL Server.

Then change your app to use <server name>, <port number>.


SQL Server Database Mail failing to send emails with error Sender address rejected: Access denied

Perhaps the IP of your second node is not allowed to relay.


Printing comments or statements when executing script to a output file using TOAD/ORACLE

The syntax below will allow you to place comments either before or after your query, so that the comments display on the output.txt file:

BEGIN
    DBMS_OUTPUT.PUT_LINE('TEXT GOES HERE ');
END;
/

You should also issue SET SERVEROUTPUT ON to ensure the PUT_LINE command will work.

Here are some commands you can use while using DBMS_OUTPUT.PUT_LINE to control the formatting of the output:

SET UNDERLINE OFF
SET LINESIZE 32767
SET PAGES 10000
SET TRIMSPOOL ON
SET TRIMOUT ON
SET FEEDBACK OFF
SET HEADING ON
SET NEWPAGE 0
SET HEADSEP OFF
SET WRAP OFF
SET TERMOUT OFF

In my case, I had to use particular settings to allow my output to stop word wrapping and causing the formatting to get thrown off.

SET LINESIZE 32767
SET HEADING ON
SET HEADSEP OFF
SET WRAP OFF


A Google of "mysql accent insensitive search" produces various links with various "tricks" - which all seem to invalidate any indexes you may have on your table.

I had to implement something similar years ago for Irish names and addresses which may contain accents.

The strategy (if your RDBMS doesn't support accent insensitive searches) is to use a shadow column which keeps track of a "base" word - in our case unaccented, capitalised and spaces removed so that when a record with the name:

name_display
Ronán Ó Sé

is INSERTed (or UPDATEd), what will be stored as

name_shadow
RONANOSE

and then all searches are made against the shadow column.

In the app, we capitalised, and removed accents and spaces from all search strings when sending them to the server, so that the search was comparing like with like - i.e. unaccented, capitalised, space-removed strings.

This had the dual advantage of being always able to use indexes and being portable across RDBMS servers. Being an Irish app, we also had a strategem turning Mac and Mc into MC so that variant spellings were also taken into account - maybe an issue for you also?

We did this using TRIGGERs - but I believe that as of MySQL 5.7, GENERATED (also known as VIRTUAL or COMPUTED) columns are supported which may simplify the logic?


Logins aren't syncing across Availability Groups

My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually.
Something like the script from SQLSoldier, originally posted on the article Transferring Logins to a Database Mirror, should do the trick.


Best way to keep the last N rows by a certain column

Create a table (done 1 time)

CREATE TABLE salary
( employee_id INT NOT NULL,
  amount DECIMAL(10, 2) DEFAULT NULL,
  ts DATETIME DEFAULT '1000-01-01 00:00:00'
              ON UPDATE CURRENT_TIMESTAMP );

Insert 12 records for new employee (done 1 time when creating a new employee)

INSERT INTO salary (employee_id)
SELECT @new_employee_id
FROM ( SELECT 1 UNION SELECT 2 UNION .. UNION SELECT 12 ) numbers;

Add new data (done monthly)

UPDATE salary
SET amount = @new_amount
WHERE employee_id = @employee_id
ORDER BY ts ASC LIMIT 1;


How to troubleshoot SQL "The timeout period elapsed prior to completion of the operation or the server is not responding."

Have you tried polling for dbcc opentran? It could be you have an uncommitted transaction problem occurring on that database. Also, worth checking the collation of the databases is the same, always the first thing to check in a "this one works this one dosent" scenario

http://msdn.microsoft.com/en-us/library/ms182792.aspx


Are there opensource hierarchical database management systems

Since XML databases are hierarchical, you could look at those. I recently used BaseX and liked it a lot.


How to send a parameter to a trigger function from an external resource

You could use the set_config() and current_setting() functions for that. In the application just after establishing the database connection you call something like

select set_config('myapp.app_user','johnny',false);

to set a custom configuration variable for the session. In the trigger you then use

select into v_actualuser current_setting('myapp.app_user');

to retrieve the value.

This blog post even has a sample trigger for your viewing pleasure.


Percona: Backup preparation doesn't work ("cannot open ./xtrabackup_logfile")

You have not decompressed the Xtrabackup files if they still have the .qp extension.

Try using this inside the current folder:

sudo innobackeupex --decompress --parallel=4

where 4 stands for the number of threads you would like to use.

Also, for clean-up afterwards:

sudo find ${DEST_DIR}/ -name "*.qp" -delete


As CTE's are for the first test quite difficult to understand, i wrote a approach, with all Levels at the end
The Option at the end is nit needed, but to reduce espeically when you are trying to figure out what happens, you shoukld add it at a low number
WITH MyTest as
(
  SELECT P.DBName, P.Privilege,p.PrivilegeType,P.PrivilegeDetail,P.TableName , CAST(P.PrivilegeDetail AS VarChar(Max)) as Level
  FROM SQLTest P
  WHERE P.Privilege = 'Default_Role'

  UNION ALL

  SELECT P1.DBName, P1.Privilege,p1.PrivilegeType,P1.PrivilegeDetail,p1.TableName , CAST(P1.PrivilegeDetail AS VarChar(Max)) + ', ' + M.Level
  FROM SQLTest P1
  INNER JOIN MyTest M
  ON M.PrivilegeDetail = P1.Privilege
 )
SELECT * From MyTest

OPTION (MAXRECURSION 50);

REsult
DBName  Privilege       PrivilegeType   PrivilegeDetail     TableName   Level
TSDB    Default_Role    Role            App_Role            (null)       App_Role
TSDB    App_Role        Role            Security_Role       (null)       Security_Role, App_Role
TSDB    Security_Role   Table           Customers           Customers    Customers, Security_Role, App_Role
TSDB    Security_Role   Table           Sales               Sales        Sales, Security_Role, App_Role
TSDB    Security_Role   Table           Users                Users       Users, Security_Role, App_Role


first of all, if you are trying to build standby database, you may just open database for read only.

alter database open read only;

I would recommend you to check this article for your reference


How to merge two databases, both with primary keys

I assume there is a UNIQUE index on the column with "Dave" and "Bob"?  Without that, how do you know what is "new"?

Let's call the column name (distinct from id).

Does some other table depend on Dave=5?  If so, the following will not suffice, and the solution is much messier.

Are there changes to the columns?  Or is it only "new" rows to be copied from A to B?

INSERT INTO B.user
    SELECT ...  -- excluding `id`
        FROM A.user
        LEFT JOIN B.user  USING(email)
        WHERE B.id IS NULL;     -- Not currently in B

To handle changes, look at INSERT ... ON DUPLICATE KEY UPDATE, again with a JOIN, but this time IS NOT NULL.


why the optimizer doesn't choose the optimal plan

The optimizer strives to get a plan that is "good enough", and this is not always the optimal one.

A very common reason is a too complex query. Breaking it down to a few queries helps the optimizer choose a better plan.

In some cases, too many indexes on a table can also cause this, as the optimizer
might use an index that is not the best one because as we said, it doesn't look for the best plan but rather a plan that is good enough.

Other reasons can be the use of local variables (the optimizer doesn't know their values), non SARGABLE arguments, missing indexes, improper use of hints and plan guides, and more..


MySQL 5.1 - long running select locking the table and not allowing inserts

MYISAM do not provide row-level locking so the whole table become locked on every DML or DDL statement. INNODB allows to lock only rows involved into the query so multiple concurrent queries can be performed. Therefore migration from the MYISAM to INNODB can solve the problem. The other way (not always possible) is to split the table by partitioning. Since mysql v5.7 each query locks only partitions involved while other are still free to be queried.


what is difference between RDBMS and NoSql on basis of how information is store in them?

RDBMS (Relational database management system) : is using SQL (Structured Query Language) to define and manipulate data. and it is used for large and complex query handling. in-order to work with RDBMS you need to define a structure (schema, table, constraints, ...etc). as an example (out of  a town in-order for the people to live together they need to speak the same language, have a infrastructure, laws,..etc

NoSQL (Not only SQL "none relational database"): it is like a blog, you can store the data any way you like (some use a something like table called column oriented, document oriented, graph-base, KeyValue (one of the most common uses of keyValue to call data and store them in JSON file). as an example earth since there is no common structure to be used or a define language that all people speak, the way they build houses, and infrastructure is different but differences between one place and another doesn't effect other places.

I found this image as an example in https://www.upwork.com it explain a the differences between RDBMS and NoSQL in another way.

Useful URLs:
https://www.upwork.com/hiring/data/sql-vs-nosql-databases-whats-the-difference/
https://medium.com/xplenty-blog/the-sql-vs-nosql-difference-mysql-vs-mongodb-32c9980e67b2   (this can be used as an exmaple)
What are the differences between NoSQL and a traditional RDBMS?


Troubleshooting slow connection establishment

Since the connection is fast from sqlcmd, but slow from the .NET Core SqlClient driver, it's most likely this problem is on the client driver side (rather than an AG configuration issue).
You should add MultiSubnetFailover=true to the connection string to speed things up, as it shortens the retry interval. See the docs for details:
Connecting With MultiSubnetFailover

When MultiSubnetFailover=True is specified for a connection, the client retries TCP connection attempts faster than the operating system’s default TCP retransmit intervals. This enables faster reconnection after failover of either an AlwaysOn Availability Group or an AlwaysOn Failover Cluster Instance, and is applicable to both single- and multi-subnet Availability Groups and Failover Cluster Instances.

This isn't needed anymore on .NET Framework 4.6.1 and higher because TransparentNetworkIPResolution is on by default (which causes the driver to attempt connections to all the replicas in parallel, and use the first one that works, rather than trying them one-by-one).  But it looks like that enhancement hasn't made it to .NET Core yet.


Select same data-set at different date ranges in a single query

Your pseudo-SQL is surprising close to the standard SQL clause FILTER, which has only been implemented by Postgres but unfortunately not by MySQL:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: FILTER
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       COUNT(*) FILTER (WHERE database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)) AS "1 Year"
...

The good news is that in MySQL you can use equivalent (and standard SQL as well) CASE expressions:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Standard SQL: COUNT(CASE ...)
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
               THEN 1 END) AS "1 Month"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)
               THEN 1 END) AS "3 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)
               THEN 1 END) AS "6 Months"
       COUNT(CASE WHEN database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
               THEN 1 END) AS "1 Year"
...

or the more compact but non-standard SUM() that uses the implicit conversions of boolean values (TRUE and FALSE) to 1 and 0:

SELECT database_one.users.firstname,
       database_one.users.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)
          ) AS "1 Month"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)
          ) AS "3 Months"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)
          ) AS "6 Months"
       SUM(database_two.announcements.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR)
          ) AS "1 Year"
...

By the way, you could use aliases for the various tables in the FROM clauses so the query becomes shorter and usually more readable.

Also the announcements.id is ambiguous in the SELECT orr ORDER BY clause after a GROUP BY, because it is not in the group by list. Use MIN() or MAX() or some other ORDER BY instead.

The query with aliases - and assuming you choose the SUM() solution from above:

SELECT u.firstname,
       u.lastname,
       COUNT(*) AS "Total",
       -- Non-standard SQL: SUM(boolean expression)
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS "1 Month"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)) AS "3 Months"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 6 MONTH)) AS "6 Months"
       SUM(a.archived_at > DATE_SUB(NOW(), INTERVAL 1 YEAR))  AS "1 Year"
FROM database_two.announcements  AS a
JOIN database_one.prospect_evaluations  AS pe
    ON pe.announcement_id = a.id
JOIN database_one.users  AS u
    ON pe.user_id = u.id
WHERE a.deleted_at IS NULL
  AND a.archived_at IS NOT NULL
  AND a.crmstate <> 'Scartata'
GROUP BY pe.user_id
ORDER BY MAX(a.id) DESC ;


Postgresql how to compare the tables which are present in different database

you could use the postgres_fdw to link the 2 dbs together and execute queries to compare the different tables as if they we're in the same db.

see the documentation on postgres_fdw for examples.
Note, this requires that you will be able to connect from one db to the other remotely. and queries might be resource intensive (as you are basically pulling the data of the remote table over the wire)


Partition constraint not used for joins involving tables partitioned by timestamp

Constraint-based exclusion [CBE] is performed on early stage of query planning, just after the query is parsed, mapped to actual relations and rewritten. (internals, Planner/Optimizer stage)

The planner cannot assume any contents of "sensor_sample" table.

So unless you have values hardcoded in the query, the planner will not exclude "partitions".

I guess what happens with the CTE variant... the planner is restricted because you use TABLESAMPLE and the whole subquery may be treated as volatile even if literals in the subquery are static. (that's just my guess, I'm not expert on planner code)

On the bright side, the index scan with negative result is blazingly fast. (single page scan at most!) so unless you have over 10000 partitions,  I would not bother.

So, to answer your question directly:

You cannot improve this data structure much more.
Regardin index scans - they are cheap;
Regarding sequential scans - they are avoided when possible, as you see on your own examples.


How to create a dynamic comment to an object?

Here is a very simple example of such a function:

create or replace function comment_on(p_type text, p_object text, p_comment text)
  returns text
as
$$
declare
  l_sql     text;
  l_comment text;
begin
  l_comment := replace(p_comment, '${created}', 'created at '||to_char(current_date, 'yyyy-mm-dd'));
  l_sql := 'comment on '||p_type||' '||p_object||' is '||quote_literal(l_comment);
  execute l_sql;
  return l_comment;
end;
$$
language plpgsql;

You can of course add more placeholders to also support e.g. the current user or similar things (or always append this to the comment regardless of the placeholder):

Note that it lacks any error handling or prevention of SQL injection (but that is probably not a problem).

Usage is like this:

create table foo (id integer);
select comment_on('table', 'stuff.foo', '${created} stores bar data');
select comment_on('column', 'stuff.foo.id', 'the id column');

The table foo will then have the following comment:

List of relations
 Schema | Name | Type  | Owner    |  Size     |              Description
--------+------+-------+----------+-----------+---------------------------------------
 public | foo  | table | postgres | 0 bytes   | created at 2015-10-27 stores bar data
(1 row)

And the column will have the following comment:

                           Table "public.foo"
 Column |  Type   | Modifiers | Storage | Stats target |  Description
--------+---------+-----------+---------+--------------+---------------
 id     | integer |           | plain   |              | the id column


It may have been a problem with max_allowed_packet not being set high enough on both the client (i.e. mysqldump) and the server (i.e. Amazon RDS). I set this to 500M on both and that seems to have fixed the problem.

Since InnoDB's information schema tables only give row count estimates, it's hard to tell if my snapshot truly includes everything from RDS. All the tables are there, but the row counts differ. I'll update with a more definitive answer when I have some time to script a more thorough analysis.


Cannot find the object because it does not exist

This issue here is that it doesn't so much matter what the User in DB_B is because Database-level permissions, by default, do not transfer between Databases. There are ways of getting this to work that require little effort, but that are also huge security risks: enabling Cross-Database Ownership Chaining and/or enabling the TRUSTWORTHY Database property. But you don't need either of those. Instead, what you can do is:

Create a Certificate in DB_A
Sign the Stored Procedure in DB_A with that Certificate
Extract the Certificate bytes and Private Key bytes using the built-in functions: CERTENCODED and CERTPRIVATEKEY
Create that same Certificate in DB_B using the extracted Certificate and Private Key bytes
Create a User in DB_B from that Certificate
Add the Certificate-based User in DB_B to the db_owner Database Role.

Please see the following answer of mine on a related question that shows this working between two databases:

Giving Special Permissions to a Stored Procedure in SQL Server


Calling a SQL Server job within another job

Option 1

In Job2, create a job step of type TSQL Command. In the contents, have it run the existing job (sp_start_job)

EXECUTE msdb.dbo.sp_start_job 'Job1'

That is going to run asynchronously, thus after it starts calls the stored procedure, it will return and perform the next step in the job. It will not wait for that started job to complete. If the called job fails, it will not percolate back to the invoking job.

Option 2

Right click on Job1 and script to new query window. Repeat that with Job2 and then thread the job steps from 1 into 2 as/where needed. Far less clicking than recreating the wheel and hopefully less error prone.


By default, SELECT and INSERT access will not be granted. You can REVOKE any SELECT / INSERT perms as follows:
USE MyDatabase;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');
-- you can also filter on o.name to restrict to specific objects
-- and pr.name for specific prinicipals

EXEC sp_executesql @command = @sql;

As Unkush said in the comment, this will only REVOKE any existing access to that explicitly granted. access can also be implictly granted via built the built in db_datareader role and other avenues such as db_owner group and schema ownership.
To harden this further, you can place a deny on the public role (which all users fall into but this won't affect sysadmins) at the database level as follows:
USE MyDatabase;
DENY SELECT ON DATABASE:: MyDatabaseTO [public];

Here is a re-producible test:
First, set up the test db:
USE [master];

/* Create our test db*/
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'PermissionsTest')
BEGIN
    ALTER DATABASE PermissionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PermissionsTest;
END;

CREATE DATABASE PermissionsTest;
GO

USE PermissionsTest;

/* Create some tables*/
CREATE TABLE MyTable1
(
    a INT,
    b INT
);

CREATE TABLE MyTable2
(
    a INT,
    b INT
);

CREATE TABLE MyTable3
(
    a INT,
    b INT
);

CREATE TABLE MyTable4
(
    a INT,
    b INT
);

CREATE TABLE MyTable5
(
    a INT,
    b INT
);

CREATE TABLE MyTable6
(
    a INT,
    b INT
);

/* set up some users */
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin1') DROP LOGIN MyLogin1;
CREATE LOGIN MyLogin1 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser1 FOR LOGIN MyLogin1;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin2') DROP LOGIN MyLogin2;
CREATE LOGIN MyLogin2 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser2 FOR LOGIN MyLogin2;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin3') DROP LOGIN MyLogin3;
CREATE LOGIN MyLogin3 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser3 FOR LOGIN MyLogin3;

IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'MyLogin4') DROP LOGIN MyLogin4;
CREATE LOGIN MyLogin4 WITH PASSWORD = 'MySuperStrongPass', CHECK_POLICY = OFF;
CREATE USER MyUser4 FOR LOGIN MyLogin4;

/* grant some explicit permissions */
GRANT SELECT ON MyTable1 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable2 TO MyUser1,MyUser2;
GRANT SELECT ON MyTable3 TO MyUser1,MyUser2;

/* grant some permissions via a custom role */
CREATE ROLE MyRole;
ALTER ROLE MyRole ADD MEMBER MyUser2;
ALTER ROLE MyRole ADD MEMBER MyUser3;

GRANT SELECT ON MyTable4 TO MyRole;
GRANT SELECT ON MyTable5 TO MyRole;

/* add a user to the standard db role */
ALTER ROLE db_datareader ADD MEMBER MyUser4;

Then we can see how these permissions work:
EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permision)*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should return 0 rows (explicit permission)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (via MyRole membership)*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

Then we can run the dynamic revoke :
USE PermissionsTest;

DECLARE @sql NVARCHAR(MAX) = '';

SELECT  @sql +=
        'REVOKE ' + p.[permission_name] COLLATE DATABASE_DEFAULT + ' ON [' + s.[name] COLLATE DATABASE_DEFAULT  + '].[' + o.[name] COLLATE DATABASE_DEFAULT + '] FROM [' + pr.[name] COLLATE DATABASE_DEFAULT + ']'
FROM    sys.database_permissions p
        JOIN sys.database_principals pr
            ON p.grantee_principal_id = pr.principal_id
        JOIN sys.objects o
            ON o.object_id = p.major_id
        JOIN sys.schemas s
            ON s.schema_id = o.schema_id
WHERE   p.[permission_name] IN ('SELECT','INSERT');

EXEC sp_executesql @command = @sql;

And test (this will leave the db_datareader perms in place)
EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT

We can then either place a deny on the entire database (a deny is the most robust way as there are various other avenues users can get SELECT permissions such as db_datareader, schema ownership, db_owner etc)
DENY SELECT ON DATABASE:: PermissionsTest TO [public];
Alternatively, you can supply a more granular DENY at a schema or table level:
DENY SELECT ON TABLE:: MyTable1 TO [public]
Running the Test again:
EXECUTE AS LOGIN = 'MyLogin1'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin2'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should error*/
REVERT

EXECUTE AS LOGIN = 'MyLogin4'
    SELECT * FROM MyTable1 /* should error*/
    GO
    SELECT * FROM MyTable4 /* should return 0 rows (db_datareader)*/
    GO
    SELECT * FROM MyTable6 /* should return 0 rows (db_datareader)*/
REVERT


function based index as a conditional unique key?

I'm not completely sure that I understand what you mean by "every FK_ID should be unique for that value of OBJ_TYPE".

If you are trying to say that each FK_ID has to be unique within each OBJ_TYPE, then it sounds like you just want a composite index on (OBJ_TYPE, FK_ID).

CREATE UNIQUE INDEX idx_uniq_metadata
   ON metadata( obj_type, fk_id );

If, on the other hand, you are trying to say that if OBJ_TYPE = 1 then FK_ID must be unique but you can have duplicate FK_ID values if the OBJ_TYPE is something else, then you'd want a function-based unique index.

CREATE UNIQUE INDEX idx_uniq_metadata2
   ON metadata( (case when obj_type = 1
                      then fk_id
                      else null
                   end) );


SSIS (SQL Server Information Services) is a first-party (Microsoft) component/platform for Microsoft SQL Server. It isn't an add-on product, you will be given the option to include components when installing SQL server.

Don't think of it as a wrapper for T-SQL, think of it as a place to run T-SQL (and do all kinds of other very useful things).

(SSIS is the successor/replacement for DTS (Data Transformation Services) in SQL 2000 and prior, if you're familiar with that.)

To use SSIS you create a "package", which is a standalone file with a dtsx extension. You create the package in SSDT (SQL Server Data Tools), a customized version of MS Visual Studio (this used to be called BIDS).

Your SSIS package can be executed on any SQL server, by calling it directly from a SQL Agent job. You can also run it on a separate server that has SSIS server components installed, or from a development workstation using either SSDT or the "SSIS Package Execution Utility".

Here's an example of typical use:

The SQL Agent allows you to create scheduled jobs, in which you might run a bit of T-SQL code to perform some database maintenance or something. Paste the T-SQL code into the step detail window, set the job schedule, and you are good to go.

But what if you need to do something more complex than you'd normally attempt in T-SQL? Or something that T-SQL can't do at all?

SSIS allows you to run T-SQL, but you can also do a ton of other stuff:

Easily manage connections to multiple SQL servers (or other ODBC sources), for imports/exports or other operations
Create high-performing data flows and transformations (fastest possible bulk imports, column lookups, robust error handling, including piping failed rows into a different dataset or destination table)
Supports a branching flow, which can launch truly parallel tasks, something that normally isn't possible using T-SQL
Supports other script/code languages for more sophisticated logic
Easy mechanisms for looping through objects
Detailed error logging and/or package progress logging
Lots of other cool stuff.

So you create your more sophisticated "nightly data processing" SSIS package that imports and validates data from 2 other SQL servers, 1 Oracle instance, and 5 text files, then processes the data per your business rules, all using parallel processing to take advantage of your multiple CPU cores. Then call that from your job step instead of your T-SQL code.

Note: installing the Server Component of SSIS isn't strictly necessary, that is a tool to store and manage SSIS packages, or (if desired) run SSIS packages on a dedicated host.

EDIT: SSDT is the tool used to create and edit SSIS packages (thanks, @JonathanFite!). It is basically a customized version of MS Visual Studio. It used to be called BIDS (Business Intelligence Design Studio).


I would be considering the use of table partitioning. You don't mention mysql version or storage engine types. Here is the doc link:

http://dev.mysql.com/doc/refman/5.6/en/partitioning.html

for 5.6


Extract entries from table where specific datetime criteria are satisfied SQL


Does Oracle use rowid to recover from redo log and archives?

From Oracle Database Utilities - section Supplemental Logging

An application that applies reconstructed SQL statements to a
different database must identify the update statement by a set of
columns that uniquely identify the row (for example, a primary key),
not by the ROWID shown in the reconstructed SQL returned by the
V$LOGMNR_CONTENTS view, because the ROWID of one database will be
different and therefore meaningless in another database

While they don't say it in so many words, the "not by ROWID" (in the context of the sentence) strongly suggests that normal redo recovery is by rowid..


SQL 2012 THROW in SP That needs to be run on SQL 2005 and 2012

Please use a dynamic statement:

IF LEFT(@@VERSION, 25) LIKE ('%2012')
BEGIN
    EXEC sp_executesql N';THROW 50013,  @FullMessage , 1','@FullMessage nvarchar(1000)',@FullMessage=@FullErrorMessage
END
ELSE IF LEFT(@@VERSION, 25) LIKE ('%2005')
BEGIN
    RAISERROR(@FullErrorMessage, 11, 1)
END


It seems there is no way to simply use the .bac files fomr inside the .bacpac archive.
I had then to revert to bcp to  create a text file that will be the source of another bcp command to create import the dat in the new DB I want.

To export the data and create the file:

bcp "SELECT * FROM $table WHERE someColumn = someValue" queryout "${table}.csv" -U $user -P $password -S $server -d $dbSource -N -k

And then to reimport it as a new table

bcp [$dbTarget].[dbo].[$table] IN "Fresh${table}.csv" -U $user -P $password -S $server -E -N -k


Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '=' and why?

It would be useful if you can provide your tables definition. However, your error suggests that your are trying to check equality between two columns having different collation.

One solution is to change collation of one of the columns to match the other collation. Check here - Column Level.

Other solution is to use COLLATE clause (I do not know if this works in mariadb, but it should be there).

Personally, I prefer to avoid COLLATE and harmonize string columns as collation mismatch tend to force lots of COLLATEs (maintenance problems).


How secure is mongodb with elastic beanstalk and EC2

If eavesdropping of traffic intra-cloud is a potential issue for you, you really should implement the connection over SSL.
You should also consider running all of your resources in a VPC (virtual private cloud). This allows you to isolate all of your EC2 resources in a private network that is isolated from the rest of the traffic. It also enables you to control where the points of connection to the external world are and how they behave.


A general recommendation by Jonathan (from SQLSkills)

  reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

You can use powershell dbatools Test-DbaMaxMemory to calculate for you. The dbatools has set-DbaMaxMemory to set the max memory for you across all your servers.

I would go with Test-DbaMaxMemory to check the recommendations first and once you are comfortable, use set to set it across all your servers.


Why can't the schema owner create a new table?

If you are using SQL Server 2005 or later then to create an table in a schema you need both CREATE TABLE at the database level and ALTER at the schema level. Ownership of a schema covers the ALTER permission requirement but not the CREATE TABLE one.

CREATE TABLE is also granted by membership in the db_ddladmin fixed database role. This role is for backwards compatibility - do not use it for new security implementations.


How can we implement "A student can enroll in up to 4 units in a semester "

I see at least 2 possibilities.
First - BEFORE INSERT / UPDATE triggers which checks the amount of enrolls per student and forbids insertion / updation if the restriction fails. UPDATE trigger needed for to restrict errorneous data while updating with student ID change.
Second - add enroll_number column which is NOT NULL, ENUM('1','2','3','4') and UNIQUE in the combination with student ID.
I am not considering the option where the table contains 4 fields for 4 separate enrolls - this is denormalized structure and bad practice. Formally the same for a variant when all enrolls are stored in one column of some serialized datatype (JSON, XML).


Use a query to generate a drop type script [in SQL Plus]:

set lines 132
set head off
set pages 0

spool drop_types.sql

select 'drop type '||type_name||';'
from user_types;

spool off

Then execute the script in SQL Plus:

@drop_types.sql


Postgres: how to tell if insert took place

Try this

 CREATE OR REPLACE FUNCTION mysp() RETURNS integer AS
 $BODY$
 DECLARE
 aff_rows numeric;
 BEGIN
     execute 'INSERT INTO mytable (locationname) VALUES (''some text'') WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE locationname=''some text'')';
     GET DIAGNOSTICS aff_rows = ROW_COUNT;
     raise info 'affected rows :: %', aff_rows;
     return aff_rows;
 END
 $BODY$
 LANGUAGE 'plpgsql' ;

Source: https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

(refer section: 39.5.5. Obtaining the Result Status)


Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution

It sounds like the issue is your queries are exhausting the available threads of the new underpowered server, interestingly even with you tuning the MAXDOP setting. Please see this DBA.StackExchange answer for more details. You may need to limit your MAXDOP on certain queries further, or this could be a hint to up the server's provisioning to increase performance more.


Why is my MySQL dump file and my phpMyadmin export file different sizes?

DISCLAIMER : Not a User of PhpMyAdmin

I can explain what mysqldump does. When you run mysqldump, only data is dumped in SQL statements. Indexed are not copied. Indexes get rebuilt when the SQL is loaded into another server.

Here is an example

I have a query I run to show index and data usage.

Here is that query and its output

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 2 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| BLACKHOLE      |             0.000 MB |             0.000 MB |             0.000 MB |
| MEMORY         |             0.000 MB |             0.000 MB |             0.000 MB |
| FEDERATED      |             0.142 MB |             0.000 MB |             0.142 MB |
| MyISAM         |             8.049 MB |             0.368 MB |             8.417 MB |
| InnoDB         |            10.828 MB |             6.766 MB |            17.594 MB |
| Total          |            19.019 MB |             7.134 MB |            26.153 MB |
+----------------+----------------------+----------------------+----------------------+
6 rows in set (0.62 sec)

mysql>

Notice that my PC has 19.019 MB of data and 7.134 MB. mysqldump will only dump the 19.019 MB of data. The indexes are not exported.

If phpmyadmin uses mysqldump, then I only have one possible explanation. mysqldump uses the option --opt by default. Here is is what --opt does

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.

If phpmyadmin summons mysqldump using --skip-opt, then notice --extended-insert is not enabled. With --extended-insert enabled, a batch of rows is inserted using INSERT INTO. Using --skip-opt or --skip-extended-insert will cause each row in every table to have a separate INSERT INTO. Consequently, this would blow up the size of the SQL script. Subsequently, a reload would be longer than usual.