Simulate production workload on test environment We are on SQL Server 2008 R2 Standard Edition. Some tables are highly fragmented. I want to see if defragmentation will improve performance and is worth the effort and tables/indexes being locked during the process. Therefore I want to restore the full backup on a test environment and simulate a live environment.
What is the best way to follow? How can I capture the events going on in a live environment for some period? What tools are available for this?
Thank you

You have a few options. There are third party tools that can help simulate a load against your database. There are open simple utilities like SQL Query Stress which can run whatever queries you supply it in whatever frequency you wish.

There is also the combination of running server side traces and replaying those traces. This can be as simple as gathering scripts from a trace (as Dan rightly pointed out above don't use the Profiler GUI to capture events, but capture the trace to a file using server side trace) and then replaying the workload if you more care about individual query performance if you care more about individual query performance and less about concurrency.

If you need to dig into concurrency and see the workload in real time from multiple angles - you would want to look at using the Distributed Replay features. This allows you to point to a trace in any version of SQL Server back to SQL Server 2005 and run it against any version of SQL Server, also back to SQL Server 2005.  Jonathan Kehayias has a step by step walk through of how to setup Distributed Replay. This would allow you to configure the replay from multiple clients distributing the load - a single profiler replay would never allow you to scale. (Please note - you'll see the tool is a SQL Server 2012 tool. Don't let that scare you, the tool came out then, but can deal with your SQL Server 2008 R2 environment)

All this said - there is complexity to this either way. Taking a trace for replay has some requirements and steps. Replaying it also has some steps to take to make sure you are starting from the same starting point, etc. This complexity is worth understanding, it can help perform upgrade readiness tests, help understand hardware changes on total application, etc. You could use the RML utilities to analyze the before and after traces also which can help show you how things improved or didn't after.

If you just want to see if rebuilding indexes made any changes, you can do simpler tests also. You could take a large repeatable insert and run only that before and after. You could take a few queries with high scans and a few with high seeks and run that before and after. I would make sure you still rebuild statistics either way, though, since rebuilding your indexes does rebuild the index level statistics and that sometimes gives more of a benefit than rebuilding indexes and people attribute the benefit to the index rebuilds rather than the index statistics that got rebuilt. I tend to fall into the "rebuild" camp more often than not, but hardware changes in IO performance, workload patterns, etc all make that not a hard and fast rule necessarily for me.


Is Fuzzy Matching available in SQL Server 2019 Standard? I can't seem to find a definitive answer to what seems like it ought to be a really simple question. Previously fuzzy matching was an enterprise only feature, but I've heard it mentioned that this is now available with the standard edition. Can someone please confirm whether this is the case?
Edit: for clarity, I am specifically referring to SSIS fuzzy grouping / lookup

Fuzzy Matching is still enterprise only
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-features-supported-by-the-editions-of-sql-server?view=sql-server-ver15


Will this RedGate backup plan interfere with a new differential backup plan I want to create? First, I need to get an assumption verified: If I setup a differential backup plan and someone comes along and manually creates a backup without specifying COPY_ONLY then it's going to throw the plan off, right? (I'm assuming a scenario where they backup to some arbitrary location and then maybe even delete the backup they created after they're done with it).

Secondly, we currently have a RedGate backup solution in place that uses the code below. I'm not familiar with how the master..sqlbackup proc it uses works so I am wondering if it's possible for me to leave this in place if I setup a differential plan alongside it or if I would need to disable this.

Why would I do this? Well, we're migrating to the cloud and I'd like to setup differential backups for the purpose of minimizing downtime as we move the database. But I'd just as soon not mess with the existing backup plan if I don't have to. And if I have to then I have to, but would rather not just in case something goes awry with my backup plan in the process.

DECLARE @exitcode INT , @sqlerrorcode INT;

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES
    [DB1, DB2, etc]

    TO DISK = ''F:\MSSQL11.MSSQLSERVER\MSSQL\Backup\<TYPE>\<DATABASE>\<AUTO>''
    WITH    ERASEFILES_ATSTART = 1b,
            CHECKSUM,
            DISKRETRYINTERVAL = 10,
            DISKRETRYCOUNT = 3,
            COMPRESSION = 4,
            THREADCOUNT = 4,
            NAME = ''<AUTO>'',
            DESCRIPTION = ''<AUTO>''"',
    @exitcode OUTPUT, @sqlerrorcode OUTPUT

IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

EDIT: And I can already imagine someone might ask why I don't just use RedGate to do the diff backup plan. First, I have no experience with it and second we won't have RedGate on the new server.

If I setup a differential backup plan and someone comes along and
  manually creates a backup without specifying COPY_ONLY then it's going
  to throw the plan off, right?

Yes, your next differential backup will have a differential base LSN of the full backup the other person performed so your restore chain would require that backup.

  Secondly, we currently have a RedGate backup solution in place that
  uses the code below. I'm not familiar with how the master..sqlbackup
  proc it uses works so I am wondering if it's possible for me to leave
  this in place if I setup a differential plan alongside it or if I
  would need to disable this.

If you add a differential backup in parallel to this, your differentials will still require the RedGate backups in order to restore the DIFF because the RG backups will reset the differential base LSN.

  Why would I do this? Well, we're migrating to the cloud and I'd like
  to setup differential backups for the purpose of minimizing downtime
  as we move the database. But I'd just as soon not mess with the
  existing backup plan if I don't have to. And if I have to then I have
  to, but would rather not just in case something goes awry with my
  backup plan in the process.

  EDIT: And I can already imagine someone might ask why I don't just use
  RedGate to do the diff backup plan. First, I have no experience with
  it and second we won't have RedGate on the new server.

If you don't have RedGate on the new server, how are you planning to restore FULL the backups taken by RedGate in order to restore your differentials? My understanding is that restoring a backup taken by RedGate requires RedGate to be installed. I could be wrong (limited experience with RG) but this is how most third-party backup tools for SQL Server work, usually because of custom encryption or compression that is applied to the backups.

Without additional info available, I would suggest one of the following:

Replace RG on the existing server now with whatever you're planning to use in Azure as an end-state (unless you're using Managed Backups in Azure).
Look at Log Shipping to keep your Azure server in sync with on-premise until you're ready to cut over. You would have to disable any RedGate log backups that are in place.


what if mongos server goes down in shard cluster setup I have followed the tuturial Deploy a Sharded Cluster

I have setup

config server replica set with 3 members (cfg1, cfg2, cfg3) [replica set name = configserverreplica]
mongodb server replica set with 3 members (mongoserver01, mongoserver02, mongoserver03)  [replica set name = mongodbreplicaset]
on another node named foreign-connector on which I am running mongos using command mongos --configdb "configserverreplica/cfg1:27019, cfg2:27019, cfg3:27019" --bind_ip_all —port 27020.
from client machine I connect to foreign-connector  with mongo --host foreign-connector --port 27020

Above is my set up.   And it works perfectly fine for failover test (I can take down any node at a given time... Everything works)

My question is what happens when mongos server (foreign-connector) goes down.
Is there any other way to be failover proof.

Thanks for your help is advance.

what if mongos server goes down in shard cluster setup

As per MongoDB documentation here mongos for “MongoDB Shard,” is a routing service for MongoDB shard configurations that processes queries from the application layer, and determines the location of this data in the sharded cluster, in order to complete these operations. From the perspective of the application, a mongos instance behaves identically to any other MongoDB instance.

  My question is what happens when mongos server (foreign-connector)
  goes down.

if mongos server will goes down then config servers connection will be lost with mongos server. Then each shard in cluster connection will be lost.

As per MongoDB documentation here Enable sharding for a database to proceed, you must be connected to a mongos associated to the target sharded cluster.

For your further ref here


What will happen if I drop SYSAUX tablespace from Oracle 11G As there are two  mandatory tablespaces in Oracle SYSTEM and SYSAUX.
They have mention on docs 1 " You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA system privilege and you have started the database in MIGRATE mode. " that means we can drop SYSAUX tablespace, then how system will be affected to this

First, it is important to understand the purpose of SYSAUX tablespace. As the name implies, it is intended to support system tablespace, by holding metadata about database components and other frequent changing data, such as reports.

Docs: "If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability."

The startup migrate option was introduced on version 9i, and it's only suitable when migrating oracle across different versions of database. So, why can I drop the sysaux tablespace with "startup migrate"? Only because Oracle will internally take care of the general errors that are expected to happen during migration processes. It will change a bunch of parameters through alter session, enable some trace events, all of these to make sure migration will happen smoothly. Example of parameters:

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

So do not drop sysaux tablespace if you don't have a reasonable reason to do so. If you need to manage its data, there are a lot of options:

You can query v$sysaux_ocuppants to determine what resources are taking space;
You can move some of its contents to another tablespace through packages/ procedures;


moving database from old disk to new disk I need to move a database from an old disk which is no longer bootable to a new bootable disk. I have been able to mount the old disk. Now I need to move the database from there to a new disk. Can this be done? Where do I start and what do I need to move?

You should be able to create a new database with the same name on the new system, shut down the service, replace the new (empty) files for your database with the old ones and finally start the service again. See http://www.kimvette.net/linux/recover_and_mount_mysql_database_files_after_recovery for more info.


what is the quickest way to delete duplicate rows? I need to delete duplicate rows from a large table.
what is the best way to achieve that?

currently I use this algorithm:

declare @t table ([key] int  )

