Always ON - What is the difference between the virtual cluster name and Listener

do we really need a Listener when the Failover clustering already handles this concept (IP address) of failover?

A SQL Server Failover Cluster Instance (FCI) will always have a "Network name resource" for clients to connect to the active node.

But with AlwaysOn Availability Groups it is optional, and created through SQL Server.  If you don't create an AG Listener, the Windows cluster will not have any IP address or hostname that moves along with the AG.

In any case an AG Listener is a Windows Server Failover Clustering "Network name resource", and so is confined to a single Cluster.  If you have a Distributed Availability Group which spans multiple Clusters, or a Read-Scale Availability Group which has no cluster, then you can't have an AG Listener.  (Although with a Distributed AG each participating AG can have its own listener).


How to retrieve the Capture Instance for a given table?

Input (hopefully stored procedure parameters):

DECLARE @table1 NVARCHAR(513) = N'dbo.Users',
        @table2 NVARCHAR(513) = N'dbo.DataObjects',
        @ID     INT = 10;

Code:

DECLARE @InstanceName1 NVARCHAR(513),
        @InstanceName2 NVARCHAR(513),
        @Begin_LSN     BINARY(10),
        @End_LSN       BINARY(10);

SELECT @InstanceName1 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table1,1)
    AND s.name = PARSENAME(@table1,2);

SELECT @InstanceName2 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table2,1)
    AND s.name = PARSENAME(@table2,2);

SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn(@InstanceName1),
       @End_LSN   = sys.fn_cdc_get_max_lsn();

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT *
  FROM cdc.fn_cdc_get_all_changes_' + @InstanceName2
  + '(@b, @l, ''ALL'') AS a
  INNER JOIN cdc.fn_cdc_get_all_changes_' + @InstanceName1
  + '(@b, @l, ''ALL'') AS b
  ON a.__$start_lsn = b.__$start_lsn
  WHERE a.ID = @ID;';
  ------^ guessing here

EXEC sp_executesql @sql,
  N'@b BINARY(10), @l BINARY(10), @ID INT',
  @Begin_LSN, @End_LSN, @ID;


Slow Sum/Group By over large amount of rows

I would try with an index like:

 echo, yankee, whiskey_india, mike_romeo

Perhaps you can replace your index_unique with:

 "index_unique" UNIQUE, btree (echo, yankee, whiskey_india, mike_romeo, oscar)

The idea is to put the predicates first in then index, followed by group by columns.


Query Time is doubled because of a column in select

The time difference is probably due to:

The larger amount of data that needs to be returned to the client; and
The FORMAT function is relatively slow

You may be able to avoid using FORMAT by using a T-SQL expression instead, for example:

REPLACE(CONVERT(char(11), dtutcDateTime, 106), SPACE(1), '-') +
SPACE(1) +
CONVERT(char(8), @dt, 108);

-- Returns 25-Mar-2016 23:45:19

See:

FORMAT() is nice and all, but… by Aaron Bertrand
CAST and CONVERT (Transact-SQL) in the product documentation

You should also avoid scalar T-SQL functions in general, for performance reasons. Both plans use a UDF called xPT_ConvertTimeToDDHHMMSS. Scalar T-SQL functions are executed per row, with an overhead similar to that of running a complete query (each time). With 121,861 function calls, that overhead will be adding up. Use an in-line function or T-SQL intrinsics instead.

Also check that the variable @sUrlHeader is not a LOB type (e.g. varchar(max)) if it does not need to be. Using varchar(8000) or below may be significantly faster.

You can check the raw performance of the query (discounting the effect of a client that is slow to accept the results) by running it in SSMS with the Discard Results option set:

...or by selecting the query into a temporary table:

SELECT
  OverspeedReportId = ol.iVehicleMonitoringId,
  AssetId = iAssetId,
  Registration = sReference,
  CategoryId = iCategoryId,
  CategoryName = sCategoryName,
  SiteId = iSiteId,
  SiteName = sSiteName,
  OverspeedDate = FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss'),
  DistanceTraveledSinceLastOverSpeed = DistanceCoveredKM,
  TimeDifferenceDDDHHMMSS = SUBSTRING(dbo.xPT_ConvertTimeToDDHHMMSS(DiffSeconds,'s'),1,12),
  SpeedKM = fSpeed,
  [Address] = sState +', '+ sDistrict +', ' +sPoi +', ' + sRoad +', '+sPoi,
  MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
      +CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
      +'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
      +'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
      +'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
      +'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),
  [Locate] = 'Locate'
INTO #Results -- NEW!
FROM #overspeedLogs ol
LEFT JOIN VehicleGISAddressLog gis
    ON gis.iVehicleMonitoringId = ol.iVehicleMonitoringId
ORDER BY
    ol.iAssetId,
    ol.dtUtcDateTime;

For the most comprehensive collection of performance data, run the queries directly from SQL Sentry Plan Explorer. Click the "Post to SQLPerformance.com" toolbar button to upload the complete session for expert analysis on that site.

If you are unable to do that, consider adding STATISTICS IO output to your question, at least.


what is causing arithmetic overflow in the query below?

I am struggling to find the reason of the arithmetic overflow. why is it happening?

Most likely the metadata is returning some unexpected values that your code cannot handle. For example:

-- Example values returned from sysfiles and FILEPROPERTY
DECLARE
    @size integer = 1,
    @spaceused integer = 10000;

-- The essence of the code in the question
SELECT
    CAST
    (
        100 *
        (
            CAST
            (
                (
                    (@size/128.0 - @spaceused/128.0)/(@size/128.0)
                )
                AS decimal(5,2)
            )
        )
        AS varchar(8)
    ) + '' AS FreeSpacePct;

...returns the error mentioned in the question, because the computed (negative!) value will not fit in decimal(5,2).

There are reasons why size might be reported as much lower than space used, including tempdb file growths, filestream files, bugs in older versions of SQL Server...too many to list. You could/should code defensively against this possibility (and also for offline/defunct files...and so on).

The question is tagged SQL Server 2014, so there's no need to use the deprecated sys.sysfiles view (for backward compatibility with SQL Server 2000):

I might write this query as:

SELECT
    DatabaseName = DB_NAME(),
    [FileName] = DF.name,
    FileType = DF.type_desc,
    SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
    SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
    FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
    FreeSpacePct =  STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, 'SpaceUsed')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN  (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);

Main advantages:

It separates out the calculation steps
Uses float arithmetic to avoid overflows
STR formats the result and does not raise an error on overflow
It does not cause the error in the question to be thrown

A dynamic SQL version (to collect information for all databases):

DECLARE @SQL nvarchar(2000);

SET @SQL = N'
USE ?;

SELECT
    DatabaseName = DB_NAME(),
    [FileName] = DF.name,
    FileType = DF.type_desc,
    SizeMB = STR(DF.size * Factor.PagesToMB, 10, 2),
    SpaceUsedMB = STR(FP.SpaceUsed * Factor.PagesToMB, 10, 2),
    FreeSpaceMB = STR(FS.FreeSpace * Factor.PagesToMB, 10, 2),
    FreeSpacePct =  STR(Factor.ToPct * FS.FreeSpace / DF.size, 7, 4)
FROM sys.database_files AS DF
CROSS APPLY (SELECT FILEPROPERTY(DF.name, ''SpaceUsed'')) AS FP (SpaceUsed)
CROSS APPLY (SELECT DF.size - FP.SpaceUsed) AS FS (FreeSpace)
CROSS JOIN  (SELECT 8e0 / 1024e0, 1e2) AS Factor (PagesToMB, ToPct);
';

DECLARE @Results AS table
(
    DatabaseName sysname NOT NULL,
    [FileName] sysname NOT NULL,
    FileType nvarchar(60) NOT NULL,
    SizeMB char(10) NULL,
    SpaceUsedMB char(10) NULL,
    FreeSpaceMB char(10) NULL,
    FreeSpacePct char(7) NULL
);

INSERT @Results
EXECUTE sys.sp_MSforeachdb
    @command1 = @SQL;

SELECT R.*
FROM @Results AS R
ORDER BY R.DatabaseName; -- Or whatever

Usual caveats about using sp_MSforeachdb.


High WRITELOG wait on Azure SQL Database

WRITELOG is waiting on commit for your transaction's log records to be hardened to disk, and HADR_SYNC_COMMIT is waiting on commit for your transaction's log records to be sent over the network to a secondary replica and hardened to disk.  So they are very, very similar waits.
The both indicate that your application is commiting a lot of transactions, perhaps too many.
And on premium your log file is on a local flash drive with very low latency, so to suffer lots of WRITELOG waits suggests there's something that needs to be fixed in your application.
If you have any processes that run INSERT, UPDATE, or DELETE of single rows in a tight loop, consider wrapping them in an explicit transaction, so you only have to wait for the transaction log to be flushed at the end.
As always Query Store is your friend, and can show you the waits by query, and you can analyze the waits by session too in sys.dm_exec_session_wait_stats to see what parts of your workload are suffering these waits.
You can get a better sense of how much your clients are waiting by comparing the session elapsed time and cpu time to the wait times.  EG
select s.session_id,
       w.wait_type,
       w.wait_time_ms,
       w.signal_wait_time_ms,
       s.total_elapsed_time,
       s.cpu_time,
       w.wait_time_ms/cast(nullif(s.total_elapsed_time,0) as float) wait_percent_of_elapsed
from  sys.dm_exec_sessions s
join sys.dm_exec_session_wait_stats w
  on s.session_id = w.session_id
where w.wait_time_ms > 0
order by wait_percent_of_elapsed desc


SQL Server : Restoring database mirroring once its turned off

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

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

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

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

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

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


Changing listener IP on default TCP endpoint via DDL statement

The clause 'AS TCP' is not valid for this endpoint type.

This is because you are trying to alter a deafult endpoint, and the only options you can change for this are STATE and AUTHORIZATION:
Network Protocols and TDS Endpoints

So you can only stop this endpoint and create other endpoints.


You have two things.  You need two tables to handle such.

A record of Invoices.  Once an invoice is issued, the row(s) that describe must not be modified.  There can be many rows for George, assuming he is issued many invoices.
Current information about George, etc.  This is updated as needed; there is one row for George.

It may be that, when issuing a new Invoice, you need to reach into the other table to update that info.  So, simply plan on always issuing two statements, preferably in the same transaction:

