Does SQL Server support custom DOMAINs? PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec,

  A domain is a named user-defined object that can be specified as an alternative to a data type in certain places where a data type can be specified. A domain consists of a data type, possibly a default option, and zero or more (domain) constraints.

This allows us to do really cool stuff like implement a domain for the HTML5-spec for email over a case-insensitive text type. It ensures that all clients accessing the database have an integrity check on the data inserted.

CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

Does SQL Server support any such functionality outside of the trigger system?

SQLCLR presents a surface area for creating fully custom data types.  In fact, that is how SQL Server supports geometric and hierarchy data types.

Since SQLCLR is based on the Microsoft.Net common-intermediate-language, a large variety of constraints are possible for a SQLCLR data type.  For instance, you could easily ensure an email address is from a valid domain by querying DNS for the MX record as part of the data validation code.

SQL Server can index a CLR UDT as long as IsByteOrdered:=True is set. There are a ton of properties like that, which you can change to affect how SQL Server uses the UDT.  For equality matching like that required by an index, SQL Server simply looks at the binary value stored in the page i.e. it doesn't need to look at the UDT code at all.

As an example of a SQLCLR User-defined-type that checks a domain-space for validity, I've written the following terrible proof-of-concept VB.Net code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered:=True, MaxByteSize:=320, ValidationMethodName:="ValidateEmailAddress")> _
Public Structure EmailType
    Implements INullable
    Implements IBinarySerialize

    Private m_Null As Boolean
    Private m_EmailAddress As String

    Public Function ValidateEmailAddress() As Boolean
        'is the email address valid?
        If Me.IsValidDomain Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overrides Function ToString() As String
        Return Me.m_EmailAddress
    End Function

    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
        Get
            ' Put your code here
            If Me.m_EmailAddress Is Nothing Then
                Me.m_Null = True
            Else
                Me.m_Null = False
            End If
            Return m_Null
        End Get
    End Property

    Public Shared ReadOnly Property Null As EmailType
        Get
            Dim h As New EmailType
            h.m_Null = True
            Return h
        End Get
    End Property

    'called when SQL Server passes in a SqlString value to the UDT
    Public Shared Function Parse(ByVal s As SqlString) As EmailType
        If s.IsNull Then
            Return Null
        End If

        Dim u As New EmailType

        u.m_EmailAddress = CType(s, String)
        If u.ValidateEmailAddress = False Then
            Throw New Exception("Invalid Email Address")
        End If
        Return u
    End Function

    Public Function IsValidDomain() As Boolean
        Dim iAtSign As Int32 = Microsoft.VisualBasic.Strings.InStr(Me.m_EmailAddress, "@")
        Dim iDomainLength As Int32 = Microsoft.VisualBasic.Strings.Len(Me.m_EmailAddress) - iAtSign
        Dim sDomain As String = Microsoft.VisualBasic.Strings.Right(Me.m_EmailAddress, iDomainLength)
        Dim bResolvable As Boolean = False
        Try
            Dim ip As System.Net.IPHostEntry = System.Net.Dns.GetHostEntry(sDomain)
            bResolvable = True
        Catch ex As Exception
            Throw New Exception(Me.m_EmailAddress & " is not from a resolvable domain.")
        End Try
        Return bResolvable
    End Function

    ' save the value to the database
    Public Sub Write(w As System.IO.BinaryWriter) Implements IBinarySerialize.Write
        w.Write(Me.m_EmailAddress)
    End Sub

    ' retrieve the value from the database
    Public Sub Read(r As System.IO.BinaryReader) Implements IBinarySerialize.Read
        Dim sTemp As String = r.ReadString
        Dim sTemp1 As String = ""
        For Each n As Char In sTemp.ToCharArray
            sTemp1 = sTemp1 & n.ToString
        Next
        Me.m_EmailAddress = sTemp
    End Sub

End Structure

Since the code above is not signed, you should test it only on a development machine where you can enable the TRUSTWORTHY database setting.  Once the code is compiled, you import it into SQL Server via the following:

CREATE ASSEMBLY SQLCLREmailType
AUTHORIZATION dbo
FROM 'C:\Path\Goes\Here\SQLCLREmailType.dll'
WITH PERMISSION_SET = UNSAFE;

CREATE TYPE EmailType
EXTERNAL NAME SQLCLREmailType.[SQLCLREmailType.EmailType]

The UDT can then be used like this:

DECLARE @t TABLE (
    col EmailType NOT NULL
    );

INSERT INTO @t (col)
VALUES ('mvernon@mvct.com')
    , ('us@them.com');

SELECT CONVERT(varchar(50), t.col)
FROM @t t
GO

The above code returns:

+------------------+
| (No column name) |
+------------------+
| mvernon@mvct.com |
| us@them.com      |
+------------------+

However, when attempting to insert an address belonging to a non-existent email domain, as in:

DECLARE @t TABLE (
    col EmailType NOT NULL
    );

INSERT INTO @t (col)
VALUES , ('us@asdfasdfasdfasdfasdfasdfasdfasdf90097809878907098908908908908.com');

SELECT CONVERT(varchar(50), t.col)
FROM @t t
GO

You see an error:

  Msg 6522, Level 16, State 2, Line 27
  A .NET Framework error occurred during execution of user-defined routine or aggregate "EmailType":
  System.Exception:  us@asdfasdfasdfasdfasdfasdfasdfasdf90097809878907098908908908908.com is not from a resolvable domain.
  System.Exception:
     at SQLCLREmailType.EmailType.IsValidDomain()
     at SQLCLREmailType.EmailType.ValidateEmailAddress()
     at SQLCLREmailType.EmailType.Parse(SqlString s)


SQL delete where not in (...) query not behaving as expected We have the following query which returns the most recent fares from our database :
SELECT f1.id
  FROM ( SELECT * FROM fares ) AS f1
INNER JOIN ( SELECT origin_id
                  , destination_id
                  , market_id
                  , cabin
                  , tripType
                  , max(created_at) AS maxDate
               FROM fares
              GROUP BY origin_id
                     , destination_id
                     , market_id
                     , cabin
                     , tripType
            ) AS f2  ON f2.origin_id      = f1.origin_id
                    AND f2.destination_id = f1.destination_id
                    AND f2.market_id      = f1.market_id
                    AND f2.cabin          = f1.cabin
                    AND f2.tripType       = f1.tripType
 WHERE f1.created_at = f2.maxDate
 GROUP BY f1.origin_id
        , f1.destination_id
        , f1.market_id
        , f1.cabin
        , f1.tripType

In our test dataset (SQL Fiddle) this returns two rows. When we change the query to work as a delete query it deletes everything but three rows, while it should delete everything but the two rows:
DELETE FROM fares
 WHERE id NOT IN
     ( SELECT f1.id
         FROM ( SELECT * FROM fares ) AS f1
        INNER JOIN ( SELECT origin_id
                          , destination_id
                          , market_id
                          , cabin
                          , tripType
                          , max(created_at) AS maxDate
                       FROM fares
                      GROUP BY origin_id
                             , destination_id
                             , market_id
                             , cabin
                             , tripType
                   ) AS f2  ON f2.origin_id = f1.origin_id
                           AND f2.destination_id = f1.destination_id
                           AND f2.market_id = f1.market_id
                           AND f2.cabin = f1.cabin
                           AND f2.tripType = f1.tripType
        WHERE f1.created_at = f2.maxDate
        GROUP BY f1.origin_id
               , f1.destination_id
               , f1.market_id
               , f1.cabin
               , f1.tripType
    )

We noticed that the three rows that remain are also the product of the select query when the GROUP BY part is ommited.
Now the question is why does the SELECT query in the DELETE statement behave different than the SELECT statement by itself?
Reproduction:

Take the schema from this SQLFiddle: http://sqlfiddle.com/#!9/e943ee/2
Run the query supplied in the SQLFiddle, only two rows are returned
Add the delete query to the schema & rebuild the schema
Execute "SELECT * FROM fares" and three rows are returned (where there should have been only two)

If you want to purge an outdated data then your query is a bit complicated:
DELETE FROM fares AS f1
  LEFT JOIN ( SELECT origin_id
                   , destination_id
                   , market_id
                   , cabin
                   , tripType
                   , max(created_at) AS maxDate
                FROM fares
               GROUP BY origin_id
                      , destination_id
                      , market_id
                      , cabin
                      , tripType
            ) AS f2  ON f2.origin_id      = f1.origin_id
                    AND f2.destination_id = f1.destination_id
                    AND f2.market_id      = f1.market_id
                    AND f2.cabin          = f1.cabin
                    AND f2.tripType       = f1.tripType
                    AND f2.maxDate        = f1.created_at  /* was omitted */
      WHERE f2.maxDate IS NULL
)


Why is Oracle so much slower for small atomic inserts compared to Postgres? And what defaults can I change to remedy this? If I create a brand new database from a Docker image and run a fresh database with the default settings.
CREATE TABLE "TEST_TABLE" (
    "ID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
    "CREATED" TIMESTAMP (6) DEFAULT NULL NOT NULL ENABLE,
    "REFERENCE" NVARCHAR2(500),
    PRIMARY KEY(ID)
);

This insert statement takes between 15ms and 30ms to complete (after being run a couple of times).
INSERT INTO test_table VALUES ('1', '10-SEP-02 14.10.10.123000000', 'hello world');

However if I run the same query in Postgres it takes 2-3ms to complete (after being run a couple of times).
What defaults in Oracle cause such poor insert performance?

SQL> CREATE TABLE "TEST_TABLE" (
    "ID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
    "CREATED" TIMESTAMP (6) DEFAULT NULL NOT NULL ENABLE,
    "REFERENCE" NVARCHAR2(500),
    PRIMARY KEY(ID)
);

Table created.

SQL>

SQL> set timing on
SQL> INSERT INTO test_table VALUES ('1', systimestamp, 'hello world');

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

SQL> select executions, elapsed_time from v$sql where sql_text = 'INSERT INTO test_table VALUES (''1'', systimestamp, ''hello world'')';

