Get major and minor version of SQL Server Running following query:

SELECT
    SERVERPROPERTY('productversion') AS version,
    SERVERPROPERTY('ProductMajorVersion') AS major,
    SERVERPROPERTY('ProductMinorVersion') AS minor;

produces the following results:

version      major   minor
------------ ------- -------
10.0.2531.0  NULL    NULL

What am I missing?

The ProductMajorVersion and ProductMinorVersion properties are only available in SQL Server 2012 and above (according to https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql). Your product version indicates that this is SQL Server 2008 SP1, which does not support the two other properties.


Will Postgres perform the computation of calculated columns that aren't selected in a view? I'm trying to understand the performance impact of selecting data from a view, where one of the columns in a view is function of other data in the original table.

Does the computation get performed irrespective of whether or not the computed column is in the list of selected columns?

If I had a table and the view declared like so

CREATE TABLE price_data (
    ticker     text,          -- Ticker of the stock
    ddate      date,          -- Date for this price
    price      float8,        -- Closing price on this date
    factor     float8         -- Factor to convert this price to USD
);

CREATE VIEW prices AS
    SELECT ticker,
           ddate,
           price,
           factor,
           price * factor as price_usd
    FROM price_data

Would that multiplication be performed in a query like the one below?

select ticker, ddate, price, factor from prices

Is there a reference that guarantees this one way or the other? I was reading the documentation on the rule system in Postgres, but I think the answer really lies with the optimiser, since nothing in the rule system documentation indicated that it wouldn't be selected.

I suspect in the above case the computation isn't performed. I changed the view to use division instead of multiplication, and inserted a 0 for factor into price_data. The query above didn't fail, but if the query was modified to select the computed column the modified query failed.

Is there any way to understand what computations are being done when a select is carried out? I guess I'm looking for something like EXPLAIN but which also tells me about the computation(s) that are being performed.

As @Laurenz said, your analysis is correct: the optimiser will avoid evaluating column expressions which do not affect the outcome of the query (and your attempt to force a divide-by-zero error is proof of this).

This depends on which columns you're selecting, but it also depends on the volatility category of the column expressions. The optimiser is free to omit immutable and stable function calls if their output is never used, as they cannot affect the outcome, but volatile functions might have side-effects, so they are not so readily optimised away.

For example:

create function stable_function() returns int as $$
begin
  raise notice 'stable_function() called';
  return 1;
end
$$
language plpgsql stable;

create function volatile_function() returns int as $$
begin
  raise notice 'volatile_function() called';
  return 1;
end
$$
language plpgsql volatile;

create view v as
  select stable_function(), volatile_function();

If only the volatile column is selected:

test=# explain (analyse, verbose) select volatile_function from v;
NOTICE:  volatile_function() called
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Subquery Scan on v  (cost=0.00..0.27 rows=1 width=4) (actual time=0.057..0.057 rows=1 loops=1)
   Output: v.volatile_function
   ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual time=0.056..0.056 rows=1 loops=1)
         Output: NULL::integer, volatile_function()

...then as you can see, stable_function() is absent from the explain output, and the lack of a NOTICE confirms that this call has been optimised away.

However, if the stable column is selected instead:

test=# explain (analyse, verbose) select stable_function from v;
NOTICE:  stable_function() called
NOTICE:  volatile_function() called
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Subquery Scan on v  (cost=0.00..0.52 rows=1 width=4) (actual time=0.139..0.139 rows=1 loops=1)
   Output: v.stable_function
   ->  Result  (cost=0.00..0.51 rows=1 width=8) (actual time=0.138..0.138 rows=1 loops=1)
         Output: stable_function(), volatile_function()

...then we see both column expressions appearing in the plan, and the NOTICEs show that both functions have been executed.

There doesn't seem to be any explicit mention of this behaviour in the docs, so there are no hard guarantees as to whether or not an expression will be evaluated, and you should not rely on any side-effects your function calls might have.

But if your only concern is performance, then as long as you mark your functions as stable or immutable where appropriate, you can be reasonably sure (especially in simple cases like this) that they will not be evaluated unless they're needed.

