Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?

Why is the execution plan always showing the original underlying table instead of the indexed view, and always using the clustered index on the underlying table even when my select query on the indexed view uses an index hint to force the use of the non-clustered index?

Your view definition is being expanded. You'll need the NOEXPAND hint.

Another Reason to Use NOEXPAND hints in Enterprise Edition.
Indexed Views and Statistics

Can I use the NOEXPAND hint on an non-indexed view?

From the docs: The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.

Does schemabinding a view materialize it?

Being schema bound is an object property that can apply to things other than views, like functions. You can't index a view that isn't schema bound, though.
For an example with functions, check out this Q&A:

Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?


The only reliable way that I know of is to use auditing. If you just want to audit a single table:

AUDIT SELECT ON <tablename>;

but there are many other options. See the documentation at http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm#SQLRF53737 for full details.

NB You must set the parameter AUDIT_TRAIL to a value other than NONE to actually enable the auditing to take place.


You can get the result by joining your two tables on the username column, then you can use a WHERE clause to filter for the row with id = 3:

select d.id, d.username, u.email
from data d
inner join users u
  on d.username = u.username
where d.id = 3;

See SQL Fiddle with Demo


This is obviously nonsense. SELECT 1 is faster (if only by a tiny bit). version() also returns quite a few bytes of text, in my case:

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

So you get some additional I/O, too.
pgAdmin, for instance, uses SELECT 1 as validation query.


What is the purpose of the USING parameter in SQL?

In the context of your query, USING helps satisfy a JOIN so long as the two tables involved in the JOIN have the same column names to join with. It is like doing a NATURAL JOIN.

Your query

SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1

works the same as

SELECT * FROM foo LEFT JOIN bar ON foo.bar_id = bar.bar_id WHERE foo_id = 1

According to the MySQL Documentation on JOIN syntax

  The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

a LEFT JOIN b USING (c1,c2,c3)

  The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.


Script to merge-replicate two MDF database files?

Without an internet connection, you would be looking at merge or peer-to-peer replication. Neither of which can be implemented easily (google I seem to remember seeing a workaround somewhere) how you want with Express edition. If you had standard or better edition I would personally go with Peer-To-Peer as it negates the need for an additional column per table and like merge includes conflict resolution. Be advised though that in my opinion replication is a fairly finicky technology and requires intermediate DBA skills to implement and troubleshoot efficiently. Also be advised that once replication is implimented that no transaction can be cleared by a transaction log backup or checkpoint (depending on your recovery model) until it has been distributed to its subscriber. Depending on the duration and number of commands between syncs this will cause your transaction log to grow and potentially fill the disk. Obviously if the avilable disk space becomes full no new transactions can be written / updated in the DB.

With an internet connection I would definitely look at Azure to give you a single database which both devices can write to.

Obviously you can also look to pull and merge the data via powershell, SSIS, C# scripts or get a trial licence of RedGate data compare and kick off via a windows scheduled task (SQL Server agent is not available in express) and do the job which replication would do out of the box.

On a side note depending on your usage you might be able to get hold of developer edition SQL Server licence which is equal to enterprise.

Edit 1: peer to peer is an enterprise only feature in 2008r2 and 2012 at least, unlike merge which is available in standard


How can I select all of the parents of rows that exist in another table?

I think you need:

SELECT f.Id AS FileId, f.Name
FROM Filenodes AS f
WHERE EXISTS
      ( SELECT *
        FROM PersonFilenodes AS pf
        WHERE pf.PersonId = @personId
          AND ( pf.FileId = f.Id
             OR pf.FileId.IsDescendantOf(f.id) = 1
              )
      ) ;

The inner query is a correlated subquery that takes two parameters: @personId, which is an actual parameter, and f.Id, which is the Filenode Id of the current row from the outer query.


Default value for UUID column in Postgres

tl;dr

Call DEFAULT when defining a column to invoke one of the OSSP uuid functions. The Postgres server will automatically invoke the function every time a row is inserted.

CREATE TABLE tbl
(
  pkey UUID NOT NULL DEFAULT uuid_generate_v1() ,
  CONSTRAINT pkey_tbl PRIMARY KEY ( pkey )
)

If you already use the pgcrypto extension, consider the Answer by bpieck.

Plugin Required To Generate UUID

While Postgres out-of-the-box supports storing UUID (Universally Unique Identifier) values in their native 128-bit form, generating UUID values requires a plug-in. In Postgres, a plug-in is known as an extension.

To install an extension, call CREATE EXTENSION. To avoid re-installing, add IF NOT EXISTS. See my blog post for more details, or see this page in StackOverflow.

The extension we want is an open-source library built in C for working with UUIDs, OSSP uuid. A build of this library for Postgres is often bundled with an installation of Postgres such as the graphical installers provided by Enterprise DB or included by cloud providers such as Amazon RDS for PostgreSQL.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Generating Various Kinds Of UUID

See the extension’s doc to see a list of multiple commands offered for generating various kinds of UUID values. To get the original version of UUID built from the computer’s MAC address plus current date-time plus a small random value, call uuid_generate_v1().

SELECT uuid_generate_v1();

  672124b6-9894-11e5-be38-001d42e813fe

Later variations on this theme were developed for alternate kinds of UUIDs. Some people may not want to record the server’s actual MAC address, for example, for security or privacy concerns. The Postgres extension generates five kinds of UUIDs, plus the “nil” UUID 00000000-0000-0000-0000-000000000000.

UUID As Default Value

That method call can be made automatically to generate a default value for any newly inserted row. When defining the column, specify:

  DEFAULT uuid_generate_v1()

See that command used in the following example table definition.