INSERT INTO Invoices ...;
UPDATE Current ...;   -- No harm if nothing actually changes.


Oracle - Need help with RMAN Active Duplication on Windows 32 bit to a 64 bit

You have to initiate the duplicate from the target database to be able to connect to the not mounted target database. I suggest you to downgrade the oracle version on the 64 bit machine. The easiest way to do this will be to install 11.1.0.6.0 version on the 64-bit server into a separate oracle home and perform the duplicate.  You can upgrade the database  after duplicate and switch it to the 11.1.0.7.0 home.

The error you are getting on the 32-bit database seems to be  a connection problem to the database. The target instance is not mounted and you cannot connect to is via SQLnet.

When performing the duplicate form 64 bit system, please make sure that you are using username/password in the connection string to the target database and not the system authentication.

Best regards,
Yuri


Is there a way to get the cardinality between tables, from a MySQL database?

What I understood from your question is that you need to list all the parent and child relationship in your database, for that you can execute below query

SELECT
    ke.referenced_table_name 'parent table',
    ke.referenced_column_name 'parent column',
    ke.table_name 'child table',
    ke.column_name 'child column',
    ke.constraint_name
FROM
    information_schema.KEY_COLUMN_USAGE ke
WHERE
    ke.referenced_table_name IS NOT NULL
        AND table_schema = 'your_db_name'
ORDER BY ke.referenced_table_name;

Here your_db_name is my database.


One SQL Server 2014 AG node down for 4 days - will it restart into AG?

There is no tombstone in Availability Groups. However, one thing you need to consider is the mode your AG is in for the replica that is being taken down for 4 days, and how much log is going to need to be kept for that period.

An AG syncs up other replicas by sending the data written to the log of the database, if you are going to stop a replica for 4 days that could be ALOT of log space needed.

You are basically suspending data movement to that replica by taking it offline (or shutting it down). That is acceptable for a few hours on moderate loads, but when you get into days you need to remove that replica from the AG altogether. This is going to prevent the need to keep all the log data in the database log files for any database in that AG.


MySQL Workbench run SQL script file Character Set encoding issue

If it is the "world" database downloaded from here, it is in latin1 encoding. Use that for importing.

UTF-8 (utf8 or utf8mb4 in MySQL) has become the de facto standard nowadays for operating systems and global communications (except in some countries and specific areas of knowledge), and utf8 is now the default encoding in MySQL (since 5.5). See also http://utf8everywhere.org

latin1 was the 1 byte representation of characters for the western languages. MySQL supports several encodings, you can see them by executing:

SHOW CHARACTER SET;

You asked in a comment:

  Given a *.sql file, how do I unambiguously find out its encoding?

If you used mysqldump to generate it, it usually presets the encoding used at the beginning like this (check the "set names" execution), so that it is imported correctly, without needing to specify it.

In general, it is impossible to know the encoding used of any arbitrary text file, but you can try to auto-detect it with tools like the standard GNU file tool on Amazon Linux file -i <filename>. There should be tons of tools like that, even more specialised.


Choosing MyISAM over InnoDB for these project requirements; and long term options

InnoDB and MyISAM each have their strengths and weaknesses.

May 03, 2012 : Which is faster, InnoDB or MyISAM?
Sep 20, 2011 : Best of MyISAM and InnoDB

If you have enough RAM, I would choose InnoDB because it caches data and index pages in the Buffer Pool. MyISAM only caches index pages in the Key Cache.

MyISAM tables experience full table locks for each INSERT, UPDATE, and DELETE. MyISAM tables always require disk access for data.

InnoDB tables always incur disk I/O in the following areas:

Double Write Buffer : Changes are posted in ibdata1 to avoid OS caching
Insert Buffer : Changes to Secondary (non-Unique) Indexes as posted in ibdata1
Data and Indexes

With innodb_file_per_table = 0, changes are written to ibdata1
With innodb_file_per_table = 1, changes are written to .ibd tablespace file. Read I/O against ibdata1 still necessary to crosscheck table metadata

SUMMARY

In an environment with the following:

heavy writes
heavy reads
tons of RAM
heavy connections

I would always choose InnoDB. Please check out my other post about InnoDB over MyISAM : When to switch from MyISAM to InnoDB?

When would I every choose MyISAM?

Under the following scenario

Using MySQL Replication
Master with all InnoDB
Slave with all tables converted to MyISAM
ALTER TABLE ... ROW_FORMAT=Fixed for all tables on the Slave

Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT-Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR is treated as CHAR this shortening access time for data retrieval.

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

On the other hand, InnoDB has to interact with multiple files (ibdata1, serveral read/write threads upon the .ibd of the InnoDB table).


Is there a better way to exclude all "not actively created by a human" indexes than what I'm doing?

This will return a list of indexes that are used to implement a constraint:
SELECT conindid::regclass
FROM pg_constraint
WHERE contype IN ('p', 'u', 'x');

Exclude these indexes from your query result, and you have thr list you desire.


What is the proper way to validate that a many-to-many relationship will not surpass a certain limit?

SELECT ... FOR UPDATE will not work in this case. It still requires SERIALIZABLE isolation level because what you need to do is to prevent other sessions from inserting rows with the same groupId. And to do this you need to lock a key (what actually SERIALIZABLE level does). What you can try to do?:

check and adjust your indexes (to not lock more rows than you actually need)
revise your code to make your transaction as small as possible
try to emulate locks using separate table (with the list of groupids and locked flag) and updating a row for required groupid before start your transaction and after the end of it.


SQL Server 2008 R2 : multiple files and filegroup

1: yes.

2: depends;) The query execution is still driven by the optimizer. It wont do paralellization for small result sets.

3: Ah - what would that be good given that you are still reading one backup file ;)?

4: Not running the files on one underlying SAN and thus improoving your IO budget?

The most brutal thing I have ever seen like that had nearly 30 files all on separate SAN volumes (count of hard discs going close to 200) and it was done as every LUN had a queue limit in the driver of 255 outstanding requests, which the SAN (with a 32 gigabyte cache) was just not caring about ;) THat thing was pulling in nearly 1.5 gigabyte per second over multiple fiber connections.


Does disk errors on a mongo primary instance trigger election process?

It depends on exactly what you mean when you talk about a disk read / write error. If the error is that the data db volume is read-only or unmounted (e.g. An EBS is removed from a running EC2 instance) from a MongoDB primary will not trigger an election process as long as the mongod primary remains running and is exchanging heartbeats, then no failover will occur. The primary will not accept writes but it will not step down in this situation.

The heartbeat checks connectivity between members within the replica set and you can look at the code for it here. It is sent every 2 seconds and will timeout after 10 seconds.


There are various methods  of achieving this ,one of them as suggested by Yasir.
Please follow the below steps:

Close toad/sqlplus
Create a folder TNS_ADMIN
Add tnsnames.ora in TNS_ADMIN
Add TNS_ADMIN in enviornment variable.
Open Toad/Sqlplus and login

I think this will be very clear ,cheer


Only solution so far is via bash script:
#!/bin/bash
user=myuser
host=127.0.0.1
database=mydb
batch_size=10000

elements=$( psql -X -A -U $user -h $host -d $database -t -c "SELECT COUNT (*) FROM vacuum_l where deleted = false" )
echo "los to unlink:"
echo $elements
echo
num_batches=$((elements / batch_size))
num_batches=$((num_batches + 1))
echo "Batches:"
echo $num_batches
echo
for ((i = 1; i <= $num_batches; i++)); do
    echo "Batch "$i
    psql -U $user -h $host -d $database -c "UPDATE vacuum_l SET deleted = true, lo = lo_unlink(lo) WHERE lo IN (SELECT lo FROM vacuum_l where deleted=false limit $batch_size )"
done


CU Update to Sql Server 2014 hangs for 7 hours

Changing the reg key HKLM\System\CurrentControlSet\Services\LanmanServer\Parameters\AutoShareServer to 1. restart the server service. Validate by navigating to \servername\C$. If you can't navigate there, the Admin$ share is not available.  In my case something is causing the key to get reverted very quickly.  I'll get with the network admin to suspend this action so the Admin$ share stays available.


I find it frustrating that the same queries don't work in different DBMS.

Yes. I find it frustrating that the headlights and wiper controls are not always in the same place in different cars. Since I can't control it, I learn to live with it. I generally stick to certain car brands but the location of these things is not anywhere near the top of the list of primary decision criteria. Similarly, I don't pick a database platform because of what percentage of the standard they cover or how many queries work the same as in Oracle and MySQL.

  Example, some DBMS (such as Microsoft SQL Server and MySQL) seems to support the information_schema table, while others (such as SQLite) don't.

INFORMATION_SCHEMA was an interesting choice. Yes, some platforms have opted not to support that standard at all. Others have covered the bare minimum. I make a pretty strong argument against using INFORMATION_SCHEMA in SQL Server, for example:

The case against INFORMATION_SCHEMA views

  Some databases such as MySQL allows you to do SHOW TABLES, etc.

Well, yes, some databases are more notorious than others for just making up stuff that seems to make sense to the developer knee-deep in code at the time (LIMIT and their perverse GROUP BY forgiveness are other examples).

  Why are different DBMS so incompatible and deviant?

I'm sure some do things like SHOW TABLES because that is considered easier than SELECT name, other cols FROM sys.tables;. In fact I would bet that that's the problem with the majority of the world's codebase, RDBMS included; people tend to do what's easier rather than what's right.

But I'm not sure it's a problem that the different platforms are incompatible and "deviant" as you call them.

  Why don't they follow the SQL standard?

Covering the entire standard is hard. Covering only the standard is even harder. Vendors develop extensions to the standard, and proprietary features not (yet) covered by the standard, to give users more powerful platforms. Imagine if we sat around waiting for the next iteration of the standard before we could develop anything? Imagine if all of the platforms covered the full standard and nothing but the standard? Imagine if all of the platforms only implemented any new functionality that they all agreed to develop the same and release at the same time? What would be the point of having different platforms? They'd all be the same, and the incompatibility would no longer be an issue because you could switch freely between platforms without risk - but then why would you, if they're all the same? See the problem you're "solving" - it just creates a different problem.

