SQL How to Convert Single Tenant Databases to a Multitenant Database We have a legacy application. For some reason, someone decided to create a database for each customer. So we have 500+ databases with the same schema.

I would like to convert into One multi-client database.
The Original customer database does not have CustomerId. New Singular database does.

We are thinking of replacing old databases with Views, so the legacy application insert/update will still work.

Old Customer Database:

CREATE TABLE [CustomerOne].[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [QuantityBought] [int] NULL,
)

New Reporting Database has CustomerId:

CREATE TABLE ReportingDB.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL,
    [QuantityBought] [int] NULL,
)

Replace Old Database with Views

create view [CustomerOne].[dbo].[CustomerTransaction]
as
select
    [CustomerTransactionid]
    ,1 as [CustomerId]
    ,[quantitybought]
from ReportingDB.[dbo].[CustomerTransaction]
where Customerid = 1
with check option

This Fails:

-- Attempt inserting into View
insert into CustomerOne.dbo.Customertransaction
(Quantitybought)
values (4)

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'customerid', table 'ReportingDB.dbo.customertransaction'; column does not allow nulls. INSERT fails.
The statement has been terminated.

How do I get the final statement to succeeed without a Trigger? Otherwise we will have to utilize some kind trigger to get it working. Maybe there is no way to easily obtain unified database.

We are trying to avoid changing the old application code (insert and update statements).

sql-server-2016
This can be made to work using SESSION_CONTEXT (new in SQL Server 2016). In previous versions CONTEXT_INFO could be used, but it is less robust (cannot be read-only, only one slot per session).
Example
First, set a DEFAULT on the underlying table's CustomerId column:
CREATE TABLE ReportingDB.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL
        DEFAULT TRY_CONVERT(integer, SESSION_CONTEXT(N'CustomerID')),
    [QuantityBought] [int] NULL,
);

Then define the view as follows:
CREATE OR ALTER VIEW dbo.CustomerOneTransaction
AS
    SELECT
        CT.CustomerTransactionid,
        CT.Customerid,
        CT.QuantityBought
    FROM ReportingDB.dbo.CustomerTransaction AS CT
    WHERE
        CT.Customerid = 1
        AND TRY_CONVERT(integer, SESSION_CONTEXT(N'CustomerID')) = 1
    WITH CHECK OPTION;

Each session would need to set the CustomerID key once using sys.sp_set_session_context:
EXECUTE sys.sp_set_session_context
    @key = N'CustomerID',
    @value = 1;

If the key should be immutable for the life of the connection (typically true), use the @read_only option:
EXECUTE sys.sp_set_session_context
    @key = N'CustomerID',
    @value = 1,
    @read_only = 1;