insert into @t select 1
insert into @t select 1
insert into @t select 1
insert into @t select 2
insert into @t select 2
insert into @t select 3
insert into @t select 4
insert into @t select 4
insert into @t select 4
insert into @t select 4
insert into @t select 4
insert into @t select 5
insert into @t select 5
insert into @t select 5
insert into @t select 5
insert into @t select 5
insert into @t select 6
insert into @t select 6
insert into @t select 6
insert into @t select 7
insert into @t select 7
insert into @t select 8
insert into @t select 8
insert into @t select 9
insert into @t select 9
insert into @t select 9
insert into @t select 9
insert into @t select 9

select * from @t

; with cte as (
    select *
        , row_number() over (partition by [Key] order by [Key]) as Picker
    from @t
    )
delete cte
where Picker > 1

select * from @t

when I run it on my system:

;WITH Customer AS
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AccountCode ORDER BY AccountCode ) AS [Version]
    FROM Stage.Customer
    )
    DELETE
    FROM    Customer
    WHERE   [Version] <> 1

I found that <> 1 is better than > 1.

I could create this index, currently not present:

USE [BodenDWH]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Stage].[Customer] ([AccountCode])
INCLUDE ([ID])
GO

Is there any other way to get this done?

On this occasion this table is not big - about 500,000 records on the live system.

the delete is part of a SSIS package, it runs daily and deletes about 10-15 records a day.

there are problems in the way the data is structured, I just need one AccountCode for each customer but there could be duplicates and if they are not removed, they break the package on a later stage.

It was not me who developed the package, and my scope is not to re-design anything.

I am just after the best way to get rid of the duplicates, in the quickest possible way, without having to refer to index creation, or anything, just the T-SQL code.

If the table is small and the number of rows you are deleting is small, then use

;WITH Customer AS
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AccountCode ORDER BY (select null) ) AS [Version]
    FROM dbo.Customer
    )
    DELETE
    FROM    Customer
    WHERE   [Version] > 1;

Note: In above query you are using an arbitrary ordering in the window order clause ORDER BY (select null) (learned it from Itzik Ben-Gan's T-SQL Querying book and @AaronBertrand cited that above as well).

If the table is large (e.g. 5M records) then deleting in small number of rows or chunks will help not bloat transaction log and will prevent lock escalation.

  A lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table.

while 1=1
begin
WITH Customer AS
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AccountCode ORDER BY (select null) ) AS [Version]
    FROM dbo.Customer
    )
    DELETE top(4000) -- choose a lower batch size than 5000 to prevent lock escalation
    FROM    Customer
    WHERE   [Version] > 1

    if @@ROWCOUNT < 4000
    BREAK ;

end


considerations when using triggers on a replicated (target) database Our SQL Sever 2008 application database is replicated from Server A to Server B (push replication).  We use the copy, let's call it database_b, on Server B to create reports and run other queries so that our reports do not interfere with the application.  Currently we leverage inefficient views to combine data across multiple tables in database_b so that report writing is simplified for our report writers (who have basic SQL skills).

99.9% of the database activity is INSERTS, so we are exploring a way to replace the inefficient views with tables we can optimize.  Here is a simplified example:

There is an appointment table and a location (lookup) table.  Every time a new appointment is scheduled, a row is added to the appointment table.  Every time this INSERT happens, I want to take that appointment_id and insert it and its corresponding location name (join on location_id from both tables) into a reporting table.

I have accomplished this with a trigger on the appointment table in database_b on Server B.

My question is - are there any particular considerations given that database_b is a replicated copy?  Do I need to worry about a failing trigger mucking up the entire (push) replication process?  Anything else I am missing?

Unfortunately it's difficult to test this in our development environment, so I don't have the opportunity for a lot of trial and error.

This sounds like a great situation to use indexed views. These are pretty much what they sound like: a view that physically stores its results in the database, and is updated automatically when the base tables are updated.

A few cautions:

Having a lot of these in your subscription database could slow down replication (but so could lots of triggers).
There are some strict SET options you need to change in order to create them, and you have to use WITH SCHEMABINDING when creating the view.
There are additional strict requirements about what can be in your view. For example, you can't use outer joins, aggregates, or cross/outer apply.
If you're using Standard Edition, you need to query the view by name using the WITH (NOEXPAND) hint. SQL Server won't automatically use the view if you attempt to query its base tables.

There's (a lot) more detail in this article, as well as a nice example using the AdventureWorks database:
http://msdn.microsoft.com/en-us/library/ms191432%28v=sql.100%29.aspx

If it's a relatively simple query that's only performing poorly because of a lot of data and (inner) joins, this could be an easy way to improve it.


How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use it, it seems to ignore any index I put on it:

    -- +++ THE QUERY THAT I WANT TO IMPROVE PERFORMANCE-WISE +++

    SELECT TOP 1 *
    FROM    dbo.TB_test1 t1
            INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1
    ORDER BY t1.somethingelse1
           ,t2.somethingelse2;

    GO

The table setup is as follows:

two tables
they are joined by an inner join by the query above
and ordered by a column from the first and then a column from the second table by the query above; only TOP 1 is selected
(in the script below there are also some lines to generate test data, just in case it helps reproducing the problem)

-- +++ TABLE SETUP +++

CREATE TABLE [dbo].[TB_test1]
    (
     [PK_ID1] [INT] IDENTITY(1, 1)  NOT NULL
    ,[something1] VARCHAR(40) NOT NULL
    ,[somethingelse1] BIGINT NOT NULL
        CONSTRAINT [PK_TB_test1] PRIMARY KEY CLUSTERED ( [PK_ID1] ASC )
    );

GO

create TABLE [dbo].[TB_test2]
    (
     [PK_ID2] [INT] IDENTITY(1, 1)  NOT NULL
    ,[FK_ID1] [INT] NOT NULL
    ,[something2] VARCHAR(40) NOT NULL
    ,[somethingelse2] BIGINT NOT NULL
        CONSTRAINT [PK_TB_test2] PRIMARY KEY CLUSTERED ( [PK_ID2] ASC )
    );

GO

ALTER TABLE [dbo].[TB_test2]  WITH CHECK ADD  CONSTRAINT [FK_TB_Test1] FOREIGN KEY([FK_ID1])
REFERENCES [dbo].[TB_test1] ([PK_ID1])
GO

ALTER TABLE [dbo].[TB_test2] CHECK CONSTRAINT [FK_TB_Test1]

GO

-- +++ TABLE DATA GENERATION +++

-- this might not be the quickest way, but it's only to set up test data

INSERT INTO dbo.TB_test1
        ( something1, somethingelse1 )
VALUES  ( CONVERT(VARCHAR(40), NEWID())  -- something1 - varchar(40)
          ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse1 - bigint
          )

GO 100000

RAISERROR( 'Finished setting up dbo.TB_test1', 0, 1) WITH NOWAIT

GO

INSERT INTO dbo.TB_test2
        ( FK_ID1, something2, somethingelse2 )
VALUES  ( ISNULL(ABS(CHECKSUM(NewId())) % ((SELECT MAX(PK_ID1) FROM dbo.TB_test1) - 1), 0) + 1 -- FK_ID1 - int
          ,CONVERT(VARCHAR(40), NEWID())  -- something2 - varchar(40)
          ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse2 - bigint
          )

GO 100000

RAISERROR( 'Finished setting up dbo.TB_test2', 0, 1) WITH NOWAIT

GO

The indexed view should probably be defined as follows and the resulting TOP 1 query is below. But what indexes do I need so that this query performs better than without the indexed view?

    CREATE VIEW VI_test
    WITH SCHEMABINDING
    AS
        SELECT  t1.PK_ID1
               ,t1.something1
               ,t1.somethingelse1
               ,t2.PK_ID2
               ,t2.FK_ID1
               ,t2.something2
               ,t2.somethingelse2
        FROM    dbo.TB_test1 t1
                INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1

    GO

    SELECT TOP 1 * FROM dbo.VI_test ORDER BY somethingelse1,somethingelse2

    GO

It seems to ignore any index I put on it

Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint.
Without the hint, the query optimizer (in Enterprise Edition) may make a cost-based choice between using the materialized view or accessing the base tables. Where the view is as large as the base tables, this calculation may favour the base tables.
Another point of interest is that without a NOEXPAND hint, view references are always expanded to the base query before optimization begins. As optimization progresses, the optimizer may or may not be able to match the expanded definition back to the materialized view, depending on previous optimization activity. This is almost certainly not the case with your simple query, but I mention it for completeness.
So, using the NOEXPAND table hint is your main option, but you might also think about just materializing the base table keys and the columns needed for ordering in the view. Create a unique clustered index on the combined key columns, then a separate nonclustered index on the ordering columns.
This will reduce the size of the materialized view, and limit the number of automatic updates that must be made to keep the view synchronized with the base tables. Your query can then be written to fetch the top 1 keys in the required order from the view (ideally with NOEXPAND), then join back to the base tables to fetch any remaining columns using the keys from the view.
Another variation is to cluster the view on the ordering columns and table keys, then write the query to manually fetch the non-view columns from the base table using the keys. The best option for you depends on the broader context. A good way to decide is to test it with the real data and workload.
Basic solution
CREATE VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t1.something1,
        t1.somethingelse1,
        t2.PK_ID2,
        t2.FK_ID1,
        t2.something2,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Brute force unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);
GO
SELECT TOP (1) *
FROM dbo.VI_test WITH (NOEXPAND)
ORDER BY somethingelse1,somethingelse2;

Execution plan:

Using a nonclustered index
-- Minimal unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
    (PK_ID1, PK_ID2)
WITH (DROP_EXISTING = ON);
GO
-- Nonclustered index for ordering
CREATE NONCLUSTERED INDEX ix
ON dbo.VI_test (somethingelse1, somethingelse2);

Execution plan:

There is a lookup in this plan, but it is only used to fetch a single row.
Minimal Indexed View
ALTER VIEW VI_test
WITH SCHEMABINDING
AS
    SELECT
        t1.PK_ID1,
        t2.PK_ID2,
        t1.somethingelse1,
        t2.somethingelse2
    FROM dbo.TB_test1 t1
    INNER JOIN dbo.TB_test2 t2
        ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.VI_test
    (somethingelse1, somethingelse2, PK_ID1, PK_ID2);

Query:
SELECT TOP (1)
    V.PK_ID1,
    TT1.something1,
    V.somethingelse1,
    V.PK_ID2,
    TT2.FK_ID1,
    TT2.something2,
    V.somethingelse2
FROM dbo.VI_test AS V WITH (NOEXPAND)
JOIN dbo.TB_test1 AS TT1 ON TT1.PK_ID1 = V.PK_ID1
JOIN dbo.TB_test2 AS TT2 ON TT2.PK_ID2 = V.PK_ID2
ORDER BY somethingelse1,somethingelse2;

Execution plan:

This shows the table keys being retrieved (a single row fetch from the view clustered index in order) followed by two single-row lookups on the base tables to fetch the remaining columns.


SQL Server seems to be ignoring specified backup file extension I have created a maintenance plan using the Wizard and this is the SQL produced, per SSMS:

BACKUP DATABASE [FOO] TO  DISK = N'C:\SQLBAKS\FOO\FOO_backup_2019_08_09_161656_6414445.diff'
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'FOO_backup_2019_08_09_161656_6414445',
SKIP, REWIND, NOUNLOAD,
STATS = 10

The resulting files do not have a ".diff" extension but ".bak"

I am trying to differentiate FULL backups from DIFFERENTIALS in the filename.

When you go through the wizard you should