CREATE TABLE public.pet_
(
  species_ text NOT NULL,
  name_ text NOT NULL,
  date_of_birth_ text NOT NULL,
  uuid_ uuid NOT NULL DEFAULT uuid_generate_v1(),  -- <====
  CONSTRAINT pet_pkey_ PRIMARY KEY (uuid_)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.pet_
  OWNER TO postgres;

UUID versions

The uuid-ossp plugin can generate various versions of UUID.

uuid_generate_v1()Contains MAC address of current computer + current moment. Commonly used, but avoid if you are sensitive about disclosing the MAC of your database server or the time when this value was generated. Defined by specification as a Version 1 UUID.
uuid_generate_v1mc()Like Version 1, but with a random multicast MAC address instead of the real MAC address. Apparently a way to use Version 1 but substituting another MAC rather than the actual MAC of your database server if you are sensitive about disclosing that fact.What is a ‘random multicast MAC’? I do not know exactly. After reading section 4.1.6 of RFC 4122, I suspect this is a random number used in place of the MAC but with bits set to indicate a multicast MAC address rather than the usual unicast so as to distinguish this variation of Version 1 from a usual real-MAC Version 1 UUID.
uuid_generate_v3( namespace uuid, name text )Contains an MD5 hash of text you provide. Defined by specification as a Version 3 UUID, namespace-based UUID.
uuid_generate_v4()Based on randomly-generated data for 121-122 of the 128 bits. Six or seven bits used to indicate Version & Variant. This kind of UUID is practical only if implemented with a cryptographically-strong random generator. Defined by specification as a Version 4 UUID.
uuid_generate_v5( namespace uuid, name text )Same as Version 3 but using SHA1 hashing. Defined by specification as Version 5 UUID.
uuid_nil()A special case, all bits set to zero 00000000-0000-0000-0000-000000000000. Used as a flag for an unknown UUID value. Known as a nil UUID.

To compare types, see Question, Which UUID version to use?

If you are interested only in Version 4 (randomly generated), and are already using pgcrypto, see the Answer by bpieck.

If you are curious about Versions 3 & 5, see this Question, Generating v5 UUID. What is name and namespace?.

For more discussion, see my Answer to a similar Question and my blog post UUID values from JDBC to Postgres.


MySQL: Return JSON from a standard SQL Query

Converting a row to json

It doesn't sound to me like you want to aggregate JSON. You state you want the equivalent of row_to_json, if so then I suggest checking out the much simpler JSON_OBJECT

SELECT JSON_OBJECT(
  'name_field', name_field,
  'address_field', address_field,
  'contact_age', contact_age
)
FROM contact;

Aggregating JSON

As a side note, if you do need to aggregate a resultset to json. then the upcoming MySQL 8 will do that for you.

JSON_ARRAYAGG()    Return result set as a single JSON array
JSON_OBJECTAGG()`   Return result set as a single JSON object


In SQL Server, why can a backward scan of clustered index cannot not use parallelism?

The referenced article specifically states the reason backward ordered scans were not parallelized in SQL Server 2008 (as of CU6) isn't technical but because the feature hadn't been requested by customers and the dev team didn't bother to implement it.

Note that the article was written nearly 10 years ago in the context of the now unsupported SQL Server 2008 version. There have been significant changes to the storage engine and optimizer. That said, I still see a parallel plan for the ASC query and a serial plan for the DESC version from the article's demo query on SQL Server 2017:

SELECT *
FROM dbo.Orders
WHERE orderid <= 100000
ORDER BY orderdate ASC;

SELECT *
FROM dbo.Orders
WHERE orderid <= 100000
ORDER BY orderdate DESC;

Running those same queries under SQL 2019 CTP 3.2 shows a serial plan for both unless I changed the query to WHERE orderid <= 50000, where I then observed the same behavior as SQL Server 2017. So it seems that either the parallel backwards scan has still not yet been implemented or a different scenario is needed to observe it.


I prefer one file per backup

DB_FULL_yyyymmddhhnn.bak
DB_DIFF_yyyymmddhhnn.bak
DB_LOG_yyyymmddhhnn.bak

I don't want to:

query a backup device to see what is stored there
copy an entire backup device to get one database
FTP or otherwise shift more I need around the place

If you have TB size database, partitions, filegroups and want to do partial backups/restores then these problems are multiplied. In a DR situation, I want everything simple and clear.

Most (large and small) shops I've been in do this: it is simpler to deal with self describing files.

The "device" concept goes back to Sybase, SQL Server 6.5 and earlier. Here you had disk devices too Separate MDFs and LDFs were introduce with SQL Server 7


SQL Server 2016 TempDB physical file size is different than SQL System Catalog And Proportional Fill Algorithm is not working as expected

Also another major issue is proportional fill algorithm is not working as expected as couple of files are having more size when compared to other files. What can be the reason for it?

One reason for this could be that the files were intentionally (mis)configured like this.

Try querying sys.master_files as well, which will have the last configured size value for the tempdb files:

SELECT
    [name],
    [file_id],
    [type_desc],
    [size] * 8 / 1024 [size_mb]
FROM sys.master_files
WHERE database_id = 2

You could manually shrink the main tempdb file to 350 MB to get the sizes allocations spreading around normally again.

Regarding your other question, I'm really not sure why the value in sys.database_files is different from what is shown in the file explorer.  Hopefully someone else can come along and solve that mystery.


Since "37" is a string, not a number, your second query would have to use a jsonb string:

... WHERE p_attribute -> 'age' @> '"37"'::jsonb

Note the double quotes around 37.

Also note that this query wouldn't be able to use the index, while the first query can.


According to:

 https://www.sqlite.org/windowfunctions.html

SQLite supports window functions. You can therefore easily enumerate the resultset like:

SELECT date_time, value
     , row_number() over (partition by DATE(date_time), HOUR(date_time)
                          order by dt desc) as rn
FROM T

I'm not sure what date functions there are, so you might have to change those. Now, since we ordered them in descending order, we can just pick the first one:

SELECT date_time, value
FROM (
    SELECT date_time, value
         , row_number() over (partition by DATE(date_time), HOUR(date_time)
                              order by dt desc) as rn
    FROM T
) AS X
WHERE rn = 1;


Server-side SELECT statement timeouts allows:

SET SESSION MAX_EXECUTION_TIME=2000;

Not exactly the same, but close.


Why does MySQL slow down in loading data?

You might need to LOCK the tables before you insert the data into the database and then you unlock after the LOAD statement.

There are some tips on MySQL manual as well, check it out:

http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam-bulk-data-loading.html


unable to start psql : no pg_hba.conf entry for host "::1" , user "postgres", database "postgres", SSL off

Here is what I did , I have no idea why it worked but it may help you lol.

The error message is something like :

FATAL: no pg_hba.conf entry for host “fe70::3069:d1cc:6120:27e5%15” ...bla..bla

I don't even know much about IP6 but I assume that long weird number is my ip6, so I added it to my pg_hba.conf

  host    all             all             fe70::3069:d1cc:6120:27e5/96  trust

That /96 is what I took from the manual. I can care less about what it mean since it is dev env. and ABOVE ALL it works on my


If SQL Server is running, the "system" backups will either (a) not include the SQL Server files, or (b) they may not be in a consistent state1.

Ensure SQL Server native backups are taking place on a regular basis, and test the restore procedure.

Also, note the lack of backup devices does not necessarily mean there are no SQL Server native backups taking place.  You need to look at the dbo.backuphistory table in the msdb database to see if backups have taken place.  One way to look at the backup history is to run the following query:

/*
    You can use this variable to either (a) show all database backup
    history, or just history for a single database.
    If you leave it as-is, it will display history for all databases
*/
DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME();
SET @DBName = NULL;

SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSize = bs.compressed_backup_size
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType =
        CASE bs.type
        WHEN 'D' THEN 'Database'
        WHEN 'L' THEN 'Log'
        ELSE '[unknown]'
        END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.database_name = @DBName
    OR @DBName IS NULL
ORDER BY bs.backup_start_date DESC;

1 - see https://msdn.microsoft.com/en-us/library/ms175536.aspx for details about running the SQL Server VSS Writer service to ensure consistent backups when running backups outside of SQL Server.


There are three "official" Oracle tools for managing MySQL in the sense you describe, two of which are currently supported.

MySQL Workbench I assume is the administration tool that's most analogous to SSMS.

The mysql command line client is a console utility that allows you to issue queries and get results displayed in "ascii line drawing" tables.

MySQL Query Browser is no longer officially supported, but still available for download and still compatible with newer versions of MySQL Server.  It's minimalistic in functionality compared to Workbench, but it's also substantially more lightweight.  It's essentially a GUI that's well-suited to a power user who doesn't want "help," just an interface that's more usable for running queries than the command line.  (As a DBA all day every day, I use Query Browser and the command line client almost exclusively.)

They're all free, of course.

There's nothing proprietary about MySQL Community Edition, so there's really nothing you can to do, administratively, to a MySQL server that specifically requires one of the official clients.  There are others out there, third party graphical tools that can provide, for all practical purposes, any functionality that "official" clients can provide.  They all have their respective strengths and weaknesses.

One thing to keep in mind, however, is that if you encounter any unusual behavior in any client, is that you should verify that behavior using the mysql command line client, to confirm whether it's the client you're using or the server behaving unexpectedly but consistently among different clients.


Count where any 3 columns have values (not null)

You can mark any NULL as 0 and not NULL as 1 and calculate the sum,
it will give you the number of not NULL values in a row.

If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):

declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);

with cte as
(
select *,
       case when col1 is null then 0 else 1 end +
       case when col2 is null then 0 else 1 end +
       case when col3 is null then 0 else 1 end +
       case when col4 is null then 0 else 1 end +
       case when col5 is null then 0 else 1 end +
       case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns
from  @ItemProperties
)

--select *
--from cte
--where Num_of_not_NULL_columns = 3

select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;

If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;


SQL Server 2008: High CPU historical queries

No, SQL Server does not track individual occurrences of queries, for the same reason you don't want to run profiler: tracing constantly can have a substantial performance impact on the server, and the last thing Microsoft wants to do is to turn on high-cost diagnostics most people will rarely or never use. Profiler is the absolute last tool you want to use for this anyway, as it is the worst offender. A server-side trace is much lighter weight, but you should also look into extended events and perhaps even 3rd party monitoring tools - I work for a vendor of one such tool and would be happy to give you guidance there.

In the meantime, you can track expensive queries in aggregate using DMVs like sys.dm_exec_procedure_stats and sys.dm_exec_query_stats. Here's a procedure from Glenn Berry's Diagnostic Queries that gets the top 25 CPU-consuming stored procedures for a specific database:

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

You can do a similar thing with sys.dm_exec_query_stats if you have a lot of ad hoc queries. For example:

SELECT TOP(25) SUBSTRING(t.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(t.text)
     ELSE qs.statement_end_offset
     END - qs.statement_start_offset)/2) + 1) AS statement_text,
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second,qs.creation_time,GETDATE()),0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY AvgWorkerTime DESC OPTION (RECOMPILE);

In fact a similar example can be found in the documentation.

Note that these DMVs do not persist data beyond a service restart and some other events, so you might not have the history you expect.


Oracle: How can I add ROWDEPENDENCIES to an existing table?

Unfortunately, you can't (unless something has changed in 12c that I'm not aware of).  You'll have to recreate the table with rowdependencies and move all the data from the old table to the new one.

Of course, you could use the dbms_redefinition package to do this redefinition online.  But under the covers, you'd still be creating a new table with rowdependencies enabled, copying the data over, dropping the old table and renaming the new one.  It would just involve potentially less downtime (at a cost to how long it takes and how much DBA time is required).


SQL Server - Service User Accounts and Licensing?

Basically User CALs are for people, not for Service Accounts.  In the User CAL model you need a CAL for each person whether they directly access SQL Server from their account or they access SQL Server through some Service Account.

This is a CAL per each and every person that uses the SQL Server, not a CAL per simultaneous user. However, your CALs will allow you to access as many SQL Servers that use CALs as you can reach within your organization.

Edit: There are also Device CALs for when several people share a device at different times.  Read about CALS at:

https://www.microsoft.com/licensing/about-licensing/client-access-license.aspx

Regarding Service Accounts on the SQL Server I cannot find my old link on the subject,  but perhaps this post will help you:

http://blogs.technet.com/b/uspartner_ts2team/archive/2013/11/05/administrative-amp-service-account-cals.aspx

See the sentence that says

  And for system service accounts – no CAL is required.

Therefore, since Service Accounts are not people, you can use as many service accounts as you can make use of: SQL Server, SQL Agent, Full Text, and so on.


MySQL stored procedure create database by month

I would advice you to take a look at partitioning if you are running a recent version of MySQL and see if this could solve your problem.

Partitioning does not result in a smaller database but offers many of the same benefits with less administration once it is set up, and you will still have access to historic data.


Grant the user read-only access and have your programs authenticate as a different user (one that's got modify rights).


Yes there are DMV's for this like sys.dm_db_index_usage_stats, but that alone will not help in analyzing the unused databases.

Here you can analyse on unused databases with help of SQL Server Audit as explained here by Aaron Bertrand When was my database / table last accessed?

In addition you may need various other methods to track the database usage like:

1) If there are any transactions count going up for the database in question with help of counters info, using below query:

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Transactions/sec%'
and instance_name like 'instance_name_here%';
GO

2) You can also ask the execution plan cache if queries have been running against the database. This query takes advantage of the dm_exec_text_query_plan DMV as explained in the article Querying Information from the Plan Cache, Simplified

3) You can check from the login history as well on those databases using Adam Mechanic's SP_WhoIsActive

Once you have the data collected all together ,and listed databases to be taken offline execute

ALTER DATABASE [database_name] SET OFFLINE;

Monitor for a duration and if an application goes down for the reason being database of that being taken offline, you can simply take those databases back online:

ALTER DATABASE [database_name] SET ONLINE;


I moved all my data to new tablespace and then recreated old one and moved everything back. Everything seems in order, no data loss.


Store extended properties and apply to another copy of the database

I don't know of an easy way to just extract them and reapply to another target, but you could use the data in sys.extended_properties to generate a script to add the properties to another database using calls to sp_addextendedproperty.

See https://msdn.microsoft.com/en-us/library/ms177541.aspx and https://msdn.microsoft.com/en-us/library/ms180047.aspx for a bit of documentation around that.

If the target database may already contain some properties (from earlier transfers, or added for other reasons) then the script you generate may need to be more clever and check to see if it should call sp_updateextendedproperty instead (also potentially sp_dropextendedproperty to remove properties that are now deprecated).


Does it matter if id (in TABLE ) starts from 0 or from 10000

Your database shouldn't care, and to it a number is a number is a number in this context. Performance will be the same either way here.

Just make sure that you select an appropriate data type so that you have enough room to "grow" and don't hit the upper bounds of that data type in your expected usage (e.g. don't use a smallint if you're going to need your IDs to go out to 2 billion).


Why does metadata have to stored in table cells according to Codd's 12 rules

Metadata isn't uniquely more important than non-metadata in this context. Rather, his rule just simply wants the reader to realize that "All Information" is inclusive of even the metadata regarding the database itself. This is useful for understanding the structure of the database and the relational model behind it. This is why the Information Schema is part of the ANSI SQL standards.


Improve Connectivity To MySQL Databases Using Multiple Ports

Running MySQL on different ports wont make any difference in the Speed of your queries.
What you could do is have a look at your Indexes. If you have a WHERE statement, make sure you have in the following order =, !=, in, < / >, like.

If you use 'where' and your 'like' is before '=' your query will run longer.


It is no secret that I am not a fan of MERGE, for good reasons, and typically when I see customer code with MERGE, I re-write it using the "old" way of separate statements (and recommend others do the same, especially when they are not MERGE experts).

  since merge will be faster than two statements.

NO IT WON'T. MERGE still runs the exact same semantics as separate statements would. There is no performance advantage at all. Please read up on it and be sure you understand how it works - if you are so confident MERGE is faster, then when you get your statement working, run some tests and try to prove it. I'm afraid you'll be disappointed with the results, but at least now your conclusion will be based on facts.

I think the error message is pretty clear; since ProductId,ImageType is not unique, you might need to add the ThumbXX columns to the comparison.

AND TARGET.Thumb90    = SOURCE.Thumb90
AND TARGET.Thumb200   = SOURCE.Thumb200
AND TARGET.Thumb500   = SOURCE.Thumb500

Also you should properly alias the VALUES() list:

WHEN NOT MATCHED
THEN
    INSERT ([ProductId],[ImageType],...)
    VALUES (SOURCE.[ProductId],SOURCE.[ImageType],...);

I would still write this as UPDATE ... WHERE EXISTS and then INSERT ... WHERE NOT EXISTS (and wrapped in an appropriate transaction of course, which your current MERGE statement needs but lacks).


The only information you have about 'when' a row entered the table is the ID of the inserting transaction.  The sad news is that this is in no way connected to any real timestamp.

So you either add the new column (where you can't figure out when the existing rows were inserted) - or probably you can create triggers on all tables which populate a transaction ID-timestamp table.

The number of rows approach can work only if you keep track of not only the inserts but deletes (and truncates) as well.

Alternatively, if this task is not needed too often, you can use PostgreSQL's point-in-time recovery mechanism to set up a database which mirrors the state of a given time in the past, then compare the data in the two versions.


how to deal with a SSIS 2005 validation error in a script component

Since you're dealing with 2005, there were some known issues with scripts.

For the reported version of 9.0.5057, you'd want to look at KB932556

Earlier version (RTM) of SQL Server would have KB932557 applied


PostgreSQL to SQL Server Migration Error Caused by Default Value Expression

For "normal" (day, week, month, year, ...) in T-SQL you'd use the DATEPART function. (Check DATEPART (Transact-SQL)). However, this function doesn't deal with 'epoch'.

The definition of date_part('epoch'::text, now()) in PostgreSQL is: `time elapsed since the "epoch time" (1 Jan 1970), measured in seconds. In T-SQL, you would compute it with:

DATEDIFF(second,'19700101 00:00:00:000', getutcdate())


You should be using the catalog views instead of the old system tables (e.g. sys.objects instead of sys.sysobjects, sys.columns instead of sys.syscolumns). The system tables still exist, but they are there for backward compatibility only. Some of them have been moved to the hidden system database, mssqlsystemresource, and/or are only visible when connecting with the dedicated administrator connection. Of course you will need certain permissions to view the new catalog views.

Please read the notes on the old docs, e.g. for sys.syscolumns:

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Here is a link to the mapping document they mention, but don't link. This should show you how to start making the transition from, say, syscolumns to sys.columns.

Stop using the old system tables. Learn the new metadata; it's here to stay.


You can do this first: REMOVE THE WHERE CLAUSE

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON a.area_id = b.i_area
GROUP BY a.area_name

This will show area 2

If you really want the WHERE clause, move it into the ON Clause

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON
    a.area_id = b.i_area AND
    YEAR(b.i_date) = YEAR(CURRENT_DATE()) AND
    MONTH(b.i_date) = MONTH(CURRENT_DATE())
GROUP BY a.area_name

GIVE IT A TRY !!!


Error trying to update stock with trigger

You cannot use the reference PRODUCT_SALES. for columns inside the trigger.

In the code, once you get passed FOR EACH ROW you must reference the new value with NEW.

CREATE TRIGGER updateStock
AFTER INSERT
ON PRODUCT_SALES
FOR EACH ROW
    UPDATE PRODUCTS
    SET PRODUCTS.stock = PRODUCTS.stock - NEW.quantity
    WHERE PRODUCTS.id_product = NEW.product_id;


Resource Hosting Subsystem was terminated which caused Availability group to fail

That error means the AG failed one of the health detection timeouts (lease, session, or health check) summarized in this table from the docs:
Mechanics and guidelines of lease, cluster, and health check timeouts for Always On availability groups - Summary of Timeout Guidelines
Your first stop should be to review the SQL Server error log on each node in the AG, to see if there was something going on that might have caused the instance to stop responding to the cluster AG resource.  For instance, you might have crash dumps, deadlocked schedulers, etc.
To dig into the cluster-related details, you'll need to call the PowerShell Get-ClusterLog cmdlet (see here) to get the cluster log file from each node in the AG.  Then find the time associated with the failure error message you mentioned in the question.  Review the cluster log files around that time period for errors (search for "ERR") or any messages of interest from the AG resource DLL itself (which will include "[RES]" or "[hadrag]").
I would expect you'll find messages that include "timed out" or "IsAlive" showing the timing of the failures.  At this point, you should be able to figure out which server was not responding, and investigate that directly.
Figuring these things can be difficult.  Microsoft support has released a tool that can be used to categorize some types of failovers, which might be useful to you:
Failover Detection Utility - Availability Group Failover Analysis Made Easy


What database supports over a thousand fields and tables

My recommendation would be for you to check out Cassandra.  In my experience, and based on the requirements you seem to have, it seems like it would be a good fit.

It appears to me that you have essentially have these requirements:

(1) Key-value storage (look up data based on ID)

(2) No need for JOIN or sub-SELECT

(3) Need for a SQL-like language

(4) Horizontal scalability

As for (1), Cassandra will hash the primary key (in your case you can use a single column id as your primary key) and which node in the cluster stores the data, and the Cassandra internals ensure look up by primary key is very fast.  Cassandra doesn't support JOIN as in (2), but you appear to be OK with that.

Since you are essentially doing key-value lookups, I'm actually not sure why you need an SQL language.  Perhaps you are wanting to not only look up data by primary key, but also perform aggregations and use GROUP BY?  In this case, Cassandra won't work, but if you only need a simple SQL language to retrieve data as per (3), Cassandra has CQL, which may serve your needs.

Horizontal scalability is built right in to Cassandra, covering (4).

All that being said, Cassandra isn't a silver bullet.  It's a great technology for certain applications, and it sounds like it fits you needs.  Still, if you want aggregations, or if you want to query for multiple primary keys in a single query (e.g. SELECT * FROM table WHERE id IN (...)), then you may run in to some issues.

Now, if you can let go of constraint (4), I'd 100% be promoting PostgreSQL.  You stated the database will be getting hit with thousands of requests per hour, but that's nothing for a database like PostgreSQL, if your queries are only individual primary key lookups, and you aren't trying to transfer large amounts of data in those queries.

Best of luck!  If you provide more info based on my feedback, perhaps the community here can help you narrow down your selection even more.


Calling a web service from T-SQL (Stored Procedure) using MSXML

Not sure about that exact error, but I would advise against using the OLE Automation stored procedures (i.e. sp_OA*) as they have been deprecated since SQL Server 2005 was released and have various "issues".

I you need to interact with a Web Service, your best bet is to use SQLCLR. I have more info regarding this option in the following answer, also here on DBA.SE:

Bringing web service data into SQL server


Table replication with extra TIMESTAMP column on slave

The behavior you are seeing is normal for row-based replication.  Extra columns are valid at the right-hand "side" of the table, but row-based replication operates at a level, on the slave, where automatic timestamps aren't updated.

Statement-based replication has been suggested, but it seems likely to become even more fragile than it already is.

MariaDB 10.1 introduces slave triggers for row-based replication, which could accomplish the purpose, assuming the master does not already have triggers on the table.

Normally, when an event is replicated in row format, triggers do not fire on the slave, because the changes made on the master by the triggers will have replicated with the row events.

MariaDB 10.1 can be configured such that row events will fire on the slave, under one condition: if the event encounters a trigger on the slave, but no trigger fired on the master, because there is no trigger defined on the master, then the slave trigger will fire.  Since this requires the master to include new information in the binlog (I can't think of any facet of binary loggong that captures this now) then the master would need to be running 10.1 also.

This capability seems like it could accomplish what you want.


There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database:

  ALTER VIEW can be applied to indexed views; however, ALTER VIEW
  unconditionally drops all indexes on the view


MySQL - Performing an IF statement for each row in a query

To get desired results, use LEFT JOIN rsvpList instead of [INNER] JOIN. Thus, in resultset r.eventId will be NULL for rows in eventList table that don't have corresponding rows in rsvpList.

 SELECT e.description,e.eventDate,e.eventTime,e.locationDescription,e.address,
 IF(r.eventId = e._id AND r.guestId = 1, 1, 0)
 FROM eventList AS e
 JOIN inviteList AS i ON e._id = i.eventId
 LEFT JOIN rsvpList AS r ON e._id = r.eventId
 WHERE i.guestId = 1;


TSQL or SSIS for Direct Insert Speed Performance

To briefly quote the documentation:

  Microsoft Integration Services is a platform for building
  enterprise-level data integration and data transformations solutions.
  Use Integration Services to solve complex business problems by copying
  or downloading files, loading data warehouses, cleansing and mining
  data, and managing SQL Server objects and data.

  Integration Services can extract and transform data from a wide
  variety of sources such as XML data files, flat files, and relational
  data sources, and then load the data into one or more destinations.

You say that you're loading data from one table to another in the same database without transformations. SSIS isn't really the right tool for the job. With a reasonable table structure, properly written T-SQL inserts will perform better than loading through SSIS because you avoid the overhead of sending data to another server, passing it through buffers, and sending it back to the destination server. Behind the scenes SSIS is just doing a BULK INSERT which isn't going to be faster than a T-SQL INSERT except in edge cases.

In the interest of giving a complete answer, I'll describe a few of the features of SSIS which can be helpful from a data loading performance perspective. Consider a table in SQL Server with a complex five column clustered index and many nonclustered indexes. Suppose you can't disable NCIs. You might get the best possible loading performance by splitting up the insert into batches because smaller sorts will be used to do NCI maintenance. It may be difficult to code an efficient batching solution in SQL Server with a five column clustered key. However, this is trivial to do in SSIS. SSIS can read the data in order and split the data up into batches for you just by changing a parameter. So in that case, you may get better performance with SSIS because the T-SQL code wasn't written to be as efficient as possible.

For another example, a BULK INSERT is able to take a BU lock on a target heap. BU locks are compatible with other BU locks. As a result, you can get multiple concurrent T-SQL inserts all loading minimally logged data into the same heap without blocking. That cannot be accomplished with T-SQL alone. The BU lock is not available in that context. SQL Server 2017 offers parallel inserts into heaps in T-SQL, so the benefit of a BU lock is diminished when loading data from a table with an identical structure. An example where a BU lock is better than anything in T-SQL would be loading from a table with a clustered index into a heap on SQL Server 2014 Standard Edition. In T-SQL you can only run a single insert at a time but in SSIS you could have many concurrent inserts running.

In summary, SSIS is almost certainly the wrong tool for the scenario that you described. You should expect to see better performance with properly written T-SQL code, with the exception of a few edge cases, because it avoids the overhead inherent to moving data between servers.


Can't set default_schema_name for Windows User Group?

Upgrade to a modern version of SQL Server. As the error states, you cannot specify a default schema for a group. You can in SQL Server 2012 and newer.


Does granting SA rights to a user in SQL compromise the OS?

sysadmin rights allow the use of things like xp_cmdshell and the sp_OA% stored procs which can affect the underlying OS, but it depends on the service account used

If you limit the service account permissions (eg not local system, not local admin and certainly not domain admin) then it's pretty much OK. The SQL installer limits the permissions of the service account during install, of course this assumes you don't escalate permissions yourself.


Storing external databases data into one

Technically your Version.DatasetId gets you back to the Dataset table and then you can determine the Provider by ProviderId. So while the Version table can be a little confusing by VersionId, as long as there's no overlapping VersionIds in Ext and Main then you should be ok. Otherwise you'll need to add the DatesetId to Ext and Main too.
From a design perspective for other developers and users, you can create a view that pre-joins the Ext and Main to Version and provide that view to them instead. You can even add a column called IsMain to the view to be extra explicit if you feel.


Simple access query based on many to many relationship

I'm going to assume for the purpose of this answer that these records refer to days in which a particular company attended something.  If that's correct you can do this with two tables instead of three, and it's a one-to-many join if you don't have duplicate company names.  It is perfectly ok to have a many-to-many join if your tables are something like this:

List of companies, Companies

Company

Dell
Apple
Google

List of attendance by company, Attendance

Company       Day_attending

Dell          08/01/2016
Dell          08/31/2016
Apple         08/05/2016
Apple         08/08/2016

So in this case you'd be looking for Google as the output since Google has no attendance records.

Access doesn't know how to do this with the query designer, so you'll have to go into SQL mode and enter the code yourself.  If you were in normal SQL you'd use EXCEPT, but Access doesn't support it.  Instead, you can do a funky join by Company value where Company is null.  Like this:

SELECT c.Company
FROM Companies as c
LEFT JOIN
Attendance as a
    ON c.Company = a.Company
    WHERE a.Company is null


is possible to use graphql with couchdb?

I think GraphQL is a tool meant to smooth the way for you to write the custom API for your own app. Very few databases have a commonly published API as yet.

Some people have started playing with GraphQL for couchdb and pouchdb - see these repos on Github

https://github.com/chentsulin/awesome-graphql
https://github.com/esnet/couchdb-graphql-test
https://github.com/robertkowalski/couchdb-graphql
https://github.com/MikeBild/graphql-pouch


There are no global variables per se in Postgres. But we have "customized options" that can be streched for the puropose.

SET myvars.test TO '0.1';

Persists for the duration of the session (not just transaction). Use with:

SELECT current_setting('myvars.test')::float;

Be aware that values are stored as text. So you may need to cast as demonstrated.

To avoid exceptions when referencing an unset variable and get NULL instead:

SELECT current_setting('myvars.test', true)::float;

See:

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

There are a number of other workarounds, too:

Is there a way to define a named constant in a PostgreSQL query?


If you want to keep the existing rows and don't want the table renamed but only a few columns altered/changed/dropped, you can do this with a single ALTER TABLE statement. This will only fail if there is no index on (id):

ALTER TABLE table_1
   CHANGE COLUMN id
      id_table_1 int NOT NULL AUTO_INCREMENT,
-- ADD PRIMARY KEY
--    (id_table_1),
   CHANGE COLUMN active
      status TINYINT NOT NULL DEFAULT 1,
   CHANGE COLUMN sent
      sent TINYINT DEFAULT 0,
   DROP COLUMN description ;

Tested at SQL-Fiddle


MySQL connection process slow for non-root user

It looks that server lookup is taking time while searching for the host over the network and in order to minimize the lookup time add the entries in hosts file as entries in hosts file (/etc/hosts) does minimize the lookup time.


I renamed few things here, just to make it easier for me to think. Most of it is self-explanatory. In essence, the problem and the solution is very similar to the previous question, with more detailed answer.


Always enjoy getting help from this site... Thanks to @billinkc.

I had the thought in the back of my mind that maybe the graph had to much data to display but guess it never clicked.

I modified the graph's Category Groups to be an expression for grouping by the hour not just the full date value: =Hour(Fields!CapturedTime.Value)

That allowed the graph to be prettified into this:


What are the primary architectural differences between the backup formats of some of the more common RDBMS' and what advantage do they serve?

The native backup of sql server, commonly have extension of .bak is something which is proprietary of microsoft.

Different RDBMS providers have different formats and it also depends on whether they are open source (PostgreSQL and MySQL) or are patented (MS SQL Server, ORACLE or SybaseASE).

For MS SQL Server :

A backup is a page-by-page copy of the database, as it existed the moment you took the backup, a restored copy of that database will be in exactly the same state as it was the moment you took the backup.

A backup contains more than just the data. It contains the entire structure of the database including all the data structures and data along with procedures, views, functions and any other code. It also contains the settings and definitions of the database along with the users of the database. Exception is contained databases have a concept called USER WITH PASSWORD.

If you want to use something that you mentioned, have a look at SqlDump - Microsoft SQL Server database backup program

  SqlDump is a program to backup a Microsoft SQL Server database as a text file. SqlDump generates SQL statements for tables, indexes, user-defined types, views, procedures, etc and also the table data. This data can be used for a variety of purposes, such as database backups, moving databases to another server, or setting up a test database based on the contents of an existing database

Note: I have not tried the SQLDump, as I would personally prefer SSIS or T-SQL along with BCP and BULK INSERT - to move data out and in of sql server.


Does the definition of functional dependency permit derivative values?

I found an excellent blog post by Ken Downs that clarifies everything:

Database Skills: Third Normal Form and Calculated Values

Basically, the tables above aren't 3NF on account of the derived attributes; 3NF doesn't permit derived attributes. And, yes, it follows from it that derived attributes are functionally dependent on their source attributes.


What is the purpose of PGBouncer's parameter server_reset_query?

Since pgbouncer is a connection pooler, it will reuse one actual connection to the database server for potentially many client connections.  It has to make sure that whatever session state the first client created is reset when the next client gets the connection.  Otherwise the first client might do something like SET statement_timeout = '5min', and that would apply to all subsequent clients who happen to be assigned that connection.  To avoid that, pgbouncer issues the "server reset query" before handing out the server connection to a new client.  As the documentation says

  A good choice for Postgres 8.2 and below is:

  server_reset_query = RESET ALL; SET SESSION AUTHORIZATION DEFAULT;

  for 8.3 and above its enough to do:

  server_reset_query = DISCARD ALL;

The main reason this setting exists is that the DISCARD ALL statement, which was invented for this very purpose, was not available before PostgreSQL 8.3.

The setting applies independently of the pool mode.  Note that the client could issue a SET statement or something like that in any pool mode.

The documentation also says

  When transaction pooling is used, the server_reset_query should be empty, as clients should not use any session features.

I think this is a faith-based approach.  If you think your code is clean and you need the extra performance, go for it.  I've been bitten by this, though.  For example, if you use check_postgres to monitor your pgbouncer connections, it will issue a SET statement_timeout like I showed above and mess up your connections.


Production database substantially bigger than Dev

Compression can cause you to see different table and index sizes for the same data across two tables.

You asked in a comment if there's any reason not to apply compression. The general trade-off for page compression is that your tables take up less space and you can fit more data into memory at a cost of CPU overhead. As a very general rule, if your server has CPU to spare you might as well test your workload before and after applying compression to see what happens. There are even some workloads which will get more efficient from a CPU perspective after applying compression. In short, "it depends".

Another reason not to use compression is if you aren't licensed for it. Your development server could be using developer edition which allows data compression but your production server could be using standard edition, which doesn't allow data compression until SQL Server 2016 SP1.


Installed Percona-Toolkit, but no sign of xtrabackup

It turned out to be an issue with our Proxy Server, blocking downloads from percona.com

Running sudo apt-get update revealed that every connection to percona.com 'failed', and so I got that added to the approved list, and after that it installed fine.


Number of times a table is read, written to and updated

In SQL Server Profiler, I tested this using SQL Server 2008. I don't know if the output will be the same in MSSQL 2000.

Using the template: TSQL_Replay

Select the Events Selection Tab
Check Show all events
Expand Performance then check Showplan XML
When you run your trace you will see the following events appear

When you select Showplan XML, you will see the Execution plan generated by the query

If you hover your mouse over the Delete event, as in my example you will get the number of
rows and other information related to the query

Update 15:50

Here is the SQL exported by profiler for the showplan XML event.

exec sp_trace_setevent @TraceID, 122, 8, @on
exec sp_trace_setevent @TraceID, 122, 1, @on
exec sp_trace_setevent @TraceID, 122, 9, @on
exec sp_trace_setevent @TraceID, 122, 2, @on
exec sp_trace_setevent @TraceID, 122, 10, @on
exec sp_trace_setevent @TraceID, 122, 14, @on
exec sp_trace_setevent @TraceID, 122, 26, @on
exec sp_trace_setevent @TraceID, 122, 3, @on
exec sp_trace_setevent @TraceID, 122, 11, @on
exec sp_trace_setevent @TraceID, 122, 35, @on
exec sp_trace_setevent @TraceID, 122, 51, @on
exec sp_trace_setevent @TraceID, 122, 12, @on
exec sp_trace_setevent @TraceID, 122, 60, @on

122 is the event and then there is an id for each column selected in profiler for Showplan XML when I exported the trace to a file.

Here is the list of events you can trace from Microsoft: Trace Events


You have a leading column specified in condition (Location = :location) . From here :

  The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as the following:
  col1 = :b1
  ...


General strategy for updating a full text search index

Any reasoning for not using Change Tracking-Based Population and letting SQL Server take care of the updates automagically?

Unless you are depending on WRITETEXT or UPDATETEXT to change LOB columns (which are not tracked), I'd suggest accepting the slight overhead associated with change tracking, enable automatic population (which is the default) and rest easy.


Selecting the max date out of a text group

You need to choose max(question_date) per subject. Something like:

WHERE question_date IN (
    select max(question_date)
    FROM messages
    WHERE subject = a.subject
)

Another option is to enumerate using OLAP functions and choose most recent subject using that:

select
  new_subject,
  message_id,
  product_id,
  si,
  question_date
from (
       select
         trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
         *,
         a.seller_id as si

       , row_number() over (partition by a.subject
                            order by question_date desc) as rn

       from messages a
         INNER JOIN sellers b
           ON a.seller_id = b.seller_id
       WHERE a.product_id = 0
     ) as x

WHERE rn = 1

GROUP BY new_subject, product_id, message_id, question_date, si
ORDER BY new_subject, question_date DESC;

The first alternative will return several rows per subject in case they have the same question_date. The second alternative will return only one
 row per subject. Using rank() instead of row_number() makes the second alternative to behave like the first one.

You may have to add another level of nesting to take into consideration that several subjects may map to the same new_subject:

select new_subject, message_id, product_id, si, question_date
from (
    select
      new_subject,
      message_id,
      product_id,
      si,
      question_date,
      row_number() over (partition by new_subject
                         order by question_date desc) as rn
    from (
           select
             trim(both ' ' from regexp_replace(a.subject, 'RE:|Re:', '', 'g')) as new_subject,
             *,
             a.seller_id as si
           from messages a
             INNER JOIN sellers b
               ON a.seller_id = b.seller_id
           WHERE a.product_id = 0
         ) as x
) where rn = 1
ORDER BY new_subject, question_date DESC;


Oracle Database Upgrade Assistant "Failed in bringing up..." Error

So I found the solution after much investigation and trial and error. I deleted the .log files from $ORACLE_BASE/cfgtoollogs/dbua/logs. After restarting the session and the UA, it worked fine. I'm not entirely sure WHY that worked (which bugs me), but there's the answer. Hopefully this will be helpful to others in this situation.

Thanks to those who commented on my question.


Migration from SQL Server 2008 to Postgres 9.2

TinyInt and SmallDateTime (AFAIK) are non-SQL Standard datatypes.
EDIT: SQL Standard types nearest equivalents are, respectively, SMALLINT and TIMESTAMP. I suggest you do an conversion of columns using those types before the migration to see if you identify any issues with the application. It's your call to solve that before the migration or note that in a checklist for use after. The nearest MSSQL equivalent of TIMESTAMP (AFAIK) is DATETIME2.

I believe that most of the tables creation script is capable of running with few edits.
Additional index creation can be more trouble if you used some unique capabilities
of SQL Server. An decent DB case tool can make that transition less difficult (sorry, don't know of a free one - we use ER/Studio here for MSSQL).

Stored Procedures - that's where is the real pain. You have to learn the PL/pgSQL and translate your logic to it. Since tricks used on one db engine have very high chance of
not working/getting very lousy performance on another, get an Postgres database developer to help you on that.
EDIT: While an DB case tool can do a decent job converting an db structure from an engine to another, I firmly believe that an tool to convert stored procedures from an procedure language to another can cause more harm than progress.


Update stats w/full scan on SQL Server 2014 uses 100% cpu, on 2008 R2, 15%

Statistics updates can go parallel based on a lot of different options in SQL Server:

Cost Threshold for Parallelism - a query must be this high to ride the parallelism train. Your two servers could have different CTFP settings that cause the 2008R2 update to go single-threaded, whereas the 2014 one can go multi-threaded.
Max Degree of Parallelism - dictates how many cores a query can use, at most, if SQL Server decides to parallelize it that far. The 2008R2 box might have MAXDOP set to 1, whereas the 2014 box might have it set to the default of 0 (unlimited.)
Resource Governor - this Enterprise Edition feature lets you throttle different groups of users or applications to different MAXDOPs.

In later versions of SQL Server (2016 & newer), this gets even more complicated:

Database-level scoped options - you can right-click on a database, go into properties, and set the MAXDOP level for that database.
Statistics parallelism hints - starting in 2016 SP2, the statistics creation & update statements accept MAXDOP hints

As you noted, your 2008R2 one is going single-threaded, whereas the 2014 one is going multi-threaded (thus finishing faster, but maxing out CPU while it runs.)

To find the right balance for your stats jobs, think about:

What other workloads are happening in the database at the same time? Can you afford to dominate the box during brief periods? For example, in data warehouses that sit idle during most weekend hours, I've seen folks crunch hard on stats updates with fullscans when they know nobody's using the server anyway. In heavy-duty transactional environments, you have to start using less impact for maintenance tasks if users complain even during midnight periods.
Is fullscan really necessary? Are you seeing queries that only get good plans when you use the fullscan option, or are you merely doing it as a best-practices thing? As your database grows, if your hardware investments don't keep pace, you may have to start making tradeoffs in stats sampling rather than doing fullscans.
Can you update stats less often? For example, update 1/4 of your stats each weekend, and then every month, everything will get stats updates?
Can you update less objects? Often I see folks updating stats even on huge audit or archive tables simply because a few dozen new inserts were done, but those inserts don't really affect the stats on the table (and nobody's querying it anyway.)


Is it safe to use "Safety" as a schema name?

The word "safety" was used as a PivotTable service option back in the days of SQL 2000.  It's still around for backwards compatibility:

http://msdn.microsoft.com/en-us/library/aa237323(v=sql.80).aspx

Otherwise, it's okay to use as a schema name.


How to log successful login attempts to a database?

Your best bet is to use a Logon Trigger

Just make sure your trigger captures SYSTEM_USER and ORIGINAL_DB_NAME() (this is only available in SQL 2008 +).

Just be careful with logon triggers. If you mess them up you can prevent any logins, because when they fail they cause the login to fail and then you have to use a workaround to get into the system and drop the trigger so you can fix it.

Don't let that hinder you though, just know you need to test them out thoroughly before you go throwing them onto a production server.


MySQL slow queries - Fast using PHPMyAdmin , low CPU and RAM usage

You need to identify which queries are taking some time to execute and optimize accordingly. By default mysql does not log slow queries, so you have to add the following to your mysql config file -

log-slow-queries
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10     // in seconds - you can replace this with another number.

For any mysql performance issues, it is a good idea to use mysqltuner


As a workaround, try using the RIGHT function to avoid the length overflow.

Just drop whatever the max length of your (I'm assuming) CHAR column in the query where I made a note.

update emp
set region_ref = RIGHT('UK_' + emp.REGION_REF , /*put the max length of your column here*/)
from EMPLOYEES emp
inner join REGIONS reg
on emp.region_no=reg.REGION_NO
where emp.region_no in (select region_no from UKREGIONS);


Can't map invalid columns based on column names in a .dtsx-Package

Something has happened such that the source column doesn't exist any more and the mapping is broken. In the bottom drop down, "spaltzenzuodnungsoption..." there should be 3? options. You want the one that says delete. This will remove all the mappings so you can start over.


AlwaysON : Log_Send_Queue_Size is increasing but the log_send_rate keeps decreasing?

Answering my own question, as future readers will benefit from it :

Seems like we might be hitting Longer latency for SQL Server 2012 database when you use Service Broker, database mirroring, and Availability Groups. This is fixed in SQL server 2012 SP2 CU1. The KB 2976982 has a typo(AlawysOn). So if you are searching by AlwaysON, it wont show up.

After the patch was applied, the issue was fixed.


Can we change Log Shipping backup file naming convention

You cannot automatically change the naming convention since it's controlled by sqllogship.exe (and if you manually change the file names I believe it will cause those files to be skipped.)

The naming convention apparently does matter regardless of the LSNs: https://www.sqlservercentral.com/forums/topic/log-shipping-skipping-files-with-different-naming-convention

I would personally just let them group based on their modified dates (since this is the true order they should be in) but if you want to compress them grouped by their name then you should control that with your compression tool / process, as opposed to the naming scheme SQL Server uses. (It would be simple enough to codify a small script to do this.)


sp_restore_filelistonly is a (currently) undocumented stored procedure with two parameters:

@device_type NVARCHAR(10) - like 'DISK'
@backup_path NVARCHAR(360) - which needs to point to the exact backup file you want to example, not just the folder path where it lives

When you point it at a backup file, like this:

EXEC sp_restore_filelistonly
    @device_type = 'DISK',
    @backup_path = 'E:\MSSQL2017\Backup\StackOverflow2010.bak';

It functions like RESTORE FILELISTONLY:

RESTORE FILELISTONLY FROM DISK = 'E:\MSSQL2017\Backup\StackOverflow2010.bak' WITH FILE = 1;

You get an output set with the files included in the backup:

LogicalName
PhysicalName
Type (D for full, L for log, etc)
FileGroupName
Size (in bytes)
MaxSize
FileId
CreateLSN
DropLSN
UniqueID
ReadOnlyLSN
ReadWriteLSN
BackupSizeInBytes - note that this is the uncompressed size, so if your backup used compression, you won't see the real file size here
SourceBlockSize
FileGroupId
LogGroupGUID
DifferentialBaseLSN
DifferentialBaseGUID
IsReadOnly
IsPresent
TDEThumbprint
SnapshotUrl

Why would Microsoft build it? Well, if they want to let someone get a file list without running a RESTORE command. Could be a separation-of-duties thing, or could be making the automation plumbing easier for Azure Managed Instances.


Get distinct category columns with multiple subcategory columns mapped in table

You can accomplish that using ROW_NUMBER().

Take a look at the example below:

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp ( Description VARCHAR(100), SubDescription VARCHAR(100));

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment', 'Peds4' );

INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds2' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds3' );
INSERT INTO #temp ( Description, SubDescription )VALUES ( 'Patient Assignment1', 'Peds4' );

WITH Temp
AS (
    SELECT
        Description,
        SubDescription,
        ROW_NUMBER() OVER ( PARTITION BY Description
ORDER BY Description ) rownumber
    FROM #Temp
)
SELECT
    CASE WHEN Temp.rownumber = 1 THEN Description ELSE '' END Description,
    SubDescription
FROM Temp;

But I'd prefer doing that in the application layer.


Executions/min and CPI ms/sec inside Activity Monitor is too high

One possible way to run a query very many times is to use a scalar user defined function as that will execute once per row in the query.
If you have multiple CPU cores each one can support 1000ms / second which is why total CPU appear strange and go over what seems possible


Moving mysql datadir cause info: checking for tables which need an upgrade

This is an [info] message and displays due to the steps taken by the Debian mysql startup script. It is not an error.


The stacked keys approach (my name for it), where you carry the combination of keys through your tables, tends to pile up very quickly and become unwieldy. I think you are beginning to see this with the example in your question.

So, if you don't want to have to drag every key around for all your relations, then then the answer to the surrogate key question is "yes".

There is a caveat, however. You want to make sure that within each of your lookup tables that you enforce uniqueness on the combinations of lookup table items (in SQL Server, use a UNIQUE KEY). For instance, from the example diagram below:

LocationLookup table:

PK: LocationLookupID
UK: LocationIDSales, LocationIDShip, LocationIDSend

ItemLookup table:

PK: ItemLookupID
UK: LocationLookupID, ItemID

LevelLookup table:

PK: LevelLookupID
UK: ItemLookupID, LevelID

Look how much simpler life becomes when you do this!

Note: When using surrogate keys, I think it is always a good idea for every table to also have at least one unique key constraint.


How to detach a sql server 2008 database that is not in database list?

I'm pretty sure the problem is that due to the uninstall/reinstall of SQL Server, the database engine no longer has permissions on the database files.

If you move the .mdf and .ldf files to the data folder that you specified on install, it should work for you. If you want to keep the files where they are, make sure the permissions are configured to allow appropriate access to the database engine service account group.


How to Migrate complete Transactional Replication to other servers without Snapshot?

I would like to test the process but I don't know if I will be able to shutdown the databases for that long just to test

You should test and automate this process in a non-production environment.


How to accomplish SQL Server 2012+2014 high availability

how to do this for a mixed environment of 2012 and 2014.

Doing HA in a mixed environment is a recipe for disaster because during disaster once you failover from lower (2012) to higher (2014) version, you cannot failback.

Since you are using Enterprise edition of SQL Server, you can leverage AlwaysON technology wherein you can configure writes occurring on Primary and reads on the secondary configured as readable secondary.

Thinking a bit more on what you mean by mixed, I presume that transactional replication would be much better from sql server 2014 as publisher and 2012 acting as subscriber.

The performance would be dependent on how big is the database, if you are replicating only the tables that you need or the entire database, the latency between the two servers as well as the amount of transactions that are occurring on your publisher.

Note that if you go with T-Rep, doing schema changes will require a new snapshot. Also there will be a distribution database created as a part of setting up T-Rep.


Community Wiki answer from a self-answer originally edited into the question (and based on a comment by Balazs Papp).

I forgot an incarnation:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD     130474031        PARENT  1          10-MAY-10
2       2       PROD     130474031        PARENT  195833299  21-OCT-10
3       3       PROD     130474031        PARENT  3655020271 06-OCT-12
4       4       PROD     130474031        PARENT  3655121615 08-OCT-12
5       5       PROD     130474031        CURRENT 5588371557 19-OCT-14

RMAN> reset database to incarnation 4;

database reset to incarnation 4

RMAN> restore database;

Starting restore at 22-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10993 devtype=DISK

creating datafile fno=3 name=/opt/oracle/oradata/prod/sysaux01.dbf
creating datafile fno=4 name=/opt/oracle/oradata/prod/users01.dbf

The problem is now solved.


Writing something then immediately read when using read/write split: how to deal with replication lag

We have an issue where we write to the master table, then immediately must read ...

If the Requirement is that the read is time-critical and must have the very latest data, then read the very latest data - from the master.
This is especially true if you're generating values as part of the update and need to retrieve them, which you should ideally do within the same transaction.  (Actually, that's about the only reason I can think of that you'd need to read back the [same] data that you've just saved).


What are the different connections of MySql?

These two are in your list

NAMED PIPE
MEMMORY

Are localized to the machine. i.e. do not use the network and thus are very safe

Named pipe or memory

The others use a network

TCP - Simple connection protocol using IP. See this

HTTP PROXY, HTTP TUNNEL - Uses the HTTP protocol to get over the (sometimes) problems of firewalls and routers). Either get a computer on the firewal just to forward requests on or just encode the reequests on the HTTP protocol (web) so it can pass thru. Both use TCP below it.

