MySQL not using the correct index I have following readings table, it has around 500 million rows.

CREATE TABLE readings (
   id int(11) NOT NULL AUTO_INCREMENT,
   value double NOT NULL,
   created_dt datetime NOT NULL,
   device_id int(11) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),
   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),
   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)
) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1

It has an index on (created_dt, device_id) and an index on device_id( which is foreign key)

When i run following query

SELECT value, created_dt
FROM readings
WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00'
  AND (device_id) IN (10, 11, 12));

Above query takes around 2 minutes.

DESCRIBE query returns the index used is readings_device_id_6a03c4ab761154d1_fk_device_id which is foreign key index on device_id. However if i remove the above index and run the query again, it uses readings_created_dt_483d31a3654ede43_uniq which is an index on both (created_dt, device_id) and retuns data in less than a second.

In my experience 5.6/5.7 is notorious for not spotting the correct index to choose for (range, range) type queries on large tables. You could try an analyze readings to see if it improves matters but I doubt it.

I have seen many examples of a SELECT on a large table filtered with a range drawn from a small list of possible values (your IN (1, 2, 3)) and an additional filter from small range drawn a massive spread of values (your created_dt BETWEEN) ... and at some threshold the planner will always choose to filter on the small list, leading to the results you observe.

The only way I have ever been able to solve this problem is either force the index if I can alter the query, or archive a significant (think 30-40%) chunk of the historical data to have the planner once again begin to choose the correct index.

Mysql 8 might be better, but I haven't any experience with really large tables on it yet.


What's a good way to model user authorization to hierarchical data? I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012).  We're working with farm production information.  We will have users who need to be authorized to view production records, but only at certain vertical levels in a hierarchy.

Our data hierarchy looks like this:

System
- Farm
  - Group
    - Animal

The idea is that some users will have access at the System level, and can see records for all Farms, Groups, and Animals within that System.  Likewise, some users will have permission starting at the Farm level, and need access only linked to that Farm and all Groups (and animals) within it.

Each table contains a primary key column, and a foreign key column linking it to the parent record (along with whatever other attributes each entity requires).

What I've implemented in the past is two-table system for linking users to the appropriate items they're allowed to see.  Implemented here, it would look like this:

Table:  Authorizations          Table:  FullAuthorizations
Columns:    Id (PK)             Columns:    Id (PK)
            UserId                          UserId
            ObjectId                        SystemId
            ObjectType                      FarmId
                                            GroupId
                                            AnimalId

The application inserts a record into Authorizations, with the user to authorize, the record id (System id, Farm id, etc), and the type of record (System, Farm, etc).  The FullAuthorizations table is used to denormalize the farm hierarchy for easier/faster filtering of data.  A trigger is used on the Authorizations table (and each of the farm, etc, tables) to update FullAuthorizations.  I considered using a View here, in a previous project with additional levels of entities, and the performance was quite poor once we began getting several hundred thousand records.

The queries would look something like:

SELECT *
FROM dbo.Animals a
WHERE EXISTS (
    SELECT 1
    FROM dbo.FullAuthorizations fa
    WHERE fa.UserId = 1 AND fa.AnimalId = a.Id
)

In the other project where we're doing this, the solution is performant, but feels like a bit of a hack, and I especially don't like that we can't maintain referential integrity on Authorizations with the associated objects.  I'd appreciate feedback on some other possible solutions.  I've been looking at things like Nested Sets, but not sure something like that fits this particular problem.

A long time ago, in 2000, I had a similar question, and posted on Google Groups. I received an awesome response from a guy called Joe Celko, and it will fit right into your problem. I've been using this methodology ever since, and it has great performance, and works really well

https://groups.google.com/forum/#!search/pascaljr/microsoft.public.sqlserver.programming/knQ5WXIDfeo/E0vfeC2kbTsJ

It uses two integer values, a left and a right value, to store the hierarchy. I've made some adaption to the table and procs over the years, but the essence remains the same.


SQL Fastest Way to find Data Difference Between Two Tables I am trying to locate fastest way to find difference between two tables.  The first table is populated from text files daily, the second table is maintained in our database.  We need to add data that is new or has changed from the text files. The comparison is done on the primary key.

create table dbo.CustomerTransaction
(
    CustomerTransactionId int primary key,
    CustomerName varchar(50),
    ProductName varchar(50),
    QuantityBought int
)

So row values Table 1: (1,'Bob','Table',8) is the Same as Table 2: (1,'Bob','Table',8)

These are different (1,'Bob','Table',8) ,  (1,'Bob','Chair',8) , different on the primary key.

I am seeing a lot of articles discussing methods, with Left Join, Except, tablediff, Visual Studio DataComparison, Union group.

However, no one talks about performance/speed.  Which is the fastest internal algorithm way? I am guessing it is the TableDiff Utility, which is a SQL tool designed specifically for this purpose.

We do not have access to CDC in legacy text file system, so we are extracting all new data into SQL Server daily, and comparing to previous data. We are finding incremental load values, and placing into Kimball Data Warehouse.

Neither table is actively being used at the time we are doing the comparison.

How to find out the content differences between 2 SQL tables and produce sync SQL
https://stackoverflow.com/questions/4602083/sql-compare-data-from-two-tables

There is no firm answer. David Lozinski conducted a study below, and found different methods succeed depending on row count, statistics, cardinality.

Fastest way to insert new records where one doesn’t already exist

I had an opportunity to test the tablediff utility, for some reason it was performing lot slower (2-3x slower) compared to T-SQL methods below. However it is good for conducting cross-server table differences (originally used in Replication method).


SQL Server 2014 EE not using all available processors First of all let me apologize for not being clear on my requirements in my original question. I am now updating my question with my current scenario:

I have few existing SQL 2014 Enterprise Edition cluster and standalone servers in my environment. I have observed that some of them are not using all the available processors on server. For e.g. One of them is only using 40 processors out of 72 available on the server. I googled for a fix of this and came to know that it could happen when you have servers upgraded from previous Server/CAL based licensed severs(I could be wrong here as well). This limits the number of processors that you can use.

Just to double check my existing license I ran select @@version and this shows my SQL Server is Enterprise Edition only. That means Server/CAL based. I also checked schedulers on my machine and saw that only 40 of them are online and rest of them are offline. Also, there is no affinity masking configured on the server.

Now, I want to know what options do I have to correct this issue and how to apply those changes(seeking only technical details and is not about the legality or pricing of licensing.) to fix it.

Please let me know if you need any further details.

You need to download the right ISO/EXE from your licensing site. (Could be MSDN, for example.) The one you want is Core-based, and it'll have the right installation key embedded in it. (You may also be able to check with your license admins to get it.)

Then, you can do an edition change (Books Online). For more practical details, check out Kendra Little's post on changing editions.

For folks who stumble across this question and just wonder what's going on, Aaron Bertrand has a great blog post with more background on the 20/40-core limit.


Mysql Fragmentation. How bad is too bad? I ran a table status for my companies production site and its showing close to around '49085939712'  for just about every table in my database and we have around 400 tables.

I am not a DB administrator and my mysql skills are not the best when it comes to optimizing. To me it looks pretty bad. but I need everyone else's oppinion.

My question is how bad is too bad. What is an on average threshold to know when its time to optimize a single table.

Fragmentation should have little to no effect on performance of InnoDB, it only affects the size of the data file.  So it's "too much fragmentation" if you are running out of disk space.

If you have innodb_file_per_table set to OFF, then all InnoDB tables are stored together in one tablespace file, by default this file is called ibdata1.

The value of information_schema.tables.data_free shows the same value for all tables in this tablespace.

See http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html

  When you ask for available free space in the tablespace by issuing a SHOW TABLE STATUS statement, InnoDB reports the extents that are definitely free in the tablespace.

In other words, the value reported in data_free is not the amount of free space for any single table, it's the amount of space taken by totally free extents in the tablespace file.  An extent is a block of 1MB, which is 64 pages by default.

You may notice that 49085939712 is exactly 46812MB.  That's how many unused extents you have in your tablespace file.

But you probably also have partially unused space within other extents.  This is not reported by data_free.  It's a little bit harder to get this information, one way is to use the InnoDB tablespace monitor to report it.

Doing OPTIMIZE TABLE won't recover free space, since you are apparently not using innodb_file_per_table.  InnoDB will reuse those extents for new data before expanding the size of your tablespace file further, but if you need to recover that ~45GB right away, you unfortunately have to do it the hard way:

Dump all InnoDB tables (or at least convert them to MyISAM temporarily)
Shut down mysqld
rm ibdata1
Restart mysqld
Restore all InnoDB tables.


