Performance Tuning ETL I administer a datawarehouse running on SQL Server 2016. This datawarehouse has most of its ETL processing done during a nightly load, except for a few SSIS packages that run throughout the day, then SSAS processing done afterward, so when I arrive in the morning, there's no active monitoring I can perform.

The processing of this nightly load fluctuates, sometimes more than expected, but with the current monitoring setup, there is none other than a sp_whoisactive loaded into a table every 30 minutes, it makes it difficult to try and tune this process. I can obviously look to the plan cache but with the nightly ETL taking about 6 hours, the plan cache does not hold all of the plans used for the night.

Has anyone come across any performance monitoring/logging tools, the cheaper the better, that have been useful when trying to investigate and tune nightly loads like this? I'm most interested in looking for information to help tune indexes or see when/where I have CPU or memory bottlenecks.

For SSIS you can implement logging on the packages.  A good place to start looking would be at the ProcessInput and PrimeOutput steps of the components within the packages.  The PipelineComponentTime event will be useful to check for these steps, as it records the time spent in each of them.  You can read more about logging for SSIS here.

For SSAS you can use Extended Events to monitor the performance while the processing is being done.  The ProgressReportEnd and ResourceUsage events are two that you may want into look into examing during the times the cubes are processed, and well as the RequestProcessBegin and RequestProcessEnd events.  This link will further outline the use of Extended Events in SSAS.


Decrypting a Master Key works well using SQLCMD but not in the script I would like to use a script to automatize the process of migration of a Db in another instance. The DB is encrypted, so after restore it I need to decrypt it and encrypt again using the new Service Key.

Quite easy from SQLCMD line after restore (SQLCMD -S.\myinstance )

USE [MyDatabase];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

And everything goes flawlessy

If instead I put it in a script (I would like to store the password and the dbname in variables) and I run it I receive the error message

Cannot find the symmetric key 'master key', because it does not exist or you do
not have permission.

Of course I'm running the script within the same user (SQL -S.\myistance -i script.sql)

declare @dbMKey nvarchar(100);
DECLARE @exec_sql nvarchar(max);

    SET @exec_sql = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = N' + quotename(@dbMKey,'''') + '; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY;';
    print @exec_sql;
    EXEC sp_executesql @exec_sql

I add a print for debugging purpose to be sure that the sql command was exactly the same  I wrote by hand.

Anyone has some idea?

As @JohnEisbrener make me noticed in the comment, the issue is due to the fact that the script is missing of the
USE [mydatabase] statement.

So the right way to write the stored procedure variable is

SET @exec_sql = 'USE MYDATABASE; OPEN MASTER KEY DECRYPTION BY PASSWORD = N' + quotename(@dbMKey,'''') + '; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY;';


Error configuring mongodb replicaset with inmemory I'm trying to configure a MongoDB replicaset with 3 replicas. The primary and another use the in memory storage engine, and the third uses wired tiger. I am using this configuration.

When I restart the cluster the data does not persist.

Server has startup warnings:

** WARNING: This replica set node is running without journaling enabled but the
**          writeConcernMajorityJournalDefault option to the replica set config
**          is set to true. The writeConcernMajorityJournalDefault
**          option to the replica set config must be set to false
**          or w:majority write concerns will never complete.
**          In addition, this node's memory consumption may increase until all
**          available free RAM is exhausted.

** WARNING: This replica set node is using in-memory (ephemeral) storage with the
**          writeConcernMajorityJournalDefault option to the replica set config
**          set to true. The writeConcernMajorityJournalDefault option to the
**          replica set config must be set to false
**          or w:majority write concerns will never complete.
**          In addition, this node's memory consumption may increase until all
**          available free RAM is exhausted.

And my configuration.

rs0:PRIMARY> cfg = rs.conf()
{
        "_id" : "rs0",
        "version" : 102744,
        "protocolVersion" : NumberLong(1),
        "writeConcernMajorityJournalDefault" : false,
        "members" : [
                {
                        "_id" : 0,
                        "host" : "mongodb-mongodb-replicaset-0.mongodb-mongodb-replicaset.default.svc.cluster.local:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "mongodb-mongodb-replicaset-1.mongodb-mongodb-replicaset.default.svc.cluster.local:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "mongodb-mongodb-replicaset-2.mongodb-mongodb-replicaset.default.svc.cluster.local:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : true,
                        "priority" : 0,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : -1,
                "catchUpTakeoverDelayMillis" : 30000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("5e736baa5c51ff3e198dd880")
        }
}

The writeConcernMajorityJournalDefault option IS set to false!

Thanks in advance!

Ok what you need to do is to change:

    mongo "${ssl_args[@]}" --eval "rs.initiate({'_id': '$replica_set', 'members': [{'_id': 0, 'host': '$service_name'}]})"

to

    mongo "${ssl_args[@]}" --eval "rs.initiate({'_id': '$replica_set', 'writeConcernMajorityJournalDefault': false, 'members': [{'_id': 0, 'host': '$service_name'}]})"

The data still doesn't persist.


Change SQL Server 2005 from Standard Edition to Workgroup I have been tasked with performing an edition change from SQL Server 2005 Standard Edition to SQL Server 2005 Workgroup Edition, on Windows Server 2003 Standard Edition. Due to a previous failed attempt, I have a few questions:

I have backed up my databases using the Backup tool in tasks when right clicking on a database in the server. Is this the correct approach? Or is there something I am possibly missing?
I keep getting a warning when the installation wizard checks my system, that in order to do the edition change (I can't remember the feature of every edition of SQL Server 2005 so I'm not sure if we’re downgrading or upgrading!), I need to supply the SKUUPGRADE=1 parameter in command prompt. I type that statement (SKUUPGRADE=1) in sqlcmd. I'm assuming this is wrong. Please tell me how to fix this problem, if you know.
After that, I select the default instance then the next step in the wizard tells me there is nothing to upgrade. I can't remember the exact error, but does this mean I need to uninstall my current version of SQL Server first?

Standard to Workgroup is a downgrade

This is the BOL page on upgrades

Unfortunately, you'll have to uninstall and reinstall. On the bright side, the master, msdb etc will simply restore into the "lower" version as long the version number is the same.


Best MySQL storage engine for read heavy table with two indexes I have a pretty big MySQL table (70G) with about 15 columns and 1.5M rows.
There is one index on the autoincrement id and one on a foreign key (integer). Apart from that there are several VARCHAR and TEXT fields.

The use case is as follows: The data is inserted once and then read several times (SELECT count(*) FROM table WHERE foreign_key = 1). Every day rows that are older than 3 months are deleted.

I have thought of using ARCHIVE as storage engine but have read that it has many downsides and basically only one upside - saving disk space.

What would be the preferred storage engine for this use case?

Thanks!

The biggest downside for ARCHIVE in your environment would be that it won't work.

The ARCHIVE storage engine only supports INSERT and SELECT -- not UPDATE or DELETE -- and only supports one index, on the primary key.

I would to go with InnoDB, which can, incidentally, also store tables compressed on disk.  I can't think of a good reason to go any other way.


Which Plan is Better? WHERE EXISTS Instead of INNER JOIN Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure.

Logical reads plummeted, but plan cost and memory performance seem worse. Duration and CPU are slightly better with the WHERE EXISTS

Curious if this new plan (with the WHERE EXISTS) is superior to the one with the INNER JOIN. If so - why technically would a WHERE EXISTS would be a better option?

Old Plan
New Plan

Here are stats of two plans:

Old plan: You've got a 1,864 memory grant, estimated number of rows 400
New plan: You've got a 69,056 memory grant, estimated number of rows 43,017

So we have two options:

Either those are estimated execution plans and if so, you should run the queries and get the actual execution plan
Either those two queries don't return the same amount of rows and if so, they are not equivalent

In order to compare right, you should try checking statistics on your tables also, and run the queries with the option recompile to make sure it doesn't take an inappropriate plan in cache.

Old plan

New Plan

---------- EDITED TO EXPLAIN THE DIFFERENCE OF MEMORY GRANT --------------

Here is an important detail that I missed the first time I looked at your plans :

Old plan: The estimated number of rows is 400 but the actual number of rows is 29899. Since SQL only did a memory grant of 1864 for 400 rows, it probably spilled to TempDb.
New plan: The estimated number of rows is 43017 but the actual number of rows is 29904. So SQL did a memory grant of 69056 for 43017 rows, but only needed the memory for 29904 rows. So in this case, it took too much memory.

So in both case, the number of rows is almost the same. To know which one is best is a tricky question and will depend on your hardware and your server. If I/O is a problem and tempDB is under high utilization, then the second one could fix some problems for you. But if your server is under memory pressure, then the first one is better.
In order to better understand the two options, you should see this great blog by Brent Ozar about memory grant

Old plan filter

New plan filter


Replicate data of some tables of a local sql server to a database in Azure We have a SQL Server which runs on a machine in our office with a database and tables which get written to daily.

Due to some business requirements we need some of these tables replicated on to a database on Azure. There will be not too many rows, only some hundred / thousand rows per day.

I have several ways to do this:

Create a job in our application which gets the data from the local SQL Server and writes it to the Azure DB via Entity Framework
SQL Server Replication (could this be used?)
Stretch Database (unfortunately not possible because some of the tables can't be stretched apperently..)
Linked server in the Azure-DB with a view that selects our local DB? (is this possible?)

Did you have such requirements before and what did you use to solve this?

What different option would you suggest?

Thanks in advance :-)

Additional infos:

SQL Server v13.0.4435.0
Microsoft SQL Server Standard (64-bit) Edition

My answer is assuming "we need some of these tables replicated on to a database on Azure" you are referring to Azure SQL Database.

First of all you need to make sure that you requirement can tolerate the generic restrictions with Azure SQL DB.

Azure SQL Database resource limits

I did deploy these 2 methods with similar requirement you have.  Again you need to remember some of the on prem feature will not be supported.

Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

  General limitations

  A table cannot have an identity column that is not the primary key.
  A primary key cannot have the datetime data type.
  The names of objects (databases, tables, and columns) cannot contain the printable characters period (.), left square bracket ([),
  or right square bracket (]).

  Azure Active Directory authentication is not supported.

  Unsupported data types FileStream
  SQL/CLR UDT
  XMLSchemaCollection (XML supported)
  Cursor, Timestamp, Hierarchyid

Transactional Replication.

  You can also use transactional replication to migrate a subset of your
  source database. The publication that you replicate to Azure SQL
  Database can be limited to a subset of the tables in the database
  being replicated. For each table being replicated, you can limit the
  data to a subset of the rows and/or a subset of the columns.


Why my query works on VIEW and doesn't work on a similar table? I have a view openstreetmapview that combines two tables with different columns. The first table is openstreetmap and the second is B.

When I execute this query on the view:

select this_.gid             as y0_
     , this_.openstreetmapId as y1_
     , this_.name            as y2_
     , this_.streetType      as y3_
     , this_.oneWay          as y4_
     , this_.location        as y5_
     , this_.isIn            as y6_
     , this_.countryCode     as y7_
     , length                as y8_
     , distance_sphere(
           GeometryFromText(
               'POINT(11.059734344482422 46.07373046875)',4326)
             , ST_line_interpolate_point(
                       this_.shape
                      , st_line_locate_point(
                             this_.shape
                           , GeometryFromText('POINT(11.059734344482422 46.07373046875)', 4326)
                      )
                )
       ) as y9_
     , ST_AsText(ST_ClosestPoint(
               this_.shape,GeometryFromText( 'POINT(11.059734344482422 46.07373046875)', 4326)
       )) as y10_
from OpenStreetMapView this_ ;

Then, I get around 50k results. When I execute the same query on the table even though the table has the same column that the query needs, it returns 0 rows. Why is that?

I use PostgreSQL 8.4 database and PostGIS .

As you have absolutely no filtering clauses (no WHERE clauses or JOINs that would filter the rows from the base table) the only reason you should get zero rows from that query is that there are zero rows in the table for it to return.

SELECT COUNT(*) FROM OpenStreetMapView will confirm or contradict this.

If a process that should have populated the table has not yet completed other transactions may not be able to see the data yet (until that transaction is complete), though I believe in this case the default locking and transaction isolation settings would cause either an error to be raised or a sleep to be imposed for the checking query until the other process is complete (rather than returning nothing).


How to paste chinese characters in psql terminal? I am trying to paste chinese characters into psql terminal:

$ psql
=> \encoding
=> UTF8
# Ctrl+v (艺术与设计)
=> ?????设计

When pasting into normal terminal (Max OS) it works fine:

$ # Ctrl+v (艺术与设计)
$ 艺术与设计

What might be wrong?

psql delegates the handling of input to a shared library, which is either libreadline or libedit.

But libedit used to not work reliably with multibyte character sets, as has been reported on multiple occasions, here for OSX for example.

From your comment:

  /Library/PostgreSQL/9.4/bin/psql -> libedit@1.47.0 does not work, /Library/PostgreSQL/9.4/bin/psql -> libedit@1.48.0 does not work BUT /Applications/Postgres.app/Contents/Versions/9.4/bin/psql -> libedit@3.0.0 works!

...it seems that by relinking to a newer version, you were able to solve the problem. That's useful to know.

The reason why libedit is used at all while libreadline did a better job first is that the latter is licensed under the GPL, whereas psql is BSD-licensed like the rest of PostgreSQL. A consequence is that psql cannot be shipped in compiled form with the necessary parts that call libreadline, without breaching its license. On the other hand libedit uses the BSD license, so it's OK to ship with it.

The solution for psql packagers so far has been to ship PostgreSQL with libedit and let the users relink it with libreadline if necessary.

On Linux, the Debian/Ubuntu packages even do this dynamically without the user intervening.


Relate few records in table to eachother I need to relate groups of records, like I have 100 records in table, 3 of them should be known they are related, and other 5 are related to own group.
I don't need any group IDs or anything like that, I just need easy way to all records bound to given one.
Additionally, only very small part of records will need this, so I tried avoiding additional fields in table, but failed so far.
My best guess would be storing group id in the main table and somehow keeping track of highest group id used to not break anything (getting max value would work probably?)
It seems messy and somehow like too much overhead to me, I'd appreciate any suggestions

EDIT: my main table contains autoincremented unique id for each record, and rest of table might be considered blob, it is using innodb
for now I need to bind records created as batch, but I'm afraid it might be needed to bind already existing records (and I don't really want to mess too much with code maintaining the database, so I though of first creating all records, gathering their ids and then binding them)