EXECUTIONS ELAPSED_TIME
---------- ------------
         1         1121

Elapsed: 00:00:00.09
SQL>

The insert took 1121 microseconds = 1.121 milliseconds, on the first try, with default settings.
SQL> INSERT INTO test_table VALUES ('2', systimestamp, 'hello world');

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select executions, elapsed_time from v$sql where sql_text = 'INSERT INTO test_table VALUES (''2'', systimestamp, ''hello world'')';

EXECUTIONS ELAPSED_TIME
---------- ------------
         1          780

Elapsed: 00:00:00.09

Even quicker on additional attempts.
Problem is not with the database, but the software/hardware environment around it.


SQL Server Replication with 2 systems in different locations I have 2 Servers with static public IP Addresses located one in UK and another in US.

Can I implement SQL Replication on these 2 servers located in 2 different locations accessible via public IP addresses and the 2 systems not fall under any common domain or VPN?

I have tried to implement it on my servers. Here I faced the issue that to add subscriber it's asking machine name. It's not accepting IP address.

It's giving me an error saying replication requires actual server name to make connection to the server.

Do we need to put these 2 servers in Active Directory or any other VPN?

Probably an entry in your hosts file (in case of a default instance) or a SQL Client alias  will solve the issue.

However, be aware that if you don't configure any security measure, all your TDS traffic will be clear and sniffable. A VPN is your best option in my opinion.


Entity relationship problem I have 4 tables related like this (it's an example):

Company:
ID
Name
CNPJ

Department:
ID
Name
Code
ID_Company

Classification:
ID
Name
Code
ID_Company

Workers:
Id
Name
Code
ID_Classification
ID_Department

Suppose that I have a classification with id = 20, id_company = 1.
And a department that has id_company = 2 (that represents another company).

This will allow the creation of a worker that is from two companies because the classification and department are linked to the company separately. I don't want that to happen, so I think I have a problem with my relationships and I don't know how to solve it.

I don't think you have a problem with the relationships.  I think instead the problem is that by using surrogate keys (ie Ids) for each table the resulting database is unable to prevent Workers from being inserted whose Department is of one Company while the Classification is of another and vice versa.  A good way to understand this is to visualize the schema using an ER Diagramming tool.  I will use the Oracle Data Modeler tool which is a free download.
ER Diagram

As it stands, you could have 2 companies - say IBM and Microsoft.  IBM can have a Software Development department, and Microsoft can have a Desktop Software department.  IBM can have a Software Engineer classification, and Microsoft can have a Software Developer classification.  Now, because you have a surrogate key for Department and Classification, the fact that Software Development is an IBM department and Desktop Software is a Microsoft department is lost for future child relationships.  This is also the case with Classification.  Therefore it is easy to accidentally assign Harlan Mills, who is an IBM employee in the Software Development department, a classification of Software Developer which is a Microsoft classification!  Likewise, the worker could be given the right classification and wrong department!  Here is a diagram showing the first example:

The 1 Ids represent IBM, and the 2 Ids represent Microsoft.  I've highlighted in red the scenario where Harlan Mills and Bill Gates are assigned to the wrong departments, which is visualized by the 10 department Id associated to the 200 classification Id and vice versa.
Options to Resolve
So what are the options to prevent his from happening?  There are two immediate options.  The first is to realize that by using a surrogate key for every table this issue exists and introduce additional programming to verify it doesn't occur.  This could be done in the application, but if inserts and updates can occur outside of the application then incorrect associations can still occur.  A better approach would be to create a trigger that fires on insert and update of an employee to make sure that the assigned department is of the same company as the assigned classification, and if not fail the insert or update.
The second option is to not use surrogate keys for every table.  Instead, use the surrogate keys only for the Company table, which is fundamental and has no parents, and then create identifying relationships to the Department and Classification child tables.  The Department and Classification tables now have a PK of the Company Id plus a Sequence Number or Name to distinguish them.  Then, the relationships from Department and Classification to Worker also become identifying and thus the PK of Worker becomes the Company Id, plus the Department Number (I'm using a sequence number in this example), plus the Classification Number.  The result is there is only one Company Id in the Worker table.  It is now impossible to assign a Worker to a Department in one Company and to a Classification in another Company.
Why is this impossible?  It is impossible because the schema implements referential integrity between Worker and Department and Classification.  If an attempt is made to insert a Worker for a Department in one Company and and a Classification of another, the combination that does not exist in the corresponding parent table will trigger a referential integrity violation and the insert will not work.
Here is an updated diagram of an implementation of the second option:

Preferred Option
Of the two options, I absolutely prefer the second - using the identifying relationships and the cascading keys - for two reasons.  First, this option achieves the desired rule with no additional programming.  Developing a trigger is not trivial.  It must be coded, tested, and maintained.  Ensuring the trigger logic is optimal so as not to impact performance is also not trivial.  The book Applied Mathematics for Database Professionals provides a lot of detail on the complexity of such a solution.  Second, the rules imply that a Department and a Classification can not exist outside the context of the Company, and so the schema now more accurately reflects the real world.
This is a great question because it shows exactly why simply assuming every table requires a surrogate key is a bad idea.  Fabian Pascal has an excellent blog post on just this topic showing that not only can a surrogate key be a bad idea from a data integrity standpoint it can also result in making some retrievals slower at the physical level precisely because joins are required that, had keys been properly cascaded, would be unnecessary.  Another interesting topic this question reveals is that a database cannot ensure that all data inserted into it is accurate with respect to the real world.  Instead, it can only ensure that the data inserted into it is consistent with the rules declared to it.  In this case we can do the best possible by using the cascading key approach to ensure the DBMS can keep the data consistent with respect to the rule that a Worker of a given Company needs to be assigned a Classification and a Department of that same Company.  But, if in the real world Microsoft has a department called Desktop Software but the user of the database asserts the department is instead Software Development the DBMS can do nothing but assume it has been given a true fact.


Log File Size and dbcc sqlperf (logspace) Consider the following log file setting for a database named XYZ:
Initial size = 250MB, Autogrowth by 500MB, Limited to 1GB
I have created a SQL Job to monitor the size of XYZ database ldf using the following script and insert it into a table in a different database named ABC.
DECLARE @sql_command VARCHAR(MAX)
DECLARE @action VARCHAR(MAX)
DECLARE @size REAL
DECLARE @sizeUsed REAL

IF exists (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = OBJECT_ID(N'tempdb..#TempForLogSpace'))
    DROP TABLE #TempForLogSpace;

CREATE TABLE #TempForLogSpace
(
    DBName varchar(MAX),
    LogSize real,
    LogSpaceUsed real,
    Status int
)

SELECT @sql_command = 'dbcc sqlperf (logspace)'
INSERT INTO #TempForLogSpace EXEC (@sql_command)

SELECT @size = LogSize, @sizeUsed = LogSpaceUsed FROM #TempForLogSpace WHERE DBName = 'XYZ'

INSERT INTO dbo.ABC.tbl (DBName, FileSize, SpaceUsedPercentage, LoggedDate)
VALUES('XYZ', @size, @sizeUsed, GETDATE())

Initial result after running it for several hours is as follows

My expectations is that,

as transactions being made in database XYZ, the SpaceUsedPercentage = LogSpaceUsed (DBCC)
should be increasing,
and if the ldf size reaching the initial size, SQL server
would increase the ldf file size by another 500MB and so would the the value of
FileSize = LogSize (DBCC).

But after seeing the result:

Seems like SpaceUsedPercentage is increasing/decreasing from
time to time - what is actually happening?
If the ldf reaching initial size - will the FileSize captured is
the initial size or the increased size?

Thank you for your kind attention.

The space used will decrease like that for different reasons based on the recovery model of the database named XYZ.
First of all, the transaction log is "circular," meaning that portions of it (virtual log files or VLFs for short) can be reused once they are no longer needed.  Marking a VLF for reuse is called "clearing" or "truncating" the transaction log.
If XYZ is in the Simple recovery model, then VLFs will be marked as resuable whenever a checkpoint operation occurs in the database (be it manual, or one of the automated checkpoints that occurs periodically).
If it's in the Full recovery model, VLFs can be reused once the information they contains has been backed up in a log backup.
It's likely one of those two things happening periodically that's causing space used to go down.
If VLFs aren't cleared fast enough, the log file will grow per your configuration / expectations (and File Size will change to the increased size).

You should check out the MS Docs page on SQL Server Transaction Log Architecture and Management Guide for detailed information on how the transaction log works.


Select rows based on values from 1 column being the same? An example of my database table

film id | scene id | value id
-----------------------------
   0    |     0    |     7
   0    |     1    |     1
   0    |     1    |     0
   1    |     2    |     8
   1    |     2    |     3
   1    |     3    |     1
   2    |     4    |     3
   2    |     4    |     7
   2    |     5    |     2

I want to select rows where the value id is equal to 8 or 3 only if they are both in a row with the same scene id.

My desired result would be:

film id | scene id | value id
-----------------------------
   1    |     2    |     8
   1    |     2    |     3

I tried grouping by count of scene id but it would output this:

film id | scene id | value id
-----------------------------
   1    |     2    |     8
   1    |     2    |     3
   2    |     4    |     3

where the last row was included because there were multiple values of scene id.

for selecting values based on a specific column being the same you could have a look at NTILE

  Distributes the rows in an ordered partition into a specified number
  of groups.  The groups are numbered, starting at one.  For each row,
  NTILE returns the number of the group to which the row belongs.

there are actually many options, including common_table_expression

on your example:

--==================================================================================
use tempdb
if object_id('tempdb..radhe','U') is not null
   drop table radhe

create table  radhe(
 [Film Id] int not null
,[Scene Id] int not null
,[Value Id]int not null
)

insert into radhe ([Film Id],[Scene Id],[Value Id])
select
   0    ,     0    ,     7
union all select
   0    ,     1    ,     1
union all select
   0    ,     1    ,     0
union all select
   1    ,     2    ,     8
union all select
   1    ,     2    ,     3
union all select
   1    ,     3    ,     1
union all select
   2    ,     4    ,     3
union all select
   2    ,     4    ,     7
union all select
   2    ,     5    ,     2

--I want to select rows
--where the value id is equal to 8 or 3
--only if they are both in a row with the same scene id.

--using a cte
;with t8 as (

        SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
        FROM radhe r
        WHERE r.[Value Id]IN (8)
) ,
t3 as (

        SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
        FROM radhe r
        WHERE r.[Value Id]IN (3)
)

select t8.*
from t8
inner join t3
        on t8.[Scene Id] = t3.[Scene Id]
union all
select t3.*
from t8
inner join t3
        on t8.[Scene Id] = t3.[Scene Id]

--====================================================================

--using NTILE
select r.[Film Id], r.[Scene Id], r.[Value Id]
from
(
SELECT
    NTILE(2) OVER (ORDER BY [Scene Id]) [NTILE],
    *
FROM Radhe
WHERE [Value Id]IN (3,8)
) r
where r.NTILE = 1

the results are the same but what about the performance?

check this and this out.


How to check that stored procedure is valid As we know, it is possible to compile and to have so stored procedures:

CREATE PROC dbo.MyProc1
AS
  SELECT * FROM Not_Exists_Or_Removed_Table

or

CREATE PROC dbo.MyProc2
AS
  SELECT
   Id
   ,Removed_Column
 FROM MyValidTable

The table Not_Exists_Or_Removed_Table is not exists or was removed (MyProc1) and  column Removed_Column was removed (MyProc2).

How to detect and prevent creating so stored procedures?

According to the MSDN article - Deferred Name Resolution and Compilation only the first situation is working, second is not:
"Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table."

For checking this kind of situations, I'd use a database project in Visual Studio with automatic (daily) deploy and verification of all warnings (maybe treat warning as errors), because these issues would be warnings, not errors.


How to get SQL Server Compact 4.0 Database content query? I need to migrate data to a SQL Server 2014 database from an existing SQL Server CE 4.0 database. I'm having difficulty because my computer runs SQL Server 2012 so I'm not able to port the data via a graphical wizard.

I am able to run scripts on all databases. I just don't know how to perform a the data migration without a graphical interface.

You can use my ExportSqlCe command line tools to export the schema and data, and then use sqlcmd to run the resulting script against a SQL Server 2014 database https://exportsqlce.codeplex.com/wikipage?title=Command%20line%20utilities&referringTitle=Documentation


Which query has better performance? I have 3 tables as follow:

class
  id:bigint(PK)
  name:varchar (unique)

principal:
  id: bigint(PK)
  pid:bigint
  role:boolean
  uniqueConstraint(pid, role)

entry:
  cid: (FK, reference class)
  pid: (FK, refernce principal)
  object_id: bigint
  code: tinyint
  PK: (cid, pid, obj)

The query must check existence of a record in entry using a parameter set.

suppose parameter set is as follow:

class's name: Class#3
principal id as user: 3
principal id as role: 2
object ID: 45

I wrote 2 queries, one using join and one using sub-query, but I don't know which one performs better in large scale production environment:

Query number 1:

select id
from entry
where pid in ( select id
               from principal
               where (pid=2 AND role)
                  OR (pid=3 AND !role))
  AND cid = (select id
             from class
             where name='Class#3')
  AND object_id=45

And query number 2:

select e.id
from class c
inner join entry e on e.cid=c.id
                  and c.name='Class#3'
inner join principal p on p.id=e.pid
                      and p.id in ( select id
                                    from principal
                                    where (pid=2 AND role)
                                       OR (pid=3 AND !role))
where e.object_id=45

Of course there's an additional condition for checking code which I didn't include it in queries

Suppose there are 100 rows in class, 10000 in principal and over 250000 in 'entry', and query(as explained) must be executed for each request, and there are 3000 users working on system constantly and simultaneously

Which of these queries will perform better And why? the reason is so important for further works
Is there better approach to write the query than these 2 approaches or even better approach to construct the schema?

Regards

PS:
I Have read this question about comparing sub-query and join, but my question is not exactly a simple comparison

UPDATED: fixed the role column name in principal table

Often the simplest way to express the query will perform the best.

The following query should be close to optimal:

select e.id
from entry e
join class c on c.id = e.cid
join principal p on p.id = e.pid
where e.object_id = 45
and c.name = 'Class#3'
and (p.id = 2 and p.role or p.id = 3 and !p.role);

But a lot depends on your data and your indexing. Compare execution plans to be sure.


Oracle SUM - ORA-00937: not a single-group group function For every journey want to sum them to work out the distance between.

SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between)
FROM Journey, Journey_Stages, Stages
WHERE Journey.Journey_No=Journey_Stages.Journey_No
AND Journey_Stages.Stage_ID=Stages.Stage_ID;

Tables are as followed:

CREATE TABLE Journey_Stages(Journey_No integer REFERENCES
Journey(Journey_No),Stage_ID integer REFERENCES Stages(Stage_ID));

CREATE TABLE Stages(Stage_ID integer PRIMARY KEY, Start_Station
integer REFERENCES Stations(Station_ID), End_Station integer
REFERENCES Stations(Station_ID));

CREATE TABLE Journey(Journey_No integer PRIMARY KEY, Train_No integer
REFERENCES Train(Train_No));

However I am getting the following error:

ORA-00937: not a single-group group function
00937. 00000 -  "not a single-group group function"
*Cause:
*Action: Error at Line: 273 Column: 26

I am certain this is something simple to resolve.

You should do one of two things:

OPTION #1: Keep only aggregate columns

SELECT SUM(Stages.Distance_Between)
FROM Journey, Journey_Stages, Stages
WHERE Journey.Journey_No=Journey_Stages.Journey_No
AND Journey_Stages.Stage_ID=Stages.Stage_ID;

OPTION #2: Add a GROUP BY clause

SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between)
FROM Journey, Journey_Stages, Stages
WHERE Journey.Journey_No=Journey_Stages.Journey_No
AND Journey_Stages.Stage_ID=Stages.Stage_ID
GROUP BY Journey.Journey_No, Stages.Stage_ID;


