How can I add "in" keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012 I want the parameters in Age and Gender to accept more than one value

    Create Procedure [Admission].[SelectPlayerData]

@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish varchar(70),
@Age varchar(200),
@Gender varchar(100),
@Mobile1 varchar(20),
@Mobile2 varchar(20)
as
set nocount on
begin
 Declare @BaseQuery nvarchar(max)
='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish,
                         Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
FROM            Evaluation.Players INNER JOIN
                         Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
    , @ParamList nvarchar(max) = N'@p1 varchar(20), @p2 varchar(70), @p3 varchar(70),@p4 varchar(200), @p5 varchar(100), @p6 varchar(20) , @p7 varchar(20)'
    , @WhereClause nvarchar(max) = ' WHERE 1=1';

IF @MembershipNumber IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = @p1';
    END

IF @PlayerName IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = @p2';
    END

IF @PlayerNameEnglish IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = @p3';
    END

IF @Age IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in (@p4)';
    END

IF @Gender IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in (@p5)';
    END

    IF @Mobile1 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = @p6';
    END

    IF @Mobile2 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = @p7';
    END

    SET @BaseQuery = @BaseQuery + @WhereClause;

    EXECUTE sp_executesql @BaseQuery, @ParamList,
     @p1 = @MembershipNumber,
     @p2 = @PlayerName,
     @p3 = @PlayerNameEnglish,
     @p4 = @Age,
     @p5 = @Gender,
     @p6 = @Mobile1,
     @p7 = @Mobile2;

end

the following execution does not work properly

USE [SportsActivities]
GO

DECLARE @return_value int