This arrangement allows your insert query to work, and also ensures that CustomerID always matches the session key when the view is used to insert or update, otherwise a CHECK_OPTION error is returned. For deletion, illegal operations are ignored rather than raising an error.
This answer addresses the question asked, but in practice I would almost always prefer a database per customer, for security, manageability, and availability reasons.
Demo: db<>fiddle
Additional Information
You can also use this pattern with Row-Level Security to enforce customer isolation without views. Database-per-customer is the pattern used most for Software-as-a-Service companies (not banks and finance companies - the benefits of customer database isolation don't really make any sense for many other industries).

Multi-tenant applications with elastic database tools and row-level security - Microsoft Docs
SQL Server Databases in the Cloud – Single Tenant or Multi Tenant? - Dr Greg Low
Row Level Security Limitations, Performance and Troubleshooting - Aaron Bertrand


Using aggregate function on data masked column returns zeroes Recently we have implemented dynamic data masking in SQL Server 2019 database in order to hide sensitive information from developers. However, for testing purposes I'd like them to see close-to-real values, so my mask looks like this:
CREATE TABLE fin.Salaries
(
    TargetMonth    DATE            NOT NULL,
    Login          VARCHAR(100)    NOT NULL,
    Department     VARCHAR(100)    NOT NULL,
    AmountUSD      MONEY           MASKED WITH (FUNCTION = 'random(500, 5000)') NOT NULL,
    CONSTRAINT PK_Salaries PRIMARY KEY CLUSTERED (TargetMonth, Login, Department)
)

It works fine for simple queries, however aggregate functions seem to always return zero values. I haven't found anything on this in the documentation, so the question is whether this is an intended behavior and is there any way to get non-zero values from aggregated data masked column?
SELECT   TargetMonth,
         AVG(AmountUSD) -- Returns all zeroes
FROM     fin.Salaries
GROUP BY TargetMonth

Since dynamic data masking is performed right before returning the query result to the user, applying functions such as AVG will not have the masked data as a resultset.
You can see this in the execution plan, the masking function appears right before the select (in other words, returning the data to the user):

While the actual aggreggate happens in the compute scalar right behind the masking function:

With [EXPR1008] and [EXPR1009] being a count and a sum:
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="SUM([Test].[dbo].[Salaries].[AmountUSD])">

And, with the datamask function being applied after the aggregate functions, it is set to 0 as a result of the DataMask([Expr1002],0x05000000,(1),(0),(0),(0)) function.
This does not mean that all DataMask function executions on aggreggates and the corresponding datatypes will return 0, only that in this case it does.


Query optimization ndbcluster + order by I'm not really all too familiar with the ndbcluster storage engine of MySQL, but I've recently written an application that does some very simple queries. However, a co-worker of mine (who is now on vacation, or I'd ask him about it, of course) left a TODO in the comments next to what I thought to be a pretty harmless query.

The comment read "TODO: Avoid sorting on PK when querying on MySQL cluster"

Granted, there are some things I'm aware of when working on a cluster (like using less JOIN's) but I gathered the index (and therefore the PK) lookups were effectively O(1) operations, so a simple ORDER BY someID DESC wouldn't hurt performance.
I've been looking around on google, but I can't quite find a definitive answer as to what this ORDER BY means on a cluster. I can make a few educated guesses, but still...

What's more, I can't really think what query I can use instead. Basically, I have a generic method that enables me to fetch the last N rows based on 1 or 2 values. The simplified version of the query looks something like this:

SELECT *
    FROM db.tbl
WHERE user_Id = ?
  AND req_action = ?
ORDER BY tbl_Id DESC
LIMIT 0,?; -- 0,1 is default, though

Most of the time, the limit, though is 0,1, so I came up with this alternative approach that enables me to not use the ORDER BY clause, but it reduces the flexibility of my method, rather significantly:

SELECT *
    FROM db.tbl
WHERE
    tbl_Id = (
        SELECT MAX(tbl_ID)
            FROM db.tbl
        WHERE user_Id = ?
          AND req_action = ?);

But in that case, I'm always going to end up with either an empty resultset, or a single row.

My questions:

Is it true that ORDER BY on the primary key is not advisable on a cluster
if so, what alternative constructs should I be looking at to maintain the flexibility of my initial query, while sticking to good practices for cluster queries?

Things to keep in mind:

The dataset I'm querying is certain to have hundreds of thousands, if not millions of rows.
The table will be used heavily, both read and write operations, 30 RW/s is not exceptional
I can write stored procedures, but we only use them as a last resort.
An EXPLAIN EXTENDED of both queries would have me believe that the first (my initial query) is still preferable, but I ran it on a vagrant box, and just noticed the test tables use InnoDB storage engine, so I take it I can't trust those results :)

Right, I've found the answer to my own question, and thought I'd post it here in case anyone else would ever have the same basic question:

Given that a cluster shard the data across numerous nodes, there is no real guarantee that an AUTO_INCREMENT primary key will reflect the order in which records were added. Especially when dealing with multiple master replication.
Details can be found here

In essence, the answer to my question boils down to this:
Each node is given a range of available increment-values for the next N inserts. However, you have no real say in which node will insert the next row, and thus you can't be absolutely sure that the next row to be inserted will receive the MAX(pk_id_val) + 1 as a PK value.
A simple diagram to clarify:

 +------------+      +------------+
 |   Node 1   | <==> |   Node 2   |
 +------------+      +------------+
 |   PK 1-3   |      |   PK 4-6   | (Range of available increment vals)
 +------------+      +------------+
 INSERT INTO cluster_tbl (field) VALUES ('test');
 INSERT INTO cluster_tbl (field) VALUES ('test');
 INSERT INTO cluster_tbl (field) VALUES ('test');

If all three insert queries are sent to Node 1, then all is fine and dandy, and the PK's will be 1, 2 and 3 respectively. However, if the first query is sent to node 1, the second to Node 2, and the third back to node 1 again, then the PK's will be 1, 4 and 2 respectively.
In which chase a SELECT * FROM cluster_tbl ORDER BY pk_field DESC LIMIT 0,1; query will serve up the second row that was inserted, and not the third.

A possible solution: create an index on an inserted field, defined as follows:

`insert` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

And order the query by descending timestamp values:

SELECT *
FROM cluster_tbl
ORDER BY insert DESC
LIMIT 0,1;

That, for me at least, did the trick


How to reduce redundant time-series data in MySQL into a function? i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this:

How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but in time-series data i can not just do this, this would hide the information, that in between the real time 15:19:45 and 15:19:55, the value did not change. So i thought about saving the delta-time and value in a separate table and only save the first entry and mark it, but i dont know if this is the best way to do so.

YOUR SAMPLE DATA

DROP DATABASE IF EXISTS rep;
CREATE DATABASE rep;
USE rep
CREATE TABLE timeseries
(
    id             INT NOT NULL AUTO_INCREMENT,
    polltime       DATETIME NOT NULL,
    lastupdatetime DATETIME NOT NULL,
    name           VARCHAR(20) NOT NULL,
    value          VARCHAR(20) NOT NULL,
    PRIMARY KEY    (id),
    KEY series_ndx (lastupdatetime,name,value,polltime)
);
INSERT INTO timeseries
(polltime,lastupdatetime,name,value) VALUES
('2016-09-09 15:19:45','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:19:50','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:19:55','2016-09-09 15:19:44','x','15,2'),
('2016-09-09 15:20:00','2016-09-09 15:19:56','y','16,05'),
('2016-09-09 15:20:05','2016-09-09 15:19:58','c','16,12'),
('2016-09-09 15:20:10','2016-09-09 15:20:12','x','15,98');
SELECT * FROM timeseries;

PROPOSED QUERY

SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
FROM timeseries GROUP BY lastupdatetime,name,value;

PROPOSED QUERY EXECUTED

mysql> SELECT SEC_TO_TIME(MAX(polltime)-MIN(polltime)) timediff,lastupdatetime,name,value
    -> FROM timeseries GROUP BY lastupdatetime,name,value;
+----------+---------------------+------+-------+
| timediff | lastupdatetime      | name | value |
+----------+---------------------+------+-------+
| 00:00:10 | 2016-09-09 15:19:44 | x    | 15,2  |
| 00:00:00 | 2016-09-09 15:19:56 | y    | 16,05 |
| 00:00:00 | 2016-09-09 15:19:58 | c    | 16,12 |
| 00:00:00 | 2016-09-09 15:20:12 | x    | 15,98 |
+----------+---------------------+------+-------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

Please note that you will have to index this table to make the aggregation fast


Method to Obtain Lossless Join Boyce-Codd Normal Form (BCNF) Decomposition I have been told a way to obtain lossless join BCNF but I don't know how to calculate candidate keys (also called super key[s] in some cases) and trivial dependencies.

I have been given the following relation:

R = (A, B, C, D, E, F)

...where R is relation name, and A, B, C, D, E, F are its attributes.

The given functional dependencies (FDs) are:

A B → C D E F
C → D
E → F

The algorithm says that the first FD is not non-trivial, but here A B is a super key so we don't break original relation based on this.

Moving to the second FD, it's neither non-trivial nor C is candidate key. So, we break the relation:

R1 = (A, B, C, E, F) and R2 = (C, D)

Moving to the third FD it's nor non-trivial neither E is candidate key. So, we break the relation again:

R11 = (A, B, C, E), R12 = (E, F) and R2 = (C, D)

Hence, we obtained Loss Less BCNF.

But, I always get confused on how to calculate candidate key(s) and see if a FD is non-trivial, although I am quite aware of the definitions1. I also googled and read some documents but still didn't understood this properly. Please help me to figure out candidate key(s), super key(s), non-trivial FD(s) in the easiest way possible so that I never struck with the concept again.

Why do we only see left one's (A B, C, E) for candidate key and not the right one's ( F, D, C D E F) in the three FDs written above?

1 My definitions:

Super key: A set of one or more attributes which taken collectively, allow us to uniquely identify an entity in an entity set.

Candidate Key: A super key for which no proper subset is also a super key.

For determining the candidate keys:
 1. Determine the attributes not present in the RHS of the FDs
 2. Find the closures of those attributes
 3. Identify those closures that determine R. These will be your candidate keys

For determining the super keys:
Include the candidate keys with all possible combinations of the rest of the attributes

For determining trivial dependency:
$A \supseteq B$, B is trivially dependent upon A. Locate those FDs.


Trying to get this MySQL Trigger working create trigger check_td before
for each row begin

declare v_code_ID varchar(25);
declare c_Worker1 cursor for select code_ID from Worker1;
declare c_Worker2 cursor for select code_ID from Worker2;

if inserting or updating Worker1 then
  open c_Worker2;
  fetch c_Worker2 into v_code_ID;

while c_Worker2%found loop
if v_code_ID = :new.code_ID then

  SIGNAL SQLSTATE '-1'
        SET MESSAGE_TEXT = 'Error A.';

end if;
fetch c_Worker2 into v_code_ID;
end loop;

close c_Worker2;
end if;

if inserting or updating Worker2 then
open c_Worker1;
fetch c_Worker1 into v_code_ID;

while c_Worker1%found loop
if v_code_ID = :new.code_ID then

  SIGNAL SQLSTATE '-1'
        SET MESSAGE_TEXT = 'Error B.';

end if;
fetch c_Worker1 into v_code_ID;
end loop;

close c_Worker1;
end if;

end;

I come from Oracle databases and I'm trying to get this MySQL Trigger working. The error says that the problem is in the 4th line (or around) but after searching, I found that this way is the correct way to declare varchar variables in MySQL.

Can somebody give me a hand? It's slightly difficult because some syntax changes a bit.

Thank you very much, any help is appreciated.

The way of declaring VARCHAR is correct
The error is that you have to specify the table, and the action. i.e. ON worker1 BEFORE {INSERT|UPDATE|DELETE}
"if inserting or updating Worker1 then" I think you need two triggers if you want the action to be performed BEFORE INERT and BEFORE UPDATE
Change the delimiter, and end your trigger with it.
If the provided code is not meant to be pseudopod, I recommend reading about MySQL triggers before start coding them.


MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels? In MySQL documentation it's written:

  If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used.

Why statement-based logging format is not working with READ COMMITTED or READ UNCOMMITTED?

The semantics of those isolation modes cannot be guaranteed in SBR since the Slave is unlikely to execute the statements of multiple threads in the same order.

More specifically, from the Change Log:

----- 2008-11-14 5.1.30 General Availability -- Bugs Fixed -- -----

With statement-based binary logging format and a transaction isolation level of READ COMMITTED or stricter, InnoDB printed an error because statement-based logging might lead to inconsistency between master and slave databases. However, this error was printed even when binary logging was not enabled (in which case, no such inconsistency can occur). Bug #40360


Should the index on an identity column be nonclustered? For a table with identity column, should a clustered or non-clustered PK/unique index be created for the identity column?

The reason is other indexes will be created for queries. A query which uses a nonclustered index (on a heap) and returns columns that are not covered by the index will use less logical I/O (LIO) because there are no extra clustered index b-tree seek steps?

create table T (
  Id int identity(1,1) primary key, -- clustered or non-clustered? (surrogate key, may be used to join another table)
  A .... -- A, B, C have mixed data type of int, date, varchar, float, money, ....
  B ....
  C ....
  ....)

create index ix_A on T (A)
create index ix_..... -- Many indexes can be created for queries

-- Common query is query on A, B, C, ....
select A, B
from T
where A between @a and @a+5 -- This query will have less LIO if the PK is non-clustered (seek)

select A, B, C
from T
where B between @a and @a+5

....

Clustered PK on identity column is good because:

It increase monotonously so no page splits when inserting. It's said a bulk insert can be as fast as on a heap (nonclustered) table
It's narrow

However, will the queries in the question be faster without setting it clustered?

** Update:**
What if the Id is the FK of other tables and it will be joined in some queries?

By default the PK  is clustered and in most cases, this is fine.
However, which question should be asked:

should my PK be clustered?
which column(s) will be the best key for my clustered index?

PK and Clustered index are 2 differences things:

PK is a constraint. PK is used to uniquely identify rows, but there is no notion of storage. However by default (in SSMS), it is enforced by a unique clustered index if a clustered index is not yet present.
Clustered indexes is a special type of index which store row data at the leaf level, meaning it is always covering. All columns whether they are part of the key or not, are stored at the leaf level.
It does not have to be unique, in which case a uniquifier (4 bytes) is added to the clustered key.

Now we end up with 2 questions:

How do I want to uniquely identify rows in my table (PK)
How do I want to store it at the leaf level of an index (Clustered Index)

It depends on how:

you design your data model
you query your data and you write your queries
you insert or update your data
...

First, do you need a clustered index? If you bulk insert, it is more efficient to store unordered data to a HEAP (versus ordered data in a cluster). It uses RID (Row Identifier, 8 bytes) to uniquely identify rows and store it on pages.

The clustered index should not be a random value.
The data at the leaf level will be stored and ordered by the index key. Therefore it should grow continuously in order to avoid fragmentation or page split. If this can not be achieved by the PK, you should consider another key as a clustered candidate.
Clustered index on identy columns, sequential GUID or even something like the insertion's date is fine from a sequential point of view since all rows will be added to the last leaf page.
On the other hand, while unique identifier may be useful to your business needs as a PK, they should not be clustered (they are randomly ordered/generated).

If after some data and query analysis, you find out that you mostly use the same index to get your data before doing a key lookup in the clustered PK, you may consider it as clustered index although it may not uniquely identify your data.

The clustered index key is composed of all the columns you want to index. A uniquefier column (4 bytes) is added if there is no unique constraint on it (incremental value for duplicates, null otherwise).
This index key will then be stored once for each row at the leaf level of all your nonclustered indexes. Some of them will also be stored several times at intermediate levels (branch) between the root and the leaf level of the index tree (B-tree).
If the key is too large,  all the non clustered index will get larger, will require more storage and more IO, CPU, memory, ...
If you have a PK on name+birthdate+country, it is very likely that this key is not a good candidate. It is too large for a clustered index. Uniqueidentifier using NEWSEQUENTIALID() is usually not considered as a narrow key (16 bytes) although it is sequential.

Then once you figured out how to uniquely identify rows in your table, you can add a PK. If you think you won't use it in your query, don't create it clustered. You can still create another nonclustered index if you sometime need to query it. Note that the PK will automaticaly create a unique index.

The non clustered indexes will always contain the clustered key. However, if the indexed columns (+key columns) are covering, there won't be any key lookup in the clustered index.
Don't forget you can also add Include and Where to a non clustered index. (use it wisely)

Clustered index should be unique and as narrow as possible
Clustered index should not change over time and should inserted incrementally.

It is now time to write some SQL which will create the table, clustered and nonclustered indexes and constraints.

This is all theoritical because we don't know your data model and datatypes used (A and B).


About single threaded versus multithreaded databases performance H2 is a single threaded database with a good reputation regarding performance. Other databases are multi-threaded.

My question is: when does a multi-thread database become more interesting than an single thread database? How many users? How many processes? What is the trigger? Anyone has experience to share?

Summary

The usual bottleneck is disk access
SSD's are fast, but fragile (failure procedure is a must)
One long query on a single thread system will block all others
Configuring multi-threading system can be tricky
Multithreaded databases are beneficial even on single core systems

If there is one thing I can say about MySQL is that InnoDB, its transactional (ACID-compliant) storage engine, is indeed multithreaded. However, it is as multithreaded as YOU CONFIGURE IT !!! Even right "out of the box," InnoDB performs great in a single CPU environment given its default settings. To take advantage of InnoDB multithreading capabilities, you must remember to activate a lot of options.

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

innodb_read_ahead_threshold allows linear readings of the set number of extents (64 pages [page = 16K]) before switching to asynchronous reading.

Time would escape me if I named more options. You can read about them in MySQL's Documentation.

Most people are unaware of these features and a quite satisfied with InnoDB just doing ACID-compliant transactions. If you tweak any of these options, you do so at your own peril.

I have played with MySQL 5.5 Multiple Buffer Pool Instances (162GB in 9 buffer pools instances) and have attempted to have data auto-partitioned in memory this way. Some experts say that this should give you 50% performance improvement. What I got was a ton of thread locking that actually made InnoDB crawl. I switched to 1 buffer (162GB) and all was well again in the world. I guess you need Percona experts at your disposal to set this. I'll be at the Percona MySQL Conference in New York tomorrow and will ask about this if opportunity affords itself.

In conclusion, InnoDB behaves well now in a multi CPU server given its default settings for multithreaded operations. Tweaking them takes great care, great patience, great documentation, and great coffee (or Red Bull, Jolt, etc.).

Good morning, good evening, and good night !!!

UPDATE 2011-05-27 20:11

Came back from Percona MySQL Conference in New York on Thursday. What a conference. Learned a great deal, but I got an answer I will look into concerning InnoDB. I was informed by Ronald Bradford that setting innodb_thread_concurrency to 0 will let InnoDB decide the best course of action internally with thread concurrency. I will experiment with this further in MySQL 5.5.

UPDATE 2011-06-01 11:20

As far as one long query goes, InnoDB is ACID-compliant and operates very well using MultiVersion Concurrency Control. Transactions should be able carry isolation levels (repeatable reads by default) that prevents blocking others from accessing data.

As for multi core systems, InnoDB has come a long way. In the past, InnoDB could not perform well in a multicore environment. I remember having to run multiple mysql instances on a single server to get the multiple cores to distribute the multiple mysqld processes across the CPUs. This is no longer necessary, thanks to Percona, and later MySQL (eh, Oracle, saying that still makes me gag), as they have developed InnoDB into a more mature storage engine that can access the cores with simplicity without much tuning. The current instance of InnoDB today can operate well in a single core server.


What are the OMem and 1Mem columns in the plan output? The plan output, for example

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR( '' ,NULL,'ALLSTATS  LAST'));