SSL - Just encrypts the communiction and uses TCP forthe communication with the encrypted data.

SSH is built upon SSL but does the authentication bit.

TO OTHERS - I HAVE SIMPLIFIED IT A BIT BUT GAVE LINKS


Is it acceptable to have a default value on a foreign key

It is acceptable, and you can prevent some of your concerns by making sure the database creation script includes inserting the default values in your data, with a specific ID... that is what

   set identity_insert on

is for.

Of course, as you mention, there is a trade off, once you decide the default, you are constraining your system against some possible future requirements. But all decisions come to trade offs, and any change to such defaults should be properly evaluated on any case.

The good part about setting it as a default constraint is that it is self-documenting, and the intention is obvious enough. The scenarios you mention would show up right away as referential integrity errors.


Result Cache disabled in Oracle 12c Enterprise - BYPASS status

which returns BYPASS.

Then enable it:

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL> exec dbms_result_cache.bypass(true, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
BYPASS

SQL> exec dbms_result_cache.bypass(false, false);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_result_cache.status() FROM dual;

DBMS_RESULT_CACHE.STATUS()
--------------------------------------------------------------------------------
ENABLED

SQL>

DBMS_RESULT_CACHE.BYPASS


To understand the reason behind slowness of your current query, you need to understand the Nested Loop Join algorithm used by MySQL. Basically, for every country_code value in the country table, it is looking for all the matching rows in the statistic table. So if you look at your explain, it is roughly accessing 334*306 rows (a large number). Unfortunately, in this case, MySQL optimizer is not smart enough to stop searching for the row as soon as a match is found (because you are using Group By, and no aggregation function is being used).

Now, JOIN is unnecessary here, as it seems that you are just looking to check that the "country" has atleast one corresponding row in the statistic_detail table. This query can be written in a more performant way using EXISTS() on a Correlated Subquery. Exists() stops as soon as a matching row is found in the statistic table.

Try the following query:

SELECT cd.country_name
FROM country_detail AS cd
WHERE EXISTS (SELECT 1 FROM statistic_detail AS sd
              WHERE sd.country = cd.country_code)

If country_name values are not UNIQUE in the country_detail table, you can additionally utilize DISTINCT clause to get rid of duplicates:

SELECT DISTINCT cd.country_name
FROM country_detail AS cd
WHERE EXISTS (SELECT 1 FROM statistic_detail AS sd
              WHERE sd.country = cd.country_code)


How to avoid errors in server log when conflict fails within a transaction before retry?

You have two unique constraints, and you say that the one on shortname causes an error only rarely.
So use
INSERT ... ON CONFLICT ON (shorturl) DO NOTHING;

Then check if you got an error and how many rows were inserted.

if you got an error, prompt the user for a new shortname

if no row was inserted, generate a new shorturl

Then you will only get error messages about duplicate shortnames in your log, which should reduce the log spam to a level that is not bothersome.


Having DB Mirroring and Replication together

When you have Mirroring and Replication you usually have two different problems that you are trying to solve.  You are using the mirroring to solve a high availability problem and you are using replication to give yourself a scale out solution probably for reporting.


Can I extrapolate from the progress reporting in MyISAM ALTER?

You have redundant indexes

Instead of your ALTER TABLE

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d),
    ADD INDEX my_table_abc (a, b, c),
    ADD INDEX my_table_c (c),
    ADD INDEX my_table_ce (c, e),
    ADD INDEX my_table_d (a);