PostgreSQL postmaster will not start after configuring for SymmetricDS I'm trying to configure SymmetricDS for use with PostgreSQL, a database platform widely supported by the former.

When running through the quick-start tutorial (which leads you to some pre-setup documentation), I am instructed to append the following line to postgresql.conf:

custom_variable_classes = 'symmetric'

The next step of the tutorial requires the postmaster to be running (at least that's what I glean from the stack trace...), so I

service postgresql-9.2 start

but this fails.

Without this single line added (or if it's commented out), PostgreSQL starts as expected.

I must be doing something wrong, so what is it?

yum install postgresql92 postgresql92-server
Download and unzip SymmetricDS into ~
service postgresql-9.2 initdb
Edit /var/lib/pgsql/9.2/data/postgresql.conf as above

I must be doing something wrong, so what is it?

Per the 9.2 release notes, custom_variable_classes was removed. It is no longer required or supported. You can just leave it out without problems.


PostgreSQL 10 Optimizing slow query performance with aggregate function I have two tables person and tag.
person has an bigint[] column containing ids of tags.
I want to write a query which fetches all tags including the count of the tagged persons.
So far I have the following query

SELECT t.id AS id, t.name AS name,
(select count(id) from person where person.tags @> array[t.id]) as instances_count
 FROM tag AS t

I know the sub-query is bad but I couldn't think of a way of doing it better.

Any suggestions are welcome.
Thank you

Edit:
PostgreSQL 10.8

Here is the 'explain analyze':

Seq Scan on tag t  (cost=0.00..4309761.27 rows=79 width=41) (actual time=131.109..11329.945 rows=79 loops=1)
  Buffers: shared hit=560506 read=3505230
  SubPlan 1
    ->  Aggregate  (cost=54553.91..54553.92 rows=1 width=8) (actual time=143.411..143.412 rows=1 loops=79)
          Buffers: shared hit=560505 read=3505230
          ->  Seq Scan on person i  (cost=0.00..54550.82 rows=1233 width=8) (actual time=41.367..143.393 rows=148 loops=79)
                Filter: ((NOT is_deleted) AND (tags @> ARRAY[t.id]))
                Rows Removed by Filter: 245452
                Buffers: shared hit=560505 read=3505230
Planning time: 0.098 ms
Execution time: 11330.014 ms

You really need an index on the array:

create index on person using gin (tags);

It is not obvious to me that the subselect is worse than any other way of accomplishing the same thing.

Your query plan doesn't match your query text, "is_deleted" occurs in the plan but not the text.  If you want to optimize for that you could do:

create index on person using gin (tags) where not is_deleted;

How important that is depends on what fraction of your table is_deleted.

An alternative method not needing the index would be

select * from
    (select unnest(tags) as id, count(id) from person group by 1) agg
join
    tag
using (id);

But this would not return any tags which had a count of zero.  You could make the join a right join, but then reported count would be null rather than zero.


My.cnf with 64GB RAM May the following my.cnf file be fine for a dedicated server with 64GB of RAM (on Debian)?

I'm running just one website on the server and on high load data insert in some table takes centuries...it seems that the DB can't handle some table lock (50Mb tables, not so much).

If it is fine, then i'll step to analyze every single query.

Any help would be truly appreciated!

[client]

port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

back_log = 50

skip-networking

max_connections = 100

max_connect_errors = 10

table_open_cache = 4096

table-definition-cache = 4096

skip-external-locking

skip-name-resolve

max_allowed_packet = 32M

binlog_cache_size = 2M

max_heap_table_size = 64M

read_buffer_size = 4M

read_rnd_buffer_size = 32M

sort_buffer_size = 16M

join_buffer_size = 8M

thread_cache_size = 20

thread_concurrency = 16

query_cache_type=1

query_cache_size = 1024M

query_cache_limit = 2M

ft_min_word_len = 4

default-storage-engine = MYISAM

thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 4G

log-bin=mysql-bin

binlog_format=mixed

slow_query_log

long_query_time = 2

server-id = 1

key_buffer_size = 16G

bulk_insert_buffer_size = 256M

myisam_sort_buffer_size = 246M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 12G

innodb_data_file_path = ibdata1:10M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 8192

A good tool to help you tune MySQL config is the Percona Configuration Wizard for MySQL. This asks you some questions about your system and your workload, and gives you some approximate tuning values to start with.

Of course the term tuning suggests that further refinement may be beneficial, but this happens over time as you monitor your workload, resources usage, and bottlenecks. It's not something we can answer once and for all in a post like this one.

However, I have other comments about your config:

max_allowed_packet = 32M

This is not going to take effect, because you override it with a smaller value later in your config file. See below.

binlog_cache_size = 2M

This is 64x the default. Unless you have a good reason to increase it, don't. Note that the binlog cache is allocated per user thread, so if you have 100 concurrent transactions, this would use 200MB of RAM. Suggest you remove this line and use the default.

max_heap_table_size = 64M

This constrains tmp_table-size. See below.

read_buffer_size = 4M

This is 32x higher than the default. It's used only during table-scans of MyISAM tales. It would be better to create appropriate indexes and avoid table-scans. Suggest you remove this line and use the default.

read_rnd_buffer_size = 32M

This is 128x higher than the default, and 16x higher than the maximum allowed value of 2M. This is used only for MyISAM reads in MySQL 5.5 and earlier, and in MySQL 5.6 it's also used for multi-range reads. But it's probably not necessary to increase it. Suggest you remove this line and use the default.

sort_buffer_size = 16M

This is 4x higher than the default. In MySQL 5.6, it's 16x higher than the default. This is also a buffer that is allocated for each user thread, so be careful how much you increase it. Suggest you remove this line and use the default.

join_buffer_size = 8M

This is 64x higher than the default, so be sure you have a good reason for increasing it. It's used during index scans and joins on unindexed columns. It would be better to index tables better. Keep in mind that the join buffer can be allocated for each user thread, so this 8MB amount can account for unpredictable memory growth. Suggest you remove this line and use the default.

thread_cache_size = 20

Modern Linux kernels can create threads a lot faster than in the old days, so it's no longer as important to keep a thread cache. Suggest you remove this line and use the default.

thread_concurrency = 16

Are you running on Solaris? If not, this variable has no purpose. Suggest you remove this line.

query_cache_type=1
query_cache_size = 1024M

This is a lot of RAM to dedicate to the query cache, and it's probably overallocated. The query cache has some downsides, especially when you have many concurrent connections. Frequently we actually recommend to disable the query cache unless you can demonstrate you're getting a lot of bang for the buck.

Disable by setting both query_cache_type=0 and query_cache_size=0.

ft_min_word_len = 4

This is the default. Suggest you remove this line and use the default.

default-storage-engine = MYISAM

Recommend to set the default storage engine to InnoDB.

I recommend against using MyISAM for anything. MyISAM supports table-locking only, it's susceptible to corruption in crashes, it's not being developed anymore, and it's gradually being phased out. Claims that MyISAM is faster than InnoDB are based on very old versions of MySQL. Even as far back as 2007, benchmarks show that InnoDB is faster than MyISAM under most workloads.

One of the last reasons to use MyISAM, fulltext indexes, is also obsolete. InnoDB is getting fulltext indexes (they were introduced in 5.6, but they're not really usable yet). Sphinx Search is better for fulltext search anyway.

thread_stack = 192K

This is actually lower than the default of 256K on 64-bit systems. Suggest you remove this line and use the default.

transaction_isolation = REPEATABLE-READ

This is the default. Suggest you remove this line and use the default.

tmp_table_size = 4G

This is not going to allow any tmp tables higher than max_heap_table_size, which you set to 32M earlier. So if you want large tmp tables, you must also increase that config variable to match. But be careful, because you could end up with multiple threads populating 4G tmp tables in memory, and then start swapping.

key_buffer_size = 16G
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 246M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

These are used only by MyISAM, and you don't need them if you don't use MyISAM.

innodb_additional_mem_pool_size = 16M

This is moot on modern Linux kernels, where dynamic memory allocation is actually more efficient than letting InnoDB preallocate memory. Suggest you remove this line and use the default.

innodb_buffer_pool_size = 12G

This should be enough to hold all your InnoDB data and indexes, if possible, plus maybe about 10% extra for the change buffer.

You can query the size of your InnoDB data and indexes:

SELECT SUM(data_length+index_length)/1024/1024 AS total_InnoDB_size_in_MB
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'InnoDB';

If that total greater than the size of your physical system RAM (64G), then increase the buffer pool as much as is practical. Usually the advice is about 80% of your system RAM, assuming you don't have any other big consumers of memory on the same server. If you do, subtract the memory needed for other processes.

If your data is a lot smaller than your system RAM, then there's no benefit to allocating a big buffer pool. Only one copy of each page of data and indexes is held in the buffer pool.

innodb_data_file_path = ibdata1:10M:autoextend

This is the default.

innodb_file_io_threads = 4

This variable is not recognized after MySQL 5.0 (or 5.1 unless you enable the InnoDB plugin). Use innodb_read_io_threads and innodb_write_io_threads. Besides, you're setting the variable to the default value anyway, so you don't need this line in your config file.

If you're still running a version of MySQL old enough that this variable is recognized, I strongly suggest you upgrade.

innodb_thread_concurrency = 16

This is probably outdated, unless you're running a very old version of MySQL. Suggest you remove this line and use the default of 0 (unlimited thread concurrency) unless you observe a bottleneck.

innodb_flush_log_at_trx_commit = 1

This is the default. Suggest you remove this line and use the default.

innodb_log_buffer_size = 8M

This is the default. Suggest you remove this line and use the default.

innodb_log_file_size = 256M

Is this enough? Too much? Here's a blog to help: http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

innodb_log_files_in_group = 3

There's no benefit to changing the innodb log file in group. Suggest you remove this line and use the default.

innodb_max_dirty_pages_pct = 90

This was the default in MySQL 5.0. Now the default is 75. But it's not likely you'll have so many dirty pages regardless. Suggest you remove this line and use the default.

innodb_lock_wait_timeout = 120

The default is 50 (seconds), and should be adequate for most workloads. Do you have a reason to increase this to 120?

max_allowed_packet = 16M

You set max_allowed_packet to 32M earlier. Keep in mind that if you set a given variable in the config file more than once, the last setting takes precedence. So your packets are limited to 16M. If you intend them to be allowed up to 32M, then take out this line.

[myisamchk]

[mysqlhotcopy]

myisamchk and mysqlhotcopy are only used for MyISAM and ARCHIVE tables. If you migrate to use InnoDB, you won't need to use these tools.


SQL Jobs Running - How to Kill I ran the below query and it shows list of currently running jobs. However, in Activity Monitor none of these are running. How do I verify that these are/are not running?

And how do I kill them?

    select sj.name
    ,sja.*
from msdb.dbo.sysjobactivity as sja
inner join msdb.dbo.sysjobs as sj on sja.job_id = sj.job_id
where sja.start_execution_date is not null
and sja.stop_execution_date is null

To Get Currently running jobs and their Status

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Currently running jobs and their Status

    SELECT  jb.name,
            jb.originating_server ServerName,
            ja.run_requested_date JobStartTime,
            DATEDIFF(MINUTE, ja.run_requested_date, GETDATE()) as TimeElapsed_Minutes,
            case when ja.last_executed_step_id is null
                 then 'Step '+Cast (start_step_id as varchar(20)) +' ['+sstep.step_name+'] executing'
                 else 'Step ' +Cast ( last_executed_step_id+1 as varchar(20))+' ['+ lstep.step_name
                      + '] executing'
            end CurrentStepID , sstep.step_name JobStartedfromStep
            , Case ja.run_requested_source when 1 then 'SCHEDULER'
                                   when 2 then 'ALERTER'
                                   when 3 then 'BOOT'
                                   when 4 then 'USER'
                                   when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end JobRunSource
    FROM    msdb.dbo.sysjobs_view jb
            INNER JOIN msdb.dbo.sysjobactivity ja ON jb.job_id = ja.job_id
            INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = ja.session_id
            LEFT JOIN MSDB.DBO.sysjobsteps sstep ON sstep.step_id =start_step_id and sstep.job_id=jb.job_id
            LEFT JOIN MSDB.DBO.sysjobsteps lstep ON lstep.step_id =last_executed_step_id + 1 and lstep.job_id=jb.job_id
    WHERE   stop_execution_date IS NULL
            AND run_requested_date IS NOT NULL

Stop\kill Job command

USE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GO


Use MySQL to regularly do multi-way joins on 100+ GB tables? Background:
I’ve created a web application that I would like to be able to scale reasonably well. I know I'm not Google or Twitter, but my app uses a fairly large amount of data for each user and thus has fairly high data requirements. I want to be ready to scale reasonably well without having to re-architect everything later.

I consider myself a software developer, not a database expert. That’s why I am posting here. Hopefully someone with a lot more database expertise can give me advice.

With a relatively large number of users, but nothing like Facebook numbers, I expect to have a DB that looks like this:

One "Big table":

250 million records
20 columns
Approximately 100 GB of data
Has an indexed bigint(20) foreign key
Has an indexed varchar(500) string_id column
Has an int(11) "value" column

4 other tables:

10 million records each
Approximately 2 - 4 GB of data each
each of these tables has 4 - 8 columns
one column is datetime date_created
one column is the varchar(500) string_id column
one or two columns from each of these tables will be selected in a join

One of these tables is used for storing averages -- its schema is bigint(20) id, varchar(20) string_id, datetime date_created, float average_value

What I want to do -- two relatively expensive queries:

Calculate new average values:

Using a foreign key, select up to several million separate records from the big table.
Calculate a new average, grouping by the string_id .
Insert results into the averages table.
As currently constructed, this query uses two joins.

Create de-normalized, read-only records for serving users:

Use a foreign key to select anywhere from 1,000-40,000 records from the big table.
Join with each of the other four tables on the newest record with the string id column.
Insert the results into a de-normalized table.
These records are for use by the front-end to display information to users.
As currently constructed, this query uses four joins.

I plan to run each of these expensive queries on a batch back-end database that will push its results to a real-time front-end DB server which handles requests from users.
These queries will be run at regular intervals. I haven't decided how often. The average query could be done perhaps once per day. The de-normalize query will need to be more frequent -- perhaps every few minutes.

Each of these queries currently runs in a few seconds in MySQL on a very low-end machine with a dataset with 100K records in the “big table.” I am concerned about both my ability to scale and the costs of scaling.

Questions:

Does this approach seem sound? Is there anything obviously wrong with it from a big-picture perspective?
Is a RDBMS the right tool, or should I look at other "big data" solutions like something in the Hadoop family? My inclination is to use a RDBMS because the data is structured and fits nicely into the relational model. At a certain point though, it is my understanding that I may no longer be able to use a RDBMS. Is that true? When would this switch be needed?
Will it work? Can these queries be run in a reasonable amount of time? I can wait perhaps hours for query #1, but query #2 should finish in minutes.
What should I consider from a hardware perspective? What are my RAM and CPU bottlenecks likely to be? I assume keeping indexes in RAM is important. Is there anything else I should consider?
At some point I will probably have to partition my data and use multiple servers. Does my use case seem like it is already in that category, or will I be able to scale a single machine vertically for a while? Will this work with 10x the data? 100x?

Have you tried piling more data and benchmarking it? 100K rows is inconsequential. Try 250M or 500M like you're expecting you'll need to handle and see where the bottlenecks are.

An RDBMS can do a lot of things if you pay careful attention to the limitations and try and work with the strengths of the system. They're exceptionally good at some things, and terrible at others, so you will need to experiment to be sure it's the right fit.

For some batch processing jobs, you really cannot beat flat files, loading the data into RAM, smashing it around using a series of loops and temporary variables, and dumping out the results. MySQL will never, ever be able to match that sort of speed, but if tuned properly and used correctly it can come within an order of magnitude.

What you'll want to do is investigate how your data can be partitioned. Do you have one big set of data with too much in the way of cross-links to be able to split it up, or are there natural places to partition it? If you can partition it you won't have one table with a whole pile of rows, but potentially many significantly smaller ones. Smaller tables, with much smaller indexes, tend to perform better.

From a hardware perspective, you'll need to test to see how your platform performs. Sometimes memory is essential. Other times it's disk I/O. It really depends on what you're doing with the data. You'll need to pay close attention to your CPU usage and look for high levels of IO wait to know where the problem lies.

Whenever possible, split your data across multiple systems. You can use MySQL Cluster if you're feeling brave, or simply spin up many independent instances of MySQL where each stores an arbitrary portion of the complete data set using some partitioning scheme that makes sense.


Is it safe to delete mysql-bin files? I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those files like mysql-bin.000123, mysql-bin.000223 etc. I have checked the mysql replication by doing show master status and show slave status they are using some mysql-bin files at certain positions, but I guess all the other bin files are leftovers which will not be used anymore. In this case is it safe to delete all those mysql-bin files except the ones that replication is currently pointing at ?

If it is safe to delete, then is there anything I could do to automatically delete those files once they are not in use ?

Please do not just delete them in the OS.
You need to let mysqld do that for you. Here is how mysqld manages it:
The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are:
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';

These will clear all binary logs before the binlog or timestamp you just specified.
For example, if you run
PURGE BINARY LOGS TO 'mysql-bin.000223';

this will erase all binary logs before mysql-bin.000223.
If you run
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.
If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:
mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf
[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you
SHOW SLAVE STATUS\G
This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

Master_Log_File
Relay_Master_Log_File

When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run
PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

That way, replication is not interrupted.


MariaDB default utf8 The installation puts character-set-server=utf8 in my.ini by default. Is it better to use utf8mb4 instead of clean utf8 and if Yes, why utf8mb4 is not set by default?

depending on Version utf8 is a synonym for utf8mb3 or utf8mb4.
so you are always compatible with the version and you can restore a backup without problems, if the version has no utf8mb4 support


Abstracting away filtering behavior from Oracle SQL Function I have a Oracle SQL function, which returns a bunch of records through various sub-queries. However, there are countless filters that can be applied to these result records.

In an application programming language, I'd be able to abstract away the filtering behavior by allowing a function parameter to my function i.e. a callback in JS or Functional Interface object in Java.

Another possibility is having optional parameters or function overloading, but it still isn't scalable in regards to different filtering demands. Also, it won't work on stored procedures or functions.

I'm new to writing SQL procedures and functions, so I'm wondering is there any other option besides using an application programming language?

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks.
Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1738 What do you men it doesn't work? You must place overloaded subprogramms into packages, but it's very reasonable way of bulding Oracle-side soft in any case regardless to overloading. But I agree, it hardly can help with countless filter options.

Yes, application is the best place to build the SQL query depending on multiple user choises (filter by price and color, or by year and brand, ...).


Make MySQL dump from parts of each table I need to export some data from different tables. I tried this query

select * into outfile 'C:\Users\\User\\Desktop\\ololo.sql' from db.users where id % 2 = 1;

After this query, the SQL file was created. When I use id i in the the same query to export data from other table. Thus, an error happens. I need additional data from the other table. So, the result must be not dump of all database but part of it. Thanks for your answers.

You need to use mysqldump option --where like this:

mysqldump -uroot -p --where="id % 2 = 1" db users > C:\Users\User\Desktop\ololo.sql

Give it a Try !!!


Create pivot table from SQL query results I need to convert the results from my SCCM 2012 query about user device affinity to show multiple columns per computer name.
Example of query results :-

Desired results format :-

Is it possible to create the desired table as part of the query, or do I need to manipulate the results as a second step?  There are 130,000 lines in my original query output representing approx 90,000 machines.

Hope somebody can help.

Here you go. This is for SQL Server (since you haven't tagged the DB) but I guess this is OK since you mention SCCM.

There are heaps of examples of dynamic pivots on here if the final number of [UserX] columns is unknown.

SELECT * INTO #Temp FROM (VALUES
 ('PC001', 'a1\user1')
,('PC002', 'a1\user2')
,('PC002', 'a1\user3')
,('PC003', 'a1\user4')
,('PC003', 'a1\user5')
,('PC003', 'a1\user6')
,('PC004', 'a1\user7')
,('PC005', 'a1\user8')
) A([Computer Name], UserName);

SELECT *
 FROM
(SELECT 'User'+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [Computer Name] ORDER BY [Computer Name])) [User]
,*
FROM #Temp) Src
PIVOT
(MAX(Username) FOR [User] IN ([User1], [User2],[User3])
)Pvt

Computer Name User1    User2    User3
------------- -------- -------- --------
PC001         a1\user1 NULL     NULL
PC002         a1\user2 a1\user3 NULL
PC003         a1\user4 a1\user5 a1\user6
PC004         a1\user7 NULL     NULL
PC005         a1\user8 NULL     NULL

(5 row(s) affected)

Off the top of my head, yes, you can add this to your original query - put your query in place of my #Temp as in

SELECT *
   FROM
  (SELECT 'User'+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [Computer Name] ORDER BY [Computer Name])) [User]
,*
  FROM (Your query goes here) Blah
) Src
etc

) or just select your query into a temp table - just add INTO #TempTableName into your query.