has the columns OMem and 1Mem. What is their meaning? I could not find any reference or documentation about them, just this single web page, claiming (or asking):

  OMem:- Estimated optimal mem (PGA) size, to execute the operation in memory.

  1Mem:- Estimated size to execute the operation in single pass to disk?

OMem - Estimated amount of memory needed to perform the operation in memory only. This is also called the optimal execution.

1Mem - Estimated amount of memory needed to perform the operation in one pass (write to and read from disk (temp) only once). This is called a one-pass execution.

A multi-pass execution is where the same data is written to and read from disk more than once.

Think about sorting, where the database has to sort large amount data in a small PGA/sort area.

Since you query plan statistics with ALLSTATS LAST, some extra columns:

Used-Mem - The amount of memory actually used for this operation.
Also there is a number in brackets in this column. If the number is 0, then it was an optimal execution, used only memory and no temporary space. If the number is 1, then it was a one-pass execution. If the number is greater than 1, it was a multi-pass execution, and that number is the number of passes.

Used-Tmp - The amount of temporary space used for this operation.

Here is a presentation about the execution passes starting at page 28: http://www.nocoug.org/download/2003-08/how_cbo_works.ppt


Error updating sql server geography type in trigger I have the following trigger in my database:

CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
    IF UPDATE(Latitude)
        UPDATE pt
        SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
        FROM dbo.Party AS pt INNER JOIN inserted AS i ON i.PartyId = pt.PartyId
        WHERE (i.Latitude IS NOT NULL AND i.Longitude IS NOT NULL);
        RETURN
    IF UPDATE(Longitude)
        UPDATE pt
        SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
        FROM dbo.Party AS pt INNER JOIN inserted AS i ON i.PartyId = pt.PartyId
        WHERE (i.Latitude IS NOT NULL AND i.Longitude IS NOT NULL);
        RETURN
GO

But every time I try to update a row in the Party table, I get an error, even if I don't change the Latitude or Longitude fields.

The error is:

  geography::Point' failed because parameter 1 is not allowed to be null.

Any ideas?  I'm pretty new to triggers, so I'm sorry if it's something simple and obvious.

Try this one. I have moved the conditional update into a single statement because the action you were taking was the same for both conditions. Also I have altered the way that you join to the INSERTED table so that it performs the filter pre-join:

CREATE TRIGGER dbo.triggerGeocodedAddressUpdate ON dbo.Party AFTER UPDATE AS
IF UPDATE(Latitude) OR UPDATE(Longitude)
    UPDATE pt
    SET pt.GeocodedAddress = geography::Point(i.Latitude, i.Longitude, 4326)
    FROM dbo.Party AS pt
    JOIN
    (
        SELECT PartyId, Latitude, Longitude
        FROM inserted
        WHERE (Latitude IS NOT NULL AND Longitude IS NOT NULL)
    ) AS i ON i.PartyId = pt.PartyId;
    RETURN
GO

I hope this helps you.


How to pass Oracle Wallet auth to sh script? I am running an Oracle DB update script that requires password. I have Oracle wallet installed. How to pass Oracle wallet to sh script so that I don't have to enter password when running the script?

I have my sqlnet.ora

sqlnet.expire_time=60
sqlnet.inbound_connect_timeout=300
sqlnet.allowed_logon_version_server=10
sqlnet.allowed_logon_version_client=10

WALLET_LOCATION =
(SOURCE =
   (METHOD = FILE)
      (METHOD_DATA =
          (DIRECTORY = /oracle/app/oracle/product/base19/19/network/admin/wallet)
   )
)

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = TRUE

Credentials are correctly installed:
    $ mkstore -wrl "/oracle/app/oracle/product/base19/19/network/admin/wallet" -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: SID USER PASSWORD

I am running a script update_sql.sh:

#!/bin/bash
sqlplus cobrball @release.sql

release.sql:

spool release.log;
exit;

Create an auto-login wallet.

Creating an Auto-Login Wallet

Then connect to the database using the below syntax:

sqlplus /@connect_string @release.sql

connect_strings must be the same connect_string that is stored in the wallet. Otherwise the credentials in the wallet will not be used.


How can i deploy ssrs report one server to another server When I create a sample ssrs report and I deploy it, I get the following error:

  The specified report server URL http://localhost/reports could not be
  found. verify the syntax of the URL and that report server exits.

The URL you are currently using is for the report manager.
Report RDLs are deployed to the report server URL, so change the path to http://localhost/reportserver.

Alternatively, you can find the correct URL for your server configuration in the Reporting Services Configuration Manager > Web Service URL (generally located in the Start menu > All Programs > Microsoft SQL Server {Version} > Configuration Tools).

For more information: TechNet: How To Publish Reports


Page compression not working? I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month.

I now would expect SQL Server to apply the page compression. However using sp_estimate_data_compression_savings it doesn't look like it. (I would have savings of around 40%-50%.)

Did I miss something in configuring page compression?

The problem with this is that I have now large files, 70-80 GB per partition. When I compress them, they are half empty. Regaining this disk space is impossible because if I try to shrink the data file that is behind the partition I end up with a fragmented clustered index.

How can I avoid this disk space waste party?

If you're using big fields like VARCHAR(MAX), you'll discover that off-page data isn't compressed with page compression. (Hey, go figure.)

To see how much data of your table is being stored off-page, use the free open source script sp_BlitzIndex (disclaimer: I'm a coauthor on that). You can run it at the table level like this:

EXEC sp_BlitzIndex @DatabaseName = 'mydb', @SchemaName = 'dbo', @TableName = 'whywontthiscompress'

Then, in the top result set where it lists the indexes, look at the amount of LOB data stored on the table. If it's a lot, that's probably why compression isn't working. (And you can post a screenshot of the top result set in your question if you'd like more help/clarifications on this.)


How to get sum of the rows of 3 tables I have 3 tables and I want to know with one query sum of row's number in 3 tables

I tried 2 different queries but it they give only the number of rows each table and I c'ant read with,

     return int.Parse(cmd.ExecuteScalar().ToString());

the sum of rows of 3 tables.

I must read by reader and get the sum

Here are 2 queries I used:

    SELECT  (
        SELECT COUNT(*)
        FROM   prodbiscuit WHERE status =2
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   prodganache WHERE status =2
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   prodmacaron
        ) AS count1
        FROM   dual

And the other query which give the same result:

       SELECT COUNT(DISTINCT nom) as rows FROM prodbiscuit WHERE status=2

       UNION SELECT COUNT(DISTINCT nom) AS rows FROM prodganache WHERE status=2

       UNION SELECT COUNT(DISTINCT nom) AS rows FROM prodmacaron

How to get real sum of rows of tree tables with one query?

Should I use absolutely reader ?

