Keeping service up while taking drive offline in clustered instance I have clustered instance with two (2) data drives on it. Drive J has around 50 databases on it and drive K has about 15. One of the system administrators was trying to increase the size on K by 20 GB and he instead increased it by 200. Now he needs to take the accidental 180 GB back but the drive will need to come offline briefly. Is it possible to take the K drive offline, along with the databases on it, without taking the whole instance down?

I believe it's possible but I'm concerned about the SQL service cluster resource's dependence on the drive in question. Since the drive is setup as a dependence, would I need to remove the dependence first, then take the drive offline without affecting the SQL Server service status?

Thank you.

I've never tried this before but thinking this out logically, these are the things I would do (in no specific order):

To answer your question yes, exclude drive K as a dependency as a cluster resource (this should prevent it from causing the cluster to go down when you need to remove it)
Manually take those databases offline that are stored on drive K
(I usually set the database to single user mode to make that happen)
Triple-check that nothing else is stored on drive K (like any text log files, etc) that may be used by SQL Server (make sure you dont have any hidden files too)
Take drive offline, rescind the space, then bring it back online


use of coalesce to check for all variables being null How can I use coalesce to check for all variables with different type precedence to all be NULL without encountering errors.

DECLARE
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@StateCode CHAR(2) = NULL,
@CountyCode CHAR(3) = NULL,
@ProducerName VARCHAR(64) = null,
@TaxID VARCHAR(9) = null,
@Farm INT = null

SELECT CASE WHEN COALESCE(
    @StartDate,
    @EndDate,
    @StateCode,
    @CountyCode,
    @ProducerName,
    @TaxID,
    @Farm) IS NULL
THEN 'yes' ELSE 'no' END

Output: 'yes'

adding SELECT @StateCode = '22' gives this error

Msg 241, Level 16, State 1, Line 94
Conversion failed when converting date and/or time from character string.

this is valid

SELECT CASE WHEN
   COALESCE(@StartDate, @EndDate) IS NULL
   AND COALESCE(@StateCode, @CountyCode, @ProducerName, @TaxID) IS NULL
   AND @farm IS NULL
THEN 'yes' ELSE 'no' END

I'd love to know if there's another clever way to accomplish this in a terse manner so I don't have to remember to separate by type family.

As long as all the variables involved are of datatypes compatible with sql_variant (they are in this case - basically no LOB datatypes, CLR types, or user defined datatypes) then you can use

SELECT CASE WHEN COALESCE(
    CAST(@StartDate as sql_variant),
    @EndDate,
    @StateCode,
    @CountyCode,
    @ProducerName,
    @TaxID,
    @Farm) IS NULL
THEN 'yes' ELSE 'no' END

It is only necessary to cast one of the arguments as COALESCE

  Returns the data type of expression with the highest data type
  precedence.

And sql_variant has a very high datatype precedence (only beaten by user-defined data types and they would prevent the method from working anyway).

Personally I don't find this any more understandable then just finding the conjunction of the individual IS NULL results though.


Do SELECT (without ORDER BY) return the same message as RECEIVE? My current client needs to be able to PEEK messages in a SQL Server Service Broker queue. The current implementation uses RECEIVE and rollback to peek.

As that generates writes to the transaction log I'm wondering if a SELECT TOP(1) would return the same row as the RECEIVE command?

There are several writers but just one reader to that queue.

No. RECEIVE constructs a query AST that cannot be constructed with SELECT. RECEIVE has to guarantee EOIO and conversation group locking, SELECT does not.

Service Broker has a built-in 'peek': GET CONVERSATION GROUP. This will lock the next available conversation group, on which you are guaranteed to get a message if you issue a RECEIVE within the same transaction. I strongly discourage the use of it.

Anything else you're trying to do is broken and you need to change the application to stop doing it. Always issue a RECEIVE and react to the returned messages result set.


Do I need to have two queues for Service Broker? I am just starting with Service Broker and looking at the examples in blogs, most have a sending and receiving service with separate queues.

Looking at the SEND TO command (to send a message) I need to reference a CONVERSATION. And it seems that a CONVERSATION has FROM SERVICE and TO SERVICE parameters.

Is it wrong or bad design to send from/to the same service, with a single queue? I tried it and it seems to work.

My SQL table will have code in a trigger to send a simple empty message. This will notify a SignalR client that the table has changed, and send out new data. For such an application, surely a single queue and single service is okay?

You do not have to, but is a good practice.

You must be aware that your 'from' service will also get messages in it's queue, even if you never send any. There are system messages, most obvious ones being the EndDialog and Error messages. You have to handle at least those. So your code that services the queue must be able to properly handle the messages sent to the initiator (the 'from' service). With a single queue for both services you will get a mix of messages, some for initiator some for target.

  will have code in a trigger to send a simple empty message

Make sure you do not do Fire and Forget, read How to prevent conversation endpoint leaks to understand why is important.


SA login history in sql Is there any PowerShell script to get the SA log in history?  I need to do an audit on SA login.

First of all you need to make sure you are auditing both successful and failed logins

  EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 1

after a restart you can read the information from the SQL Server errorlog

$server = get-item SQLSERVER:\sql\localhost\default
$server.ReadErrorLog() | ? { $_.processInfo -eq 'Logon' -AND $_.Text -match 'sa'}

or grab it from the application log


What is the difference between NORECOVERY and RECOVERY when restoring database? Can anyone explain the main differences?

Any help appreciated.

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them forward.
RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.

SOURCE

Restore Sequence and Understanding NORECOVERY and RECOVERY


Two Different Sources Taking Transaction Log Backups I have inherited a database server with SQL Server 2005.  The databases have full recovery mode with hourly transaction log backups.  There are two sources taking full backups overnight and hourly transaction log backups: Tivoli Data Protection (TDP) which is part of IBM's Tivoli Storage Manager (TSM) as well as a regular SQL Server Agent job.

If a disaster occurred and I needed to recover the database from backup, would I be missing data?

I was thinking that if the SQL transaction log backup fires off, it will mark the committed transactions in the log file as ready to be overwritten.  After that happens, users will commit more transactions.  When TSM/TDP comes in, it will mark the committed transactions in the log as ready to be overwritten.  If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.

If a disaster occurred and I needed to recover the database from backup, would I be missing data?

As long as all of the backups are in tact, no.

The transaction log chain is not broken, and point-in-time recovery is possible.

It's just that the backups that constitute a complete transaction log chain are not all in the same location.

Having said that, I would recommend consolidating the systems so that only one of them is responsible for taking backups. While it sounds like your situation was set up that way unintentionally, if you want to create a redundant copy of a backup at an alternate location, there are many options to do so, but that's beyond the scope of the question here.

  If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.

Correct, you would not be able to restore from the TDP backups alone, or the SQL Agent backups alone.

After restoring the latest full backup, you would need to restore all of the log backups between that point-in-time and the current point-in-time. This will involve combining the set of backups from both locations, and restoring them in the correct sequence as each log backup will contain a distinct portion of the backed up transaction log.


SQL Server - SQL Plan Management for baseline I am a .Net developer. I have a database upgrade coming up (from SQL Server 2005 to SQL Server 2012). The database team will be handling the migration. But I have seen forums posts saying that after migration, the queries are taking too long to execute even though amount of data and number of users are almost same. And one main reason is change in execution plan between these servers. Reference: Performance problem after migration from SQL Server 2005 to 2012

So, I am considering possibility of storing the actual execution plans from my current production server itself. In Oracle, I could find a feature for baselining:-

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines
Using SQL Plan Management

In SQL Server, what is the usual practice for achieving this?

Note: I referred Creating a baseline for SQL Server, yet it doesn't say anything about baselining execution plan

You would just pull the execution plans for the most relevant queries from the plan cache.  I would query sys.dm_exec_query_stats, and join to sys.dm_exec_query_plan and sys.dm_exec_sql_text to get the plan and query text, respectively. Within sys.dm_exec_query_stats you can pull queries with the highest IO, CPU, execution count, etc. - whatever is most important to you.

Looking to the future, SQL Server 2016 introduces the Query Store:

Monitoring Performance By Using the Query Store


Hash Match Spills and Key Lookup causing major performance issue I have updated the statistics on all the indexes but this query is still taking close to 40 seconds to execute. I need it to be under 20. I am not a DBA and very new at reading execution plans. I would appreciate any help I can receive that would help me improve the performance of this query.

https://www.brentozar.com/pastetheplan/?id=S1NotFa6N

Your query only uses about 2 seconds of CPU time. You can view that in the root node of the execution plan. It spent about 30 seconds waiting on various things.

7915 ms of that wait time is for the ASYNC_NETWORK_IO wait type. This wait type occurs when SQL Server is ready to send more data to the client but the client hasn't indicated that it's ready to receive more data yet. This can commonly be seen when viewing large result sets in the SSMS results grid. If you're seeing this same type of wait in your application then look at your code that processes the result set that's returned and check if there are unnecessary columns getting returned. Otherwise you may find it helpful to discard the result set to get a more accurate measurement of query performance. As is, you lose 8 seconds out of your 20 second goal.

You spill a small amount of data with those hash joins, yet query execution dramatically slows down as a result. I can arrange a similar result on my local machine:

The spill and join take about 0.289 seconds for me. That's about 33 times faster than what happens on your server. I understand that you may not have any control over what cloud platform that you use. However, the time sink of tuning queries by hand needs to be balanced against other solutions. The query only spends 2 seconds of CPU time. If you had better hardware then it could easily finish in under 20 seconds with no code changes required. To be completely frank with you, if someone gave me a VM with performance as poor as what you're seeing I would refuse to use it.

If you can't get better hardware then you should be able to test query performance without the spill by adding a MIN_GRANT_PERCENT = 99 query hint. If performance significantly improves then try to improve the cardinality estimates going into the hash joins. The reason that you see the tempdb spills is that the memory grant is too small because the actual number of rows is about 20X larger than the estimated number of rows:

This complicated predicate may be contributing to the problem:

@Now BETWEEN OPL.StartDateUTC AND COALESCE(DATEADD(MILLISECOND, -3, CAST(CAST(DATEADD(DAY, 1, OPL.EndDateUTC) AS DATE) AS DATETIME)), DATEADD(MILLISECOND, -3, CAST(CAST(DATEADD(DAY, 1, @Now) AS DATE) AS DATETIME)))

One thing to try is putting the results of the join of the Organization and the OrganizationProductLanguage tables into a temp table. With a temp table, the query optimizer will know that you're getting 12 rows from that step instead of 1.41312. That alone may increase the estimates enough to avoid the tempdb spills.