you only need to two of the indexes

ALTER TABLE my_table
    ADD UNIQUE INDEX my_table_abcd (a, b, c, d),
    ADD INDEX my_table_ce (c, e);

To verify this, download pt-duplicate-key-checker and run it against your table, and you will see its recommendation to remove those three redundant indexes. See my post Mysql might have too many indexes on how to do this.

BTW, that old post of mine you cited no longer applies because MySQL now applies all structural changes into a single temp table before loading the data into it.

As for the operation itself, please abort it and start over with my shortened version.


Hi the solution to problem:

Add Windows Firewall Exception to:

  Oracle_home\bin\oracle.exe - Oracle Database executable

  Oracle_home\bin\tnslsnr.exe - Oracle Listener


MySQL getting result using 3 Tables and case statements

MySQL does not support full outer joins, so you will have to use unions:

select phno,
if(count(phno1) = 0, 'No', 'Yes') as person,
if(count(phno2) = 0, 'No', 'Yes') as person2,
if(count(phno3) = 0, 'No', 'Yes') as person3,
from (select phno, phno as phno1, NULL as phno2 , NULL as phno3 from person
      union
      select phno, NULL as phno1, phno as phno2 , NULL as phno3 from person2
      union
      select phno, NULL as phno1, NULL as phno2 , phno as phno3 from person3
) sub
group by phno