Hope this helps.


Composite Foreign key to single primary key How do I create a composite foreign key to a table with a single primary key? For example suppose I have the following tables:

CREATE TABLE Branches (
    BranchId UniqueIdentifier DEFAULT NewSequentialID(),
    CONSTRAINT pk_BranchId PRIMARY KEY (BranchId)
);

CREATE TABLE Expectations (
    ExpectationId UniqueIdentifier DEFAULT NewSequentialID(),
    BranchId UniqueIdentifier NOT NULL,

    CONSTRAINT fk_Expectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId),
    CONSTRAINT pk_ExpectationId PRIMARY KEY (ExpectationId)
);

CREATE TABLE ClassSections (
    ClassId UniqueIdentifier NOT NULL,
    BranchId UniqueIdentifier NOT NULL,

    CONSTRAINT fk_ClassSections_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId),
    CONSTRAINT fk_ClassSections_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId),
    CONSTRAINT pk_ClassSections PRIMARY KEY (ClassID, BranchId)
);

CREATE TABLE ClassExpectations (
    ClassId UniqueIdentifier NOT NULL,
    BranchId UniqueIdentifier NOT NULL,
    ExpectationId UniqueIdentifier NOT NULL,

    CONSTRAINT fk_ClassExpectations_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId),
    CONSTRAINT fk_ClassExpectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId),
    CONSTRAINT fk_ClassExpectations_ExpectationId FOREIGN KEY (ExpectationId) REFERENCES Expectations(ExpectationId),
    CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId),
    CONSTRAINT pk_ClassDueDates PRIMARY KEY (ClassId, ExpectationId)
);