If performance continues to be unacceptable after fixing the tempdb spills then try getting rid of some of the key lookups by including additional columns in the nonclustered indexes already in use. I expect most of the impact to come from adding the
ActivatedDateUTC and InactivatedDateUTC columns to [ProductAsset].[UX_ProductAsset]. Less benefit should be expected from adding DataValue to [AssetCacheData].[IX_AssetCacheData]. This may improve query execution time more than typically expected because this server applies such a large penalty to I/O.


How can I use SQL Profiler to capture a single stored procedure Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this procedure so that I can load test it using realistic parameter data.

I've tried the following in a new SQL Profiler trace:

Events Selection > Column Filters > Text Data > LIKE: 'exec sprocName%'
Events Selection > Column Filters > Text Data > LIKE: 'exec sprocName'

Neither of above configurations capture my procedure. I've also tried to remove the procedure name with the following configs:

Events Selection > Column Filters > Text Data > LIKE: 'exec%'
Events Selection > Column Filters > Text Data > LIKE: 'exec'

Above configs do not capture any procedures.

Finally, I tried to execute the same profile without any column filters and I can confirm that it captures all SQL queries sent to SQL Server.

I've considered a workaround of capturing ALL stored procedure execs and post-filtering to my sproc of interest, using SQL queries or Excel Power Query.  However, the high frequency of sproc executions in the environment I want to model, make this unfeasible.

I would suggest to use Extended Events instead of Profiler as the Extended Events has less overhead and more events to capture compare to Profiler.

In your case, create a new session in Extended Events by selecting rpc_completed event. For detailed steps..


Running remote stored procedure with DDL locally Sorry if my question is redundant, I searched Stack Exchange and the internet for hours but do not have a definitive answer to my question.

Database: SQL Server 2017 + Azure

The goal is to build a BI infrastructure

I have multiple (20+) database servers (clients) which all have one common database in the cloud (Azure). All these clients should be exactly the same when these are on the same DB version. To make life easier when doing an update (almost weekly, tuning), I want my 20+ databases to update automatically.

I thought of the following solution:

I create a table in the Azure DB which has the most recent client DB version (e.g. v1.2) check
If I have an update script, I will put this as a stored procedure on the Azure DB. check
I schedule a task on the client which checks the most recent client DB version once a day and compares whether an update is needed. check
If yes -> run the "update": this is the stored procedure on the Azure DB which have to run on the client DB. oh no

Here I get into trouble, running the stored procedure which resides on the Azure DB obviously runs the stored procedure on the Azure DB. I don't know how I can run the stored procedure of the Azure DB on the client DB.

Motivation: I thought it would be clever to do it this way so I only have to change the content of the stored procedure and all clients would update itself when getting a connection with the Azure DB.

Also, is this the correct way of tackling this problem? Any help is much appreciated!

If you need more information, please let me know.

I don't have access to Azure to verify this all works without tweaking, but here is something you can try.

Here is some information on creating a linked server to Azure that might help you - I have not tested that part - https://stackoverflow.com/a/41145784

On Azure, create the stored procedure that contains the commands you want to execute on each client machine.  My example simply creates a TestUpdateTable and inserts the current timestamp.  I'm sure your stored procedure would be much more involved.

drop procedure if exists [dbo].[UpdateClient];
go
create PROC [dbo].[UpdateClient]
AS
begin transaction
drop table if exists dbo.TestUpdateTable;
create table dbo.TestUpdateTable (TextCol varchar(max));
insert into dbo.TestUpdateTable(TextCol)
    select convert(varchar(30),sysdatetime());
commit

In the following example, I'm using a linked server connection to a server I call SqlCompare.  I'm also referencing a database called SCUTILITY on that linked server.  This is where I put the central stored procedure.  This simulates what would be in Azure.

On each client machine, run the following dynamic TSQL

Declare @cmd nvarchar(max)

DROP PROCEDURE IF EXISTS dbo.UpdateClient;

SET @cmd = (
        SELECT *
        FROM openquery(SqlCompare, 'exec(''use scutility;select object_definition(object_id(''''dbo.UpdateClient''''))'')')
        )

PRINT @cmd

EXEC (@cmd)
GO

The intent is to drop the existing local client UpdateClient stored procedure first.  Then retrieve the text definition of the Azure stored procedure by using object_definition and put the results in a variable (@cmd).  Issuing EXEC (@cmd) will create the remote stored procedure locally on each client.

Now, execute the UpdateClient stored procedure locally on the client machine.

EXEC dbo.UpdateClient

The execution of the UpdateClient stored procedure just created the table TestUpdateTable and populated it with the current timestamp.  Again, your stored procedure would be more involved.

Now, you could issue select * from TestUpdateTable on the client machine and you should get a result back


Need to optimize simple SELECT query for InnoDB table with 2million rows I'd like to make this query the fastest possible, since it's called really often.

SELECT login_events.id
FROM login_events
WHERE (
  DATE(created_at) >= DATE(CURRENT_DATE)
  AND person_id = 1
)
LIMIT 1

It's running on a 157.7mb InnoDB table (says navicat), with ~2million rows and indexed on [created_at, person_id].

Using EXPLAIN I see it's using the index, but it says "Using where; Using index;". What can I do to make this the fastest possible? Would switching to MyISAM gain me anything?

First, what you need to focus on are the three fields in the query

id
created_at
person_id

The index you have (created_at,person_id) will make the query do an index scan across all the days of created_at after CURRENT_DATE looking for the person_id.

SUGGESTION #1 : You will definitely need a different index

MyISAM

If login_events is MyISAM, this is the index you need

ALTER TABLE login_events ADD INDEX person_date_ndx (person_id,created_at,id);

This changes the query because the query will look for the specific person_id and scan all days for person_id 1 only. The reason id is included in the index ? The query will retrieve the id from the index only file rather than the table. That way, all 3 fields are retrieved from the index file instead of 2 fields from the index and 1 from the table.

InnoDB

If login_events is InnoDB, this is the index you need

ALTER TABLE login_events ADD INDEX person_date_ndx (person_id,created_at);

The reason I recommend this is the same, but you do not need to include id. Why? All index pages include an index point back to the clustered index so retrieval of an index will intrinsically access the row anyway, thus accessing id. Adding id to the index would simply be redundant.

SUGGESTION #2 : Change the Date Comparison

From the expression

DATE(created_at) >= DATE(CURRENT_DATE)

I can tell that created_at is either DATETIME or TIMESTAMP.

The expression forces the query to convert every row's DATETIME value of created_on into a DATE.

Therefore, instead of

SELECT login_events.id
FROM login_events
WHERE (
  DATE(created_at) >= DATE(CURRENT_DATE)
  AND person_id = 1
)
LIMIT 1

express the date comparison as a time comparison starting from midnight of today

SELECT login_events.id
FROM login_events
WHERE (
  created_at >= (DATE(NOW()) + INTERVAL 0 SECOND)
  AND person_id = 1
)
LIMIT 1

CAVEAT

Since the table is so small, either storage engine would be fine. I would give the edge to MyISAM.

Give it a Try !!!


What do the HT* wait types mean? (HTREPARTITION, HTDELETE, ...) A big query is running right now. The result of a batch mode hash join that spills to disk is streamed into a temp table using select into. The query is showing wait types HTDELETE and HTREPARTITION occasionally. The query is not using columnstore indexes.

I'm quite sure that these wait types are normal for batch mode hash joins that spill to disk. I'm trying to understand what these wait types mean out of curiosity. Maybe this insight can help optimize the query or find problems. So what do these wait types mean and under what circumstances are they expected?

No columnstore indexes. I'm using the left join ZeroRowCsTable on 0=1 trick to enable batch mode for row mode tables.

Community Wiki answer generated from a comment on the question by ypercube

From an MSDN Forums thread:

  HTDELETE

  SQL Server 2014 now uses one shared hash table instead of per-thread copy.
  This provides the benefit of significantly lowering the amount of memory
  required to persist the hash table but, as you can imagine, the multiple
  threads depending on that single copy of the hash table must synchronize with
  each other before, for example, deallocating the hash table. To do so, those
  threads wait on the HTDELETE (Hash Table DELETE) wait type.

(This only applies to hash operators that execute in batch mode)


Sometimes the updated rows are not locked within instead of update trigger Here is as small repro:

create table dbo.t (id int primary key, v int);
insert into dbo.t values (1, 1), (2, 2);

create table dbo.s (id int primary key, v int);
insert into dbo.s values (1, 10);
go

create trigger dbo.tr_t__iou
on dbo.t
instead of update
as
begin
 set nocount on;

 exec sp_lock @@spid;
end;
go

update dbo.t set v = 10 where id = 1;

update t
 set
  v = 10
from
 dbo.s s join
 dbo.t t on t.id = s.id;

update t
 set
  v = 10
from
 (values (1, 10)) s(id, v) join
 dbo.t t on t.id = s.id;
go

drop table dbo.t, dbo.s;
go

sp_lock within the trigger reports U-key lock on the affected row in the first and the last cases, but in the second case there is no lock at all, how can it be explained?

When the update statement qualifies for a trivial plan, the optimizer rule that expands the instead-of trigger part of the statement (ExpandInsteadOfTriggerUpd) includes the part of the plan that reads from the base table. This rewrite includes adding an UPDLOCK hint to the base read. As usual, the UPDLOCK hint means that update locks are taken and held to the end of the transaction.

When the statement does not qualify for a trivial plan, the ExpandInsteadOfTriggerUpd rule only rewrites the write-cursor portion of the plan, leaving the base table reads untouched - no UPDLOCK hint is added.

My guess is that this trivial plan behaviour exists to avoid a deadlock scenario.


MySql remove counted value from another count I'm trying to remove a counted value from another counted value but I can't get it to work.

I'm using MySql

This is my SQL:

SELECT `GameID`,
count(case `Liked` when 1 then 1 else null end) as likes,
count(case `Liked` when 0 then 1 else null end) as dislikes
FROM `rating`
SET likes = likes + dislikes
GROUP BY `GameID`
ORDER BY likes DESC

For each separate game I'm counting the amount of likes and dislikes this works fine. But I'm wondering if its possible to remove the amount of dislikes from the amount of likes like this:

SET likes = likes-dislikes

This doesn't seem to work.

Does anyone have any ideas how to do this?

Thanks for reading/helping!

There are multiple ways to achieve that

Repeat the expressions in the new column definition:

SELECT `GameID`,
count(case `Liked` when 1 then 1 else null end) as likes,
count(case `Liked` when 0 then 1 else null end) as dislikes,
count(case `Liked` when 1 then 1 else null end)-count(case `Liked` when 0 then 1 else null end) as diff
FROM `rating`
GROUP BY `GameID`
ORDER BY diff DESC

Wrap it in another query:

select GameID, likes, dislikes, likes-dislikes AS diff
from (
    SELECT `GameID`,
    count(case `Liked` when 1 then 1 else null end) as likes,
    count(case `Liked` when 0 then 1 else null end) as dislikes
    FROM `rating`
    SET likes = likes + dislikes
    GROUP BY `GameID`
) as tmp
ORDER BY diff DESC