http://searchoracle.techtarget.com/answer/Sum-of-two-table-row-counts-as-one-answer

 select ( select count(DISTINCT nom) from prodbiscuit WHERE status =2 )
 + ( select count(DISTINCT nom) from prodganache WHERE status =2 )
 + ( select count(DISTINCT nom) from prodmacaron)
      as total_rows

Finally I've found it at given link


Selecting specific rows SQL I want a query to select 2 rows for every NumberId from the table  that occur without being interrupted by another NumberId, and same for another NumberId and so on

ID      Name         NumberId
---------------------------
1       Abc          1
2       bcd          1
3       cde          1
4       def          2
5       efg          2
6       fgh          2
7       ghij         2
8       defg         1
9       efgj         1
10      fghi         1
11      ghij         1

I want to select 2 rows from NumberId 1  and 2 rows from NumberId 2  and again 2 rows from NumberId 1

Try this.

--set up demo data
declare @T table (id int, name varchar(10), NumberId int)
insert into @T(id, name, NumberId) values
(1,'Abc',1),
(2,'bcd',1),
(3,'cde',1),
(4,'def',2),
(5,'efg',2),
(6,'fgh',2),
(7,'ghij',2),
(8,'defg',1),
(9,'efgj',1),
(10,'fghi',1),
(11,'ghij',1 )
;

I used a common table expression to create groupings of consecutive NumberId's

--Create a grouping for consecutive values called grp

;WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Numberid ORDER BY [id]
                ), [id]) AS grp
    FROM @T
    )
    select * from _cte

| id | name | NumberId | grp                     |
|----|------|----------|-------------------------|
| 1  | Abc  | 1        | 1900-01-01 00:00:00.000 |
| 2  | bcd  | 1        | 1900-01-01 00:00:00.000 |
| 3  | cde  | 1        | 1900-01-01 00:00:00.000 |
| 4  | def  | 2        | 1900-01-04 00:00:00.000 |
| 5  | efg  | 2        | 1900-01-04 00:00:00.000 |
| 6  | fgh  | 2        | 1900-01-04 00:00:00.000 |
| 7  | ghij | 2        | 1900-01-04 00:00:00.000 |
| 8  | defg | 1        | 1900-01-05 00:00:00.000 |
| 9  | efgj | 1        | 1900-01-05 00:00:00.000 |
| 10 | fghi | 1        | 1900-01-05 00:00:00.000 |
| 11 | ghij | 1        | 1900-01-05 00:00:00.000 |

Then, I used another common table expression to add a row_number to each row in the above result

;WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Numberid ORDER BY [id]
                ), [id]) AS grp
    FROM @T
    )
    select * from _cte order by id
    ,AddedRn    --add a row number for each entry in the group
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY grp ORDER BY id
            ) AS rn
    FROM _cte
    )

| id | name | NumberId | grp                     | rn |
|----|------|----------|-------------------------|----|
| 1  | Abc  | 1        | 1900-01-01 00:00:00.000 | 1  |
| 2  | bcd  | 1        | 1900-01-01 00:00:00.000 | 2  |
| 3  | cde  | 1        | 1900-01-01 00:00:00.000 | 3  |
| 4  | def  | 2        | 1900-01-04 00:00:00.000 | 1  |
| 5  | efg  | 2        | 1900-01-04 00:00:00.000 | 2  |
| 6  | fgh  | 2        | 1900-01-04 00:00:00.000 | 3  |
| 7  | ghij | 2        | 1900-01-04 00:00:00.000 | 4  |
| 8  | defg | 1        | 1900-01-05 00:00:00.000 | 1  |
| 9  | efgj | 1        | 1900-01-05 00:00:00.000 | 2  |
| 10 | fghi | 1        | 1900-01-05 00:00:00.000 | 3  |
| 11 | ghij | 1        | 1900-01-05 00:00:00.000 | 4  |

Now put it all together and only select rows where rn in (1,2)

--set up demo data
declare @T table (id int, name varchar(10), NumberId int)
insert into @T(id, name, NumberId) values
(1,'Abc',1),
(2,'bcd',1),
(3,'cde',1),
(4,'def',2),
(5,'efg',2),
(6,'fgh',2),
(7,'ghij',2),
(8,'defg',1),
(9,'efgj',1),
(10,'fghi',1),
(11,'ghij',1 )
;

--Create a grouping for consecutive values called grp
WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Numberid ORDER BY [id]
                ), [id]) AS grp
    FROM @T
    )
    ,AddedRn    --add a row number for each entry in the group
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY grp ORDER BY id
            ) AS rn
    FROM _cte
    )
--Select only the rows with rn in (1,2)
SELECT *
FROM AddedRn
WHERE rn IN (1,2)
ORDER BY id

| id | name | NumberId | grp                     | rn |
|----|------|----------|-------------------------|----|
| 1  | Abc  | 1        | 1900-01-01 00:00:00.000 | 1  |
| 2  | bcd  | 1        | 1900-01-01 00:00:00.000 | 2  |
| 4  | def  | 2        | 1900-01-04 00:00:00.000 | 1  |
| 5  | efg  | 2        | 1900-01-04 00:00:00.000 | 2  |
| 8  | defg | 1        | 1900-01-05 00:00:00.000 | 1  |
| 9  | efgj | 1        | 1900-01-05 00:00:00.000 | 2  |


Most performant SQL query needed I'm storing documents with fields that are set in a DataSource table. The document is written to the database as follows:

Document table (ID, DataSourceID)
Document fields (DocumentID, DataSourceField, FieldValue)

For example:

Document
100 | "Invoice"
101 | "Receipt"

Fields
100 | "Date" | 2011/12/01
100 | "Amount" | 1200,00
101 | "Date" | 2011/12/02
101 | "Warehouse" | "Central"

The documents have many more fields. Now, the question is how to write the fastest query to find documents by entering some filter values on fields (ie. Date between '2011/12/1' and '2011/12/31' and Amount > 1000,00)?

This is what I came up:

select * from Document, Fields
where DocumentID = FieldDocumentID and DataSource = "Invoice"
and DocumentID in (select FieldDocumentID from Fields where Field = "Date"
and FieldValue between '2011/12/01' and '2011/12/31')
and DocumentID in (select FieldDocumentID from Fields where Field = "Amount"
and FieldValue >= 1000)

I'm not sure how well this is going to work with millions of rows. How could I do this better?

EAV based systems (e.g. Agresso) have query performance problems inherent to the EAV structure.  The problems are really based on three underlying issues:

The structure is fiddly to query, so you get added complexity in your queries.
You can't index the base table on any of the attributes, which drives some inherent inefficiency in the query plans.
You have to do multiple joins against a large table.

Some possible strategies for mitigating EAV performance problems are:

Using a clustered index to keep all records relating to a given parent in close physical proximity can help somewhat.  This minimises the I/O.
Consider using the metadata to create a view that flattens out the EAV structure.  You may have to redefine the view every time it changes, and it won't necessarily help performance much, but it will be much easier to query.  Try to create the view as a roll-up rather than a multiple way join.
Any attribute that is not strictly user defined (eg. transaction dates, amounts) should go on the base table.  These can also be indexed.
Resolve the most selective predicates in subqueries first and join against the sub-query.
If you have the option flatten out the physical structure, even if it has to be into a series of fields like 'StringField1', 'StringField2', 'DateField1', 'MoneyField1'.  Your application will have to hold the configration data anyway, so you can just map to a field on the database.This sounds clumsy, but it's much more efficient to query, and it doesn't have to be in the users' faces.  You can also use the metadata to generate a view over the table that names the fields, so other customers of the data can see a meaningful view of the data and query it without necessarily having access to the application metadata. If you run out of columns in the table, just add more in the database.  The field mapping system can always query the system data dictionary to see what's available.

At some point in these debates, somebody often chips in with a comment about XML columns ("I know, I'll just use XML").  To paraphrase JWZ, now you have two problems.  Around 1998 they installed another circle in hell just for people who defile their systems with XML blob fields.

Although it doesn't look slick and high-tech, just having a set of user attribute columns on the table is by far the best solution.  It is the most efficient, and there are many ways to make this approach more user friendly at the database level.

EDIT: Here is a snippet of T-SQL showing how to create a view that flattens out an EAV structure.  Note that you wound have to write a generator for the view based on the application's attribute metadata and re-create it every time this is changed.

if object_id ('dbo.Foo') is not null
    drop table dbo.Foo
go

if object_id ('dbo.FooView') is not null
    drop view dbo.FooView
go

create table dbo.Foo (
       FooID     int not null
      ,FooKey    varchar (20)
      ,FooVal    varchar (100)
)

insert Foo (FooID, FooKey, FooVal)
values (1, 'Date', '2011-11-11')

insert Foo (FooID, FooKey, FooVal)
values (1, 'Amount', '100')

insert Foo (FooID, FooKey, FooVal)
values (2, 'Date', '2012-12-12')

insert Foo (FooID, FooKey, FooVal)
values (2, 'Amount', '200')
go

create view dbo.FooView
    as
select *
  from (select FooID
              ,FooKey
              ,FooVal
          from dbo.Foo) keys
 pivot (Max (FooVal)
        for FooKey in ([Date],[Amount])) pvt
go

select * from dbo.FooView

This is much more efficient than solutions involving multiple self-joins on the table, and will be somewhat efficient with a clustered index on the base entity and attribute type keys.  You will probably also want to put type conversions into the view so you can sort the results correctly.


Implementing a family database using Access 2010 A while ago I asked this question about using Access 2007 to implement a family tree model. I had all the tables and relationships mapped out in a UML diagram but realised it was getting out of hand (for a "fun" project), so I carried on with what I was doing (storing all my data in Excel).

I've realised now that I still want to find a way to do this in Access 2010, simply to be able to query the data in a number of ways using SQL. The forms will also be a much better way for me to capture the data and represent it visually. Before I start drawing up the UML diagram again (want to start it with a fresh approach), I would like to know if anyone has any advice about the feasibility of doing such a project in Access?