Click the differential option and
Specify diff in the Backup file extension on the Destination Tab
(it's bak by default, even for differential)

Most people aren't fans of maintenance plans since you are kind of blindly turning over
some control and they create SSIS packages which aren't fun to troubleshoot. Ola's scripts are super easy, well documented, and have a ton of options. You can always roll your own via Agent Jobs too.


Is this compound index unnecessary? I have a large table which contains sensor data, along with the fields:

sensor_id
timestamp

I do queries against it using these fields almost exclusively. There are multiple sensors, and different sensors might have the same timestamp for a given set of data, so neither index can be unique.

I created three indexes: one for each of these columns (not unique), and a compound one for both (unique).

The table is constantly being written to, and queries to read data seem increasingly slow.

My question is, is the compound index unnecessary? Would it be faster to have only the two separate indexes? (Or remove those and keep only the compound index?) No other columns are used for filtering query data.

My question is similar to this one: Do I need separate indexes for each type of query, or will one multi-column index work?

Edit:

Here's the table definition:

CREATE TABLE `data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sensor_id` int(10) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  -- 71 other columns...
  PRIMARY KEY (`id`),
  UNIQUE KEY `IX_sensor_timestamp` (`sensor_id`,`timestamp`),
  KEY `IX_timestamp` (`timestamp`),
  KEY `IX_sensor_id` (`sensor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=78542614 DEFAULT CHARSET=latin1

It might be worth posting the table definition from your other question for clarity.

The composite index is doing a few things for you:

As you know, enforcing uniqueness on (sensor_id, timestamp); I'm unsure whether this is an important data integrity constraint.
Allowing queries that filter on both columns to look up matching rows by using a single index. MySQL can answer some queries (equality conditions on multiple columns are the ones I know about) by merging two indexes, but this tends to be significantly slower compared to using a single composite index.
The index can also be used to search for values in a left-based subset of the composite index, but not a right-based subset. So in this case it could help a query that filters on sensor_id values or sensor_id and timestamp values, but not timestamp values alone.

There are a number of caveats to this, so it's good idea to look at the EXPLAIN output for your queries and verify what indexes they're using. Keep also in mind that indexes can support  the read part of UPDATE and DELETE queries, as well as JOINs, GROUP BY, ORDER BY, and other operations I'm neglecting.

An example of a scenario where the composite index is unnecessary would be if you don't care about the uniqueness constraint and all your queries filter on timestamp or sensor_id, but not both.

The single-column index on sensor_id is actually redundant since the composite index on (sensor_id, timestamp) can be used by the same queries, but still you might find that some queries perform faster when doing scans on the single-column index compared to using a composite index with a wider key. The difference might not be enough to matter, though, and some testing will probably be required to find out.

In addition to looking at the EXPLAIN output for your queries, tools such as pt-index-usage from the Percona toolkit or the table INFORMATION_SCHEMA.INDEX_STATISTICS if you're running Percona Server or MariaDB can help you assess what indexes are actually being used.

References:

The Optimization That (Often) Isn’t: Index Merge Intersection

Practical MySQL indexing guidelines


Mongo Memory management I had a few questions

If there is sufficient RAM, does Mongo load entire collection into memory?
Suppose I had two collections A & B and I know that there is not enough RAM to hold both - and if 90% of my queries are to collection A - is there a way to configure Mongo to prefer to keep A in RAM as much as possible?

No, MongoDB don't "read whole collection to memory" (automatically).
If you don't read/write collection B, then yes, MongoDB keeps collection A in the memory.

So, basically MongoDB keeps (as long as there is enough memory to keep) all read and write data (indexes, documents) in the memory. Of course, you can "warm up" collection, by reading all documents. When memory is "full", the oldest documents are thrown away when newer data comes in.


SSRS 2016 - Change connection string in all reports We have migrated from SSRS 2012 to SSRS 2016, and moved it to a new domain.

Using below code, we are able to list all datasources and their connection strings. How can we change the connection strings of nearly 700 reports using PowerShell or T-SQL? (so that the point to new data warehouse server)

WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;

You can use the replace value of XML DML command to modify the value stored in the connection string. I have put together an example below for you as a guide but please note that it is untested as I do not have access to a report server at the moment, it should at least point you in the correct direction:

WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
        ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
 AS rd)
,SDS AS
(SELECT SDS.name AS SharedDsName
       ,SDS.[Path]
       ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
 FROM dbo.[Catalog] AS SDS
 WHERE SDS.Type = 5)     -- 5 = Shared Datasource
SELECT [SharedDsName], [Path], [DEF]
  INTO #catalog
  FROM SDS;

UPDATE #catalog
   SET DEF.modify ('
    declare namespace rds="http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource";
    declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
    replace value of (/rds:DataSourceDefinition/rds:ConnectString/text())[1]
    with "new connection string"
');

UPDATE a
   SET a.[Content] = CONVERT(image, CONVERT(varbinary(max), b.[DEF]))
  FROM dbo.Catalog AS a
  JOIN #catalog AS b
    ON a.[Name] = b.[SharedDsName]
   AND a.[Path] = b.[Path];

DROP TABLE #catalog;

More information regarding the replace value of command can be found at Microsoft Docs.


Issue Restoring Certificate to SQL Server When Different Service Account Used I am attempting to configure backup encryption in SQL 2017 and have run into some problems.

I have no issues in creating the master key and certificate on one machine. Nor do I have a problem getting the cert installed on another machine and reading the backup, however, I have an issue where this does not work should the other machine be running a different service account than the one where cert is created.

Here are the steps I am taking (I also tried restoring the master key but that also throws an error):

/* Server 1 */
/* Create the master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO

/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
   WITH SUBJECT = 'Backup Encryption Certificate';
GO

/* Backup the master key */
BACKUP MASTER KEY TO FILE = '\\FileShare\DatabaseMasterKey_Master.key'
    ENCRYPTION BY PASSWORD = 'SomeRandomPwd';

BACKUP CERTIFICATE BackupCert TO FILE = '\\FileShare\BackupCert.cer'
  WITH PRIVATE KEY (FILE = '\\FileShareBackupCert.pvk',
     ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd');

GO

/* Server 2 */
/* Create master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO

/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = '\\FileShare\BackupCert.cer'
  WITH PRIVATE KEY (FILE = '\\FileShare\BackupCert.pvk',
     DECRYPTION BY PASSWORD = 'RandomEncryptionPwd');

--Msg 15208, Level 16, State 6, Line 32
--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

/* Try restoring the master key instead */
DROP MASTER KEY;

RESTORE MASTER KEY FROM FILE = '\\FileShare\DatabaseMasterKey_Master.key'
    DECRYPTION BY PASSWORD = 'RandomEncryptionPwd'
    ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd';

--Msg 15317, Level 16, State 2, Line 39
--The master key file does not exist or has invalid format.

References I have looked at to try and figure out what is going on here:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-2017

I have also ensured that the SMK is the same between all machines, but still no luck in getting the cert restored.

I feel sure that I'm doing something wrong here, but I have not been able to figure out what.

Any ideas would be appreciated. Thanks.

You need to fix the NTFS permissions on the certificate. By default SQL, when it creates the backup will set the NTFS permissions so that only the account running the SQL Service can read the certificate backup file.

This is why it works with a single account, and doesn't work with two accounts.


How to do this binary appearance analysis I have a table named results:

integer: id
json: websites

An example of websites column is:

[{"url": "http://google.com"}, {"url": "http://example.com"}]

What I want to do is generate a binary appearance chart, where it shows a 1 or a 0 depending on if ANY url in the JSON blob is contained within another set.

All the possibilities are on columns, and all the results are as rows.

result_id | wikipedia.org | google.com | example.com
10         1               1            1
11         0               1            0
12         0               0            0
13         1               0            0

For it to return 1, ANY url in the JSON array object (with key ->>{'url'}) must match the domain
The URLs are full urls so ideally I would like to use regex to check if its a match (i can't do a full string match because I only care about the HOST of the URL). I'm thinking something like /https?:\/\/(example\.com)(/|?|$)/

Assuming data type jsonb and current Postgres 10.

Exact matches are simpler, since you can use the built-in jsonb contains operator @>:

SELECT id
     , (websites @> '[{"url": "http://wikipedia.org"}]')::int AS "wikipedia.org"
     , (websites @> '[{"url": "http://google.com"}]'   )::int AS "google.com"
     , (websites @> '[{"url": "http://example.com"}]'  )::int AS "example.com"
FROM   results
ORDER  BY id;

The cast to integer only to meet your desired form of 1/0. Or omit the cast and just use the boolean t/f instead.

Pattern matching is more sophisticated, since there is no direct jsonb operator:

SELECT id
     , count(*) FILTER (WHERE url ~ '^https?://wikipedia\.org\M') AS "wikipedia.org"
     , count(*) FILTER (WHERE url ~ '^https?://google\.com\M')    AS "google.com"
     , count(*) FILTER (WHERE url ~ '^https?://example\.com\M')   AS "example.com"
FROM   results r
LEFT   JOIN LATERAL (
   SELECT elem->>'url' AS url
   FROM   jsonb_array_elements(r.websites) w(elem)
   ) w ON true
GROUP  BY id
ORDER  BY id;

db<>fiddle here

Replace count(*) FILTER ... with bool_or(url ~ '^https?://wikipedia\.org\M') etc. if URLs are never contained more than once and/or you are not interested in the actual count.

Related:

Search for nested values in jsonb array with greater operator
Indexed range comparison of array key's value in row inside JSONB
Return counts for multiple ranges in a single SELECT statement
Collapsing a column to yes/no on query
What is the difference between LATERAL and a subquery in PostgreSQL?


SSIS hangs during For Each File enumeration While debugging an SSIS package that loops over thousands of files, execution hangs with no errors and no failed tasks. Slightly simplified, the package does this:

For Each File                       (a directory full of pdf files, each ranging from 800 -
                                        2000 pages and 3 - 8MB filesize)
    *   Script Task                 (get file information)
    *   File System Task            (clear out temp folder)
    *   Execute Process Task        (use pdftk to break file into individual pages)
    *   For Each File               (each page in temp folder, 40 - 100KB each)
        *   Script Task             (parse pdf file)
        *   Execute Process Task    (pdftk to concatenate like pages)
        *   Execute SQL Task        (insert one row into staging table using OPENROWSET)
        *   File System Task        (delete inserted file)
        *   Script Task             (set variables for next iteration)
    *   Execute SQL Task            (execute proc to merge staging into prod)
    *   File System Task            (rename completed file)

When I execute the package (debug mode in BIDS), it runs as expected through the first file and well into the second, but eventually hangs between iterations of the inner loop. When it hangs, all tasks are green, and the two loop containers are yellow. The output window does generate any messages, and going through the process tab, each task has a matching number of starts and stops except for the two loops and the package itself.

My first thought was that I'd exceeded an iteration limit for the For Each File enumerator, but I cannot find any documentation for such a limit. What else can I check to determine why SSIS stops running?

This appears to be an issue with the Visual Studio / BIDS client hitting memory boundaries, not with SSIS itself. While the package is running in debug mode in BIDS, the devenv.exe process can be observed to continually occupy additional memory. While the number of files processed before hanging is inconsistent, the package did, on one occasion, process nearly 9000 files before Visual Studio threw an out of memory exception.

Running the package with dtexec or through SQL Agent appears to resolve the issue.


Phpkb Query Optimization Running a phpkb CMS on a ubuntu lamp server.
mysql - 5.5.28-0ubuntu0.12.04.3-log

The tables in this database are not large in fact I have not found one over 10k rows.

Every five min I get a 'copy to temp table' query that hangs out on the server and locks tables. It brings the site to a stand still. Mysql takes 100% of one of the four cores on the server sometimes over I assume taking resources from the other cores. It still hangs.

I have made a ram disk as suggested in other threads and still no performance gains. I'm starting to think it's just going to come down to query optimization for the one that hangs. This seems to be the query that kills it from show full processlist; I also have logged slow queries to the mysql logs and this is the one that shows up over and over.

# Query_time: 152.569759  Lock_time: 0.000075 Rows_sent: 1181 Rows_examined: 383893921
use newkbdb;
SET timestamp=1461243155;

SELECT
  phpkb_articles.article_id AS article_id,
  article_title,
  article_content,
  article_status,
  article_date_time,
  SUM(phpkb_article_visits.article_hits) AS article_hits
FROM
  phpkb_articles
  LEFT OUTER JOIN phpkb_article_visits ON (phpkb_article_visits.article_id
  =phpkb_articles.article_id), phpkb_relations
WHERE
  article_show='yes'
  AND (phpkb_relations.article_id = phpkb_articles.article_id)
  AND article_status IN ('featured','approved')
  AND ( (article_expiry_date = '0000-00-00') OR (article_expiry_date <>
  '0000-00-00' AND article_expiry_date >= now()) )
  AND category_id=221 GROUP BY (phpkb_articles.article_id)
ORDER BY
  FIELD(article_status,'featured','approved'),
  article_title ASC;

Any suggestions or help appreciated I'm not a mysql dba this is just a unicorn site that some dept bought and now its become a huge dependency.

Thanks in advance.

UPDATE:
Create table statements:

CREATE TABLE `phpkb_articles` (
`article_id` int(11) NOT NULL AUTO_INCREMENT,
`author_id` int(11) NOT NULL,
`article_title` varchar(250) NOT NULL,
`article_content` longtext NOT NULL,
`article_date_time` datetime NOT NULL,
`article_status`enum('approved','disapproved','draft','featured','pending','pending-deleted','approved-deleted','featured-deleted','disapproved-deleted') NOT NULL DEFAULT 'pending',
`article_show` enum('yes','no') NOT NULL DEFAULT 'no',
`article_last_updation` datetime NOT NULL,
`article_keywords` varchar(250) NOT NULL,
`article_metadesc` varchar(250) NOT NULL,
`article_expiry_date` date NOT NULL,
`article_email_count` int(11) NOT NULL DEFAULT '0',
`article_print_count` int(11) NOT NULL DEFAULT '0',
`article_comments_allow` enum('yes','no') NOT NULL DEFAULT 'yes',
`article_ratings_allow` enum('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`article_id`),
FULLTEXT KEY `article_title` (`article_title`,`article_content`)
) ENGINE=MyISAM AUTO_INCREMENT=2872 DEFAULT CHARSET=utf8

 CREATE TABLE `phpkb_relations` (
 `relation_id` int(11) NOT NULL AUTO_INCREMENT,
 `article_id` int(11) NOT NULL,
 `category_id` int(11) NOT NULL,
 PRIMARY KEY (`relation_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=21960 DEFAULT CHARSET=latin1

CREATE TABLE `phpkb_article_visits` (
`visit_id` int(11) NOT NULL AUTO_INCREMENT,
`article_id` int(11) NOT NULL,
`article_hits` int(11) NOT NULL,
`visit_date` date NOT NULL,
PRIMARY KEY (`visit_id`)

Indexes !

Use InnoDB, not MyISAM.
Reformulate the query so it does not 'explode' (LEFT JOIN), then 'implode' (GROUP BY)...

Something like:

SELECT  a.article_id AS article_id, article_title, article_content,
        article_status, article_date_time,
      ( SELECT  SUM(article_hits)
            FROM  phpkb_article_visits
            WHERE  article_id = a.article_id
      ) AS article_hits
    FROM  phpkb_articles AS a JOIN phpkb_relations AS r  ON r.article_id = a.article_id
    WHERE  article_show='yes'
      AND  article_status IN ('featured','approved')
      AND  article_expiry_date >= now()
      AND  category_id=221
    GROUP BY  a.article_id
    ORDER BY  FIELD(article_status,'featured','approved'),
              article_title ASC;

Please qualify each field with the table that it belongs to.  (I am depending on all the unqualified article_* fields belonging to article_hits.)

4.

Indexes needed:

phpkb_article_visits:  (article_id, article_hits)
article_hits:  (category_id, article_show, article_expiry_date)
phpkb_relations:  (article_id)

Notice how I simplified the test for article_expiry_date.


MySQL Query One Table/Column With Value While Another Table/Column Empty I set up a test database named ‘test’ that contains 2 tables, tbl1 and tbl2.  In plain language, I’m trying to return the name of all entries in the package2 column of tbl2 that do not exist in the package1 column of tbl1.

I’ve tried all different sorts of queries with no success as shown below.  How can I accomplish this?

Returns nothing:

SELECT *
FROM tbl1,tbl2
WHERE NOT tbl1.package1 != tbl2.package2
OR (tbl1.package1 IS NULL AND tbl1.package1 IS NOT NULL)

Returns nothing:

SELECT *
FROM tbl1,tbl2
WHERE (tbl1.package1 IS NULL AND tbl2.package2 IS NOT NULL)

Database setup

CREATE TABLE IF NOT EXISTS `tbl1` (
  `host` varchar(10) NOT NULL,
  `package1` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `tbl2` (
  `packageid` tinyint(10) NOT NULL,
  `package2` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `tbl2` (`packageid`, `package2`) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c'),
(4, 'd');

-- Indexes for table `tbl1`
ALTER TABLE `tbl1`
  ADD PRIMARY KEY (`host`,`package1`) USING BTREE;

-- Indexes for table `tbl2`
ALTER TABLE `tbl2`
  ADD PRIMARY KEY (`packageid`);

ALTER TABLE `tbl2`
  MODIFY `packageid` tinyint(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

What you need is a LEFT JOIN

SELECT tbl2.* FROM
tbl2 LEFT JOIN tbl1
ON tbl2.package2 = tbl1.package1
WHERE tbl1.package1 IS NULL;

Note how I specify tbl1.package1 IS NULL checking the condition of the JOIN, not the data.


Where EVENTS history are logged/recorded in MySql? I just setup some stored procedures to run in the event scheduler using CREATE EVENT.

I'm trying to find where (if somewhere) the history of it running is stored. I looked at the docs but couldn't find anything.

Is there some table or log where I can see that my scheduled events successfully ran?

There is no log for scheduled events, unless they throw exceptions... in which case, you'll find an error written to the standard MySQL "hostname.err" error log.  If there isn't an error, and they aren't still running, then they processed.  Each event gets a new thread id in the processlist each time it fires and the thread is destroyed when the event is done.

One option you could use is creating a table for the purpose, and inserting log entries into that table from within the body of the event, or the procedure that the event calls.

Inside a running event, the CONNECTION_ID() function returns the id of the thread where the event is running, and you can use this function in queries that do your logging, if that's useful, such as inserting a log entry including that value when the event starts, and updating that log entry when the event is almost done.

Another option is a bit of a hack (some would say), but it also works -- you can generate a "warning" in the MySQL error log from within the event or procedure fired by the event by raising a SQL warning at the end of the event.  Note that this only writes to the error log from within an event, and that's because there is no client connected.

SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'hey, this job ran';

On some of my systems where the MySQL error log is monitored by an external process "tailing" the error log file, I have scheduled events whose only purpose is to write a warning like this to the error file periodically, so that the external process never goes more than so many minutes without seeing "something" in the often-quiet error log, to keep it happy that it is properly seeing the correct log file growing.


SQL Server: index creation date In SQL Server 2005 and above, how can I find when an index was created?

My answer is a direct quote of this link:

http://www.sqlservercentral.com/Forums/Topic960549-391-1.aspx#bm960643

There is no direct way of finding the creation date of an index. But however if you look in sysobjects there is a creation date for Primary Key or Unique Constraints.Indexes associated with primary Primary Key or Unique Constraints creation date can be known.

check if there are any new DMVs (Dynamic Mgmt Views) in sql server 2008 which can help you in getting creationDate.

try this query

select crdate, i.name, object_name(o.id)
from sysindexes i
   join sysobjects o ON o.id = i.id
order by crdate desc


How to pass an array to a plpgsql function with VARIADIC parameter I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.

I have already defined the function

CREATE OR REPLACE FUNCTION mix_table_fields(input_table_name VARCHAR, output_table_name VARCHAR, VARIADIC field_names VARCHAR[])
[...]

Now, when I try to call it:

SELECT mix_table_fields('articles'::VARCHAR, 'output_random_articles'::VARCHAR, array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]) ;

I get the following error : " the function mix_table_fields(character varying, character varying, character varying[]) does not exist".

I have had this error before. It cannot recognize the signature of the function (its input parameters).

When I use pgAdmin3 to look at the function definition, it is:

CREATE OR REPLACE FUNCTION public.mix_table_fields(
    IN input_table_name character varying,
    IN output_table_name character varying,
    VARIADIC field_names character varying[])

I cannot see what's wrong.

And if there is a way of getting rid of the ::VARCHAR at each parameter when calling the function, it would be easier.

To pass an actual array for a VARIADIC parameter, you must use the keyword VARIADIC in the call:

SELECT mix_table_fields('art'::VARCHAR
                      , 'out'::VARCHAR
                      , VARIADIC array['type'::varchar,'colour'::varchar,'size'::varchar,'price'::varchar]);

Even works without explicit type casts in your case. Function type resolution can find the best candidate for untyped string literals without ambiguity - as long as the function is not overloaded:

SELECT mix_table_fields('art', 'out', VARIADIC array['type','colour','size','price']);

Or with an array literal:

SELECT mix_table_fields('art', 'out', VARIADIC '{type,colour,size,price}');

Without the keyword VARIADIC, Postgres expects a list of element values like:

SELECT mix_table_fields('art', 'out', 'type', 'colour', 'size','price');

... which is the whole point of a VARIADIC function parameter: to be able to call it with a variable number of parameters of the element type, so you don't need the array wrapper. (Also the reason why a VARIADIC parameter must come last in the parameter list: else the call could be ambiguous.) If you don't need this feature, use a plain varchar[] parameter without VARIADIC in the function definition to begin with.

Related:

How to use an array as argument to a VARIADIC function in PostgreSQL?
Make query depend on input in PostgreSQL function


A many-to-many relationship I'm designing a simple database and have a question regarding a relationship. So far, I have users, books, highlights and bookmarks. The relationship rules are as follows:

Each user may 'subscribe' to a book.
Each book may have many users 'subscribed' to it.
Each user can have highlights and bookmarks for the book(s) he has 'subscribed' to.

The schema I have so far:

I'm having a problem with the last relation. What I have thought about so far was to use a single primary key instead of 2 keys acting as a composite key for the table book_users and then referencing that as a foreign key in the highlights and bookmarks table.

Another method that my friend recommended was to add another field called additions to the table book_users with a field of TEXT and storing the information in JSON format. The latter seems cleaner and I can't forsee any problems but I just want to confirm if this method is alright to implement.

I was wondering if there is a more efficient way to do this?

Thanks for your time.

I think your first design is better with two additional table highlights and bookmarks.

In long run it will be difficult with design to save data in JSON format, Let say you need to search a bookmark or search a highlights, I am thinking what would be sql query for that. or Let say for page you need to show the highlights, How you will show that.

Even if somehow you solve above problem, performance issue will be a concern. The first design offers indexes and even with long run big tables data can fetched quickly.

Check the datatype of id column in table books_users, it would be INT,


Time out while connecting to the database. Current time out value is 30 seconds I am installing a repository creation utility on a windows 8 x64 bit system and getting the following error:

  Time out while connecting to the database. Current time out value is 30 seconds

When I connect via sqdeveloper, it works fine.
My database connection details are:

- Database type: Oracle Database
- Host name: Bonge
- Port: 1521
- service name: XE
- Username: sys
- Password: XXXXX
- Role: Sysdba

Can anyone help me solve this problem?

Better use TNSNAMES.ORA file in your client. You can set Instant Client's timeout in SQLNET.ORA file

%ORACLE_HOME%\admin\network\

ORACLE_HOME is where your Instant Client binaries are and should be already added to your local environment variables.

If you don't know what Instant Client is, it is a binary dll that provides connection, execution and fetching capabilities over network. sqldeveloper has a lite one inside it's "installation" (read unzipped) folder, but you either have to reference to that manually, or install a proper one yourself. It can be downloaded from Oracle.com


How to JOIN with missing rows? I need to JOIN two tables to correct the price with exchange rates as

SELECT date, price/rate AS adjusted FROM
table1 a JOIN table2 b ON a.date=b.date

The problem is that table2 does not have the exchange rate for all dates.

How can I get a nearest exchange rate for missing dates in table2?

EXAMPLE

Table 1

date          price
2019-01-01    2.3
2019-01-03    3.4
2019-01-04    2.6
2019-01-05    2.2
2019-01-06    2.5
2019-01-07    5.1
2019-01-08    4.6
2019-01-09    4.2
2019-01-10    3.3

Table 2

date          rate
2019-01-04    1.1
2019-01-06    1.3
2019-01-09    1.0

Alternatively, an approach to fill the missing dates in table2 works too.

To perform the join without matching rows on the dates you need to use an outer join. The challenge here is going to be propagating down the nearest rates as you are missing various rates in table2 both ordered ascending and descending. I don't have much experience with MySQL so I'm not sure if Window Functions are available? I believe this will help you accomplish your goal.

ISNULL(ISNULL(rate, (SELECT TOP (1) rate FROM table2 AS t2
WHERE a.date <= t2.date)),
(SELECT TOP (1) rate FROM table2 AS t2
 WHERE a.date >= t2.date
 ORDER BY t2.date DESC))

SELECT a.[date],
       b.[date],
       a. price,
       b.rate,
       price/rate AS adjusted,
       ISNULL(ISNULL(rate,
(SELECT TOP (1) rate FROM table2 AS t2
 WHERE a.date <= t2.date)),
(SELECT TOP (1) rate FROM table2 AS t2
 WHERE a.date >= t2.date ORDER BY t2.date DESC))
FROM table1 AS a
LEFT OUTER JOIN table2 AS b
    ON a.[date] = b.[date];


DateTime returning with AM/PM I have a SQL Server database (2008R2) running on a Windows 2003 box.  I'm connecting to it using another Win2003 box.

When I select a datetime value from the database, I'm getting it back in this format:

  11/5/2014 12:07:46 PM

However, when I'm using a different system (Win2008 connecting to a 2008R2 database), I'm getting it back in a more expected format:

  2014-11-02 10:53:00

In both of these cases, I'm pulling the values through a Microsoft OLE DB driver directly from the database tables. The SELECT statements are not converting these values in any way.  This is being pulled from some C++ code and logged to a file.  When I connect using SSMS, the values are always in a more expected format (no AM/PM).

My Question:
Why am I getting two separate formats from the same version of SQL Server?  Is this an OLE DB Driver setting somewhere?

Your regional settings control the way dates are displayed. Try changing settings there.


Mixed SQL Server instances with static and dynamic ports Let's assume someone has 5 different SQL Server instances running on a server. Initially all are using dynamic ports.

Is it safe to restrict one of those instances to use the static port 1433 while all others still use dynamic ports?

Normally, depending on startup order it could happen that one dynamic instance claims port 1433, so that the fixed port instance can't grab it later on. Would this possibly prevent the fixed port instance to run?

Or is there any internal mechanism in SQL Server that starts fixed port instances first, and dynamic port ones afterwards?

When a SQL Server named instance configured with dynamic port is started, the dynamic port is provided by windows. The dynamic port range is outside the range of well-known static ports, where port 1433 resides. The dynamic port range is between 49152 and 65535, as documented here.

At subsequent startups, the instances reuse the same dynamic port used during previous startups (the port is written in the registry). If the port is found to be in use, SQL Server asks the OS for a new port. This behaviour is documented in this page, in the "Dynamic Port Allocation" section.

To answer your question in a single sentence: the scenario you describe is impossible (in a supported version of Windows with ranges properly configured).


What is the format of raw geometry column in PostGIS? It doesn't seem to be a WKB, since it's basically a string, so what is it?

It is the hexadecimal rendition (two characters per byte) of the EWKB format.
Per the documentation:

The "canonical forms" of a PostgreSQL type are the representations you get with a simple query (without any function call) and the one which is guaranteed to be accepted with a simple insert, update or copy. For the PostGIS 'geometry' type these are:
- Output
  - binary: EWKB
        ascii: HEXEWKB (EWKB in hex form)
- Input
  - binary: EWKB
        ascii: HEXEWKB|EWKT

For example this statement reads EWKT and returns HEXEWKB in the process of canonical ascii input/output:
=# SELECT 'SRID=4;POINT(0 0)'::geometry;

geometry
----------------------------------------------------
01010000200400000000000000000000000000000000000000
(1 row)


SQL Query taking long to execute especially with offset and fetch for pagination This is the query I'm running for a report. It takes about 20 seconds to run without the offset and fetch. With those two, The query doesn't even complete in 10 minutes.

select *
-- h.ActionDate as Date, h.Action, h.Actor , h.PaymentLotNo
-- , sh.OwnerName as OwnersName,sh.TECHID as TechId, sh.Id as SubsidyCardId, sh.TotalSubsidy as SubsidyAmount
-- ,a.ToleEng + '-' + v.NameEng + cast(a.Ward as nvarchar(4)) + ',' + d.NameEng + ',' + s.StateNameEng as Address
from common.CTB_SUBSIDY_APPLICATION_HISTORY h
join fsams.CTB_SUBSIDY_CARD_HEADER sh on sh.LotNumber = h.PaymentLotNo
join fsams.CTB_SUBSIDY_CARD_ADDRESS sa on sa.SubsidyId = sh.Id
join common.CTB_ADDRESS a on a.AddressID = sa.AddressId
join COMMON.CLK_STATE s on s.StateId = a.StateID
join COMMON.CLK_DISTRICTS d on d.DistrictCd = a.DistrictCD
join common.CLK_DISTRICT_VDC v on v.VdcCd = a.VdcCd where 1 = 1 order by h.ActionDate desc
offset 790 rows
FETCH first 10 row only

The following is the execution plan for the query.

How do I optimize this query?

Using OFFSET...FETCH likely introduces a row goal (in the same way that TOP does so - see Setting and Identifying Row Goals in Execution Plans by Paul White for more information on that subject).  This is why you see such a difference without that part of the query - the lack of a row goal changes the plan entirely.

The row goal leads to SQL Server choosing the series of nested loops joins in the execution plan you shared.  This would normally be fine, but my guess it that the joins are more restrictive than the estimates indicate, and thus it takes a lot longer than expected to get the 10 rows requested.

You can test this theory by adding OPTION (QUERYTRACEON 4138) to the end of the query and running it again.

As far as a proper solution - as Dan Guzman suggested, you could try creating a narrow index on fsams.CTB_SUBSIDY_CARD_HEADER:

CREATE NONCLUSTERED INDEX IX_PaymentLotNo
ON FSAMS.CTB_SUBSIDY_CARD_HEADER (PaymentLotNo)
INCLUDE (OwnerName, TECHID, Id, TotalSubsidy)

Also, don't use SELECT * for your performance-related testing, as it's definitely going to change the final plan that you get (as compared with the column list of the actual query).

There is an implicit conversion going on that could be throwing the query's estimates off - LotNumber is stored as a string in the CTB_SUBSIDY_CARD_HEADER table, which is being converted to an int before being joined to PaymentLotNo in CTB_SUBSIDY_APPLICATION_HISTORY.  You should ALTER these columns to have matching data types if possible.

Side note: you're running SQL Server 2014 RTM, which is out of support.  You should upgrade to the latest service pack.  Also, consider enabling TF 4199 at that time, as the issue you're experiencing could be related to an optimizer bug that has since been corrected.


Mysql: Tell InnoDB to keep a specific table or database out of pool/cache? I have some tables that are used primarily to keep historical data, or that are only used once a day.

I do not want any operations on these tables to disrupt the more important tables present in the InnoDB buffer pool that get queried multiple times per second.

Should I just let the engine just do its thing and trust that it decides which tables are most important?
Is there a way to "pin" certain tables to the InnoDB cache?
Am I wasting my time?

Any SELECT, INSERT, UPDATE, or DELETE performed against InnoDB tables will do all the following:

Add new pages to the InnoDB Buffer Pool
Generate dirty pages (via UPDATE) within the InnoDB Buffer Pool
Remove old pages from the InnoDB Buffer Pool that you may still need

You cannot dictate what tables will and will not affect the InnoDB Buffer Pool. However, you can stagnate the rotation of Buffer Pool pages at MySQL startup with these two options:

innodb_old_blocks_pct

  Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool).

innodb_old_blocks_time

  Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: A block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many ms after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.

For further information, please see The InnoDB Buffer Pool section of MySQL Documentation

If you are not sure of the effects, it is best to leave it as is. Perhaps more server memory and a bigger InnoDB Buffer Pool may be the answer. Tweaking these settings may be overkill if you could just upgrade RAM.


I want to select all records for those persons who have one or more entries that equals zero dues DUES_TABLE
ID       LNAME   FNAME   DUES    DATE
3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01

9450     Jones   Jane    12.50   2014-10-01
9450     Jones   Jane    12.50   2014-11-01
9450     Jones   Jane    12.50   2014-12-01

6074     Suggs   Tom     12.50   2014-10-01
6074     Suggs   Tom      0.00   2014-11-01
6074     Suggs   Tom     12.50   2014-12-01

I want to output all records, whether dues were paid or not, for those persons who have one or more zero dues entries, like so:

3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01
9450     Suggs   Tom     12.50   2014-10-01
9450     Suggs   Tom      0.00   2014-11-01
9450     Suggs   Tom     12.50   2014-12-01

Notice that Jane Jones is excluded because she paid all her dues. And Both Bob Smith and Tom Suggs have their zero and positive dues entries listed.

Anyone have an idea how I can achieve this result?

SELECT DISTINCT ID FROM tbl WHERE DUES = 0

will get the IDs of the naughty people, correct?  So now you need to do a "self-JOIN" back to get the rest of the data:

SELECT DISTINCT a.*
    FROM tbl AS a
    JOIN tbl AS b  USING(ID)
    WHERE b.DUES = 0
    ORDER BY a.ID, a.DATE

Or...

SELECT *
    FROM tbl AS a
    WHERE EXISTS ( SELECT ID FROM tbl
                      WHERE DUES = 0
                      AND ID = a.ID
                 )
    ORDER BY ID, DATE


When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index? In our database a big table exists that more or less looks like this:

CREATE TABLE dbo.production_data
(
    pd_id BIGINT PRIMARY KEY,
    serial NVARCHAR(16) NOT NULL UNIQUE,
    ...
);

but now the size of the serial field has become to low, so I want to change it to 32.
The Visual Studio schema compare tool suggests doing this by:

DROP INDEX ux_production_data_serial ON dbo.production_data;
GO
ALTER TABLE dbo.production_data ALTER COLUMN serial NVARCHAR(32) NOT NULL;
GO
CREATE INDEX ux_production_data_serial ON dbo.production_data(serial ASC);

Is this really needed? Or more like a ultra save way of doing this?

Also when recreating the unique index, will my table get locked? Because this would be a big problem (as the table has 30 million rows and i guess recreating the index will take quite some time), because the next maintenance window is a few month in the future.
What are my alternatives?

There is no need to drop and recreate the index.

Just use

ALTER TABLE dbo.production_data
  ALTER COLUMN serial NVARCHAR(32) NOT NULL;

This is a metadata only change.

Altering a column from NVARCHAR(16) to NVARCHAR(32) does not affect the storage at all.

Going the other way round (from  NVARCHAR(32) to  NVARCHAR(16)) would give you an error about objects being dependent on the column though so maybe Visual Studio just always generates that boiler plate code in lieu of checking whether it is actually required.


Oracle hierarchical query question (start with ... connect by ... ) From the Oracle documentation:

  To find the children of a parent row, Oracle evaluates the PRIOR
  expression of the CONNECT BY condition for the parent row and the
  other expression for each row in the table. Rows for which the
  condition is true are the children of the parent. The CONNECT BY
  condition can contain other conditions to further filter the rows
  selected by the query.

When Oracle select child node for a parent node, it selects from all rows except the current row right? Can a row appear multi times in the result set (I mean a row is both the child node of row A and row B)?

There would have to be data indicating that the row is both a child of row A and a child of row B.  This can be done, but does not seem like it would be typical.  Think of an employee.  Most employees only have one direct managers.  In a simple design an employee could have two managers, but it would require multiple employee records.  For example:

--drop table e;
create table e (id number, name varchar2(100), manager number);
insert into e values (1,'Amos',null);
insert into e values (2,'Bob',1);
insert into e values (3,'Cindy',1);
insert into e values (4,'Dave',2);
insert into e values (4,'Dave',3);
select substr(name,1,7) name, substr(prior name,1,7) Manager
   from e start with id=1 connect by prior id=manager order by name;


Sql Server 2012 Restoring Master and Migrating a server I am migrating an entire SQL Server instance with 10 databases on it.

It has many jobs and users. What is best practice regarding the master database? Should I backup and restore this too, or script the users and jobs?

Also, is there another better way to migrate an entire SQL Server instance with all of its databases?

I dont see a need (nor I do it when migrating server) to do a restore of master (or system databases) on the destination server - unless you are trying to achieve something that you have not mentioned in your question.

I find it easy to use sp_help_revlogin to just move logins from one server to another.

so your migration steps would be like below: Server A = Old server ; Server B is NEW server.

script out logins from server A.
script out jobs from server A.
Backup and restore databases from Server A to Server B.
Recreate logins scripted out from server A to Server B.
Sync up orphan users (if any).
Recreate jobs scripted out from server A to server B. Make sure to move packages if any as well.
change compatibility level (if moving from lower to higher version).
run DBCC UPDATEUSAGE
update stats on all tables with fullscan
Run DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY

You can refer to my script at https://dba.stackexchange.com/a/41179/8783 (make sure you do a backup restore and not detach/attach method - now learned it !!).


Describe Interim Table and Basic Concept behind it? Hy Everyone...
Can anyone describe me what is interim table in sql Server 2008? what is the basic concept and structure behind it.
thanks in advance.

Interim table is a table that is generated by joining two tables and not the final result table. In other words, when two tables are joined they create an interim table as resultset but the resultset is not final yet.

Check this link for examples


Oracle concatenation of queries and function index usage I have a problem with something I can't understand about indexes in Oracle 11g.

We can create test data with:

create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 varchar2(100));

create index test2_idx1 on test2(upper(field1));

create index test2_idx1b on test2(field1);

create index test2_idx2 on test2(field3);

DECLARE
  j NUMBER :=1;
BEGIN
  FOR i IN 1..500000
  LOOP
    INSERT
    INTO test2
    (field1,field2, field3, field4)
    VALUES
    ('field1='||i,'a', j, 'i' );

    IF (i mod 1000)=0 THEN
       j := j+1;
    END IF;
  END LOOP;
  COMMIT;
END;

EXEC DBMS_STATS.GATHER_TABLE_STATS ('system', 'test2');

Then I make some explain plans which result I can't understand

Query 1:

SELECT * FROM test2 WHERE field3=1;

Explain plan:

Everything OK here. Index is used.

Query 2:

SELECT * FROM test2 WHERE upper(field1)='FIELD1=1';

Explain plan

Everything OK again. Index is used.

Query 3:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE field1='FIELD1=1' OR field3=1;

Explain plan

Indenxes are used in concatenation. No problem again.

Query 4:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1;

Explain plan

Here my problem arises. Why is test2_idx1 not being used? Is it because it is a function index? Is there any workaround in this cases?

Thanks a lot in advance.

This seems to be a limitation of the optimizer (or perhaps a bug).

The plan for this query:

SELECT /*+ USE_CONCAT */ * FROM test2 WHERE upper(field1)='FIELD1=1' OR field3=1
union all
(
SELECT * FROM test2 WHERE upper(field1)='FIELD1=1'
union all
SELECT * FROM test2 WHERE field3=1
);

shows that it is not the fault of bad stats, and also demonstrates a workaround using union all (but note you need something like upper(field1)='FIELD1=1 and (field3 is null or field3<>1) for an exactly equivalent query)

(My test was on 10.2)


Replicate_Do_DB and Replicate_Wild_Do_Table - Replication does not work I have been tasked to set replication up on a slave host. The master database is a "data store" where tables are dropped, recreated, and reloaded on a daily basis.
My initial setup of the slave host worked fine by replication was always days behind the master. After talking to the users of the "data store", I realized that not all of the databases and not all of the tables need to be replicated over. So this is what I did
CI-DB002-PRD [root@localhost] ON (none)>  STOP SLAVE\G

CI-DB002-PRD [root@localhost] ON (none)> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (bidw,cf2_fact,ct_fact,ez_fact,gt_fact,sfdc,soa_fact,tesla_fact,tmc_fact);

CI-DB002-PRD [root@localhost] ON (none)> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('bidw.domo%', 'bidw.consolidated%', 'bidw.cf2%', 'bidw.tesla%');

CI-DB002-PRD [root@localhost] ON (none)>  START SLAVE\G

Very quickly, replication caught up and now, the slave is 3-7 seconds behind the master.
But replication is not touching the databases I want to be replicated. Even though data changes on master every 10 minutes for the schemas I list above. I did several validation queries and results now differ. When I check the status of my slave, nothing stands out
CI-DB002-PRD [root@localhost] ON (none)> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 10.239.0.34
                  Master_User: ci02replicadb
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binary-log.009871
          Read_Master_Log_Pos: 14678596
               Relay_Log_File: ci-db002-prd-relay-bin.007914
                Relay_Log_Pos: 814824
        Relay_Master_Log_File: binary-log.009871
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: bidw,cf2_fact,ct_fact,ez_fact,gt_fact,sfdc,soa_fact,tesla_fact,tmc_fact,staging,phoenix,data_science
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: bidw.domo%,bidw.consolidated%,bidw.cf2%,bidw.tesla%,bidw.ez%,bidw.ct%,bidw.gt%,bidw.tmc%,bidw.did%,bidw.Shortened%,bidw.other_revenue%,bidw.revenue%,bidw.advanced_cohort%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 814626
              Relay_Log_Space: 14679056
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 22
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a485ab14-aa57-11ea-bef5-42010aef0022
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.01 sec)

For "Replicate_Wild_Do_Table", I replicate only the tables listed there because the entire "bidw" database is lousy with junk tables.
What am I missing here? Am I not allowed to use both filters at the same time? I am on version 5.7.31.

Here is an old blog from my company : Best Practice For Setting Up MySQL Replication Filters.
It says in part:

MySQL provides 3 levels of filters for setting up replication: Binary log, DB and Table. The binlog filters apply on the master to control how to log the changes. Since MySQL replication is based on the binlog, it is the first level filter and has the highest priority. While the DB-level and Table-level filters apply on the slaves, since each table belongs to a schema, the DB-level filters have higher priority than the Table-level ones. Inside the Table-level filters, MySQL will evaluate the options in the order of: –replicate-do-table, –replicate-ignore-table ,  –replicate-wild-do-table , –replicate-wild-ignore-table.

Given this statement and looking at your SHOW SLAVE STATUS\G, I would do one of the following:

Remove bidw from the Replicate_Do_DB list (at the very least)
Remove Replicate_Wild_Do_Table and migrate away all junk tables
Remove Replicate_Do_DB list altogether (last resort)

UPDATE 2020-08-12 10:54 EDT
To further clarify, Replicate_Do_DB is allowing everything and its grandmother from the midw database to replicate and the Replicate_Wild_Do_Table is actually ignore as a result.


How to Duplicate Column in same table in SQL I'm pretty basic with SQL and need help with the following task:
I already have a table with data in it. What I would like to do is to Duplicate a Column (Including the data) in the same table but with a different column name. Is this possible?
Any help would be greatly appreciated.

Depending on other requirements, you might be able to get away with using a computed column. This assumes you don't need to manipulate the data in the duplicated column.
ALTER TABLE dbo.t
    ADD NewColumn AS OldColumn;


How can I get top users in specific tags? I have a function like this:

CREATE DEFINER=`root`@`localhost` FUNCTION `user_top_tags`(`user_id` INT, `tags_num` TINYINT(1)) RETURNS varchar(50) CHARSET utf8mb4
    NO SQL
BEGIN
    DECLARE  top_tags varchar(50);

    SELECT substring_index(group_concat(x.name  ORDER BY x.tag_score DESC SEPARATOR ','), ',', tags_num) INTO top_tags
    FROM (
        SELECT t.name, sum(r.score) AS tag_score
        FROM reputations r
        JOIN qanda_tags qt ON qt.qanda_id = r.question_id
        JOIN tags t ON t.id = qt.tag_id
        WHERE r.owner_id = user_id
        GROUP BY t.name
    ) x;

    RETURN top_tags;
END

And I call it like this:

SELECT u.name, user_top_tags(u.id, 3) FROM users u WHERE 1;

And it returns a list of users with their top three tags. Something like this:

+--------+-----------------+
| Jack   | php,oop,mysql   |
| Martin | css,js,html     |
| Peter  | jquery,js,react |
+--------+-----------------+

Now I want to get the users which has active in specific tags. Something like top users page in SO (which is for javascript tag, but I want to get list of users in multiple tags, like IN ('css','html')).

Now should I do a join on the query? Or should I modify the function? Does anybody have any idea how can I do that?

Let's assume these are the settings for the problem. These will be the tables:

CREATE TABLE users
(
    user_id INTEGER NOT NULL PRIMARY KEY
) ;

CREATE TABLE tags
(
    id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR(100)
) ;

CREATE TABLE qanda_tags
(
    question_id INTEGER,
    tag_id INTEGER NOT NULL REFERENCES tags(id)
) ;

CREATE TABLE reputations
(
    owner_id INTEGER NOT NULL REFERENCES users(user_id),
    question_id INTEGER,
    score INTEGER NOT NULL DEFAULT 0
) ;

And these are the values stored on the different tables:

INSERT INTO users (user_id)
VALUES (1), (2) ;

INSERT INTO tags
    (id, name)
VALUES
    (1, 'MySQL'),
    (2, 'PostgreSQL'),
    (3, 'MS SQL Server'),
    (4, 'Informix'),
    (5, 'MongoDB'),
    (6, 'CouchDB'),
    (7, 'Oracle'),
    (8, 'DB2'),
    (9, 'Sybase') ;

INSERT INTO qanda_tags
    (question_id, tag_id)
VALUES
    (1, 1),
    (1, 2),
    (2, 4),
    (2, 5),
    (3, 1),
    (3, 2),
    (3, 4);

INSERT INTO reputations
    (owner_id, question_id, score)
VALUES
    (1, 1, 3),
    (2, 2, 5),
    (1, 3, 8) ;

At this point, we could run this query...

SELECT
    *
FROM
    reputations r
    JOIN qanda_tags qt
        ON qt.question_id = r.question_id
    JOIN tags t
        ON t.id = qt.tag_id ;

and get

owner_id | question_id | score | question_id | tag_id | id | name
-------: | ----------: | ----: | ----------: | -----: | -: | :---------
       1 |           1 |     3 |           1 |      1 |  1 | MySQL
       1 |           1 |     3 |           1 |      2 |  2 | PostgreSQL
       2 |           2 |     5 |           2 |      4 |  4 | Informix
       2 |           2 |     5 |           2 |      5 |  5 | MongoDB
       1 |           3 |     8 |           3 |      1 |  1 | MySQL
       1 |           3 |     8 |           3 |      2 |  2 | PostgreSQL
       1 |           3 |     8 |           3 |      4 |  4 | Informix

Your original function (slightly modified so it is no actually a function and can be played on at dbfiddle, and with some extra info), would do the following:

(these would be your function parameters)

SET @user_id = 1 ;
SET @tags_num = 4 ;

SELECT
    substring_index(
        group_concat(name_and_score ORDER BY x.tag_score DESC, x.name ASC SEPARATOR ', ')
        , ', '
        , @tags_num
    ) AS top_tags_from_user
FROM
   (SELECT
        t.name,
        sum(r.score) AS tag_score,
        concat(t.name, ' (', sum(r.score), ')') AS name_and_score
    FROM
        reputations r
        JOIN qanda_tags qt
            ON qt.question_id = r.question_id
        JOIN tags t
            ON t.id = qt.tag_id
    WHERE
        r.owner_id = @user_id
    GROUP BY
        t.name
    ) AS x ;

| top_tags_from_user                        |
| :---------------------------------------- |
| MySQL (11), PostgreSQL (11), Informix (8) |

If you want to limit the tags that are checked, you can change the previous code just slightly:

SET @user_id = 1 ;
SET @tags_num = 4 ;
SET @tags_list = 'MySQL,Informix' ;

SELECT
    substring_index(
        group_concat(name_and_score ORDER BY x.tag_score DESC, x.name ASC SEPARATOR ', ')
        , ', '
        , @tags_num
    ) AS top_tags_from_user
FROM
   (SELECT
        t.name,
        sum(r.score) AS tag_score,
        concat(t.name, ' (', sum(r.score), ')') AS name_and_score
    FROM
        reputations r
        JOIN qanda_tags qt
            ON qt.question_id = r.question_id
        JOIN tags t
            ON t.id = qt.tag_id
               /* This is just what you need to add */
               AND (@tags_list IS NULL OR find_in_set(t.name, @tags_list))
    WHERE
        r.owner_id = @user_id
    GROUP BY
        t.name
    ) AS x ;

That will now return:

| top_tags_from_user       |
| :----------------------- |
| MySQL (11), Informix (8) |

The previous code will also work and choose all tags if you set @tags_list to NULL. In that case, the @tags_list IS NULL is true, and the JOIN condition is equivalent to the original case.

You can check everything at dbfiddle here


Restoring the superuser account on the postgres server I have a complete curious situation with a postgres dbms. I have installed the database and due to a misconfiguration of my installation scripts I made the superuser role to be a regular user. Now I have a completeley fresh installation of postgres with only the template database and a single user 'postgres' that can do pretty much nothing. How can I make postgres user superuser again?

I tried to reinstall the server under ubuntu 12.04 with aptitude reinstall postgresql-9.1 but the role seems to be unaffected. Most probably because the reinstallation does not affect the files where the roles are recorded.

Superuser rights aren't in any config file, they're part of the pg_catalog.pg_authid database table, which is shared between all databases in a PostgreSQL install.

You  need to stop the PostgreSQL server, then restart it in single user mode, where it's always running as superuser.

There, you can ALTER USER myuser SUPERUSER to grant superuser rights. Exit single user mode, and start the server back up.

The exact details are a little distro and version dependent, but something like:

service postgresql stop
sudo -u postgres postgres --single /var/lib/pgsql/9.1/main/data

ALTER USER postgres SUPERUSER;

(control-D to exit)

service postgresql start

On some systems you might need to use an explicit path to the postgres executable, like /usr/pgsql-9.1/bin/postgres. Again, paths may vary based on version and distro.


What causes 'Unknown token received from SQL Server'? I have a SQL Server 2005 box with a classic ASP front end (IIS6) calling a stored procedure that uses dynamic SQL. It works except for a single day of data that somehow throws this error even though it runs fine in SSMS.

Since it works for other date ranges and works within SSMS (with this date range), I'm guessing there must be something in the dataset that's triggering the error. However, calls to it using the date range plus filters that result in no data returned still triggers this error, so I don't think it's related to what's being passed back to the IIS app.

Any suggestions on how I can track this down would be appreciated.

Thanks

The error message means client TDS* processing received a token it didn't expect.

This might be a bug, but if so, it is most likely an old one that has long been fixed. You should ensure that the client connectivity components (e.g. MDAC, SQL Server client) are patched up to date (as far as possible, given the old technology).

You should also check that your SQL Server 2005 is running at least Service Pack 4 (though 2005 is out of mainstream support now too).

*tabular data steam; SQL Server's communication protocol


Why store XML Schema in SQL Server 2012 database? What a real world production environment example for storing and or using a XML schema in a SQL Server database?

  I am working my way though the Microsoft 70-461 exam objectives. I have
  completed the Microsoft walk through for creating and storing an XML
  schema in the database at
  https://technet.microsoft.com/en-us/library/ms176009(v=sql.110).aspx .

  I have yet to work in a database environment where the application
  will access a XML schema that is stored in the database. What is a
  real world use case for creating and storing a XML schema in a
  production SQL Server database?

XML Schemas aren't stored in SQL Server so that an application can access them. The reasons for storing XML Schema Collections are found in how they improve working with the XML data.

According to the MSDN page for XML Schema Collections:

  SQL Server uses this type information to optimize data storage.

  The query-processing engine also uses the schema for type checking and to optimize queries and data modification.

  Also, SQL Server uses the associated XML schema collection, in the case of typed xml, to validate the XML instance. If the XML instance complies with the schema, the database allows the instance to be stored in the system with their type information. Otherwise, it rejects the instance.

And, according to the MSDN page for Compare Typed XML to Untyped XML:

  An XML schema provides the following:

  Validation constraints. Whenever a typed xml instance is assigned to or modified, SQL Server validates the instance.
  Data type information. Schemas provide information about the types of attributes and elements in the xml data type instance. The type information provides more precise operational semantics to the values contained in the instance than is possible with untyped xml. For example, decimal arithmetic operations can be performed on a decimal value, but not on a string value. Because of this, typed XML storage can be made significantly more compact than untyped XML.

So the reasons to use an XML Schema Collection are:

Increased functionality
Optimized storage (this one is "iffy": I tested with type="xsd:int" and it took up more space than the untyped, but perhaps other types such as datetime and/or float optimize better)
Increased performance
Validation (i.e. data integrity)

Below are two articles / posts related to the performance benefits of typed XML. There is mention of XML Indexes performing much better with typed XML than with untyped. But, there is also mention of typed XML being worse for DML operations.

Performance tips of using XML data in SQL Server
XML Schema Collections: the XML accelerator pedal


Create menu from multiple meals relationship I have the following relations:

Meals(meal_id, name, cuisine, ingredient)

Users(user_id, name, username, password, email)

Chefs(chef_id, name, username, password, email)

I want to create the following relation and schema: a relation that create menus from multiple meals for different users

Also, user can use menus that created by chefs.

I tried the menus(menu_id, name) and put menu_id attribute into Meals. it didn't work

You'll need a menus table as you've specified (albeit with a user_id field), so menus(menu_id, user_id, name)

I would then include a linker table to join a meal to a menu, seeing as a menu can have multiple meals and a meal can belong to multiple menus meal_menu(menu_id, meal_id)

EDIT

As Colin 't Hart suggests, for the second part of the question I would also have the chef and user share a common table (probably called users) as they're sharing fields. You could then have a roles table that links each user to a role (chef, diner etc.)

That way, there's only one link from any type of user to a menu.


Consolidate to multiple or single instance SQL Server on a VM with many third party vendor databases? I need to consolidate several older instances to SQL Server 2016 Enterprise on a single VM. Since all of the databases are third party vendor systems, they are a sprawling mess of SQL Agent jobs, custom roles, dynamically created users, and demands for doing installs/upgrades with SA access that I have to fight frequently.

I yearn for the isolation of multiple instances, but hear that they will create resource contention and other woes. In a VM environment where resources such as I/O would be shared across all VMs anyways and being able to set max memory per instance, should I still be avoiding multiple instances?

Breaking out to separate VMs is not an option for me due to licensing costs. Also, the scale I'm looking at is about 15 different vendor platforms, some of which I know could happily sit in the default instance together without the above-mentioned issues, so I would probably have a maximum of 5 instances if I went that route.

Obviously an opinion here, but...
Personally, I think separate instances is definitely the way to go.  We have several servers at my work like that.  You have so much more control of stuff, especially server related objects like linked servers, etc.  Being able to restart one instance and leave the others alone is a valuable benefit.  And you can upgrade each instance independently as the vendor allows