Take indexes for example. The standard doesn't care about physical implementation, and some vendors have made decisions to implement all kinds of beneficial index variations and physical storage mechanisms to better their platforms - SQL Server has filtered indexes, include columns, XML indexes, full-text indexes, in-memory tables, columnstore, etc. etc. None of which you'll read a lick of in the standard. So as they develop these things, how are they supposed to make it compatible with anything along the same lines that Oracle may or may not be developing or planning? Postgres has gone on and made the decision that elaborate JSON support is far more important than, say, performance gains from parallel processing. That's fine, and totally within their right. That doesn't mean I want SQL Server to do the same thing.

Normal ad hoc CRUD stuff should work identically for basic vanilla tables across platforms (though even there, MySQL has done some funky things like use backticks around table names by default, which need to be stripped out if trying to port the code to any other platform). As soon as you start doing anything more exotic outside of standard storage and retrieval, the standard throws its arms in the air. You want Window functions? Okay, you need your vendor to roll up their sleeves and implement them, because most platforms don't have them, and they take a lot of work; even SQL Server is not yet complete, but they're way ahead of the other guys. And they have plenty of other language features that I'm very happy are there, in spite of them not being in the standard.

  Which are good at adhering to the SQL standard?
  Which are notoriously bad at adhering to the SQL standard?

I don't know enough about the full standard and all of the platforms to comment. But I will suggest this: If this is the reason you're choosing one platform or another, stop what you're doing right now, and ask yourself why this is so important. I will also suggest that SQL Server has gotten much better about adhering to the standard and implementing extensions where they are beneficial (a look back in history at things like timestamp and, yes, top shows that they used to care a lot less). Some other platforms give a big middle finger to the standard, and continue to do so.


How do you get the default compatibility level in Azure SQL Database from T-SQL?

I couldn't find anything specifically documented for this, but:
SELECT SERVERPROPERTY('ResourceVersion');

...currently returns "15.00.2000" for Azure SQL Database, which may be useful to you.
I had thought you might be able to query:
SELECT H.[name]
FROM sys.dm_exec_valid_use_hints AS H
WHERE H.name LIKE N'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_%';

...but that returns:

name

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160

...which are all valid hints, but 150 is the maximum you can set database compatibility to at the time of writing.


How to report stats on restore via sqlcmd?

UPDATE: 2018-12-20 - I completely re-wrote my answer to use Powershell.  Perhaps this solution will work for you.

I believe SQLCMD was rewritten for 2012 in such a way that print statements and even raiserror messages issued during a run via SQLCMD never displayed until the entire job finished.

If using Powershell is an option, you can use the Invoke-SqlCmd cmdlet to run your scripts.  To see the output while the script is running, include the -Verbose parameter.  (you'll also want to include something like -QueryTimeout 0 to keep the script from timing out at the default 30 seconds)

Here is a quick way to test this functionality.

Create the following stored procedure which issues raiserror statements every 10 seconds (for 50 iterations) and displays the current timestamp.  Under normal runs of SQLCMD, you wouldn't see any output until the procedure completely finishes.  However, using INVOKE-SQLCMD and the -Verbose parameter, you will see the output as the script runs

DROP PROCEDURE IF EXISTS [scutility].[SqlCmdTestOutput]
GO
CREATE PROCEDURE [scutility].[SqlCmdTestOutput]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @count INT = 50
    DECLARE @msg VARCHAR(8000)

    WHILE @count > 0
    BEGIN
        SET @msg = convert(VARCHAR(50), sysdatetime())

        RAISERROR (@msg,0,1) WITH NOWAIT

        WAITFOR DELAY '00:00:10'

        SET @count -= 1
    END
END

--The powershell command
powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"

--output
C:\>powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"
VERBOSE: 2018-12-20 08:12:24.3689374
VERBOSE: 2018-12-20 08:12:34.3696751
VERBOSE: 2018-12-20 08:12:44.3704152
VERBOSE: 2018-12-20 08:12:54.3710561
VERBOSE: 2018-12-20 08:13:04.3717697

You can also eliminate the string 'VERBOSE:' from the output by redirecting the -Verbose output and using Write-Host.

C:\>powershell.exe  -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose 4>&1 -QueryTimeout 0 | % {Write-Host ($_.Message)}"
2018-12-20 08:42:59.9874559
2018-12-20 08:43:09.9891544
2018-12-20 08:43:19.9898284
2018-12-20 08:43:29.9905699
2018-12-20 08:43:39.9912415
2018-12-20 08:43:49.9919723
2018-12-20 08:43:59.9936545
2018-12-20 08:44:09.9943466
2018-12-20 08:44:19.9960623
2018-12-20 08:44:29.9967689


Migrating 300 GB mysql database from one server to another cleanly with minimum downtime

This solution can be done with mysqldump but with a bit of a risk

For the sake of this example, suppose you have the following:

The database name is mydb
The source DB server's private IP is 10.20.30.40
The target DB server's private IP is 10.20.30.50
User is root on both source and target DB Servers
Password is whatever on both source and target DB Servers
Binary Logs on both servers are named mysql-bin

Here are your steps

STEP 01 : Create Replication User

On the Live Master, run the following

CREATE USER repluser@'%' IDENTIFIED BY 'replpass';
GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO repluser@'%';

STEP 02 : Set Up Replication

On the target DB Server, run the following:

If you have GTID enabled on source and target servers, do this:

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repluser',
master_password='replpass',
master_auto_position=1;

If you do not have GTID enabled on source and target servers, do this:

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repluser',
master_password='replpass',
master_log_file='mysql-bin.000001',
master_log_pos=4;

STEP 03 : Create a script to perform live mysqldump and load

Create a shell script called live_dump_and_load.sh

Put the following lines in it

MYSQL_USER=root
MYSQL_PASS=whatever
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB_TO_DUMP=mydb
MYSQLDUMP_OPTIONS="--routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single_transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} -B ${DB_TO_DUMP}"

date > live_dump_and_load.runlog
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} | mysql -h10.20.30.50 ${MYSQL_CONN}
date >> live_dump_and_load.runlog

STEP 04 : Run the shell script

chmod +x live_dump_and_load.sh
nohup ./live_dump_and_load.sh &

STEP 05 : Monitor the run log

watch cat live_dump_and_load.runlog

STEP 06 : Start Replication

When the dump is loaded onto the target server, go to the target server and run

START SLAVE;

GIVE IT A TRY !!!


Insert new value into table if it does not exist

You have a few options.

You can handle this programmatically, in the code that sends the supplier info to the database.

You can create a stored procedure that finds payment methods in the supplier info that do not exist in the payment method table, and inserts them.

You can create a BEFORE trigger on supplier info, check if the payment method exists, and add it.

That said, I would advise against automatically creating something as critical as payment methods based on user input (or even on imported files).

First, the supplier info presumably will not have more than the payment method name. You may create a record, but critical info like payment type (credit card vs. checking account mean different data that you need to collect) will be missing.

Second, something as simple as a typo will create a new, invalid payment method in your system, instead of properly forcing the invalid data to be corrected before being added to the database.

Third, you would be allowing potential maliciously bad data to be created in your system. Without proper controls, you might allow someone to actually place valid fillable orders, and charge them nothing.

Screens that allow data to be added to your core look-up tables should be tightly controlled, only accessible to users who know what to add, and how.


How to insert fields to different tables at once?

You could write an (AFTER?) INSERT trigger, like this:

CREATE TRIGGER temp_inserts_trg AFTER INSERT ON temp
    FOR EACH ROW BEGIN
        INSERT INTO customer VALUES(NEW.ID, NEW.Email, NEW.Name);
        INSERT INTO payment...
        ...
    END;

Check the MySQL CREATE TRIGGER documentation for more info.


This is what the documentation says about the topic:

  wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal removes all logging except the information required to recover from a crash or immediate shutdown. Finally, logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.

From the text it's not overly clear, but logical is the highest level of all, meaning it includes everything the replica level does.  That means you can safely set wal_level to logical, it won't break your existing replication.


tSQL Stored Procedure with CTE; CASE statement in WHERE slowing run

Two big performance problems:

Your CSV splitter function is a major performance killer. Swap it out for Jeff Moden's DelimitedSplit8k function. You can read all about it here. Or better yet, swap it out for the CLR function or a table-valued parameter if you are on 2008+. Check out Aaron Bertrand's performance tests for the various CSV splitter functions. CLR is the winner overall.
The table variable can be a big performance killer even if it only has 1 row. Switch it to a temporary table and add a clustered index to it.

In your now provided execution plans, the function shows a 0% cost, but that is not the case. The function cost is higher, but you won't see the actual cost in the execution plan unless it's an inline table valued function.

  Unfortunately, although I once had a runtime of 1 sec, by changing the
  SP and then back again, it takes 6 seconds again.

That smells of parameter sniffing.


Select all rows from a table where a value is NOT found?

So if I'm understanding the schema right, this should work with a LEFT OUTER JOIN with a subquery:

SELECT q.*, ua.UserId, ua.Answer
FROM ve_survey_questions q
LEFT JOIN (
     SELECT u.*, a.QuestionId, a.Answer
     FROM ve_survey_useranswers u
     INNER JOIN ve_survey_answers a ON u.AnswerId = a.AnswerID
     WHERE u.UserId = @userid
     ) AS ua
ON q.Qid = ua.QuestionId
WHERE ua.QuestionId IS NULL
ORDER BY q.Qid

So the subquery returns a row for each answered question for the given user (along with the answer they selected), then we use an outer join to see what questions are still unanswered.

You can eliminate the WHERE ua.QuestionId IS NULL if you instead want the full question list for that user, along with their answer, if there is one.


Copy one database to another (existing) database on the same server

I suspected multiple backups might exist in this backup file.  If you run:

RESTORE HEADERONLY from disk = [tempfile]

this will show all the backups that are in the file.  You are not restoring the latest backup, use FILE=X to restore the appropriate backup.


OPTION FORCE ORDER improves performance until rows are deleted

Is this normal for a forced join order to make the query estimates to
  be completely inaccurate (and thus query times unpredictable)?

The use of FORCE ORDER isn't making estimates inaccurate, the deletion of rows did. Forcing an update of statistics on the table may improve the estimation accuracy.

  Should I just expect that I'll have to either accept sub-optimal query
  performance, or watch it like a hawk and frequently manually edit
  query hints? Or maybe hint every join as well? .3s to 2s is a big hit
  to take.