I have been using Family Tree Maker (genealogical software) to store the family data as well, but I do not like some of its features, and there are features I feel it is lacking. I'm also not too concerned about actually having a physical family tree drawn and populated in front of me, but merely a way to store all the data in perfectly normalised tables and relating them correctly so I can query the data in a variety of ways.

edit I should clarify that it is not strictly a family database, but also a "membership" type database as well, where people are divided into various groupings not necessarily dependent on family relations. The TRIBE -> CLAN -> FAMILY -> SIM relations are quite simple, but adding in the CLASS cuts across family lines.

You can certainly create a database such as you describe using Access, if that is what you want to do. I don't see any reason why it would not be feasible. If you want to include other relationships (tribe/clan/family), make those part of your logical data model at the start.

"Perfectly normalized" is a relative term. Normalize according to what you are trying to accomplish. 5th normal form probably isn't going to help in a reporting database where denormalized happens to be what is needed. What you need is a data model that matches what you plan to do with it. Define the entities and relationships in terms of the problems you need to solve. Normalize where needed, and denormalize where needed.

MS Access is OK, I guess, as a front-end GUI builder, if your expectations aren't too great. I've never worked with an Access application that was really well done. A few of them have looked impressive, but they were hard to use. I'm not saying that it is not possible to develop a good one, but I suspect that a lot of people give up before they arrive there.

Whenever I have tried to develop using Access I have run into an endless series of restrictions on what I could do. The SQL dialect itself has many limitations, and the VB programming environment is full of pitfalls. I haven't worked with it now for some years, and I don't know if it has moved on to VB.Net since then, but working with "On Error" in the versions I used was awful. If you are going to do everything in Access then you are probably going to be stuck doing GEDCOM imports and refreshes in VB.

On the other hand there are many things you can do with the forms features, if you know them well. If you don't, you may waste a lot of time trying to do things and failing because you didn't know the particular trick for that task. I say this as somebody that doesn't know all the tricks.

I would like to be able to import my data into a SQL database as well, in order to do queries against it that typical genealogy software does not do. I came across your question while looking to see what might already be out there.

I would be inclined to use SQL Server, because SQL Server development is what I do for a living. If you don't know it well, it can present a steep learning curve. But importing, refreshing, and exporting GEDCOM data is straightforward, if not especially easy, using Integration Services, standard reports can be created using Reporting Services (or even Report Builder), and I might even see possible applications for attribute management using Master Data Services. This is all on top of a powerful database engine, and all of these components are available in the Developer Edition for about the cost of a genealogy software package, if you don't count the time to develop the application, which could be quite a lot.

Personally, I don't have time to develop something like this right now, and I will keep on looking. Good luck.


Function access query/queries in Postgres I'm working on a project in which I need to determine which functions a given account has access to run.

So essentially, if I give this query an ID, can I get a list of functions that ID has access to execute.

Is this possible?

Thanks in advance.

Sounds to me as if you are looking for:

select *
from information_schema.routine_privileges
where grantee = 'arthur';

https://www.postgresql.org/docs/current/static/infoschema-routine-privileges.html


Issue with FMTONLY in SQL Server 2012 I support an application that uses CodeSmith and NetTiers templates to generate C# code. CodeSmith inspects the database and uses the SET FMTONLY ON setting to determine the columns for which code should be generated.

Unfortunately in moving from SQL Server 2005 to 2012 there is a particular circumstance where this is now no longer working. There is a Stored Procedure that executes a Stored Procedure on a linked server and the code being generated for that Stored Procedure is incorrect.

I've managed to isolate the issue to the EXEC statement with SET FMTONLY ON. The following (example only) SELECT works on both the 2005 and 2012 instances:

SET FMTONLY ON
SELECT TOP(10) [MCMCU]
              ,[MCSTYL]
              ,[MCDC]
FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]
SET FMTONLY OFF

This returns the column headings only, as expected. The following works only on the 2005 instance:

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE_CRP].[CRPDTA].[F0006]') AT [JDE]
SET FMTONLY OFF

When run on the 2012 instance, SSMS shows the message "Command(s) completed successfully" but doesn't display the column headings.

Is there anything I'm missing here? Perhaps a setting that I need to change? The linked server definitions are identical, including the identity used to connect. And yes I'm aware that FMTONLY is deprecated but I don't have any ability to change the way CodeSmith interrogates the database.

I can reproduce that same behavior on SQL Server 2012. However, I am able to get it to work by putting the Linked Server name back into the query. Is there a reason you moved it out into the AT clause?

Try the following:

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]');
SET FMTONLY OFF

Well, while the above does work on SQL Server 2012, it was not a good test because it does not work when executing a stored procedure. It might have something to do with it being a different type of call (i.e. the stored procedure call is RPC, and that had to be configured separately).

I even just tried adding WITH RESULT SETS ((...)) and that still didn't work.

Ok, so I found something. It is generally known that FMTONLY ON doesn't actually run the code, it just scans it for SELECT statements. But, that also means that it doesn't know how to handle conditional code. So it returns whatever result sets could be returned, even if no code path would ever return some of them.

Example 1 has two SELECT statements, but only one can ever be returned at a time. Still, when running with FMTONLY ON, both are returned:

CREATE PROCEDURE dbo.FmtOnlyTest1
AS
SET NOCOUNT ON;
  DECLARE @A INT = 5;

  IF (@A <> 6)
  BEGIN
    SELECT TOP 5 * FROM [master].[sys].[objects];
  END;
  ELSE
  BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
    EXEC(@SQL);
END;
GO

Results:

-- Running normally returns one result set of 5 rows from sys.objects:
EXEC Test.dbo.FmtOnlyTest1;

-- With FMTONLY ON, it returns two result sets: one from sys.objects AND one with [IntVal]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest1;
SET FMTONLY OFF

Example 2 shows another consequence of not being able to run the code. If the code produces the result set dynamically, it cannot be determined by FMTONLY ON. This probably explains why it can't follow the EXEC call when the code is remote. I suspect that calling local Stored Procedures works by just doing a lookup in the local system catalog table for that definition.

CREATE PROCEDURE dbo.FmtOnlyTest2
AS
SET NOCOUNT ON;
  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest2;

-- With FMTONLY ON, there are no result sets returned (just like with the remote procedure)
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest2;
SET FMTONLY OFF

Knowing what we now know, we can use the fact that FMTONLY ON will find all result sets while not actually running the code to, even if not ideal, fake a result set to get CodeSmith (and other tools that still work in this fashion) working again.

Example 3 shows that we can effectively hide a dummy result set in a code block that cannot logically execute. Only FMTONLY ON will be able to see this so it won't present a problem for any other code (well, I did try sys.dm_exec_describe_first_result_set and that didn't really like it, but I don't think that is an issue in this case).

CREATE PROCEDURE dbo.FmtOnlyTest3
AS
SET NOCOUNT ON;

  IF (1 = 0)
  BEGIN
    SELECT CONVERT(INT, NULL) AS [FieldName1],
           CONVERT(DATETIME, NULL) AS [FieldName2];
  END;

  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest3;

-- Running with FMTONLY ON returns one result set of [FieldName1], [FieldName2]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest3;
SET FMTONLY OFF

For this test I had the code return a different result set for the IF (1 = 0) block to make the difference in behavior more apparent. But in practice, you would want to return the exact same result set structure in the SELECT as the remote stored procedure returns.

The obvious down-side here is that if you change the result set of that remote procedure you will have to remember to also change the definition in the IF (1 = 0) block, but at least CodeSmith will function.


How can I connect to SQL server by just using local ip address Currently I am connecting to SQL server using 192.168.1.157\MyInstance. I would like to connect to SQL server by just using 192.168.1.157 i.e if I type IP addressin SSMS it should connect properly using 1433 port number. How can I do so?

Create an SQL Client Alias as Goody said. Maybe better to put a name isntead of the IP address on the alias name.

Here below an screenshot:


SQL Server 2000 - 'Performance: Deadlock' We had to restart our SQL Server today, we had made no changes to it.

When it came back up we immediately started getting this error from the server

  DATE/TIME:    2/27/2014 3:09:31 PM

  DESCRIPTION:  The SQL Server performance counter 'Number of
  Deadlocks/sec' (instance 'Database') of object 'SQLServer:Locks' is
  now above the threshold of 1.00 (the current value is 2.00).

  COMMENT:  (None)

  JOB RUN:  (None)

We ran the DBCC TRACEON (1204) command and have watched the log's but it's not reporting any deadlocks.

Any idea what could trigger this to just go off? We are getting the alert every minute yet can't find any actual deadlocks.

Edit: I should add that before this reboot we had never received this error

Edit 2: We used SQL Server Profiler as well to look for deadlocks, let it run for 5 minutes over which we received 5 error alerts and when we checked the details we had NO deadlocks found.

Edit 3: March 06/2014: Ran the query and it worked, but it reports what our other details have said that we have no locks we where still getting the error above the whole time.

Thanks again for all your help!

Edit 4: March 06/2014: I ran the query and here is a sampling of the result set, I will admit I am not exactly sure what I am looking at here, that is to say I am not sure if it shows me something that I can act on or not.

Edit 5: March 07/2014: Image below shows the Alert that generates this error all of a sudden.

Thanks

Update March 5th 2014

-- Removed the CTE and any options that could be incompatible with SQL SERVER 2000

if OBJECT_ID(N'tempdb..#process', N'U') is not null
    drop table #process
GO

if OBJECT_ID(N'tempdb..#tmp', N'U') is not null
    drop table #tmp
GO

select spid as victim, blocked as blocker
into #process
from master..sysprocesses p1
where spid > 50
and blocked > 0
and spid <> blocked