I'm trying to add the following constraint, but it won't allow me:

ALTER TABLE ClassExpectations ADD CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId);

I keep getting the error:

  There are no primary or candidate keys in the referenced table
  'Expectations' that match the referencing column list in the foreign
  key

How else do I ensure that an ExpectationId is never added to ClassExpectations without its corresponding BranchId (from the Expectations table)?

The answer is a little bit like the old joke: "Doctor, doctor! It hurts when I do this."

If you want a foreign key to an entity then that entity needs to have a unique key defined to be referenced.

The fact that your Expectations table has a unique candidate key in Expectations.ExpectationId should not prevent you from defining another candidate key that combines ExpectationId and BranchId.  This seems like a violation of third normal form, and technically it is.  However, that is only true because you happen to define ExpectationId as unique.  You could have defined ExpectationId as a sequence within BranchId, such that ExpectationId is not unique on its own.  As it is, you can think of it as the Expectation table defining the combination of two unique values as being the fact of interest.

In any case, if you want to define a foreign key to Expectation that is the combination of ExpectationId and BranchId then there is no way around it but to define the primary key that way.


Selecting info from users who have been inactive for at least a week I need to find the users who have not done a task in at least a week, and pull the relevant information from them. In this case, their id, names, and data1 from another table. This is what I have so far, but it's not working. Something to do with the joins I think?