Preferable would be to ensure the optimiser is given the information it needs to generate the best plan, without using the FORCE ORDER hint. By doing so, it should cope better with  changes to the underlying data distribution without requiring manual intervention. That said, if the nature of the data is such that cardinality could vary significantly hour by hour or day by hour, consider using a plan guide to ensure the plan is fixed.

  Is it obvious why the optimizer blew up after deleting rows? For
  example, "yes, it took a sample scan, and because I archived most of
  the rows earlier in the data history the sample yielded sparse
  results, so it underestimated the need for a sorted hash operation"?

You didn't mention the row counts in the problem tables but it's likely that the deletions either:

didn't remove enough rows to to trigger a statistics update. This should occur when 20% of rows have been modified but there is the option to use trace flag 2371 to enable a dynamic threshold.
did trigger a statistics update but the sample gathered wasn't representative. Correct this by running a manual update WITH FULLSCAN.

You could also be running into good old fashioned parameter sniffing problems, for which there are myriad options to work around. WITH RECOMPILE might be an expensive option to specify with a query this large but it's worth investigating at both procedure and statement level.


There are distributed locks used in a sharded environment.  The balancer takes a lock (only one migration is active at a time), the shards will take out meta data locks when doing splits also.  Those live in the config.locks collection.

As for the lock pinger, the config.lockpings collection keeps track of the active components in the cluster, so it is an informational collection.  The lock pinger is what populates this collection and you have pasted what looks to be the result of a successful ping.

Note: please do not use these collections for anything in your application or elsewhere.  These collections (as noted on the linked pages) are considered internal only and can (and will) be changed/removed/updated without notice.


query using the where clause if variables are not null

Here's the code:

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
    @filterCol NVARCHAR(20) = NULL, --<<<<
    @filterValue NVARCHAR(40) = NULL, --<<<<
    @PageNumber INT,
    @PageSize   INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    '
    IF @filterCol IS NOT NULL
    BEGIN
        SET @SQL = @SQL + 'WHERE       [' + @filterCol + '] LIKE ''%' + @filterValue + '%'''
    END

    SET @SQL = @SQL + '
    ORDER BY    id
    OFFSET      ' + CAST((@PageSize * (@PageNumber - 1)) AS VARCHAR(50)) + ' ROWS
    FETCH NEXT  ' + CAST(@PageSize AS VARCHAR(25)) + ' ROWS ONLY OPTION (RECOMPILE);'

    EXEC sp_executesql @SQL

    select count(1) as totalCount from [dbo].tblEmployees5m
END

As has been mentioned, dynamic SQL is vulnerable to SQL Injection, so take care when utilising dynamic SQL that you're sufficiently protected against these attacks.

SQL Injection Protection: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet


Is WAL Database log on separate disk good idea on RAID10?

You asked for pros and cons, the con that few people mention when giving the standard advice to separate out the logs is that it will slow your reads because you now have fewer spindles to dedicate to reads.  Which way you go depends on the ratio of reads to writes in your database.  If it is 99% read (say a web server scenario) then you may end up not gaining anything by separating out your logs.

The other possible gotcha that is rarely mentioned is that putting your logs on a separate spindle only gives you sequential writes if you only put a single databases logs on that spindle.  If you have a server with 10 active databases then putting all of their logs on the same disk does not give you sequential writes, they will be spread about on the different log files.  Then again putting them on 10 separate mirrored drives means you need to dedicate 20 drives to logs which also may not be possible.  A good solution in this case is to use SSD storage for your log files which performs MUCH better on random writes when you find your environment just does not have sequential writes.


Multiple product attribute inventory database design

You have basically created a basic Key Value Store. See here for more details on that. You will need to make use of the PIVOT command to get the result you need. Check here for that.


Best practice for MySQL CASE with multiple WHEN conditions vs a single OR condition

The performance difference between the two suggestions is insignificant.
As a rule of thumb, fetching a row is far more costly than any expressions that need to be evaluated for that row.
Do whichever is easier for you.
The expressions in those examples will be evaluated before it starts to perform the query.  Perhaps your real query is more involved than that?  In particular, if 2 > 0 is, instead, some complex subquery, then I need to reconsider my answer.
These may apply to MySQL:

"short circuit" -- But for simple expressions, you would be hard put to measure the performance difference.
When possible MATCH .. AGAINST (FULLTEXT) is performed first.  (But this applies to WHERE, not CASE.)
User functions can be defined as DETERMINISTIC or non.  This should impact the evaluation.  (But I don't have proof of such.)
@variables are probably soon to be deprecated due to evaluation issues.


Database denormalization: multiple rows into single column. How bad is it?

It depends on how the views are going to be queried.  If the purpose is to do something like

SELECT OrderID from dbo.vw_Orders WHERE Traveler like '%Jon Appleseed%'

then you should NOT do this.  If you are just using the view to return a list that will be parsed by your application, then it may not be so bad.


MongoDB Zone Sharding without min & max key

Your collections need "key" (like 'zone') what is set when documents need to be "zoned". There will be only so many different key values than you have shards. When user wants to be zoned, you update all users documents (at all sharded collections) with that "key". Those collections are sharded that zone key. You choose one of those shards to be "default" shard so, all those documents what don't have zone key set, goes there. This default shard have minkey and you choose some other shard have that maxkey.

Not the best solution, but...


Set based solution to group multiple rows within a sequence

Not the prettiest query I've ever written, but it seems to get the job done without any recursion. (I've made the assumption that the output needs to be grouped by GroupID and Code.)

WITH starting AS ( --Find each "starting" entry
    SELECT
        d.GroupID,
        d.Code,
        d.StartSeq,
        d.EndSeq,
        d.StartDayNo,
        d.EndDayNo
    FROM SampleData d
        LEFT OUTER JOIN SampleData prev
            ON d.Code = prev.Code
            AND d.GroupID = prev.GroupID
            AND d.StartSeq = prev.EndSeq + 1
    WHERE prev.Code IS NULL
)
SELECT
    s.GroupID,
    s.Code,
    s.StartSeq,
    ISNULL(e.EndSeq, s.EndSeq) AS EndSeq,
    s.StartDayNo,
    ISNULL(e.EndDayNo, s.EndDayNo) AS EndDayNo
FROM starting s
    OUTER APPLY ( --Find the next highest "starting" entry
        SELECT TOP 1
            s2.*
        FROM starting s2
        WHERE s2.Code = s.Code
            AND s2.GroupID = s.GroupID
            AND s2.StartSeq > s.StartSeq
        ORDER BY s2.StartSeq ASC
    ) s2
    OUTER APPLY ( --Find the highest entry less than the next highest "starting" entry
        SELECT TOP 1 *
        FROM SampleData e
        WHERE e.Code = s.Code
            AND e.GroupID = s.GroupID
            AND (e.EndSeq < s2.StartSeq OR s2.Code IS NULL)
        ORDER BY e.EndSeq DESC
    ) e
ORDER BY s.Code, s.StartSeq


The color printing extension for Visual Studio 2010 works just fine for adding the color print ability to SSMS 2012. Download the VSIX package, and unzip into it's own directory in your to your SSMS extensions folder (C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Extensions most likely). You'll then want to modify the extension.vsixmanifest file -- in the <SupportedProducts> section add

      <IsolatedShell Version="1.0">ssms</IsolatedShell>

After that, restart SSMS, and you'll have the ability to print in color, even if it doesn't look like it.


Statement Based Replication still giving duplicate Entry Error

Replication behaved exactly how it is supposed to be- you wrote the same row (as in, it had the same id on both masters, even if data was different), but a multi-master setup is not a tight cluster- it just redos what on the replicas the same things that happened on the master, up to the ids. It does not handle conflict resolution automatically.

I understand that you expected, with Statement Based Replication, to replicate the actual SQL (vs ROW, which replicates the previous old and new rows), so it gets a new, proper ID. While this is exactly what happens (what it is written to the binary log), it also stores internally the auto_increment id. This is because if later you run a DELETE or an UPDATE, or an INSERT... SELECT it would affect the wrong rows.

Generally, multi-source replication does not behave like a tight cluster- you want to use it just to consolidate data from different tables, or to share tables among several different replica sets. If you want automatic conflict resolution and being able to write to any server on a set, you want clustering technologies such as Galera or InnoDB Cluster which would make your writes either fail or work in a coordinated way when inserted the same data on several hosts.

Technically, it would be possible to making work the setup you want- there is this option called auto_increment_increment (combined with auto_increment_offset) that allows to "merge" inserts from several masters by, for example, creating only odd auto_increment values on one host and even on the other so you configure:

MASTER 1:
auto_increment_increment=2
auto_increment_offset=0

MASTER 2:
auto_increment_increment=2
auto_increment_offset=1

so the autoincs don't collide. There are other multiple options, like dropping or renaming the auto_increment column on the replica or creating triggers on it, both while using STATEMENT based replication so they generate the right code.

However, these 2 options are quite fragile (e.g. auto_increment_increment only works for inserts, not for updates or deletes), and tend to make replication break due to side effects- plus when it breaks it is difficult to fix it due to this custom setup, so I don't recommend it normally. I would suggest to avoid setting up replication with different masters and replicas unless it is in a very controlled environment (separate tables or dbs) and try clustering technologies instead. The actual decision would depend on what you really want to achieve in the end, as it is not fully clear on your question- so I am just explained why it didn't work and how people make it work normally.


MongoDB / WiredTiger: reduce storage size after deleting properties from documents

Just to clarify, please be careful about using repairDatabase on a replica set node. repairDatabase is meant to be used to salvage readable data i.e. after a disk corruption, so it can remove unreadable data and let MongoDB start in the face of disk corruption.

If this node has an undetected disk corruption and you run repairDatabase on it, this could lead into that particular node having a different data content vs. the other node as a result of repairDatabase. Since MongoDB assumes all nodes in a replica set contains identical data, this could lead to crashes and hard to diagnose problems. Due to its nature, this issue could stay dormant for a long time, and suddenly manifest itself with a vengeance, seemingly without any apparent reason.

WiredTiger will eventually reuse the empty spaces with new data, and the periodic checkpointing that WiredTiger does could potentially release space to the OS without any intervention on your part.

If you really need to give space back to the OS, then an initial sync is the safest choice if you have a replica set. On a standalone, dump/restore will achieve the same result. Otherwise, compact is the safer choice vs. repairDatabase. Please backup your data before doing any of these, since in my opinion this would qualify as a major maintenance.