insert into #process
select spid as victim, blocked as blocker
from master..sysprocesses p
inner join #process
on #process.blocker = p.spid
where spid > 50

select  distinct 'dbcc inputbuffer(' + CAST( victim as nvarchar(4)) + ')' as cmd, 'sp_who2 ' + CAST( victim as nvarchar(4)) as spwho, *
into #tmp
from #process
where blocker = 0
order by victim

select *
from #tmp
order by victim

DECLARE @cmd nvarchar(400)
DECLARE @spwho nvarchar(12)

DECLARE curseur CURSOR
FOR
    select  cmd, spwho
    from #tmp
    order by victim

OPEN curseur
FETCH NEXT FROM curseur INTO @cmd, @spwho

WHILE @@FETCH_STATUS = 0
BEGIN
    print (@cmd)
    exec (@cmd)
    print (@spwho)
    exec (@spwho)
    FETCH NEXT FROM curseur INTO @cmd, @spwho
END

CLOSE curseur
DEALLOCATE curseur

GO

select
    db_name(dbid),
    'dbcc inputbuffer(' + CAST(a.spid as nvarchar(4)) + ')',
    a.spid,
    a.blocked,
    a.status,
    a.cmd,
    a.cpu,
    a.waittime,
    a.loginame,
    Base = DB_NAME(a.dbid),
    *
from master.dbo.sysprocesses a
where 1 = 1
    and a.spid > 50
    and spid <> @@spid
order by
    a.blocked desc,
    a.waittime desc,
    a.cpu desc

Update 3/6/2013

So we've seen that no sessions are being blocked. Now we can also look at all current locks on your system.

Show locks server wide MSSQL 2000

Monitoring Lock Activity

select convert(varchar(30), suser_sname(p.sid)) as login,
       convert (smallint, req_spid) As spid,
       convert(varchar(30), db_name(rsc_dbid)) As db_name,
       case rsc_dbid when db_id()
            then convert(varchar(30), object_name(rsc_objid))
            else convert(varchar(30), rsc_objid) end As Object,
       rsc_indid As indid,
       substring (lock_type.name, 1, 4) As Type,
       substring (lock_mode.name, 1, 12) As Mode,
       substring (lock_status.name, 1, 5) As Status,
       substring (rsc_text, 1, 16) as Resource
   from master..syslockinfo s
   join master..spt_values lock_type on s.rsc_type = lock_type.number
   join master..spt_values lock_status on s.req_status = lock_status.number
   join master..spt_values lock_mode on s.req_mode = lock_mode.number -1
   join master..sysprocesses p on s.req_spid = p.spid
   where lock_type.type = 'LR'
     and lock_status.type = 'LS'
     and lock_mode.type = 'L'
     and db_name(rsc_dbid) not in ('master', 'msdb', 'tempdb', 'model')
order by spid, lock_type.number
go

The output will you tell you
1. what type of lock is being held: database, table, page.
2. the mode of the lock: S shared, X exclusive, RangeS-S where a range of values is locked in an index

Update 3/7/2014

I have updated your screen shot with some basic explanations. What we are seeing, is just a bunch of shared database locks.

To be brief, the locks on your system are shared (in other words, they don't block other sessions from accessing the database) and those locks are meant to prevent the database from being dropped, detached or renamed while data is being read and/or written from and to that database and its contents.

Getting back to your original question; there are no deadlocks or blocked sessions on your server. I don't remember having seen this message before, but from what you are sending back, this is definitely not about deadlocks and more likely to be just an informational message.

Or use sp_lock

sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

Which will give you a more detailed view of all locks currently being held on your server.

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


Run SQL query with python in SQL Server raises error: SqlSatelliteCall error: Unsupported input data type in column I am trying to run Python in SQL Server and want to define a variable which contains the result of a simple select query.

exec sp_execute_external_script
@language = N'python', @script = N'

import pandas as pd
inventory = pd.DataFrame(sql_inventory)

',
@input_data_1 = N'SELECT top 10 * from inventory',
@input_data_1_name = N'sql_inventory'

The structure of the SQL server table (inventory) is as below:

ProductID numeric(18,3)
RegionID numeric(18,3)
ShopCode int
QTY float
Date varchar(6)
Amount float
ID bigint

But I get the below error:
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 1 An external script error occurred:

  Unsupported input data type in column 'ProductID'. Supported types:
  bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float,
  char, varchar, nchar, nvarchar, varbinary. SqlSatelliteCall error:
  Unsupported input data type in column 'ProductID'. Supported types:
  bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float,
  char, varchar, nchar, nvarchar, varbinary. STDOUT message(s) from
  external script: SqlSatelliteCall function failed. Please see the
  console output for more information. Traceback (most recent call
  last): File "C:\Program Files\Microsoft SQL
  Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py",
  line 406, in rx_sql_satellite_call rx_native_call("SqlSatelliteCall",
  params) File "C:\Program Files\Microsoft SQL
  Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py",
  line 291, in rx_native_call ret = px_call(functionname, params)
  RuntimeError: revoscalepy function failed.

Should I specify data types every time I want to read from a query?

Sometimes the error messge can be misleading. In this case it's not. You're trying to use unsupported data type:

  Unsupported input data type in column 'ProductID'. Supported types:
  bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float,
  char, varchar, nchar, nvarchar, varbinary.

These two columns are causing the error:

ProductID numeric(18,3)
RegionID numeric(18,3)

According to Microsoft docs, bigint is the SQLtype equivalent to numeric Python type.

Please, try to run your code like this:

exec sp_execute_external_script
@language = N'python', @script = N'

import pandas as pd
inventory = pd.DataFrame(sql_inventory)

',
@input_data_1 = N'SELECT TOP 10 ShopCode, QTY, Date, Amount, ID FROM inventory',
@input_data_1_name = N'sql_inventory'

If I'm right, it should run fine. Since both columns ProductID and RegionID are numeric type you must either CAST or CONVERT those columns to refer to them in the query, but beware of the possibility of precision loss when converting from numeric to float or real.

  Should I specify data types every time I want to read from a query?

No. Just use the supported data types and you should be fine.


What means Command column in sp_who2 and text column in sys.dm_exec_sql_text? I have a script that shows commands being fired in SQL Server 2008 database as follows:

--shows who is active and executed commands
declare @SPID int=null,
    @Status varchar(max) = null,
    @LOGIN varchar(max) = null,
    @HostName varchar(max) = null,
    @DBName varchar(max) = NULL,
    @Command varchar(max) = null

declare @sql varchar(max)=''

set @sql='use master;DECLARE @Table TABLE(SPID INT,Status VARCHAR(MAX),LOGIN VARCHAR(MAX),HostName VARCHAR(MAX),BlkBy VARCHAR(MAX),DBName VARCHAR(MAX),Command VARCHAR(MAX),CPUTime INT,DiskIO INT,LastBatch VARCHAR(MAX),ProgramName VARCHAR(MAX),SPID_1 INT,REQUESTID INT);INSERT INTO @Table EXEC sp_who2;

SELECT  w.SPID,w.Status,w.LOGIN,w.HostName,w.BlkBy,w.DBName,w.Command,r.estimated_completion_time/60000.0 as remaining_minutes, r.percent_complete,r.total_elapsed_time/60000.0 as minutes,r.reads,r.writes,t.text,w.CPUTime,w.DiskIO,w.LastBatch,w.ProgramName,w.SPID_1,w.REQUESTID    FROM @Table w
inner join sys.dm_exec_requests AS r on w.SPID=r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE 1=1'
    if @SPID is not null
        set @sql=@sql+' and SPID='+@SPID
    if @Status is not null
        set @sql=@sql+' and upper(Status) like ''%'+upper(@Status)+'%'''
    if @LOGIN is not null
        set @sql=@sql+' and upper(LOGIN) like ''%'+upper(@LOGIN)+'%'''
    if @HostName is not null
        set @sql=@sql+' and upper(HostName) like ''%'+upper(@HostName)+'%'''
    if @DBName is not null
        set @sql=@sql+' and upper(DBName) like ''%'+upper(@DBName)+'%'''
    if @Command is not null
        set @sql=@sql+' and upper(Command) like ''%'+upper(@Command)+'%'''

exec (@sql)

I have noticed that there are DDL commands for creating a procedure, but I am not sure if the procedure is really being created or if this is just an information to tell which procedure was called.

Does anyone know what UPDATE really means in Command column?
And the text column from sys.dm_exec_sql_text: is ist a called procedure or a procedure being altered/created?

The text value shows the procedure in which the statement that is being executed resides. The Command references the action of the executing statement within the procedure

To find the exact statement within the procedure that is being called, you'll need to use the statement_start_offset and statement_end_offset from dm_exec_requests.

Add the following to the list of columns selected:

,SUBSTRING(text, (statement_start_offset/2)+1,
                            ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text)
                                  ELSE statement_end_offset
                                END - statement_start_offset)/2)+1)


Random performance of stored procedure across databases I have a stored procedure that is installed on 30 databases across 4 servers.  We encountered an issue last night where 3 of these stored procs are now taking over 8 minutes (one 18 minutes!) to execute while all of the others are under 1 second.  2 of the SP's are on databases under the same server, the other is on another server.

I have looked at the following and seen no improvement or rational explanation why.