Can someone help me out? :)

Additional info: the user_identity table is a bit complicated, with multiple rows with the same userid hence the distinct.

SELECT DISTINCT ON (u.id) u.id, u.given_name, u.family_name, i.data1
                FROM users u JOIN task t_week on u.id = t_week.user_id
                and t_week.created_at > %s JOIN user_identity i on (u.id=i.user_id)
                WHERE t_week.created_at IS NULL AND i.content_type='email_address' AND i.owner_id is NULL
                GROUP BY u.id, u.given_name, u.family_name, i.data1,i.created_at ORDER BY u.id, i.created_at

Assuming the date you are referring to is t_week.created_at, not i.created_at:

select * from (
    select DISTINCT ON (u.id) u.id, t_week.created_at, ...
        FROM users u JOIN task t_week on u.id = t_week.user_id JOIN...
        ORDER by u.id, t_week.created_at desc
) foo
WHERE t_week.created_at < %s
ORDER BY u.id, i.created_at

You have to use the sub-select because DISTINCT ON doesn't support a HAVING clause, so you wrap it in a sub-select and use a WHERE clause on the outside.

This would not find people have never done a task all.  To do that you would need to do a left join to task table, then

WHERE t_week.created_at < %s or t_week.created_at is null


How to know how healthy an SQL Server database is now? I want to know how healthy an SQL Server database is now (before I started to work with it).
Then, in a month or so, I want to check again to see how healthy database is at that time and compare it to the previous state.
Basically is there any way I can take something like a snapshot of the database health now and later? And then see what's been improved, so I can track my progress.
Any source of information would be great.

Look at sp_blitzfirst from www.brentozar.com
This store procedure will give you a good status of your SQL servers.


How to join sys.columns and sys.synonyms table using object_id column I have used two servers, Server A(local server) and Server B(remote server).
I have created synonyms in the Server A for the Server B table. I have created  synonym using following syntax.

CREATE SYNONYM RemoteServerATable FOR Server B .[database_name].[schema_name].object_name

I have tried to join two tables sys.columns and sys.synonyms using below query

select s.name as [SynonymName], c.name as [ColumnName], c.object_id from [Server B].[DatabaseName].sys.columns c inner join sys.synonyms s on s.name = 'RemoteServerATable' and  c.object_id  = 1973582069

I have joined two tables using object_id of the sys.columns tables in the other server and hard coded integer value. I got this value using below query

exec ('select object_id(''[Server B DatabaseName].[Server B SchemaName].[Server B ObjectName]'') as object_id') at [Server B]

I am not getting correct columns information. Kindly suggest someways join two tables and get the column information.

object_id() does not work for remote databases.

The documentation states:

  For objects that are not found in the sys.objects catalog view, obtain
  the object identification numbers by querying the appropriate catalog
  view

SQL that provides an answer to your query requirement is here.


I need help with a SQL Query. SQL beginner here! I'm totally new to SQL and I've inherited a customer's server (the company that managed it no longer exists). They need me to run a SQL Query that shows their inventory sold for pots.

Specifically, how many of each item sold by each price code during a specific period of time. The guy who pulled this info used a program called Crystal Reports, but he said these reports could be run via SQL Query. This is what they were able to get from the guy (he created the database) before he retired:

Items sold contained in:
ARInvoiceDetails & ARInvoiceHdr

ARInvoiceDetails is the main table.

ARInvoiceHdr contains invoice date.

ARInvoiceDetails contains item sold + price code (price code contained in ARPriceLevels).

Item description is obtained from ICItems.

Size is obtained from ICItemSizes (ICValidSizes).

Note: categories and subcategories are also contained in ICItems using ICCategories & ICSubCategories

ARInvoiceDetails contains:

Selling Price (ARDetSellingPrice) - my note: I don't see this one, but I do see a ARDetUnitPrice.

Price Code (ARDetPriceLevelID).

Quantity Sold (ARDetQty).

My issue(s) is that I can't figure out how to pull the data from both tables. I've done a little research into join clauses, but don't there need to be at least one column in common between the tables? I've been analyzing the output from Queries that show the column names from both tables. There are NO common column names. There is, however, a column in each table that has the same data but different column names.

ARInvoiceDetails - column name: ArDetPriceLevelID (price code)

ARInvoiceHdr - column name:  ArInvPriceLevelID (price code)

According to the person that requested the report, price code will tell them the size and color of pots. Any help would be appreciated! I have access to the server and can answer any questions if this isn't enough information. I can't stress this enough - I'm totally new to SQL and my level of SQL knowledge is poor at best. I'm using Microsoft SQL Server Management Studio version 10.50.4042.0

Thanks!

The columns in common, do not have to have the same name, they just need to have the same data.

  I've done a little research into join clauses, but don't there need to be at least one column in common between the tables? I've been analyzing the output from Queries that show the column names from both tables. There are NO common column names. There is, however, a column in each table that has the same data but different column names.

The easiest way to get started in SSMS (SQL Server Management Studio) is to use the it to get the basics of your query. Then fine tune it, be editing the code, it makes for you.

Go to the table, right click on the table name, select option "Select top 1000 rows. this will create the beginning of your query, and you can edit it until it gets you what you want.  If this is a production system you might want to limit to the top 25 until you get your query working well.

SELECT TOP (25) [ARInvoiceDetails].[ArDetPriceLevelID]
, [ARInvoiceHdr].[ArInvPriceLevelID]
  FROM [ARInvoiceDetails]
  LEFT JOIN [ARInvoiceHdr]
  ON [ARInvoiceDetails].[ArDetPriceLevelID] = [ARInvoiceHdr].[ArInvPriceLevelID]


MySQL - How to select rows until condition is met I have a table that has a numeric field (call it NUM), I want to select rows until the sum of all NUM selected reaches some threshold. Is there an efficient way to do this?

Assuming:

CREATE TABLE T
(  x int not null primary key
,  y int not null );

INSERT T(x,y)
VALUES (1, 1)
  ,(2, 1)
  ,(3, 2)
  ,(4, 8)
  ,(5, 3)
  ,(6, 5)
  ,(7, 4);

You can determine the cumulative sum like below, and then filter the result in an outer select:

select x,y,acc
from (
    select x, y, (select sum(y)
                  from T T2
                  where T2.x <= T1.x) as acc
    from T T1
) as tmp
where acc < 10;

+---+---+------+
| x | y | acc  |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 1 |    2 |
| 3 | 2 |    4 |
+---+---+------+

For DBMS that support window functions like sum(..) over ( ... ) much more elegant solutions exists.


How to unnest and display elements of a JSON array? i have this table :

And i want display all the names in the table like this :

Also i want to list the number of gender each genre : Desired output:

According to the documentation, the #> operator comes in useful:

-- name and genre of people (1 row per person)
select
    json_array_elements(
      t.test#>'{clients, info}'
        )::json
    ->>'nom' as nom,
    json_array_elements(
      t.test#>'{clients, info}'
        )::json
    ->>'genre' as genre
from my_table t

-- number of people, grouped by gender (1 row per gender)
select
      json_array_elements(
         t.test#>'{clients, info}'
           )::json
           ->>'genre' as genre,
      count (t.*) as count
from my_table t
group by 1

SqlFiddle


Filtering extended events with configuration table I've created E.E. session to audit logins, but there are many logins that I need to filter out.
I'm wondering, if there is a better option(like creating conf. table with unwanted logins and filter sqlserver.username using those values) than just adding them one by one via gui\script.

I would say it's better to collect the information then filter out at reporting time, because then at least you have it.  Constantly updating the session seems fiddly and someone else might be able to make use of it: "Oh I hear you are tracking logins over time, do you track login X?"

Also, have you thought about using the histogram target?  This might be a good use case for it.  There would be a single entry per login with a count property indicating how many times that login has logged in during that extended event session.  You would have to record the start and end time of the Extended Events (XE) session for it to be meaningful, but here's a simple example:

IF EXISTS ( SELECT * from sys.server_event_sessions WHERE name = 'xe_watchLogins' )
DROP EVENT SESSION [xe_watchLogins] ON SERVER
GO

CREATE EVENT SESSION [xe_watchLogins] ON SERVER
ADD EVENT sqlserver.login (
    ACTION ( sqlserver.username )
)
ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.login', source = N'sqlserver.username' )
WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF )
GO

ALTER EVENT SESSION [xe_watchLogins]
ON SERVER
STATE = START;
GO

/* Sample output from the histogram
<HistogramTarget truncated="0" buckets="256">
    <Slot count="4">
        <value>someDomain\bob</value>
    </Slot>
    <Slot count="1">
        <value>someSqlUser</value>
    </Slot>
</HistogramTarget>
*/

SELECT IDENTITY( INT, 1, 1 ) AS rowId, GETDATE() AS dateCreated, CAST( st.target_data AS XML ) XEhistogramData
INTO #tmp
FROM sys.dm_xe_sessions s
    INNER JOIN sys.dm_xe_session_targets st ON s.[address] = st.event_session_address
WHERE st.target_name = 'histogram'
  AND s.name = 'xe_watchLogins'

-- Optionally create xml index
ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( XEhistogramData );
GO

SELECT
    h.c.value('(value/text())[1]', 'SYSNAME') AS loginName,
    h.c.value('@count', 'INT') AS logins
FROM #tmp t
    CROSS APPLY t.XEhistogramData.nodes('HistogramTarget/Slot') AS h(c)
-- WHERE NOT EXISTS ( ... )

The histogram defaults to 256 buckets or distinct login names in this example.


How do I 'hint' the cardinality of a recursive CTE? I'm using the following recursive CTE as a minimal example, but in general, the optimizer has to use default 'guessed' cardinalities for recursive CTEs:

with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w;
/*
 n
---
 1
 2
 3
 4
 5
*/