Do I need a domain controller for multiple PCs accessing the same SQL server db? How do I configure it? (more details/replicate from: https://superuser.com/questions/1612260)
Dear all,
I'm a total newbie in connecting 2 PCs and making them use the same SQL server DB from one computer ... so please support me.
At home, I have 2 PCs from my company (working from home due to Covid19; I don't have better windows images and I would prefer not changing them, or at least as possible):

PC
Windows
SQL server
SSMS
IP

1
7, version 6.1, build 7601 sp1
2014 (installation in progress)
not yet
192.168.0.10

2
10, version1803, build17134.345
2017
18.1
192.168.0.105

The final goal: to have PC1 act as a db server and PC2 as a client (that can access the server's dbs). On both PCs I will install the same application which should be able (from any PC) to update the data from PC1/the db server.
I'd been told I would need a domain controller (DC) for this scenario, but I really don't have any clue what this means or how I should configure it:

do I need a DC?
is this DC something that comes with the windows images?
is this DC something that comes with SQL Server?
is this an application which I'll need to install separately? (I initially thought that if I'm installing sql server on PC1, I will have to insert the windows credentials from the PC2 and this will suffice)
could you please give me step-by-step on how to make this work?

Thank you!
R

do I need a DC?

No you can use SQL Authentication, or Windows Auth (NTLM) with "workgroup auth".  And to use a DC you would have to join both of your boxes to the domain, so I wouldn't bother.

is this DC something that comes with the windows images?
is this DC something that comes with SQL Server?
is this an application which I'll need to install separately?

Domain Controller is a feature of Windows Server, and is not available on the client editions of Windows, like Windows 7 or Windows 10.

(I initially thought that if I'm installing sql server on PC1, I will have to insert the windows credentials from the PC2 and this will suffice)
could you please give me step-by-step on how to make this work?

If you create a local user on both boxes that has the same password, you can use Windows Integrated Auth for SQL Server.  This is the "workgroup auth", and it would enable accessing File Shares and other services between the computers.


Is SQL Server Replication transactionally 'safe' across all published tables? We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 cluster.

My question is, are the INSERT/UPDATE/DELETE statements on the source database across all published tables guaranteed to be transactionally safe (as the name suggests)? For example, if I perform the following query on the source database:

BEGIN TRAN
INSERT  t1  (mycol) VALUES  ( 0 )
INSERT  t2  (mycol) VALUES  ( 1 )
COMMIT TRAN

Assuming that t1 and t2 are part of the same publication, will the following query on the destination always yield the result below?

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT 'Value in t1: ' + CAST(t1.mycol AS VARCHAR(10)) from t1
SELECT 'Value in t2: ' + CAST(t2.mycol AS VARCHAR(10)) from t2
COMMIT

Result:

Value in t1: 0
Value in t2: 1

Well, there are three different types of replication, but they are all transactionally safe, to a degree.
There are three types of replication in SQL Server:  Transaction Replication, Merge Replication, and Snapshot Replication.
Transaction Replication
This type of replication transfers each modification that occurs in the master to each of the subscribing databases.  This type of replication insures that you will get each changes as it occurs (more or less) on the master system.
This will perform like you describe.  There may (will) be a time delay while the replication is moved from the master database to the subscriber database, though.
Merge Replication
This type of replication is essentially Transaction Replication, but it allows for conflicts.  (Obviously, there's a bit more to it than that, but that's the essence.)
So, just like Transaction Replication, you will get the data on each subscriber as it changes (plus a little time lag).
Snapshot Replication
This is different from the previous two in that the database does not replicate individual changes.  Instead, it takes periodic snapshots and sends down the snapshots.
This is transactionally safe in that you will only see completed transaction being replicated to the subscribers.  However, you will not see this in real time (or anything like real time).

So, yes, replication is transactionally safe in that you will only see completed transactions.  Any transactions that are partial or rolled back will not be applied to the subscriber databases.
However, they are not transactionally safe in that replication does not maintain perfect consistency between databases.  (Thus breaking ACIDity and therefore transactions.)  For this, you will need distributed transactions (which is an entirely different concept).


Restore With Move has poor performance As part of our regular development cycle we need to take a copy of production into development databases.

To do this, we take a copy of production backups and move them to the Development Server. Then we use the RESTORE WITH MOVE function to define the where the data and logs should be stored.

This restore is very time consuming, and generally takes 2 to 3 times as long as a standard restore. This is an expensive overhead for us, as it often has to be done several times during the day.

I was wondering whether there is a more efficient way to perform this task.

I have tried writing the backup to several files to improve performance, and whilst this speeds up the backup, the RESTORE WITH MOVE seems unaffected.

Any help is greatly appreciated.

The restore speed will depend on the hardware you use for DEV environment.

I would suggest to turn on Instant File Initialization. When backups are restored the space has to be pre-allocated before a restore can occur and IFI is a boon for such scenarios. Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization.

Also, you can play with BufferCount  and MaxTransferSize. Read up Optimizing Database Restores.


TRY .. CATCH sometimes fails to catch PRIMARY KEY VIOLATION I wrote code to upsert records to the database. The database is Azure DB, S4 tier.

Updates and inserts are sometimes are highly concurrent since it's coming from the application that doing too many updates.

The code works successfully most of the times, but sometimes it throws 2627 exception (that handled inside of the CATCH statement). Cannot figure out so far what could be the issue.

I can replace it with MERGE statement, but I tbh don't favor it much, and also want to figure out what's happening with this try and catch.

SET nocount ON;
IF NOT EXISTS(SELECT * FROM MyTable WHERE ID = @ID)
BEGIN TRY
INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END TRY
BEGIN CATCH
IF @@Error <> 2627 ----Violation of PRIMARY KEY constraint
THROW;
END CATCH

UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged < GETDATE()

Not sure if this is the issue, but you should use ERROR_NUMBER() not @@error in your catch block.

In any case something like this should work reliably, without PK voliations:

SET nocount ON;
begin transaction
IF NOT EXISTS(SELECT * FROM MyTable with (updlock, holdlock) WHERE ID = @ID)
begin
  INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END
else
begin
  UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged < GETDATE()
end
commit transaction


2018 of MySQL (InnoDB) CHAR vs VARCHAR In 2018, does it still hold true that MySQL (InnoDB) CHAR performs better VARCHAR speed-wise?

I've only found a performance comparison explanation that is 7 year old. I've checked the official MySQL 8 CHAR vs VARCHAR but the article speaks about storage only, not about performance. I've also read a notice that VARCHAR causes some problems with table fragmentation (see the very 1st comment to this answer.

I seriously doubt that it ever held true for InnoDB.  Even for MyISAM (which really can have FIXED length rows) it was rarely true.

Roughly speaking, this is the order (most overhead first)

Disk hit(s).
Locate the record(s).
Parse the record (split it into columns).
Apply functions and evaluate expressions.

If the data is bigger (as in CHAR being padded), the #1 is impacted.  The rest pales into insignificance.

Note: There are 4 flavors of InnoDB ROW_FORMAT.

For REDUNDANT, a CHAR(100) CHARACTER SET utf8mb4 always takes 400 bytes.
For COMPACT or DYNAMIC, it takes between 100 and 400 bytes.
For COMPRESSED, the padding spaces are highly compressible, but still take some space.

Allegedly, the "fixed" size of a CHAR (which, is not all that fixed), tends to prevent block splits.  But I kinda doubt it.

My Rule of Thumb:  CHAR should almost always be restricted to truly fixed length strings, and most of them are hex or ascii:

country_code CHAR(2) CHARACTER SET ascii
british_postal_code CHAR(6) CHARACTER SET ascii
zip5 CHAR(5) CHARACTER SET ascii
md5 CHAR(32) CHARACTER SET ascii  -- UNHEX() into BINARY(16) is tighter
base64 CHAR(?) CHARACTER SET ascii COLLATION ascii_bin  -- need case-significance

Note:  latin1 works equally well as ascii, the 8.0 default of utf8mb4 does not.

(See also my answer to the first link.  It starts "Most of the answers in this thread are 5 years old, written before InnoDB and utf8 were defaults. So, let me start over..")


Difficulty getting sp_executesql to work Alex Aza's comment solution here generates all names of tables with records referencing a specified input record. I would like to adapt Alex's solution into a table-valued function. I'm having trouble seeing how to parameterize his dynamic SQL code correctly. The single-vs-double quotes are tripping me up, for example.
A typical value of @Command from Alex's solution is as follows:
SELECT 'PrimaryTable'
WHERE EXISTS(
    SELECT *
    FROM PrimaryTable
    WHERE ForeignKey_PrimaryTable = 999
    )
UNION ALL
SELECT 'ForeignTable_A'
WHERE EXISTS(
    SELECT *
    FROM ForeignTable_A
    WHERE ForeignKey_PrimaryTable = 999
    )
UNION ALL
SELECT 'ForeignTable_B'
WHERE EXISTS(
    SELECT *
    FROM ForeignTable_B
    WHERE ForeignKey_PrimaryTable = 999
    );

I think I've set up the framework correctly & would appreciate any assistance with assignment of the @Command variable.
Thanks so much for your consideration!
My partial solution is as follows:
-- Declarations needed for sp_executesql
declare @ExecParameterDefinition NVARCHAR(1024)
declare @ExecOutput TABLE(TableName NVARCHAR(1024));
declare @CapturedOutputTable TABLE(TableName NVARCHAR(1024));
SET @ExecParameterDefinition =
    N'@RowId int, ' +
    N'@TableName sysname, ' +
    N'@ExecOutput TABLE(TableName NVARCHAR(1024)) OUTPUT'

    -- Alex Aza's solution
    declare @Command nvarchar(max)

    -- This statement needs to be completely quoted as an executable string:
    SET @Command = isnull(@Command + ' union all ', '') + 'select '''
    + object_name(parent_object_id) +
    ''' where exists(select * from ' + object_name(parent_object_id)
    + ' where ' + col.name+ ' = ' + cast(@RowId as varchar) + ')'
    from sys.foreign_key_columns fkc
    join sys.columns col on
    fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
    where object_name(referenced_object_id) = @TableName;

-- Display constructed SQL command:
SELECT @Command;

-- Execute dynamic SQL & capture output into a table variable:
EXEC sp_executesql
    @Command,
    @ExecParameterDefinition,
    @RowId = 999,
    @TableName = 'PrimaryTable',
    @CapturedOutputTable = @ExecOutput OUTPUT

-- Display captured results:
select * FROM @CapturedOutputTable

UPDATE : Thanks, Paul White, for pointing out that dynamic-SQL can't be called from within a function. It appears though that I could call sp_executesql from a stored procedure & simply return a bit value indicating whether sp_executesql returned any results or not. Does this make my problem tractable?
UPDATE 2 : I finished parameterizing my code solution, which was incomplete in my original post. Solutions provided here, such as Hannah Vernon's below, can now be quickly tested. I also included Hannah's suggestion to "SELECT @Command".
Running this code with Hannah's suggested solution seems to hit a syntax error in the call to sp_executesql:
(1 row(s) affected)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.

(0 row(s) affected)

(1 row(s) affected)

I can only imagine that the @ExecOutput table variable declaration must be incorrect within @ExecParameterDefinition..? Could someone please correct me on this as well?

I think you are attempting to get a list of tables that contain rows referenced in another table.  I think you want to encapsulate this into a re-usable object, such as a stored procedure.

I created a simple schema to test your code, as such:

USE TempDB;
CREATE TABLE dbo.T1
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(255)
);
CREATE TABLE dbo.T2
(
    ID INT NOT NULL FOREIGN KEY REFERENCES dbo.T1(ID)
    , SomeOtherData VARCHAR(255)
);

INSERT INTO dbo.T1 VALUES (1, 'Test');
INSERT INTO dbo.T2 VALUES (1, 'Test2');

The following shows the table T2 as output, which I think is what you are attempting to do.

CREATE PROCEDURE dbo.GetReferencedTables
(
    @TableName SYSNAME
    , @RowID INT
)
AS
BEGIN
    DECLARE @OutputTable TABLE
    (
            TableName SYSNAME
    );
    DECLARE @Command nvarchar(max);

    SELECT @Command = isnull(@Command + ' union all ', '') + '
        SELECT ''' + object_name(parent_object_id) + '''
        WHERE EXISTS(SELECT * FROM ' + object_name(parent_object_id)
        + ' WHERE ' + col.name+ ' = ' + cast(@RowId as varchar) + ')'
    FROM sys.foreign_key_columns fkc
        JOIN sys.columns col ON
            fkc.parent_object_id = col.object_id AND fkc.parent_column_id = col.column_id
    WHERE OBJECT_NAME(referenced_object_id) = @TableName;

    --Totally unnecessary to put the results into a table variable
    --unless you want to further manipulate or use the results
    INSERT INTO @OutputTable
    EXEC sp_executesql @Command;

    SELECT * /* NEVER use SELECT * unless you're me.
            see http://bit.ly/1nRziYq for reasons.
            */
    FROM @OutputTable
    ORDER BY TableName;
END
GO
EXEC dbo.GetReferencedTables 'T1',1;

Output:


SQL Server Network Configuration node missing from Configuration Manager after install I have a server running Windows 2012 R2 Datacenter 64-bit. I was using SQL 2016 RC3 with no issues and I want to replace it with the RTM build we have from MSDN.

After uninstalling RC3 and then installing RTM, I was unable to connect remotely to the server. Upon inspection, I see within Configuration Manager that there is no node for "SQL Server Network Configuration". The 32-bit versions are listed, and TCP/IP is enabled. How do I get this missing node installed?

I have started the SQL Browser service and attempted to repair the 2016 installation. Nothing seems to revive this missing component.

I find interesting that there are pieces of the RC3 and RTM installs left behind that I can't uninstall. I'm not sure if this is relevant. I followed Microsoft instructions on uninstalling SQL Server so I feel like I've done everything right.

Posting this up in case anyone else sees this and runs into the same perceived issue as I had. We have SQL Server 2016 SP1 x64 installed and SSMS 2017 installed. When I open SQL Server 2017 Configuration manager, I can't see all of the items (just as you couldn't). However, when I open SQL Server 2016 Configuration Manager I can see all of the items as expected.

Some more details are here:
http://internationaldatascience.com/sql-server-2016-configuration-manager-missing-ssms-17-2-cannot-connect-to-wmi-provider/

Here's the relevant info incase that blog goes down:

  However, “SQL Server Network Configuration” was missing! The “SQL
  Server Network Configuration (32bit)” section was there, but blank as
  usual. Searching the web wasn’t turning up much, so I started poking
  around on the local machine to find the old tool. Looking on a machine
  that had 2016 but not the problem, I could see that the command in the
  Shortcut on the Start Menu for SQL Server Configuration Manager is
  actually a call to open a Microsoft Management Console (MMC):

  C:\Windows\SysWOW64\mmc.exe /32

  C:\Windows\SysWOW64\SQLServerManager13.msc I copied the “SQL Server
  2016 Configuration Manager” shortcut file in the folder

  “C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL
  Server 2016\Configuration Tools” from the donor machine to the one I
  was having the problem with.


The innodb_system data file 'ibdata1' must be writable even with full permissions set i'm trying to change the datadir in mysql, i created a new directory with the user mysql as a owner, i know am not supposed to set the permission to 777. but even with this full permission still getting the same error in the log file whenever i try to start the mysql service.
[InnoDB] The innodb_system data file 'ibdata1' must be writable
user@user:~$ ls -l /database2/mysql | grep ibdata1
-rwxrwxrwx 1 mysql mysql 12582912 Jan 15 15:24 ibdata1

any solution ?

After reading this article and getting a main idea about SELinux and Apparmor i have finally managed to solve the issue.
The solution was adding the new path of my new datadir in /etc/apparmor.d/usr.sbin.mysqld like this:
/database2/ r,
/database2/** rwk,

Now restart the apparmor service and start the mysql service.
sudo systemctl restart apparmor
sudo systemclt start mysql


Query against JSON array in PostgreSQL The data I'm working with is in a column ('transaction') of JSONB type, containing documents similar to this {"transactions":[{"id":"ABC123", ...}, {"id":"DEF456", ...}, {"id": "GHI789", ...}]}

I'm trying to get the distinct transaction id's but since the "id" field is part of a hash, inside an array, I'm struggling to get data out in a useful format.

I've got a solution that returns me something but it feels ungainly, and as a newcomer to Postgres, I feel like I'm not on the right track:

I'm using this from within a Python application, running sqlalchemy, but to get to grips with the data, I've constructed the following in the psql terminal:

WITH orders AS (SELECT transaction->'transactions' AS t FROM transactions) SELECT jsonb_array_elements(t)->>'id' FROM orders;

This returns me a rowset of JSON items extracted from the nested hashes. However, when trying to run this in sqlalchemy I get the following:

>>> query = sqlalchemy.text("WITH orders AS (SELECT transaction->'transactions' AS t FROM transactions) SELECT jsonb_array_elements(t)->>'id' FROM orders")
>>> rowset = engine.execute(query)
>>> rowset.fetchall()
[(u'ABC123',), (u'DEF456',), (u'GHI789',)]

... meaning I need to deconstruct further nested types from the sqlalhemy response. Which overall feels pretty cludged together.

Is there a better way to be querying this nested data?

You can write the query simpler without CTE which probably would perform better.

select (jsonb_array_elements(transaction->'transactions')->'id')::text as trx_id
from transactions;

SQL alchemy returns a list of tuples. This is the standard behaviour. A row in PostgreSQL is represented as a tuple. Even if you query a single columns it's going to be a tuple with one element. Hence the (u'ABC123',) representation.

If you need a list of the first column you can easily transform your resultset in python:

[r[0] for r in rowset.fetchall()]

This should return

['ABC123', 'DEF456', 'GHI789']


Make a report from 3 tables I need to make a report of 3 differences tables.

I tried something but I have an error.

Here my structure:
http://sqlfiddle.com/#!9/7c0e3a

I need something like this:

|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|
| RAT_Id | RAT_Name      | RAT_Price | booking_nb | booking_total | booking_pct | night_nb | night_total | night_pct |
|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|
| 1      | April Special | 125.00    | 2          | 1             | 50          | 3        | 4           | 75        |
|--------|---------------|-----------|------------|---------------|-------------|----------|-------------|-----------|

Here what I tried but I have an error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':hotel_id ORDER BY t.RAT_Id ) q LEFT JOIN ( SELECT r.RAT_Id , COUNT(' at line 17:

SELECT q.RAT_Id
, q.RAT_Name
, SUM(IF(c.RAT_Id=q.RAT_Id,c.booking_nb,0))     AS booking_nb
, SUM(c.booking_nb)                             AS booking_total
, SUM(IF(c.RAT_Id=q.RAT_Id,c.booking_nb,0))
    * 100.0 / SUM(c.booking_nb)                 AS booking_pct
, SUM(IF(c.RAT_Id=q.RAT_Id,c.night_nb,0))       AS night_nb
, SUM(c.night_nb)                               AS night_total
, SUM(IF(c.RAT_Id=q.RAT_Id,c.night_nb,0))
* 100.0 / SUM(c.night_nb)                       AS night_pct
FROM ( SELECT t.RAT_Id
    , t.RAT_Name
    FROM ___Rates t
    WHERE t.RAT_HotelId = 'cus_AjGG401e9a840D'
    ORDER BY t.RAT_Id
) q
LEFT
JOIN (
    SELECT r.RAT_Id
    , COUNT(DISTINCT b.BOO_Id) AS booking_nb
    , COUNT(DISTINCT d.BIL_Id) AS night_nb
    FROM ___Rates r
    JOIN ___Bookings b
    ON d.BIL_RateId = r.RAT_Id
    JOIN `___BillableDatas` d
    ON d.BIL_BookingId = b.BOO_Id
    AND d.BIL_Date BETWEEN "2017-01-01" AND "2017-12-31"
    AND d.BIL_Type="Night"
    WHERE r.RAT_HotelId = 'cus_AjGG401e9a840D'
    GROUP
    BY r.RAT_Id
) c
ON 1=1
GROUP
BY q.RAT_Id
, q.RAT_Name
ORDER
BY night_nb DESC, q.RAT_Name ASC

First: as noted by joanolo, the code you posted (when added to the set-up in your SQLFiddle link) generates a completely different error.

I obviously cannot guarantee that my changes are correct, but it appears that you were having problems with your JOIN clauses. The ON conditions for the JOINs weren't lined up correctly.

I've modified them as follows:

    FROM `___BillableDatas` d
    JOIN ___Bookings b
    ON d.BIL_BookingId = b.BOO_Id
    JOIN ___Rates r
    ON d.BIL_RateId = r.RAT_Id
    WHERE r.RAT_HotelId = 'cus_AjGG401e9a840D'
    AND d.BIL_Date BETWEEN "2017-01-01" AND "2017-12-31"
    AND d.BIL_Type="Night"

Note: I moved the d.BIL_Date and d.BIL_Type checks to the WHERE clause. It's not necessarily going to generate incorrect results to put them in the JOIN, but I generally recommend that only the checks that are explicitly tied to how the two tables' rows relate to each other be included in the ON clause; it helps clarify your intentions, and makes changing the query later easier.

And here's a SQLFiddle link with the modified code included. It returns a single row.

Now: based on the error you report, I don't think it's necessarily a MySQL issue. It seems likely that you're running the SQL command from somewhere else (PHP code, for instance), and that you've got a problem with how you're passing in your parameters to the statement. I've seen :hotel_id used in such code to indicate a parameter to be replaced, and the fact that you need to use the same value at two points in the code may be causing your problem. It's a direction to look, at least.

Hope this helps.


Query to list all tables in a schema I am using Rational Application Developer to run querys on a database. We are unable to locate anyone who has a list of the tables on the schema and so far the queries we have found to get a list of tables are unsuccessful.

USE <DB>
GO
SELECT *
FROM sys.Tables
GO

Use breaks but I replaced it with CALL.

GO breaks as well and I cannot locate a function that does the same thing.

The given statement smells like MS SQL-Server, not like DB2.

According to IBM Documentation I think SYSCAT.TABLES is the view you are looking for.


Scenario of huge SQL Server Database backup I use SQL Server and have a huge database with large compressed backup size (about 250 GB).
The backup scenario of this database is:

Create full backup at the first day of each month.
Create Differential backup daily exclusive first day of each month.

also I use mirror backup in order to copy my backup file to another server.

Total time that need to create full backup is about 3 hour.

I look for a better scenario to create small backup size and copy faster than now.

In this database I have 2 filegroups (ArchiveFG and PRIMARY). The size of ArchiveFG is 200 GB, also this file group is read-only filegroup.

I don't know that can I use following scenario?

create full backup from ArchiveFG filegroup (One time).
create full backup from PRIMARY filegroup the first day of each month.
create differential backup from PRIMARY filegroup daily exclusive first day of each month.

And how can I restore backup with above scenario.

Thanks in advance

What you're looking to do is called a "piecemeal restore" and is pretty complicated. Here's a small pull quote from the MSDN article:

  Every piecemeal restore starts with an initial restore sequence called the partial-restore sequence. Minimally, the partial-restore sequence restores and recovers the primary filegroup and, under the simple recovery model, all read/write filegroups.

I'm assuming that your database is in simple recovery since you didn't mention any log backup schedule. So, if you ever need to restore your archive filegroup, you'll end up restoring the entire database.

If I were you, I'd try tuning your backup before going this route. For instance, try taking the mirroring out of the backup command note the time difference. You can always copy it to the secondary location after the full backup completes.

But, if you're looking to proceed with the piecemeal restore solution, the actual syntax isn't that bad:

restore database [yourDB]
filegroup = 'PRIMARY'
from disk = '\\path\\to\\full_backup.bak'
with partial, norecovery;

restore database [yourDB]
filegroup = 'PRIMARY'
from disk = '\\path\\to\\diff_backup.bak'
with partial, recovery;

restore database [yourDB]
filegroup = 'ArchiveFG'
from disk = '\\path\\to\\archive.bak'
with recovery;


mysqldump file restore while sql is still being dumped Im using MaridaDB 10, but my question applies to all mysql variants.
I have large dataset (70GB uncomressed when dumped to file).

The mysqldump process takes 2-3hours. The restore process takes even longer 10-14 hours.

I have a nfs share I use for syncing data between many servers. My question is specifically.. If I have mysqldump process running and outputting data to /nfs/file.sql and is at say ~35gb (50% complete). Can I start a restore process (mysql < /nfs/file.sql) on a different server and have it work as expected? (finish through the end of file when its complete).

Im not worried about restore process getting ahead of mysqldump. With even a 20% buffer, its unlikely.

You could do things to speed up this whole process

STEP 1 : Disable the double write buffer / increase log buffer size

Go to the target server. Login o mysql as root and run

mysql> SET GLOBAL innodb_fast_shutdown=0;

then restart mysqld like this

service mysql restart --innodb-doublewrite=OFF --innodb-log_buffer-size=268435456

This disables the double write buffer and make the log buffer 256M

Running SET GLOBAL innodb_fast_shutdown=0; beforehand does a complete flush of InnoDB.

STEP 2 : Pipe mysqldump from source server to target server

mysqldump ... | mysql ...

This will not save the dump to a file. This send the commands straight to the target server

STEP 3 : Restart mysqld on the target server (restore original options)

service mysql restart

SUGGESTED READING

I have suggested disabling the double write buffer before

May 03, 2014 : Optimizing a large number of INSERT ... SELECT statements
Dec 19, 2014 : When is it safe to disable InnoDB doublewrite buffering?
Sep 29, 2016 : MySQL settings useful to speed up a mysqldump import

I have also suggested piping mysqldump to mysql client before

Jan 13, 2012 : MySQL - MySqlDump uses \n instead of null
Mar 02, 2012 : How to log verbose output from mysqldump?
Nov 11, 2016 : Large MYSQL DB on single file
Nov 23, 2016 : Moving large databases


Cannot output MySQL data to file I am trying to output the data from an MySQL table to a file but getting permission errors:

$ pwd
/home/dotancohen
$ mkdir in
$ chmod 777 in/
$ mysql -ugs -p
mysql> USE someDatabase;
mysql> SELECT * FROM data INTO OUTFILE '/home/dotancohen/in/data.csv';
ERROR 1045 (28000): Access denied for user 'gs'@'localhost' (using password: YES)
mysql>

If the directory in question is chmodded to 777, then why cannot the MySQL user write the file? Interestingly enough, I cannot write to /tmp/ either.

EDIT:
It looks like the DB user has the proper MySQL permissions:

mysql> show grants;
+----------------------------------------------------------------------------------+
| Grants for gs@localhost                                                          |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'gs'@'localhost' IDENTIFIED BY PASSWORD 'somePassword'     |
| GRANT ALL PRIVILEGES ON `gs\_%`.* TO 'gs'@'localhost'                            |
+----------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

According to MySQL Documentation on SELECT ... INTO OUTFILE

Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all
users on the server host. The reason for this is that the MySQL server
cannot create a file that is owned by anyone other than the user under
whose account it is running. (You should never run mysqld as root for
this and other reasons.) The file thus must be world-writable so that
you can manipulate its contents.

You should output the SELECT INTO OUTFILE to /var/lib/mysql as follows
SELECT * FROM data INTO OUTFILE 'data.csv';

Of course, you need to make sure you have FILE permission on gs@localhost.
There are two ways to have this permission given
METHOD #1
GRANT FILE ON *.* TO 'gs'@'localhost';

METHOD #2
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
FLUSH PRIVILEGES;

UPDATE 2012-05-01 07:09 EDT
To give yourself FILE privilege, do the following:
service mysql restart --skip-networking --skip-grant-tables
mysql <hit enter>
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
exit
service mysql restart


Drop constraints vs disable constraints during a data import I am in a tough situation, and looking for advice on how to proceed.  I have to import some data from one environment to another.  80% of the records have been modified, so the plan is to simply delete all the existing records, and import the new ones.

Unfortunately this is a base table.  Several other tables have FK's defined, pointing at this table.  In order to delete the data, I need to either drop the FK's and recreate them when finished the import, or disable the constraints and re-enable them.

No records have been deleted.  A copy of prod was made, and developers have tweaked the data and added new records.

Which approach would you take?

As recommended by Mikael Eriksson, I'd do the import without dropping/disabling the Foreign Keys.

The downside to this is if you want to delete any records who's values are referenced by an FK or want to update values such that the original value is no longer in the table and referenced by an FK.
In these cases you'd need to keep track of any such issues and decide how to handle each "conflict" (don't update/delete, alter the data in the other table or some other solution).

This will be quicker, easier and ensure data integrity compared to dropping/disabling the FKs, importing and then restoring the FKs.


How to get number of locks acquired by my MySQL Query? How to get number of locks acquired by my MySQL update query?

Is it possible using MySQL Command Prompt or MySQL Workbench?

Example query : update employees set store_id = 0 where store_id = 1;

As per info linked in comment - it needs some preparing, cannot probably be used automatically:
that is output from

show engine innodb status

You can see it if you do in commandline:

begin;
update employees set store_id = 0 where store_id = 1;

and then in any (even the same) session

show engine innodb status\G

Where you look for section labeled

------------
TRANSACTIONS
------------

and find transaction which the update is part of.

Original answer (still holds for general usage IMHO)
I don't think it is possible directly, but you can get some details about locks from server status:

show session status like 'Innodb_%_lock%';

if you run it before and after query, you can check difference and analyze it.


Can a plan "age" reach zero? I am reading SQL Server Execution plans by Grant Fritchey, He mentions:

  SQL Server does not keep execution plans in memory forever. They are
  slowly aged out of the system using an "age" formula that multiplies
  the estimated cost of the plan by the number of times it has been
  used. The lazywriter process, an internal process that works to free
  all types of cache (including the plan cache), periodically scans the
  objects in the cache and decreases this value by one each time.

  If the following criteria are met, the plan is removed from memory:

  more memory is required by the system
  the "age" of the plan has reached zero
  the plan isn't currently being referenced by an existing connection.

He also mentions earlier in the book the following:

  Once the optimizer arrives at an execution plan, the estimated plan is
  created and stored in a memory space known as the plan cache –
  although this is all different if a plan already exists in cache.

I would assume that the plan could theoretically reach zero if the actual and estimated plan differ. This would give the estimated plan execution count zero even though it was stored in the cache.

My question is What are the different scenarios that a plan age could reach zero? And am I correct in my assumption?

Fritchey, G. (2012). SQL Server Execution Plans. Springfield, USA: Simple Talk Publishing.

My question is when could the Age of a plan reach zero?

The algorithm that SQL Server uses to determine when and how plans should be removed from cache is called the eviction policy.
The cost of plan is analyzed to determine which plans gets evicted. Upon detecting memory pressure, zero cost plans are removed from the cache and the cost of all other plans is reduced by half.

For adhoc plans, the cost is considered to be zero, but it is increased by one every time the plan is reused.
For other types of plans, the cost is a measure of the resources required to produce the plan. When one of these plans is reused, the cost is reset to the original cost.
For non–adhoc queries, the cost is measured in units called ticks, with a maximum of 31. The cost is based on three factors: I/O, context switches, and memory. Each has its own maximum within the 31-tick total.

When not under memory pressure, costs are not decreased until the total size of all plans cached reaches 50 percent of the buffer pool size. At that point, the next plan access will decrement the cost in ticks of all plans by 1.
Once memory pressure is encountered, then SQL Server will start a dedicated resource monitor thread to decrement the cost of either plan objects in one particular cache (for local pressure) or all plan cache objects (for global pressure).

so the gist is ...
The Clock algorithm sweeps the cache at regular intervals. Every time an unused entry is found, the cost is decreased by some amount. If the cost is 0 and is not used, then it is removed from the cache.
Best references :

Plan Cache Internals
An in-depth look at SQL Server Memory–Part 1 and Part 2


Database Table/Column rules We are making a new database design where we are setting some rules upfront. I dunno how much it makes sense to have these rules in place,

Each table and column name should be meaningful. It should give information about the table or the column's purpose.
Table names should not exceed 27 characters and column names should not exceed 30 characters.
Table names are plural. For example: COMPANIES, EMPLOYEES, PERSONS, GENDERS, RELIGIONS, COUNTRIES, ...
Each table has a primary key as table_name_ID which is NUMBER(15).
table name is used as singular in the Primary Key column. Example:
the USER_REQUESTS table has a primary key as USER_REQUEST_ID.
If the table or column name exceeds the limits, a suitable abbreviation is used. Example: CMS_EXEMPTIONS is the table to keep content management system exemptions.
Tables that keep transactional data should end with _TRANS. Example: COMPANY_TRANS is the table that keeps company related transaction data.
Each table has 4 WHO column as :

CREATE_USER        NUMBER(15)
CREATE_DATE        DATE
MODIFY_USER        NUMBER(15)
MODIFY_DATE        DATE

Columns which are a foreign key relation are named as the primary key of the referenced table. Example: if a table has a reference to the COUNTRIES table, the referencing column is also named as COUNTRY_ID as the primary key of the COUNTRIES table.
If the meaning of the referencing column should be other than the 8th rule because of the first rule (meaningful names), meaningful name rule is applied. Ex: Both nationality and country columns have a relation to COUNTRIES table if it is used to keep a person nationality than it is named as NATIONALITY_ID.
If the table has more than one related to the same table they are given different names like BIRTH_COUNTRY_ID, PASSPORT_ISSUE_COUNTRY_ID
If a table is a relation table between to tables, the table name contains both table names.

To give meaningful names were always our first rule in naming.

This is the stuff of holy wars. There is no right answer. Further these are not "rules" of the sort that can be (easily) enforced in a CI tool chain. They are a style guide. Enforcement will be by code review and consensus, so make sure that all (or enough) of those affected agree, can live with the decision, and can justify them to the next generation and the unbelievers who will inevitably call foul.

Ask yourself why you want these rules. For me it is to impose a consistency which makes code easy to read. Very soon the eye gets used to reading a style and comprehension improves. When every query and table follows a unique style one starts from scratch on every reading. Further, rules remove the cognitive load of deciding how objects should be named. That decision's been made; done; move on to the important stuff. Right or wrong you never have to spend another second on this time-consuming low-value activity. And finally, a good style can uncover and make clear semantics within the structure without cruft.

By these measures I think your suggestions are fine.

There are a few suggestions I'd make, though. Forcing every table to have a numeric surrogate key may be unnecessary. I've touched on this here.

Extend the conventions to queries. There can be a lot of variation in select lists (leading or trailing commas?), joins (single line or clause per line?), sub queries, indentation. The team needs to think about this once and once only. A consistent layout can help make clear errors like missing join conditions.

I've seen the WHO column convention used many times. It's almost useless. What special significance does the original creator have over the most recent updater? Why is it important to know who updated the row a second ago but not who updated it three seconds ago?

If we saw a PERSONS table with columns FIRST_MAILING_ADDRESS and MOST_RECENT_MAILING_ADDRESS we, as data modelers, would have conniptions. The same applies to these WHO tables. Normalise them into their own table. If it's important to know how specific columns' values change over time these columns should be in the normalised table, too. Generally this pattern is know as temporal tables.


How to optimise request One sql request is performed 10 times longer with Postgresql than Oracle.
How can we speed up that query with Postgresql?

Hereunder the request:

select t1.c1, t1.c2 from MyTable t1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MyTable t2 where t2.c4=t1.c4);

The complete query:

select t1.c1, t1.c2 from MyTable t1
left outer join Table t3
on t1.c1=t3.c1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MyTable t2 where t2.c4=t1.c4);

In Postgres this type of query is much more efficient when done using distinct on ()

    select distinct on (c1)
         t1.c2, t1.c3
    from MyTable t1
    where MyTable.c4='string'
    order by c1, c3 desc;

Alternatively using standard SQL and a window function:

select *
from (
  select t1.c2,
         t1.c3,
         row_number() over (partition by c1 order by c3 desc) as rn
  from MyTable t1
  where t1.c2='string'
  and t1.c4 is null
) t
where rn = 1;

If you can't change the query because this is generated by an obfuscation layer (aka "ORM"), then adding the column c3 to the index idx_multi_c2_c4 and creating an index on (c1,c3) might help.


Why is SSMS inserting new rows at the top of a table not the bottom? Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using identity columns and this results in things like

id  row
42 first row
1 second row
2 third row

When rows are fetched and not explicitly ordered. This results in a different appearance when the rows are fetched for the web app and changes what a TOP 1 query returns.

I know I can order by them, but why is this happening? Most of my data is inserted through a web application, all inserts from this application result in a First In First Out ordering, e.g. latest insert is at the bottom, so the ids are all in a row. Is there some setting in the server or Management Studio that causes this improper ordering?

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.
From Craig Freedman:

Combining TOP with ORDER BY adds determinism to the set of rows
returned.  Without the ORDER BY, the set of rows returned depends on
the query plan and may even vary from execution to execution.

Always use ORDER BY if you expect a well-defined and consistent order in your result set. Never rely on how your database may store the rows on disk (e.g. via a clustered index) to guarantee a certain ordering of data in your queries.


SSRS Query Timeout vs DatabaseQuery Timeout In SSRS there are appear to be two separate query timeout options. The Query Timeout option can be found in the Dataset Properties dialog window on the Query tab. The DatabaseQuery Timeout option is found in the rsreportserver.config file. I realize the Query Timeout option is specific to the dataset, and the DatabaseQuery Timeout option is specific to Report Server.

What are the reasons for using different settings? Why not just make the Query Setting to always default to the overall report server DatabaseQuery setting? If I have a lower DatabaseQuery setting will that timeout before the Query setting is met?

Edit: I also want to know if this is in fact a serverside setting that can be overriden with the report specific setting. I can't seem to find any documentation on how these two settings are related/different.

I once supported an application that would timeout certain queries after 300ms (not a reporting system). If that application were to default to a server level setting, they wouldn't have been able to achieve the different timeouts that the business required. This is an extreme case of course, but it's an example of why you might want different settings at the server and data set level.


Do I have to retype information every time I make a mistake in SQLPlus If I make a mistake in SQLPlus while inputting an 'insert into' command, is there a way I can bring this information back up in order to edit it?

Several ways.

You can set the EDITOR environment variable before running SQL*Plus (Assuming Unix), to allow use of an external text editor (vi, by way of example):

export EDITOR=vi

Then type ed in SQL*Plus to edit the previous query in the vi editor. Of course, you may prefer nano, pico, emacs, vim etc etc.

If you don't wish to set an environment variable, you can add define _editor=vi to your $ORACLE_HOME/sqlplus/admin/glogin.sql file instead.

My favourite way of achieving SQL*Plus edit heaven (in Unix) is by using rlwrap. This adds readline support to SQL*Plus and allows you to easily edit within SQL*Plus itself.

Install rlwrap from your distributions package repository (or download & compile from the website here), then add the following to your shells startup script (.bashrc/.bash_profile or similar):

alias sqlplus="rlwrap -i sqlplus"


Combining 2 SQL queries I have these 2 queries below that I use to calculate net profits from Jan to Dec.

Query 1

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T0.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T0.[Credit] - T0.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities]
FROM JDT1 T0 WHERE (T0.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231')
AND T0.[ContraAct] <> '311201'
GROUP BY T0.RefDate) AS q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

Results:

Query 2

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T1.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T1.[Credit] - T1.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities]
FROM OACT T0 INNER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
WHERE (T1.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231')
AND T0.GroupMask > 3
GROUP BY T1.RefDate) AS q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

Results:

The difference between the 2 comes in month 12. Query 1 accurately gets the Dec profit because it is an account that stores data after a process of closing the 2015 year in December.

How do I combine the two into one query such that I get all results of query 2 except for month 12 instead of -4764758.550000 I get 3280335.630000?

I have created 2 table variables in an attempt to match your environment.
I have added an UNION ALL to your original query, one query selects all other months but december, and the second selects only december.

there are other ways to do this, but in any way, it looks like an expensive query.

here we go:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @JDT1 table(

ContraAct char(6) primary key clustered,
Account int,
RefDate datetime,
credit money,
debit money

)

declare @OACT table(
AcctCode int primary key clustered,
GroupMask int
)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311202',1,'20151231', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311203',1,'20151231', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311204',2,'20151231', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311205',2,'20151231',10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311206',1,'20151031', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311207',1,'20151031', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311208',2,'20151031', 10,7)

insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
values ('311209',2,'20151031',10,7)

insert into @OACT(AcctCode,GroupMask)
values (2,4)

/* Profit for the Period 13 */
SELECT * FROM
(
        SELECT MONTH(T0.RefDate) AS Month,
              '311999' AS 'SAP Code',
              'Period Profit' AS 'Description',
              SUM (T0.[Credit] - T0.[Debit]) AS 'Amount',
              '311200' AS 'FatherNum',
              12 AS 'Group',
              'Liabilities' [Liabilities]
        FROM @JDT1 T0
       WHERE (T0.RefDate
              BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0)
              AND '20151231')
        AND T0.[ContraAct] <> '311201'
        AND MONTH(T0.RefDate) = 12
        GROUP BY T0.RefDate

        UNION ALL

        SELECT MONTH(T1.RefDate) AS Month,
              '311999' AS 'SAP Code',
              'Period Profit' AS 'Description',
              SUM (T1.[Credit] - T1.[Debit]) AS 'Amount',
              '311200' AS 'FatherNum',
              12 AS 'Group',
              'Liabilities' [Liabilities]
        FROM @OACT T0
             INNER JOIN @JDT1 T1
                     ON T0.[AcctCode] = T1.[Account]
        WHERE (T1.RefDate
               BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0)
                   AND '20151231')
        AND T0.GroupMask > 3
        AND MONTH(T1.RefDate) <> 12
        GROUP BY T1.RefDate

) AS q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

the results, you can compare october and december.


How do cardinality estimates affect CPU and Reads in SQL Server? Trying to understand a concept, not sure where to look for answers.  I have been researching cardinality estimates and their affects on memory.  In the process of optimizing a query to get better cardinality estimates for a stored procedure I discovered some interesting data.

There was a stored procedure that the team lead of our DBAs asked that I optimize.  This particular proc was causing significant issues with our server the DBA was not sure why, he had attempted to optimize it but without any success.  The issue was the simplicity of the procedure.  It was JOINing 4 tables and returning about 20 columns.

He provided me a spreadsheet that had the data of over 13,000 executions from a particular day.  This data included the parameters for each execution, the duration, CPU, and number of reads.  I optimized it and it went live 3 days ago.  Yesterday I asked for similar data for the day after it went into production so I could see if the optimization was working.  The real issue was the original proc ran really great in test, even using the parameters of the worst runs from the spreadsheet, it only had the issue when it was in production and running so many executions in a day along side all the other procedures that were running.  This procedure and the mystery (for us) was why I starting looking into the cardinality as all the JOINs were giving me some huge discrepancies on estimated and actual rows.  This lead to me asking this question to try understand more about how statistics work and yesterday I asked this question about memory grant.

My question today stems from the data on the executions from before and after optimization.  I have tested the old procedure on the test server and if 3 of the 4 tables' indices had bigger sample sizes then the procedure would not have needed more optimization.  I spent a lot of time manipulating JOINs with additional tables and creating several temp tables and indices on temp tables in order to get better cardinality estimates.

The new optimized procedure ran great.  It ran over 54k times in an 11 hour time span for the day in question.  The average duration of each run was much better but 'only' 34% faster.  I say 'only' because the CPU was 98% less usage and the reads were 95.5% fewer.  This is why I put in the previous story. Like I mentioned, the original proc ran fine in test for a one-off execution and when compared to the new procedure I was getting 15-20% improvement on CPU and reads for some of the better executions, several executions had the same CPU and a few had worse CPU, reads or duration.

The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and reads so much?  I am looking for technical reasons and this is going to be included, if there is a correlation, in a presentation I am doing next week to try and promote better sample sizes on our indices. If there is no correlation I can accept that as an answer, but it would be nice to still know what could possible cause such a change when all that was really improved was the cardinality.

First of all, it would be really great if you could provide an actual execution plan for the query you're working on tuning.  Otherwise, all of the answers you will get will be somewhat general, and may not perfectly apply to your situation.

  The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and reads so much?

The effect is indirect.  In general, better cardinality estimates will allow the query optimizer to make better choices about the execution plan - which can result in less reads and CPU.

Consider this simple query:

SELECT *
FROM dbo.TableA a
ORDER BY a.[high];

The results should be ordered by high, and there is a nonclustered index on the high column.  Since SQL Server only expects to see 50 rows come out of the index scan, it decides to do read the data from the pre-sorted index, and do 50 key lookups to get the rest of the columns (to avoid sorting the data).

If SQL Server instead expects 10,050 rows to come out of the index scan, we get this plan instead:

It scans the clustered index, which is sorted by the number column.  It then has to sort that data by high in order to satisfy the ORDER BY part of the query.  This query has the following CPU and IO characteristics:

CPU time = 0 ms,  elapsed time = 326 ms.
Table 'TableA'. Scan count 1, logical reads 95

If I force the key lookup plan when there are 10,000 rows (in order to simulate a "bad cardinality estimate"), the query has these characteristics:

CPU time = 15 ms,  elapsed time = 305 ms.
Table 'TableA'. Scan count 1, logical reads 20768

As you can see, the bad cardinality estimate caused I/O and CPU to increase.

This is a really simplified example, but hopefully it helps to illustrate the general point.

  The average duration of each run was much better but 'only' 34% faster. I say 'only' because the CPU was 98% less usage and the reads were 95.5% fewer

If reads and CPU both dropped, but duration didn't drop as much as you expected, the cause is probably related to concurrency in the live environment.

David Browne makes this point well:

  Often this indicates a reduction in the degree of parallelism used in the query. A parallel scan across multiple cores generates a lot more CPU and Reads per second than a single-threaded plan. This is one of the problems with focusing on duration for query tuning.

If this is a parallel query, you may be getting a lower degree of parallelism (DOP) in the live environment than in your test.  Check the execution plan XML for the DegreeOfParallelism property (e.g., <QueryPlan DegreeOfParallelism="8") to see if it's different between the two environments.

The stored procedure you've tuned might be blocked waiting on other queries to release locks.  If you are able to measure wait statistics, this problem would show up as LCK_* waits.  This is a typical cause of "low CPU, high duration" problems.

Or maybe it takes a while for the stored procedure to even begin running in some cases.  If other queries are running that need large memory grants, you could be encountering RESOURCE_SEMAPHORE waits.  If there are lots of parallel queries running at once, especially if they are running for a long time and waiting on locks, you might be encountering THREADPOOL waits.

Here are some resources that might help track down what's going on in production for this situation:

Use the SQL Server First Responder Kit

run sp_Blitz to get a general sense of what's going on on the server, and see if there are any of those really bad waits
run sp_BlitzFirst during representative times of the day (when issues are occurring) to get info on waits, what queries are running, etc

If you want to reproduce the query results I showed in order to play around with this yourself, here are the definitions:

CREATE TABLE [dbo].[TableA]
(
    [name] [nvarchar](35) NULL,
    [number] [int] NOT NULL,
    [type] [nchar](3) NOT NULL,
    [low] [int] NULL,
    [high] [int] NULL,
    [status] [int] NULL
);
CREATE CLUSTERED INDEX CX_number
ON dbo.TableA ([number]);

CREATE NONCLUSTERED INDEX IX_high
ON dbo.TableA ([high]);

INSERT INTO dbo.TableA
SELECT TOP (10000) v1.*
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2;

SET STATISTICS TIME, IO ON;

SELECT *
FROM dbo.TableA a
ORDER BY a.[high];

SELECT *
FROM dbo.TableA a WITH (INDEX (IX_high)) -- force the key lookup plan
ORDER BY a.[high];


loss of all redologs scenario I'm having this question regarding a restore scenario in RMAN:

A database is running in Archivelog mode. Full RMAN hot backups are taken, redologs are backed up with RMAN to tape. The application remains online while any backup is taken.

Enter this scenario:
01h30 Redologs are backed up to tape using RMAN
    02h00 a Full backup starts
    08h00 the Full backup ends (and it was 100% successfull)
    08h05 ALL Redologs on disk, including multiplexed copies, are permanently lost. Additionally, one (1) regular datafile is lost.

Note the backup itself took a long time : assume that is normal. During that same time, there was activity (as stated above, the application was running), but there really was not much happening. Still, redologs were written in that period (the same redologs that were lost).

And this is the question I'm wondering now :  If we just restore the complete database, on purpose not specifying any time to restore to, it is correct to say the the restore will restore to a point in time, just after 02h00 ?

No.

The rman command 'BACKUP FULL ...'  makes a backup of the datafiles but does not backup any archivelogs. If you want to backup archivlogs you have to make an archivlog backup too. This can be done by adding the 'PLUS ARCHIVLOG' clause to the 'BACKUP FULL' command or by issuing a 'BACKUP ARCHIVELOG' command after the 'BACKUP FULL' command.

Online backup of datafiles are useless if you do not have an archive log backup that covers the time of the online backup.
If I understand your scenario description, you have lost all archivelogs/redologs that cover the time after 01:30 and you haven't yet made a backup of these archives. Then you can only restore the all datafiles from a backup finished before 01:30 (maybe in the previous night) and then apply all archivelogs from the beginning of this backup until 01:30.
(I assume that you have a backup of all the archives generated in this period of time).

So you can recover the database until 01:30.

--

some consequences:
 1 ) datafiles and archivelogs should be placed on different disks. If the data disk crashes you still have all archives even if you haven't backuped them to tape.
 2) redo logs should not be placed only on data disks. Duplex them because they are so important. Place at least one copy to other disks than data disk.
3) backup your archive logs regularly. Even during other back operations like a long during fullbackup make the archivelog backup parallel to these operations.


Wasted space in MDF file: Should I just use SHRINKFILE or some other alternative? I've got a database which has an 850GB MDF file. Over the course of 20 months a logging mechanism in the application created a variety of huge tables. The maintenance script which was only supposed to retain a weeks data at any one time wasn't executing properly (it is now) so we had to manually clean things up.

We actually cleared about 600GB worth of data. I want to free up some of this space as the drive it is on is near limit. The data file will never grow this large again so it's just wasted space.

I was planning to use SHRINK-FILE to deal with this. I have tested on a clone of the database it takes around 3 hours. There doesn't seem to be any performance degradation on the database. Yeah, the indexes are heavily fragmented afterwards but I can sort those.

My question is this:

The application needs to remain 'up' throughout this process as it's critical. I understand that the SHRINKFILE operation is a fully online one, or am I wrong?

The database has a large amount of write activity occurring pretty much every second.

Will the increased I/O caused by the SHRINKFILE operation affect these writes?

Turning this on its head, will the continuous writes affect the SHRINKFILE operation, i.e. make it slower to complete?

Finally, is there a better way to do this?

  Update

  Just for anyone reading this - I ran SHRINKFILE and monitored I/O and
  CPU. There was a slight rise in both but no latency issues at all with
  my high-writes-per-second database. It remained up, stable and
  trouble-free throughout the 1.5 hour SHRINK operation.

  Indexes were horribly fragmented afterwards which gave the reporting
  side of the application a little grief (graphs were slow to render on
  the client application) but an Online index rebuild later and
  everything was back to normal.

It's impossible for us to know if it will affect the application, and it will be extremely difficult for you to determine this either. Even with a 100% like-for-like system and distributed replay clients, you would not notice the millisecond impact the disk IO has, and you would not be able to tell that the application itself is sensitive to that.

The only certainty is if the vendor themselves provides you a solution to prove it one way or the other (i.e. the application itself can do its own load testing) and IMHO very few vendors have such a thing.

Now onto the rest.

Shrink file on a data file is doing a lot IO and this could affect an extremely latency sensitive application - but I've never seen it. If your backups aren't killing you then it's fairly unlikely that this will kill you either. You'd have to already be living on a knife edge of tolerances.

As a DBA you should always cover yourself by letting people know what's going on (either formally through a change ticket or informally through other means). Let them know there's no known or expected impact but that you're available to stop it on a dime in the off chance anything does occur. Shrink file does stop immediately if you cancel it.

But otherwise for me it's a business as usual change with no impact. I'm not in finance or health care so your standards may be higher.

Here's what you do need to know though:

You'll be generating a lot of log records. This means you better be in simple mode, or full mode with very frequent (every few minute) log backups, to ensure you don't start filling your disk.
If you're in full mode you'll have a massive set of DIFF backups (if you do them) up until the next full backup.
On the weekend, or whenever your index maintenance window is, you're going to hit the same case of massive log backups and DIFF backups. This might be a problem if your maintenance does rebuilds and not just reorganisation; rebuilds could put you outside of your maintenance window, will take a massive amount of log or tempdb space while running, and blow up to hundreds of GB. If they're just doing reorganisation though (i.e. no outage) then you'll just see the large log and DIFF backups.

Now all of those latter things definitely can cause outages. But if you have plenty of backup space and disk space for your data and log files, and every-few-minute log backups, and are only doing reorgs, and have sensible small non-percentage based log growth sizes set, then you're unlikely to run into any trouble.


Query table A for any rows that are like any search term in table B I need some help writing a select statement (I still don't think in sets!). Here's the scenario:

I have table A with column "Name varchar(20)". I have table B with column "SearchTerm varchar(20)". I want to query A for any rows that are like any search term in B.

Let me give specific data:

Table A has:

Jay
Jim
Frank
George

If table B has 'a', then the results would be:

Jay
Frank

If table B has 'j', then the results would be:

Jay
Jim

If table B has 'y' and 'g' (two rows), then the results would be:

Jay
George

If table B has 'Jay', then the results would be:

Jay

I actually want to be able to apply this idea to be able to search two columns, FirstName and LastName, but I'm guessing that once I see the idea, I'll be able to generalize the solution to search multiple columns.

...
FROM
  TableA
  JOIN
  TableB ON
          TableA.FirstName LIKE '%' + TableB.SearchTerm + '%' OR
          TableA.LastName LIKE '%' + TableB.SearchTerm + '%'
....

Now, this isn't going to be the best of queries. Consider full text search instead (I'm not too familiar with it)

This OR could be split into a UNION


How I should design tables for transaction (payment) for banking? My team and I are trying to design a new simple database system to manage transaction of customers who use their debit cards. These are some problems we haven't yet decided:

When it comes to 'payment by card' process, there should be a transaction to deal with it, there might be pending status before the process will have completed. So it is necessary to create 2 separate tables, one of which manage the transaction status from that payment , and the other only about the successful result after the transaction completes. Or is there any other way around?

We have considered a 'isPending' field (bollean or bit) should be added but since we have not decided which table should contains it so it is still a problem. But anyway, assuming that the tables have completed, should this filed only update (.e.g from true to false, 1 to 0,etc) or there should be a new records should be created?

We hope that our questions do not confuse you. Please give some recommendation to help our system more improved! Thank you! (We just hope that we do not post in the wrong section, so if that was the case, we would be very sorry)

i think there are a lot of different solutions in the field.
There are different workflows on wirecard, Computop, Saferpay and so on.
Basicly i prefer these steps.

Store the initial Step (init payment) with timestamp, useragend... (Table A)
Put this id to the Payment-Request to the prowider as an optional field.
Often you get an response to yor success-url and to a notify-url. I fetch the full response and store it as a json string ( for debug ) (Table B)
Update Table A with the encrypted data from the Response ( SUCCESS|FAIL ... )
If success, update the shopping basket
If you have problems, you need as much information as you can have. Specially for seamless payment.


mysql-community-server5.7.18 conflicts file /etc/my.cnf with Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 When i tried to install percona-xtrabackup on Master server db
yum install percona-xtrabackup-24

I got this error:
 file /etc/my.cnf from install of Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 conflicts with file from package mysql-community-server-5.7.18-1.el6.x86_64

How to solve this problem?
-CentOS 6.5  2.6.32-431.el6.x86_64

try:

Download mysql-community-libs-compat from mysql (https://dev.mysql.com/downloads/mysql/)

rpm -i mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm

Install perl-DBD-MySQL

yum install perl-DBD-MySQL

Install percona-xtrabackup

yum install percona-xtrabackup


How to avoid IX deadlock in check constraint I have following situation, I have a table with a primary key, and a constraint that I cannot have rows with specific requirements. For demonstration purposes, here I have constraint that doesn't allow to insert duplicate values in N column. In real case, it checks several columns using foreign keys to other tables and with additional filters, so I cannot put simple unique constraint.
So here is example
create table dbo.T1 (
    Id int not null identity (1,1),
    N int not null
)

alter table dbo.T1
add primary key (Id);

go

create function [dbo].[fn_CheckN](@id int, @n int)
returns int
as
begin
    if exists (select * from dbo.T1 t where t.n = @n and t.Id != @id)
        return 0

    return 1
end

go

alter table [dbo].T1 with nocheck add  constraint [CK_T1_Valid] check  (([dbo].[fn_CheckN]([Id],[N]) = 1))
go

alter table [dbo].T1 check constraint [CK_T1_Valid]
go

When I run concurrently
insert into dbo.T1 (N)
values (@i)

I get this deadlock S -> X, X -> S on primary key. And that I kind of understand why. Deadlock xml:
https://pastebin.com/hceR3sum
My first attempt to fix this was to grab S lock first
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablock, holdlock))

insert into dbo.T1 (N)
values (@i)

commit

But it failed with this deadlock S -> IX, IX -> S. Can somebody explain what is going on? Deadlock xml: https://pastebin.com/mLXJb59C.
And I fixed it with locking entire table with X lock. Is it ok? Is there a better approach?
begin tran
declare @lock int = (select top(1) 1 from dbo.T1 with (tablockx, holdlock))

insert into dbo.T1 (N)
values (@i)

commit

If I put index on N column I get this deadlock https://pastebin.com/KJGmLDhH. The real requirement is pretty much the same, the simplest case there are 4 columns with accountIds and enabled flag, and when something happens to the record I have to check that account id is unique among all enabled records. or if it's disabled that I have nothing to check. Something like that.
I use C# to run queries simultaneously
class Program
{
    private const string connectionString = "Server=.;Database=Performance;Trusted_Connection=True;MultipleActiveResultSets=True; Max Pool Size=3000";

    static async Task Main(string[] args)
    {
        await ClearAsync();
        await Task.WhenAll(Enumerable.Range(0, 100000).Select(async i => await InsertAsync(i)).ToArray());
        Console.WriteLine("Done");
        Console.ReadKey();
    }

    public static async Task InsertAsync(int i)
    {
        using var connection = new SqlConnection(connectionString);
        using var cmd = new SqlCommand(@"
insert into dbo.T1 (N)
values (@i)
", connection);
        await connection.OpenAsync();
        cmd.Parameters.Add("@i", SqlDbType.Int).Value = i;
        await cmd.ExecuteNonQueryAsync();
    }

    public static async Task ClearAsync()
    {
        using var connection = new SqlConnection(connectionString);
        using var cmd = new SqlCommand("delete from dbo.T1", connection);
        await connection.OpenAsync();
        await cmd.ExecuteNonQueryAsync();
    }
}

I use Microsoft SQL Server Express (64-bit) 13.0.1601.5

If you don't provide an index on column N, SQL Server cannot check if the target value exists efficiently, and must scan the table. The scan continues until a match is found, or scans the entire table if no match exists (your target case). This is terribly inefficient, and happens per row.
At the default locking read committed isolation level, the scan also usually means acquiring and releasing shared locks on every row while they are tested for a match.
Depending on the order in which rows are inserted (and so exclusively locked) on different connections, the competing activities will lead to extensive shared-exclusive blocking or deadlocking.
So you need a nonclustered index on N, such as:
CREATE NONCLUSTERED INDEX [IX dbo.T1 N]
ON dbo.T1 (N);

With an index
You may still encounter deadlocks when the index is present (as you have seen). This is because when the table is small, SQL Server may choose to scan the nonclustered index rather than seeking to the desired value of N and then seeking for id > @id OR id < @id.
This can lead to blocking or deadlocks in a similar way to the original case. The exact interactions are a little more complex because SQL Server will normally choose to insert the new row in the clustered index and nonclustered index before checking the constraint using the scalar function. (It is possible to avoid this in the demo scenario by forcing SQL Server to insert into the nonclustered index after checking the constraint, but I don't want to get into that.)
A workaround
Let me just say that all but the most expert practitioners should avoid enforcing check constraints with scalar functions. There are just too many quirks and hidden gotchas. You will likely end up with invalid data when constraints don't work as expected, or indeed even fire at all.
Bear in mind that most people think they are a great deal more expert than is truly the case. That said, and for educational value only, it is possible to avoid the deadlock scenario in the demo by forcing seek access on the nonclustered index.
I have also added a READCOMMITTEDLOCK hint because validating constraint must be done using the most recent committed values, not row versions. The demo fails to ensure uniqueness when read committed snapshot isolation or snapshot isolation is in use, because the function may read out-of-date data.
CREATE FUNCTION dbo.fn_CheckN
(
    @id integer,
    @n integer
)
RETURNS integer
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        CASE WHEN EXISTS
            (
                SELECT 1
                FROM dbo.T1 AS T
                    WITH (READCOMMITTEDLOCK, FORCESEEK)
                WHERE T.N = @n
                AND T.Id != @id
            )
            THEN 0
            ELSE 1
        END;
END;

This is still not robust code, and not fit for production use. It does address the issue shown in the question.
Depending on the real requirements, you might be able to use triggers, denormalized constraints, or an indexed view instead.
Other remarks

HOLDLOCK does not mean hold locks. SQL Server always holds locks long enough to guarantee correctness given the query specification and configuration settings (including isolation level). HOLDLOCK is a synonym for SERIALIZABLE. It is an advanced option for expert users to specify serializable isolation semantics for a particular object, while using a different level on other objects in the same transaction.
TABLOCK ensures only object-level locks are taken. TABLOCKX ensures only exclusive object-level locks are taken. Both will tend to completely serialize access to the specified object, which is great for avoiding contention — but only because you now have no concurrency at all.
The clustered index identity pattern will create a very hot page at the end of the base table. Concurrent inserts will all be targeting that one page, resulting in latch contention, and a reduction in throughput.
Your demo code specifies MARS but you are not using it. Instead, you create a new connection for each single insert. I realize it is just a quick test rig, but still.

Examples of scalar function check constraints not behaving as expected (some links are quite old, but the described behaviour is still current):

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates by Alex Kuznetsov
Using a UDF in a check constraint to check validity of history windows (start - end date windows) by Tony Rogerson
Be careful with constraints calling UDFs by Tibor Karaszi
Snapshot isolation: A threat for integrity? by Hugo Kornelis
Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints by Erik Darling
Is there any benefit to SCHEMABINDING a function beyond Halloween Protection? by me

Note also that scalar functions used in check constraints are not eligible for inlining on SQL Server 2019+.


SQL Replication: Which tables/views should be a part of replication? We need a reporting Database & the Production Database.

The reporting team is unsure of which tables in the database are used in the reporting tools and hence wants us to replicate the complete database to the reporting.

I would like to understand what will be the pros and cons of replicating the complete database?

What I think:

Pros: The reporting databases will serve as a disaster recovery option too.

Cons: More disk consumption on Reporting Server, higher resource utilization and more difficult maintenance.

Anything more?

I have replicated entire databases, performance is an issue with frequently changed data when your talking larger volumes. For us, a larger amount of data was 500gb of data being supported by 32gb of memory and 8 cores (didn't have control or say in hardware specs). We had transactional replication, and about 1-2 times a month it would get behind, far enough behind where we had to re-snapshot it to get our subscriber current (this would take us about 4 hours each time). Whenever replication had an issue and data started to queue up, it just couldn't catch up.


Oracle restrict user to use USERS tablespace I have a schema 'TEST' that schema has own tablespace 'TEST'. But when I create index on USERS tablespace, it created. How can I restrict to use USERS tablespace in ORACLE database?

CREATE UNIQUE INDEX "TEST"."INDEX1" ON "TEST"."TESTTABLE" ("TESTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

It success, I want TEST user can't create any object on USERS tablespace. It has own TEST tablespace. Is there any configuration? Should user use TABLESPACE "TEST" instead of TABLESPACE "USERS" ?

The user TEST may have the UNLIMITED TABLESPACE privilege, or a quota defined on the USERS tablespace. To revoke them:

revoke unlimited tablespace from test;
alter user test quota 0 on users;

If the user had the UNLIMITED TABLESPACE privilege without any quota defined, you need to define a quota on the TEST tablespace, so the user can use it again after the above commands:

alter user test quota unlimited on test;


MySQL concurrent INSERTs I have a MySQL database with InnoDB tables.
There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) statements.
What I fear is a possible concurrent access to data causing only INSERTs and no UPDATEs.
Is LOCK Table WRITE the only solution?

It looks like you need SELECT ... FOR UPDATE

Pseudo code:

START TRANSACTION;
SELECT some_column INTO @value FROM table_name WHERE ... FOR UPDATE;
IF @value = 'some value' THEN
  INSERT INTO table_name VALUES(...);
END IF;
INSERT INTO referenced_table VALUES(...);
COMMIT;


Scheduling jobs on a SQL Always On HA cluster When scheduling jobs using SQL Server Agent for AOHA cluster, does one use a Master-Target config as you would as if the servers were separate?  Or is there a better way to distribute the jobs to all of the servers so that they can be picked up by one of the replicas in case the primary server fails?

We create the jobs (and sync them as well) across all the instances that are part of AlwaysON AG.

There is an additional logic that you should put in the job to check if the instance is primary and then only run the job. A pseudo code would be like below :

-- http://dba.stackexchange.com/a/45152/8783
if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

To sync logins, jobs, etc, you can use PowerShell or this utility from SQLSkills (not sure if it works for 2014 !).


Moving MSDB database files fails I'm trying to relocate system databases files.
I managed to do that with the tempdb database, but I'm stuck with msdb.

When I run:

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id IN DB_ID('msdb')

it shows that the logical names are  'MSDBData' and 'MSDBLog'. Then when I run below mention TSQL query

alter database tempdb modify file (name = MSDBData, filename = 'C:\Data\MSDBData.mdf')

I am getting the following error: MODIFY FILE failed. File 'MSDBData' does not exist.

MS Sql 2008 R2 server version: 10.50.1600

First, you should use:

... WHERE DB_NAME(database_id) IN ('msdb', 'xxx', ...)

or

... WHERE database_id = DB_ID('msdb')

Syntax for ALTER DATABASE is:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

In your case values are as follow:

database_name = MSDB
logical_name = MSDBData (or MSDBLog)
'new_path\os_file_name' = 'C:\Data\MSDBData.mdf'

Then you can follow this procedure for MSDB:

execute ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData, FILENAME = '...new path' )
and/or execute ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog, FILENAME = '...new path' )
stop SQL Server service
Move MSDBData and/or MSDBLog to their new location
Start SQL Server service
Check new file location

SELECT name, physical_name AS current_file_location
FROM sys.master_files
WHERE database_id = DB_ID('msdb')

You must replace value such as MSDB, file location, file name with the correct values for your environment.


How to insert values through inner join Could anybody tell me how to do insert statement with inner join.

i am trying to insert
ProductName,ProductCompany,ProductPrice,ProductQty in Product table and CategoryName in category Table. And i also want to fetch Category_id to the Category_id_FK.

I am using SQL Server 2008

First add Category information
Get ID number against that Category using function SCOPE_IDENTITY() and save value to a variable
Then use product detials and that varible to insert values in Product table

.

DECLARE @CategoryName VARCHAR(200),
        @Category_id INT

SET @CategoryName='CategoryName1';

INSERT INTO Category(CategoryName)
SELECT @CategoryName;

SELECT @Category_id= SCOPE_IDENTITY();    ---

INSERT INTO Product(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
SELECT 'ProductName', @Category_id , 'ProductCompany', 'ProductPrice', 'ProductQty';


JDBC Read uncommitted in SQL Server If I set the jdbc isolation level to read uncommitted in SQL Server, will SQL Server ever lock any tables, pages, rows etc?

Thanks.

Read uncommitted from Books Online:

  Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

So no it doesn't lock tables, rows or pages. However, any SELECT (even with READ UNCOMMITTED set) will issue a Sch-S (schema stability) lock, which basically means no schema changes can happen while the SELECT is executing (schema changes = add/drop column, change datatype of a column, change nullability of a column and a couple other operations I can't think of off the top of my head). A Sch-S lock should not interfere with DML statements (SELECT/INSERT/UPDATE/DELETE).


improve performance of insertions using MySQL for 10 million records in 5 tables *env: windows 10 version mysql 5.7 Ram 32GB ide : toad mysql*

Count of records is 11308147.

I have 5 like tables

I have sufficient hardware requirement but issue is the performance of insert into simple table that does not have any relation ships. I need to have index on the table.

table structure

 CREATE TABLE `2017` (
      `MOB_NO` bigint(20) DEFAULT NULL,
      `CAF_SLNO` varchar(50) DEFAULT NULL,
      `CNAME` varchar(58) DEFAULT NULL,
      `ACT_DATE` varchar(200) DEFAULT NULL,
      KEY `2017_index` (`MOB_NO`,`ACT_DATE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am using below for inserting the records into table. with out index it took around 45 min with out indexing and with out set statements where as with indexing it took 22 hrs still going on.

   SET autocommit=0;
    SET unique_checks=0;
    SET foreign_key_checks=0;
    LOAD DATA LOCAL INFILE 'D:/base/test/2017/2017.txt'
    INTO TABLE `2017` COLUMNS TERMINATED BY '|';
    commit;

I have seen suggestion to change cnf file, Could not find any in my machine. I would request some one to help me in this concern. I have been working on this for past 7 days but there is no improvement in the performance.

A date in a VARCHAR?  That will probably be a nuisance later.
No PRIMARY KEY?  Naughty.  If it is a "mapping" table, see my tips and justficications
What order are the rows in?  If the incoming data is roughly in the same order as some index, then that index will be more efficiently built.  (This won't explain 22 hours.)
What is the value of innodb_buffer_pool_size, and how much RAM is there?  For 4GB of RAM, I recommend innodb_buffer_pool_size 2G
key_buffer_size applies to MyISAM, not InnoDB.  Use 20M if not using MyISAM.

Adding the secondary key after the LOAD will probably make it run faster.  However, it won't explain 22 hours.  In InnoDB, any KEY other than PRIMARY KEY is a 'secondary key'.  Currently (mob_no, act_date) is a secondary key.  (but it may be come PRIMARY if this is a mapping table).