Avoid joining to the same table multiple times I have two tables:

CREATE TABLE one (
  id int4 primary key,
  p_id int4,
  k_id int4,
  c_id int4
);

CREATE TABLE two(
 id int4 primary key,
 p_id int4,
 k_id int4,
 t_id int4,
 pos int4
);

Table one: is a join table, I'm using it in this report only because of the c_id and to limit the number of row.

Table two: contains my data, a p_id, k_id and t_id can have many pos, so I'm grouping them and using min(), to get the min pos. I've tried some ways to get all my data for a k_id and p_id here is a way (with this solution I was not able to order by pos), so I end up doing this (see fiddle):

SELECT
  o.p_id,
  o.k_id,
  min(t.pos) as t_pos,
  min(t1.pos) as t1_pos
FROM one o
  LEFT JOIN two t ON t.p_id = o.p_id
    AND t.k_id = o.k_id
    AND t.t_id = 1
  LEFT JOIN two t1 ON t1.p_id = o.p_id
    AND t1.k_id = o.k_id
    AND t1.t_id = 2
WHERE o.p_id = 1 AND o.c_id = 1
GROUP BY 1, 2
LIMIT 1

This solution is 2-3 times slower then @Erwin Brandstetter's solution from the other post, but sorting t_pos, t1_pos is possible and also getting all data where type_id = 1 and pos = 1 for example.

Is there a better way of achieving the same output and improve the speed and be able to sort my data (t_pos and t1_pos columns)?

Try how good this works for you

Converted left join to one inner join for table Two for both t_id's. And on computing min, using case statement to split to two columns based on the value t_id. What we save here is scanning the table two time and an inner join compared to left join

SELECT
    o.p_id,
    o.k_id,
    MIN(case when t.t_id = 1 then t.pos else null end) AS t_pos,
    MIN(case when t.t_id = 2 then t.pos else null end) AS t1_pos
FROM
    one o
INNER JOIN two T ON t.p_id = o.p_id
AND t.k_id = o.k_id
AND t.t_id =any ( 1,2)
WHERE
    o.p_id = 1
AND o.c_id = 1
GROUP BY
    1,
    2
LIMIT 1


LEFT JOIN with WHERE (Microsoft Access SQL) Given following 2 tables a, b:

table a

ID job
1  farmer
2  physician
3  bank clerk

table b

bID ID lang job
1   1  en   farmer
2   2  en   physician
3   3  en   bank clerk
4   2  de   Arzt
5   1  fr   paysan

Query

SELECT a.ID, a.job, b.job FROM a LEFT JOIN b ON a.ID=b.ID WHERE b.lang='de'

wanted:

a.ID a.job        b.job
1    farmer       -
2    physician    Arzt
3    bank clerk   -

actual result:

2    physician    Arzt

What's wrong with the LEFT JOIN? Do I need some (())?

In your query left join is performed only using only the condition a.ID=b.ID and then results are filtered based on condition b.lang='de'. You need to add both conditions on the left join:

SELECT a.ID, a.job, b.job
FROM a LEFT JOIN b
ON (a.ID=b.ID and b.lang='de')


Restore .mdf data file for a File Group in SQL Server I have a database with three filegroups and each filegroup hosting one datafile. One of the secondary filegroups XYZ with data file xyz.ndf got updated with wrong/bad information in various tables under this filegroup. Before these various bad changes were made I took a backup of the xyz.ndf file using SSMS --> Backup Component --> Files and Filegroups.

I have an urgent need to restore only xyz.ndf file using the backup without impacting the operation of the  remaining datafiles

For this I ran the query below

RESTORE DATABASE AAAA FILEGROUP='XYZ' FROM dISK='C:\MyLoc\XYZ_Backup.bak'
  WITH RECOVERY

I get the message below.. and there were no log backups after my original backup. What should i do to fix this?

  Processed 48 pages for database 'AAAA', file 'xyz' on file 1. The roll
  forward start point is now at log sequence number (LSN)
  40000000032600001. Additional roll forward past LSN 40000000034300001 is required to complete the restore sequence. RESTORE DATABASE ...
  FILE= successfully processed 48 pages in 0.054 seconds (6.944
  MB/sec).

The only way to restore a single file group is to run in all the logs so it's up to date with the rest of the files.  This would of course run in the bad data modification you're trying to prevent but it's necessary to ensure consistency throughout the database.  You'll need to restore it to another location and move the data over.

http://msdn.microsoft.com/en-us/library/ms178099%28v=SQL.105%29.aspx


Do I need tweak something for this MySQL setup? I'm running a fairly popular website with the following stats and setup.

stats: ~250,000 pageviews per day, rendering ~47 queries / second to MySQL setup: the MySQL instance is running alone on a Linode512 on Debian 6.0. Nothing else runs there, for performance reasons. Using MyISAM and the ini for "large" mysql.

I see the following figures right now from MYSQLREPORT.

Please advice. The site performance is quite OK but I'm not sure what the 256 MB key buffer indicates and if that is tannable in the long run or not. I'm not very good at understanding the inner workings of MYSQL. Also, I can't figure out why the QC is being hit so low i.e. why isn't it using all of the QC.

-- Key --
Buffer used   226.52M of 256.00M  %Used:  88.49
  Current     256.00M
 Write hit      64.93%
 Read hit      100.00%

-- Questions --
Total         139.38M    47.0/s
  QC Hits      51.28M    17.3/s  %Total:  36.79
  DMS          41.99M    14.2/s           30.12
  Com-         30.75M    10.4/s           22.06
  COM-QUIT     15.36M     5.2/s           11.02
  +Unknown     10.68k     0.0/s            0.01
Slow 10 s         127     0.0/s            0.00  %DMS:   0.00  Log:  ON
DMS            41.99M    14.2/s           30.12
  SELECT       35.92M    12.1/s           25.77         85.56
  UPDATE        3.15M     1.1/s            2.26          7.50
  INSERT        2.66M     0.9/s            1.91          6.33
  DELETE      256.47k     0.1/s            0.18          0.61
  REPLACE           0       0/s            0.00          0.00
Com-           30.75M    10.4/s           22.06
  set-option   15.38M     5.2/s           11.03
  change-db    15.36M     5.2/s           11.02
  show-fields   2.70k     0.0/s            0.00

-- SELECT and Sort --
Scan            1.21M     0.4/s %SELECT:   3.38
Range         175.99k     0.1/s            0.49
Full join          24     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan       2.51M     0.8/s
Sort range      4.04M     1.4/s
Sort mrg pass 177.95k     0.1/s

-- Query Cache --
Memory usage    2.78M of  16.00M  %Used:  17.38
Block Fragmnt  23.23%
Hits           51.28M    17.3/s
Inserts        33.35M    11.2/s
Insrt:Prune   68.97:1    11.1/s
Hit:Insert     1.54:1

-- Table Locks --
Waited          1.19M     0.4/s  %Total:   1.74
Immediate      67.07M    22.6/s

-- Tables --
Open              227 of  256    %Cache:  88.67
Opened          1.16k     0.0/s

-- Connections --
Max used           27 of  151      %Max:  17.88
Total          15.36M     5.2/s

-- Created Temp --
Disk table      2.16M     0.7/s
Table           2.30M     0.8/s    Size:  16.0M
File          327.26k     0.1/s

-- Threads --
Running             7 of    7
Cached              1 of    8      %Hit:  99.88
Created        19.02k     0.0/s
Slow                0       0/s

-- Aborted --
Clients             4     0.0/s
Connects           26     0.0/s