explain analyze
with recursive w(n) as ( select 1 union all select n+1 from w where n<5 ) select * from w;
/*
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 CTE Scan on w  (cost=2.95..3.57 rows=31 width=4) (actual time=0.005..0.020 rows=5 loops=1)
   CTE w
     ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.003..0.017 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
           ->  WorkTable Scan on w w_1  (cost=0.00..0.23 rows=3 width=4) (actual time=0.002..0.002 rows=1 loops=5)
                 Filter: (n < 5)
                 Rows Removed by Filter: 0
*/

Note the rows=31 estimated and rows=5 actual cardinalities in the above plan. In some cases 100 seems to be used as an estimate, I'm not sure the exact logic behind the guesses.

In my real world problem, the poor cardinality estimate is preventing a fast 'nested loops' plan from being chosen. How can I 'hint' the optimizer cardinality for a recursive CTE to work around this?

I've worked round the issue like this, but I'm hoping there is a less kludgy way:

explain analyze
with recursive w(n) as ( select 1 union all select n+1 from w where n<5 )
select * from w limit (select count(*) from w);
/*
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.66..3.72 rows=3 width=4) (actual time=0.032..0.034 rows=5 loops=1)
   CTE w
     ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.003..0.019 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
           ->  WorkTable Scan on w w_1  (cost=0.00..0.23 rows=3 width=4) (actual time=0.002..0.002 rows=1 loops=5)
                 Filter: (n < 5)
                 Rows Removed by Filter: 0
   InitPlan 2 (returns $2)
     ->  Aggregate  (cost=0.70..0.71 rows=1 width=0) (actual time=0.029..0.030 rows=1 loops=1)
           ->  CTE Scan on w w_2  (cost=0.00..0.62 rows=31 width=0) (actual time=0.005..0.025 rows=5 loops=1)
   ->  CTE Scan on w  (cost=0.00..0.62 rows=31 width=4) (actual time=0.000..0.002 rows=5 loops=1)
*/


Insert / Edit Rows by Selecting FK row When inserting or editing rows ("Edit Top 200 Rows") for a table with a FK constraint, can the editor be configured to select the foreign row, rather than me having to look up the FK?

No. You're referring to SQL Server Management Studio, presumably, and that behaviour doesn't exist there. There are other applications you can use that will leverage FKs in that way though.


MySQL Like for Like - 100% accurate Backup/ Restore I've struggled to find the answer to get a perfect backup and restore for MySQL.
I need to do performance test on a database so I need to make sure that the page files, indexes, fragmentation - the whole things is like for like - 100% preserved.
I tried copying the raw files (.frm, .ibd, .trn, .myi etc) files across but get
Table '' doesn't exist in engine.
I also tried dBForge to copy the database which worked, but after looking at the fragmentation levels I can see differences, so I think this isn't a like for like (although I'm copying from production so perhaps I saw some anomaly there).
mysqldump - Does this do it?

It's not safe to copy data files unless mysqld is stopped.
I would suggest Percona XtraBackup for a physical backup of the database. This should be close to preserving the exact pages, including any fragmentation.
Another alternative is filesystem snapshots. You could revert to the snapshot after each of your tests.


Postgres 8.3 correct deletion of files in pgsql_tmp We are talking about the PostgreSQL 8.3 RDBMS here. So, no pg_terminate_backend() is possible with this version.

Sometimes we have to kill running processes at Operating System level (kill -9 PID) to solve issues related to max_connections value reached. In such cases, we targeted long running SELECT queries to kill.

As a result, we found out that our filesystem grows at 98% and fills up quickly, showing 1500+ files in the pgsql_tmp directory.

Some orphan files are the expected result of this kind of maneuver, since Temp files should be deleted during proc_exit processing and aggressively terminating running processes are not the best option.

So, to get rid of this "trash", what is our best option:

Conduct a postmaster restart and expect the RDBMS will run around and clean out all the temp directories by itself; or
stop the postmaster, manually delete the files in $PGDATA/pgsql_tmp/ and then, start postmaster again; or
without stopping the server, manually delete the files in $PGDATA/pgsql_tmp/ which are older than the current day.

Please justify your answer(s).

Rather than seeking better ways to suture up your foot, you should just stop shooting yourself in the foot.

All that pg_terminate_backend does is check for permission, then deliver a SIGTERM. You can do this directly by using kill -15 PID rather than kill -9 PID.  This will give that one process a chance to clean up after itself before exiting.  More importantly, it will not needlessly kill every other process and trigger a crash recovery.

If that process is so wedged that it won't respond to kill -15, then pg_terminate_backend wouldn't help anyway.  And in that case, it is probably a bug, and using software that is not 5 years past its EOL would likely help there.  If it is wedged like that, then rather than killing it with -9, you could probably just restart the server with pg_ctl restart -mi.  It would accomplish the same thing, but would clean up the files for itself.

If you are really only interested in better ways to suture up your foot, then your option 1 should work fine.  Option 2 seems pointless, since 1 works.

Your option 3 should also work as long as one day is long enough to avoid any still-running processes.  If you remove a file that some process still needed, the worst that happens is that that one process throws an error (which you must already be used to, as a user of kill -9). I would say it is not a "best practice", but since you are running long out-of-support software and routinely using kill -9, the best practice ship has already sailed.


Does logging a SLOW query impact postgres performance? Context...

Our Production system is reporting(occasionally) query timing that is taking longer than usual.

I have already measured EXPLAIN ANALYZE against the SQL statement but nothing seems to explain the longer than usual query timing.

cprdb=# explain ANALYZE select ror from crns where crn='8000440008' limit 1;
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..8.30 rows=1 width=6) (actual time=0.092..0.092 rows=1 loops=1)
   ->  Index Scan using crn_idx on crns  (cost=0.29..8.30 rows=1 width=6) (actual time=0.091..0.091 rows=1 loops=1)
         Index Cond: (crn = '8000440008'::bpchar)
 Planning time: 0.200 ms
 Execution time: 0.132 ms

My next bet is to log the PostgreSQL (DML) query time using log_min_duration_statement=0 and get a conclusive evidence as to where the problem is.

For that, I want to cross check ..

By Enabling query logging I would not deteriorate the overall PostgreSQL performance right?

How is the production system reporting the query times now?  If you are already getting that data, it is questionable that getting it a separate way will be helpful.

But to answer your question, there are two kinds of overhead you will get.

Setting log_min_duration_statement to any non-negative value will cause the system clock to be queried more often.  This should be almost negligible on modern common hardware and kernels, but can be quite slow on older things.

The other overhead is actually writing the log entries.  If the server is very busy, this is probably going to be quite noticeable with log_min_duration_statement=0.  You can probably come up with some threshold greater than 0 will that still log the slow statements, but without logging every single statement.


How to connect to remote MySQL server with sshuser and mysqluser being distinct I'm trying to connect to a remote MySQL server via ssh so that I have write-access to export some data. At the moment I can do the following:

Connect to the remote MySQL using mysql -u mysqluser -pPASSWORD -h HOSTIP DATABASENAME
Connect to the machine using ssh sshuser@HOSTIP

(Note that HOSTIP is the same for ssh and mysql server, but mysqluser and sshuser are different).

However, I cannot connect to remote MySQL via ssh. I tried two things:

After ssh-ing into the machine, I tried mysql in the terminal but it is not installed.
I tried ssh -L 1234:mysqlhost:3306 sshuser@sshhost. However, I can only enter my sshpassword, after which I get ssh-ed in but no connection to MySQL still.

First you need to do SSH user@serveripaddress then click enter you will be prompting to enter password. please enter the same.

After that use mysql -u root -p then click enter you need to enter mysql password. If mysql is not installed in your server. Please install then get root password from DBA and then try.


Mysql create trigger if not exist I have a script that creates triggers,
I would like to run it multiple times and if a trigger already exist it needs to skip the creation part.

Is there a "create if not exist" in mysql triggers?