(And while you're there auditing your volatility declarations, you might want to set the parallel safety flags as well.)


Why does using a variable make a difference in performance? I have a table that constantly doing insert, and I am trying to run a delete script to remove old record.

The delete script looks like this:

DECLARE @StarDate DATETIME = '2013-01-13';
DECLARE @DeleteTop Int = 2000

WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (@DeleteTop) FROM VehicleLocation
    WHERE
    MessageGenerationDate < @StarDate
    AND (NOT EXISTS
    (SELECT NULL FROM VehicleLocationTP
    WHERE VehicleLocationTP.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationGF WHERE VehicleLocationGF.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationInpt WHERE VehicleLocationInpt.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationFare WHERE VehicleLocationFare.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationOBD WHERE VehicleLocationOBD.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationAPC WHERE VehicleLocationAPC.VehicleLocationKey = VehicleLocation.VehicleLocationKey));
END;

When running this script, it causing LCK_M_IX wait type on the insert, causing the insert being blocked.

However, if I remove the variables, and simply go for

DELETE TOP (2000) FROM VehicleLocation...

I will no longer see the LCK_M_IX wait type.

What kind of optimization SQL-Server was trying to perform? Is it better to avoid using variables and instead specific the value explicit?

This question is related to a similar question: https://stackoverflow.com/questions/2293772/t-sql-query-performance-puzzle-why-does-using-a-variable-make-a-difference

Sometimes yes it's good to use statics.  In this case the SQL Server doesn't know what the value is in the variable when it's creating the plan so it's escalating to a table lock I'm guessing.  You could force the locks to be row locks on the delete statement which should fix the problem.


Intellisense not working because of code creating columnstore index I use SQL Server 2016 Developer Edition on Windows 10 64 bit. All the databases are local on my laptop.

I have a SQL script file, and Intellisense doesn't work on it. I have enabled Intellisense and tried various fixes suggested in this post:

IntelliSense not working but it is enabled

None of these fixes work for me; however, Intellisense works on all the other files. The only special thing of this file is its length: it has more than 600 lines of code.

It seems that the problem is caused by a command creating columnstore index. Here is a sample code:

SELECT TOP 100 *
INTO #test1
FROM tab1

CREATE CLUSTERED COLUMNSTORE INDEX idx ON #test1

SELECT TOP 100 *
INTO #test2
FROM tab1

I found that Intellisense stopped working after inserting the block of code in the middle! The outlining will also disappear after inserting the middle block of code.

Once I remove the middle block of code, the outlining and Intellisense will work again.

Can anyone help me test this sample case?

--

I tried to add semicolon an GO to separate code, but neither of them works for me. Now it seems that the only feasible solution is commenting that middle block out.

In case it's already enabled, press Ctrl + Shift + R. That will update Intellisense with used database.

Sometimes, if the used database is not frequent, that does not work instantly in SSMS, but the above will update Intellisense.

In case of long script related issue, go to Tools>Options>. In there in on the left side, search for Text Editor>Transact-SQL>IntelliSense.

There is an item called Maximum Script Size. It may be 100KB`. In the drop down, change it to 5MB at least, or to Unlimited per your preference. After setting this, you need to close SSMS and start it again. Some older versions required a restart of the system. That should work.


MDX Query for RANK regardless of hierarchy I'm hoping to add a calculation to my SSAS Cube (SSAS 2012) that will show the relative rank of whatever the current member is against all other members in that grouper, without specifying the groupers in advance.

For example, say I have a calculation already for Efficiency which is defined as $$/hour.

I would like to add an Efficiency Rank calculation as well that will tell me what relative position the current member has in it's set.

The Syntax I tried was

RANK([Measures].[Efficiency].CurrentMember, [Measures].[Efficiency])

Which I realize is incorrect.

I can't seem to locate a guide to this anywhere else, so I'm hoping there is some established best practice or pattern for this since it seems like it would be a relatively common business requirement.

Please explain in more detail what you meany by "groupers"?

For instance, if you want to rank a member within a set, you need to tell SSAS what the set is that you are ranking within.

RANK( Tuple, Set, Measure to rank by)

So

RANK( Machines.Machines.CurrentMember, Machines.Machines.AllMembers, Measures.Efficiency)

Now, I think that what you are trying to do is have that all members be dynamic?

The question is "How would you both specify the current member and the set?"

And here's a kicker: the frontend tool you're using will make a difference here as well


Running out of disk space, start saving new data to different disk I'm currently at around 7 billion rows on one table and approaching the max disk space for the entire database.

I need to start saving new data on the one large table to a new disk.

I've looked at partitioning by datetime, which seems the obvious choice
This is an interim solution before we get a new server in 6 months, but need a "now" solution.

How would I achieve this? Can I implement manual partitioning so all new data goes to the new disk?

Everything I've seen requires creating something like a monthly option, whereas I just want to put all new stuff on a new disk.

One option is to split the existing data by month, and then manually move the partitions around.

I'm not even sure if this is possible, so would appreciate some feedback on the best solution to achieve the above.

SQL Server 2017 Enterprise Edition.

The normal short-term solution here is to simply move some indexes and tables to the new filegroup to free up space in the primary filegroup.

But

  Can I implement manual partitioning so all new data goes to the new disk?

Yes you can.  The key idea is to create a new table on a partition scheme that is compatible with the existing table, and partitioned on a clustered index key column.  Then switch the table into a partition on that partition scheme, and split its partition function so new rows go to the new filegroup.

And I just discovered that you can create a partitioned table with only the "primordial" partition, and then switch a non-partitioned table into the primordial partition without needing a CHECK constraint on the non-partitioned table.

Here's an end-to-end demo:

use master
go
drop database parttest

go
create database parttest
go

use parttest
go
drop table if exists large
go
create table large(id int identity primary key, a char(1000), b int, c datetime);
create index ix_large_b on large(b);

--load some data
with q as
(
  select top 100000 row_number() over (order by (select null)) n
  from sys.messages m, sys.objects o
)
insert into large(a,b,c)
select replicate('z',n%989), n%39, getdate()-n%1000
from q;

--add a new filegroup with a single file
alter database current
  add filegroup newfg

alter database current
  add file (name = 'newfg_data', filename = 'c:\temp\newfg_data.mdf')
  to filegroup newfg

--create the new partition function and partition scheme
--but don't specify any boundary points, so the target
--table can be switched in without a check constraint
create partition function pf_large(int)
  as range right for values ()

 create partition scheme ps_large
   as partition pf_large all to ([Primary])

--create the table to switch into
create table large2(id int identity primary key, a char(1000), b int, c datetime)
 on ps_large(id);

--create the secondary indexes on the new table
--if you don't create them they won't be switched
create index ix_large2_b on large2(b) ;

alter table large switch to large2 partition 1

-- drop and rename
begin transaction
  drop table large
  exec sp_rename 'large2','large'
commit

--reset the identity values
dbcc checkident('large')
go

--split the partition function so new rows to to the new filegroup
alter partition scheme ps_large next used newfg

begin transaction
  declare @splitPoint int = 1+(select max(id) from large with (tablockx) )
  alter partition function pf_large() split range (@splitPoint)
commit

go

--load some more data
with q as
(
  select top 100000 row_number() over (order by (select null)) n
  from sys.messages m, sys.objects o
)
insert into large(a,b,c)
select replicate('z',n%989), n%39, getdate()-n%1000
from q;

go

--check that it went to the new filegroup
select i.index_id, p.partition_number, ds.name fg, au.data_pages
from sys.tables t
join sys.indexes i
  on t.object_id =i.object_id
left join sys.partitions p
  on p.object_id = t.object_id
  and p.index_id = i.index_id
left join sys.allocation_units au
  on (au.type in (1,3) and au.container_id = p.hobt_id)
   or(au.type = 2 and au.container_id = p.partition_id)
left join sys.data_spaces ds
  on ds.data_space_id  = au.data_space_id
where t.object_id = object_id('large')
order by ds.data_space_id, index_id, partition_number

outputs

index_id    partition_number fg           data_pages
----------- ---------------- ------------ --------------------
1           1                PRIMARY      14286
2           1                PRIMARY      174
1           2                newfg        14286
2           2                newfg        231

You will have to drop and recreate foreign key constraints referencing this table.  And recreating the FK constraints after the switch the switch require table scans during DDL, so can be lengthy offline operations.  To optimize for up time you can recreate the FK constraints with NOCHECK and CHECK them later.  Until then they will be enforced for DML but not trusted by the query optimizer.


Error installing Oracle Database Client 12c: "OracleMTSRecoveryService Control Utility has stopped working" The installation of the "Oracle Services For Microsoft Transaction Server" component fails when I attempt to install Oracle Database Client 12c (12.1.0.2.0) on Windows Server 2012 R2.

When installing that component, I first get an error that oramtsctl.exe has crashed:

Immediately afterwards, I get the error message "[INS-20802] Oracle Services For Microsoft Transaction Server failed.":

It seems that "INS-20802" is some sort of catch-all error, as none of the solutions I found were applicable to the problem. Usually the issues at this point were that the Control Utility isn't found. In my case it is found, but crashes when started.

This is the relevant information from the install logs:

INFO: ConfigClient.executeToolsInAggregate action assigned
INFO: Started Plugin named: Oracle Services For Microsoft Transaction Server
INFO: Found associated job
INFO: Starting 'Oracle Services For Microsoft Transaction Server'
INFO: Starting 'Oracle Services For Microsoft Transaction Server'
INFO: MTSInternalPlugIn: ... adding </ouiinternal>
INFO: Executing MTS
INFO: Command C:\Windows\system32\cmd /c call C:\app\lays\product\12.1.0\client_1\bin\oramtsctl.exe -new    -internal
INFO: ... GenericInternalPlugIn.handleProcess() entered.
INFO: ... GenericInternalPlugIn: getting configAssistantParmas.
INFO: ... GenericInternalPlugIn: checking secretArguments.
INFO: No arguments to pass to stdin
INFO: ... GenericInternalPlugIn: starting read loop.
INFO: Completed Plugin named: Oracle Services For Microsoft Transaction Server
INFO: Oracle Services For Microsoft Transaction Server failed.
INFO: Oracle Services For Microsoft Transaction Server failed.
INFO: ConfigClient.executeToolsInAggregate action performed
INFO: Exiting ConfigClient.executeToolsInAggregate method
INFO: Calling event ConfigToolsExecuted

What might be the issue here?

The issue in my case was that a DLL file was missing that prevented "oramtsctl.exe" from running. I found when I attempted to run the oramsctl.exe manually from its installation folder. The missing DLL file was in the Microsoft Visual C++ Redistributable Packages, which I installed to solve the issue.


Postgresql and MS Access unable to insert new data I have a Postgresql server with a data base and table, columns dont matter. My issue is my primary key is set to BIGSERIAL, it auto-increments. However, on MS ACCESS when I create a form to insert new entrys - I am unable to do so unless I enter a digit into the bigserial ID field. It will not automatically increment.

I've tried playing with the enabled and locked settings in Access with no luck.

Found this person with same issue that seemed to never have been resolved.

https://stackoverflow.com/questions/3065130/how-to-use-serial-autonumber-in-a-postgresql-access-combination

Either insert DEFAULT (unquoted) for that column, or specify the list of columns being inserted and omit the primary key column from that list.


SELECT lock only for inserts I need to get the MAX aggregate of certain field in a transaction. I use this value further inside the transaction. I want to avoid inserts in the entire table from which I'm getting the MAX while the transaction is completed.

I researched the isolation modes and lock hints available. The closest seems to be TABLOCKX, which is the only one that avoids inserts in the entire table (also updates and selects).

Since I no will be affected by updates or selects in the table I wish to avoid only inserts during the transaction.  This is possible?

UPDATE: The field on wich the MAX is performed is seted only on inserts, thus there's no risk of get stale results for updates.

I'm using SQL Server 2000.

Setting the isolation level to SERIALIZABLE would have the same effect:

  The highest isolation level, serializable, guarantees that a
  transaction will retrieve exactly the same data every time it repeats
  a read operation, but it does this by performing a level of locking
  that is likely to impact other users in multi-user systems.

TABLOCK will suffice, there is no need for TABLOCKX to achieve what you've described so far. If the transaction you mention later updates or inserts to the table then TABLOCKX, if it's just to read for the MAX it's unnecessary and other processes will at least be able to read from the table.

Whether you use the isolation level or TABLOCK hint depends on what proportion of the table you will be scanning to find the MAX aggregate. If you need to touch any significant percentage of the rows TABLOCK is logical. A small percentage and a desire to maintain some degree of concurrency then try SERIALIZABLE. TABLOCK would also eliminate the deadlock risk.

Both options would achieve your aim of preventing an inconsistent result from the aggregate.


Performance comparison between using Join and Window function to get lead and lag values I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a specific id.

I have used two methods to achieve this. One method is using join and another method is using the window functions lead/lag with clustered index on time and id.

I compared the performance of these two methods by execution time. The join method takes 16.3 seconds and the window function method takes 20 seconds, not including the time to create the index. This surprised me because the window function seems to be advanced while the join methods is brute force.

Here is the code for the two methods:

Create Index

create clustered index id_time
 on tab1 (id,time)

Join method

select a1.id,a1.time
   a1.value as value,
   b1.value as value_lag,
   c1.value as value_lead
into tab2
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.time-1= b1.time
left join tab1 c1
on a1.id = c1.id
and a1.time+1 = c1.time

IO statistics generated using SET STATISTICS TIME, IO ON:

Here is the execution plan for the join method

Window Function method

select id, time, value,
   lag(value,1) over(partition by id order by id,time) as value_lag,
   lead(value,1) over(partition by id order by id,time) as value_lead
into tab2
from tab1

(Ordering only by time saves 0.5 seconds.)

Here is the execution plan for Window function method

IO statistics

[

I checked the data in sample_orig_month_1999 and it seems that the raw data is well ordered by id and time. Is this the reason of performance difference?

It seems that the join method has more logical reads than the window function method, while the execution time for the former is actually less. Is it because the former has a better parallelism?

I like the window function method because of the concise code, is there any way to speed it up for this specific problem?

I'm using SQL Server 2016 on Windows 10 64 bit.

The relatively low row-mode performance of LEAD and LAG window functions compared with self joins is nothing new. For example, Michael Zilberstein wrote about it on SQLblog.com back in 2012. There is quite a bit of overhead in the (repeated) Segment, Sequence Project, Window Spool, and Stream Aggregate plan operators:

In SQL Server 2016, you have a new option, which is to enable batch mode processing for the window aggregates. This requires some sort of columnstore index on the table, even if it is empty. The presence of a columnstore index is currently required for the optimizer to consider batch mode plans. In particular, it enables the much more efficient Window Aggregate batch-mode operator.

To test this in your case, create an empty nonclustered columnstore index:

 -- Empty CS index
CREATE NONCLUSTERED COLUMNSTORE INDEX dummy
ON dbo.tab1 (id, [time], [value])
WHERE id < 0 AND id > 0;

The query:

SELECT
    T1.id,
    T1.[time],
    T1.[value],
    value_lag =
        LAG(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time]),
    value_lead =
        LEAD(T1.[value]) OVER (
            PARTITION BY T1.id
            ORDER BY T1.[time])
FROM dbo.tab1 AS T1;

Should now give an execution plan like:

...which may well execute much faster.

You may need to use an OPTION (MAXDOP 1) or other hint to get the same plan shape when storing the results in a new table. The parallel version of the plan requires a batch mode sort (or possibly two), which may well be a little slower. It rather depends on your hardware.

For more on the Batch Mode Window Aggregate operator, see the following articles by Itzik Ben-Gan:

What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 2
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 3


How much of a view is persisted when you create an index? Say I have a view defined by the following SQL:

SELECT t1.id, t2.name, t3.address
FROM Table1 as t1
INNER JOIN Table2 t2
  ON t1.id = t2.tID
INNER JOIN Table3 t3
  ON t1.id = t3.tID

From what I understand, if I create an index on this view then the data will become persisted, but I'm unclear what exactly gets persisted.  Say I create a unique clustered index on t1.id, will all three columns be saved to disk or will the values being pulled from table2 and table3 still be calculated at runtime?

Let me know if any of this doesn't make sense or if I have left anything important out.

All three columns are persisted to disk in the clustered index on the indexed view (no different, really, from a clustered index on a regular table). You can validate this using DBCC PAGE. I created the following structure in tempdb:

USE tempdb;
GO

CREATE TABLE dbo.a(a INT);
CREATE TABLE dbo.b(a INT, b DATETIME);
CREATE TABLE dbo.c(a INT, c CHAR(32));

INSERT dbo.a SELECT 1;
INSERT dbo.b SELECT 1, SYSDATETIME();
INSERT dbo.c SELECT 1, REPLICATE('c', 32);
GO

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
  SELECT a.a, b.b, c.c
  FROM dbo.a
  INNER JOIN dbo.b
  ON a.a = b.a
  INNER JOIN dbo.c
  ON a.a = c.a;
GO

CREATE UNIQUE CLUSTERED INDEX x ON dbo.v(a);
GO

Turned a trace flag on so I can use IND/PAGE:

DBCC TRACEON(3604, -1);
DBCC IND('tempdb', 'dbo.v', 1);

Results:

So I knew to check page 312:

DBCC PAGE(2, 1, 312, 3);

And here are the three values from the join, even though they're not all part of the unique aspect of the clustered index:


How can I improve a slow comparison query that have over partition and group by I have a query (see below) that is working just fine, when there aren't that many rows, around 5k.
However, if I run the same query on a table with around 80k rows the query takes between 30 and 60 seconds to complete.  Don't ask me why it fluctuates so much...
I need help or tips on how I can improve this query so it runs faster, preferable a lot faster, but still retains the same functionality.
The [ExternalTable] in the query is a external table that are pointing on a view that i have little control over (it is a view residing on a linked server). I have full control over the [Table].
The query below is shortened a little for readability; we have 7 columns that we check against in the real query.
The purpose of the query is to find new or updated rows in the [ExternalTable] compared with the [Table] and then output the new or changed rows. This is working just fine on smaller amounts of rows but it takes a very long time when the number of rows increase.
If it makes a difference, this runs on a SQL Server in Azure.
WITH CombinedTables AS
(SELECT SourceTable,foo, bar, data, ROW_NUMBER()
OVER (PARTITION BY foo, bar, data
ORDER BY foo, bar, data) Row_Nbr
FROM (SELECT '2' AS SourceTable, foo, bar, data FROM [ExternalTable]
         UNION ALL
         SELECT '1' AS SourceTable, foo, bar, data FROM [Table] WITH (NOLOCK)
) AS CombinedTables GROUP BY foo, bar, data, SourceTable
) SELECT DISTINCT * FROM CombinedTables WHERE CombinedTables.Row_Nbr < 2 AND CombinedTables.SourceTable = '2'

What can I do to improve the performance? Add indexes? Make it into a stored procedure?  Rewrite it somehow?
As you might have gathered, I'm a bit too inexperienced with SQL Server to solve this myself and I need some help from you smart people!
edit: added a sql fiddle
http://sqlfiddle.com/#!18/86075/1
edit 2: added actual executionplan image: http://prntscr.com/uuys04
edit 3: It might be that the query is fine and it's Azure that is limiting the query executiontime, I posted a new question about this: Tweak Azure Data IO operations to lower the DTU cost

Since [ExternalTable] is linked to a view on another database server, it probably makes sense to "materialize" the rows in [ExternalTable] into a local temp table, then run the query against that.
So, something like this:
CREATE TABLE #t_combined
(
    SourceTable char(1) NOT NULL
    , foo int NOT NULL
    , bar int NOT NULL
    , [data] int NOT NULL
    , INDEX ix CLUSTERED (foo, bar, [data], SourceTable)
);

INSERT INTO #t_combined WITH (TABLOCKX) (SourceTable, foo, bar, [data])
SELECT '2', foo, bar, [data]
FROM [ExternalTable] et;

INSERT INTO #t_combined WITH (TABLOCKX) (SourceTable, foo, bar, [data])
SELECT '1', foo, bar, [data]
FROM [Table] et;

;WITH CombinedTables AS
(
    SELECT SourceTable
        , foo
        , bar
        , data
        , ROW_NUMBER() OVER (PARTITION BY foo, bar, data ORDER BY foo, bar, data) Row_Nbr
    FROM #t_combined
    GROUP BY foo
        , bar
        , data
        , SourceTable
)
SELECT DISTINCT *
FROM CombinedTables
WHERE CombinedTables.Row_Nbr < 2
    AND CombinedTables.SourceTable = '2';


sql script repository I am a fresh DBA just being promoted, I am looking for few good sites which constains T-sql Scripts for DBA tasks. Is there a common place from where you can get a collection scripts to perform operation like tuning, re-indexing, DMV, restore, mirror etc.

regards Thanks for listening

This might be a good start: http://www.sqlservercentral.com/Scripts/


SQL Except Statement and Add in One more column How do I conduct a Except statement and Add in one more column?
Three tables with the exact same structure. (CustomerTransaction1,CustomerTransaction2, CustomerTransactionDiff)

I want the LoadDate from CustomerTransaction1. If not, I will have to rejoin on the primary key, and extract the load date.

I do not want to except/compare on the LoadDate, just add in the column.

Create table dbo.CustomerTransaction1
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
)

insert into CustomerTransactionDiff
( CustomerTransactionId, PurchasePrice,Quantity,Loaddate)
select
    CustomerTransactionId,
    CustomerName,
    PurchasePrice,
    Quantity
from dbo.CustomerTransaction
except
select
    CustomerTransactionId,
    CustomerName,
    PurchasePrice,
    Quantity
from dbo.CustomerTransaction2

Here is a sample solution (based on sp_BlitzErik's comment):

--demo setup
drop table if exists dbo.CustomerTransaction1;
drop table if exists dbo.CustomerTransaction2;
drop table if exists dbo.CustomerTransactionDiff;
Create table dbo.CustomerTransaction1
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);

insert into dbo.CustomerTransaction1(CustomerTransactionId,CustomerName,PurchasePrice,Quantity,Loaddate) values
(1,'Fred',100,5,'2018-10-25'),
(2,'Bob',200,1,'2018-10-25')

Create table dbo.CustomerTransaction2
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);
insert into dbo.CustomerTransaction2(CustomerTransactionId,CustomerName,PurchasePrice,Quantity,Loaddate) values
(2,'Bob',200,1,'2018-10-25')

Create table dbo.CustomerTransactionDiff
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);

--The solution
INSERT INTO CustomerTransactionDiff (
    c1.CustomerTransactionId
    ,CustomerName
    ,c1.PurchasePrice
    ,c1.Quantity
    ,c1.Loaddate
    )
SELECT c1.CustomerTransactionId
    ,c1.CustomerName
    ,c1.PurchasePrice
    ,c1.Quantity
    ,c1.Loaddate
FROM dbo.CustomerTransaction1 c1
WHERE NOT EXISTS (
        SELECT *
        FROM dbo.CustomerTransaction2 c2
        WHERE c2.CustomerTransactionId = c1.CustomerTransactionId
            AND c2.CustomerName = c1.CustomerName
            AND c2.PurchasePrice = c1.PurchasePrice
            AND c2.Quantity = c1.Quantity
        );

--verify differences
select * from dbo.CustomerTransactionDiff

| CustomerTransactionId | CustomerName | PurchasePrice | Quantity | Loaddate                |
|-----------------------|--------------|---------------|----------|-------------------------|
| 1                     | Fred         | 100.00        | 5        | 2018-10-25 00:00:00.000 |


Restore all applicable files in a backup device in T-SQL I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a differential backup is added and every hour a transaction log backup is added to the backup device.

When restoring this onto a new server using SSMS I first create a new database, then add the backup device and then restore from that backup device. I then have to manually select the applicable files:

The Full backup (always the first one in the list)
The latest differential backup
All transaction log backups that came after the last differential backup.

This works fine for the occassional restore, but I need to automate this in T-SQL.

There is some logic on which files to pick, but I don't even know how to get to the content of the backup device yet.

I would like to have a stored procedure that takes the name of the database and the backup device and restores the latest version. Anybody knows of such a thing?

Or do I miss something here and there is a simple command for this?

This procedure should do what you want:

create procedure dbo.dbrestore
@dbname nvarchar(50)
as
declare @fileno integer
declare @dumpdevice nvarchar(50)
declare @mediasetid integer
declare @position integer

select top(1) @dumpdevice=a.logical_device_name, @mediasetid=b.media_set_id, @position=b.position from backupmediafamily a inner join backupset b on a.media_set_id=b.media_set_id
where b.database_name='backuptest' and b.type='D' order by a.media_set_id desc, b. position desc

declare restore_cursor cursor for
select position from msdb.dbo.backupset where database_name=@dbname and type='D' and media_set_id=@mediasetid and position=@position
union
select  max(position) as position from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position
union
select position from msdb.dbo.backupset where database_name=@dbname and type='L' and media_set_id=@mediasetid and position>@position
and last_lsn>(select max(last_lsn)from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position)
order by position asc;

open restore_cursor
fetch next from restore_cursor into @fileno
while @@FETCH_STATUS = 0
begin
--comment the print statement and uncomment the exec statement to run
--exec('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+@fileno+';')
  print ('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+convert(varchar(4),@fileno)+';')
fetch next from restore_cursor into @fileno
end
close restore_cursor
deallocate restore_cursor
--exec('restore database ['+@dbname+'] with recovery;')
go

execution goes:

get the name of the dumpdevice based on the supplied database name. I'm assuming that they are all on the same device. The top is to work around having multiple media sets per database if using 'with format' for the initial DB, and to cope with the possibility of multiple full backups in the device

Then build up a union query consisting of:

The last full database backup (type=D) in the device

the last differential backup (type=I and has the greatest LSN (Log Sequence Number)) taken after the last full db backup.

the transaction logs (type=L) taken after the above diff.

returning just the position value, which maps to the FILE value in this case (http://msdn.microsoft.com/en-us/library/ms186299.aspx)

Loop through the result restoring them individually, and then a final restore with recovery to finish it off.

Call it as:

exec dbrestore 'db2restore'

I've tried it on a test db and it appears to work happily, and comparing it to the files SSMS wants to restore for the same operation it looks the same.

Provided as is, there's probably typos, I've probably missed a possible boundary condition, and there's no error checking.


Index for split string. PostgreSQL 10 I have the following string in db:

A/B/C/
A/B/D/
E/F/C/

It's the uri column.

The table is the following:

create table meta_info (
    id bigserial primary key,
    uri varchar,
    created timestamp with time zone,
    version varchar,
    binary_length bigserial
    )

In most cases I find the directory and want to find children.

E.g.,

SELECT tt.stem[2+1] as current, tt.stem[2+2]
as child from (SELECT regexp_split_to_array(uri, '/')
                      as stem FROM meta_info
                      where uri like 'A/B%' )
as tt group by tt.stem[2+1], tt.stem[2+2]

... , where "2" is the size of path to find (A/B% - size 2 , A/B/C% - size 3 , A/B/D/E%  - size 4)

E.g.,

SELECT tt.stem[4+1] as current, tt.stem[4+2]
as child from (SELECT regexp_split_to_array(uri, '/')
                      as stem FROM meta_info
                      where uri like 'A/B/C/D%' )
as tt group by tt.stem[4+1], tt.stem[4+2]

At the moment I have 400000 rows. It takes too much time to run that sql query.
Can I create an index to improve the performance?

The following index didn't help.

CREATE INDEX idx ON meta_info (regexp_split_to_array(uri, '/'));

The query still is quite slow.
Especially is slow the following sql:

EXPLAIN ANALYZE
    SELECT DISTINCT((regexp_split_to_array(uri, '/'))[1]) as branch
    FROM meta_info

I need to run the mentioned sql when I need to get the unique first levels (folders).

..........................
what about text_pattern_ops, yep, I know it. I am already using it. But thanks for split_part(uri, '/',1). Wow, it works really faster. I have replaced all regexp_split_to_array with split_part. Thanks a lot!

If you will be constantly parsing the data out on the fly, you should probably just stored it parsed in the first place.

If you do want to store it as flat strings there are some things you can do to speed it up.

  where uri like 'A/B/C/D%'

This part can be sped up creating an index

create index on meta_info (uri text_pattern_ops);

How much of a speed up this is depends on how selective 'A/B/C/D%' is.

  EXPLAIN ANALYZE
      SELECT DISTINCT((regexp_split_to_array(uri, '/'))[1]) as branch
      FROM meta_info

This can be sped up 2 to 3 times if you avoid the unnecessary regexp machinery and the array machinery.

EXPLAIN (ANALYZE, BUFFERS)
SELECT DISTINCT split_part(uri, '/',1) as branch
FROM meta_info;


Datetime fields return as integers in view We ran into a curious case today where a view that's doing "select * from table" had a couple of datetime fields come back as integers.  This was working previously up until very recently.  These is nothing in the view itself that is forcing these columns to return as integers.

Another curious thing is that rewriting the view to explicitly select all of the columns makes the data come back as datetimes.  I'm thinking maybe it's some sort of bad cache plan, but I'm unsure.

What could the culprit be of the data pulling back incorrectly?

This has happened because the definition of the table has changed since the view was last modified. Maybe someone altered one or more columns, or the table was dropped and re-created with columns in a different order, different types, etc.

Temporary fix:

EXEC sys.sp_refreshview N'dbo.viewname';

Permanent fix:

Stop using SELECT * in views (and maybe even consider always using WITH SCHEMABINDING for all views).
I blogged about the SELECT * symptom here.
I blogged about the benefits of SCHEMABINDING here, and they include being unable to alter a table when a view is schema-bound to it.


Publication shows up in secondary server I have created  log shipping and the primary server is also used for transactional replication. Now after creating log shipping I have noticed the publisher shows up on the secondary and it wont let me delete the database on the secondary as it is being used for replication. How do I delete the database from the secondary?

When you've restored the database on the secondary you actually moved all its properties, including the fact it's a publisher. In this case you don't have a working replication, just the attributes for replication enabled. So this won't let you drop the db as you want.

The simplest way to remove replication metadata for this db is by using the system stored procs:

-- ON THE PUBLISHER
use YourDb
Go

exec sp_cleanupdbreplication
exec sp_removedbreplication
GO

Just make sure you're running this script on the secondary, the one you want to drop, not on the production db.


Extract four digits from a column by regexp I am sitting on a database where one column contains two data types ;)

Example

"1";"When the Wind Blows   0275";"James Patterson "
"4";"The Reluctant Fundamentalist      1026";"Mohsin Hamid";

The user thought it would be easily searchable this way...

Now I am trying to fix this, taking the ID out of the title field and putting it into it's own column.

How do I select the title with a regexp so that I only get the four digit number at the end of the title? That number can start with a zero and the title could contain more digits than just those 4.

There is also no guarantee that the digits are the last four characters in the title, sometimes there are some spaces following. SUBSTRING will not work in this case

thanks for help!

If the four digits you need to extract are either exactly at the end of the string or are followed only by spaces, it would probably be easiest to extract them using the RIGHT() function after removing the trailing spaces using the RTRIM() function:

SELECT
  RIGHT(RTRIM(YourColumnName), 4) AS SomeCode
FROM
  YourTableName
WHERE
  ...
;


Creating procedure in mysql I am trying to build a procedure that i would use to check my data format before processing it to the net stop and i need this procedure to check the format of my file. So what i need is, my database to use a specific folder and then check the data thats loaded into the table.
How can I get it work, any ideas, suggestions?

MySQL cannot reach outside itself to do anything with files.

Checking/cleansing incoming data is best done in your client language (PHP, Java, VB, ...)


Finding all date ranges that overlap a target date range Say I wish to store when staff are on holiday (FromDate, ToDate) and then I wish to find all staff that are on holiday between two given dates (QFromDate, QToDate).

Now assume that I have many such records (more than will fit in the server’s RAM) and need to do this query often.

Now assume that I also have sick_leave tables, shift_pattern tables, pay_rate tables, etc – all that have FromDate and ToDate with the requirement to join them based on overlapping dates.

How should I store the date ranges and how can the queries be written to run fast?

(The choice of RDBMS is not fixed, but being able to run on any “standard” RDBMS is of value unless doing so has a large adverse effect.)

I have posted some answers that I have considered, but don't like!   However they may help other people.

You can generate a customized index table, for example, that has a row for all year&week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another vacation, as an inner join.

pseudo example:

create table rangeindex (
  vacation_id bigint,
  year int,
  week int,
  primary key (year,week,vacation_id),
  index (vacation_id))

select v2.*
    from vacation v1
    join rangeindex r1 on r1.vacation_id = v1.vacation_id
    join rangeindex r2 on r2.year = r1.year and r2.week = r1.week
    join vacation v2 on v2.vacation_id = r2.vacation_id
where v1.vacation_user = ?
    -- and the usual start/end comparisons to filter unwanted pseudo hits

Similar things are used for example spatial coordinate stuff, dividing linear coordinates to grid cells that can be indexed and scanned more easily.

Update: fixed primary key


Postgres DDL and DML best practices and coding standards Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend.

I am looking for something similar to what Google has for programming.

Thank you in advance.

Like all coding standards, every project is going to have preferences.  I am not saying you will like my preferences but I will offer them.  Keep in mind that LedgerSMB is heading towards a model where half of the code will be SQL so we have had to put some thought into these.

The first thing we did was decide that DDL should be documented for autodocumentation tools rather than human reviewers. If you are going to go the human reviewer route (which reduces your documentation), you do something like:

CREATE TABLE asset_item (
    id serial not null unique, -- internal id
    tag text primary key, -- accounting control code
    description text not null,
...
);

That is great and all, but it means that people have to read your DDL files to get a sense of what you want to tell them.  If you go the autodoc documentation tool route, you get something different:

CREATE TABLE asset_item (
    id serial not null unique,
    tag text primary key,
    description text not null,
...
);

COMMENT ON TABLE asset_item IS $$ This table stores the fixed asset items.$$;
COMMENT ON COLUMN  asset_item.id IS $$Internal id, used for joins$$;
COMMENT ON COLUMN asset_item.tag IS $$Accounting control code$$;

This makes the comments available through general documentation tools which is very helpful in a large project.  The big limitation is that comments must follow definitions.

For DML we usually justify the queries, lining up the verbs on one side and conditions or identifiers on the other.  An example might be:

   SELECT ai.id, ai.tag, ai.description, coalesce(ai.start_depreciation, ai.purchase_date),
          adm.short_name, ai.usable_life
           - months_passed(coalesce(ai.start_depreciation, ai.purchase_date),
                                  coalesce(max(r.report_date),
                                           ai.start_depreciation,
                                           ai.purchase_date))/ 12,
          ai.purchase_value - ai.salvage_value, ai.salvage_value, max(r.report_date),
          sum(rl.amount), ai.purchase_value - sum(rl.amount)
     FROM asset_item ai
     JOIN asset_class ac ON (ai.asset_class_id = ac.id)
     JOIN asset_dep_method adm ON (adm.id = ac.method)
LEFT JOIN asset_report_line rl ON (ai.id = rl.asset_id)
LEFT JOIN asset_report r on (rl.report_id = r.id)
    WHERE r.id IS NULL OR r.approved_at IS NOT NULL
 GROUP BY ai.id, ai.tag, ai.description, ai.start_depreciation, ai.purchase_date,
          adm.short_name, ai.usable_life, ai.purchase_value, salvage_value
   HAVING (NOT 2 = ANY(as_array(r.report_class)))
          AND (NOT 4 = ANY(as_array(r.report_class)))
          OR max(r.report_class) IS NULL
 ORDER BY ai.id, ai.tag, ai.description;

Not everyone likes this style but we find it easy to read, follow, and maintain.

A few big points I would make:

Avoid inline views if you can.  Formally define them or use CTE's if you need them.  Inline views make maintenance harder because the simple structure of the query and rigid order is degraded.
Sometimes subqueries are needed but try to avoid them where you can for the same reason.
When deciding whether to use select * or a named column list consider whether you expect a named type back.  If you do, then select * is preferable.  Otherwise typically you want to avoid select *.
If you are going to use stored procedures consider your contracts carefully.  Changes to interfaces in these are often not as easy as you'd like unless you have some sort of service discovery framework in between.


Min/ Max dates based on results of query Need to find the Max and Min dates using the results of a query. The following query is working but have to add additional code to find the Max/ Min dates based on its results (results of dates from last 2 AND statements). Using Oracle SQL Developer.

SELECT SO.COM_DATE, SO.RAISED_DATE, SE.SERV_NO, SO.OR_NO
FROM S_ORDER SO, S_SERV SE
WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
AND TO_DATE('2018-07-30','YYYY-MM-DD')

Tried to add the Max/ Min statements to the end  but getting an Invalid Statement error. Thanks for any help.

Results of the query will therefore be:

COM_DATE | RAISED_DATE | SERV_NO | OR_NO
2018-07-05 | 2018-07-04 | 69780 | GHYUYR
2018-06-27 | 2018-06-08 | 69780 | HHUYTT
2018-03-10 | 2018-02-04 | 69780 | JJUHYT

Therefore, after the Min/ Max code added, the results should ONLY return:

COM_DATE | RAISED_DATE |
2018-07-05 (max)| 2018-02-04 (min)

This should work to get the min and max date for you.

SELECT MIN(RAISED_DATE), MAX(RAISED_DATE), MIN(COM_DATE), MAX(COM_DATE)
FROM   (SELECT SO.COM_DATE, SO.RAISED_DATE, SE.SERV_NO, SO.OR_NO
        FROM S_ORDER SO, S_SERV SE
        WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
        AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
        AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
        AND TO_DATE('2018-07-30','YYYY-MM-DD'))

If you want the other data in the query you will need to add a group by

SELECT SO.COM_DATE, MIN(SO.RAISED_DATE), MAX(SO.RAISED_DATE), SE.SERV_NO, SO.OR_NO
FROM S_ORDER SO, S_SERV SE
WHERE SO.CU_NO='286243' AND SE.SERV_NO='69780'
AND SUBSTR (SE.ACCT_NO, 1,8)=SO.CU_NO
AND SO.RAISED_DATE +4/24 BETWEEN TO_DATE('2017-11-01','YYYY-MM-DD')
AND TO_DATE('2018-07-30','YYYY-MM-DD')
GROUP BY SO.COM_DATE, SE.SERV_NO, SO.OR_NO


Return only One Row From Dataset I have three tables and all tables hold a primary key of empnum this is an image of my desired result set

However my query is returning 9 rows instead of 3!  See screenshot

This is my DDL -->

CREATE TABLE [dbo].[__One](
    [empnum] [char](12) NOT NULL,
    [empid] [char](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[__Three](
    [empnum] [char](12) NOT NULL,
    [empjobdesc] [char](64) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[__Two](
    [empnum] [char](12) NOT NULL,
    [empprimarydutydesc] [char](64) NULL,
    [emphourssched] [int] NULL,
    [empdesiredsellamt] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[__One] ([empnum], [empid]) VALUES (N'ec28191', NULL)
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Rigalo')
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Fitzgerald')
GO
INSERT [dbo].[__Three] ([empnum], [empjobdesc]) VALUES (N'ec28191', N'Gooey Glue')
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'RGM', 23, 5520)
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'FZG', 12, 4840)
GO
INSERT [dbo].[__Two] ([empnum], [empprimarydutydesc], [emphourssched], [empdesiredsellamt]) VALUES (N'ec28191', N'GGRW ', 19, 5000)
GO

And this is the query I write that returns the 9 rows instead of the 3...

;With CTE As (
Select * from __One
),
CTE2 As (
Select * from __Three
), CTE3 As (
Select * from __Two
)
Select Distinct a.empnum, a.empid, c.empprimarydutydesc, b.empjobdesc, c.emphourssched
FROM CTE a
LEFT OUTER JOIN CTE2 b
ON a.empnum = b.empnum
LEFT OUTER JOIN CTE3 c
ON b.empnum = c.empnum

What in the syntax needs to be changed in order to only return my desired 3 rows?

All of your employee numbers are the same. So right now every row will join to every row (1 * 3 * 3 = 9)  and nothing ties tables 2 and 3 together (for example a foreign key to show that FZG belongs to Fitzgerald).  Adding the foreign key will fix the problem you are seeing.


Questions for 'Tips on Converting to Galera' on MariaDB KB Some questions about the excellent writing Tips on Converting to Galera.

Is "cross-colo writing" cross connect datacenters write?

  Also, you should use "START TRANSACTION READONLY" wherever
  appropriate.

this means the transaction will be handled only by one node (no replication latency)?. Are there cases when a SELECT without START TRANSACTION READONLY involving more than one node? SELECT marks it's a read?

  Transaction "size"

  (This section assumes you have Galera nodes in multiple colos.)
  Because of some of the issues discussed, it is wise to group your
  write statements into moderate sized BEGIN...COMMIT transactions.
  There is one latency hit per COMMIT or autocommit. So, combining
  statements will decrease those hits. On the other hand, it is unwise
  (for other reasons) to make huge transactions, such as
  inserting/modifying millions of rows in a single transaction.

Is this the max transaction size? Is it recommend to use close to max transaction size? Can you give a value what number you mean with moderate sized BEGIN...COMMIT transactions

MariaDB [(none)]> select @@wsrep_max_ws_size;
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
|          1073741824 |
+---------------------+
1 row in set (0.00 sec)

  Critical reads

  Here is a 'simple' (but not 'free') way to assure that a
  read-after-write, even from a different connection, will see the
  updated data.

when an app needs to use a read-after-write? What's the use case for read-after-write? But anyway then this read-after-write should be in the same transaction? Please a little more info about read-after-write and when to use it.

  Variables that may need to be different (...)
      innodb_doublewrite - ON: When an IST occurs, want there to be no torn pages? (With FusionIO or other drives that guarantee atomicity,
  OFF is better.)

what are torn pages? I translated torn in my native language and now even more confused.

  Miscellany (...) DROP USER may not replicate?

Any more details and infos? We use automated DROP USER since a few years and never noticed issues. Did we just didn't saw the problems?

(I guess I better answer this.)

"colo" = "co-location" = "datacenter" -- meaning a non-trivial latency between nodes.

SELECT ... FOR UPDATE should be in a transaction.

An example of where you would like to do stuff on one node without bothering the others:  You need to compute a bunch of stuff to get the data to INSERT in a table.  But you are happy to do the computation on a single node and not worry if the node crashes.  For example, you might have some other mechanism to recover from a crash.  Think "idempotency".

"Transaction size" -- well that should probably say "how long the transaction takes to run".  At some level, lots of rows being modified makes it take longer, but that is not the only cause of "size".

Too big -- more chance of cross-node deadlocks.  Too small -- more effort and delay in doing the cross-node COMMIT.

Critical read:  User write a comment on a blog, then clicks submit.  The next page comes up but fails to show that comment.  Why?  The write went to one node; the read went to another node, but it had not gotten there yet.  (This is perhaps the only realistic example of why Galera does not claim to be fully synchronous.  The check in the gcache only checks for "will it successfully commit", not "has it already been written to disk".)

"Torn page" -- An innodb unit of data is a 16KB block.  The typical disk thinks in units of 512-byte blocks.  InnoDB can recover from a power failure if its blocks are entirely written or not written at all.  But if only part (N*512, but less than 16K) is written it is a "torn page".  So, InnoDB stands on its head with "doublewrite buffer" to be able to recover from such.  This involves a non-trivial amount of I/O.  But, since Galera can completely recover a dead server, you can allow torn pages if you are willing to rebuild the node after a power failure.

DROP USER involves MyISAM tables in the database mysql.  MyISAM tables are (or were) not replicated.  I suspect that Galera has made sure that DROP USER works.  However, since all the GRANT stuff involves MyISAM, I suspect there are cases where it can fail.  (MySQL 8.0 moves GRANT stuff into InnoDB tables, thereby making it crash-safe.)

Thanks for pointing out the flaws in the article.  I will try to make improvements to http://mysql.rjweb.org/doc.php/galera and the MariaDB link you gave.  (Some day)


Extracting string after and before a character '-' or '<' using Sql Query I have table with one column 'UDF_WorkLocation_6' which contain string.Please Review following table.
UDF_WorkLocation_6
MUMBAI - Maharastra<6>
AHMEDABAD - Gujarat<1>
MUMBAI - Maharastra<6>
CHANDIGARH - Chandigarh<34>
NULL
DELHI - Delhi<42>
NULL
INDORE - Madhya Pradesh<56>

Here it is column name 'UDF_WorkLocation_6' which contain string in table such as 'INDORE - Madhya Pradesh<56>'.
I want split string from '-' then show output into two columns,In First Column getting string before '-' like 'INDORE' from 'INDORE - Madhya Pradesh<56>' string & second column getting string  after '-' and also remove charter after '<'this like Madhya Pradesh from 'INDORE - Madhya Pradesh<56>'.
So output in table like,
**Output Table**

 Column1    Column2
 MUMBAI      Maharastra
 AHMEDABAD   Gujarat
 MUMBAI      Maharastra
 CHANDIGARH  Chandigarh
 NULL        NULL
 DELHI       Delhi
 NULL        NULL

I have tried to prepared query for split string.
For instant, string is 'MUMBAI - Maharastra<6> ' and got output for second column like 'Maharastra<6>' using below sql query
SELECT LTRIM(SUBSTRING(AccountMasterUDF.UDF_WorkLocation_6, CHARINDEX('-',
        AccountMasterUDF.UDF_WorkLocation_6) + 2, 100))

But i also want two remove charters after '<' this and query for getting string 'MUMBAI' in first column.
Can anyone please help me to prepared query without split function?

The LEFT and RIGHT functions are helpful for this kind of parsing. They only return X number of characters from that side of a VARCHAR like so:
SELECT RTRIM(LEFT(AccountMasterUDF.UDF_WorkLocation_6, CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) - 1)) AS Column1,
LEFT(LTRIM(RIGHT(AccountMasterUDF.UDF_WorkLocation_6, LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6))), CHARINDEX('<',LTRIM(RIGHT(AccountMasterUDF.UDF_WorkLocation_6, LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)))) - 1) AS Column2

As you can see, combined with CHARINDEX to locate the - in both cases, and LTRIM and RTRIM to remove any unneeded whitespace.
In the second case you need to apply the LEFT function on top of your output from the RIGHT function to do one more search and removal of the ending .
Also here's a more formatted version of the stacked functions above that I find more readable if you prefer:
SELECT
    RTRIM
    (
        LEFT
        (
            AccountMasterUDF.UDF_WorkLocation_6,
            CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6) - 1
        )
    ) AS Column1,
    LEFT
    (
        LTRIM
        (
            RIGHT
            (
                AccountMasterUDF.UDF_WorkLocation_6,
                LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)
            )
        ),
        CHARINDEX
        (
            '<',
            LTRIM
            (
                RIGHT
                (
                    AccountMasterUDF.UDF_WorkLocation_6,
                    LEN(AccountMasterUDF.UDF_WorkLocation_6) - CHARINDEX('-', AccountMasterUDF.UDF_WorkLocation_6)
                )
            )
        ) - 1
    ) AS Column2


Oracle to PostgreSQL: Create type object In Oracle:

create or replace type tr_del as object (row_id varchar2(18), pk_id number);

select tr_del(last_name, employee_id)
from employees where
department_id=30;

output:

(Raphaely, 114)
(Khoo, 115)
(Baida, 116)

How can I achieve the same in PostgreSQL?

Although the syntax is different, you can achieve exactly what you want.

You can create type by using the script below;

CREATE TYPE tr_del AS (row_id varchar(18), pk_id integer);

Then your table structure should look like this;

CREATE TABLE employees (
   department_id integer,
   any_field_name tr_del,
   ...
);

After that you can use it in the query like this;

SELECT
    (any_field_name).row_id AS last_name,
    (any_field_name).pk_id AS employee_id
FROM
    employees
WHERE
    department_id = 30;

Fiddle is here

You may want to read Composite Types section.


Best way to display number of overspent projects I have the first view displayed as:

CREATE VIEW Projects AS
    SELECT DepartmentName,
        COUNT(*)NumberOfProjects,
        OfficeNumber,
        Phone
    FROM   DEPARTMENT AS D JOIN PROJECT AS P
            ON D.DepartmentName = P.Department

    GROUP BY Department;

However, I want to display a "NumberOfOverBudgetProjects" for each department in this view.
I created a view which lists which projects are going over their hourly budget:

CREATE VIEW Budgets AS
    SELECT Department,
        MaxHours AS ProjectMaxHours, SUM(HoursWorked) AS TotalHoursWorked,
        SUM(HoursWorked) - MaxHours AS Balance
    FROM   PROJECT AS P JOIN ASSIGNMENT AS A
        ON P.ProjectID = A.ProjectID
    GROUP BY P.ProjectID

;

Marketing department should list 2 projects being over budget and Accounting and Finance should both list 0.

Any ideas how I can incorporate a column of "OverBudgetProjects" in the original view? Or by creating a new view?

I am using SQL Server 2012, but this, or something close, should work in MySQL:

USE Test;
GO
CREATE TABLE Departments
(
    Department NVARCHAR(255)
);
CREATE TABLE Projects
(
    Department NVARCHAR(255)
    , Project NVARCHAR(255)
    , MaxHours INT
);
CREATE TABLE Assignments
(
    Project NVARCHAR(255)
    , HoursWorked INT
);
GO
CREATE VIEW Budgets AS
    SELECT ProjectInfo.Department
        , COUNT(ProjectInfo.Project) AS NumberOfProjects
        , SUM(Projectinfo.OverBudget) AS NumberOfOverbudgetProjects
    FROM (
        SELECT D.Department
            , P.Project
            , P.MaxHours AS ProjectMaxHours
            , SUM(HoursWorked) AS TotalHoursWorked
            , CASE WHEN (SUM(HoursWorked) > P.MaxHours) THEN 1 ElSE 0 END AS OverBudget
        FROM   Departments AS D
            INNER JOIN Projects AS P ON D.Department = P.Department
            INNER JOIN Assignments AS A ON P.Project = A.Project
        GROUP BY D.Department
            , P.Project
            , P.MaxHours
        ) AS ProjectInfo
    GROUP BY ProjectInfo.Department;
GO
INSERT INTO Departments (Department) VALUES ('Marketing');
INSERT INTO Departments (Department) VALUES ('Sales');
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Marketing', 'MarketingProject1', 10);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Marketing', 'MarketingProject2', 20);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Sales', 'SalesProject1', 15);
INSERT INTO Projects (Department, Project, MaxHours) VALUES ('Sales', 'SalesProject2', 25);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject1', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject2', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('MarketingProject2', 5);
INSERT INTO Assignments (Project, HoursWorked) VALUES ('SalesProject1', 14);
SELECT * FROM Budgets;

This returns the folloowing resultset, showing only the Marketing Department, which is over budget.


Can't create schema for a db with PostgreSQL Use psql -U postgres to login.
postgres=> CREATE DATABASE mydb;
postgres=> \c mydb;

You are now connected to database "mydb" as user "postgres".

When create schema, got
mydb=> CREATE SCHEMA main;
ERROR:  permission denied for database mydb

What will be the possible denied permission that didn't set?
Edit
When I run \l, it showed
   Name    |        Owner         | Encoding |   Collate   |    Ctype    |               Access privileges
-----------+----------------------+----------+-------------+-------------+-----------------------------------------------
 mydb      | mydb_administrator   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/mydb_administrator                     +
           |                      |          |             |             | mydb_administrator=CTc/mydb_administrator
 postgres  | postgres             | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ...

\du got
      Role name       |                         Attributes                         |                          Member of
----------------------+------------------------------------------------------------+-------------------------------------------------------------
 mydb_administrator   |                                                            | {rds_iam}
 mydb_application     | Cannot login                                               | {}
 mydb_user            |                                                            | {rds_iam,mydb_application}
 postgres             | Create role, Create DB                                    +| {rds_superuser}
                      | Password valid until infinity                              |
 ...

All the things were executed by postgres user.

If you create the schema as user mydb_administrator, it will work.
But you messed up your installation by removing the SUPERUSER property from postgres. If that was not deliberate and you have no other superuser, you will have to start PostgreSQL in single-user mode to repair that. See the many existing anwers for details about that.


Mysql Workbench crashing when connecting I have created an user i intend to give access to one db only.
But when i try connecting with him, the program crashes. When i connect with the root user, it works.

 create user 'test'@'localhost';
 GRANT ALL PRIVILEGES ON sbb2_test.* To 'test'@'localhost' IDENTIFIED BY 'test';
 FLUSH PRIVILEGES;

Then i have verified he has the permissions for the sbb2_test db only:

 SHOW GRANTS FOR 'test'@'localhost'

Output:

'GRANT USAGE ON *.* TO \'test\'@\'localhost\' IDENTIFIED BY PASSWORD \'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29\''
'GRANT ALL PRIVILEGES ON `sbb2_test`.* TO \'test\'@\'localhost\''

Then i have tried connecting with the new user:

I have entered "test" as the password, and the program crashed.
Why?
These are the last lines from the wb.log file:

15:58:40 [INF][      Workbench]: Current version given by meta info is: 6.1.7

15:58:41 [INF][      Workbench]: Exiting as another instance of WB is already running.
15:58:41 [INF][      Workbench]: Running the application

15:58:41 [INF][      WBContext]: Parsing application arguments.

15:58:41 [INF][      WBContext]:     C:\Program Files (x86)\MySQL\MySQL Workbench 6.1 CE\MySQLWorkbench.exe

15:58:46 [WRN][   WBContext UI]: Server instance  has no attached connection, deleting it

15:58:46 [WRN][   WBContext UI]: Server instance  has no attached connection, deleting it

15:59:17 [INF][     SSH tunnel]: Starting tunnel

15:59:17 [ERR][  GRTDispatcher]: exception in grt execute_task, continuing: Exception: Access denied for user 'test'@'localhost' (using password: NO)

15:59:30 [INF][          Admin]: Auto-created instance profile for connection

15:59:30 [ERR][      WBContext]: Unsupported server version: mariadb.org binary distribution 10.1.26-MariaDB

Looks like a known issue with it not handling an exception error properly and also the wrong version:
https://bugs.mysql.com/bug.php?id=79364

Description:
WB 6.3.5 (and also older release like 6.1.7) can connect to MariaDB.

> How to repeat:
> 1. Download WB 6.3.5 CE 64 bit (32 bit has the same issue)
> 2. Download MariaDB 10.0.22 64 bit windows
> 3. Start MariaDB using mysqld --console
> 4. Create a new connection using WB : testing the connection works
> 5. Connect to the database ( may or may not crash) : a SQL tab opens
> 6. Connect to the same database : crashes : it says MySQL workbench has stopped working : a new SQL tab can not open

[20 Nov 2015 23:32] Godofredo Miguel Solorzano
Thank you for the bug report. Looks like you have the reason for in the log:

15:14:22 [ERR][      WBContext]: Unsupported server version: mariadb.org binary distribution 10.0.22-MariaDB

Are you using containers in your environment?

edit:
For now I solved it this way:from the menu Database => Manage Connections... I have created and saved the connection for the "test" user, so that it appears on the start screen ( when you launch WB, the saved connections appear as a list of gray buttons , and you can launch them directly from there).


mysql 5.7 json filter and features products sorry for my english!
I need use this column(feature) for show features, filters for products and in some language(It depends on the country).
I have products table(innodb) and a column(json) features, the column feature is like this:

{feature:{
        [{"en": "System Operation",
        "it": "Sistema operativo",
        "value": "IOS 8"
    },
        {
        "en": "Memory Ram",
        "it": "Memoria Ram",
        "value": "16 Gb"
    },
        {
        "en": "display",
        "it": "schermo",
        "value": "5.5\""
    }]}

Here have just 2 language but can have 30 or 100 language.

For show feature i use foraech in php I should optimize... maybe in mysql if is possible.

$language_detect = substr($_SERVER['HTTP_ACCEPT_LANGUAGE'], 0, 2);
if($language_detect == "it") {$lang_det = "it";}
elseif($language_detect == "en") {$lang_det = "en";}
elseif..
elseif...

foreach($feature->feature as $row) {
      foreach($row as $key => $val){
        if($key == '$lang_det'){
              echo $key->$lang_det .'  ' . $val . ' : ' ; }
        if($key == 'value'){
                echo $val . '<br>' ; }
        }
    }

The really first problem is: how I can make the query like this result?:

        [{
        "en": "System Operation",
        "value": "IOS 8"
    },
        {
        "en": "Memory Ram",
        "value": "16 Gb"
    },
        {
        "en": "display",
        "value": "5.5\""
    }]

and second problem is: how can I search product/s with the feature/s selected?
In mysql sure :)

Thank you!!

For the first problem, AFAICT, there is no way to use SQL to project certain properties of a JSON object.  I think you will have to do that in the application layer.

For the second problem, how to search for products with a certain feature: I assume you have a table products with one row for each product.  The table contains a column of type JSON which contains the features of the product as you have shown.  If so, I think the following query should find the products where the value of the feature called "display" in English has the value of 5.5":

SELECT * FROM products
WHERE JSON_EXTRACT(feature,
    REPLACE(JSON_UNQUOTE(
        JSON_SEARCH(feature, 'one', 'display', NULL, '$.feature[*].en')),
        '.en', '.value')) = "5.5\"";

JSON_SEARCH will give you the path to the feature called 'display'.  To find the value of the same feature, we replace '.en' in the path with '.value' and use JSON_EXTRACT to look up the value at this path.  (JSON_UNQUOTE is needed to convert the JSON string from JSON_SEARCH to an SQL string to be passed to JSON_EXTRACT).


SQL Server 2000 Page Splits per second higher han 12 million? I was reading about index fill factors & performance, and out on a whim I tried the query given by the webpage on the company's production SQL Server 2000 server:

SELECT *
FROM MASTER.dbo.sysperfinfo
WHERE counter_name ='Page Splits/sec' AND
OBJECT_NAME LIKE'%Access methods%'

Imagine my shock when I saw the result is 12164778 (yes, that's larger than 12 million).

Is it actually possible to have 12 million page splits per second? Or am I reading the numbers incorrectly? Or is there a mistake in the query?

Please read Calculating Counter Values to understand what performance counter values are and how you use them. Per second values are computed from two samples delta divided by time.

Jason Strate has a SQL Server specific discussion here.


Does creating an index on BIT(n) attribute in MySQL make sense? I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2.

I've seen some mentions on the internet that BIT attributes are not index-friendly in MySQL. Without further elaboration though.

So the question is - do MySQL indexes work well with BIT(n) attributes or I better find some other ways, like restrict n to, say, 32 and use INT instead?

Unless you are querying for an exact match on the whole value stored in the bit column, an index will not help, because it can't generally be used.  On the remote chance that you are, then it would.

The reasons an index can't be used when storing a bitmapped value and then querying based on the state of one or more bits include the fact that the commonly-available index types in MySQL are only useful for exact-value or range matching, and bitwise comparisons aren't truly either of those things.

Because of the nature of this limitation, it also holds true if you use any of the integer data types or even the SET data type.

Granted, for example, testing the state of the high bit on an 8-bit number is the same as testing whether the number is >= 128, which is indeed a range match and could be accomplished using a b-tree index, but to make use of an index for this test, the optimizer would have to understand that this what you're "really" asking when what you actually asked for is WHERE bin_col & b'10000000' ... the optimizer isn't going to realize that.

My inclination for storing bitmapped values would be to use an UNSIGNED [something]INT column, since BIT columns in MySQL are actually more analogous to CHAR/BINARY columns than they are to integer data, but this will ultimately depend on your application as well as your storage engine.

MyISAM apparently stores different bit columns together in the raw row data, so if you're not using even increments of 8/16/32/64, there might be a small storage advantage there when using BITs vs INTs -- but unless you're already using MyISAM, I wouldn't think this would be of enough of an advantage to make you consider using it.

The MEMORY and InnoDB engines allocate the smallest standard integer size that can hold the required number of bits in a BIT column.

The SET data type also stores the values as an unsigned integer of 1, 2, 3, 4, or 8 bytes, as required by the number of labels you define for the bits.  It is easier on the eyeballs when you're looking at your stored bitmapped data, because if you select from it without casting the result back to an integer (either explicitly or implicitly with SELECT column_name + 0), you get back a comma-separated list of the labels of the bits that are set to 'on'... It doesn't offer any optimization in querying but it does give you the enhancement of expanding your bits in to labels without any real penalty compared to using a raw integer column.


Showing data that is not already in the data base I have the following problem. I want to search a query in an existing data base and that the result shows me the entries of the query that are NOT already in the database.

Minimal example:

| ID | name |
| 1 | Paul |
| 2 | Clara |

ID Query (1,3)

The result should be 3!

So I want to do something like this:

Select * from names Where ID Not in (1,3);

But well this is obviously not correct.

Furthermore, I don't want to insert an additional table with the query!
Does anyone know a solution for this problem?

SELECT args_list.id
FROM ( SELECT 1 id
       UNION ALL
       SELECT 3 ) args_list /* the ID's you need to check for presence */
LEFT JOIN names ON args_list.id = names.id
WHERE names.id IS NULL

In MySQL 8+ you can transfer the id's list into the query as CSV string and parse it to separate values in CTE. It will be something like (not tested)

WITH src AS ( SELECT '1,3' src ),
     RECURSIVE
     num AS ( SELECT 1 num
              UNION ALL
              SELECT num + 1
              FROM num
              WHERE num < (SELECT MAX(LENGTH(src) - LENGTH(REPLACE(src, ',', ''))
                           FROM src) ),
     ids AS ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(src, ',', num), ',', -1)
              FROM src, num )
SELECT ids.id
FROM ids
LEFT JOIN names ON ids.id = names.id
WHERE names.id IS NULL


SQL Server Linked Server to Azure SQL using Azure AD I am trying to set up a linked server from an on-prem SQL Server to an Azure SQL instance.  The Azure SQL instance uses Azure AD for authentication.  When we attempt the connection we get this error

The server it can not open is the piece after the @ symbol in the typical azure ad user name ie:something@company.com
I have searched the internet for answer and I can not find any reference to used Azure AD for a linked server connection.  Is this simply not possible?

Not possible today.  AD auth works, AAD auth is not implemented in linked servers.  I have not tried ADFS, however.


Select statement on tables with the same name and schema but in different databases I have a table that lists the names of the databases that have tables with the same schema. Each customer has a different database.

Is there a way to run a select on same table in multiple databases?

The first query I wrote was to get a list of databases concatenated the table name.

Select concat(database_name,'.','TableA') from dbnamelist

This results in output like

database1.TableA
database2.TableA

Now I want to select a value in the column type but across different tables.

Select type from database1.TableA,database2,TableA

Is it possible to do this in a single SQL statement?

SELECT  GROUP_CONCAT(CONCAT(table_schema, '.', tableA))
    FROM information_schema.tables
    WHERE table_name = 'tableA';

will generate the list you need.

Figure out what the query needs to look like (and I do not think your example is correct), and modify this SELECT to generate the desired query.

Then copy and paste it into mysql commandline tool to run it.


Stored Procedure return unwanted cursor result Context
I have created a stored procedure that simulates a merge command for an OLTP in-memory table. It used the simulated merge for upsert moving massive data form a normal table to an OLTP one.
Procedure
This is the code:
CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;

        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status);
    END

    CLOSE Employee_Cursor;
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END

The cursor upserts a range of rows like this because OLTP tables (target) do not support MERGE commands.
Problem
If I execute my SP I will get a result the first cursor selected FETCH and a list of single row counts.

Question
I understand that this is normal behavior, but I try to suppress this to get a simpler result.
What I want:

No table result from CURSOR select
A sum of affected row count as single number of all update/insert operations

The empty FETCH was indeed an error and solves the ROW result problem (point 1).
Looks like this now:
CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status);

        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    END

    CLOSE Employee_Cursor;
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END

Instead of using a cursor*, write the update and insert operations explicitly as set-based operations:

CREATE OR ALTER PROCEDURE cache.MoveInverterData
(
    @sourceInverterID bigint,
    @from datetime2(7)
)
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    DECLARE @RowsAffected integer = 0;

    -- TODO: Add error handling

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

        -- Update existing rows
        UPDATE CID
        SET [Status] = DID.[Status]
        FROM [data].InverterData AS DID
        JOIN cache.InverterData AS CID
                WITH (SNAPSHOT)
            ON CID.InverterID = DID.InverterID
            AND CID.[Timestamp] = DID.[Timestamp]
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from;

        SET @RowsAffected += @@ROWCOUNT;

        -- Insert new rows
        INSERT cache.InverterData
            (InverterID, [Timestamp], [Status])
        SELECT
            DID.InverterID,
            DID.[Timestamp],
            DID.[Status]
        FROM [data].InverterData AS DID
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from
            AND NOT EXISTS
            (
                SELECT 1
                FROM cache.InverterData AS CID
                    WITH (SNAPSHOT)
                WHERE
                    CID.InverterID = DID.InverterID
                    AND CID.[Timestamp] = DID.[Timestamp]
            );

        SET @RowsAffected += @@ROWCOUNT;

    COMMIT TRANSACTION;

    -- TODO: Use the @RowsAffected result
END;

Ensure both tables have a (non-hash) index on InverterID, [Timestamp].

* Unlike some other database engines, cursors are usually quite inefficient in SQL Server. The overhead and per-row operations almost always make a set-based solution faster and more efficient.


How can I properly merge the rows of several table with default values? Problem
I have three tables that are structured like so:
t1
 id | count_1
--------------
 1  | 9
 2  | 4
 3  | 3

t2
 id | count_2
--------------
 1  | 2
 3  | 3

t3
 id | count_3
--------------
 1  | 1
 4  | 8

id is unique in each table.  Note that not all ids occur in each table.  Here is the SQL to create those tables if you'd like to test.
I'm trying to merge all those tables with a column for each count, defaulting to zero if there is no count for that particular id.  Like this:
 id | count_1 | count_2 | count_3
----------------------------------
 1  | 9       | 2       | 1
 2  | 4       | 0       | 0
 3  | 3       | 3       | 0
 4  | 0       | 0       | 8

Attempt
I thought this was a natural use case for a full outer join, like this:
SELECT
  COALESCE(t1.id, t2.id, t3.id) as id,
  COALESCE(t1.count_1, 0) as count_1,
  COALESCE(t2.count_2, 0) as count_2,
  COALESCE(t3.count_3, 0) as count_3
FROM
  t1
FULL OUTER JOIN t2
  ON t1.id = t2.id
FULL OUTER JOIN t3
  ON t1.id = t3.id
ORDER BY id ASC;

But this returns a result with non unique ids, where each row is just a row from one of the original tables with zeroes filling in the remaining columns:
 id | count_1 | count_2 | count_3
----------------------------------
 1  | 9       | 0       | 0         # <- should
 1  | 0       | 2       | 0         # <- be
 1  | 0       | 0       | 1         # <- one row
 2  | 4       | 0       | 0
 3  | 3       | 0       | 0         # <- should also be
 3  | 0       | 3       | 0         # <- one row
 4  | 0       | 0       | 8

Evidently I don't understand outer joins as well as I thought I did.  Can anyone show me the correct way to do this?

You could use FULL JOIN but the code gets a bit messy - at least for my taste. With 3 tables it's not so bad, you'd only need to change:

FULL OUTER JOIN t3
  ON t1.id = t3.id

to:

FULL OUTER JOIN t3
  ON COALESCE(t1.id, t2.id) = t3.id

but with more tables, it gets rather ugly. The other option is to gather all distinct id values and then LEFT JOIN all the tables:

SELECT
  d.id,
  COALESCE(t1.count_1, 0) AS count_1,
  COALESCE(t2.count_2, 0) AS count_2,
  COALESCE(t3.count_3, 0) AS count_3
FROM
  ( SELECT id FROM t1
  UNION
    SELECT id FROM t2
  UNION
    SELECT id FROM t3
  ) AS d
  LEFT JOIN t1 ON t1.id = d.id
  LEFT JOIN t2 ON t2.id = d.id
  LEFT JOIN t3 ON t3.id = d.id
ORDER BY id ;


SQL 2005 Replication Create/Delete Tables, Views, and Procs I have replication setup on a couple SQL2005 servers.

According to http://msdn.microsoft.com/en-us/library/ms151870(v=sql.105).aspx replication works when changing an item.

How do I have replication pick up new tables and proced

Adding tables (or articles) involves adding the article to the publication, generating a new snapshot, and synchronizing the subscription(s) to apply the schema and data for the newly added article.

See Add Articles to and Drop Articles from Existing Publications


How to detect in which schema a procedure is called? I'm struggling with a problem in Oracle 12.1 regarding to procedures.

We are using a data isolation approach based on schemas. We use one schema as PUBLIC and each project has exactly the same tables and procedures, but different data.

Problem:
I need to get the schema name where the procedure was called in order to update a row for a table in the PUBLIC schema that holds all the different schemas.

Header definition

CREATE OR REPLACE PACKAGE PKG_DONE
AUTHID DEFINER
AS
  PROCEDURE ACK_FROM_WEB(UUID INTERNAL.UUID%TYPE, USERNAME PUBLIC.USERS.USER_NAME%TYPE);

END PKG_DONE;

Body definition

CREATE OR REPLACE PACKAGE BODY PKG_DONE AS
  PROCEDURE ACK_FROM_WEB(P_UUID INTERNAL.UUID%TYPE, P_USERNAME PUBLIC.USERS.USER_NAME%TYPE)
  AS
  BEGIN
    INSERT INTO ACK_WEB_USER(UUID,USERNAME) VALUES (P_UUID,P_USERNAME);
    UPDATE PUBLIC.ACK_CONFIRMATIONS SET ASSIGNED = P_USERNAME WHERE UUID = P_UUID AND SCHEMA_NAME = USER;
  END ACK_FROM_WEB;

END PKG_DONE;

It works perfectly in our website as we set the session with the SCHEMA when operating, but now we need external confirmation, via email and it only works with PUBLIC schema for security reasons. I would like to retrieve the schema where I call the procedure instead of USER in the statement UPDATE PUBLIC.ACK_CONFIRMATIONS SET ASSIGNED = P_USERNAME WHERE UUID = P_UUID AND SCHEMA_NAME = USER;.

Is there any command/dbms procedure or anything where I can get it?

You can use one of the functions of Oracles owa_util's package named WHO_CALLED_ME.

To demonstrate this feature, we can create a table which will store the results of the call to Oracle's procedure.

create table T_CALLSTACK (
    OWNER_NAME      varchar2(30),
    OBJECT_NAME     varchar2(30),
    LINE_NUMBER     number,
    CALLER_INFO     varchar2(100),
    INSERTDATE      date
);

As a test procedure which will insert some values we use:

create or replace procedure P_CALLED_BY_T is

    v_Owner          varchar2(30);
    v_PackageName    varchar2(30);
    v_LineNumber     number;
    v_CallerT        varchar2(100);

begin

    owa_util.WHO_CALLED_ME (v_Owner, v_PackageName, v_LineNumber, v_CallerT);

    -- v_Owner will contain the schema name of the procedure which
    -- called this procedure itself
    insert
      into T_CALLSTACK (OWNER_NAME, OBJECT_NAME, LINE_NUMBER, CALLER_INFO, INSERTDATE)
    values (v_Owner, v_PackageName, v_LineNumber, v_CallerT, sysdate);

end P_CALLED_BY_T;

The procedure WHO_CALLED_ME will return the schema's name as well as the package name and the line number inside the calling package body, caller-T is the type of database object which called our custom procedure P_CALLED_BY_T. If the caller itself is just a procedure, the variable v_PackageName will hold the procedure's name instead of a package name.


SQL Server Connection Problem I'm completely stuck on a connection problem that we're experiencing for users connecting to our SQL Server instance from a particular domain using a particular application. The error that we're seeing is (from Windows Event log):
2020-11-30 00:11:22.33 Logon       Error: 17806, Severity: 20, State: 14.
2020-11-30 00:11:22.33 Logon       SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: xxx.xxx.xxx.xxx]
2020-11-30 00:11:22.33 Logon       Error: 18452, Severity: 14, State: 1.
2020-11-30 00:11:22.33 Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xxx.xxx.xxx]

Users are able to connect to the database over the same session from an old legacy app that uses the old ADO database driver. But our newer .NET Framework app is unable to connect due to this error. Also, it works when I run the app from my sysadmin account. As a hapless developer with a limited understanding of SQL Server security I'm a bit out on thin ice when it comes to further diagnosing the problem here. Does anyone have any ideas what could be causing this problem?

Your newer .NET Framework app seem to be using Windows authentication to connect to SQL Server
If that is your intent, make sure the app itself runs on user machines on behalf of domain user that has a Windows-authenticated login at SQL Server
For example, if your SQL Server has login [Domain/Name.Surname] account, make sure your newer .NET Framework app runs on behalf of that domain account, then it should be able to connect to SQL Server
p.s. I am seeing this error from time to time when our developers launch SSMS on behalf of their local Windows accounts and trying to use Windows authentication to connect to SQL Server, instead of launching SSMS on behalf of their domain user accounts (which have corresponding logins at SQL Server)
p.p.s. if your intent was actually use SQL authentication for connecting app to SQL Server, remove Integrated Security = True/SSPI from connection string...


Person-to-Address Relationship Cardinality — One-to-One or Many-to-One (In this context)? I have four tables:

Province (province_id, name)
City (city_id, name, province_fk)
Another Smaller subset under city say... City_sub (sub_id, name, city_fk)
Address table (address_1, address_2, sub_fk, person_fk)

Had an argument that it should be N-1, i.e. many-to-one, (actually he said N-N [many-to-many] but I limited the address for a user to be 1 only so let's change it) because there might be 2 persons living on the same house

But how would you query it to ensure that there will be no repeated data in the Address table? Address lines itself is already vulnerable. There are some people who type "street", "st.", and misspelled "stret".

Well what do you guys say? I think the saying "base your model on the real world" has limitations.

I am currently siding with my opinion which is 1-1, i.e. one-to-one, (1-N [one-to-many] if I returned to allowing persons to have multiple addresses).

The person I argued with tells me that this would have "data quality issues".

The easiest and more realistic real world scenario to maintain and query is going to be either 1-1 or 1-N, choose whichever is appropriate for your business logic. Several of the products I've worked on have used either of these methods and they are far easier to maintain than trying to get N-1 to work.

Maintaining N-1 for addresses would very challenging because you will inevitably run into misspelling and different grammar scenarios (as you mentioned), but additionally you will get convoluted logic to maintain the single record reference on a table that may not necessarily be static.

Something else we've learned is to avoid separate State, County, and City reference tables as they can create some convoluted logic for querying addresses. Try turning those tables into a single hierarchical Jurisdiction table and store the 'smallest' appropriate reference (usually the town) for the address record. The hierarchy maintains the other references and allows easy expansion when necessary.

If you're using MS SQL they have a HierarchyID type for this specific use case, but you can also create your own with a simple path style NVARCHAR field combined with a type field. Some text indexing and trailing wild card queries will cover all your expansion and filtering needs. Even if using MS SQL I would recommend using the NVARCHAR option alongside the HierarchyID as it generally performs better than the HierarchyID functions in most cases.


shrink database in always on availability hi i was using this query for shrinking the database log file

USE Tfs_CoE;
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (Tfs_CoE_Log, 1);
GO
ALTER DATABASE Tfs_CoE
SET RECOVERY FULL;
GO

it was working fine until i have added the database to always on availability group and now i get this error

  Msg 1468, Level 16, State 2, Line 4
  The operation cannot be performed on database "Tfs_CoE" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  Msg 5069, Level 16, State 1, Line 4
  ALTER DATABASE statement failed.
  The log for database 'Tfs_CoE' cannot be shrunk until all secondaries have moved past the point where the log was added..

  (1 row(s) affected)
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Can anyone help me in shrinking the log file of the database which has grown to 650gb which is configured for always on availability group actual size of the database is 39gb only ?

I have checked with taking backup of transaction log even but that dont work for me the log file size remains same 650gb.

For a database to be in an Availability Group it needs to be in FULL recovery mode. Part of your script tries to set it to Simple mode.

The bigger question of course is, why are you shrinking your log at all?

Edit: OK, having seen your edit - the error indicates (one of) your secondary database(s) is not up to date, I'd look into why that might be... You won't be able to truncate the log until all secondaries are up to date.