-- Bytes --
Sent          958.38M   323.0/s
Received      954.98M   321.9/s`

Btw, I tried posting this question in the DBA forums of stack exchange prior to here but it's like a grave in the other forums. Appreciate the help.

Your questions come down to, basically, "Do I have a problem" and you go on to say at the moment no but not sure about the future.

  The site performance is quite OK but I'm not sure what the 256 MB key buffer indicates and if that is tannable in the long run or not.

I don't think anyone can suggest that there is or is not a problem here based on the fact that performance is currently ok but you are worried about long-term performance concerns.  Without knowing what sort of growth you are expecting nobody can give more than a guess.

So I am going to go a different approach and suggest that you need to start with a framework for determining whether you need to start thinking about optimizing/upgrading.  This seems the best way at this current point.

Your database looks like it fits in RAM.  Is it growing?  How fast?  How big is it?  When will it exceed 250M in size?  When will it exceed your server's RAM?
How many concurrent connections do you typically have?  How fast is this growing?

Once you have this sort of data  (and you really need to start with hard numbers here!) then it should be pretty straight-forward to determine whether you need to be thinking about capacity or performance tuning.  However as long as performance is good right now, then you have time to gather data, evaluate, and plan.

UPDATE BASED ON FEEDBACK

So the db doesn't fit in RAM.  It is apparently a virtualized server with only 512MB of RAM.  I don't think you need a larger system yet but it is worth keeping your eyes on the performance numbers.

The cache is going to be the MySQL page cache.  This is used to store frequently used parts of the database in memory.  Note that ALL of your select and update queries are currently hitting the cache.  Your inserts are not as expected.  This means you have enough ram reserved for the MySQL page cache to hold everything being queried in RAM.  While I had assumed it was the full db, actually unqueried portions of the db would not need to fit in RAM for these numbers.

You have a few options.  You could decide to get more RAM (and run tuner programs as @RolandoMySQLDBA suggests) when your cache utilization hits 90% or so.  You could upgrade things now with the idea you may likely need it.  Or you could be more conservative and wait for some read queries not to hit the cache on a consistent basis.  It depends on what you see as necessary performance.


Query plan cache bloated by ad-hoc queries, even with "Optimize for Ad-hoc Workloads" I've been noticing what I thought to be unusual issues with our query plan cache, where the plans in the cache were never more than a day old.

Through running the following query (courtesy of Kimberly Tripp), it showed that the majority of plans (4.5Gb of 6Gb cached plans or 44813 of ~50000), were Ad-hoc queries that had a use count of 1.

SELECT objtype AS [CacheType]
    , count_big(*) AS [Total Plans]
    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
    , avg(usecounts) AS [Avg Use Count]
    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

I've determined the problem query (dynamic, with an EXEC, of course...), which is rather hideous, but also complicated to just 'fix', so I was looking into immediate improvements that could be made.

The instance is already set to use Optimize for Ad-hoc Workloads, however the CacheObjType from sys.dm_exec_cached_plans are all Compiled Plan rather than Compiled Plan Stub.

When using the Ad-hoc Workload mode, should the plans not be Compiled Plan Stub until their usecounts are greater than 1? Or is that not how this works?

There's also a refcounts field that no one seems to refer to when talking about Adhoc queries. The refcounts is always 1 when the type is Compiled Plan Stub and 2 when type is Compiled Plan.  Even through reading BOL, I'm not entirely sure what this field means. Can someone clarify?

According to this, the second run of an ad hoc batch removes the stub (which was used only once) and creates and caches the plan (using it for the first time).

I also haven't seen many references to refcounts other than it being a count of references by cache objects. Adhoc Compiled Plan objects can still have a refcount of 1, so it's not exclusively caused by the persistence of the plan.


Inconsistent presence of view in Postgres system tables I am perplexed here. I have two environments, let's say TEST and PROD.

In both environments' Postgres DBs there is a view foo_view visible in my DB client (DBeaver). However, only in PROD can I find that view in the system tables:

select * from information_schema.tables WHERE table_name = 'foo_view';
select * from information_schema.views WHERE table_name = 'foo_view';

When executed in PROD, the above SQL returns data that corresponds to the client rendering but not in TEST. I was not the DBA who introduced that view in either of the instances.

I am looking for an explanation as to which possible scenarios could lead to that view not being in the system tables but still visible in the client UI.

The views in information_schema are subject to permission checks.

Specifically, the definition information_schema.tables has this filtering clause at the end (use \d+ information_schema.tables in psql to see this):

  [... long definition cut...]
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"]))
  AND NOT pg_is_other_temp_schema(nc.oid)
  AND (pg_has_role(c.relowner, 'USAGE'::text)
   OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text)
    OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

information_schema.views has similar restrictions.

If the user issuing the following query

 select * from information_schema.tables WHERE table_name = 'foo_view'

has no privilege whatsoever on foo_view, it's to be expected that this query returns 0 row.

If a user sees different results on this query between databases PROD and TEST  , it's presumably because that user has different permissions on this object between databases.

As for the client program, DBeaver, it can show all objects in both databases because presumably it skips these permission checks by addressing directly pg_class, pg_type, pg_namespace and so on (the postgres-specific underlying tables implementing the catalog), like for instance psql, rather than the views in information_schema.

Also when you're connected as a superuser, you should see everything in all cases.


Is index on foreign key used? (No difference in EXPLAIN.) Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table.  I'm not sure if the index is in fact being used and it simply can't be seen in the EXPLAIN output, or if in fact for some reason the index isn't useful (either in this particular case or in general).

For example:

Table 1:
ex_cat, ex_name
(Primary key on ex_cat.)

Table 2:
cat, ex_cat, data
(Composite primary key on cat, ex_cat; separate
 non-unique index on ex_cat?)

I am running the following query:

SELECT *
FROM table1 INNER JOIN table2
     ON table1.ex_cat = table2.ex_cat
WHERE table2.cat = 'value'
ORDER BY table2.data;

It gives me the following EXPLAIN output, regardless of whether I have an index on the ex_cat column in table 2 or not:

id  select_type  table   type    possible_keys  key      key_len  ref             rows  Extra
1   SIMPLE       table2  ref     PRIMARY        PRIMARY  22       const           1     Using where; Using filesort
1   SIMPLE       table1  eq_ref  PRIMARY        PRIMARY  32       table2.ext_cat  1     Using where

Which seems right; it's narrowing using the WHERE clause on the parent table primary key, then it's joining the result using the ext_cat column in the parent table and the primary key in the child table.  However, since it's the same either way, there's nothing to explicitly show whether there is value in having an index on table2.ext_cat.  The tables are so small the queries complete near-instantly either way, so this is more out of academic interest at the moment.

I also tried switching to InnoDB to see if there was any difference in behaviour (not officially using foreign keys, since those would create indexes automatically; just the same test as above) but the explain output was identical.

So, is it indeed improving query performance to have an index on the foreign key?  If so, is there some way to 'see' the effect with an EXPLAIN (or otherwise)?  And if not, can you explain what about this situation causes it to be unnecessary?

Edit: OK, I expect what's going on is because MySQL is choosing to first narrow using the WHERE clause, then join the result to the child table, any indexes on the parent table are irrelevant at that point.  In cases where it is more efficient to join first, then apply the clause, it would use the foreign key index in the join.  Is that correct, and do both behaviors indeed occur depending on the table contents and the query?  If not, when are indexes on foreign keys useful?

Edit 2: According to this it would appear the answer is never, if the parent table appears first in the EXPLAIN: mysql-indexes.  An index is only used on one half of a join, since it essentially goes one table at a time, joining the result to the next table in the cascade.

Very quick answer: MyISAM does not support FOREIGN KEYs at all... Any FOREIGN KEY you define on a MYISAM table is silenty thrown away.

When you later switch to InnoDB (i.e. ALTER TABLE my_table ENGINE=InnoDB) the FOREIGN KEY is not there either; it was thrown away when the table used to be MyISAM.


Improving performance for sum and group by with large datasets I have a query that looked like this:

SELECT sum(score) as score, count(distinct(user_id)) number, round(sum(score)/count(distinct(user_id))) score_per_member, teams.*, types.name as type
FROM `teams` inner join `users` on `teams`.`id` = `users`.`team_id`
inner join `results` on `results`.`user_id` = `users`.`id`
inner join `types` on `types`.`id` = `teams`.`type_id`
WHERE `results`.`year` = '2015'
GROUP BY `team_id`
ORDER BY `score` DESC;

Users has 250k rows. Results has 4m rows with 1m with year 2015.

Although explain doesn't look too bad:

1   SIMPLE  teams   ALL PRIMARY,teams_type_id_foreign   NULL    NULL    NULL    4449    Using temporary; Using filesort
1   SIMPLE  types   eq_ref  PRIMARY PRIMARY 4   teams.type_id   1
1   SIMPLE  users   ref PRIMARY,users_team_id_foreign   users_team_id_foreign   5   teams.id    57  Using where; Using index
1   SIMPLE  results ref user_id,results_year_user_id_index  results_year_user_id_index  6   const,users.id  6

It was taking 50 seconds to run.

I refactored it to:

SELECT score, number, round(score/number) score_per_member, teams.*, types.name as type FROM `teams`
    inner join
        (
            select sum(score) score, team_id, count(distinct(user_id)) number
            from `results`
            join users on `results`.`user_id` = `users`.`id`
            WHERE `year` = '2015' and year_id is not null
            GROUP BY `team_id`
    ) r on teams.id = r.team_id
    inner join `types` on `types`.`id` = `teams`.`type_id` ORDER BY `score` DESC;

Explain actually 'looks' much worse:

1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    2045    Using filesort
1   PRIMARY teams   eq_ref  PRIMARY,teams_type_id_foreign   PRIMARY 4   r.team_id   1
1   PRIMARY types   eq_ref  PRIMARY PRIMARY 4   teams.type_id   1
2   DERIVED users   range   PRIMARY,users_team_id_foreign   users_team_id_foreign   5   NULL    132496  Using where; Using index
2   DERIVED results ref user_id,results_year_user_id_index  results_year_user_id_index  6   users.id    6

But the performance is now much, much better (2 seconds).

How could I improve queries like this even more? Is it just a case of throwing more hardware at it?

The big issue is "inflation-deflation" when you do JOIN + GROUP BY.

Think of what JOIN does -- it generates an intermediate table with teams * types/team * users/type/team * results/...  That is, that table is much bigger than any of the original tables.

Then it does the GROUP BY do deflate that back to one row per team.

Usually the combination gives COUNTs and SUMs that are bigger than expected.  (Did you check?)  And it takes a long time.

So, the trick is to use subqueries to avoid any single query with both JOIN and GROUP BY in it.  (The combo is ok if the JOIN is known to be 1:1, which seems to be the case for teams and types, but not the other JOINs.)

There are two ways to do such subqueries:

SELECT
        ( SELECT SUM() FROM ... WHERE ... ) -- correlated subquery; single result only
    FROM ...

The subquery is effectively a GROUP BY without saying it, but it boils down to a single value, thereby avoiding the "inflation".

and

SELECT ...
    FROM ( SELECT COUNT(), SUM(...) FROM ... GROUP BY ...
         ) AS x  -- tmp table for some of the data; no JOIN
    JOIN ...

The subquery "deflates" some of the data before getting to the JOIN.

Caution:  Using multiple subqueries of the second kind is slow because of lack of indexes:

SELECT ...
    FROM ( SELECT ... ) a
    JOIN ( SELECT ... ) b ON ...

5.6 helps somewhat because it discovers and adds an index.


Use a condition to limit the databases a policy checks I'd like to create a Policy that checks, on demand, one database in an instance for tables that have no index. I can see how to create a Condition, linked to the 'Database' Facet and checks for my database's GUID. I can also see how to create a Condition, linked to the 'Table' Facet that checks for the presence of an index. When I create a Policy, however, I seem to be able to only check one Condition. I know you can add clauses to Conditions, which would be fine if I didn't have to specify a Facet and so limit my options. Can you not have multiple Conditions in a Policy?

You can certainly limit the set of databases that are checked, using a condition, and have that condition be separate from the actual condition the policy is using to check. Pull down the "Every" drop-down in the UI:

Create a new condition:

Then you should be able to use that condition:

Note that this is using the SQL Server 2012 version of Management Studio (which is now completely free, as of SP1). If the UI does not behave this way in the 2008 R2 version of the tools, upgrade your tools.


Convert multiple databases into schemas SQL Server Due to a high amount of databases being created on my project and the rising cost per databases I had the following question:

Is there a way to convert multiple databases in SQL Server into one database with multiple schemas?

Thank you for helping out.

Yes, this can be done.

In the open source community, it has been done for years. In most situations, they just prefixed the tables.

I am not aware of a tool to simplify the process.

You are aware that you introduce new challenges.

Permissions.
Queries are more complicated and may lose some performance.

An alternative approach is to move to multi-tenant design. Add a new field to all tables. Let's call it ClientId. Add filtered indexes. Now all queries need to be filtered on ClientId. You still need to deal with any auto increment columns.


Algorithm for finding the longest prefix I have two tables.

First one is a table with prefixes

code name price
343  ek1   10
3435 nt     4
3432 ek2    2

Second is call records with phone numbers

number        time
834353212     10
834321242     20
834312345     30

I need write a script which find longest prefix from prefixes for each record, and write all this data to third table, like this:

 number        code   ....
 834353212     3435
 834321242     3432
 834312345     343

For number 834353212 we must trim '8', and then find the longest code from prefix table, its 3435.
We must always drop first '8' and prefix must be in the beginning.

I solved this task long time ago, with very bad way.
Its was terrible perl script which do a lot of queries for each record.
This script:

Take a number from calls table, do substring from length(number) to
1 => $prefix in the loop
Do the query  : select count(*) from prefixes where code like '$prefix'
If count>0 then take first prefixes and write into table

First problem is query counts - it's call_records * length(number).
Second problem is LIKE expressions. I am afraid those are slow.

I tried to solve the second problem by:

CREATE EXTENSION pg_trgm;
CREATE INDEX prefix_idx ON prefix USING gist (code gist_trgm_ops);

That speeds up each query, but did not solve problem in general.

I have 20k prefixes and 170k numbers now, and my old solution is bad. Looks like I need some new solution without loops.

Only one query for each call record or something like this.

I am assuming data type text for the relevant columns.

CREATE TABLE prefix (code text, name text, price int);
CREATE TABLE num (number text, time int);

"Simple" Solution

SELECT DISTINCT ON (1)
       n.number, p.code
FROM   num n
JOIN   prefix p ON right(n.number, -1) LIKE (p.code || '%')
ORDER  BY n.number, p.code DESC;

Key elements:

DISTINCT ON is a Postgres extension of the SQL standard DISTINCT. Find a detailed explanation for the used query technique in this related answer on SO.
ORDER BY p.code DESC picks the longest match, because '1234' sorts after '123' (in ascending order).

Simple SQL Fiddle.

Without index, the query would run for a very long time (didn't wait to see it finish). To make this fast, you need index support. The trigram indexes you mentioned, supplied by the additional module pg_trgm are a good candidate. You have to choose between GIN and GiST index. The first character of the numbers is just noise and can be excluded from the index, making it a functional index in addition.
In my tests, a functional trigram GIN index won the race over a trigram GiST index (as expected):

CREATE INDEX num_trgm_gin_idx ON num USING gin (right(number, -1) gin_trgm_ops);

Advanced dbfiddle here.

All test results are from a local Postgres 9.1 test installation with a reduced setup: 17k numbers and 2k codes:

Total runtime: 1719.552 ms (trigram GiST)
Total runtime: 912.329 ms (trigram GIN)

Much faster yet

Failed attempt with text_pattern_ops

Once we ignore the distracting first noise character, it comes down to basic left anchored pattern match. Therefore I tried a functional B-tree index with the  operator class text_pattern_ops (assuming column type text).

CREATE INDEX num_text_pattern_idx ON num(right(number, -1) text_pattern_ops);

This works excellently for direct queries with a single search term and makes the trigram index look bad in comparison:

SELECT * FROM num WHERE right(number, -1) LIKE '2345%'

Total runtime: 3.816 ms (trgm_gin_idx)
Total runtime: 0.147 ms (text_pattern_idx)

However, the query planner will not consider this index for joining two tables. I have seen this limitation before. I don't have a meaningful explanation for this, yet.

Partial / functional B-tree indexes

The alternative it to use equality checks on partial strings with partial indexes. This can be used in a JOIN.

Since we typically only have a limited number of different lengths for prefixes, we can build a solution similar to the one presented here with partial indexes.

Say, we have prefixes ranging from 1 to 5 characters. Create a number of partial functional indexes, one for every distinct prefix length:

CREATE INDEX prefix_code_idx5 ON prefix(code) WHERE length(code) = 5;
CREATE INDEX prefix_code_idx4 ON prefix(code) WHERE length(code) = 4;
CREATE INDEX prefix_code_idx3 ON prefix(code) WHERE length(code) = 3;
CREATE INDEX prefix_code_idx2 ON prefix(code) WHERE length(code) = 2;
CREATE INDEX prefix_code_idx1 ON prefix(code) WHERE length(code) = 1;

Since these are partial indexes, all of them together are barely larger than a single complete index.

Add matching indexes for numbers (taking the leading noise character into account):

CREATE INDEX num_number_idx5 ON num(substring(number, 2, 5)) WHERE length(number) >= 6;
CREATE INDEX num_number_idx4 ON num(substring(number, 2, 4)) WHERE length(number) >= 5;
CREATE INDEX num_number_idx3 ON num(substring(number, 2, 3)) WHERE length(number) >= 4;
CREATE INDEX num_number_idx2 ON num(substring(number, 2, 2)) WHERE length(number) >= 3;
CREATE INDEX num_number_idx1 ON num(substring(number, 2, 1)) WHERE length(number) >= 2;

While these indexes only hold a substring each and are partial, each covers most or all of the table. So they are much larger together than a single total index - except for long numbers. And they impose more work for write operations. That's the cost for amazing speed.

If that cost is too high for you (write performance is important / too many write operations / disk space an issue), you can skip these indexes. The rest is still faster, if not quite as fast as it could be ...

If numbers are never shorter then n characters, drop redundant WHERE clauses from some or all, and also drop the corresponding WHERE clause from all following queries.

Recursive CTE

With all the setup so far I was hoping for very elegant solution with a recursive CTE:

WITH RECURSIVE cte AS (
   SELECT n.number, p.code, 4 AS len
   FROM   num n
   LEFT    JOIN prefix p
            ON  substring(number, 2, 5) = p.code
            AND length(n.number) >= 6  -- incl. noise character
            AND length(p.code) = 5

   UNION ALL
   SELECT c.number, p.code, len - 1
   FROM    cte c
   LEFT   JOIN prefix p
            ON  substring(number, 2, c.len) = p.code
            AND length(c.number) >= c.len+1  -- incl. noise character
            AND length(p.code) = c.len
   WHERE    c.len > 0
   AND    c.code IS NULL
   )
SELECT number, code
FROM   cte
WHERE  code IS NOT NULL;

Total runtime: 1045.115 ms

However, while this query isn't bad - it performs about as good as the simple version with a trigram GIN index - it doesn't deliver what I was aiming for. The recursive term is planned once only, so it can't use the best indexes. Only the non-recursive term can.

UNION ALL

Since we are dealing with a small number of recursions we can just spell them out iteratively. This allows optimized plans for each of them. (We lose the recursive exclusion of already successful numbers, though. So there is still some room for improvement, especially for a wider range of prefix lengths)):

SELECT DISTINCT ON (1) number, code
FROM  (
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 5) = p.code
            AND length(n.number) >= 6  -- incl. noise character
            AND length(p.code) = 5
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 4) = p.code
            AND length(n.number) >= 5
            AND length(p.code) = 4
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 3) = p.code
            AND length(n.number) >= 4
            AND length(p.code) = 3
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 2) = p.code
            AND length(n.number) >= 3
            AND length(p.code) = 2
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 1) = p.code
            AND length(n.number) >= 2
            AND length(p.code) = 1
   ) x
ORDER BY number, code DESC;

Total runtime: 57.578 ms (!!)

A breakthrough, finally!

SQL function

Wrapping this into an SQL function removes the query planning overhead for repeated use:

CREATE OR REPLACE FUNCTION f_longest_prefix()
  RETURNS TABLE (number text, code text) LANGUAGE sql AS
$func$
SELECT DISTINCT ON (1) number, code
FROM  (
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 5) = p.code
            AND length(n.number) >= 6  -- incl. noise character
            AND length(p.code) = 5
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 4) = p.code
            AND length(n.number) >= 5
            AND length(p.code) = 4
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 3) = p.code
            AND length(n.number) >= 4
            AND length(p.code) = 3
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 2) = p.code
            AND length(n.number) >= 3
            AND length(p.code) = 2
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 1) = p.code
            AND length(n.number) >= 2
            AND length(p.code) = 1
   ) x
ORDER BY number, code DESC
$func$;

Call:

SELECT * FROM f_longest_prefix_sql();

Total runtime: 17.138 ms (!!!)

PL/pgSQL function with dynamic SQL

This plpgsql function is much like the recursive CTE above, but the dynamic SQL with EXECUTE forces the query to be re-planned for every iteration. Now it makes use of all the tailored indexes.

Additionally this works for any range of prefix lengths. The function takes two parameters for the range, but I prepared it with DEFAULT values, so it works without explicit parameters, too:

CREATE OR REPLACE FUNCTION f_longest_prefix2(_min int = 1, _max int = 5)
  RETURNS TABLE (number text, code text) LANGUAGE plpgsql AS
$func$
BEGIN
FOR i IN REVERSE _max .. _min LOOP  -- longer matches first
   RETURN QUERY EXECUTE '
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(n.number, 2, $1) = p.code
            AND length(n.number) >= $1+1  -- incl. noise character
            AND length(p.code) = $1'
   USING i;
END LOOP;
END
$func$;

The final step cannot be wrapped into the one function easily.
Either just call it like this:

SELECT DISTINCT ON (1)
       number, code
FROM   f_longest_prefix_prefix2() x
ORDER  BY number, code DESC;

Total runtime: 27.413 ms

Or use another SQL function as wrapper:

CREATE OR REPLACE FUNCTION f_longest_prefix3(_min int = 1, _max int = 5)
  RETURNS TABLE (number text, code text) LANGUAGE sql AS
$func$
SELECT DISTINCT ON (1)
       number, code
FROM   f_longest_prefix_prefix2($1, $2) x
ORDER  BY number, code DESC
$func$;

Call:

SELECT * FROM f_longest_prefix3();

Total runtime: 37.622 ms

A bit slower due to required planning overhead. But more versatile than SQL and shorter for longer prefixes.


How to detect which indexes have checksums and which ones don't? Is there a query that will show which tables / indexes need to be rebuilt so that they have checksums available for data verification?   The DB was switched recently from torn page to checksum verification so only some newer indexes / tables have the required checksums and those which index maint has rebuilt.  Need to see what the remaining tables / indexes are that need rebuilding to have checksum enabled.

The checksum isn't at the object or index level (aka allocation unit in this context). It is at the page level.
I.e., an allocation unit can have some pages without checksum (those that hasn't been modified since you turned on checksum) and other pages do have checksum (those that has been modified since you turned on checksum).
So you have to look at the page level to see whether each page has checksum or not. Below can serve as a starting point for you, you probably want to customize to your needs, though.
SELECT
 OBJECT_SCHEMA_NAME(a.object_id) AS schema_
,OBJECT_NAME(a.object_id) AS tbl
,a.index_id
--,*
FROM sys.dm_db_database_page_allocations(DB_ID('Adventureworks'), NULL, null, null, 'DETAILED') AS a
CROSS APPLY sys.dm_db_page_info ( DB_ID(), a.allocated_page_file_id, a.allocated_page_page_id, 'LIMITED' ) AS p
WHERE p.has_checksum = 0
AND p.page_type IN(1, 2)

Note that as written it read every page in the database. So consider whether you want to use the parameters to sys.dm_db_database_page_allocations and run it only for those tables/indexes you are interested in.
You can of course also aggregate and count number of pages with/without checksum, etc. Here's another version:
SELECT
 OBJECT_SCHEMA_NAME(a.object_id) AS schema_
,OBJECT_NAME(a.object_id) AS tbl
,a.index_id
,SUM(CAST(p.has_checksum AS int)) AS pages_with_checksum
,SUM(CASE p.has_checksum WHEN 1 THEN 0 ELSE 1 END) AS pages_without_checksum
--,*
FROM sys.dm_db_database_page_allocations(DB_ID('Adventureworks'), NULL, null, null, 'DETAILED') AS a
CROSS APPLY sys.dm_db_page_info ( DB_ID(), a.allocated_page_file_id, a.allocated_page_page_id, 'LIMITED' ) AS p
WHERE p.page_type IN(1, 2)
AND OBJECTPROPERTY(a.object_id, 'IsMsShipped') = 0 --Filter out system tables
GROUP BY
  OBJECT_SCHEMA_NAME(a.object_id)
 ,OBJECT_NAME(a.object_id)
 ,a.index_id
 ORDER BY pages_without_checksum DESC, pages_with_checksum DESC

Note sys.dm_db_page_info was added in SQL Server 2019.


MySQL Partitioning on event tables with start and end datetime columns I am curious as to what solutions are out there for partitioning tables that log events, that allow both efficient querying and the advantages of pruning when truncating data.

Suppose I have a simple table that records events from different locations:

tblEvents (
  event_id,
  location_id,
  start_datetime,
  end_datetime
)

Most queries on this table will take the form of:

SELECT    event_id
FROM      tblEvents
WHERE     location_id = @queried_location_id
AND       start_datetime < @queried_end_datetime
AND       end_datetime > @queried_start_datetime

To date, I have partitioned by the location_id, and simply just indexed the datetime columns. Performance-wise, this has sufficed, and I never planned to keep more than a couple of months worth of data in the database, so it seemed future-proof as well.

The problem arose when I actually went to purge data from this table (it is very large and also involved in replication). Using DELETE FROM tblEvents WHERE start_datetime < @some_date proved to be very slow, and created problems with requests being made from other clients (not surprisingly).

Partitioning by either of the datetime columns is no good. Although it solves my data purging problem, it makes the query above inefficient, as it still needs to look at multiple partitions depending on the dates being queried.

Am I missing a common solution here? Is there a more efficient way to purge my data? Or is there a smarter way to partition/index that I have missed?

Do the time ranges overlap for a given location?

If you need to delete all rows where start_datetime < @some_date, then the best way is via PARTITIONing based on start_datetime.  Use PARTITION BY RANGE(TO_DAYS(start_datetime)) and base it on days or weeks or months, such that you have about 20-50 partitions.  See details.

Plan A:

Once you partition that way, then have the PRIMARY KEY be (location_id, end_datetime, start_datetime), in this order.  If those 3 columns are not sufficient to be unique, and, say, you have id AUTO_INCREMENT, then do this:

PRIMARY KEY(location_id, end_datetime, start_datetime, id),
INDEX(id)

Now, let's analyze

WHERE     location_id = @queried_location_id
AND       start_datetime < @queried_end_datetime
AND       end_datetime > @queried_start_datetime

The following steps happen:

"partition pruning" based on start_datetime.  This may or may not help much, depending on how new the 'start' is.  On average(?), it will eliminate half the partitions (not much benefit).
Using the PK, it will home in on the appropriate location_id and the end_datetime.
Scan forward in the data (since the PK is 'clustered' with the data), checking the rest of those location_ids for a match.
Repeat for each partition not pruned out by step 1
Combine the results from the partition.

Plan B:

If your start..end ranges are predominately recent dates and if you can purge based on end_datetime, then this would probably be better because it would predominately focus on the last partition:

PARTITION BY RANGE(TO_DAYS(end_datetime))

PRIMARY KEY(location_id, start_datetime, end_datetime, id),
INDEX(id)

If the ranges are not overlapping, then folding in this may give significantly more performance.

But... If end_datetime is NULL initially, this will throw the row in to the "first" partition.  So... Be sure to have an extra partition at the beginning for that -- else the DROP for purging old records will toss them by mistake.  That is, have the first range be VALUES LESS THAN (0).
 Then have the second range be the oldest month (or week or whatever), and drop that for purging.

Another issue with NULL -- when you do set end_datetime, the record will have to be moved from one partition to another, thereby making the UPDATE statement somewhat costly.  (It is effectively a DELETE plus an INSERT.)  For this reason, I don't recommend Plan B for very high activity.


Postgres Access Roles I'm trying to get my head around postgres roles.

I want to create a database and have the owner role have all privileges on the database.

I then want another role which cannot create tables but able to read/write from any tables that may be added to the db in the future.

I have done REVOKE ALL ON schema public FROM public and then GRANT ALL ON schema public TO admin_role and the admin role works.

But I can't figure out how to create a limited role with the create table permission revoked and it still able to see and read to tables within the database. If I do the GRANT ALL ON schema public it has all privileges which I don't want.

Any help much appreciated.

You don't need to GRANT only on a schema. You can get more specific and GRANT specifically to a table, like this:

GRANT ALL ON TABLE xyz.items TO admin_role;

but note that GRANT ALL and REVOKE ALL are very large sledgehammers for building very small spice racks.

Consider reviewing the excellent GRANT documentation which covers this in detail.

For your specific case, you could do something as simple as:

REVOKE CREATE ON SCHEMA public FROM admin_role;

which would remove the right to create new objects in the schema.


sp_BlitzFirst: blocking_session_id not displayed in list of session_ids I have version sp_BlitzFirst 2016-12-10.

I executed the following:

sp_blitzfirst @ExpertMode = 1, @CheckProcedureCache = 1, @OutputType = 'Opserver1'

Below output shows SPID 124 is blocked by SPID 143, however SPID 143 did not show up in any of the output.  Is there another way to have sp_BlitzFirst show the info of the blocking_sesesion_id as well, so I can see what query is being executed?

It's most likely that the blocking SPID is sleeping. If you run sp_BlitzFirst again, look in the second section down, and you may see warnings for sleeping sessions with open transactions.

By default sp_BlitzWho filters out sleeping SPIDs. We did recently add a parameter to show them, but we haven't bubbled it up to sp_BlitzFirst yet.

I'm on my phone so I can't post screen caps at the moment. Leave a comment if you need more information.

Thanks!


What does the bit data type map to in a Java Domain Object When you have a column of the bit data type what Class would be used to represent that in a Java POJO mapping that table to an object which is using javax.persistence annotations?

I tried Boolean, which didn't work and produced the following errors:

  org.hibernate.exception.SQLGrammarException: could not insert: [com.DomainClassName]
  java.sql.SQLException: Incorrect syntax near '-'.

EDIT:  a Java boolean does map to the bit data type in SQL Server.  I had a column name with a dash in it that I had to change.

Incorrect syntax near '-'.

This tells me that you've named a database, table or column with a dash in it, not that POJO is having an issue mapping your columns.

As an example, if you've named your entity bar-none and POJO issues the following CREATE TABLE statement:

CREATE TABLE dbo.foo(bar-none BIT);

Or this one:

CREATE TABLE dbo.foo-bar(none BIT);

You will get this error:

  Msg 102, Level 15, State 1, Line 1
  Incorrect syntax near '-'.

And in fact even if you declare a data type that does not exist in SQL Server, such as Boolean, Frank or WhatTheHeckDataTypeIsThis, the parser will still barf with the above error due to the hyphen, before it even bothers validating the data type(s).

So, please check your table name and column names and be sure that your names don't use dashes, spaces or other special characters, that they aren't reserved words, and that each name you provide obeys the well-documented rules for identifiers. While you can get around this error if you can coerce POJO to properly delimit bad names with [square brackets], this is not a best practice.


what does this query do? Can you explain me the bold part?

    select   company_code,
             founder,
             (select count(distinct lead_manager_code)
              from Lead_Manager
              where  company_code = c.company_code
             )
    from     Company c
    order by company_code;

The answer is in the bold portion of my answer.

Starters

You have a table of Lead Managers (Lead_Managers) and a table for Companies (Company). A Lead Manager can be in multiple companies and can have multiple Lead Manager roles in one company.

Inner Query

The inner query retrieves the DISTINCT number of (COUNT) Lead Manager's  Codes (Lead_Manager.company_code) where the Lead_Manager's company code matches ...

Outer Query

... the company code (c.company_code) in the Company (Company c) table.

Result

The result should be a list of Company Codes, Founders and distinct count of Lead Managers for each given company. The Distinct will only select unique Lead Manager', or put another way: Even if a lead manager has multiple Lead Manager Roles in a company only 1 count will be made for that company.


How is MySQL using an index when doing a case sensitive comparison? I noticed that Ruby on Rails (ActiveRecord) was using the BINARY operator for a case sensitive comparison.  Yet, when I look at the EXPLAIN for this, MySQL can still use the appropriate index for a case insensitive column.  How is that possible?  Are all text columns indexed case sensitivly?

Without BINARY operator:

  select_type: SIMPLE
        table: my_table
         type: const
possible_keys: my_table_unq,my_table_idx
          key: my_table_unq
      key_len: 771
          ref: const,const
         rows: 1
        Extra: Using index

With BINARY operator:

  select_type: SIMPLE
        table: my_table
         type: range
possible_keys: my_table_unq,my_table_idx
          key: my_table_unq
      key_len: 771
          ref: NULL
         rows: 1
        Extra: Using where; Using index

Looks like the later is using a range.

Note the link you gave on the BINARY operator where is it says the following

  Posted by Yann Neuhaus on July 20 2005 12:34pm

  Per default the search operation in not case sensitive, example,
  looking for 'HYPE BEAU' returns 'Hype Beau' in the table product :

  mysql> select prod_name, prod_id from products where prod_name = 'HYPE
  BEAU';

+-----------+---------+
| prod_name | prod_id |
+-----------+---------+
| Hype Beau |    9600 |
| Hype Beau |   25860 |
| Hype Beau |   42120 |
| Hype Beau |   46185 |
+-----------+---------+

4 rows in set (0.01 sec)

  This is because the default collation is case insentitive. With the
  binary clause the binary collation is used and the search becomes case
  sensitive :

If the character set's collation is case insensitive, then using the
BINARY operator is not necessary. It would only appear to work fine in that instance.

For more clarification on collations being case insensitive, please read the MySQL Documentation section "The _bin and binary Collations"


changing float->numeric casts from assignment to implicit, dangerous? In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding precision.

In MySQL, round(some_float_column, 2) works as expected, returning the value of some_float_column rounded to two decimal places.  In Postgres it errors with ERROR:  function round(double precision, integer) does not exist and suggests HINT:  No function matches the given name and argument types. You might need to add explicit type casts..

As indicated by the docs...
http://www.postgresql.org/docs/9.1/static/functions-math.html
...Postgres has two round functions, round(value) which takes a double precision or a numeric, and round(value, precision) which takes a numeric only and an integer.

So, I don't understand why the two-argument form of round doesn't take a double to begin with, but whatever.  In searching I discovered two solutions to this problem.  One is to simply create my own version of round(value, precision) that takes (double, int) and wraps the existing (numeric, int) version with an explicit cast.  That certainly works, but I don't like it (my background is in Oracle which doesn't even have a true floating point type).  It seems to me that float/double ought to be implicitly castable to numeric.  And it turns out that an ASSIGNMENT cast for these types comes pre-defined.  But ASSIGNMENT doesn't work for function calls, as we see here, it would need to be IMPLICIT.  The trouble with that is that only one cast can be defined per pair of types, and the float4->numeric and float8->numeric assignment casts are required by the system and can't be dropped.  So the only way to make those casts implicit is to update pg_cast set castcontext = 'i' where castsource in (700,701) and casttarget = 1700.

Now we're into hacking the catalog, and that's a sign that this might be a bad idea.  But I don't have any hard evidence that it's bad.  Float/double values are inexact, and numeric values are exact, so it seems to me that casting from the former to the latter would be fully data-preserving and therefore logically safe.  The only potential problem I know of would be introducing ambiguity into other functions' argument patterns, but the purpose of asking this question is mainly to fish for potential problems I don't know of.

So, is it dangerous to change the float->numeric casting behavior from ASSIGNMENT to IMPLICIT?

Please be aware that different rounding algorithms are used for different data types.

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE

  When rounding values, the numeric type rounds ties away from zero,
  while (on most machines) the real and double precision types round
  ties to the nearest even number. For example:

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;
  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4


Duplicate Relationships in Rails/MySQL Healthcare Application Overview of the Application

I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and also provide more general information about the utility of drugs to treat various conditions.

Current Entities

“Drug”  - Example: “Abilify”

“Dosage” - Example: “10mg”

“Condition" - Example: “Depression”

Potential Entities For Required Use Cases

“Prescription” (Drug + Dosage + Condition) => This is required for dosage specific data, for a specific condition.  Clinical research may be attached to this entity.

“Therapy" (Drug + Dosage) => This is required because it represents how most medications are priced, and how most medications are interchanged between 3rd party health applications, using a government standards primary key known as a RxNorm Concept Unique Identifier.

“Usage” (Drug + Condition) => This is required for connecting drugs to conditions, without consideration to dosage. Some drugs are used to treat many conditions.

Concern

These three models, if implemented, create duplicate relationships between either drug and condition, or drug and dosage.  How can we build out a normalized system that supports our needs for both dosage and condition specific tools, as well as dosage and condition agnostic tools?

Example

“Abilify 5 mg for depression“ => The “Prescription” entity would allow us to attach clinical studies, and alternatives, for 5 mg of Abilify in the treatment of depression.  The “Therapy” entity would allow us to find pricing, as well as 3rd party supplemental data, for 5 mg of Abilify.  The “Usage” entity would allow us to track all conditions that Abilify is used to treat, along with their efficacy in treating these various conditions.

This question if part database schema and part rails. From a Rails perspective, you can create as many aliased relationships as you like so normalization isn't as much of a concern there.

Without seeing the schema and just going based off of what you have written above though, I'd say that Dosage doesn't belong in a table with it's own model.

Dosage is effectively

Amount: 10
Unit Of Measure: mg

Writing things like 10mg into a table and giving them an id doesn't make a ton of sense. Organizing the types of units of measure that you have available or even standardizing by converting everything to milligrams in the database so that you can just do conversions when displaying would probably make the most sense.

Then you eliminate the dosages table completely and simplify to:

therapies
 - id
 - drug_id
 - milligrams

And Prescription becomes:

prescription
 - id
 - therapy_id
 - condition_id

Now, usage is the outlier here because it's where your only real "duplicate" relationship is but everything you need to link drugs to conditions can be inferred from your prescription data, effectively like so:

SELECT DISTINCT prescriptions.condition_id, therapies.drug_id
FROM prescriptions
  INNER JOIN therapies ON prescriptions.therapy_id = therapies.id

You could use a query like the above to create a view called usages that could be used by a read only Usage model in Rails. If performance became a concern you could use that to create a cached usages table and setup a database trigger to update the cache table when a new prescription is added though.


Rollback group of DDL statements Working in SQL Server 2008 R2, I am trying to rollback a set of DDL statements as a group (think of an upgrade script for a database), but am running into trouble.

Take the following code:

begin try

begin tran

create table foo (i int)
alter table foo add x dog
insert into foo select 1,1
insert into foo select 1,1,1

commit tran

end try

begin catch

rollback tran
print @@error

end catch

I'm expecting the try to fail on the alter table statement, drop to the catch, rollback the transaction, and print the error message. However, if you check your objects/tables, you'll see that foo is still there (so the create table didn't rollback properly).

select * from sys.objects where name = 'foo'

What am I doing wrong here?

The error is happening because the error being thrown part of a recompile error due to deferred name resolution.  Looking at SQL BOL those aren't trapped when they happen at the same level as the try...catch.  However, if it's happening at a different level, either as dynamic SQL or a SP call, then it will get caught and rolled back.

Using Profiler you can see that the "alter table foo add x dog" statement recompiles before executing and then errors and bypasses the catch block.

If you wrap the statements in dynamic SQL the error isn't returned to Profiler and the transaction is rolled back


Categorize age ranges and display in one column I want to see the data as follows

Member   Age  Category
Test1    25    Under 30
Test2    55    51-60
Test3    67    Above 60

I have used the below query (from an answer to my previous question) to calculate the age from DOB and current date and then categorize each age under one name.

This query is creating separate columns, instead I want to see all the categories under one column so that I can group this category column

WITH AgeData as
(
SELECT
    Username,
    Birthdate,
    DATEDIFF(YEAR, birthdate, GETDATE()) AS AGE
FROM @Table)
SELECT
    Username,
    Birthdate,
    Age,
    CASE
        WHEN AGE < 30 THEN 1
        ELSE 0
    END AS 'Under 30',
    CASE
        WHEN AGE BETWEEN 31 AND 40 THEN 1
        ELSE 0
    END AS '31 - 40',
    CASE
        WHEN AGE BETWEEN 41 AND 50 THEN 1
        ELSE 0
    END AS '41 - 50',
    CASE
        WHEN AGE > 50 THEN 1
        ELSE 0
    END AS 'Over 50'
FROM AgeData

Instead of using multiple CASE expressions, one for each group, just combine them into a single expression. Then, in the THEN clause you can use something more descriptive to group on later. In the example below I used the column aliases you provided.

WITH AgeData as
(
  SELECT [Username],
         [Birthdate],
         DATEDIFF(YEAR, [Birthdate], GETDATE()) AS [AGE]
  FROM @table
),
GroupAge AS
(
  SELECT [Username],
         [Birthdate],
         [Age],
         CASE
             WHEN AGE < 30 THEN 'Under 30'
             WHEN AGE BETWEEN 31 AND 40 THEN '31 - 40'
             WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
             WHEN AGE > 50 THEN 'Over 50'
             ELSE 'Invalid Birthdate'
         END AS [Age Groups]
  FROM AgeData
)
SELECT COUNT(*) AS [AgeGrpCount],
       [Age Groups]
FROM GroupAge
GROUP BY [Age Groups];


Is it safe to kill vacuumdb? I would like to schedule a vacuumdb cron job for an entire database, but I don't want it to run past a certain time. Is it safe to just stop the job with killall vacuumdb?

Yes, it's safe. Use SIGTERM (the default, signal 15 on Linux) not SIGKILL (signal 9). If it doesn't complete it might waste recent work and have to repeat it.

However, you should really just let autovacuum do its job.


PostgreSQL ignores implicit casts in some situations The setup (tried only on PostgreSQL 9.6):
CREATE TYPE MY_ENUM AS ENUM ('a', 'b');
CREATE CAST (CHARACTER VARYING AS MY_ENUM) WITH INOUT AS IMPLICIT;
CREATE TABLE t (x MY_ENUM);
INSERT INTO t VALUES ('a'::MY_ENUM), ('b'::MY_ENUM);

These work just fine, as expected:
INSERT INTO t VALUES ('a');
SELECT * FROM t WHERE x = 'a';

But these don't:
PREPARE p(CHARACTER VARYING) AS SELECT * FROM t WHERE x = $1;
;; error: operator does not exist: my_enum = character varying

CREATE FUNCTION f(ix CHARACTER VARYING, OUT ox MY_ENUM) AS
$$
    SELECT * FROM t WHERE x = ix
$$ LANGUAGE sql;
;; error: operator does not exist: my_enum = character varying

CREATE FUNCTION f(ix CHARACTER VARYING) RETURNS VOID AS
$$
BEGIN
    SELECT * FROM t WHERE input_type = ix;
END;
$$ LANGUAGE plpgsql;

SELECT f('piano');
;; error: operator does not exist: my_enum = character varying

According to the documentation:

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression.

So why the errors?

The difference is this:
In the first two (working) examples, 'a' is just an untyped string literal - which is strictly different from varchar. The conversion to your custom enum type is provided by basic input/output functions of the enum type. (There is no explicit entry in the system catalog pg_cast.) Postgres determines the best match for a data type as the user did not provide one. Your custom cast is not even needed. Neither for type resolution nor for operator resolution.
In the following examples, you pass a typed value (varchar). Now, your custom cast may be of use. For example, this wouldn't work without your custom cast:
INSERT INTO t VALUES ('a'::varchar);

Your examples are not plain assignments, but expressions with the operator =. We are in the realm of Operator Type Resolution. Item 2a states:

If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check.

That would cover my_enum_column = 'foo' - with untyped literal.
But there is nothing that can be stretched to cover my_enum_column = 'foo'::varchar. Seems like the fact that only a generic = operator for left and right operand anyenum is available is not enough to clue in operator resolution:
-- missing operators:
SELECT oprleft::regtype, oprright::regtype, *
FROM   pg_operator
WHERE  oprname = '='
AND   ('anyenum'::regtype IN (oprleft, oprright) OR
       'my_enum'::regtype IN (oprleft, oprright));

 oprleft | oprright | ...
 --------+----------+----
 anyenum | anyenum  | ...
(1 row)

You would have to register a more explicit operator for this. But I wouldn't do that. I would do things like (also note various orthogonal syntax fixes):
PREPARE p(my_enum) AS SELECT * FROM t WHERE x = $1;  -- prepare with type my_enum
EXECUTE p('a'::varchar); -- then your cast kicks in
EXECUTE p('a'); -- untyped string literal works in any case

CREATE FUNCTION f1(ix my_enum, OUT ox my_enum)   -- again, declare enum
  RETURNS SETOF my_enum AS
$$
SELECT x FROM t WHERE x = ix
$$ LANGUAGE sql;

SELECT * FROM f1('a'::varchar);

CREATE FUNCTION f2(ix my_enum)  -- same here
  RETURNS SETOF t AS
$$
BEGIN
   RETURN QUERY
   SELECT * FROM t WHERE x = ix;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM f2('a'::varchar);
SELECT * FROM f2('a');

db<>fiddle here
Or just add explicit casts. That's simplest and safest. The manual warns (on the same page you quoted):

It is wise to be conservative about marking casts as implicit. ...

There is more - recommended reading.
If you must create that custom cast (or even add an operator), consider using text instead of varchar, which is the "preferred type" among string types and hence more robust against possible corner-case issues.


How can I add back Identity to a column? I have a table:

CREATE TABLE [dbo].[Question] (
    [QuestionId]       INT              IDENTITY (1, 1) NOT NULL,
    [QuestionUId]      UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL
);

QuestionId was a primary key and when I dropped the primary key then the identity seemed to drop also. Is there a way that I can get back (add) the identity as a default for this column and have it so the new value starts at a value greater than the existing values ?

Dropping the primary key constraint from a table does not alter the IDENTITY property of the primary key column.

USE tempdb;
DROP TABLE dbo.Question;
GO
CREATE TABLE dbo.Question
(
    QuestionID INT NOT NULL CONSTRAINT PK_Question PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData VARCHAR(255) NULL
);

INSERT INTO dbo.Question (SomeData) VALUES ('test');

SELECT *
FROM dbo.Question;

ALTER TABLE dbo.Question DROP CONSTRAINT PK_Question;

INSERT INTO dbo.Question (SomeData) VALUES ('test2');

SELECT *
FROM dbo.Question;

The code above creates a table with a primary key, then drops the primary key.  It then adds a row to the table; and as you can see in the results below, the new row contains an auto-generated IDENTITY value for the QuestionID column.

Results:

Be aware that you could use the SQL Server Management GUI to modify the IDENTITY property of the column in question, however behind the scenes, SSMS will actually drop and recreate the table, which might be problematic to say the least.


MySql crashes in VPS I have a VPS in GoDaddy, which is crashing MySql service at frequent times.

Here is the log file that may help to help me.

160425 09:24:53 mysqld_safe mysqld from pid file /var/lib/mysql/s166-62-85-17.secureserver.net.pid ended
/usr/sbin/mysqld[0x593491]
/usr/sbin/mysqld[0x59c2bd]
/usr/sbin/mysqld[0x6ea5d1]
/usr/sbin/mysqld[0x972d7d]
/usr/sbin/mysqld[0xa2ad41]
/usr/sbin/mysqld[0xaad67a]
/usr/sbin/mysqld[0xaaba64]
160425 09:24:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160425 09:24:47 mysqld_safe mysqld from pid file /var/lib/mysql/s166-62-85-17.secureserver.net.pid ended
/usr/sbin/mysqld[0x593491]
/usr/sbin/mysqld[0x59c2bd]
/usr/sbin/mysqld[0x6ea5d1]
/usr/sbin/mysqld[0x972d7d]
/usr/sbin/mysqld[0xa2ad41]
/usr/sbin/mysqld[0xaad67a]
/usr/sbin/mysqld[0xaaba64]
160425 09:24:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160425 09:24:41 mysqld_safe mysqld from pid file /var/lib/mysql/s166-62-85-17.secureserver.net.pid ended
/usr/sbin/mysqld[0x593491]

mysql has failed. Contact your system administrator if the service does not automagically recover.

Please help me to recover it!
Thanks.

Did you make any changes in my.cnf file recently? Most of the time MySQL fails to start due to any incorrect changes made in its conf file.

Next option is to check the ownership and permission of /var/lib/mysql. It should be owned by MySQL user and not the root user.


Extract Year only from 'Select @@version' output I'm trying to automate a short task in configuring our SQL Server for which I have to grab SQL Server year. Is there a way to extract year only from the output of following query: Select @@version ?

As you know it outputs the whole informatioin e.g.:
Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64)   Nov 16 2019 01:14:50   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

I tried the year function as below but I'm getting conversion error.

declare @SQLVersion varchar(100)
set @SQLVersion = (Select year(@@version))
print @SQLVersion

Or is there another way of grabbing the year only using tsql?

Any help is appreciated.

Found a related answer from this post: https://stackoverflow.com/questions/13331621/sql-query-to-get-sql-year-version. Below code will provide year only. Credit goes to that post.

Declare @SQLVersion varchar(100)
Declare @TargetServer Varchar (4)
--Extract SQL Server Year
set @SQLVersion = (SELECT
   CASE SUBSTRING(CONVERT(VARCHAR(50), SERVERPROPERTY('productversion')), 1, 2)
          WHEN '8.' THEN '2000'
          WHEN '9.' THEN '2005'
          WHEN '10' THEN '2008'
          WHEN '11' THEN '2012'
          WHEN '12' THEN '2014'
          WHEN '13' THEN '2016'
          WHEN '14' THEN '2017'
          WHEN '15' THEN '2019'
    END)
print @SQLVersion


Fire a trigger function when checks change I have the following use case:

I have a set of different checks on different tables in one schema. If a data set is 'wrong' (did not pass a check) it gets pushed into another table in another schema. There one can edit the data again and, via an AFTER UPDATE trigger, the data set will be pushed back to the original table. This will be successful when no check is violated.

Now I have the problem that I sometimes need to edit my check functions, because some data set might have been wrongfully pushed away from the table. But the data which still lay in the 'error' table will not get pushed back into the original table before I trigger an UPDATE on it, even though it might be correct after my edition of the check function.

My question is: Is it possible to automatically trigger an UPDATE on all data sets in such an 'error' table after editing the check functions of the original table (for instance based on the error message)?

You can use the event trigger machinery to achieve your goal.

Say, you have a table in the noalter schema, and want to show which modifications took place.  First, you need an event trigger function:

CREATE OR REPLACE FUNCTION test.tr_alterbla()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
DECLARE obj record;
        q text;
BEGIN
    q := current_query();
    RAISE WARNING '%', q;
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        RAISE EXCEPTION 'This happened: % %', tg_tag, obj.objid::regclass;
    END LOOP;
END;
$function$
;

For testing, this will prevent altering tables by raising an error, in which telling the type of operation and the table name.  Additionally, it will emit the query that initiated the table change.  While the obj record would return everything about the triggering query in its command column, there is no out-of-the-box way to inspect it deeper (or just show it), so you have to parse the output of current_query(), unfortunately.

With all this, you should be able to change the function body to do your UPDATE.

Now what remains is creating the event trigger:

CREATE EVENT TRIGGER show_alter
    ON ddl_command_end WHEN TAG IN ('ALTER TABLE')
    EXECUTE PROCEDURE tr_alterbla();

This reduces the scope of the trigger to ALTER TABLE only.  Unfortunately, there is no way of further focusing on a single table - that's why the obj.objid::regclass bit is present in my example function.

Note: if you are really changing functions, you have to specify CREATE FUNCTION in the WHEN clause above.  I'm afraid in this case it will be (much) harder to figure out which check of which table is affected - that might be worth a separate question here.


SQL Server: insert french characters without using them in a query I have a query inserting a special character into a table, for instance:

INSERT drinks VALUES( 'Café' )

I would like to do the same thing, in the most portable way possible, using only ASCII in my query, for instance:

INSERT drinks VALUES( 'Caf' + <something> )

Is it possible?

in the most portable way possible, using only ASCII in my query

Requiring ASCII-only will make it harder to achieve this goal in a truly reliable manner. If you only have one database, or if you have multiple databases that all have the same default Collation, then using CHAR(asciiValue) as mentioned in @Nelson's answer would work. Just keep in mind that the character you get from using CHAR(someValue) depends on the default Collation of the "current" or "active" database that the query is running in. The Collation determines the code page, and that can be different per each DB. The following simulates what would happen if the same ASCII value were used in different DBs that had different default Collations that used different code pages:

DECLARE @Test TABLE
(
  [Latin1] VARCHAR(10) COLLATE Latin1_General_100_CI_AS_SC,
  [Hebrew] VARCHAR(10) COLLATE Hebrew_100_CI_AS_SC,
  [Greek] VARCHAR(10) COLLATE Greek_100_CI_AS_SC
);

SELECT CONVERT(BINARY(1), 233);

INSERT INTO @Test ([Latin1], [Hebrew], [Greek]) VALUES (0xE9, 0xE9, 0xE9);

SELECT * FROM @Test;
-- é      י       ι

  when I send queries to a counterpart to perform database changes, the text file is opened and edited on different machines, and the special characters are damaged.

ASCII, is inherently difficult to make portable. ASCII text files have no ability to indicate what encoding was used to create them. The problem you are seeing is that the file is being written with one encoding / code page and then being read with another. If you open the file on your computer, does it look exactly as it should? Or has it already been altered? Because é exists in many code pages, I suspect that it still looks fine on your computer, which means it is being read with the wrong encoding.

One way to avoid this is to ensure that the person opening the file opens it with the correct encoding. You can use the File Open dialog in SSMS to select a file, but don't click the Open button. Instead, click the down arrow on the right side of the Open button and select Open With.... Next, select SQL Query Editor with Encoding and click OK. A new dialog will pop up and will be a drop-down of encodings. The drop-down will default to (Auto-Detect) but that won't work here. Just select the encoding that you are using when saving the file and then click OK.

An easier and more reliable way is to use a Unicode encoding (UTF-8 or UTF-16) with a Byte Order Mark. The Byte Order Mark (BOM) is 2 to 4 bytes at the very beginning of a file that indicates the encoding used to create the file. The BOM is used to auto-detect the encoding, which means that you don't need to tell the person you are sending the file to what encoding to use. And, it doesn't require that you use Unicode data in the script; you current INSERT statement with the VARCHAR data should be unaffected.

You can do this by going to File | Save As... Then, just like with the Open dialog, click on the down arrow on the Save button and select Save with Encoding. Then select either Unicode (UTF-8 with signature) - Codepage 65001 or Unicode - Codepage 1200 and then click OK.


SCN issue for older Oracle versions I am new to this, therefore need some help:

We are using PeopleTools 8.48, Peoplesoft Enterprise 8.8 for CRM application. We have database of Oracle v10.2.0.5.0. Recently, because of SCN issue, we have been asked to apply the following patches- Patch 14121009 + patch 16619894 DBPSU (July 2013 PSU).

my question- are these patches compatible with the PeopleSoft/PeopleTool and Oracle versions? Do we need to make any upgrades in parallel?

Product certifications typically include version numbers up to the patchset version, the 4th number (10.2.0.5), which will not change by installing those patches.

I applied those patches last year at a customer and we had no issues at all, although they were using a custom application, not Peoplesoft.