sp_recompile
update statistics
review indexes fragmentation compared with successful databases but recreated anyways.
change proc for param sniffing problem/solution
Reviewed execution plan for anything shocking (estimated rows was WAY off but came back to normal after #2 above
drop and recreated procs with no changes
watch temp db, as proc uses temp tables nothing jumped out

We have changed the proc to be more efficient and now its executing under 1 second, but I cannot let go as to what is causing the existing SP to be fast in  90% of the situations, but seriously slow in the others.  Especially across 2 servers.

Due to the speed and consistency for months, I am baffled as to what might have caused this.
I have looked at database settings using sys.database comparing one to another.
The only differences I see are
                           Slow setting / Normal Database setting

recovery_model         1 /          3
recovery_model_desc    FULL/        SIMPLE
log_reuse_wait         2    /       0
log_reuse_wait_desc    LOG_BACKUP/  NOTHING

Could this have to the with the above settings?

The proc (which I can provide if needed, along with execution plan) reads data from tables and inserts into a couple of temp tables and returns a dataset.  from existing tables it only performs selects.  The tables are no bigger then 250,000 rows.  Some databases where the proc is running quick are in excess of 800,000.   So the 3 in question actually have a small overall data set.

Any ideas of what to check ?
What else can i do to figure this out?

Have you checked the CPU / Memory stats across servers where one SP is significantly different from another? (I assume your I/O are of the same performance more or less)?

I would also check various wait statistics, using Paul Randal's query as good starting point

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/


Multiple columns from one table rely on one single table I'm working with PostgreSQL 9.2 and have the following relation between 2 tables:

The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost always included in the same waste water and drinking water perimeters. But a few ones can be included in a waste water perimeter different from the drinking water one.

Table town:

t_code  | t_waste_water | t_drinking_water
--------+---------------+------------------
 001    | 01            | 01
 002    | 02            | 02
 003    | 01            | 03
 004    | 02            | 02
 005    | 01            | 01

Table perimeter:

perim_code | perim_name
-----------+------------
 01        | North
 02        | West
 03        | East

For instance, town 003is included in the North perimeter for waste water and to the East perimeter for drinking water stuff. Perimeters having the same names for waste water or drinking water concerns, I have put them in a single central table, instead of duplicating informations into one waste water table and one drinking water table.

Now, I would like to get perimeters' names together with town code in a single table, as bellow:

town    | perim_waste_water | perim_drinking_water
--------+-------------------+------------------
 001    | North             | North
 002    | West              | West
 003    | North             | East

My problem is that I don't see how to display more than one perimeter name for I can specify one relation (JOIN) between the two tables. The following query is not usefull but is a start...

SELECT
  a.t_code,
  a.t_waste_water,
  a.t_drinking_water,
  b.perim_name AS waste_water_perim,
  b.perim_name AS drinking_water_perim

FROM schema.town a
  LEFT JOIN schema.perimeter b ON a.t_waste_water = b.perim_code AND a.t_drinking_water = b.perim_code;

Is there a solution inside the query or do I have to structure data differently in the database?

I had to use two JOINs here but I think that this will work. You can join on the same table multiple times

SELECT
  a.t_code,
  a.t_waste_water,
  a.t_drinking_water,
  b.perim_name AS waste_water_perim,
  c.perim_name AS drinking_water_perim

FROM schema.town a
  JOIN schema.perimeter b ON a.t_waste_water = b.perim_code
  JOIN schema.perimeter c ON a.t_drinking_water = c.perim_code;


MYSQL Slow SELECT with multiple joins and ORDER BY I have this query

SELECT
r0_.id AS id0,
r0_.deleted_at AS deleted_at1,
r0_.updated_at AS updated_at2,
r0_.created_at AS created_at3,
r0_.is_vehicle_offer AS is_vehicle_offer4,
r0_.vehicle_offer_price AS vehicle_offer_price5,
r0_.vehicle_offer_description AS vehicle_offer_description6,
r0_.nr_of_parts_found_mails_sent AS nr_of_parts_found_mails_sent7,
r0_.client_id AS client_id8,
r0_.parent_id AS parent_id9,
r0_.vehicle_id AS vehicle_id10
FROM
requests r0_
INNER JOIN vehicles v1_ ON r0_.vehicle_id = v1_.id
INNER JOIN sibren_brands s2_ ON v1_.sibren_brand_id = s2_.id
INNER JOIN member_sibben_brands_subscriptions m4_ ON s2_.id =m4_.sibren_brand_id
INNER JOIN members m3_ ON m3_.id = m4_.member_id
INNER JOIN member_vehicle_categories m6_ ON m3_.id = m6_.member_id
INNER JOIN vehicle_categories v5_ ON v5_.id = m6_.vehicle_category_id
INNER JOIN member_sibren_brands_filter m8_ ON s2_.id = m8_.sibren_brand_id
INNER JOIN members m7_ ON m7_.id = m8_.member_id
LEFT JOIN request_metadata r9_ ON r0_.id = r9_.request_id AND (r9_.member_id = '210')
WHERE m3_.id = '210' AND v5_.id = s2_.vehicle_category_id AND
m7_.id = '210' AND r0_.updated_at >= '2012-04-19 13:31:26' AND
(r9_.id IS NULL OR r9_.deleted_at IS NULL) AND
r0_.updated_at <= '2012-06-19 13:31:25'
GROUP BY r0_.id
ORDER BY r0_.updated_at DESC
LIMIT 25 OFFSET 0;

Wich takes about 1.5 secs to run.  If I remove the last part GROUP BY .etc.....
than it only takes 0.07 sec.

Here's the result of EXPLAIN

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  m3_ const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  m7_ const   PRIMARY PRIMARY 4   const   1   Using index
1   SIMPLE  m6_ ref PRIMARY,vehicle_category_id,member_id   PRIMARY 4   const   1   Using where; Using index
1   SIMPLE  v5_ eq_ref  PRIMARY PRIMARY 4   oz_nl.m6_.vehicle_category_id   1   Using index
1   SIMPLE  s2_ ref PRIMARY,vehicle_category_id vehicle_category_id 4   oz_nl.m6_.vehicle_category_id   37  Using index
1   SIMPLE  m4_ eq_ref  PRIMARY,sibren_brand_id,member_id   PRIMARY 8   const,oz_nl.s2_.id  1   Using where; Using index
1   SIMPLE  m8_ eq_ref  PRIMARY,sibren_brand_id,member_id   PRIMARY 8   const,oz_nl.s2_.id  1   Using where; Using index
1   SIMPLE  v1_ ref PRIMARY,sibren_brand_id sibren_brand_id 5   oz_nl.s2_.id    111 Using where; Using index
1   SIMPLE  r0_ ref vehicle_id,updated_at   vehicle_id  4   oz_nl.v1_.id    1   Using where
1   SIMPLE  r9_ eq_ref  request_id,member_id,request_id_2   request_id  8   oz_nl.r0_.id,const  1   Using where

As far as i can see indexes are set OK

The issue that jumps out at me is the first table in your EXPLAIN:

1   SIMPLE  m3_ const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort

As you've noticed, if you remove the GROUP BY...ORDER BY..., the query runs much faster. Doing so most likely removes the Using temporary;Using filesort (but I can't know for sure without the CREATE TABLE statements.

I would suspect this is creating the temporary table on disk. See this link on how MySQL uses temporary tables.

First, if you are selecting any BLOB or TEXT columns, this will automatically be an on-disk temporary table. From your list, it looks like r0_.vehicle_offer_description AS vehicle_offer_description6 could fit the bill.

If this is the case, try removing that column and see if there is any noticeable difference.


AAG trigger a query when failing over I'm running a Failover cluster on windows 2016 servers (2 nodes) where I set up an AG in SQL 14.0.2027.2
This works good and I'm happy. But when it failover I need to fix my logins so I need to run a query. It doesn't happen often but when it happens we are always too late-running this script. So is there a good way of running this script automatically right after failover and just send us one e-mail that the node's failover?

I can not see your script but I am assuming you get orphaned users on the database after the failover because the users are linked to SQL Server local logins with different SIDs on the different instances.
The best way would be to recreate the login with the same SID in both instances CREATE USER SID=<sid> so you do not have to fix orphaned users every time you failover.
If this solution doesn't suit your needs, perhaps you can set up an alert for event 1480 - AG Role Change, and run the script as the response action.


PostgreSQL: Administration as Non-Superuser I have a database on a shared PostgreSQL server. I possess the ownership of the database and the CREATEROLE privilege and would probably be able to get a few other privileges if I request them but I won't be able to get superuser.
The database should be used by different users that I will create. I was wondering if it would be possible for me to actually maintain the system. A few issues I am seeing:

Other users can and should be able to create their own tables. Would it be possible for me as the database owner to modify/delete them?
Can I create full backups? I.e., would I even be able to see all tables if the creating user doesn't grant me the permissions explicitly? Could I at least set up that privileges are granted to me per default for all users?

As said, I don't have superuser privileges but I might be able to request certain changes to the system and get additional privileges.

That can easily be done if you make your administrative user a member of the users you create:
CREATE ROLE newuser LOGIN;
GRANT newuser TO admin;

Then you inherit all rights from that user and can ALTER and DROP the objects it owns. You can even become that user:
SET ROLE newuser;

The only problem is that newuser can revoke that privilege.


Insert range of value based on SQL where clause count SQL Server 2012, say I have this:

select '12358', resource_code, 'N', '1' from sch_resource
where resource_desc like '%avid%media%comp%dongle'

instead of inserting '1', I want to instead insert 1 to X, where X is:

select count(resource_code) from sch_resource
where resource_desc like '%avid%media%comp%dongle'

Is there an easy way to do this without a loop?

Result, count equal to 7, would look like this:

Wouldn't it just simply be:

select '12358', resource_code, 'N',
  rn = ROW_NUMBER() OVER (ORDER BY resource_code)
from dbo.sch_resource
where resource_desc like '%avid%media%comp%dongle'
ORDER BY rn;

If in your real code you're selecting more than one (whatever column 1 is), and you want the row numbering to start over with each new value in (whatever column 1 is), then you can say:

select [whatever column 1 is], resource_code, 'N',
  rn = ROW_NUMBER() OVER
       (PARTITION BY [whatever column 1 is] ORDER BY resource_code)
from dbo.sch_resource
where resource_desc like '%avid%media%comp%dongle'
ORDER BY [whatever column 1 is], rn;


Oracle 11GR2 - When will datafile autoextend? Let's say I have a single datafile, DF1. After DF1 becomes 50% full, I add a new datafile DF2 of equal size. Both datafiles have autoextend set to ON. When file DF1 becomes full (and therefore, DF2 will be around 50% full), will it autoextend DF1 despite having free space in DF2? Or will it instead leave DF1 as is (100% full) and start allocating extents from DF2 only?

Here's a visualization

When I add new datafile:
DF1: xxxxxxx00000000
DF2: 000000000000000

After DF1 becomes full:
DF1: xxxxxxxxxxxxxxx
DF2: xxxxxxx00000000

Will it start to do this?

DF1: xxxxxxxxxxxxxxxxxxx
DF2: xxxxxxxxxxx0000

Or will it start to do this?

DF1: xxxxxxxxxxxxxxx
DF2: xxxxxxxxxxxxxxx

Extents will spread evenly:

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> create tablespace test datafile size 10M autoextend on next 10M maxsize 100M;

Tablespace created.

SQL> create table t1 (id number) tablespace test;

Table created.

SQL> alter table t1 allocate extent (size 50M);

Table altered.

SQL> select * from dba_tablespace_usage_metrics where tablespace_name = 'TEST';

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
TEST                                 6536           12800      51.0625

SQL> alter tablespace test add datafile size 10M autoextend on next 10M maxsize 100M;

Tablespace altered.

SQL> select file_id, sum(blocks) from dba_extents where tablespace_name = 'TEST' group by file_id;

   FILE_ID SUM(BLOCKS)
---------- -----------
         7        6408

SQL> alter table t1 allocate extent (size 30M);

Table altered.

SQL> select file_id, sum(blocks) from dba_extents where tablespace_name = 'TEST' group by file_id;

   FILE_ID SUM(BLOCKS)
---------- -----------
         8        1536
         7        8712


What would be the best way to store one valuer per hour per day per customer? We have a table that stores a value per each hour per each per day per customer. A simplification of the table would be like this (removed some extra fields and the indexes):

CREATE table #timevalues (
dateinsert date,
customerid int,
hour0 int,
hour1 int,
....
hour23 int
)