What tables are affected by Change Data Capture

First you have to enable CDC at database level.
USE database_name
GO
EXEC sys.sp_cdc_enable_db
GO

Then you have to enable CDC at table level
USE database_name
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'table1',
@role_name     = NULL

Below query will tell you what tables have cdc enabled
SELECT [name], is_tracked_by_cdc
FROM sys.tables

update:

What I'm trying to find is for a given Lsn, which cdc tables have a record

I guess this is not relevant, as when enabling a CDC, you will have

__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask

The __$start_lsn and __$end_lsn will correspond to the table itself that you have enabled CDC ON.
I dont see a need where you have to do a table search based on LSN to find out the table name - unless I am not comprehending your question.
You can even look up in the system tables

cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping => Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.
dbo.systranschemas

Read up : An Introduction to SQL Server 2008 Change Data Capture by Brad McGehee and Introduction to Change Data Capture (CDC) in SQL Server 2008 by Pinal Dave.


MySQL error 1674 even when binlog is set to ROW

ALTER TABLE statements are still replicated as statements. Even with binlog_format=ROW this will be requiring the existing table populate the existing rows with an AccessCode.

I recommend:

ALTER TABLE Account ADD AccessCode binary(16) NOT NULL;
UPDATE Account SET AccessCode = UUID_TO_BIN(UUID());

This manual page says that non-determinstic default functions on columns may produce a warning or error (CURRENT_TIMESTAMP was an exception).

I'm not sure you can create this, even on the master after executing SET SESSION sql_log_bin=0; ALTER TABLE Account Alter AccessCode SET DEFAULT (UUID_TO_BIN(UUID())), I tried MySQL-8.0 however your version may differ.


how to insert big data in mariadb with execution time less than max_statement_time value

Don't change the GLOBAL setting; change the SESSION setting.


SQL Monitoring will be triggered if one of the following conditions is present:

query is taking more than 5 seconds of CPU time
or
query is taking more than 5 seconds waiting on I/O
or
query is running in parallel
or
/*+ MONITOR */ hint was present in the query

In other words, if your query is a very fast one, doesn't run in parallel and doesn't have MONITOR hint, it won't be monitored.


Does MySQL support querying for the current running transaction id

Starting with MySQL 5.7, you can see the current transactions in the performance schema.

See http://dev.mysql.com/doc/refman/5.7/en/events-transactions-current-table.html

Now, to find out the THREAD_ID of the current connection, use

SELECT THREAD_ID FROM performance_schema.threads
  WHERE PROCESSLIST_ID = CONNECTION_ID()

See http://dev.mysql.com/doc/refman/5.7/en/threads-table.html


How to create an Oracle Database Link when the password has special characters

Use quotation marks:

CREATE DATABASE LINK my_link
CONNECT TO daniel identified by "*password"
USING 'hostname:port/servicename';


Oracle number precision datatype performance impact

The precision and scale of a number column is a matter for business rules and data modelling. If a number can have values in the trillions don't declare it as number(6).The general cases seem to be

leave number columns without further definition - number
enforce integer columns e.g. number(20,0) or less commonly integer
for cases where it really matters (e.g. monetary values) specify scale and precision e.g. number(15,2)

Then there are the specialized edge cases for binary_float and binary_double. If you are doing heavy-duty calculations there are definite performance benefits from choosing these data types (at the price of losing some exactitude). But if you're using regular number columns just choose the precision and scale you need for the business rules, and don't sweat on performance.

  if i define as a number without precision, how it works? what would be default precision?

If we don't define number further there is no default precision. We can store any number from 0.0000000000000000000000000000000000001 to 99999999999999999999999999999999999999.


You don't need to disable the STOPWORDS / STOPLIST feature completely, rather you can just remove that specific word "8" from it if you want the search to always be able to match it.
Microsoft's Docs ALTER FULLTEXT STOPLIST explain how to remove words from the default STOPLIST. And for general information on managing STOPLISTS you can read there docs Configure and Manage Stopwords and Stoplists for Full-Text Search as well.


Is there possible chache query or save it in memory in MySql? Mayby Should I use something other?

Let's try the Query cache first.  Add these to my.cnf, then restart mysql:

query_cache_type = ON
query_cache_size = 50M

If that does not work adequately, we can discuss alternatives.


Is it possible for a weak entity, already related to a strong entity, to get related to another weak entity in an ER diagram?

Depending on how much you briefed your problem statement, it can be stated that:

Trainer is a strong entity (it has an ID)

Neither Client nor Membership Plan seem to be weak or strong entities.
They both could be considered strong entities provided that a primary key is defined on them*; e.g. Client.(Name, Phone) and Membership Plan.("Beginning Date", Duration)

In your Chen diagram, Meets entity should have both ends total participation, but there's an arrow between Trainer and Meets?
Other than that, it seems to be alright.

* A strong entity is defined as having a key that does not depend on any other key; a weak entity as as having a key that depends on a foreign key. E.g. Order has a primary key order_id which is independent, and that makes it a strong entity, whilst Order_Detail has a primary key order_detail_id and unique key order_id, line_no, which makes it a weak entity since order_detail_id does not provide a meaningful independent ID, and the unique key depends on Order table. See weak entity entry in Wikipedia.
They're independent from other entities, so they are candidates for being considered strong entities provided that they have a primary key, which is not known if they have and is not clear if they could in the definition.


How to rollback transaction if row changed?

Not sure why you are not updating directly with additional predicates in the where clause, but if that is not possible you can select for update as in:

SELECT * FROM tbl WHERE id = 1 FOR UPDATE

This will lock the selected row and prevent updates of it while you do your validation.


Key-range RangeI-N lock compatibility in SQL Server

My understanding is that this type of lock is during the process of
  identifying the range where the newly inserted key should be placed
  (I'm assuming that's what 'test the range' means). After this happens
  the lock is released, the new key gets inserted and an X lock is
  placed on it.

RangeI-N lock is requested to test that insertion into key range will not interfere with another transaction that may potentially do some serializable operation incompatible with insertion (like reading for example).

During insertion of record into index database engine locates page, where key being inserted should be placed. Page and key locks are not used at this stage yet, only latches. Once page is located, engine starts acquiring locks necessary for performing insertion of record to the page. These are IX lock for the page located, then RangeI-N lock for the key next to key being inserted. Upon acquiring RangeI-N is not held, and next X lock for the key being inserted is requested. After necessary locks acquired, insertion of the record proceeds.

Say we have table and data

CREATE TABLE T
(
    K int NOT NULL,
    CONSTRAINT UQ_T UNIQUE CLUSTERED (K)
);

INSERT INTO T (K)
VALUES (1), (2), (9), (10);

Let's take result of the following query

SELECT K, %%lockres%% AS lockres
FROM T;

which is

K    lockres
---- ---------------
1    (8194443284a0)
2    (61a06abd401c)
9    (30b7763ed433)
10   (d08358b1108f)

(we will refer to it in the things that follow).

Then, say we have a query

SELECT COUNT(*)
FROM T
WHERE K BETWEEN 1 AND 9;

Running it under SERIALIZABLE isolation level results to the following locking footprint

To protect key range, database engine locks every key in this range with RangeS-S lock. Additionally, key next to the range is locked (this is necessary for non-unique index, but engine is doing it for unique index too). In this case, locks are placed on resources corresponding (according to result of the query taken earlier) to keys 1, 2, 9 and 10.

Non-concurrent insertion to the table

INSERT INTO T (K)
VALUES (3);

results to following locking footprint

Key 3 does not exists in the table, and so database engine requests RangeI-N lock on the resource (30b7763ed433) corresponding to key 9, which is next to 3 at the moment. Acquiring RangeI-N means that there is no incompatible serializable activity being performed, so engine proceeds and acquires X lock on the resource (98ec012aa510) corresponding to key 3. Then actual insertion happens and locks are released. Notice that there is no release of RangeI-N lock.

Running above SELECT and INSERT concurrently causes INSERT to wait until transaction performed serializable SELECT is active

because of RangeI-N incompatibility with RangeS-S. As soon as transaction performed SELECT ends INSERT will proceed.

This is what "test the range" means.

  However I do not see why two RangeI-N locks are said to be compatible
  with each other.

RangeI-N locks compatibility is good, because of it allows concurrent insertions into key range.

Imagine we are doing

INSERT INTO T (K)
VALUES (4);

and

INSERT INTO T (K)
VALUES (6);

concurrently.

It may happen that at some moment both statements acquire RangeI-N on the resource (30b7763ed433) corresponding to key 9. But this is fine, because of then concurrency is controlled by X on the keys and by page latch further.


If I understand correctly, you could have any number of columns from table B? If so, then you will need to dynamically create the SQL statement (by querying the information_schema table) and just wrap each column from B in your ifnull(field, ifnullvalue).


How to import table's data in MySql from Sql Server?

You could try changing the datatype in SQL Server to VARBINARY(MAX) :

http://wiki.ispirer.org/sqlways/mysql/data-types/longblob

Even if this is just to get your data into SQL Server. It may also be worth noting that IMAGE is being removed in future editions of SQL Server.


select * from table(dbms_xplan.display_cursor('sql_id', child_number));

You can find sql_id and child_number in V$SESSION (sql_id and sql_child_number columns).


How to know which process is causing high memory usage on DB?

The software you use is out of date. An advantage of that is that there could very well be a solution provided in My Oracle Support. The Oracle OEM agent have lot's of problems in many versions. Prepare for patching the agents or use a daily restart agent procedure. The Oracle OEM agent is part of the Grid Control monitoring infra structure so you could take it down without harming the application that is using the database[s] that the agent is monitoring.


How to get SQL Server filetable path locator for subfolder by its literal path

I found a solution.
Here is example where filetable name is 'physical_files', target path is 'folder1', target file have stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9'

declare @path varchar(MAX);
set @path = '\folder';

declare @parent hierarchyid;
set @parent = GetPathLocator(CONCAT(FileTableRootPath('dbo.physical_files'), @path));
select @parent

update physical_files set path_locator = @parent.GetDescendant(NULL, NULL)
where stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9';


Full text search over multiple CPU processes?

The default setting of "max_parallel_workers_per_gather" is 2, which won't spread work over all 4 CPUs for any one query. But that doesn't matter if you aren't getting parallel plans in the first place.