EXEC    @return_value = [Admission].[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = N'12,14',
        @Gender = N'Male,Female',
        @Mobile1 = NULL,
        @Mobile2 = NULL

SELECT  'Return Value' = @return_value

GO

I think that you need to split up the male + female values, so you can add the ' and the comma's characters for each gender. If you are on sql 2016 you could use string split:

Example for SQL Server 2016 and up:

DECLARE @gender nvarchar(255)= N'Male,Female';
DECLARE @genderlist nvarchar(1000);
SET @genderlist=(select ''''+ value+''',' from string_split(@Gender,',')
FOR XML PATH(''));
select @genderlist = left(@genderlist,len(@genderlist)-1);
select @genderlist;

SQL Server 2012 Example

declare @gender nvarchar(255)= N'Male,Female'
declare @genderlist nvarchar(1000)
declare @genderlist2 table (genders nvarchar(1000))
declare @temp int
INSERT INTO @genderlist2(genders)
SELECT Split.a.value('.', 'NVARCHAR(MAX)')  as a

FROM
(
    SELECT CAST('<X>'+REPLACE(@Gender, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X')
 AS Split(a)

 set @genderlist=(
select ''''+genders+''',' from @genderlist2
for xml path(''))
select @genderlist = left(@genderlist,len(@genderlist)-1);
select @genderlist

Source

Another issue apart from splitting your string:

One other issue found, is that you need to keep the variables out of the where string created, otherwise the variable will not change to what it is holding. E.G. @p4 would have stayed @p4 instead of parsing to 12,14. I changed it to @Age in my example.

SQL Server 2012 Full procedure

   ALTER Procedure [Evaluation].[SelectPlayerData]

    @MembershipNumber varchar(20),
    @PlayerName varchar(70),
    @PlayerNameEnglish varchar(70),
    @Age varchar(200),
    @Gender varchar(100),
    @Mobile1 varchar(20),
    @Mobile2 varchar(20)
    as
    set nocount on
    begin
     Declare @BaseQuery nvarchar(max)
    ='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish,
                             Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
    FROM            Evaluation.Players INNER JOIN
                             Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
        , @ParamList nvarchar(max) = N'@MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish varchar(70),@Age varchar(200), @Gender varchar(100), @Mobile1 varchar(20) , @Mobile2 varchar(20)'
        , @WhereClause nvarchar(max) = ' WHERE 1=1';

    IF @MembershipNumber IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = '+@MembershipNumber+'';
        END

    IF @PlayerName IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = '+@PlayerName+'';
        END

    IF @PlayerNameEnglish IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = '+@PlayerNameEnglish+'';
        END

    IF @Age IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in ('+@Age+')';
        END

    IF @Gender IS NOT NULL
        BEGIN

        declare @genderlist nvarchar(1000)
        declare @genderlist2 table (genders nvarchar(1000))
        declare @temp int
        INSERT INTO @genderlist2(genders)
        SELECT Split.a.value('.', 'NVARCHAR(MAX)')  as a

        FROM
        (
            SELECT CAST('<X>'+REPLACE(@Gender, ',', '</X><X>')+'</X>' AS XML) AS String
        ) AS A
        CROSS APPLY String.nodes('/X')
         AS Split(a)

         set @genderlist=(
        select ''''+genders+''',' from @genderlist2
        for xml path(''))
        select @genderlist = left(@genderlist,len(@genderlist)-1);

            SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in ('+@genderlist+')';
        END

        IF @Mobile1 IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = '+@Mobile1+'';
        END

        IF @Mobile2 IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = '+@Mobile2+'';
        END

        SET @BaseQuery = @BaseQuery + @WhereClause;

       EXECUTE sp_executesql @BaseQuery, @ParamList,
         @MembershipNumber = @MembershipNumber,
         @PlayerName = @PlayerName,
         @PlayerNameEnglish = @PlayerNameEnglish,
         @Age = @Age,
         @Gender = @Genderlist,
         @Mobile1 = @Mobile1,
         @Mobile2 = @Mobile2;

    end

SQL Server 2016 Full procedure

     ALTER Procedure [Evaluation].[SelectPlayerData]

@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish varchar(70),
@Age varchar(200),
@Gender varchar(100),
@Mobile1 varchar(20),
@Mobile2 varchar(20)
as
set nocount on
begin
 Declare @BaseQuery nvarchar(max)
='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish,
                         Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
FROM            Evaluation.Players INNER JOIN
                         Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
    , @ParamList nvarchar(max) = N'@MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish varchar(70),@Age varchar(200), @Gender varchar(100), @Mobile1 varchar(20) , @Mobile2 varchar(20)'
    , @WhereClause nvarchar(max) = ' WHERE 1=1';

IF @MembershipNumber IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = '+@MembershipNumber+'';
    END

IF @PlayerName IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = '+@PlayerName+'';
    END

IF @PlayerNameEnglish IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = '+@PlayerNameEnglish+'';
    END

IF @Age IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in ('+@Age+')';
    END

IF @Gender IS NOT NULL
    BEGIN
    DECLARE @genderlist nvarchar(1000);
    SET @genderlist=(select ''''+ value+''',' from string_split(@Gender,',')
    FOR XML PATH(''));
    SELECT @genderlist = left(@genderlist,len(@genderlist)-1);

        SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in ('+@genderlist+')';
    END

    IF @Mobile1 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = '+@Mobile1+'';
    END

    IF @Mobile2 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = '+@Mobile2+'';
    END

    SET @BaseQuery = @BaseQuery + @WhereClause;

   EXECUTE sp_executesql @BaseQuery, @ParamList,
     @MembershipNumber = @MembershipNumber,
     @PlayerName = @PlayerName,
     @PlayerNameEnglish = @PlayerNameEnglish,
     @Age = @Age,
     @Gender = @Genderlist,
     @Mobile1 = @Mobile1,
     @Mobile2 = @Mobile2;

end

Test 1

create schema Evaluation
        create table Evaluation.Players(       PlayerID int,  MembershipNumber int,  PlayerName nvarchar(255),  PlayerNameEnglish nvarchar(255),  Age varchar(200),
                          Mobile1 char(15),Mobile2 char(15) , GenderID tinyint)

        create table Evaluation.Gender(GenderID tinyint, GenderEnglish nvarchar(255))

        insert into Evaluation.players VALUES (1,1,'Randi','Randi',12,123214215215,12421421422,1)
        insert into Evaluation.players VALUES (2,2,'Gandalf','Gandalf',142,123214215215,12421421422,1)
        insert into Evaluation.players VALUES (3,3,'Gandalf','Frodo',14,123214215215,12421421422,1)

        insert into Evaluation.gender VALUES(1,'Male')
        insert into Evaluation.gender VALUES(2,'Female')

DECLARE @return_value int

EXEC    @return_value = Evaluation.[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = '12,14',
        @Gender =  NULL,
        @Mobile1 = NULL,
        @Mobile2 = NULL
   SELECT  'Return Value' = @return_value

Result

>  PlayerID MembershipNumber    PlayerName  PlayerNameEnglish   Age GenderEnglish   Mobile1 Mobile2
>     1 1   Randi   Randi   12  Male    123214215215    12421421422
>     3 3   Gandalf Frodo   14  Male    123214215215    12421421422

Test 2

Extra insert

INSERT INTO Evaluation.players VALUES (4,4,'Emma','Watson',12,123214215215,12421421422,2);

Execute proc

 DECLARE @return_value int
EXEC    @return_value = Evaluation.[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = '12,14',
        @Gender =  'Male,Female',
        @Mobile1 = NULL,
        @Mobile2 = NULL

Result

PlayerID    MembershipNumber    PlayerName  PlayerNameEnglish   Age GenderEnglish   Mobile1 Mobile2
1   1   Randi   Randi   12  Male    123214215215    12421421422
3   3   Gandalf Frodo   14  Male    123214215215    12421421422
4   4   Emma    Watson  12  Female  123214215215    12421421422


How to understand I/O bottleneck for SQL Server I'm getting into the dba world for SQL Server via "trial by fire", and I have a IO question.

I have a SQL Server 2016 engine built upon Windows Server 2012.  This setup includes:

128Gb Ram
6Gb/s Megaid SAS controller

4 x 600Gb 15KSAS drives in RAID 5 on a 2-backplane configuration with 6Gb/s connection:

2 x 1Gb Network Connection (Fiber I believe)

So here's my question:

if everything is connected via 6Gb/s SAS connections, why is my IO only at the 5 to 6Mb/s speed resulting in a continuous PAGEIOLATCH_EX suspension?  This is where I'm confused.  This includes SQL Server Read/Write, and Operating System negotiation.

Any ideas on how to figure this one out would be greatly appreciated.

Thank you very much.

If you're using good ol' fashioned spinning rusty frisbees, also known as magnetic hard drives, this isn't an unusual number to see when you're doing random reads and writes. You can outpace those drives with a simple USB 3 flash drive.

To find out for sure, run the portable edition of CrystalDiskMark on your server during a maintenance window. Test the C drive, test where data is stored, where logs are stored, etc - and then compare it to your laptop's SSD. You might be surprised at how much slower random reads & writes are on this array of spinning rusty frisbees.


Oracle listagg forces SORT (GROUP BY) execution plan I have the following query

SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
, listagg(type, ', ') within group (order by null) types
FROM test
group by id

Both type and id are short strings.
The execution plan for when I comment out listagg and leave count aggregation in is a HASH GROUP BY (even without the hint) and works fast.
With the listagg aggregation Oracle always chooses SORT GROUP BY which is an order of magnitude slower.  Is there any reason for that?

The queries are an order of magnitude different !

The access paths themselves shouldn't have such an impact but you're comparing the simplest aggregation function (COUNT(*)) to one of the most complex (LISTAGG) !

Furthermore, you have specified an ORDER BY clause in your LISTAGG, this will force Oracle to sort, which explains the optimizer decision to ignore your hint (this hint is also undocumented as far as I can tell).

If you wish to compare the different access paths, use the exact same query with different hints, eg:

SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
FROM test
group by id

and

SELECT /*+ NO_USE_HASH_AGGREGATION */ id
--, count(id)
FROM test
group by id


Ideal way to return a scalar from function (RETURN vs. SELECT)? There's a blog for SQL Server that says returning a scalar using SELECT is wasteful:

  SELECT should be used for returning resultsets, not scalars.  This procedure uses a SELECT statement to return a single value to the client.  This is inefficient because most applications will have to prepare additional objects (typically referred to as "recordsets") and other support in order to consume the result.  While it is certainly valid syntax to use SELECT to return scalar values, this does not need to be common in production code.  This is the kind of thing that can make it slightly harder for high-end applications to scale.

Does this apply to PostgreSQL as well? Should functions always use RETURN for outputting scalars, or is it ok to use SELECT whenever it's more convenient?

I'd prefer to use RETURN rather than RETURN QUERY SELECT in pl/pgsql.

But there's no need to jump through hoops. The cost of returning results with SELECT is negligible compared to other costs in pl/pgsql procedures.

Everything is a result-set in PostgreSQL anyway, even a single scalar, so you're not saving much with RETURN.


User unable to edit a subscription after migration of SSRS from 2008 R2 to 2017 I performed a migration of our SSRS 2008 R2 instance to a brand new SSRS 2017 instance.  This was accomplished via Microsoft's documented instructions "Migrate a Reporting Service Installation (Native Mode)":  Backup/Restore of the DBs, setup of the RSExecRole in the system DBs with permissions, import the encryption keys for the old server.  Everything is working well and we're slowly turning on reports in the new environment and turning off in the old after testing.

Everything seems to be working except for this one thing:  We give our DOMAIN\Domain Users group access via Site settings.  It's a System User.  And, for a particular folder of reports, we have a user who is explicitly added to that folder by domain account name with permissions more than sufficient to edit subscriptions.  And, this still works fine.  However, when a user who did not have this explicit access/permissions on the old server was added to the folder on the new server in the same fashion, he is not able to edit an existing subscription.  The error is "The permissions granted to user 'DOMAIN\USER' are insufficient for performing this operation"  This is despite the user not only being a member of all the same roles as the existing user but being added to the System Administrators role in Site settings. (!)

The error occurs when the user clicks the "Edit" link for the subscription.  The subscription page begins to open but the error dialog appears and the page is greyed out.  The user can create a subscription (or at least get the page to open).  It doesn't matter whether the subscription is data driven or standard. It doesn't matter if the subscription existed before the migration or was created after.

Given that the user now has all the permissions possible in SSRS, I'm not sure where to look next.  It's baffling to me that the function works in general, it just doesn't work for someone who was given permissions after the migration.

Both the old and new server are running under the same service account.

After a week of mucking around with permissions and getting ready to place a support call with Microsoft, Bart Edgerton's final suggestion to me in the Slack SQL Server Community workspace gave the solution:  I needed to give the user permissions to the folder containing the data source used by the report.

It's a bit odd to me as editing a subscription doesn't allow the user to edit the data source.  But, after a week puzzling over this and tweaking every permission under the sun, I'm at peace with it.

And, finally, this wasn't a migration issue.  At least, not related to my current 2008 to 2017 migration.  The same problem exists on the 2008 SSRS instance.  But, that instance was itself a migration from 2000 or 2005, I don't recall which.


SET LOCK_TIMEOUT, is it session or statement based? For what period does the LOCK_TIMEOUT last?

I do a SET LOCK_TIMEOUT 10 and a SELECT @@LOCK_TIMEOUT in one command after a login and get 10 returned. Immediately after this I do a SELECT @@LOCK_TIMEOUT again and it returns -1. I would have thought it still be 10.

I did some looking on the MSDN site and could not find if the LOCK_TIMEOUT is session based or statement based.

From the SET LOCK_TIMEOUT Books Online entry:

  "At the beginning of a connection, this setting has a value of -1.
  After it is changed, the new setting stays in effect for the remainder
  of the connection."

All the tests I have performed have reflected this documented behaviour.  Perhaps you are using different connections but getting the same session id?  Use this query to check:

SELECT
    dec.connection_id,
    dec.connect_time
FROM sys.dm_exec_connections AS dec
WHERE
    dec.session_id = @@SPID;


Splitting the servers We have a Windows 2012 VM running SQL Server 2012. There are around 20 databases with a total size of 5.83 TBs. There is 64GB of RAM and 16 logical processors on the box.

The server is extremely busy with a lot of users hitting the databases with queries during 7 - 6 window and a lot of jobs hitting it in the evening after 6. Most of the jobs run integration server packages that are stored in MSDB. On top of all this there is snapshot replication on one dB and backup/restore on 2 other databases every night.

Now, there is a need to load balance on the server, we have been talking on a couple of points which are below:

Separate into two servers, A & B, A only runs jobs and B is for users, every night back databases from A and put them on B.
Always On Availability Groups with readable secondary.
Separate Integration Services and database engine into 2 servers. We have earlier separated SSRS and SSAS from database engine.
Have two servers with heavy load dBs on one server and other dBs on other.

Which approach would you guys go with?

Approach 1 & 2 -- are kind of automatically out of the picture as users need read/write access. They not only read data from the databases but also write the data in their own schema.

Approach 3 -- separate SSIS & database engine -- I am against this as I have noticed that IS does not take more that 2GB of memory at its heaviest load and not to forget that even though we separate IS & engine the IS will still be hitting the databases and will be locking tables which users are trying to read. I agree that putting it on different server will allow IS to have its own processors and memory but whatever little performance we see will be offset by transferring data over the network.

Approach 4 -- is seeming most feasible with taking 1/4th of busy databases on other server and keep 3/4th of less busy one on same server.

I have allotted 50GB to the database engine and left 14GB for OS and other processes.

If you do AlwaysOn with readable secondaries you don't have to backup and restore every night, SQL Server will send all transactions to the secondary as they are executed.

You will also need to make sure the database drivers your application uses support read intent. I'm saying this because I've worked in an organization that used Java and the particular Java drivers we used did not support this feature. There is also some development overhead since your developers will have to create a read-only connection separate from the connection that writes and changes data. I've never used this in production, but I've done some testing about a year ago with MS Access and this is how I had to set it up.

You can always use SQL replication. I've worked with transactional and snapshot replication and in some cases prefer it to readable AlwaysOn secondaries. The reason being is that I can customize indexes on a replicated databases separate from the writable copy of that database. And this is a good solution if you have users running queries directly against SQL server.

I've set up SQL servers that do nothing but run job, but there is still overhead on the servers where you're reading from and changing data on.


MySQL Master Slave Replication and backup I would like to implement the following backup solution as highlgihted on the mysql site:

  To use replication as a backup solution, replicate data from the
  master to a slave, and then back up the data slave. The slave can be
  paused and shut down without affecting the running operation of the
  master, so you can produce an effective snapshot of “live” data that
  would otherwise require the master to be shut down.

I will have the master -> slave replication running every 5 minutes and then perform a full backup of the slave every 30 minutes

I understand that when the slave is backed up this is a database dump which will require all tables to be locked. Therefore the master wont be able to replicate to the salve, will this result in any errors on the master side? What actually happens in this case? Will the replication fall over if master cant replicate to the slave or will it handle gracefully?

I can accept if the master cant replicate when slave is backed up as the replication is quite frequent. i would just like to know what happens if the master is trying to replicate and the slave is locked.

Operations and issues on slaves will not impact the Master. I dont't understand your concept of "the master -> slave replication running every 5 minutes". All transactions on Master will immediatelty be replicate on slaves.

If your tables are InnoDB, your dump will not lock any tables (with the option --single-transaction) so you can have normal activity on master and slave too.

To backup your slave you can also stop the replication, shutdown your MySQL (Slave) and copy your datafiles or use a hot backup solution like Percona XtraBackup.

Max.


Rewrite sqlite trigger to postgresql Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format:

In sqlite:

CREATE TRIGGER connections_INSERT_update_connection_root_trg
                    AFTER INSERT ON dionaea.connections
                    FOR EACH ROW
                    WHEN
                            new.connection_root IS NULL
                    BEGIN
                            UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
                    END;

I have tried this:

CREATE FUNCTION dionaea.connections_INSERT_update_connection_root_trg() RETURNS TRIGGER AS '
                    BEGIN
                            UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
                    END
                    ' LANGUAGE plpgsql;

It is correctly accepted.

CREATE TRIGGER connections_INSERT_update_connection_root_trg
AFTER INSERT ON dionaea.connections
FOR EACH ROW
WHEN
 NEW.connection_root IS NULL
EXECUTE PROCEDURE dionaea.connections_INSERT_update_connection_root_trg();

After this I get this error:

NEW.connection_root IS NULL

Where is the problem please?

Need parentheses in the WHEN part:

CREATE TRIGGER connections_INSERT_update_connection_root_trg
AFTER INSERT ON dionaea.connections
FOR EACH ROW
WHEN
 (NEW.connection_root IS NULL)
EXECUTE PROCEDURE dionaea.connections_INSERT_update_connection_root_trg();

You also need to return NEW in your trigger function.

create or replace function  dionaea.connections_INSERT_update_connection_root_trg() returns trigger language plpgsql as $$
begin
  update dionaea.connections set connection_root = connection where connection = new.connection and new.connection_root is null;
  return new;
end $$;


SQL Server on Linux hangs on initial startup, no errors, and no new / updated ErrorLog file I'm using SQL Server 2017, Release Candidate 2 (RC2) on Linux (Ubuntu 16.04).
When the server starts up, SQL Server usually starts as well. But for some reason, SQL Server won't start anymore. At least I cannot connect to it using sqlcmd. I get an ODBC timeout ("Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server") error every time now:
Login timeout expired.
TCP Provider: Error code 0x2749.
A network-related or instance-specific error has occurred while establishing a
connection to SQL Server. Server is not found or not accessible. Check if instance
name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online..

However, when I run:
ps aux | grep mssql

I get two entries returned showing that the mssql user is running the sqlservr process.
Also, the errorlog file in /var/opt/mssql/log/ does not have a timestamp matching when I started the VM (or restarted the service), nor are there any new entries in that file.
AND, in /var/log/messages, all that shows up is:

This is an evaluation version.  There are [141] days left in the evaluation period.

If I run systemctl status mssql-server, then I get the following:

● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Mon 2017-09-04 20:01:56 BST; 36s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Process: 8009 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=255)
Main PID: 8009 (code=exited, status=255)
Started Microsoft SQL Server Database Engine.
This is an evaluation version.  There are [141] days left in the evaluation period.
Stopping Microsoft SQL Server Database Engine...
mssql-server.service: Main process exited, code=exited, status=255/n/a
Stopped Microsoft SQL Server Database Engine.
mssql-server.service: Unit entered failed state.
mssql-server.service: Failed with result 'exit-code'.

This ended up as a case of not being careful when working as root.

I had been researching if SQLCLR on Linux would have access to the app.Config file like it does in Windows (sadly, it does not: SQL Server 2017 on Linux ignores app config file if it exists, or sometimes locks-up if it doesn't (SQLCLR) ) and under certain circumstances SQL Server would completely lock up. When that happened, the only way to stop it was to do a kill -9 on sqlservr. One of the times that I was starting the service again, I did so by directly executing /opt/mssql/bin/sqlservr and while I was working as root (hence the process itself was owned by root).

There were no immediate errors or odd behavior resulting from running sqlservr as root, BUT when the VM restarted and SQL Server attempted to start properly (i.e. running as the mssql user), that is when it got stuck at the very beginning.

I found that a direct consequence of running sqlservr as root was that the /var/opt/mssql/log/errorlog file (and some others that are created upon SQL Server starting) were owned by root (makes sense).

And, a direct consequence of those files being owned by root is that when the process is started properly (as mssql), then the mssql user doesn't have permission to rename the file to end in .1 (and whatever else needs to happen with any other files, such as default trace, etc). However, rather than getting a permissions error, it just hangs forever.

The primary fix is to simply run the following as root (I haven't tried running it as mssql). For both of the following commands, sudo is only needed when not currently acting as root as it will run the command that follows it as root (or some other user if you specify -u username), after being prompted to enter the root password.

sudo chown -R  mssql:mssql /var/opt/mssql

The secondary fix (to make sure that this doesn't happen again), is to start SQL Server properly ;-):

sudo systemctl start mssql-server


Using SQL Server Profiler to track performance for nested stored procedure I have a slow unit test (defined as a SQL script) executing stored procedures nested in other stored procdedures.
I'd like to know if SQL Server Profiler provides tracks for execution along the 'call stack' at different nesting levels.

I got the answer in the meantime - sorry for the inconvenience

in profiler settings it is possible to select all events from there a list is provided and some settings can be checked to tracks for SP execution


How to tell MySQL to ignore all indexes for a given query? I've been benchmarking several queries and learning about indexes by using the explain function, profiling and the SQL_NO_CACHE option.

My usual benchmarking session goes something like this:

SET PROFILING=1;
SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE xxx;
SHOW PROFILES;

Some times after adding indexes, I want to know how the query would perform without any index optimization; so my options so far are to either delete the related indexes or use explain to check what indexes are in use and hint MySQL what indexes to ignore;

EXPLAIN SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE xxx;
SELECT SQL_NO_CACHE IGNORE INDEX (field_a) field_a, field_b FROM table WHERE xxx;
SHOW PROFILES;

Is there a similar option to tell MySQL not to use any index so I can get the query duration without any index optimization. Something like this:

SELECT SQL_NO_CACHE SQL_NO_INDEXES field_a, field_b FROM table WHERE xxx;

After a table name add USE INDEX() without arguments. That will make MySQL to ignore all index in the table.

Example:

SELECT SQL_NO_CACHE field_a, field_b FROM table USE INDEX() WHERE xxx;

Sorry for my english.


Mysql Restore - ERROR 1227 (42000) at line 30908: Access denied DEFINER issue Im trying to restore the database dump which has the DEFINER=test@%.

Backup file is 50GB.
OS - CentOS 7.5

While restoring this, I got the below error.
ERROR 1227 (42000) at line 30908: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The line 30908 is,
CREATE DEFINER=`test`@`%` PROCEDURE `myprod`(IN id INT)

So I tried to remove the definer using perl.
perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" my_dump_file.sql

Its executed, but after that also, I got the same issue and its not changed in file.
Then I tried,
sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' < my_dump_file.sql > my_dump_file.new

This is also executed, and tried to restore my_dump_file.new but still same issue on the same line.
Then finally I tried to create an user on the MySQL.
create user `test`@`%` identified by 'test';
grant all on *.* to `test`@`%`;
flush privileges;

Again, Im getting the same issue.
Can anyone help me to fix this?

sed -r -e 's/DEFINER\s*=\s*\S+//'

Notes:

-r : better syntax

\s : any whitespace (eg, tabs, too)

\S : non-space

(I tested on Ubuntu, not CentOS.)


I need help to optimize a long query I have a table Chron containing the following columns:

ID | timestamp | ...

Some timestamps have been erroneously changed to a specific date D. I want to change them to the timestamp of the nearest rows (in terms of ID) which have not been changed. My query is:

UPDATE Chron as C1
SET C1.timestamp = (
                    SELECT C2.timestamp
                    FROM Chron as C2
                    WHERE C2.timestamp != D
                    AND NOT EXISTS (
                        SELECT *
                        FROM Chron as C3
                        WHERE ABS(C2.id - C1.id) >
                              ABS(C3.id - C1.id)
                    )
)
WHERE C1.timestamp = D

The problem is that my table contains more than 300K rows and this query would take a lot of time to run. How can I optimize it? I know that it is not necessary to compare a given row C1 with the whole table, but just the neighbourhood is sufficient, but the optimal size of the neighbourhood is not always the same.

Here I suppose that id is ordered chronologically just like timestamp field.

UPDATE Chron AS w
   SET w.timestamp = IF( w.timestamp = D, @ts, @ts := w.timestamp )
 ORDER BY w.id ASC
;

The only requirement is that first (by id) record should have the correct timestamp.

Also that query update the broken timestamp to the value of the previous correct/corrected row and if you have the long continuous run of rows with broken timestamps result will be different from the "closest value". Anyway the "closest value" need a complicated query so "previous value" is good enough if you need some fast solution.


mongodb distinct() in an array In my mongodb database,there is only one document below:
  {
      "_id" : ObjectId("5226efc5ccf23796f9590f67"),
      "count" : NumberLong(126045),
      "eissn" : "1466-5034",
      "grade" : 0.0,
      "issn" : "1466-5026",
      "journalAlias" : ["International journal of systematic and evolutionary microbiology", "Int J Syst Evol Microbiol", "Int. J. Syst. Evol. Microbiol."],
      "journalName" : "International journal of systematic and evolutionary microbiology",
      "ccNameCount" : [{
          "ccName" : "ATCC",
          "paperCount" : 1,
          "strainCount" : 2
        }, {
          "ccName" : "ATCC",
          "paperCount" : 3,
          "strainCount" : 4
        }, {
          "ccName" : "BCCM/DCG",
          "paperCount" : 5,
          "strainCount" : 6
        }, {
          "ccName" : "BCCM/IHEM",
          "paperCount" : 0,
          "strainCount" : 0
       }
    }

What I want ,is to find out how many different ccNameCout.paperCount whose ccName is "ATCC".So ,in my java code ,I write like this:

coll.distinct("ccNameCount.paperCount",new BasicDBObject("ccNameCount.ccName","ATCC")),

but the result is 4, instead of 2.

I know the process of function distinct.Firstly , it search the collection whose ccNameCount.ccName is "ATCC", and then ,it will do a distinct() in these found documents.

But what I need, is to find how many different paperCount whose ccName is "ATCC".
Anyone can help me ?

I believe the best way to achieve that is by using the aggregation framework:

db.collection.aggregate(
    {$unwind : "$ccNameCount"},
    {$match : {
                "ccNameCount.ccName" : "ATCC"
    }},
    {$group: {
        _id : "$ccNameCount.paperCount"
    }},
    {$group: {
        _id : "count",
        total : {"$sum" : 1}
    }}
);

This will give you the distinct count of paperCount for ccName="ATCC" in the results total field.

Also, If you need to know each of the distinct values besides the total count you can use the $addToSet operator:

db.collection.aggregate(
    {$unwind : "$ccNameCount"},
    {$match : {
                "ccNameCount.ccName" : "ATCC"
    }},
    {$group: {
        _id : "$ccNameCount.paperCount"
    }},
    {$group: {
        _id : "count",
        total : {"$sum" : 1},
        distinctValues : {$addToSet : "$_id"}
    }}
);


Check if SQL Agent job is being executed manually I have a stored procedure that performs database backups.  Because I'm looking at Availability Groups within SQL Server 2012 I want to utilise the *sys.fn_hadr_backup_is_preferred_replica* function within that script to test whether the script should produce the backup.

If the script is run manually instead of as part of a scheduled SQL Agent job, or if the SQL Agent job is executed manually rather than via a schedule, then I want the job to fail with an error message to allow the user to see that the backup has not succeeded due to not being on the preferred replica.  If the job is run on a schedule then I obviously don't want it to fail as it would be producing errors and sending out alerts all day long on the server that is not the preferred replica.

The only bit that I don't know how to do is to check whether the job is being executed by a schedule or manually.  Is this possible?

Failing that, is there any other way that I can alert a user that the script hasn't produced a backup, whilst not causing the scheduled task to fail and produce an alert?

Using Kenneth's code, and a few other bits and pieces that I pulled together, I came up with the following code which I think should do the job.  If anybody sees any potential problem with this I'd be interested in knowing!

-- Check if this server is the preferred replica for backups.
    If sys.fn_hadr_backup_is_preferred_replica( @DatabaseName ) <> 1
    BEGIN

        -- Check whether the script is running from SQL Agent.  If it is not then raise an error so that the user knows that the backup hasn't completed.
        -- (If the job is run from the agent then there should be an equivalent job running on the preferred replica, so no need to alert).
        declare @AgentAccount varchar(100)
        set @AgentAccount = (select cast(value_data as varchar(100)) as [AccountName]
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%SQLSERVERAGENT%' --For named instance change to SQLAgent$<instanceName>
                                AND value_name = 'ObjectName'
                            )

        if @AgentAccount = SYSTEM_USER
        begin

            declare @jobSource tinyint
            SELECT  @jobSource = ja.run_requested_source
                    --WHEN 1 THEN 'SOURCE_SCHEDULER'
                    --WHEN 2 THEN 'SOURCE_ALERTER'
                    --WHEN 3 THEN 'SOURCE_BOOT'
                    --WHEN 4 THEN 'SOURCE_USER'  -- This one is manual
                    --WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                    --ELSE 'UNKNOWN' END
            FROM msdb.dbo.sysjobactivity ja
            WHERE ja.session_id = @@SPID
            AND start_execution_date is not null
            AND stop_execution_date is null;

            if @jobSource = 4   -- Job was run manually.
            begin
                RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
                return 1
            end
            else
            begin
                print 'No backup produced as this server is not the preferred replica for backups.'
                return 0
            end
        end
        else
        begin
            RAISERROR ('No backup produced as this server is not the preferred replica for backups.  Please run the script again from the preferred replica.', 16, 1)
            return 1
        end
    END


mySQL changing output unit of ST_Distance function I'm trying to get a result in nautical miles between two lon/lat saved in a point data type field in mySQL. However, the result seems to be not in any unit I'm familiar with (maybe degrees). How can I convert to my preferred unit?. My query as follows...

select t1.id, t2.id, ST_Distance(t1.test,t2.test) as DistanceApart from table1
t1 join table2 t2 on (ST_Distance(t1.test,t2.test) <= 10)

Update Oct, 2018

Hello everyone,

If someone is still landing here wondering for an answer. I have found one. My scope was to get all cities under 50 miles radius of a city selected by the user.

In the database, I have longitude and latitude column of all the cities.

What I did, is that I made a new column loc of "Point" datatype to store the combination of longitude and latitude of each city.

Then lets say user has chosen a city with some $latitude and $longitude, so I get all nearby cities (within 100 miles) as:

st_distance(POINT( '.floatval($longitude).', '.floatval($latitude).' ) , loc) <= 1.448

Putting this in you WHERE statement of MySQL query will give you all cities under 50 miles radius of that center city.

The formula I found to convert miles in angle is simple:

1 miles = 1/69.0585729 degree
Hence,
50 miles = 50/69.0585729 degree = 0.724 degree and 100 miles = 100/69.0585729 degree = 1.448 degree

If you need to have it in km, the formula goes like this:

1 degree = 111.139 km

Now you can convert km into degrees as well so that you can compare it with the output of st_distance() MySQL function

Please bear in mind that it might exclude some cities as mentioned by Rick in the comments. If you really want to go in depth of it, her eis the link posted by Rick James in the comment section, maybe this can help you out:

http://mysql.rjweb.org/doc.php/latlng

I hope it helps


Fastest way to sync (or keep import) 3.5TB data from hadoop to sharded mongodb cluster There are 3.5TB data in our hadoop cluster(yes on hdfs). And we have newly built a sharded mongodb cluster(the latest 3.x) with 3 mongos, 3 configdb and 3 shards(each shard has 1 primary and 2 secondary nodes)

We are looking for the best/fastest way to import these data from hadoop/hdfs to our newly built sharded mongodb cluster.

All these data will be into sharded collections in mongodb cluster.

We don't have much experience on this and no clue how to do this with the fastest way in our environment.

Appreciate if anyone can give a clue or the tools we can leverage. open source tools or commercial are both ok to us.

Joe

Why would you want to copy TB's of data from one environment to another system. There are rich projects available such as Apache Drill (to name few) which can seamlessly access Mongo for querying requirements. Have you explored that option?

When you say you have TB's of data in HDFS, what is the format of the data because this is a critical question to be aware of


Granting SELECT permission on a object I am not sure if this is an odd question, so please let me know if what I describe below is possible.

We have a server, which is configured by someone else and none of us are "added in it".

As of now I have only sa login in said server.

I am checking this via

SQLCMD -E -S ".\SQLServer"

and then by

SELECT name FROM sys.syslogins

And the output is

sa

I wanted to just run a SELECT statement against a table to see the value.

Question

Is there anything I can do at all?

I have tried DB Changing On and Trustworthy, but since I am no member at all my access is denied.

I just need to run the SELECT statement.

I wanted to just run a SELECT statement against a table to see the
  value.

  Question

  Is there anything I can do at all?

To find your permissions on the object you can use the function sys.fn_my_permissions

Just run this query to figure out your permissions on that table:

 use your_db;
 select *
 from sys.fn_my_permissions ('your_table_name', 'object')

If you have no SELECT permission on the object, maybe at least you have SELECT permission on subset of its columns.

In any case you can check your database permissions this way:

 use your_db;
 select *
 from sys.fn_my_permissions (null, 'database')

If you are fortunate and see CONTROL permission among them, there is a possibility to re-grant missing permission to you.

It can be even this situation: you are db_owner of this database, but someone used explicit DENY on this table to your user.

Some db_owners underestimate the power of being db_owner, so don't give up at the first

  The SELECT permission was denied on ...

and continue to explore what permissions you have on the server and in the database of interest.

First, check out your server level permissions like this:

select *
from sys.fn_my_permissions(null, 'server');

I think you have nothing interesting at the server level, at least from what you said it seems you lack VIEW ANY DEFINITION permission and of course you are not sysadmin.

Then check your database roles like this:

select *
from sys. user_token;

This gives you not only the roles you are member of, but it gives you all the users under which you can reach this database.
It can be usefull in case you are Windows login that is a member of many Windows groups mapped to this database, because simple select user
will return your win account (we are talking about Windows Authentication now).

I think this is all I can suggest you in your situation. Explore what permissions you have, what roles you are member of, and then may be it will be possible to gain the access on your table

Second supplementary part

And now I want to give my version of how it can be that OP sees only sa login
while he is not sa and don't see any other login while logged in as a Windows principal

Yes it's true that compatibility view syslogins does not show all security principals: at least it does not show fixed server roles.
But it has no problem with Windows principals, I controlled it on SQL Server 2014 as well.

So how it can be possible that OP sees only sa when querying syslogins logged in as Windows principal? Why doesn't he see his own win login, be it win account or just a win group, BUILTIN\Users or smth like this?

The simple answer is maybe OP has mistaken and did see another login but told us he was seeing only sa

The other and the only answer that I can give is this:
there is simply NO WIN USER mapped to this server

This situation can be reproduced only on versions 2005/2008/2008 R2 by granting CONNECT SQL to public role

So here is the scenario:
We are on SQL Server 2008 R2 and someone granted CONNECT SQL to public server role.

Well, in this case ALL THE ACTIVE DIRECTORY can log on to SQL Server without having no corresponding login at all!

Here in the picture the Windows principal hp2\Mary was successfully logged on my local instance (instance need not to be local, it's just a repro on my notebook) without having any corresponding login at all.

Mary is not a member of BUILTIN\Administrators (maybe it was even better drop that login at all to have purer repro), and what she see as logins is only sa.

And executing OP's code she's got only sa as well

Just to prove that there is no fake and my local 2008 R2 instance is full of logins and does not have only sa I attach the whole screenshot of my notebook with 2 SSMS the first one started under sysadmin Anna and another one under login without permissions and even without mapping: Mary

UPDATE
I figured out another situation that can lead to what OP was seeing:

CONTAINED DATABASE

Starting with 2012, database can be configured as CONTAINED.

In this case database, not a server, can authenticate a user.
The user will see only the database where it was created and (surprise!) syslogins view, that in my opinion should be unaccessible at all to the user,
still shows sa principal. Of course it shows no win login of a user as it just does not exists at all


Advice on scripting a "whitewashed" and downsized development db from main db Currently we (an active opensource project) have a large PostgreSQL production database which contains all the site records. Since we can't just allow every developer or contributor access to the database (containing emails, passwords, phone numbers, etc.) but we need to give the developers a somewhat up-to-date SQLite database. We currently do the following:

dump the database
analyze and change it with custom python scripts including truncating the table to make exporting to SQLite more bearable, remove sensitive data like passwords
export this to SQLite

This is very slow and error prone.

Is there a recommended/best practice approach way to do this, both the whitewashing and the table truncation, without breaking object relational mappings between tables?

As I analyze the problem domain I see the main problem with the object related mapping. I can't just pull the first thousand records from all the tables (with LIMIT) since an object in line 900, for example, in one table may map a foreign key to the 1001 line in another table. I guess changing and sanitizing data can be done with views, replacing certain columns for a calculated one (based on the original table column). Then a cron job could just dump the views.

I'll be glad for help/references.

What you're referring to is usually called Test Data Management. There are several commercial solutions, e.g. IBM Optim Test Data Management (disclosure: I work for IBM, but don't speak for them). I'm not aware of any free products that can perform that function, so I'm afraid you'll have to stick to your Python scripts unless you have deep pockets.

On a side note, I would suggest using the same database platform, i.e. PostgreSQL, for both production and development -- it will probably save you some troubleshooting time later.


TSQL Convert FLOAT to STRING Truncation/Rounding Issue SQL Server 2008 R2

Why when converting a FLOAT to a string (Varchar / Nvarchar) using Cast or Convert, does a rounding or truncation occur?

Using CAST

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    CAST(@floatVal AS VARCHAR(20)) 'VARCHAR',
    CAST(@floatVal AS NVARCHAR(20)) 'NVARCHAR'

Output

VARCHAR NVARCHAR
4.76759 4.76759

Using CONVERT:

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    CONVERT(VARCHAR(20), @floatVal) 'VARCHAR',
    CONVERT(NVARCHAR(20), @floatVal) 'NVARCHAR'

Output

VARCHAR NVARCHAR
4.76759 4.76759

With STR however, there's no truncation/rounding

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    LTRIM(STR(@floatVal, 17, 14)) 'STR'

Output

STR
4.76758527755737

Thanks in advance

From the documentation about CAST and CONVERT:

CAST is 'apparently' using the default style of 0 since you cannot specify a style when using CAST.

CONVERT defaulted to style 0 since you did not specify a style.

You can achieve more precision using CONVERT and style (and your have to increase the length of the target datatype), but the results appear to be in scientific notation (so, not exactly like STR).

DECLARE @floatVal   FLOAT = 4.76758527755737

SELECT
    CONVERT(VARCHAR(30), @floatVal,1)     'VARCHAR'
    ,CONVERT(NVARCHAR(30), @floatVal,1)   'NVARCHAR'
GO


How do I an add a DEFAULT on a column if the table already exists? I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT

You can see the example in their docs,

ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b;

In my case, I'm trying the same pattern,

ALTER TABLE foo
ADD CONSTRAINT column_name
  DEFAULT now() FOR bar;

This doesn't work. What is the proper way to add a DEFAULT for a column in PostgreSQL?

In PostgreSQL, DEFAULT is not a constraint. PostgreSQL follows the spec and implements the <set column default clause>. See the ALTER TABLE command documentation for more details. Your command should look like this:

ALTER TABLE foo
  ALTER COLUMN column_name
  SET DEFAULT now();


SQL Server 2012 Publisher SQL Server 2008 Subscriber version issue So I have a setup where one virtual machine is a SQL Server 2012 publisher and I need to setup merge replication on a SQL Server 2008 subscriber on a different vm.

When setting up the subscription at the subscriber, during the wizard step where to add the SQL Server subscriber, I get that error :

  For merge publications, the version of the Subscriber must not exceed the version of the Publisher.

I read that SQL Server 2012 publishing supported 2008 and above subscribers.

Any ideas ?

It seems That this Problem is due to the SSMS versions. What you could do is create the subscription by right clicking on the Publication.

I assume you are using SQL 2012 as Distributor/Publisher & 2008 as Subscriber.


When should I use a unique constraint instead of a unique index? When I want a column to have distinct values, I can either use a constraint

create table t1(
id int primary key,
code varchar(10) unique NULL
);
go

or I can use a unique index

create table t2(
id int primary key,
code varchar(10) NULL
);
go

create unique index I_t2 on t2(code);

Columns with unique constraints seem to be good candidates for unique indexes.

Are there any known reasons to use unique constraints and not to use unique indexes instead?

Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.

So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather than a constraint to make that clear - this way if the uniqueness requirement is changed in a later revision of the application you (or some other coder) will know to make sure a non-unique index is put in place of the unique one (just removing a unique constraint would remove the index completely). Also a specific index can be named in an index hint (i.e. WITH(INDEX(ix_index_name)), which I don't think is the case for the index created behind the scenes for managing uniqueness as you are unlikely to know its name.

Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I'd use the constraint, again to make the intended use more obvious when someone else looks at your table definition.

Note that if you use both a unique constraint and a unique index on the same field the database will not be bright enough to see the duplication, so you will end up with two indexes which will consume extra space and slow down row inserts/updates.


Schema for analytics table in Postgres We use Postgres for analytics (star schema).
Every few seconds we get reports on ~500 metrics types.
The simplest schema would be:

timestamp      metric_type     value
78930890       FOO              80.9
78930890       ZOO              20

Our DBA has came up with a suggestion to flatten all reports of the same 5 seconds to:

timestamp   metric1     metric2     ...  metric500
78930890    90.9        20          ...

Some developers push back on this saying this adds a huge complexity on development (batching data so it is written in one shot) and to maintainability (just looking at the table or adding fields is more complex).

Is the DBA model the standard practice in such systems or only a last resort once the original model is clearly not scalable enough?

EDIT: the end goal is to draw a line chart for the users. So queries will mostly be selecting a few metrics, folding them by hours/minutes, and selecting min/max/avg per hour (or any other time period).

EDIT: The DBA main argument is that reducing the number of rows x500 times will allow more efficient indexes and memory (the table will contain hundreds of millions of rows before this optimization). Then when selecting multiple metrics the suggested schema will allow one pass over the data instead of separate index search for each metric.

EDIT: 500 metrics is an "upper bound" but in practice most of the time only ~40 metrics are reported per 5 seconds (not the same 40 though)

As commented, arguments from both sides are valid. Let's call them "star" (the flattened schema of your DBA) and "EAV" (entity-attribute-value). The latter can serve as a hint. Details in this related answer:
Is there a name for this database structure?

Well, if your 500 metrics are of well known type and you don't invent new ones / drop old ones all the time, it's not the worst case EAV scenario, but similar.

There is no "standard" way to do it. The more flexible approach would be the "EAV" schema. Then you can easily add and delete metric-types: add a row to the referenced metric_type table, or delete one (cascading to metrics table). That would need a schema-change and be more expensive in the "star" model.

You can do quite a bit for either model with smart covering / partial / multicolumn indexes.

Some decision guidance

Aggregates (min/max/avg/...) on a single metric-type? -> "star"
Aggregates considering all or many different metrics? -> "EAV"

Do these attributes describe a common entity? -> "star"
Or is it just a bunch of numbers that may be grouped / split up any other way? -> "EAV"

Your data is written once and then never changed? -> "star"
Or do you run UPDATES on selected metrics? -> "EAV"

Is your set of 500 metrics complete? The set hardly ever or never changes? -> "star"
New metrics are added, existing metrics are dropped all the time? -> "EAV"

Storage size

Concerning your comment:

  Storage is less important now for optimization, we are focusing on query times.

Storage size is a major factor for query times. The number of data pages that have to be read to satisfy a query is probably the most important single factor for performance.

Let's start with your casual remark: Data type is int or double.

int occupies 4 bytes.
double occupies 8 bytes.

Assuming all columns are NOT NULL, 500 integer columns, plus 1 timestamp plus row overhead (no padding) would occupy 2036 bytes in the "star" schema. No compression possible. Here is how you calculate that:
Configuring PostgreSQL for read performance

If you mix int with double, be sure not to waste space for padding. For instance, group integer and double metrics.

In the "EAV" model, you'd need at least 44 or 52 bytes per row. 22000 or 26000 bytes for one timestamp. 11 - 13 times as much. That matters. For one hour's worth of data, you need to fetch 2000 data pages or more (default page size 8k) vs. around 180 pages for the "star" schema.

Here are some tools to measure size:
Measure the size of a PostgreSQL table row

I think storage size can be the key to performance here. If you are focusing on query times, and I had to make a wild guess, the "star" schema is probably the better choice.

But as I said, it depends on a lot of details.

Either way, you may be interested in cross tabulation ("pivot table"). The tablefunc module provides the crosstab() function:
Have aggregate function group results in one row


Microsoft SQL Server: Prepared Statements How do you create and use a prepared statement in SQL Server?

In PostgreSQL, you do something like:

PREPARE prepared (varchar, varchar, etc) AS
    INSERT INTO table(field,field,etc)
    VALUES ($1,$2,etc);
EXECUTE (value,value,etc);

Something similar in MySQL.

I can’t find any examples in SQL Server. I have seen a number of examples using a function, but I don’t think that’s the same thing.

Thanks

Prepared statements are supported in SQL Server, but you are probably having a hard time finding examples of using them because they aren't really necessary.

You could call sp_prepare to get a handle and then pass that to sp_execute. Or you can call either sp_prepexec or sp_prepexecrpc, both of which combine the "prepare" and "exec" steps. In all cases, you would end by calling (eventually) sp_unprepare, passing in the handle returned by sp_prepare, sp_prepexec, and sp_prepexecrpc.

However, as stated previously, this pattern is obsolete / unnecessary in Microsoft SQL Server as queries and execution plans are naturally cached. Queries are cached, and looked up, based on a hash of the query text. Ad hoc queries are cached based on the exact, full text of the query (binary comparison), including values concatenated into the query to use for filtering, etc. Those plans are less likely to get re-used, unless the exact same query (with respect to white-space and everything else) is executed again. Parameterized queries are cached based on the query text which does not include the parameter values, and can be re-used for different values of the parameters -- just like when doing a "prepare". Hence, no need to call prepare / exec / unprepare.

If you execute queries using Stored Procedures, sp_executesql (for Dynamic SQL), or a properly parameterized query from app code, then SQL Server is already doing a lookup to see if that parameterized query has already been parsed and compiled (i.e. "prepared") and if so, then it will use the cached info.

I believe that the most wide-spread usage of this pattern in SQL Server is by ODBC drivers. When writing an application that can connect natively to SQL Server, one would not employ this pattern. And in fact, I would recommend against using this pattern (whenever possible) as it has the downside of taking up more memory per each Session: it stores the query definitions so that the execution plan can be regenerated in cases where the plan was, for whatever reason, removed from the plan cache. For more details on this behavior, including sample .NET / C# code, please see my other answer here on DBA.StackExchange:

What is the sense and benefit of using SqlCommand.Prepare()?


Is necessary to create a new table in this context? I have a table Template and another ProjectDescriptions. A template can have many ProjectDescriptions, so the tables are like this:
Template: id, title, etc
ProjectDescriptio: title, description, template_id, etc

In the backoffice there is a screen for the user to create new project descriptions for a specific template.
In the frontend there is a list that shows the project descriptions for a specific template. There is a combobox for the user to select the project description that he wants, the can associate more than one project description to a template. My doubt is how to store the selected project descriptions. Is necessary to create a new table?

If the same ProjectDescription can be used by multiple different Templates then you'll need a third table, as a linking table, for the many-to-many relationship between the two entities. Otherwise you'd be forced to store duplicate rows in ProjectDescription which is bad design from a Normalization standpoint.
The linking table would be simple and just store template_id and projectdescription_id, and the primary key would be a composite key on both those fields.


automatic undo for version control in mysql I'm not a mysql expert, but it seems like there is no obvious way to do that.
I'll explain what I want.
Let's say I'm doing a data migration which would update some rows in some table.

I'd like to be able to make a backup, execute migration script and get undo script automatically.

I don't see why this should be impossible, since rdbms stores all changes in a log already for transaction (mysql has --log-bin option for example) and they already know how to revert them (because they can rollback a transaction). So maybe it's possible to extract and use this data for the purpose of creating undo scripts for migrations?

EDIT.

Okay, so thanks to one of the answers I've found that mariadb actually partially support what I want already. This feature called flashbacks. It's actually doing what I've suggested - translating INSERTS into DELETE via binlog. So what I am missing is the upcoming support of flashbacks over DDL statements and making flashbacks only for one transaction.

I'd like to be able to make a backup, execute migration script and get undo script automatically.

Why?

If the migration fails so badly that it's going to be abandoned, then restore your backup of the database.
That's the only way to guarantee that you'll get back to the point you started from.

I don't know of any way to create an "undo" script but, even if you could, there's no guarantee that it would work, potentially failing part way through its "recovery" and leaving you in an even worse position, somewhere between the last known Good State and the point where the migration failed but you won't be exactly sure where.  From then on, you're clutching at straws to try and clean up the mess.

Far better ( / easier / more reliable) to toss the "corrupted" database in the bin and restore it from the backup.


Transaction question I have a question.

c stands for Commit, a stands for Abort, T is a transaction (db Transaction)

Is the following sentence true?

either c or a belong to T

In my opinion the sentence is true since if you have Abort you cannot Commit...and also the opposite is true.

So if you have c you cannot have a, a mutual exclusion.

Sorry to bother you but my teacher said I made a mistake

Thanks

Moved from comment to answer

I think 'belong' is a wrong word here. It usually says that transaction ends with commit or rollback. Group of DML statements belong to transaction; commit/rollback is a way to finish transaction...emphasized text


Licensing of Oracle Standard One multiple homes we I have a server with Oracle SE One ... I need to separate the test data from prod data as each one has it's own schema, I will do 1 of 2 which is both are OK for the dev team:

Create a new instance and move the test into it
Install a new home and then create the new instance into it then move the data, I like this option because I can test the Oracle patch on the test home before applying it on the prod ...

any thought on the license?
we already have the license, but do we need additional one if we do both or any one them?

The correct answer for licensing questions is: contact your oracle sales representative.

With Oracle you license cpu's. If your test and prod run on the same cpu's, they don't need a new license. As long as it all runs on the same cpu's, it does not matter if you have multiple databases (and instances) or run it all in one single database. Normally we don't mix test and prod in the same database so at least make sure to create separate databases for test and prod.

If you run this on the same machine, it could be wise to run test using a test account and prod database using a prod account. This also requires 2 separate ORACLE_HOME's on that single machine but this does give you some control about the separation of your environments.


SQL Server service permissions on network share depending on IntegratedSecurity=true/false Our company receives a daily file from one of our customers. It is an EDIFACT file translated to XML and downloaded from an SFTP server.

We have developed one (c# console) application that:

Download the file.
Upload it to one SQL Server (2005) using an stored procedure.
Deletes this file from network share.
Runs another stored procedure to integrate it with our sales order's tables.

SQL-Server & SQL-Server agent service initiates as LocalSystem account. (It will be replaced soon and I'd prefer to let it as is, I know it is not configured properly.)

The procedure to store the file in our SQL-Server is similar to this one:

CREATE PROCEDURE [dbo].[sp_ProviderName_Download]
(
    -- FQN of XML file
    @file_name nvarchar(260)
)
AS
BEGIN

    DECLARE @xml_data XML,
            @cmd nvarchar(max);

    BEGIN TRY

        SET @cmd = 'SELECT @xmlText = BulkColumn FROM OPENROWSET(BULK '
                    + '''' + @file_name + ''''
                    + ', SINGLE_BLOB) x;';

        EXEC sp_executesql @cmd, N'@xmlText XML OUTPUT', @xmlText = @xml_data OUTPUT;

        INSERT INTO [dbo].[ProviderXML_Register] (..., xml_data)
        VALUES (..., @xml_data);

        RETURN 0;

    END TRY
    BEGIN CATCH

        EXEC [dbo].[vsp_error_handler];
        RETURN -1;

    END CATCH

END

The issue:

Stored procedure works depending on how we set the ConnectionString in our C# application.

If it uses IntegratedSecurity=false:

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "xxx.xxx.xxx.xxx";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = false;
this.scsb.Password = "*******";
this.scsb.UserID = "SqlServerLogin";

It works properly without errors.

But if it connects using IntegratedSecurity=true (AD user)

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "SQL_INSTANCE_NAME";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = true;

it fails with error:

  Operating system error 5 Access denied.

AD user has MODIFY rights on the network share. And as far as I know, 'LocalSystem' account shouldn't be able to access to the network share.

Why I'm getting this error when IntegratedSecurity=true?

The problem is due to how security is handled for bulk operations and the default limitations of Impersonation.
When connecting with a SQL Server Login, there is no external Windows SID / security context, so SQL Server uses the security context of the service account when needing to reach outside of SQL Server (i.e. interact with the OS, network, etc.). So, if the SQL Server service account is the Local System account, then that is the account used for external access. Since your file is on the network, it would have to be that "Local System" has access to that network share. This is possible by giving the server itself access (by specifying <domain_name>\<computer_name>$ for the NTFS permissions). In this case, the SQL Server process is not using Impersonation: the external access is being made in the security context of the process itself.
When connecting to SQL Server using a Windows Login (whether AD account or local account on the server), there is an SID to use, and SQL Server will use that SID (Security ID that points to the Windows / AD account) for external access. This is what happens when connecting with "Integrated Security" / "Trusted_Connection" = true. Now, using the SID of the account logging into SQL Server means that SQL Server is using Impersonation to make it's network request appear to be coming from that other SID instead of from "Local System" (remember, the service account owns the sqlservr.exe process, and it is that process, not the SID logging into SQL Server, that is making the network request). Impersonation, by default, is quarantined to the machine in which the process was started (similar to how using Impersonation via EXECUTE AS user is, by default, quarantined to the Database). So, the BULK INSERT / OPENROWSET(BULK... commands should be able to see files locally on the server that SQL Server is running on, but network shares are not local, and the impersonated security context, by default, does not extend that far. What you are seeing here should be the same thing as the infamous "double-hop" issue.
So, you can do one of the following:

give permission on that network share to EVERYONE

continue logging in via the SQL Server Login and not worry about using Integrated Security / Trusted Connection.

Enable "Delegation", though I am not sure if that will work on a Local System account (you mentioned SQL Server Agent running as "Local System", so this is what would be connecting to SQL Server). It might require setting up an AD account and enabling that account for "Delegation" so that it can go beyond the local server. Either way, "Delegation" is how one configures AD to allow an account (maybe even an account / service combination) permission to go beyond the default quarantine.
P.S. @StrayCatDBA mentioned that using the Network Service account (i.e. NT AUTHORITY\NETWORK SERVICE allows for Delegation. Also, please see the "Configure Windows Service Accounts and Permissions" link at the bottom as that documentation goes over the various options you have.

Some info here:

Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (the Security considerations section; link should go directly there)
Kerberos Constrained Delegation Overview
How to Configure the Server to be Trusted for Delegation
How to Implement Kerberos Constrained Delegation with SQL Server 2008
Configure Windows Service Accounts and Permissions


One name for use in a connection string I'm not primarily a dba so I apologize if this question is high-level and please provide feedback if I'm asking the wrong question or going about this the incorrect way.

I currently have one database (virtual) server that many applications point to. Some are line of business apps and some are custom applications using connection strings. The server is old and I want to upgrade the OS and SQL Server version but I don't want to do an in-place upgrade, I'd rather build a new server and migrate all of the applications and I'm realizing that it's going to be a gargantuan task. We're going to have to go through all of the settings in our applications but also all of the code in-house applications to change the name of this server in every connection string.

My question is:

How do I go about this so that this is the last time I ever have to
go through this task?
Should I be using alwayson clustering and use the listener in my
connection strings?
Is there a way I can do this and use the name of my server as the
virtual network name so that I don't have to change all of my
connection strings?
Should I be using something else entirely?

Thank you for any info provided.

I'll expand my comment into a proper answer, as requested.

Since, in this situation, the desired result is more of an automatic redirection to the new server without needing to change connection strings then the following options are available:

DNS Alias (CNAME)
Hardware Load Balancer
SQL Alias
Local Host Record

Each of these has its' own merits, however since this change is environment wide you'll want to have the least amount of moving parts and administrative overhead.

A Local Host Record (editing the HOSTS file) would have to be completed on each server so that it could resolve the proper address. This would be bother administratively burdensome and also riddled with troubleshooting issues.

A SQL Alias would also work but would fall under the same set of issues as the Host Record.

Hardware Load Balancers (or software load balancers, depending on implementation) are a great alternative as they can do the DNS swap for you. This could bring up some issues with Kerberos or Certificate signing, however those application could be singled out and fixed (potentially, depending on your environment and resources).

The DNS Alias is roughly the same as the Hardware Load Balancer option and the same set of issues would occur. However this option does not require specialized hardware or software in order to complete.

I would recommend either the HLB or DNS Alias options.


Clustered Index during data load in MS SQL Server Until today, I was under impression that it's been considered as a best practice to disable the indexes in sql tables during the data load. And with the fact that one cannot disable the clustered indexes in MS Sql server, you would have to delete that and then perform the data load operation. But today, I performed the data load operation of around 30M records with 10-15 columns of different column types and I was surprised to see the result where keeping the clustered index out performs the load compared to dropping the clustered index, loading data and then creating the clustered index.
In my test, the data which was being inserted was presorted in the source table and that might be one reason for the difference, but I just want to understand is it still considered as a best practice to drop the clustered index prior to data load and recreate it after or keeping the clustered index is the optimal way?

It's important to note that a clustered index isn't really just an index, it is also the physical structure/ordering of the pages that comprise the table, not just the B-Tree of the primary key.
This is one of the areas why Sybase/SQL Server sort of dropped the ball in naming, while other vendors (IBM/Oracle) did a better job referring to them as "Index-organized tables" which IMO illustrates the concept more clearly.
So if you drop the clustered index, the table turns into a heap.  When you re-enable it, the table has to be physically rebuilt, which requires sorting data and moving pages - both expensive operations.


How can I replace the following query with ISNULL function in SQL? I have the following query. For performance optimization reasons I have been asked to replace the logic with ISNULL function in SQL. How can I achieve this? please advice.

                   SELECT CASE
                               WHEN tb1.[tag-value] IS NOT NULL THEN tb1.[tag-value]
                               ELSE 'N/A'
                          END     AS [tag-value]
                   FROM   @AgentTableFilt ta

This should do what you need

SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value]

FROM   @AgentTableFilt ta

Or you could use the following

SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value]

FROM   @AgentTableFilt ta


How can I set columns for an entire table in one UPDATE based on the SUM of a join table column? I have tables  companies and employees:

 explain companies;
+--------------------+---------------+------+-----+---------+----------------+
| Field              | Type          | Null | Key | Default | Extra          |
+--------------------+---------------+------+-----+---------+----------------+
| id                 | int(11)       | NO   | PRI | NULL    | auto_increment |
| score              | int(11)       | NO   | MUL | NULL    |                |
+--------------------+---------------+------+-----+---------+----------------+

 explain employees;
+--------------------+---------------+------+-----+---------+----------------+
| Field              | Type          | Null | Key | Default | Extra          |
+--------------------+---------------+------+-----+---------+----------------+
| id                 | int(11)       | NO   | PRI | NULL    | auto_increment |
| company_id         | int(11)       | NO   | PRI | NULL    |                |
| score              | int(11)       | NO   | MUL | NULL    |                |
+--------------------+---------------+------+-----+---------+----------------+

What I'd like to do is update the companies.score column based on the SUM of the employees.score column where employees.company_id = companies.id.

So given Company A and Company B, and Employee A1 (score 5), Employee A2 (score 2), and Employee B1 (score 3); I want to update Company A's score to 7, and Company B's score to 3.

What's the most elegant way to achieve this in MySQL?

Thanks!

update companies c set c.score = (select sum(e.score) from employees e where e.company_id = c.id);


combined contents of 2 columns can't contain duplicates This is a hard question to ask. I find it difficult to put into words.

I have two columns where the content of column A can not appear in column B and vice versa. So, the combined contents of both columns needs to be unique.

In other words: I want any value that already exists in column A not to be allowed in either A or B. Likewise for B (no duplicates in B or A).

Is there a way to do this in MySQL with only a table schema change?

I also don't want to use triggers to prevent insertions.

MySQL version 8.0.18

The only way that I can think of to do this (without using triggers) is to use Firebird. Doesn't really answer the question (MySQL), but is interesting functionality to explore - see the fiddle here!

CREATE TABLE test
(
  column_A INTEGER NOT NULL,
  column_B INTEGER NOT NULL,
  CONSTRAINT a_uq UNIQUE (column_A),
  CONSTRAINT b_uq UNIQUE (column_B),
  CONSTRAINT a_ne_b_ck CHECK (column_A != column_B),
  CONSTRAINT a_ni_b_ck CHECK (column_A NOT IN (SELECT column_B FROM test)),
  CONSTRAINT b_ni_a_ck CHECK (column_B NOT IN (SELECT column_A FROM test))
);

It does make the table declaration more complex (con) but with the massive pro of not having to maintain triggers oneself. It would appear from the error messages that Firebird uses triggers in the background - better that the server maintain them than me!

--
-- The next two INSERTs work (as one would expect)
--
INSERT INTO test VALUES (2, 4);
INSERT INTO test VALUES (3, 5);

Then, to test (standard functionality first):

--
-- This doesn't work - breaches UNIQUE constraint on column_A
-- Look at the quality of the error message!
--
INSERT INTO test VALUES (2, 7);  -- 2 is a dup in column_A

fails with the error:

violation of PRIMARY or UNIQUE KEY constraint "A_UQ" on
table "TEST" Problematic key value is ("COLUMN_A" = 2)

Look at the quality of that error message - even telling you the offending value!

Then we try (again standard CHECK constraint stuff):

--
-- This doesn't work - breaches the column_A != column_B CHECK. Easy, peasy!
-- All RDBMS do this - even MySQL now since version 8!
--
INSERT INTO test VALUES (4, 4);

Error message:

Operation violates CHECK constraint A_NE_B_CK on view or
table TEST At trigger 'CHECK_1'

Now, we test the SQL within the CHECK constraint!

--
-- This is where it gets interesting! Notice that it **_won't_** allow a value
-- that's in column_B be inserted into column_A! Pretty cool!
--
INSERT INTO test VALUES (5, 9);

gives the error:

Operation violates CHECK constraint A_NI_B_CK on view or table
TEST At trigger 'CHECK_3'

The SQL in the CHECK constraint works the other way round also - see the fiddle. Note the part of the error message where it says: table TEST At trigger 'CHECK_3' - triggers at work in the background!

This is really powerful stuff! It's such a shame that Firebird isn't more popular/widespread - it really deserves the no. 2 spot in F/LOSS database ecosphere!

Incredibly, MySQL only recently introduced CHECK constraints (only at least 25 years after all its other major - and not so major - competitors). But, now it appears to be making serious efforts to catch up! But nobody else (AFAIK) has SQL in CHECK constraints, not even the mighty Oracle, MS SQL Server or PostgreSQL (note to self - suggest this on the lists!).


SQL Performance - One counter to rule them all - identify CPU/MEM/DISK/NET pressure I've been performance tuning SQL servers for a few years as a DBA.

I'm trying to create a fast-food version of performance metrics that can quickly (in 5 minutes) and accurately (provable) answer a question from Management "Does this server need more/faster _ ?"

'_' being one of these 4 possible bottelenecks in an IT-stack bottom up (from server-perspective, without going in to the app/code/ui):

NETWORK
DISK
MEMORY
CPU

There are thousands of counters, articles, products to help monitor these. But is there a simple, instant and accurate script that can pinpoint if any of these 4 need to be scaled up or scaled out ?

e.g.
sys.dm_os_wait_stats - SOS_SCHEDULER_YIELD has high signal waits => need either more or faster CPU.
PAGEIO_LATCH => need more files or faster DISK

Are these 2 accurate? Does Page Life Expectancy accurately 'PROVE' the need for more memory ? What is your GO-TO script for diagnosing a performance issue ?

I've used sp_whoisactive, sp_blitz, Glenn's DMVs, Spotlight, Idera etc. but am yet to come across a script that will satisfy a CIO's question about where to spend budget money, or that will correctly blame the problems on bad code, or slow SAN, or the ISP.

Everytime any (Network/Systems/DBA/App) engineer points fingers at the other team, we have to 'PROVE' our statement, and with Virtualization and Cloud, without ideal test environments, without downtime, it's getting increasingly difficult to provably pinpoint the source of server performance issues, other than maybe Task Manager <excuse the rant>

No. Say you have a server with:

High PAGEIOLATCH waits
High IO stall times
Low Page Life Expectancy (say, consistently under 30)
128GB RAM

You could fix this scenario with:

Index tuning - because maybe the queries are scanning a table too large to fit in memory
Query tuning - because maybe they're using non-sargable predicates, or not caching
Configuration - because maybe some wacko set max server memory too low, or we're using the wrong Cardinality Estimator for our code
Adding memory - but now you have to check version & edition, because your Windows or SQL Server may not support more memory
Faster storage - but this is one heck of an expensive fix

There's no one metric that tells you which of those solutions is the right one. Welcome to performance tuning. Ain't no easy button here.


MySQL DB import/export command line in Windows How to export / import a database in MySQL through the command line?

MySQL system configuration, WampServer server installed.

OS: Windows

The advice for my local host, not an live hosting...

Use this for export:

mysqldump -u username -p databasename > filename.sql

this is my Sample Export in CMD and i'll save in drive D:\

mysqldump -u root -pmypassword pos > d:\pos.sql


Change database loaction I have installed new MS SQL Web Edition on C Drive and I have also another D drive on server. Now I want to change database location, like when we make new database it should be make on D drive.

Can you please give me an idea how can I Do this.

When you don't provide any location,SQL Server by default uses server level settings which are provided during installation.What this means is if you don't provide any location while creating database,SQL uses this location by default..This can be changed at any time and can be found here

Further while creating a database,You also can provide location like  below,if you want any database to be created at different location

CREATE DATABASE [sdsd] ON  PRIMARY
( NAME = N'sdsd', FILENAME = N'new path ' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'sdsd_log', FILENAME = N'new path' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

If you are looking to change the existing database location,here is an answer which goes in depth ..

How do I attach a database in SQL Server?


Transaction Error in TRY CATCH block The following code returns this message in case something goes wrong:

  Transaction count after EXECUTE indicates a mismatching number of
  BEGIN and COMMIT statements. Previous count = 0, current count = 1.

OPEN extensionViews
     FETCH NEXT FROM extensionViews
     INTO @Viewname
     WHILE @@FETCH_STATUS = 0
     BEGIN
        BEGIN TRY
                exec sp_refreshview @Viewname
        END TRY
        BEGIN CATCH
            print 'ERROR sp_refreshview'
        END CATCH

        FETCH NEXT FROM extensionViews
        INTO @Viewname
     END
     CLOSE extensionViews
     DEALLOCATE extensionViews

I already found out that there is something going wrong with the implicit transactions, but I do not really understand it entirely.

In case of a CATCH, do I have to explicitly ROLLBACK the transaction? Does this mean that I have to start the transaction explicitly as well insode of the Cursor to rollback THIS one command instead of all?

Given the following info:

  Previous count = 0, current count = 1.

the implication is that sp_refreshview is opening an explicit transaction but encountering an unhandled exception such that it doesn't do its own ROLLBACK. The "Previous count = 0" means that your code is not creating the Transaction.

This is not something that you can fix in your code because a subprocess must exit with the same @@TRANCOUNT that it started with. I doubt you will be able to add a ROLLBACK, or even a BEGIN TRAN and then ROLLBACK to help this.

At the very least, you need to figure out which View is causing the problem. You can do that by printing the view name being given to that system stored procedure before executing sp_refreshview, especially if the error is one of the few that cannot be trapped by the TRY...CATCH construct. Just add the following statement just after the BEGIN TRY:

RAISERROR(N'View: %s', 10, 1, @ViewName) WITH NOWAIT;

Also, it would be helpful to see the exact error message, so replace the PRINT in the CATCH block with the following query:

SELECT @Viewname AS [ViewName],
       ERROR_NUMBER() AS [ErrorNumber],
       ERROR_MESSAGE() AS [ErrorMessage];

You can also add the following to the CATCH block, but I doubt it will solve this issue:

IF (@@TRANCOUNT > 0)
BEGIN
  ROLLBACK;
END;

P.S. You should be using sysname (all lower-case) for variable and columns that hold SQL Server object names since that is the datatype used in the system tables for object names. sysname is an alias for NVARCHAR(128).


SSRS executionlog having rowcount 0 I'm reviewing some execution logs for long running queries in SSRS.
A common thread I'm seeing is reports that seem to take a few hours to process, with a rowcount of 0.

I'm using the following query to get my results:

SELECT e.instancename,
   e.username,
   e.timestart,
   e.timeend,
   Datediff(mi, e.timestart, e.timeend) AS 'Minutes',
   e.timedataretrieval,
   e.timeprocessing,
   e.timerendering,
   e.[RowCount],
   catalog.PATH,
   catalog.name
FROM   executionlogstorage e
   INNER JOIN catalog
     ON e.reportid = catalog.itemid
   INNER JOIN users
     ON catalog.modifiedbyid = users.userid
   INNER JOIN users AS users_1
     ON catalog.createdbyid = users_1.userid
WHERE  Datediff(mi, e.timestart, e.timeend) > 15
   AND catalog.name <> ''
   and TimeStart > 01-01-2014
   -- Filtering out Oracle reports--
   and (TimeDataRetrieval + TimeProcessing + TimeRendering + [RowCount]) <> 0
ORDER  BY 'Minutes' DESC

And one of the results I'm not sure how to translate is:

|Minutes | timedataretrieval | timeprocessing | timerendering |  RowCount   |
  |243     | 3                 | 14453530       | 0             |  0          |

How is it possible to have a report processing for 4 hours, and return 0 rows, with a dataretrieval time of 3 miliseconds?
If this were a lone case I'd ignore it, but I've got a hundred similar ones. (and a few thousand ones that do make sense)

This has happened to me before when the InteractiveHeight was set to 0. The query only returned a few thousand rows, but since there was no paging in the report, SSRS was trying to put everything on a single page. Eventually I would hit the SSRS timeout before it reached the rendering phase, and have 0 rows returned.

If that doesn't apply to your situation, please check out this link for some additional troubleshooting ideas: http://technet.microsoft.com/en-us/library/bb522806(v=sql.105).aspx#Process


MySQL - Automatically Replicate structure-only from DB1 to DB2 on the same host? I have two databases:

- production
- development

Is there a way I could set a replication schema in order to replicate any structure changes from production to development? Preferably, structure only, no data replication.

Thanks

This can be achieved by using either black-hole storage engines or by invoking some schedule script to truncate the tables periodically.

Both approaches have prons and cons so use which suits you best. You can use following method when you want to use black-hole storage engine

1. Take back-up using mysqldump using following command

mysqldump --login-path=cred  --port=$i  --master-data=2 --databases user-databases > mysql_backup.sql

2. Replace storage engines from innodb to blackhole using following command

sed -i.bak 's#InnoDB#blackhole#g'  mysql_backup.sql

3. Now proceed to restore this backup on to your target server using following command

mysql --login-path=<cred> -h <emote_host> --show-warnings <mysql_backup.sql

4. Issue change master on slave server getting co-ordinates from the backup file.

5. Issue show slave status\G to verify slave is working.

6. Confirm from some table to verify that no data is being replicated.

You need to change default storage engine to Blackhole on slave server so structure of tables created on master is replicated and engine needs to be overwritten.

The second approach involves truncating tables periodically, this way you can recover or switch-over in case of some disaster.
Use following commands to print a truncate table for all user databases and then execute it in some script.

select concat('truncate ',table_schema,'.',table_name,';') from information_schema.tables where table_schema not in ('information_schema','mysql','performance_schema','sys','test');

Hope it helps


SQL Server 2019: Rasing errors between 1 and 13000 I've been struggling with a task which requires me to raise a number of errors for testing purposes. Of course the time consuming errors are between 1 and 13000. I've managed to do them, but I need to do it several times on multiple test instances and I wanted to ask if there's a way to raise SQL Engine errors which normally only could be raised by the engine itself. By 'a way', I mean any possible routes to generate those error messages.
The ultimate goal is that the messages appear in the current error log.
Any help would be appreciated!

Try RAISERROR syntax below;
RAISERROR(
          'Test error' -- Error message
          ,0 -- Severity
          ,1 -- State
         ) WITH LOG;

For more information, you can check out the document.


How to select NOT NULL columns from DB2 I've got a script I'm using against a DB2 database, and I'd like it to be able to check that all required values are present prior to trying an INSERT-- so I'd like to select a list of the not null columns from the table I'm about to insert into. Since the table definition may change I'd like to do this rather than using a static list so the script won't break if there's a new not null column added.

Is there a way to get these values from a DB2 query, maybe against syscat?

VERSIONNUMBER VERSIONTIMESTAMP    AUTHID    VERSIONBUILDLEVEL
-------------------------------------------------------
10010400    2016-12-03 16:46:01.509317  ADMIN   s140509

According to the IBM DB2 UDB for iSeries SQL Reference
V5R3  documents, the SYSCOLUMNS view contains an IS_NULLABLE column.

DB2 for z/OS 10 has a NULLS column that indicates if a column is nullable in SYSIBM.SYSCOLUMNS table

Potentially, SYSCAT.COLUMNS may have the IS_NULLABLE or NULLS column.

I'm not a DB2 expert, so this may or may not be applicable to your system.


Tables with FK's of each other I'm working on a database that stores address information. (Related to this question I posted)

I have 2 tables:

CREATE TABLE [dbo].[Country] (
[Code]          CHAR (2)     NOT NULL,
[Code3]         CHAR (3)     NOT NULL,
[CodeNumeric]   CHAR (3)     NOT NULL,
[Name]          VARCHAR (50) NOT NULL,
[ContinentCode] CHAR (2)     NOT NULL,
[CurrencyCode]  CHAR (3)     NOT NULL,
PRIMARY KEY CLUSTERED ([Code] ASC),
CONSTRAINT [CountryContinentFK] FOREIGN KEY ([ContinentCode]) REFERENCES [dbo].[Continent] ([Code]),
CONSTRAINT [CountryCurrencyFK] FOREIGN KEY ([CurrencyCode]) REFERENCES [dbo].[Currency] ([Code])
);

and

CREATE TABLE [dbo].[City] (
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[Code]        VARCHAR (10)  NOT NULL,
[Name]        NVARCHAR (50) NOT NULL,
[CountryCode] CHAR (2)      NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [CityCountryFK] FOREIGN KEY ([CountryCode]) REFERENCES [dbo].[Country] ([Code])
);

Now I want to add a Capital to my Country table using a FK from the City table. I know that this is impossible because of the constraints (there has to be a City with the Capital's Id, but there also has to be a Country with the Country's Code for the City...if this makes any sense).

Approach 1:
I'm using this database together with C# and was thinking to write method that would first add a Country with a Capital that is nullable, then add the City to its table and update the Countries Capital with the City that was added.

Approach 2:
Another approach I consider is not adding Capital to the Country table but instead creating a new table CountryCapital and link both the Country and the City (Capital) together.

Which one of these 2 is the better approach? What I think is that approach 1 is a good solution if the database is maintained through C# but would be hard to keep up with if data is added manually. Approach 2 on the other hand looks easier to maintain if manual edits have to be made (add a City, add a Country and add a record in the CountryCapital table with the City's Id and the Country's Code).

For just the country/capital relationship I would go with option 1 and allow the capital to be NULL for a country record, rather than introducing an extra table. In both cases you are allowing the capital to be undefined at any given time so there is nothing to chose between the options from the completeness and referential integrity points of view.

If you do go for option 2, if the country column is not your PK make sure it is set to be UNIQUE otherwise you could accidentally end up with two capitals. With an extra "relationship type" column you could offer extra flexibility with this table though, by allowing things like "second city" and "religious capital" (some countries have this separate from their political capital for various reasons) to be defined.