If you only want to check for a single person, you can use Aditys code slight modifications:

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

It does work, I just tested it myself, feel free to execute my test and confirm:

DROP TABLE IF EXISTS person;
CREATE TABLE person(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person2;
CREATE TABLE person2(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person3;
CREATE TABLE person3(NAME VARCHAR(255), phno INT);

INSERT INTO person(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', 3456),('jkl', NULL);

INSERT INTO person2(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', NULL),('jkl', 4567);

INSERT INTO person3(NAME, phno)
VALUES ('abc', NULL),('def', 2345),('ghi', 3456),('jkl', 4567);

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

result:

phno  person  person2  person3
4567  NO      Yes      Yes


Single-column user defined variable based on a comparison

I'd recommend you to create static, temporary or dynamic table which contains the adjacent border ranges for each error level normalized with “constant” value to avoid additional calculations (schema):

CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )
AS
SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,
                  -- assume -1 is below min possible value of actual_error
UNION ALL
SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"
UNION ALL
SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"
-- ...
UNION ALL
SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count < 99
                  -- and 999 is over max possible value of actual_error

and then

SELECT t1.*, t2.level
FROM error_events t1, error_level_ranges t2
WHERE t1.actual_error >= t2.lower_bound
  AND t1.actual_error < t2.upper_bound
/* or alternatively, dependent by a real logic
WHERE t1.actual_error > t2.lower_bound
  AND t1.actual_error <= t2.upper_bound */

If you have a lot of error categorization schemes with different level counts and their borders, you must add a field (named similar to categorizing_scheme) into error_level_ranges table and proper additional expression like AND t2.categorizing_scheme=@current_scheme to conditions section.


hide system databases sql server management studio

You can hide the system database in SQL Management Studio following Way that I have mentioned below.

  Navigate Tools-->Options-->Environment-->General and choose "Hide
  system objects in Object Explorer".

OR

  In the SQL Server Management Studio, Right click the server and click
  "Properties". Click on "Permissions" and then select the "Public" role
  and remove "Grant" from "View Any Database".

Or You can use

USE <YourDatabase>

ALTER AUTHORIZATION ON DATABASE::<YourDatabase> to <Login>

USE MASTER

DENY VIEW ANY DATABASE TO <Login>


I would suggest you to leave MIN Server memory to DEFAULT.
Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory
usage has increased beyond the Min Server Memory setting, SQL Server won’t release any
memory below that amount.
Bob Dorr explains this settings as :

Min Server Memory

Use the min server memory setting with care.   This is a floor to SQL Server.   Once committed memory to reach the min server memory setting SQL Server won't release memory below the mark.   If you set max server memory to 59GB and min server memory to 56GB, but the server needs to back SQL Server down to 53GB SQL Server won't drop below 56GB.    When you combine this setting with locked pages in memory the memory can't be paged.  This can lead to unwanted performance behaviors and allocation failures.

Searching the web -- internet has plenty of information for max server memory

This is because, this settings is least tuned (people just set it to default), instead max memory is what is generally tuned as it is the "ceiling" for the buffer pool. A good value for max memory will ensure that windows and other processes runing on the server will have enough physical memory to perform their work without forcing sql server to trim.


Best way to backup PostgreSQL cluster before upgrade

AFAIK, the several ways to backup my cluster is : pg_dumpall, pg_basebackupp or tar.

Also, filesystem instantaneous snapshots if your filesystem/OS has this feature.

For small/medium databases it's probably OK to use any of these methods.

Choose the method which you feel most comfortable with.

With tar/cp backups remember to shutdown cluster before doing the backup.

Logical backups (pg_dump + pg_dumpall) have extra advantage of being portable (to other Pg versions). But the restore is a multi-step process, with Fs/directory it's just a plain replacement of contents.

  I tested pg_dumpall and pg_basebackup but I've seen that after doing
  an upgrade and restore, the size and files of PGDATA did changed (like
  WAL folder for example). Is it normal

Yes, completely normal. I mean - if the difference is 100-fold then probably something fishy is going on, but it's normal that after dump/restore the on-disk size is smaller.


encrypt data without performance issues?

I got a SaaS product and the data is kinda sensitive. I want to give my customers 100% security on the fact that I can't read their data.

If it's software as a service, and you do more than simply store blobs of data then give those blobs back when the customer asks, this is pretty much impossible.

You're dreaming. You can't realistically combine SaaS and the inability to see the customers' data.

(About the only outfit I know that does so is SpiderOak, and it does so by supporting almost no features - it stores stuff, and then it retrieves that stuff. That's it.)

  Is this even possible? What kind of possibilities do I got?

Few.

You can't index encrypted data unless you know the key - and the index will contain decrypted values.

You can't write a WHERE clause that filters based on the encrypted values unless you know the key. You could get the client to supply a pre-encrypted key to compare if it's a simple equality test, but you can't do b-tree index scans (requires > and < operators), or much else of interest.

You can't aggregate values unless you know the key.

You really can't do anything useful with them unless you know the key.

In theory you can use homomorphic encryption to allow computations on encrypted values. In practice it's incredibly limited, slow, and impractical, as well as weaker than other crypto. In practice most real-world homomorphic systems are useful for sums and that's about it.


Select Statement to generate result, for group by per second and link with Table

How about creating a table holding all times for a day and using it to drive the query?

In this simple demo, I set up a table similar to yours, load it with data, then use the dbo.timeLookup table to drive it.  The example assumes you are only working with one day, but it could be extended if your timespan needed to span multiple days:

USE tempdb
GO

SET NOCOUNT ON

-- Create a large table
IF OBJECT_ID('dbo.user_table') IS NOT NULL DROP TABLE dbo.user_table
CREATE TABLE dbo.user_table (
    rowId INT IDENTITY PRIMARY KEY,
    someData UNIQUEIDENTIFIER DEFAULT NEWID(),
    session_datetime DATETIME DEFAULT GETDATE(),
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
    ts ROWVERSION
    )
GO

IF OBJECT_ID('dbo.timeLookup') IS NOT NULL DROP TABLE dbo.timeLookup
CREATE TABLE dbo.timeLookup (
    [time]      TIME ,
    [hour]      AS DATEPART( hour, [time] ),
    [minute]    AS DATEPART( minute, [time] ),
    [second]    AS DATEPART( second, [time] ),

    CONSTRAINT PK_dbo_timeLookup PRIMARY KEY ( [time] ),
    CONSTRAINT UK_dbo_timeLookup UNIQUE ( [hour], [minute], [second] )
    )
GO

-- Load up some dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.user_table ( session_datetime )
SELECT DATEADD( second, rn % 86400, '09/29/2014' )
FROM cte
WHERE rn % 33 != 0
GO

-- Suggested index
CREATE INDEX _idx ON dbo.user_table ( session_datetime )
GO

-- Populate the timeLookup table
;WITH cte AS (
SELECT TOP 86400 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.timeLookup ( [time] )
SELECT DATEADD( second, rn, '1 Jan 2014' )
FROM cte
GO

-- Remove certain seconds for the demo
DELETE dbo.user_table
WHERE  CAST( session_datetime AS TIME ) In ( '15:10:08', '15:10:09' )

-- Have a look at the sample records loaded
--SELECT MIN(session_datetime) xmin, MAX(session_datetime) xmax, COUNT(*) records
--FROM [User_Table]

-- Main query
SELECT tl.[time], COUNT(ut.session_datetime) AS records
FROM dbo.timeLookup tl
    LEFT JOIN dbo.user_table ut ON tl.[time] = CAST( ut.session_datetime AS TIME )
        AND ut.[session_datetime] Between '2014-09-29 00:00:00.000' And '2014-09-29 23:59:59.999'
WHERE tl.[time] Between '15:10:06' And '15:10:10'
GROUP BY tl.[time]

Result:


Killed SPID on xp_LogInfo not rolling back and growing tempdb

Thanks for the assistance on this one,
Although the kill/rollback wasn't showing to be progressing it looks as though it was and after 45 hours the process completed and the space returned to my TempDb
Thanks again


How to configure MySQL Router on two application servers?

I managed to resolve the issue and I am posting what resolved it for me in case someone else might run into this dilemma.

The resolution was to run mysqlrouter with the --bootstrap option on both app servers but I was missing the --name parameter (which is a bootstrapping option). The --name parameter gives a symbolic name to the router instance and since I was not passing on that parameter it gave it a blank name or ' '. Like ' ' router instance already existed then when trying to bootstrap on server 2 it was failing because ' ' router instance already exited. The trick here is to give each router instance a different name. All the examples that I found online only show you how to configure the router for a single instance not for a double instance that is why I was making the mistake on not giving it a name.

Anyway, I hope this helps someone.