Parallel query is a relatively new feature to PostgreSQL, and is still being improved.  You should use the newest version you can to give yourself the best chance of benefiting from it.

I believe the index consultation cannot be parallelized (in any version).  The table consultation can be, but it often doesn't make sense to.

If the indexed part of the query is highly selective and returns few rows, then "parallel_setup_cost" will exceed the benefit of parallelizing the table access for just a few rows.

On the other hand, if you return a lot of rows, then "parallel_tuple_cost" (multiplied by rows returned) will exceed the benefit.  If you access a lot of rows, but don't return them (like count(*) or some other aggregate, or a filter which the index is unable to address) that is the optimal case for parallelization to work well.


Approach to save many to many relationship in case select all values

If you use a flag in the products table assume it's a bit data type and set to True which means that product is related to all countries, no doubt you will have less record in the database but your query becomes a bit complex.

If you go with the first approach using the regular joins on foreign keys would also solve the problem with simple queries.

I would prefer to go with a proper many-to-many relationship approach with proper indexes, avoid updates and simple query joins. However if the need arise of having a helper column to say this product is applicable in all countries then I would add it but only if the issue arise.


SELECT ... FOR UPDATE returns old value after waiting for lock

I have figured out the explanation for the behavior, by doing some experiments. I have used connection 1 to perform some other changes rather than insert a new row. The combined results reveal how the process works.

If connection 1 updates the description for the id, connection 2 will see the updated description. (The index scan has halted before the locked row.)
If connection 1 updates the id to a smaller  value, e.g. from 10 to 9, connection 2 will return the new value, 9. (The index scan has halted before the locked row, sees it disappear, and continues in a descending manner, thus arriving at 9.)
If connection 1 updates the id to a greater value, e.g. from 10 to 11, connection 2 will skip that value and instead return the greatest id that is smaller than what its original value was. In this example, it returns id 9 if that exists. (The index scan has halted before the locked row, sees it disappear, and continues in a descending manner, thus never passing 11, but rather arriving at 9.)
If connection 1 updates the id to a greater value, e.g. from 10 to 11, and then inserts a new row in place of the old one, e.g. with id 10, connection 2 will select the newly inserted row. (The index scan has halted before the locked row, and will see something inserted in its place.)
TODO: If connection 1 updates the id to a greater value, e.g. from 10 to 20, and then inserts a new row in place of the old one, but with a greater value than the original, e.g. 11, will connection 2 see it? Most likely not, but this should be tested.
If connection 1 locks a MAX(id) of 10, and connection 2 selects a hard-coded id of 9, then the locks do not overlap, and both can proceed independently.

We can conclude that the MAX(id) is determined using a reverse scan, and that the locks  are on actual (index) rows. This makes sense. Our isolation level of READ COMMITTED does not acquire gap locks. REPEATABLE READ might, but it causes the transaction of connection 2 to fail entirely, so there is no exploring that scenario any deeper.
Concluding the exact workings

Connection 2 starts reverse scanning until it hits a lock on a certain row.
It will wait for that lock to be released, and continue its scan from (and including) whatever id it left off at. Not whatever row, but whatever id.

As a result, if there is no longer a row at that id, the scan continues beyond it, to smaller ids.
As a result, if the row has been updated, the updated values are visible (if selecting more than just the id).
As a result, if the row has been moved and a new one inserted for its id, that new row is selected.


Deny user to see any database through DB_NAME()

DB_NAME does not work as advertised before SQL Server 2016 (where the behaviour of DB_ID is also changed). For details, see:

Information disclosure with the db_name and db_id function (Connect bug report)

There is a similar situation with other metadata functions, including:

suser_name
suser_sname
suser_sid
user_id
database_principal_id
is_rolemember
is_srvrolemember

These issues remain unresolved (either by design, or by being 'fixed') as of the time this answer was written, as far as I know.


Hide columns that are constant across a small number of rows

Columns are not dynamic. They can't be suppressed on the result because at that point it's too late. You have two options

Unpivot

id|ident|x|y
1 | foo |1|2

Becomes

id|ident|key|val
1 | foo | x |2
1 | foo | y |2

The schema is fixed from that point and you can return just what you need.
Use dynamic SQL. The server returns sql that you issue against the server.

You could also fix the schema, if most of those columns are redundant modify the schema.

Update

  I think perhaps you misunderstood my goal. I'm working with a query that returns something like: [...] I want to turn hide/supress the columns which are "constant," as in they don't change from row to row. That would turn the above table into...