EDIT2: I'm using ORM to manage DB records (Eloquent to be precise), so my question was purely about DB structure and I may even decide to use secondary table to store bulk ids and then create new bulk entity and then create new records with bulk id set for them

Ok, so as I forgot about one more important thing: I use soft deletes, I decided to:
- Create new table containing only autoincrementing unique id and nullable datetime containing date of soft delete
- Add nullable foreign key linking my main table to the new one with ondelete cascade to sort out hard deletes
- When batch adding create new records first create the bundle entity and link them to it on creation
- Make my bundle entity cascade soft deletes to all related records
- When (soft) deleting entity from main table call proper function on bundle entity (if exists) or main entity otherwise

This way everything works as I expected, but the extra table still hurts my eyes and I don't find it the solution, just something that works, but I wanted to avoid


single or multiple databases advice needed I know there are a few of these posts, but not in relation to my specific quandary.

I've always gone the 'per user' SQL Server DB rather than 'one DB for all' because:

a) I thought speed wise it's better for a lite user to only work within their small DB rather than all users being possibly affected by a few big users (surely searching a few hundred rows each time is better than searching through thousands of rows all the time).

b) If a server has too many DBs (very wishful thinking) then I can put the next lot of users on a 2nd, 3rd, 4th, etc server.

c) If a user no longer wishes to use my app or needs it restoring to a backup then I just bin/or restore their specific DB and don't affect anybody else.

My problem is that my next pet project involves pupils potentially competing against pupils in other schools and also showing a league table of schools so I just keep thinking a per school DB is going to be really messy compared to everybody in one DB.  Would you agree?

My fear of one DB is that later down the line there might be potentially 500 schools (again wishful thinking) with on average 300 pupils in each school which is a lot of pupil logins and results to manage in one DB - without it being lumpy.

Anybody had similar problems and overcome them?

Thanks

How much data are you dealing with? 500 schools with 300 students in each is not a lot of data for SQL Server to deal with if you design the tables well. As other users have mentioned in the comments, a few indexes on the right columns and you should be fine. SQL Server can happily run terabytes of data without working too hard.

The management of multiple databases is also much harder than it looks. A few examples:

Deploying schema changes to multiple databases when you upgrade your application
Backups for multiple databases - location, times, restore time, etc.
Running other maintenance tasks (checkdb) for multiple databases

The one point you mentioned that is harder is if restoring or deleting the data for a particular school is an important requirement. It is much harder to restore/delete data for 1 school if they are all in the same database, and that reason might be enough to mean you should split the data.

My general rule is if it's the same app then you should start designing it in 1 database until you have a reason to split it.


Stored procedure runs forever, but inner query runs fine If I run exec pr_BuscaLog -2, it will run forever (days, without end).

But, if I just copy the query and run it on its own, it runs in seconds.

Updating statistics did not help.

--pr_BuscaLog -1
CREATE procedure [dbo].[pr_BuscaLog] (@qtdDias int)
as
begin
declare @cmd as nvarchar(4000)
declare @banco as varchar(100)
declare @dataLimite as datetime

UPDATE: When I run EXEC PR_BUSCALOG, it start to run, and then stops.

It is difficult to tell just from examining the code if it is running forever because of an infinite loop or blocking - my guess is the latter. But here is a slightly different approach that avoids some of the things that could be problematic:

CREATE PROCEDURE dbo.pr_BuscaLog
  @qtdDias INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @cmd NVARCHAR(MAX) = N'', @dataLimite DATETIME = CONVERT(SMALLDATETIME,
      DATEADD(DAY, @qtdDias, GETDATE()));

  SELECT name INTO #tmp FROM sys.databases WHERE database_id > 4;

  -- delete the ones that don't have that view
  SELECT @cmd += N'DELETE #tmp WHERE name = N''' + name
      + ''' AND NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
      + '.sys.views WHERE name = N''vwLogProjetos'');' FROM #tmp

  EXEC sys.sp_executesql @cmd;
  SET @cmd = N'';

  SELECT @cmd += N'INSERT PainelControle..tblLogProjetos(...columns...)
    select ...columns... FROM ' + QUOTENAME(name)
      + N'.dbo.vwLogProjetos as T1 with (nolock)
          where DtTrabalho >= @dt and not exists
          (
            select 1 from PainelControle.dbo.tblLogProjetos T2
             Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS
             = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS
             and t2.codlog = t1.codlog
          );' FROM #tmp;

  BEGIN TRY
    EXEC sys.sp_executesql @cmd, N'@dt DATETIME', @dataLimite;
  END TRY
  BEGIN CATCH
    insert into dbo.tblLogProjetosErro (rotina, comando,Data)
      values ('pr_BuscaLog', @cmd + ERROR_MESSAGE(), GETDATE());
  END CATCH
END

Some things I've changed (or that you should):

No explicit loops. Contrary to popular belief, a while loop is not faster than a cursor (see this post), but neither is needed here.
Don't count when you don't have to - see this post.
I parameterized the date value instead of dealing with messy string concatenation (see this post and this post). This is harder to avoid with an entity, but should always be done with variables.
Please stop using lazy shorthand; e.g. use DATEDIFF(SECOND instead of DATEDIFF(s - see this post.
Use the schema prefix. Always.


SQL Server - Show Content of a partition I did somes partitions on my sql database with the wizard of microsoft Sql Server Management Studio. But i wanted to know if it's possible to do an request for show just lines contains by one partition.

I tried this :

select * from  MyTable where $PARTITION.LinkTest(ID) = 2;

But it's just show me the result of the partition function LinkTest not the lines contained on one filegroups or file. Someone know how I can do that?

This is a pretty basic query I use to get rows ( lines? ) per object on my partitioned schemes:

SELECT  DISTINCT ObjectName = so.name, ds.name,
        sp.partition_number, sp.rows
FROM    sys.objects so
INNER JOIN sys.partitions sp
    ON  so.object_id = sp.object_id
INNER JOIN sys.destination_data_spaces dds
    ON  sp.partition_number = dds.destination_id
INNER JOIN sys.data_spaces ds
    ON  dds.data_space_id = ds.data_space_id
WHERE   so.is_ms_shipped = 0
    AND sp.rows > 0
    AND ds.name <> 'PRIMARY'
ORDER BY so.name, ds.name;

You could add the name of your table and the partition you're interested in to the predicate:

    AND so.name = 'MyTable'
    AND sp.partition_number = 2

For a little more detailed information ( per index, compression stats, etc ), I use this little guy:

SELECT  DatabaseName = DB_NAME(), SchemaName = ss.name,
        TableName = OBJECT_NAME( so.object_id ),
        IndexID = p.index_id, IndexName = CASE
            WHEN p.index_id = 0
              THEN 'HEAP'
            ELSE i.name
        END, [Compression] = sp.data_compression_desc, PartitionNumber = p.partition_number,
        LowerBoundary = prv_left.value, UpperBoundary = prv_right.value,
        FileGroupName = CASE
            WHEN fg.name IS NULL
              THEN ds.name
            ELSE fg.name
        END, UsedPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.used_page_count * 0.0078125 ),
        DataPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.in_row_data_page_count * 0.0078125 ),
        ReservedPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.reserved_page_count * 0.0078125 ),
        [RowCount] = CASE
            WHEN p.index_id IN ( 0, 1 )
              THEN p.row_count
            ELSE 0
        END, [Type] = CASE
            WHEN p.index_id IN ( 0, 1 )
              THEN 'DATA'
            ELSE 'INDEX'
        END
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects so
    ON  p.object_id = so.object_id
INNER JOIN sys.partitions sp
    ON  so.object_id = sp.object_id
    AND p.partition_id = sp.partition_id
    AND p.partition_number = sp.partition_number
INNER JOIN sys.schemas ss
    ON  so.schema_id = ss.schema_id
INNER JOIN sys.indexes i
    ON  i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
    ON  ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
    ON  ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
    ON  dds.partition_scheme_id = ps.data_space_id
    AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
    ON  fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
    ON  prv_right.function_id = ps.function_id
    AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
    ON  prv_left.function_id = ps.function_id
    AND prv_left.boundary_id = p.partition_number - 1
WHERE   OBJECTPROPERTY( so.object_id, 'ISMSSHipped' ) = 0;


The user specified as a definer ('root'@'%') does not exist I have a trigger defined as follow:

USE `veeva_bi`;
DELIMITER $$
DROP TRIGGER IF EXISTS veeva_bi.account_ai$$
USE `veeva_bi`$$
CREATE DEFINER = CURRENT_USER TRIGGER `veeva_bi`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    // do trigger stuff here
END$$
DELIMITER ;

When I run that query I got the trigger created as root@localhost:

CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_bi`.`account_ai` AFTER INSERT ON `account` FOR EACH ROW
BEGIN
    // do trigger stuff here
END

But any time I try to run a PHP script, that connects to MariaDB as root user I got this error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1449 The user specified as a definer ('root'@'%') does not exist' in /var/www/html/veeva_replicator/DB.php:158

Why is that? I have others triggers created on the same way and them did works but this one doesn't, any advice or help regarding this?

I met this problem when update a row in mysql.

I connect mysql use a diffrent user and password rather than root.

When I type "show triggers" in mysql, I found the triger use "root@%", this will cause this exception.


User created new login - how to find where they got permissions from One of our developers just mentioned how he created a login on a server. I didn't think he should have permissions to do that, so I went and checked.

I've looked at which logins have sysadmin, and he's not a member of any of those groups. There are no logins in the security admin role.
There also don't seem to be any ALTER ANY LOGIN granted in the sys.server_permissions table.

Is there anything else I can look for here?

As first thing I'd check server level permissions of that login:

exec as login = 'TheLogin';
select * from sys.fn_my_permissions(null, 'server');

As second thing I'd check login's groups/roles, like this:

exec as login = 'TheLogin';
select *
from sys.login_token
where principal_id > 0;

And my future actions depend on what I've got for now.
The thing may be tricky: One can have impersonate on sa or be a db_owner of trustworthy's database, so he has "hidden" permissions


Design: Multiple Values in One Column or Many Columns? I am designing a database based on NBA stats.

For a particular part, I have:

Game: [ Id, HomeTeam, AwayTeam, Date, GameTime … ]

GameScore: [ Id, GameId(FK), QuarterOne, QuarterTwo … ]

For game score, there is quarter one to quarter four. How should I design it it to include overtime quarters. What is best design method for this situation?

Should I put the values into one column?

Or should I have a separate table as follows:

Game: [ Id, HomeTeam, AwayTeam, Date, GameTime … ]

GameScore: [ Id, GameId(FK), QuarterOne, QuarterTwo … ]

OvertimeScore: [ GameScoreId(FK), Quarter, score ]

The method above, will include the overtime score in the table "OvertimeScore", where quarter column stores the quarter value, and score column stores the score value.

It doesn't seem right design for some reason. Please help thanks.

If you created an OverTimeScore table, you'd then need to add an INT or BIT field to GameScore or Game table to check if overtime exists or not. Are you also going to store the total score? Or just each quarter score and calculate the total in your app/query? If this were me, I would add a total score field and an overtime field into my GameScore table, I don't see a reason to break it out into its own table. Populate the field if there is overtime, otherwise leave it NULL. You can then just work your queries to check is null. 101 ways...


Is my DB design alright or not? Quick dscription of a problem

first of all I am still learning so please be understanding. Recently I've come across a post where someone was proposing nice schema for storing recurring events. I found it interesting and decided to expand that for learning purposes. But I endded up with a "circular" schema that really freaks me out. Let's look at that through the prism of appointment system (let's say to a doctor).

Presentation of my approach

Company - a private clinic that employs doctors and let patients make appointemnts.

Frame - a range in time when an event occurs. F.e. an appointemnt in a company calendar might be an event, that is represented by a row in the frame table. It's possible to create a repetitive frame that happens f.e. every monday.

Doctor - obviously a person that can be assigned to a given frame (appointment) and company.

A company can have a calendar. A calendar can have "appointments"/"events". Each event occurs within a given time frame. Each such an "event" is held as a "range in time" in a frame table (along with meta info). Thus one
company can have many "appointments"/"events" or many "frame"s.
A frame can be in many companies (many calendars). Because a frame,
is basically just a start_date and an end_date. F.e. frame
2018-01-01 08:00:00 - 2018-01-01 09:00:00 can be defined in
calendar of a "companyA" and a "companyB".
Doctor can be a member (an employee) of many comapnies (clinics). that is obvious. A company (clinic) can have many doctors, that's obvious too.
But an employee of a clinic, can assign a doctor to a frame (make an appointemnt on behalf of a patient). Let's assume that, one frame can have assigned many doctors (f.e. difficult DDX, like in a tv show "Dr. House"). Thus one evetn/appointement/frame can have many doctors.
A dcotor can be in many frames, be assigned to many appointments. That's obvious.

My questions

Is my schema acceptable ?
Is "circular square of tables" acceptable in that case ?

Update:

When company A and company B both are assigned to a frame (2018-03-26 09:00 - 2018-03-26 12:00), that means that company A has an event in the frame, and company B has a completely distinct event in that same frame.

Company A
An appointment between Ms.Alice and Dr.Frank at 5 o'clock (frame: 2018-01-01 05:00:00 PM)

Company B
An appointment between Mr.Bob and Dr.Smith at 5 o'clock (frame: 2018-01-01 05:00:00 PM)

Two different companies, two different calendars, two different events, one (time) frame.

Corrected design (according to review in an answer):

I see two potential issues:

Clarity on frames

I understand that a frame represents one (or more) intervals, as established by a company.

I believe you originally intended a frame to be a  block of time, and to allow multiple companies to use it. The potential problem with that is - what if one company needs to alter that frame? You basically have to create a second frame, and migrate over only the references for the one company to the old frame. This is possible, but would be a maintenance hassle (and would slow things down while you did it).

I also note that a frame can include multiple time intervals, and repeating time frames (allowing, I assume, you to set up weekly standing "appointments" where appropriate). If a frame represents a specific event (even if that "event" is a recurring weekly appointment, or a five-day conference), this shouldn't be a problem; If multiple events are re-using the same frame, then the maintenance issue mentioned above would grow even worse.

If a frame belongs to just one company, you don't really need the company_frame table; you can have the company_id in the frame table directly.

NOTE: One specific clinic may have many rooms, and many doctors, each of whom may have different appointments at the same time. It may be worthwhile to add another distinguishing factor to a frame - perhaps a location ("Room 2" in a doctor's office; "Conference Suite 100"; whatever's appropriate) to help differentiate. In some cases, it may be sufficient to use the doctor and the time interval (in a doctor's office, "Dr. Cameron, 15:00 - 15:30, 2018-03-23" should be distinctive); in other cases, you might need something more.

Data consistency

The next thing that comes to mind is the potential for data consistency issues. This is where the "circular" design comes into play.

In theory, with this design, nothing guarantees that a doctor associated with a frame is connected to the same company as the one that "owns" the frame. However, logically, all doctors associated with a given frame should belong to the same company as the frame.

Based on this, I think that it makes more sense that frame_doctor actually be frame_co_doctor, and that it be used to maintain a many-to-many relationship between company_doctor and frame instead of between doctor and frame (doctor would only be connected to anything else via company_doctor then).

Presumably, if a given doctor stopped working for company X, he would no longer be tied to the frames belonging to that company; he might still be connected to frames owned by other companies, though. And, company_doctor and frame would both have the company_id, making it easy to verify that the correct records are being linked - if company_doctor.company_id <> frame.company_id, then you shouldn't create the frame_co_doctor row.

Those are the biggest potential issues I see with the design as it stand now, and how I'd resolve them, based on what I know of your requirements. I may, of course, have misunderstood a requirement, or there may be some you haven't noted as yet, that would invalidate these approaches, but they're the way I'd look at it.


Load different date formats in mysql I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD.

Is there a way by which I can load DD-MON-YY data into YYYY-MM-DD format using MySQL LOAD or any other native utility?

It's not difficult. Just need a set section in your LOAD DATA INFILE command.

mysql> create table tst ( datecol datetime );
Query OK, 0 rows affected (0.04 sec)

mysql>

File:

[root@node1 ~]# cat /tmp/tst.dat
01-JAN-03
21-MAR-09
28-FEB-11
[root@node1 ~]#

Test:

mysql> load data infile '/tmp/tst.dat'
    -> into table tst
    -> fields terminated by '|'
    -> lines terminated by '\n'
    -> (@datecol)
    -> set
    -> datecol = str_to_date(@datecol, '%d-%M-%y');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tst;
+---------------------+
| datecol             |
+---------------------+
| 2003-01-01 00:00:00 |
| 2009-03-21 00:00:00 |
| 2011-02-28 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

mysql>


In Oracle AWR report how to use "Latch statistics" section in analysis Latch statistics in AWR report contain following sections

1.Latch Activity
2.Latch Sleep Breakdown
3.Latch Miss Sources
4.Parent Latch Statistics
5.Child Latch Statistics

How can I use these sections of AWR report for useful insights to improve Oracle performance?

Usually, latches are used to optimize SQL. Latches are what provide concurrent access to Oracle's internals. You always want a high hit ratio, although occasionally a high hit ratio isn't indicative of everything running well. Always see if latch "latch free" is in your top wait events: if it is, you might want to look closely at your latches.

Actually using these statistics can be difficult if your new to SQL tuning. A common latch in transaction heavy databases (the ones that I work in are) is the "In memory undo latch" which allows you to rollback. If you ever noticed a high miss rate on this type of latch, you would want to examine not only the total number of SQL events that are rolling back during your AWR window, but you might examine any culprits that are might be issuing rollbacks when there is actually nothing to roll back at all (relatively common in nested loops and recursive function calls).

Thats just one example, and to be honest if you have the time to be researching your latch performance then your DB is running pretty smoothly (unless its on your top 5 wait events). Use them to examine your SQL and you should start to see some benefit in your CPU wait times.

Here are links for more information from people who know far more about this than I do:

http://www.dba-oracle.com/oracle10g_tuning/t_latch_statistics.htm

http://www.dba-oracle.com/m_latch_hit_ratio.htm


How can we foresee (and configure) B-Tree and R-Tree indexes width and depth in PostgreSQL? The B-Tree and the R-Tree are two similar index structures that PostgreSQL offers in its implementation.

Both structures can have at most m children per node and as other tree structures, children can have other children. However, in my test data, my structures are always in the form of a root node with many children i.e. just 2 levels.

How can I understand better at which point will the structure's depth increase? What determines how many elements can be on the same level of a tree in terms of PostgreSQL? Is it possible to configure these or the m variable?

Both structures can have at most m children per node and as other tree structures, children can have other children.

The million dollar question there is what you mean when you say m children. Math and algorithms in the abstract are concerned with purity: the name for that structure is a Binary Search Trees. Databases are concerned with concurrency and performance, they don't use Binary Search Trees (usually). PostgreSQL implementation of a B-tree isn't based on "children" but on index tuple size and fill factors. Simply, if you can fit more items on the page, you do that: it's called the Index Blocking Factor. The structure is called a B-Tree. Every level of the tree requires a block seek, so you want to do the most you can do to minimize the amount of blocks you must retrieve to complete the index operations. Further complicating matters, the size of the index tuple is variable, from the docs,

  Lehman and Yao assume fixed-size keys, but we must deal with
  variable-size keys. Therefore there is not a fixed maximum number of
  keys per page; we just stuff in as many as will fit.  When we split a
  page, we try to equalize the number of bytes, not items, assigned to
  each of the resulting pages.  Note we must include the incoming item in
  this calculation, otherwise it is possible to find that the incoming
  item doesn't fit on the split page where it needs to go!

tldr; you have little control of the depth (outside of fillfactor). The rows being indexed can result in a variable page size entry, and the depth is a factor of your block size and the index entry size.


Is there a way to see the access log of a table? I am using MS SQL Server 2008. I have a table in a database, in which records are being inserted and deleted.

E.g.

Initially the table is empty. A Java program (probably) inserts few records in it. Another Java program quickly access the table and deletes the records (All the records which were inserted). And then I manually run a select query on the table. So basically I can see only the "initial" and final state of the table, both of in which the table is empty.

I need to know that is there a way by which I can see those records, or at least check if something is being inserted/deleted?

Any help is appreciated.

You need to be granted the necessary permissions on the table that contains the audits.  The minimum for you to achieve what you want is you need to be granted SELECT on that table.

As far as check if something is being inserted/deleted, you could always create a trace with SQL Profiler.  At the same token though, you need ALTER TRACE permissions on the server, and if you don't have the former SELECT permission I doubt you have ALTER TRACE.


How to trust trust a document in access via PowerShell so I can trigger my database export marcos Long story short I'm working on using PowerShell to automate a nightly DB conversion from access to PostgreSQL.

In it's more basic form the script does the following.

Copies the .accdb file to a local folder
Triggers a macro in the .accdb that exports the data to Postgres

The code looks something like this. File locations and names changed. Some database cleanup macros omitted since the extra steps aren't really relevant and are subject to the same problem.

$CopyLocation = "C:\Copy.accdb"
$SourceLocation = "C:\Source.accdb"
Add-Type -AssemblyName Microsoft.Office.Interop.Access
$MsAccess = New-Object -ComObject Access.Application
$MsAccess.OpenCurrentDatabase($CopyLocation)
$MsAccess.Run("ExportToPostgreSQL")
$MsAccess.Quit()

So the issue I'm having is between the copy and the macro execution. Since the document isn't "Trusted" the execution of the macro fails. If I run the script up to the copy, then manually open access and click the enable content button. I can then trigger the macro from powershell.

Is there a way I can trust the document and enable to content from within powershell?

Microsoft Office documents, including Microsoft Access .accdb databases, need to reside in a "trusted" location in order for macros and VBA functions to run without requiring intervention from the user.

Add the desired target location to the list of trusted locations by making a change to the system registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Access\Security\Trusted Locations

See this StackOverflow post for further details.


How can I use LOAD DATA LOCAL INFILE for specific columns? I have a directory of text files, as the filename is the id, and the content text for this table:
CREATE TABLE texts
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

I use PHP glob function to find files to INSERT file by file (row by row) as
INSERT INTO texts (ID,Added,Text) VALUES ($id,CURDATE(),'$text')";

$id is the filename, and $text the entire content of the file.
How can I use LOAD DATA LOCAL INFILE to directly INSERT into the database and to avoid reading the large text into PHP variable?

Mysql can do following with user defined variables
 LOAD DATA LOCAL INFILE '/your_path/yourfule.csv'
 INTO TABLE texts
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n'
 ( @ID
 , @Text
 )
 SET `ID`   = @ID
   , `Added` = CURDATE()
   , `Text`     = @Text
 ;

Please check, if MySQL allows from that path to insert data.
And also you may have to change the options for INFILE


CREATE DATABASE permission denied for sysadmin I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases.  See Restricting Database Access/Functions to User Groups? for more info

When I attempt to execute the stored proc, SQL Server generates a security error:

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.

This is the code:

CREATE LOGIN DatabaseCreator WITH PASSWORD='Pa$$w0rd'; /* REPLACE WITH A SECURE PASSWORD! */
GO
CREATE USER DatabaseCreator FOR LOGIN DatabaseCreator;
GO
EXEC sys.sp_addsrvrolemember 'DatabaseCreator','sysadmin';
GO
CREATE PROCEDURE dbo.CreateDatabase
(
    @DatabaseName SYSNAME
)
WITH EXECUTE AS 'DatabaseCreator'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(max);
    IF COALESCE(@DatabaseName,'') <> ''
    BEGIN
        SET @cmd = 'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + ';'
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = 'INVALID DATABASE NAME';
        RAISERROR (@cmd, 0, 1);
    END
END;
GO
EXEC master.dbo.CreateDatabase 'MyDatabaseName';

msg 262 appears to be a generic Permission Denied message.  Since DatabaseCreator has sysadmin privileges, this should not be happening.  Unless I'm missing something.

The EXECUTE AS clause of CREATE PROCEDURE can only be used to impersonate a user (not a login) and the scope of impersonation is restricted to the current database. The sysadmin permission is associated with the login, not the user, so you receive a permissions error.

The correct way to grant CREATE DATABASE here is to sign the procedure. The process is a little involved because we want to sign the procedure to grant a server-level permission, but each step of the process is reasonably simple:

First, create a certificate and login in master with the CREATE ANY DATABASE permission. The certificate will later be copied to the target database to allow procedure signing.

USE master;
GO
CREATE CERTIFICATE CreateDatabaseCert
   ENCRYPTION BY PASSWORD = 'password'
   WITH SUBJECT = 'Create Database',
   START_DATE = '20140101',
   EXPIRY_DATE = '20141231';
GO
CREATE LOGIN CreateDatabaseLogin
FROM CERTIFICATE CreateDatabaseCert;
GO
DENY CONNECT SQL TO CreateDatabaseLogin;
GO
GRANT CREATE ANY DATABASE
TO CreateDatabaseLogin;

We now need to copy the certificate to the database where the procedure will be run. There are a couple of ways to do this, but the most compatible is to read and write the certificate via a file. This next step writes the certificate:

BACKUP CERTIFICATE CreateDatabaseCert
TO FILE = 'C:\Temp\CreateDatabase.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\Temp\CreateDatabase.pvk',
    ENCRYPTION BY PASSWORD = 'password',
    DECRYPTION BY PASSWORD = 'password'
);

Now we switch to the target database, restore the certificate, and delete the temporary files:

USE Sandpit;
GO
CREATE CERTIFICATE CreateDatabaseCert
FROM FILE = 'C:\Temp\CreateDatabase.cer'
WITH PRIVATE KEY
(
    FILE = 'C:\Temp\CreateDatabase.pvk',
    ENCRYPTION BY PASSWORD = 'password',
    DECRYPTION BY PASSWORD = 'password'
);
GO
--EXECUTE sys.sp_configure 'show advanced options', 1;
--RECONFIGURE;
--EXECUTE sys.sp_configure 'xp_cmdshell', 1;
--RECONFIGURE;
EXECUTE sys.xp_cmdshell 'ERASE C:\Temp\CreateDatabase.*';

Now create the procedure, and sign it using the certificate:

CREATE PROCEDURE dbo.CreateDatabase
(
    @DatabaseName SYSNAME
)
WITH EXECUTE AS CALLER
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(max);
    IF COALESCE(@DatabaseName,'') <> ''
    BEGIN
        SET @cmd = 'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + ';'
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = 'INVALID DATABASE NAME';
        RAISERROR (@cmd, 0, 1);
    END
END;
GO
ADD SIGNATURE TO dbo.CreateDatabase
BY CERTIFICATE CreateDatabaseCert
WITH PASSWORD = 'password';

To test this works, we create a new login and user with permissions to execute the procedure only:

CREATE LOGIN test WITH PASSWORD = 'password';
CREATE USER test FROM LOGIN test;
GRANT EXECUTE ON dbo.CreateDatabase TO test;

The test itself:

EXECUTE AS LOGIN = 'test';
EXECUTE dbo.CreateDatabase @DatabaseName = 'NewDB';
REVERT;

The database is created successfully. To clean up, run the following:

DROP DATABASE NewDB;
DROP USER test;
DROP LOGIN test;
DROP PROCEDURE dbo.CreateDatabase;
DROP CERTIFICATE CreateDatabaseCert;
GO
USE master;
DROP LOGIN CreateDatabaseLogin;
DROP CERTIFICATE CreateDatabaseCert;

For more information on permissions, see Erland Sommarskog's excellent article.


Any disadvantages to enabling federated storage engine? We've encountered a couple use cases where having federated tables prove useful, despite their limitations and caveats.

As best I can tell this must be enabled with a federated declaration in the .cnf upon startup.

Our use cases are limited and certainly don't warrant restarting every instance just to have it.  At the same time I'm thinking of adding it as a default to our standard config so it is available on instances as they get bounced during more pressing maintenance.

Is there any reason not to have this option available unless decided absolutely necessary?

For a final clarification: This question is not about the disadvantages of actually using a federated engine; rather it is just about just enabling as an available option. Perhaps to put it another way: Is there good reason it is disabled by default other than making sure you don't shoot yourself in the foot if you understand the consequences?

IMHO it is really about security. If a table uses the federated storage engine, all one needs to do is this :
SHOW CREATE TABLE fedtblname\G

and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text.
It is too bad that the connection information is not encrypted.
The bottom line is that you can use federated option in my.cnf totally at your own risk.
UPDATE 2012-01-18 11:31 EDT
It was pointed out that you can create a server definition and then define that server definition in the CONNECTION option of the federated table.
I tested this out and it works great.
However, there is still a security flaw. If you can SELECT from the table mysql.servers, you can see all definitions. Here is the table's layout:
mysql> show create table mysql.servers\G
*************************** 1. row ***************************
       Table: servers
Create Table: CREATE TABLE `servers` (
  `Server_name` char(64) NOT NULL DEFAULT '',
  `Host` char(64) NOT NULL DEFAULT '',
  `Db` char(64) NOT NULL DEFAULT '',
  `Username` char(64) NOT NULL DEFAULT '',
  `Password` char(64) NOT NULL DEFAULT '',
  `Port` int(4) NOT NULL DEFAULT '0',
  `Socket` char(64) NOT NULL DEFAULT '',
  `Wrapper` char(64) NOT NULL DEFAULT '',
  `Owner` char(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'
1 row in set (0.00 sec)

mysql>

In order to see the server definitions, run this query:
SELECT * FROM mysql.servers\G

This will expose all remote servers known by the MySQL Instance, including the password in plain text.
To see all the federated tables, run this:
select table_schema,table_name from information_schema.tables where engine='federated';

This confirms my original assertion: all one needs to do is this :
SHOW CREATE TABLE fedtblname\G

and you can see the connection information (server IP, database, table name, username, password) of the real MyISAM table on the remote DB server, and all in plain text. As given by my update, that plain text info is located in mysql.servers.


Why does adding LIMIT to my query make it crawl? Simple query:

select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by scores.user_id having sum(score)>=1000 order by sum(score) desc limit 50

So trying to get a list of accumulated scores for Jan 2012, order them by scores descending and paginate them.

Without limit: slowish but OK: searches 69348 rows. (Be nice to work out how to avoid the temporary table but I can't). Explain says:

1, 'SIMPLE', 'scores', 'range', 'user,date,user+date', 'date', '8', '', 69348, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'users', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'scores.user_id', 1, 'Using where'

With limit: it's the same but the rows search are now 1806794 and it takes forever.

It's a partitioned InnoDB if that makes any difference, with all data being on one partition.

Use correct ANSI group by (not the MySQL abomination extension) and see what happens
select sum(score) total,name,gender,dob,country
from users join scores on users.id = scores.user_id
where date between '2012-01-01' and '2012-01-31 23:59:59'
group by name,gender,dob,country
having sum(score)>=1000
order by sum(score) desc limit 50

Why?
GROUP BY in MySQL implies ORDER BY which causes the filesort here
Changing the query to be more standard may help the optimiser
More on the MySQL abominations extensions:

https://stackoverflow.com/a/6642253/27535
https://stackoverflow.com/a/10894876/27535
https://stackoverflow.com/a/6457144/27535

And from the MySQL Docs on ORDER BY Optimization

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
...
You have different ORDER BY and GROUP BY expressions.


Opening Symmetric Keys I am trying to open a symmetric key I created in a database, but it doesn't seem to be working. I am executing the query as dbo and it even says that it is completed successfully. However, the value for the key being open is still set to false and I cannot move forward. I am not sure how to proceed.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go

CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Subject';
go

CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Certificate1;
go

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
go

I can create the master key, certificate, and symmetric key just fine. That OPEN statement just doesn't work. It doesn't return any errors like I said, it just completes successfully without actually doing anything. This is for a class and I am totally lost. I feel like I am doing it correctly and am not sure what to change. I have tried recreating everything and even deleted the database itself.

Any advice would be welcome.

Each Go statement lets SQL Server know that the preceding commands are meant to be a single batch. This works for the create statements, but I suspect that if you try to use the symmetric key in a command after the batch, it will not be open. Remove the Go statements, then try to use the symmetric key after the open statement.


how to avoid duplicated column in two different tables? OK, I'm inexperienced in terms of database design, so hopefully my question isn't too silly.

I have 2 tables users and user_profiles these two tables has one-to-one relation between them. so(obviously) each row in users table will be linked to a row in user_profiles table.

And, when I'm displaying a user's profile in the web app I'm creating I have to do two SELECT statements(or JOIN) to get the required data, which is -somehow- overkill for me, and I'm looking for an alternative to it, I'm trying to get the required data with one SELECET statment from one table.

users schema:

CREATE TABLE IF NOT EXISTS `users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(50) COLLATE utf8_bin NOT NULL,
   `password` varchar(255) COLLATE utf8_bin NOT NULL,
   `email` varchar(100) COLLATE utf8_bin NOT NULL,
   PRIMARY KEY (`id`)
)

user_profiles schema:

CREATE TABLE IF NOT EXISTS `user_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `nickname` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
)

So for example, if I want to grab the nickname, user and email I'll definitely use JOIN or double SELECT. Which will lead me to use two tables for such a simple query.

So, I have currently three solutions in mind:

Use JOIN or SELECT and hopefully my website won't be too slow
because of this.    Will this have huge impact on the performance of
my web app anyway?
Remove user_profiles table and keep all user's personal data in
one table.
Duplicate columns in two tables, so for example the email column will be present in users and user_profiles. Which will result in double INSERTS at the time of user registration.

Looking forward to hear your opinions.

I think that you incorrectly identified relationship between users and user_profiles;  I usually see it's either 1:0..1 (user can -but doesn't have to- have at most one profile) or 1:0..n(user can have many profiles). Surely, if it's really 1:1 in your system, it makes sense to get rid of user_profiles and add nickname to users.

There is nothing wrong with having join in the query, as dezso mentioned in comments, it's RDMS main purpose and responsibility to perform such operations in fast and reliable way.
Also, in my understanding there are just very few places in the system where you need to retrieve information from user_profiles; normally you just display basic information from users.

Finally, if it's 1:0..1 relationship, there is absolutely no need in user_profiles.id column - user_profiles.user_id should be primary key and foreign key to users.


Can SQL server deny connections until the AG failover has completed ? We have been doing rigorous testing of the Always On environment by doing manual failovers multiple times at the same time doing transaction in the midst of failover and we observed the following and would like to know your opinion on this.

The application that uses the AG databases does inserts/updates frequently (like in seconds interval). During failover… the application is connected to the failed over AG Group but throwing an error saying the database is in read only state and not able to write to the database and it couldn’t do any inserts and updates until the failover completes. The Nodes are set up for synchronous commit mode (automatic failover).

When we are doing manual failover it takes about 10 seconds to fully failover. The issue we have is on this 10 second interval.

I know we can add error handling codes from the application side to handle the error or make the application wait until the failover fully takes place. The problem is to change the application code is not going to be an easy process for us since it is a third party application.

Is there any way we can set/tell SQL server to not allow connections to the server until the failover has completed; hence we do not see the “database is in read only state” error. In other words, can SQL server deny connections until the failover has completed?

There is not a built in option to do this directly from AlwaysOn.  You would have to go with something a bit custom.  Depending on your setup, you could do something as simple as a job or a trigger scanning for changes in alwayson state.  Which then disables/enables the login for the application during a failover. But then you may see other errors in your log for the failed login attempt.


Is there a good "rule of thumb" for translating EXPLAIN cost to (wall-clock) runtime? From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have trouble trying to translate this to "ask me again in 15 minutes". Does anyone know a good "rule of thumb" to use for any particular database?

I realize this isn't going to be a hard and fast rule, but even being able to give a ballpark figure could be useful in some instances.

I suspect that this is not possible the way you imagine it.  One important reason is that the actual run time is very hardware dependent, and many of the optimization decisions that the database engine does are effectively about balancing the use of the different hardware components (e.g., disk, memory, CPU).

I suggest that you run a bunch of queries relevant to your application, record the cost estimations and the run times, and try to make sense of that data.  You might get a nice linear relationship, or you might learn that the cost numbers are worthless for this purpose.


Sort spills to tempdb due to varchar(max) On a server with 32GB we are running SQL Server 2014 SP2 with a max memory of 25GB we have two tables, here you find a simplified structure of both tables:

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceId] [int] NULL,
    [typeID] [int] NULL,
    [remark] [varchar](max) NULL,
    CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Resources](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [resourceUID] [int] NULL,
 CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

with following non-clustered indexes:

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[Resources]
(
    [resourceUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Settings]
(
    [resourceId] ASC,
    [typeID] ASC
)

The database is configured with compatibility level 120.

When I run this query there are spills to tempdb.
This is how I execute the query:

exec sp_executesql N'
select r.id,remark
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

If don't select the [remark] field no spills occurs. My first reaction was that the spills occurred due to the low number of estimated rows on the nested-loop operator.

So I add 5 datetime and 5 integer columns to the settings table and add them to my select statement. When I execute the query no spills are happening.

Why are the spills only happening when [remark] is selected? It has probably something to do with the fact that this is a varchar(max).
What can I do to avoid spilling to tempdb?

Adding OPTION (RECOMPILE) to the query makes no difference.

There are going to be several possible workarounds here.

You can manually adjust the memory grant, though I probably wouldn't go that route.

You can also use a CTE and TOP to push the sort lower, before grabbing the max length column. It will look something like below.

WITH CTE AS (
SELECT TOP 1000000000 r.ID, s.ID AS ID2, s.typeID
FROM Resources r
inner join Settings s on resourceid=r.id
where resourceUID=@UID
ORDER BY s.typeID
)
SELECT c.ID, ca.remark
FROM CTE c
CROSS APPLY (SELECT remark FROM dbo.Settings s WHERE s.id = c.ID2) ca(remark)
ORDER BY c.typeID

Proof-of-concept dbfiddle here.
Sample data would still be appreciated!

If you want to read an excellent analysis by Paul White, read here.


Loading Custom Rules into unaccent postgresql hosted into an amazon aws instance The default unaccent module has no or little support for Greek Letters using the unaccent module as seen in following links:

https://stackoverflow.com/q/49845063
https://www.postgresql-archive.org/BUG-15347-Unaccent-for-greek-characters-does-not-work-td6035023.html

Also this is my problem in my case as well. So I wanted to load custom unaccent rules as seen in https://www.postgresql.org/docs/current/unaccent.html

So I wanted to know:

In the example CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french ); where the ( COPY = french ) is located?
Can be the copy statement ( COPY = french ) use data from the STDIN with my own rules?
Also can I use a dedicated database table in order to load my own unaccent rules as well?

The database is hosted into an Amazon RDS instance.

In the example CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
  where the ( COPY = french ) is located?

Text search configurations are stored in pg_catalog.pg_ts_config.

They can also be displayed with \dF[+] in psql.

  Can be the copy statement ( COPY = french ) use data from the STDIN with my own rules?

No. The COPY clause in CREATE TEXT SEARCH CONFIGURATION clones a text configuration and has nothing to do (besides the name) with the SQL COPY command meant to import data from the outside of the database.

  Also can I use a dedicated database table in order to load my own unaccent rules as well?

No.

The latest version of PostgreSQL (12) comes with unaccent rules that correctly support Greek (as far as I understand) solving the issue #15347 mentioned in the question. For on-premise PostgreSQL instances, DBAs can get the latest unaccent.rules and copy it into an older PostgreSQL version, and making sure that the indexes that depend on unaccent get reindexed. With instances managed by a third party you would have to ask them.


Set permissions on a view I'm creating a view on a SQL Server 2005 database via SQL Server Management Studio 2008, the view reads from two tables. The view is accessed from a web application and I want users to be able to update one of the tables by updating the view, but not the other. One table is live data from an important system and should not be changed by this application. I control the web application so it shouldn't ever generate a query that will update that table, but I don't want it to be able to at all.

Is there any way to set permissions so the view itself can only read from one of the underlying tables? Everything I know about permissions is at the user level, but I thought there was a way to make a view read-only.

Don't grant UPDATE rights on the view.  Grant update rights on specific column within the view.  Only grant the update rights on the columns which you want the user / application to be able to update.


Are columns which are not indexes, sorted on disk together with index? Are columns which are not indexes, sorted on disk together with index, in MySQL, in MyISAM and InnoDB?

An incorrect thought that I started to write:

  I think that probably not, since they are not indexed; if they were sorted that would mean that they are indexes.

This is not correct because every index column is sorted by its own content's order, but I am asking about being ordered of every row (or of only of some columns) with its corresponding index.

To explain, I say: this would be useful to make selecting ranges of rows, which stand side-by-side, together, by their indexes, faster. For example, if I want to select * where id >1000 and id<2000 (there may be mistakes in the MySQL syntax, I do not know it well), then, the id column itself can be read from disk quickly because probably its cells from 1000 to 2000 stay together on physical disk. But other column content corresponding to id 1000 to 2000 may be written on different places on physical disk. If they are also sorted, they would be read faster. I think, maybe MySQL automatically sorts that columns on physical disk, for performance of such operations.

Are they sorted in other types of databases (PostgreSQL, etc.)?

December 27: I see from the 2 answers, that in the case when there is clustered index/primary key, the simple rows themselves are not sorted on physical disk (as I thought it might/may be), and even the clustered index is not sorted, if it is b-tree, i have read about b-tree and see that its nodes, as i understand, stay at random places on disk.

They may be sorted in some cases. The sorting index is usually called the clustering key. If it is the case then the entire table is stored inside such index (usually in some sort of a B-tree structure).

In the other case the table structure is known as a heap, rows are stored as they come, deleting leaves "holes" in the data blocks and those holes are later filled with new rows, so not even the "insertion order" is preserved.

MyISAM uses the heap structure, with each row being identified by the offset(sort of array index) into the data file. Each index then contains the indexed column(s) for each row, sorted in the proper order and with the offset number to locate the real row. That means that accessing the row by any index means locating the right node(s) in the index (B-tree) and then reading the right offset(s) from the data file (random seek to a different part of the disk may happen).

InnoDB uses clustering by the primary key (or if none is defined, first non-null unique key is used, or an internal autoincrementing column is added - so the rows are always sorted somehow). In such case an access by the primary key is "direct", when the proper value is located, you have entire row at hand, no need to do a second read. The secondary indexes on the other hand cannot store an offset like in MyISAM (because the B-tree is dynamically rebalancing itself, so the offset of a specific row can change anytime) and they store the primary key values of the row instead - so an access by a secondary key means two B-tree searches in InnoDB.

MS SQL Server offers an option to make the primary key (or another index) either clustered or nonclustered, so you can choose between the heap (no index is clustered) and the tree structure (one index is clustered). All other non-clustered indexes store a special (RowID) values in the heap case or the clustered key values of the row in the case of the CI.

PostgreSQL uses only heap tables but lets you reorder them by some index on demand (you have to trigger it, so the rows are ordered after the action but further writes to the table can break that order again).

TokuDB (a 3rd-party MySQL/MariaDB engine) can use multiple clustering keys on one table - effectively it maintains multiple copies of the table, each sorted different way. It comes with a penalty on writes, but TokuDB claims to use somethink they call fractal indexes which should make that penalty quite small.

If you need to use that functionality for some queries, you can "emulate" it by creating a covering index - that way the colums your query needs are available in the right order anytime, but again it means maintaining an ordered copy of (parts of) the table in your indexes.


How to generate a dynamic file name for a mysqldump on Linux? I want to schedule my MySql backs to happen on an hourly basis, but the problem is I want to customize the name of the file that is outputs but not to sure how to?

This is the command I am using now

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  > db-backup.sql

This works but I would to have it output a file something like this, but for it to happen dynamically, when the command is run

1-01-18.13:50.db-backup.sql

How could I do this on a single line of code

This should do it :

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  > `date +"\%Y-\%m-\%d_\%H-\%M"`.db-backup.sql

Will generate files like : 2018-01-01_13-50.db-backup.sql. You can adapt this to whatever exact format you want (but you should avoid using special characters such as :).

If you want to compress the file on the fly :

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  | gzip > `date +"\%Y-\%m-\%d_\%H-\%M"`.db-backup.sql.gz


Counting number of occurences in a time period I have a view that has 4 columns, an order date, machine reference number, item number, and quantity. How would I generate a list of machines that have consumed an item more than once in a given time period?

For example, I have 5 cars. on car #1, I changed the spark plugs on the 21st and again on the 29th, meaning I changed them more than once in a given period (2 weeks for this example). car 3 had its plugs changed on the 1st and then on the 21st, but the previous month it had its plugs changed on the 1st and the 4th, so it shows up on the list.

order_date    machine_reference_number    item_number    quantity
-----------------------------------------------------------------
'2016-05-21'     1                          21              1
'2016-05-29'     1                          21              2
'2016-04-01'     3                          13              4
'2016-04-04'     3                          13              1
'2016-05-01'     3                          13              4
'2016-05-21'     3                          13              4

I'm fairly certain this will have to be a stored procedure that compares each row, but I was hoping there would be a way to do it in plain-jane SQL without all the loops.

Edited based on your sample data.

For an item number to have a total quantity of two or more in a given date range:

 Select MachineRefNo, ItemNumber, Sum(Quantity) NumberOfItems
       From Table
       Where OrderDate Between DateRangeStart And DateRangeEnd
       Group By MachineRefNo, ItemNumber
       Having Sum(Quantity) > 1

For an item number to have two or more records in a given data range:

Select MachineRefNo, ItemNumber, Count(*) NumberOfOccurances
   From Table
   Where OrderDate Between DateRangeStart And DateRangeEnd
   Group By MachineRefNo, ItemNumber
   Having Count(*) > 1


PowerShell based SQL script execution I am new to powershell. Even I dont think I know the basics..But now I am assigned a work to do the execution of sql scripts residing in a remote server.

1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)

2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)

3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.

The 3 steps described above is my requirement. Someone please help on this..Any help would be really appreciated..

Thanks in advance..

Note: Before I get into sample code on one way to go about this, I recommend you make every attempt at grasping the programmatic interaction between SQL Server and PowerShell.  It is not hard, and by muscling through a few scripts from given requirements like the ones above your proficiency will exponentially grow.

  1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServerName = Read-Host "Server name"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

$SqlServer.ConnectionContext.LoginSecure = $false

$SqlServer.ConnectionContext.Login = Read-Host "Login"
$SqlServer.ConnectionContext.SecurePassword = Read-Host "Password" -AsSecureString

  2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)

$SqlScripts = @()

while ($true) {
    $ScriptLocation = Read-Host "Enter location (""x"" if no more needed)"
    if ($ScriptLocation.ToLower() -eq "x") {
        break
    }
    $SqlScripts += $ScriptLocation
}

  3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.

foreach ($SqlScript in $SqlScripts) {
    try {
        $SqlServer.Databases["master"].ExecuteNonQuery((Get-Content -Path $SqlScript))
    }
    catch {
        Write-Error $_.Exception
    }
}

As for your last statement "After the execution, then it should again ask user to continue or exit", I'm not quite sure what you're looking to do there.  Ask the user to continue doing what?  Regardless, the above sample code should give you quite a head start on your requirement.  Test this logic out in a non-production environment to ensure it does what you want and rework accordingly.  There are a couple of assumptions here (such as you're not looking for a return from the script, which is why I used ExecuteNonQuery(), I don't know your databases layout so it's going to execute in the context of master, so on and so forth).


High availability database switchover I have a database in a high availability group with two nodes and one listener.

Several times a week, I have a problem with switchovers. The message I get is:

  Stopped listening on virtual network name ''. No user action is required.

Any idea?

When your cluster tries multiple times to attempt a failover and it maxes out on the failover threshold value, an error that you described occurs. Basically it gives up and goes into a failed/offline state and stops listening on Virtual Network Name. To find the RCA of your issue, you need to identify the errors that occurred prior to this error. Let's us know what you see and then we can further dig into issue.


InnoDB tablespace critical Could the error shown on the screenshot be the reason why my site is very slow?

If so, I really need help to fix it since I am far from being an engineer!

I wouldn't think this is why your site is very slow.  You can change the alert levels by editing the following file:

vi /etc/munin/plugin-conf.d/mysql_innodb

In this file you should see:

[mysql_innodb]
env.warning 2147483648
env.critical 1073741824

If the tablespace falls below either of these levels a warning or critical is raised.  Change these limits to whatever values (in bytes) you need. If you wish to completely disable this monitoring set the values to 0:

[mysql_innodb]
env.warning 0
env.critical 0

Restart munin:

/etc/init.d/munin-node restart
su munin -c /usr/bin/munin-cron


Removing Brackets and the content I have a column in my database called Horse_Name. This currently includes the country if outside of UK in brackets (ire),(fra) ect.

Is it possible to run a query to remove the brackets and the content very much like in excel whereby you can replace all (*) within the given column?

An example would be as follows

Current            Outcome
----------------+--------------
Horse_Name      |  Horse_Name
----------------+--------------
Rocket(FRA)     |   Rocket
Apple Jade(IRE) |   Apple Jade
Zac             |   Zac

I would like to go through the whole column named Horse_Name to remove the brackets and data.

I have tried the following code but it only finds the values and does not replace them

SELECT REPLACE(Horse_Name,
               SUBSTRING(Horse_Name, LOCATE('(', Horse_Name), LENGTH(Horse_Name) - LOCATE(')',
               REVERSE(Horse_Name)) - LOCATE('(', Horse_Name) + 2), '') AS Horse_Name
FROM  TFResults;

Kind Regards.

I've set up this example:

create table tbl (horse_name varchar(100));
insert into tbl values ('Rocket(FRA)'),('Apple Jade(IRE) 2'),('Zac');

If last parenthese is located always at end of the string you could use next syntax:

select case when locate('(', horse_name) = 0
            then horse_name
            else left(horse_name, locate('(', horse_name) - 1)
       end as horse_name
from   tbl;

But if there are additional characters after the last parenthese you could CONCAT left and right substrings in this way:

select case when locate('(', horse_name) = 0
            then horse_name
            else concat(left(horse_name, locate('(', horse_name) - 1),
                        right(horse_name, length(horse_name) - locate(')', horse_name)))
       end as horse_name
from   tbl;

This is the final result (note I've added a 2 after (IRE):

| horse_name   |
| :----------- |
| Rocket       |
| Apple Jade 2 |
| Zac          |

db<>fiddle here


Managing multiple DBs and servers with central or local processes? In my previous job, I had to manage some DBs (~500) installed on some servers (~20). Everything on SQLServer.

For basic tasks such as backups, archives or monitoring, I used to have a process installed on a central server which knows where is everything and what to do on each DB or server. So, a generic SSIS package, some configuration tables, a couple of SQLServer jobs, and we loop, apply scripts, and pull results and logs to this central DB server.

Everything was going well and I get used to that kind of design. And also learnt the good and the bad (ex: pro = only one process easy to configure ; con = it's a kind of spof).

Now, in my new company, I am facing the opposite: each server has its own local jobs and scripts, and pushes the results to a central DB server (using linked servers).
First, I thought it was not a very reliable way of doing things (if one task is completely failing, nobody knows / harder versioning / etc...). But actually, it seems to work well for years.

So I would like to have a better point of view than "I'm not used to that so it's bad".

What could be the pros and cons of each approach ? (in a SQLServer environment, version 2008 and more).

In the environment I managed for about a decade, backups were handled by local jobs, but we did have a couple of monitoring jobs (daily login records for audit purposes, and daily report on failed jobs) that was centralized.

So, here's what I can think of:

Centralized job "hub"

PROS:

Same approach applied to all servers.
Easy to change process for all servers in the environment.
Easy to monitor entire environment.

CONS:

One point of failure - If the hub fails, nothing gets backed up, etc.
Likely implemented with one account with admin access to all servers - if that's compromised, everything is.
"One size fits all" approach to all tasks may not be appropriate, in some cases (server-specific jobs may limit when you want to do full backups, for example; if all full backups are run from a single job, it becomes more difficult to accommodate specific time frames for particular servers).
You can presumably run certain unique jobs from that single server, but beyond a certain point it gets confusing as to which jobs hit which servers.
Harder to check status of a single server (unless that data is retained in a central table, in which case it may actually be easier).

Server maintenance jobs run locally, reported to hub

PROS:

Jobs can be configured as is best for that server.
One server can fail with no impact to the others.
Can use accounts that do not need access to all servers.
Easier to confirm activity for a single server - check the Job Activity Monitor for the server, all jobs are there.

CONS:

Failure of one server to report job status to hub server may not be noticed (harder to notice something that isn't there than something that is).
Jobs have to be set up one each server, or backups, etc. won't happen.
May be harder to spot trends affecting multiple servers.
Jobs may not be configured in a consistent way (even if the same person configures all the servers).

Many of the cons can be overcome if you've still got a central server that receives/collects data on jobs across all servers. As I said, that's the environment I worked in for several years, and it worked well for us.


MSSQL Select all parent rows for which exists a combination of children rows with desired value I would like to select all parent rows for which exists a combination of children rows with desired value. The easiest approach is to use multiple exists clauses, but this is not optimal - anyone knows better solution? Here is a SQL fiddle: http://sqlfiddle.com/#!6/8da76/5

Schema:

CREATE TABLE [dbo].[Parents](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [GroupId] [uniqueidentifier] NOT NULL
);

INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000001');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000002');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000003');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000004');
INSERT INTO [Parents] ([GroupId]) VALUES ('00000000-0000-0000-0000-000000000005');

CREATE TABLE [dbo].[Childrens](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [ParentId] [bigint] NOT NULL,
    [TypeId] [int] NOT NULL
);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 1);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 2);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 3);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 4);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (1, 5);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (2, 1);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (2, 4);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (2, 5);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (3, 4);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (3, 5);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (3, 6);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (4, 2);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (4, 6);

INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (5, 1);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (5, 2);
INSERT INTO [Childrens] ([ParentId], [TypeId]) VALUES (5, 3);

Sample query to select all parent rows for which exists a combination of children rows with TypeId = 1 and 2 (query should return parent with Id = 1 and 5):

SELECT *
  FROM Parents p
  WHERE EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 1 )
    AND EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 2 );

Sample query to select all parent rows for which exists children row with TypeId = 4, 5 and 6 (should return parent with Id = 3)

SELECT *
  FROM Parents p
  WHERE EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 4 )
    AND EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 5 )
    AND EXISTS(
        SELECT *
          FROM Childrens c
          WHERE p.Id = c.ParentId
            AND c.TypeId = 6 );

Number of conditions for children TypeId is variable.

Best Regards!

Below is one method.  In the outer WHERE clause, specify the number of types included in the IN clause.  An composite index on the Childrens table ParentId and TypeId will help optimize the query.

SELECT  *
FROM    Parents p
WHERE   2 = ( SELECT    COUNT(DISTINCT c.TypeId)
              FROM      Childrens c
              WHERE     p.Id = c.ParentId
                        AND c.TypeId IN ( 1, 2 )
            );

SELECT  *
FROM    Parents p
WHERE   3 = ( SELECT    COUNT(DISTINCT c.TypeId)
              FROM      Childrens c
              WHERE     p.Id = c.ParentId
                        AND c.TypeId IN ( 4, 5, 6 )
            );

EDIT:
The original DDL in the question didn't include constraints or indexes.  These aren't relevant for query functionality but are important to compare performance of alternative solutions.

Assuming the IDENTITY columns in both tables are also the primary keys and the tables are related, I added the constraints below.

ALTER TABLE Parents
    ADD CONSTRAINT PK_Parents PRIMARY KEY CLUSTERED(Id);

ALTER TABLE Childrens
    ADD CONSTRAINT PK_Childrens PRIMARY KEY CLUSTERED(Id);

ALTER TABLE Childrens
    ADD CONSTRAINT FK_Childrens_Parents FOREIGN KEY (ParentId) REFERENCES Parents(Id);

I added candidate indexes on columns specified on JOIN or WHERE clauses and specified UNIQUE for those indexes that include the primary key column.  Indexes not deemed to be used can be dropped afterward.

CREATE UNIQUE INDEX idx1 ON Parents (Id, GroupId);
CREATE INDEX idx1 ON Childrens (ParentId, TypeId);
CREATE INDEX idx2 ON Childrens (TypeId, ParentId);

Finally, I ran the original query in my answer in SSMS along with the query below which Tpsamw1 proposed in a comment (but without ORDER BY to level the playing field). SET STATISTICS IO ON and including the actual execution plan:

SELECT  p.Id
      , p.GroupId
FROM    Parents p
        INNER JOIN Childrens c ON c.ParentId = p.Id
WHERE   c.TypeId IN ( 4, 5, 6 )
GROUP BY p.Id
      , p.GroupId
HAVING  COUNT(DISTINCT c.TypeId) = 3;

SSMS reported comparable performance (50% of batch cost for each query) but that doesn't tell the whole story.  The STATISTICS IO showed a higher number of logical reads for my original query, which I believe a better indicator of performance.  This suggests Tpsamw1's query performs better.  Below are the stats:

My original query:

Table 'Childrens'. Scan count 15, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Parents'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Tpsamw1 query:

Table 'Parents'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Childrens'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I then loaded additional data by copying the original test data and update stats:

INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 1;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 1;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 2;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 2;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 3;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 3;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 4;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 4;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 5;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 5;
GO 1000

UPDATE STATISTICS dbo.Childrens WITH FULLSCAN;
UPDATE STATISTICS dbo.Parents WITH FULLSCAN;
GO

Running the queries again shows execution plan changes to both queries.  Both queries yielded the same number of logical IOs, indicating performance is be about the same.  The only execution plan difference was an inner merge join versus a right outer merge join.

Table 'Parents'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Childrens'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


not returning 0 if value is NULL Everything works as intended, but function should return 0 if value is NULL. Currently it returns nothing (see problem at the end of the post).
How could I make to return 0 if no order is found?
'orders' Table
CREATE TABLE orders (
    id INT NOT NULL,
    code INT NOT NULL,
    service_id INT NOT NULL,
    status CHARACTER VARYING(50),
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

Records
INSERT INTO orders
(id, code, service_id, status, creation, status_date, created_user)
VALUES
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);

Testing Statement
SELECT EXTRACT(YEAR FROM creation) AS year,
                        orders.code,
                        COUNT(orders.code)
                FROM orders
                WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = 2019 AND orders.code = 2394
                GROUP BY year, orders.code
                ORDER BY year ASC

Returns

year | code | count
:--- | ---: | ----:
2019 | 2394 |     3

Function
CREATE OR REPLACE FUNCTION
yearly_orders(inyear numeric DEFAULT 0, incode INT DEFAULT 0)
RETURNS TABLE (retyear numeric, retcode bigint, retcout bigint) AS
$$
DECLARE
    retcount int DEFAULT 0;
    retcode int DEFAULT 0;
    retyear numeric DEFAULT 0;
BEGIN
    RETURN QUERY SELECT CAST(EXTRACT(year FROM creation) as numeric),
                        CAST(orders.code as bigint),
                        COUNT(orders.code)
                FROM orders
                WHERE status = 'Requested'
                AND EXTRACT(YEAR FROM creation) = inyear
                AND orders.code = incode
                GROUP BY EXTRACT(year FROM creation), orders.code
                ORDER BY retyear ASC;

END;
$$
LANGUAGE plpgsql;

Executing Function
select * from yearly_orders(2019, 1194);

Returns <-- PROBLEM

retyear | retcode | retcout
------: | ------: | ------:
        |         |

SHOULD RETURN

retyear | retcode | retcout
------: | ------: | ------:
 0       |     0    |    0

db<>fiddle
The db<>fiddle to try this out can be found here.
The code was made by user:15356 (Here)

You are wanting GROUP BY and the COUNT aggregate to return a row with a zero value when there are otherwise no rows to return - GROUPing does not work like that. It combines what rows would have been returned if GROUP BY was not present, so no rows in that case.
You could add a second SELECT, combined with UNION ALL, that returns one row containing inyear, incode, 0 if there are zero matching rows (counted by sub-select). If there are rows this part returns nothing, if there are no matches it returns a row indicating that. Note that this may be quite inefficient.


Sort all monthly incomes and group by year I want to show all my monthly incomes and group them yearly, like in the picture below.
The query is like

SELECT
monthname(date_added) as monthno,
year(date_added) as year,
SUM(amount) as amounts
FROM incomes GROUP BY year ORDER BY monthno

but it groups yearly and hides months of the year:

screenshot

Quick solution:

SELECT MONTH(date) AS monthno
     , SUM(amount) AS monthsum
  FROM incomes
 GROUP BY monthno
 ORDER BY monthno
;

More date functions are available for getting a text version of the date.


How to fetch the last updated time of a record? I have a table with 5 columns in the following format:

| id | value1 | value2 | value3 | last_updated |
------------------------------------------------

The value in the columns value1, value2 and value3 can change or remain same. Those values comes from an external process, which makes a lot of entries in that table. It records the changes in the last_update column. The value of the value* columns can remain the same. I am trying to find the first event (last_update) time when any of the value1, 2, 3 is changed for first time.

You task has only meaning if the "id" is not a primary key, but a foreign key of your table. It means, that your table has multiple rows with the same id.

Thus, your table is essentially a log.

To get the first row of all ids, you can use the MIN() aggregate function, grouped the tables with ids:

SELECT MIN(last_update) FROM yourTable GROUP BY id;

However, if it is possible that all the values remain the same if a new log entry arrives into your table, then the situation is a little bit harder.

SQL is not very effective is we want to compare consecuting rows, although it is possible.

A possible solution would be using windowing functions. Essentially, we count the timeline of all the records, and then compare all of them to their consecuting one.

WITH Raw AS (
  SELECT *, RANK() OVER (PARTITION BY Id ORDER BY last_update) AS EventNo
  FROM yourTable
)
SELECT Prev.Id, MIN(Next.LastUpdate) AS FirstChangeTime
FROM Raw AS Prev, Raw AS Next
WHERE
  Prev.Id = Next.Id
  AND
  Prev.EventNo + 1 = Next.EventNo
  AND
  ( Prev.Value1 <> Next.Value1
    OR
    Prev.Value2 <> Next.Value2
    OR
    Prev.Value3 <> Next.Value3
  )
GROUP BY Prev.Id;

Note, this solution is the simplest what came in my mind now. It has a terrible performance and a resource waste. Use it only for data mining related tasks.

To do the same effectively requires more advanced technics over the limit of the current answer.