I tried :

    IF NOT EXISTS ((SELECT TRIGGER_NAME
        FROM information_schema.triggers
        WHERE TRIGGER_SCHEMA = 'xxx_admin' AND TRIGGER_NAME = 'test_AFTER_UPDATE'))
        THEN
    CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE` AFTER UPDATE ON `test` FOR EACH ROW
        BEGIN
        INSERT INTO auditTest
        select *, now() from test where id = NEW.id;
        END;
  END IF;

I get : syntex error " 'IF' is not valid input at this location

There is no CREATE TRIGGER IF NOT EXISTS

There is DROP TRIGGER IF EXISTS

You must execute the trigger's creation as follows:

USE xxx_admin
DELIMITER $$
DROP TRIGGER IF EXISTS test_AFTER_UPDATE $$
CREATE DEFINER=`root`@`localhost` TRIGGER `xxx_admin`.`test_AFTER_UPDATE`
AFTER UPDATE ON `test` FOR EACH ROW
BEGIN
    INSERT INTO auditTest
    select *, now() from test where id = NEW.id;
END $$
DELIMITER ;

GIVE IT A TRY !!!

As for you error, using IF like that is not valid from the CLI.


Unable to run union in parallel on SQL Server I am unable to get this fairly simple query to parallelize the union operation:

select va.ObjectId, 0 as IsFlag
  from Oav.ValueArray va
 where va.PropertyId = @pPropertyId
   and va.value in (select value from #MatchValues)
 group by va.ObjectId
having count(distinct va.Value) = (select count(*) from #MatchValues)

union all

select odv.ObjectId, 1 as IsFlag
  from Pub.OtherTable codv
 where PropertyId = 2551
   and Id in (select value from #Ids)
   and Flag = @pFlag
   and Value in (select value from #MatchValues)
 group by codv.ObjectId
having count(distinct codv.Value) = (select count(*) from #MatchValues)

Running with MAXDOP 1 gives an expected .8s (.5 + .3).  I was hoping that increasing MAXDOP to 2 would optimize for the biggest gain by using one processor for each side but that is not the case.  Maxdop zero on a lightly loaded 12 Cpu machine all ~4% only results in parallel execution about 10% of the time.

Is there a way to weight the hints so that parallelization at the union point is the most important?  Does the syntax support separate MAXDOP's for each side?

I have tried (concat/hash/merge union) with little change.

Match values is usually a small table (~10 rows).

There is no separate MAXDOP for each side. But you could play with:

OPTION (QUERYTRACEON 8649)

This sets the cost threshold of parallelism to 0, meaning it will consider a parallel plan even if the costs are very low. You can also play with DBCC SETCPUWEIGHT, which Paul White describes here or other techniques he has for forcing parallel plans here. or even play with DBCC OPTIMIZER_WHATIF - which really should just be for playing.

There has been a suggestion on Connect to allow for a MINDOP syntax or something similar.

In any case, I'm not convinced that parallelism will necessarily help you in this case. Sure, you might get a parallel plan, but is it really going to reduce the runtime of the query? With all those GROUP BY and DISTINCT (why would you ever need both?) I think you should focus your optimization elsewhere (such as pre-aggregating some of this information perhaps). Or even something simple, like perhaps assign the COUNT(*) FROM #MatchValues to a variable instead of trying to evaluate it twice (not sure if SQL Server will do that in this case, but it can't hurt to remove the temptation).


Binlog - 'Race-like' Condition on Replication I have a typical master-slave MySQL setup. Slave is in test mode currently. Master is heavily used.

I am noticing something that looks like a potential race-like condition on the slave. On occasion, because of heavy use on the master, I will get 2 entries in the Master binlog with the same timestamp on a very heavily used table.

On the slave, the relay log is identical to the binlog on the Master so I know that part is working properly. However, when these statements are replayed I end up with a 1062 Duplicate error condition.

When I check the slave tables I get the first statement replayed properly but only part of the second. The binlog format is currently mixed.

I am considering testing the setup with row-based instead of mixed mode replication as I have read that it is suppose to help with such a condition - I have not used RBR myself before.

Has anyone else had similar experiences?
Do you have a resolution to this condition?
Since RBR can have an impact on performance, is that significant from your experience?

Here were my settings:

MySQL ver is 5.5.13
Sync_binlog=1
innodb_flush_log_at_trx_commit=1

Thanks

Replication is always serialized to prevent the race-conditions you are speaking about.  See this stackoverflow post for my explanation of InnoDB locking.  Some important notes:

RBR with row-based replication and read-committed isolation level sets fewer locks than InnoDB would have previously.
We should be moving to a world with RBR anyway, because this will open more doors for parallel replication threads (by working out dependencies between transactions).

To answer your last question, I use RBR in production, and I haven't seen any major performance hit.  We do generate a lot more binary log data however, since the events logged contain whole rows.


How do I write this query? I have a table that looks like this:

CREATE TABLE `message` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `user_id` int(10) unsigned NOT NULL,

  `grp_id` int(10) unsigned NOT NULL,

  `body` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`created_at`,`grp_id`,`user_id`,`id`),

  KEY `message_user_id_foreign` (`user_id`),

  KEY `message_grp_id_foreign` (`grp_id`),

  KEY `mssg_id_is` (`id`),

  CONSTRAINT `message_grp_id_foreign` FOREIGN KEY (`grp_id`) REFERENCES `groups` (`id`) ON UPDATE CASCADE,

  CONSTRAINT `message_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I want to fetch the latest 50 MESSAGES for every group that the user is in. This list will be supplied in the query . (something like 8,4,2,1,6,...)

What will the mysql query look like?
The number of groups a user is in is variable.

This is best done by LATERAL JOINs or if it is not available window functions. MySQL unfortunately doesn't support either but the latter is relatively easy to be mimicked.

> set @rownum=0;
> set @grp_lag=null;
> select id, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id
from message
order by grp_id, created_at desc;

+-------+--------+------------------+
| id    | rownum | @grp_lag:=grp_id |
+-------+--------+------------------+
| 50008 |      1 |                1 |
| 50007 |      2 |                1 |
| 50001 |      3 |                1 |
| 50004 |      4 |                1 |
| 50002 |      1 |                2 |
| 50005 |      2 |                2 |
| 50003 |      1 |                3 |
| 50006 |      1 |                6 |
+-------+--------+------------------+
8 rows in set (0.00 sec)

After having this you can have the final query something like this:

-- Set initial values for mimicing the window function
set @rownum=0;
set @grp_lag=null;

-- Query every message where rownum < per group limit
select * from (
    -- subquery providing rownumber for every group order by the created_at column
    select *, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id
    from message
    where grp_id in (1,2)
    -- Futher filtering for optimisation if possible
    order by grp_id, created_at desc
) numbered
where rownum < 50;

You can wrap this into a procedure to execute it easier. Please keep in mind, set calls also return and they need to be in the same session (yet another reason for a stored procedure).

> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)

The query above will fetch every row where grp_id matches the in condition and generate the row number for each per group. So in case of large number of messages further optimisations are necessary. For example you can heuristically filter by created_at > [estimated date for having at least 50 message].


SQL Server : Log File Shrink We are running SCCM 2012 r2 Reporting services and the SQL Server log file has got out of hand due to the backup not being set up properly and I'd like to confirm something before I continue.

I have set up our backup solution to do full backups of the DB and an incremental that is set to truncate the log on a regular schedule now but from what I've read, shrinking your log file is a bad idea due to performance issues, but it seems like this is the sort of case where it seems appropriate as we're sitting around 220GB for the log file.

Firstly, should I definitely be attempting to shrink the log file and what is the best way to do it? Is it simply by using DBCC SHRINKFILE?

Secondly, I believe I should be setting a reasonable limit on the log file size after shrinking but I'm unsure how to also come up with this limit. Is it just a size I'm happy with it taking up in-between truncation backups or is there a best practice.

As you may tell I'm not that familiar with SQL Server so I apologise if I've worded anything incorrectly. Thanks in advance.

In addition to your Full and Incremental/Differential backups you should be doing a backup on the transaction log in a reasonable frequency to mark all locations in the log file with committed entries as available for reuse instead of doing a truncation on the log file.  With a correctly sized log file the transaction log backups will help to keep your log file from growing continuously and might only grow if it a larger volume of changes come in between backups windows then you predicted for.

Now on to log file shrinking.  Shrinking your log file is not necessarily bad it will just take a bit of planning to do it correctly.  (Make sure you are doing this in a maintenance window with a full backup before you begin and if at all possible without any other users connected.) You can use the DBCC shrink command on it to get it down to 1MB but right after you should regrow the log file back to a size that is appropriate for the rate of change that is happening in your database plus a free space buffer to handle larger than normal amounts of data changes.  Also growing the log file in specific consistent increments will create evenly sized VLFs that can help with log performance.  For example after you shrink it down to 1MB you regrow the log file in 1GB increments until you hit 5GB in size.

Kimberly Tripp has a couple of good articles explaining VLFs and transaction log throughput that might be helpful to read over.  Do take note of her warning about a bug in growing the log file by 4GB.

Here is another VLF reference from Brent Ozar that has some more details and also references Kimberly's posts.

It's worth noting, Thanks @BradC, that shrinking and regrowing your log file should only have to be done once or only if your log file grows causing unevenly sized VLFs.  But if you set your auto growth size on the log file to the specific size increments that use used to originally grow it back from 1MB you shouldn't see uneven VLFs even after it auto grows.


How to select two fields from a table using join with another table I have a books database.

CREATE TABLE `books` (
  `book_id` int(11) NOT NULL,
  `book_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `book_subject` int(11) DEFAULT NULL COMMENT 'What genre the book is to be picked up from subject table',
  `book_subject2` int(11) DEFAULT NULL COMMENT 'What genre the book is to be picked up from subject table',
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `subject` (
  `subject_id` int(11) NOT NULL,
  `subject_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Let's say a book xyz falls under two subjects 'Mystery' and 'Thriller' with id values 1,2 in 'subject' table.

How do I write a select query that fetches the 'subject_name' value for both 'book_subject' and 'book_subject2'

It is messy and tedious.  You would need a UNION or OR, plus worry about NULL.

It would be better to have a separate table relating book_id and book_subject, and allow any number of rows in that table.  This has the benefit of allowing more than a fixed "2".

Counting

SELECT  book_subject,
        SUM(ct) as ct
    FROM  (
            ( SELECT  book_subject, COUNT(*) AS ct
                    FROM  books
                    GROUP BY  book_subject )
            UNION  ALL
            ( SELECT  book_subject2, COUNT(*)
                    FROM  books
                    GROUP BY  book_subject2 )
          ) AS s
    GROUP BY  book_subject;
    WHERE  book_subject IS NOT NULL

That gets the counts by id; then another JOIN is needed to get the names.

Or, if you avoid splaying an array (subjects) across columns...

I would not have a mapping table, but simply a tagging table (1:many instead of many:many)

create table subjects
(
  `book_id` int(11) NOT NULL,
  `subject_name` VARCHAR(99) NOT NULL
);

SELECT  subject_name,
        COUNT(*) as ct
    FROM  subjects
    GROUP BY  subject_name;


SQL Server tempdb rogue temporary tables I've got some rogue temporary tables being generated which is starting to cause disk space issues.
The database creating these tables is fairly large and I'm unsure what stored procedure/function etc is causing them. At the moment the tempdb is around 50 gig and only restarting the service clears it down (something we don't want to continually do).

Using SQL Server 2008 R2 SP1

The temp tables are all named in a similar fashion and are located in System Databases > tempdb > Temporary Tables:

dbo.#0519C6AF
dbo.#1273C1CD
dbo.#2A4B4B5E

There are about 12 of them at the moment – all created in the 9 days since the last service restart.
So far I’ve been looking for stored procedures that either create temp tables (although I don’t think this is the issue), use cursors, use transactions that could be left uncommitted.

Does anyone know what could cause the creation of temporary tables named similarly to the ones above?

Following what @Damien_The_Unbeliever commented, yes those are Table variables only and if you are still observing them then it's evident that from whichever procedure those got created is still opened (or) not completed yet else those would have already been cleared from TempDB. To do a test try the code snippet and check against  System Databases > tempdb > Temporary Tables: you will see that the table variable is exist.

declare @tab table(col1 int);
waitfor delay '8:10';
insert into @tab values(123);

Once you cancel the query, it gets cleared from TempDB.