I'm very sure I don't misunderstand your goal. We have at least one question a week that is along the same lines we have to turn around for the very same reason. In fact, a year ago yesterday I asked a similar question out of desperation thinking there may be a better way, and was turned around for the same reason.. I can still quote Erwin's relevant parts here,

  Column names and types cannot be dynamic. SQL demands to know number, names and types of resulting columns at call time. Either by explicit declaration or from information in the system catalogs (That's what happens with SELECT * FROM tbl: Postgres looks up the registered table definition.) ... You want Postgres to derive resulting columns from data in a user table. Not going to happen. [...] One way or the other, you need two round trips to the server. Either you create a cursor and then walk through it. Or you create a temp table and then select from it. Or you register a type and use it in the call.

Your use case here is slightly different, but you should still read the above bolded parts at least 15 times, because it has to sink through.

Then you've got to either,

Change the format you're will to accept from the server.
Process the result set on the server that generates the right query, and then issue that query back to the server.

For your example

CREATE TABLE tinyexample(id,ident,key,val)
AS VALUES
  ( 1, 'foo', 'x', 2),
  ( 1, 'foo', 'y', 2);

What you want is something like this. Note this is not the best way to do this, you could save a round trip by running this in a function.

SELECT FORMAT (
  $$
    SELECT FORMAT(
      'SELECT %%s FROM %%I.%%I.%%I;',
      array_to_string(array_remove(ARRAY[%s]::text[],null), ', '),
      %s,
      %s,
      %s
    ) AS inner
    FROM %I.%I.%I;
  $$,
  colexpr,
  quote_literal(table_catalog),
  quote_literal(table_schema),
  quote_literal(table_name),
  table_catalog,
  table_schema,
  table_name
) AS outer
FROM (
  SELECT
    table_catalog,
    table_schema,
    table_name,
    string_agg(  format($$CASE WHEN count(DISTINCT %I) > 1 THEN %s END$$, column_name, quote_literal(column_name)), ', '  ) AS colexpr
  FROM information_schema.columns
  WHERE table_catalog = 'test'
    AND table_schema = 'public'
    AND table_name = 'tinyexample'
  GROUP BY table_catalog, table_schema, table_name
) AS t;

This seems like a mess, most of that's not me though. We have

Issue our first query to the information_schema to get the column listing. Without this you'd have to statically type the columns. That's no fun. This returns query "outer".
Issue our second query ("outer") to find out which columns are not distinct based on their values. This returns query "inner".
Issue our third query ("inner") which returns the resultset we want.


Why can't you do basic operations AFTER an aggregate in a window function? Like (max(x)+5), vs (5+max(x))

If I'm understanding your question correctly, you're trying to divide the result of the window function by 5. The entire window function consists of MAX(x) OVER () therefore your query would look something like this:

SELECT MAX(x) OVER ()/5 FROM generate_series(1,10) AS t(x);

This will result in 10 rows all containing the value 2, since the generate_series function results in 10 rows and each would contain the result of our window function.


What is the system view for sys.syscolpars?

The closest equivalent is sys.columns.

You can use

exec sys.sp_helptext 'sys.columns'

to see the underlying query used and how the columns are mapped.

CREATE VIEW sys.columns
AS
  SELECT c.id                                                             AS object_id,
         c.NAME,
         c.colid                                                          AS column_id,
         c.xtype                                                          AS system_type_id,
         c.utype                                                          AS user_type_id,
         c.length                                                         AS max_length,
         c.prec                                                           AS PRECISION,
         c.scale,
         CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
         sysconv(bit, 1 - ( c.status & 1 ))                               AS is_nullable,-- CPM_NOTNULL
         sysconv(bit, c.status & 2)                                       AS is_ansi_padded,-- CPM_NOTRIM
         sysconv(bit, c.status & 8)                                       AS is_rowguidcol,-- CPM_ROWGUIDCOL
         sysconv(bit, c.status & 4)                                       AS is_identity,-- CPM_IDENTCOL
         sysconv(bit, c.status & 16)                                      AS is_computed,-- CPM_COMPUTED
         sysconv(bit, c.status & 32)                                      AS is_filestream,-- CPM_FILESTREAM
         sysconv(bit, c.status & 0x020000)                                AS is_replicated,-- CPM_REPLICAT
         sysconv(bit, c.status & 0x040000)                                AS is_non_sql_subscribed,-- CPM_NONSQSSUB
         sysconv(bit, c.status & 0x080000)                                AS is_merge_published,-- CPM_MERGEREPL
         sysconv(bit, c.status & 0x100000)                                AS is_dts_replicated,-- CPM_REPLDTS
         sysconv(bit, c.status & 2048)                                    AS is_xml_document,-- CPM_XML_DOC
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1


SQL Server: Failed to start debugger - Data is Null

I had this exact problem, and solved it, on my local dev machine, by reinstalling a Visual Studio update.

I use 2012 SQL Server and VS 2015.2. This is all Localhost - no remote debugging.

Problem occurred after installing SSMS 2016 RC3. Debugging in SSMS 2016 caused the above error.

SSMS 2014 worked just fine.

The problem went away when I reinstalled (Repaired) the VS2015.2 update.

Each SSMS version has a VS Isolated Shell version it uses. (SSMS 2016 uses VS2015, SSMS 2014 uses VS2012 etc)

It would seem there was some sort of version conflict between the SSMS installation's VS Isolated Shell, and the full VS installation. Maybe the VS update has something to do with it.

Anyway, hope this helps somebody.


How to efficiently a query to return rows plus a summary

Assuming you're not return millions of rows, you could output the results into a temporary table, then run the "summary" query on that temp table.

As an example, I'll create a table with 10,000 rows of data, then return 3,333 rows, with a summary query:

USE tempdb;

IF OBJECT_ID('dbo.MyData') IS NOT NULL
DROP TABLE dbo.MyData;

CREATE TABLE dbo.MyData
(
    MyDataID int NOT NULL
        CONSTRAINT PK_Mydata
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(30) NOT NULL
);

;WITH cte AS (
    SELECT v.num
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(num)
)
INSERT INTO dbo.MyData (SomeData)
SELECT CONVERT(varchar(30), CRYPT_GEN_RANDOM(c1.num + 1))
FROM cte c1
    CROSS JOIN cte c2
    CROSS JOIN cte c3
    CROSS JOIN cte c4;

This is the code your client would send.  It first creates a temp table, then populates the rows into the temp table, using the OUTPUT clause to return the rows immediately to the client.  Then a simple COUNT() of the rows is returned.

IF OBJECT_ID('tempdb..#MyData') IS NOT NULL
DROP TABLE #MyData;
CREATE TABLE #MyData
(
    MyDataID int NOT NULL
        PRIMARY KEY CLUSTERED
    , SomeData varchar(30) NOT NULL
);

INSERT INTO #MyData (MyDataID, SomeData)
OUTPUT inserted.*
SELECT *
FROM dbo.MyData md
WHERE md.MyDataID % 3 = 0;

SELECT COUNT(1)
FROM #MyData;

Comparing the query plans for the the two possible "summary" queries shows any possible improvements; which would only be more exaggerated with a more complicated source query:

SELECT COUNT(1)
FROM #MyData;

SELECT COUNT(1)
FROM dbo.MyData md
WHERE md.MyDataID % 3 = 0;

The plans:


How to fan out rows with a specified range of values?

This can be achieved using generate_series()

with test_data (id, "first", "last") as (
   values (42, 5, 8),
          (43, 6, 9)
)
select td.id, g.nr
from test_data td
  cross join lateral generate_series(td."first", td."last", 1) as g(nr)
order by td.id, g.nr;

Online example: https://rextester.com/TWE47868


Delete and archive old data from several related tables

Try pt-archiver from Percona Toolkit, it permits to transfer data on the fly between two MySQL instances.

You can for instance use the --where option to filter result set by date.

It's particularly well designed for your needs, because you can transfer data and delete it from source in the same command.

Official page is here : pt-archiver

An example :

pt-archiver --source h=<server_source>,D=<database_source>,t=<your_table> --dest h=<server_target> --where "date_field < DATE_SUB(NOW(), INTERVAL 3 MONTH)" --limit 1000 --txn-size 1000 --statistics

Add the option --dry-run if you want to test.

Add the option --no-delete if you want to keep data on source DB.


Storing reports with a lot of rows in relational database (Oracle)

Some things to consider are;

How is the data populated, in one operation or over a period of time ?
How you access the data, e.g. how does a user know that report_id 32645 is the one they want ?
Will you need the data in a different order ?
Will one reports data ever be correlated with another reports data ?
Will data for a report_id ever need to be removed ? If so how do you identify it ?
If you use generic column names be prepared to confuse some people.

This sort of solution CAN work but like any solution it has some limitations (some of which can be seen from the above questions).
Without knowing what sort of reports are stored its very hard to guess but in my experience reports tend to change quite frequently based on a customers needs.

They will see some data and that will lead them on the path to refining and expanding what they want to see - and that's a good thing.

Ideally you store the data in a way that reflects how it sits in the organisation (as you would with any modelling) and the reporting solution sits above that pulling out the information in whatever way it wants.
This is what a data warehouse/reporting database is designed for.


How to filters data with two windows conditions?

Another way to do it would be:
select id, door_id, type
from (
    select u.id, a.door_id, d.type
         , count(case when a.door_id is null then 1 end)
               over (partition by u.id) as x1
         , count(case when a.door_id is not null then 1 end)
               over (partition by u.id) as x2
    from
    users u
    left join access a
        on a.user_id = u.id
    left join doors d
        on d.id = a.door_id
) as t
where x1 > 0
  and x2 > 0;

I hijacked @McNets Fiddle and added this query to it


MySQL, many writes waiting on disk i/o access

Switch to InnoDB.  Heed the advice here .
Change a few things in my.cnf because of the Engine change.
Don't do anything special with disk.

InnoDB works with 16KB chunks, not 4KB, like MyISAM for data; 16KB vs 1KB for indexes.

If you still have trouble,...  What indexes are on your table?  What order are the rows in?  Are the tables bigger than RAM?  How much RAM do you have?  Let's see SHOW CREATE TABLE.


How does SSMS get a list of files in a backup?

Here is a simple script that might help you.

Works for me on SQL Server 2008 sp2

select @@version
--Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
--Sep 16 2010 19:43:16
--Copyright (c) 1988-2008 Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

Part 1:

--------------------------------------------------------------
-- first get the list of all backups in your server
--------------------------------------------------------------

declare @backup_type CHAR(1) = 'D' ;    --'D' full, 'L' log

with Radhe as (
    SELECT  @@Servername as [Server_Name],
        B.name as Database_Name,
        ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))),
               'NEVER')
            as DaysSinceLastBackup,
        ISNULL(Convert(char(11), MAX(backup_finish_date), 113)
                   + ' '
                   + CONVERT(VARCHAR(8), MAX(backup_finish_date), 108),
               'NEVER')
            as LastBackupDate
       ,BackupSize_GB = CAST(COALESCE(MAX(A.BACKUP_SIZE), 0)
                                 /1024.00/1024.00/1024.00
                             AS NUMERIC(18,2))
       ,BackupSize_MB = CAST(COALESCE(MAX(A.BACKUP_SIZE), 0)
                                 /1024.00/1024.00
                             AS NUMERIC(18,2))
       ,media_set_id = MAX(A.media_set_id)
       ,[AVG Backup Duration] = AVG(CAST(DATEDIFF(s, A.backup_start_date,
                                                    A.backup_finish_date)
                                         AS int))
       ,[Longest Backup Duration] = MAX(CAST(DATEDIFF(s, A.backup_start_date,
                                                         A.backup_finish_date)
                                             AS int))
       ,A.type
   FROM sys.databases B
       LEFT OUTER JOIN msdb.dbo.backupset A
           ON A.database_name = B.name
           AND ( @backup_type IS NULL
                 OR A.type = @backup_type )
   GROUP BY B.Name, A.type
)
SELECT r.[Server_Name]
    ,r.Database_Name
    ,[Backup Type] = r.type
    ,r.DaysSinceLastBackup
    ,r.LastBackupDate
    ,r.BackupSize_GB
    ,r.BackupSize_MB
    ,F.physical_device_name
    ,r.[AVG Backup Duration]
    ,r.[Longest Backup Duration]
FROM Radhe r
    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
        ON R.media_set_id = F.media_set_id
ORDER BY r.Server_Name, r.Database_Name ;

Part 2:

--------------------------------------------------------------
-- now with any of the backup files listed above
-- you can add them in the place below and select from @fileListTable
--------------------------------------------------------------

declare @fileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32) -- remove this column if using SQL 2005
) ;

insert into @fileListTable
    exec('restore filelistonly from disk = ''YourBackupFile.bak''') ;

select * from @fileListTable ;

Have a look at these other answers too:

How to use variables with RESTORE FILELISTONLY

SQL Server “RESTORE FILELISTONLY” Resultset


Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it

I can see why you're misunderstanding this - it's a little tricky. These are all valid:

VARCHAR(1) - one character string
VARCHAR(4000) - 4,000 characters
VARCHAR(8000) - 8,000 characters - and if you use a number for this field's definition, that's the highest NUMBER you can use, but watch this:
VARCHAR(MAX) - that one holds up to 2GB.

And yes, if you try to get data out of a VARCHAR(MAX) field, and somebody stored 2GB in there, buckle up.


Is having Estimated number of Rows > Actual Number of Rows an issue to worry about?

In the particular scenario presented:

No, the difference between estimated and actual number of rows is not important. There are two key pieces of information to support that statement:

The query has qualified for a trivial plan; and
The query has further qualified for simple parameterization

This is clear from the Seek Predicate text, where the literal values for ISBackEndSystemMigrated and ISDuplicate have been replaced with the parameter markers @1 and @2:

This means that the plan selected is the same one SQL Server would choose for all possible literal values. Trivial plan reduces compilation time to a minimum for simple queries; simple parameterization promotes execution plan reuse.

In addition, the apparent cardinality mismatch is due to SQL Server producing an estimate for the average case (over all possible literal values).

In other cases, of course it can matter a great deal that cardinality estimates are inaccurate. The trick is knowing when it is important or not. There is no general safe rule of thumb here, much of it comes down to experience. Query tuning is not all science; there is room for art too :)


you can use the below query to get the result. i think this will work for you

SELECT qs.question,group_concat(an.answer) as myanswer FROM answers an,question qs WHERE qs.id=an.q_id group by an.q_id


How to Rename Table or Field With Merge Replication

You could do it this way:

Add the new field.
Force a software upgrade that includes the logic to insert new data into the new field and to read the data from the new field if it is not null, otherwise to read that field from the old field. ( in case of update: you read the value from the old field then do an update to the new field )
Wait for a while, perhaps a month till you are sure that all clients have upgraded your software.
Migrate data from the old field to the new field if the new field is null.
Delete the old field.


How do you submit a List of items to a Stored Procedure to limit results?

If you are using SQL 2008 or higher you can use a table valued parameter which would allow you to pass in what is basically an array from the .NET side then use the table variable as an in clause an Nick has said.

If you aren't using SQL 2008 or higher then you'll want to pass in the values as an XML document, then use xquery or OPENXML to parse the XML document and put the values into a temp table (or table variable depending on how many values you expect).  Then use the temp table as part of an IN within the WHERE clause.


SQL Server TDE with PKI - converting P12/PKX to PVK/DER format

It seems to work with mycert_3.cer:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';
GO

USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password';
GO

CREATE CERTIFICATE TestCert
FROM FILE = 'D:\path\mycert_3.cer'
WITH PRIVATE KEY (FILE = 'D:\path\mycert_3.pvk',
DECRYPTION BY PASSWORD = 'Password2');
GO

USE TDE_Test
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TestCert;
GO

ALTER DATABASE TDE_Test
SET ENCRYPTION ON;
GO


Connect to PostgreSQL via a ssh tunnel using Cygwin

I was finally able to make it work by removing the -f option when creating the SSH tunnel.

For an unknown reason, the error appears when using the -f option but it works fine otherwise and I can connect via pgAdmin using the SSH tunnel.


Solution to error NID-00135 in Oracle 10g, Solaris 10