The problem is that this table is huge, and altough we have an index with the dateinsert and customerid that allows us for a fast seek of the record, retrieving the record also takes time. Our issue is more with the speed and being future proof than with the space.
What would be a recomendation to improve this?
We were thinking about replacing the column format to store a json instead of the fixed columns, but the result would be less optimal I guess.
We regularly need to get stuff like:

Daily totals customer XXX in last month
Average per hour per day

If the values don't change too often, I would use and Slowly Changing Dimension (Type 2) approach. In this case, you will store a new row only when your value changes. This is mostly common used on Data Warehouses to track historical data.

In a nutshell:

1) Change your table to something like this:

CREATE TABLE dbo.CustomerHistory (
CustomerHistoryKey int identity (1,1),  -- PK and surrogate key
CustomerId int NOT NULL,
Value int,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL
)

ALTER TABLE dbo.CustomerHistory ADD  CONSTRAINT PK_CustomerHistory  PRIMARY KEY CLUSTERED
(CustomerHistoryKey)

CREATE UNIQUE NONCLUSTERED INDEX [BK_CustomerHistory] ON dbo.CustomerHistory
(CustomerId, StartDate)

CREATE UNIQUE NONCLUSTERED INDEX [IX_CustomerHistory_currentRow] ON dbo.CustomerHistory
(CustomerId, EndDate)
WHERE EndDate = CAST('99990101' AS datetime)

Avoid using nulls on StartDate and EndDate. Instead, choose a lower and higher date value, such as 19000101 and 99990101

2) Continue running your routine every hour to check if Value was changed, but only add a new row for those customers with changes. To add a new row, do:

DECLARE @ChangeDate datetime = GETDATE()
Set the current row EndDate equals to @ChangeDate
Add a new row with the updated Value with StartDate equals to @ChangeDate and EndDate equals to '99990101'

How it works:

You will store much less data if your values doesn't change to often
If you need to create a relationship pointing to a specific point in time, use CustomerHistoryKey
If you need to check one value in time, you can search by CustomerId and time >= StartDate and time < EndDate

Check the following link for an quick overview: https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row


What are different ways to replace ISNULL() in a WHERE clause that uses only literal values? What this isn't about:

This is not a question about catch-all queries that accept user input or use variables.

This is strictly about queries where ISNULL() is used in the WHERE clause to replace NULL values with a canary value for comparison to a predicate, and different ways to rewrite those queries to be SARGable in SQL Server.

Why don't you have a seat over there?

Our example query is against a local copy of the Stack Overflow database on SQL Server 2016, and looks for users with a NULL age, or an age < 18.

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 17) < 18;

The query plan shows a Scan of a quite thoughtful nonclustered index.

The scan operator shows (thanks to additions to actual execution plan XML in more recent versions of SQL Server) that we read every stinkin' row.

Overall, we do 9157 reads and use about half a second of CPU time:

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 485 ms,  elapsed time = 483 ms.

The question:
What are ways to rewrite this query to make it more efficient, and perhaps even SARGable?

Feel free to offer other suggestions. I don't think my answer is necessarily the answer, and there are enough smart people out there to come up with alternatives that may be better.

If you want to play along on your own computer, head over here to download the SO database.

Thanks!

Answer section

There are various ways to rewrite this using different T-SQL constructs. We'll look at the pros and cons and do an overall comparison below.

First up: Using OR

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

Using OR gives us a more efficient Seek plan, which reads the exact number of rows we need, however it adds what the technical world calls a whole mess of malarkey to the query plan.

Also note that the Seek is executed twice here, which really should be more obvious from the graphical operator:

Table 'Users'. Scan count 2, logical reads 8233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 469 ms,  elapsed time = 473 ms.

Second up: Using derived tables with UNION ALL
Our query can also be rewritten like this

SELECT SUM(Records)
FROM
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

This yields the same type of plan, with far less malarkey, and a more apparent degree of honesty about how many times the index was seeked (sought?) into.

It does the same amount of reads (8233) as the OR query, but shaves about 100ms of CPU time off.

CPU time = 313 ms,  elapsed time = 315 ms.

However, you have to be really careful here, because if this plan attempts to go parallel, the two separate COUNT operations will be serialized, because they're each considered a global scalar aggregate. If we force a parallel plan using Trace Flag 8649, the problem becomes obvious.

SELECT SUM(Records)
FROM
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

This can be avoided by changing our query slightly.

SELECT SUM(Records)
FROM
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

Now both nodes performing a Seek are fully parallelized until we hit the concatenation operator.

For what it's worth, the fully parallel version has some good benefit. At the cost of about 100 more reads, and about 90ms of additional CPU time, the elapsed time shrinks to 93ms.

Table 'Users'. Scan count 12, logical reads 8317, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 500 ms,  elapsed time = 93 ms.

What about CROSS APPLY?
No answer is complete without the magic of CROSS APPLY!

Unfortunately, we run into more problems with COUNT.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age IS NULL
) x (Records);

This plan is horrible. This is the kind of plan you end up with when you show up last to St. Patrick's Day. Though nicely parallel, for some reason it's scanning the PK/CX. Ew. The plan has a cost of 2198 query bucks.

Table 'Users'. Scan count 7, logical reads 31676233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 29532 ms,  elapsed time = 5828 ms.

Which is a weird choice, because if we force it to use the nonclustered index, the cost drops rather significantly to 1798 query bucks.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age IS NULL
) x (Records);

Hey, seeks! Check you out over there. Also note that with the magic of CROSS APPLY, we don't need to do anything goofy to have a mostly fully parallel plan.

Table 'Users'. Scan count 5277838, logical reads 31685303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 27625 ms,  elapsed time = 4909 ms.

Cross apply does end up faring better without the COUNT stuff in there.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY
(
    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age IS NULL
) x (Records);

The plan looks good, but the reads and CPU aren't an improvement.

Table 'Users'. Scan count 20, logical reads 17564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4844 ms,  elapsed time = 863 ms.

Rewriting the cross apply to be a derived join results in the exact same everything. I'm not going to re-post the query plan and stats info -- they really didn't change.

SELECT COUNT(u.Id)
FROM dbo.Users AS u
JOIN
(
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x ON x.Id = u.Id;

Relational Algebra:
To be thorough, and to keep Joe Celko from haunting my dreams, we need to at least try some weird relational stuff. Here goes nothin'!

An attempt with INTERSECT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   INTERSECT
                   SELECT u.Age WHERE u.Age IS NOT NULL );

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 1090 ms.

And here's an attempt with EXCEPT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   EXCEPT
                   SELECT u.Age WHERE u.Age IS NULL);

Table 'Users'. Scan count 7, logical reads 9247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2126 ms,  elapsed time = 376 ms.

There may be other ways to write these, but I'll leave that up to people who perhaps use EXCEPT and INTERSECT more often than I do.

If you really just need a count
I use COUNT in my queries as a bit of shorthand (read: I'm too lazy to come up with more involved scenarios sometimes). If you just need a count, you can use a CASE expression to do just about the same thing.

SELECT SUM(CASE WHEN u.Age < 18 THEN 1
                WHEN u.Age IS NULL THEN 1
                ELSE 0 END)
FROM dbo.Users AS u

SELECT SUM(CASE WHEN u.Age < 18 OR u.Age IS NULL THEN 1
                ELSE 0 END)
FROM dbo.Users AS u

These both get the same plan and have the same CPU and read characteristics.

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 719 ms.

The winner?
In my tests, the forced parallel plan with SUM over a derived table performed the best. And yeah, many of these queries could have been assisted by adding a couple filtered indexes to account for both predicates, but I wanted to leave some experimentation to others.

SELECT SUM(Records)
FROM
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

Thanks!