The "damn thing" tries to prevent you from destroying your database. The error is described in the manual. You did not await the shutdown immediate of your database but killed the oracle processes. Therefor not all sessions could be rolled back. Start the database again and make a shutdown immediate and wait until the database is shut down cleanly.

Stop the listner make a local connection with the dbnewid utility. The dbnewid utility is described in the manual too. You have always a good chance to destroy your database if you handle the dbnewid utility in the wrong way. The dbnewid is necessary to change the id of the database that is used by rman in an rmancatalog. if you dont use an rmancatalog th change of the dbid is not necessary. Renaming can be done by recreating the controlfile. Often it suffices only to change the insatnce name. This can be done in the parameterfile (init.ora, spfile.ora)

The simplest and therefore best way to create a copy of the production database for testing is to use the rman duplicate database command.

The reason why it tooks so long to shutdown your database is that there is a long running transaction that must be rolled back. Such a situation can be avoided if you recover your database to a point in time where no such long transactions where running on the database.


Replication agents run as jobs scheduled under the SQL Server Agent.  They can be scheduled to run continuously or on a recurring schedule.

If the replication has been scheduled to run continuously, then yes, it could be continuously 'executing'.


The fill factor does not apply to new rows being added to a page.  The fill factor only applies to how full a page is filled when the index is created, rebuilt, or reorganized.

Accordingly, if the index is rebuilt or reorganized on a scheduled basis, your index will be able to accommodate new rows with fewer page splits if the fill factor is set appropriately.


Change Data Capture not running when Mirror is Suspended

CDC uses the log reader, same as replication. When mirroring or availability groups are used, by default the log reader will not process log records that have not already been hardened at all mirroring / availability group secondary replicas.

This can be changed with Trace Flag 1448, which enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled, the log reader always waits for the synchronous secondary replicas.

References:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability#CDC

https://support.microsoft.com/en-us/help/937041/fix-changes-in-the-publisher-database-are-not-replicated-to-the-subscr


Sudden performance decrease in SQL Server

Sounds like your statistics are getting out of date, then eventually SQL's auto update statistics will kick in and update the stats, proper execution plans are being generated, then everything starts running faster again.

If you have installed Service Pack 1 for SQL Server 2008 R2 then you can turn on Trace Flag 2371 which will change the algorithm which is used from the static 20%+500 to a changing value depending on the number of rows in the table.

If you don't have service pack 1 installed then I'd recommend setting up a job that runs UPDATE STATISTICS for all your tables weekly or nightly.


How to substitute substring in column A with matching string in map table

I wouldn't do this. This is called "lexicon replacement". It's done using FTS's Thesaurus Dictionary. You can ask another question if you have a problem getting that working and you decide to go down that route.

That said, if you want to do this recursively, you can. It's certainly doable.

WITH RECURSIVE f AS (
  SELECT row_number() OVER (), *
  FROM bar
),
t(orig,working,row) AS (
  SELECT a , a , 0
  FROM foo
  UNION ALL
    SELECT orig, regexp_replace(working,pattern,repl), row+1
    FROM t
    INNER JOIN f
      ON row_number = row+1
)
SELECT orig, working
FROM t
WHERE row = (SELECT count(*) FROM f)
ORDER BY orig;

Output

           orig            |                    working
---------------------------+-----------------------------------------------
 one, 1, four, 4, three, 3 | one apple, four oranges, three strawberries
 one, 1, two, 2, three, 3  | one apple, two bananas, three strawberries
 two, 2, four, 4, three, 3 | two bananas, four oranges, three strawberries
(3 rows)

Note if you have a serial on bar then remove CTE f.


UPDATE column without FK fires other FK triggers constraint check

Tom Lane answered on the mailing list

This happens because an UDPATE invalidates the foreign key triggers for a previous INSERT in the same transaction, so postgres fires them when there is an old tuple version in the same transaction. It doesn't have the data to know that the previous change on that row was an update without fk changes.


Should an INSERT cause an exclusive lock on a foreign key?

Here's my first recommendation to remove the deadlock (and this is a very common cause with the same resolution every time). Your RoutePlan_Save procedure currently has this logic:

IF EXISTS (SELECT ... WHERE key = @key)
  UPDATE ... WHERE key = @key;
ELSE
  INSERT(key) VALUES(@key);

So you actually have two queries here that require locks on the table, regardless of whether you are ultimately going to perform an insert or update. Stop doing that! What is the point of checking if a row exists, and then in a separate query, firing an update statement that again has to check if the row exists? This requires more resources, not less, than just trying to update the row not knowing whether it exists. So do this instead:

BEGIN TRANSACTION;
UPDATE ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT(key) VALUES(@key);
END
COMMIT TRANSACTION;

You can also isolate the other read-only processes by using a different isolation level that doesn't require them to wait for write operations to complete. NOLOCK / READ UNCOMMITTED is a popular one, but READ COMMITTED SNAPSHOT - assuming you can take the hit on tempdb - is way better IMHO. There are other alternatives, too. I happened to blog about this just yesterday:

Bad habits : Putting NOLOCK everywhere


How can we get the table and the column name of known data value?

Just run this in a database.

As long as its not a text / ntext field..

SET NOCOUNT ON  /* AUTHOR DT 10/19/15 Server: Microsoft SQL Server */

CREATE TABLE #TBL_SEARCH (server_name varchar(100), db_name varchar(100), table_name varchar(40), raw_tablename varchar(max), [schema_nm] varchar(max), matching_field varchar(40), counts int,
A_SAMPLE varchar(100)
)

DECLARE Table_Column_Matches CURSOR
READ_ONLY
FOR (
    select '[' + OBJECT_SCHEMA_NAME(o.id) + '].[' + o.name + ']' as table_name, o.name as raw_table_name,
    OBJECT_SCHEMA_NAME(o.id) as [schema_nm], c.name as field_name, t.name as datatype_field from sysobjects o
    join syscolumns c
    on o.id=c.id
    join systypes t
    on t.type=c.type and t.xtype=c.xtype and c.xusertype=t.xusertype
    where
    o.type='U' and
    t.name IN ('char', 'nchar','nvarchar','varchar' )
)

DECLARE @table_name varchar(300)
DECLARE @field_name varchar(300)
DECLARE @raw_table_name varchar(300)
DECLARE @schema_nm varchar(300)
DECLARE @datatype_field varchar(300)
declare @query nvarchar(3000)

OPEN Table_Column_Matches

FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        set @query = 'SELECT @@SERVERNAME, DB_NAME(), ''' + @table_name + ''' as [table_name], ''[' + @raw_table_name + ']'' as raw_table_name,''[' + @schema_nm + ']'' as schema_nm, '''+ @field_name +''' as matching_field,
            COUNT([' + @field_name + ']) AS COUNTS,
            MAX([' + @field_name + '] ) A_SAMPLE
            FROM  ' + @table_name + ' WITH (NOLOCK)
            WHERE UPPER([' + @field_name + ']) LIKE ''IT DEPT%''
        '

        INSERT INTO #TBL_SEARCH
        exec sp_executesql @query
    END
    FETCH NEXT FROM Table_Column_Matches INTO @table_name, @raw_table_name, @schema_nm, @field_name, @datatype_field
END

CLOSE Table_Column_Matches
DEALLOCATE Table_Column_Matches

select * from #TBL_SEARCH
where counts > 0
drop table #TBL_SEARCH


InnoDB & Isolation Levels - Aren't repeatable reads a bad thing?

InnoDB isolation levels are not "bad" or "good", it depends on what your application requires in terms of isolation.

In your example, by using repeatable read, you make sure that once the first select has been done, no other transaction can modify the state of the database, from the point of view of that transaction. In other words, it is as if you had run "START TRANSACTION WITH CONSISTENT SNAPSHOT" on before your 5th line. This may be required in some cases, and let me put you an example:

TX A: START TRANSACTION;
TX A: SELECT * FROM products WHERE stock BETWEEN 4 AND 6; -- I see rows 4 and 6 only,
                                                          --  and I want to update those only
TX B: START TRANSACTION;
TX B: INSERT INTO products (stock) VALUES (5);

TX A: UPDATE products SET stock = 3 WHERE stock BETWEEN 4 AND 6;
TX A: COMMIT; -- race condition
TX B: COMMIT;

By setting InnoDB's REPEATABLE READ, phantom reads disappear, because TX B will be locked at the insert until A commits. However, if we are doing READ COMMITTED, we will get different outputs depending if A or B commits first, something that violates the ACID properties, and that our application may not expect.

In particular, STATEMENT-based replication requires no phantom reads, that is why READ COMMITTED is the default level, and has even a stricted behaviour than READ COMMITTED in other databases (where serializable is needed to get the InnoDB behaviour). If the first select was an update, executing those transactions independently would lead to different results (drifting the slave). Please note that transactions are written to the binlog atomically and serially.

I agree with you that that may not be needed in most applications, because you will handle those cases at application level. If that is the case, use ROW-based replication and set the default transaction isolation level to READ COMMITTED. You will also get in most cases better concurrency.


Create a maintenance plan for sql database with C#

Aaron Bertrand is highly regarded in the SQL community, he is asking about the source to understand the reason behind shrinking the logs. There are very few cases where shrinking a log file is a sound process. In the question you ask if it is a good idea, and then in the reply you cite it is a requirement, which implies there is no room for discourse.

2)
As a rule of thumb you do not shrink a log file, unless you understand why you should, or the technical details of the log file. If you are to shrink a log file, it should especially not be based on a time-frame or arbitrarily dictated.

The point is made Aaron or DBA01 would be amiss to give instruction that contradicts sound advice.

Basically you are only causing issues for the database, that often have a performance impact for the users, and will likely cause you or DBA01 additional grief.

Your questions elicit the golden default SQL answer 'It Depends'. You lack enough information for someone give you the advice you seek.

1)
Rebuilding indexes is an offline operation, typically done when there is 30% fragmentation, when this will occur is based on how often you are updating said indexes. Reorgs are online operations, typically done after ~5% fragmentation but to do this every 30 minutes would also be futile. There are maintenance plan walk-throughs you could look up.

Aaron also hints at a larger issue, why C#? If anything it would be better to decouple your application from the database. One approach might be to use the tool set provided and then call the agent job or process from your C# application.

3) It Depends ...
What are you trying to accomplish with your backups, RTO? RPO? which one is the focus, what are acceptable data losses?