Subquery magic in PostgreSQL I have a query:

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id in (
    select distinct product_tmpl_id
    from product_template
    where type='import');

And it takes 7 hours to complete. However, when I execute the subquery:

select distinct product_tmpl_id
from product_template
where type='import';

I get the error:

  column "product_tmpl_id" does not exist

There isn't a column product_tmpl_id in the table product_template.

It was my mistake. The first query should have been:

update product_product set (write_date,default_code) = (LOCALTIMESTAMP,'update')
where product_tmpl_id in
 (select distinct id from product_template where type='import');

And it onle takes several seconds to run.

So my questions are the following:

why didn`t the first query fail?
why took it so long to run?
what exactly did it do?

The result of select version(); is

PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu,
compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

why didn`t the first query fail?

Because this is valid SQL.

First of all consider that it is valid to run a SELECT against the table that doesn't reference any columns from that table.

SELECT DISTINCT 'foo'
FROM   product_template
WHERE  type = 'import';

The above will return a single row result of foo if any rows exist that match the WHERE clause.

Secondly consider that it is valid to reference a column from the outer table in a sub query (this is required for correlated sub queries to work).

In the event that the column name is not qualified with a table reference then it will be resolved in the inner scope if possible and outer scopes if not. In your case product_template has no such column so it was resolved as belonging to product_product.

As a best practice be explicit about the tables the columns belong to. Had the subquery been written as below it would have failed to compile and alerted you of the error.

IN (SELECT DISTINCT pt.product_tmpl_id
    FROM   product_template pt
    WHERE  pt.type = 'import');

  what exactly did it do?

This depends. If FROM product_template WHERE  type = 'import'; returns zero rows then you were lucky and this is equivalent to

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id = null; /*Never true*/

Probably you weren't lucky though and it did return at least one row. In this case you ran the equivalent of the following

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id = product_tmpl_id;

which is equivalent to

update product_product
set (write_date, default_code) = (LOCALTIMESTAMP, 'update')
where product_tmpl_id IS NOT NULL;

  why took it so long to run?

I imagine as firstly it updated all rows in the table.

I'm not sure what the execution plans for this would be like in Postgres too.

In the worst case it might have been selecting all rows matching the where from product_template, passing in the correlated parameter, then performing DISTINCT on the result for each row in the outer product_product table.


Migrating BLOB in Oracle into PostgreSQL I use the data type BLOB in an Oracle table to store images and document files.  Now for Postgres(9.4.5), I have two options, i.e., BYTEA or OID.

With consideration of passing the params (SAVING) from the Java side as follows:

DiskFileItemDeepy file  =  myFile;
InputStream is = null;
long fileSize = 0;
if (file != null && file.getFileSize() > 0){
  is = file.getInputStream();
  fileSize = file.getFileSize();
  call.setBinaryStream(1, (InputStream)is,  (long)fileSize);
}
...
call.execute();

//When retrieve the data use:

java.sql.Blob blob = (Blob) resultSet.getBlob(tableColumnName);

For the purpose mentioned above, which Postgres data type is a better candidate for replacement of the BLOB, is it BYTEA or OID?

From a conceptual point of view the equivalent to Oracle's BLOB datatype is bytea in Postgres.

bytea only allows you to read and write the complete data (you can't just retrieve 100KB from a 1GB value). Which means that this is kept in the memory on the JVM while reading it. This is the same as with Oracle's BLOB type. From a JDBC point of view those two data types behave nearly the same.

The OID (or better: "large objects") isn't a real "data type". It is merely a pointer to the data stored in the file system. The advantage of using large objects is that you can properly stream the content from the server to the client, you don't need to load the entire binary data into the client's memory.

However dealing with large objects is much more complicated than dealing with bytea columns. You also need to manually clean up deleted large objects if you delete the corresponding row from the base table (something you do not need to do with bytea). Reading and writing of large objects is also much more complicated then dealing with bytea values.

As you are currently using a BLOB column, you apparently do have the memory on the client side to process the data, in that case to make transition as smooth as possible, I highly recommend to use bytea in Postgres.


Count NULL values per row I would like to count the number of null's present per row in a table without enumerating column names. For example:

WITH t as (VALUES
  (NULL ,'hi',2,NULL,'null'),
  ('' ,'hi',2,3,'test'),
  (NULL ,'hi',2,3,'null')
)
SELECT countnulls(t)
FROM t;

Would result in:

numnulls
2
0
1

The closest I can get is with the following hack of row_to_json():

select
(CHAR_LENGTH(row_to_json(t)::text)
 - CHAR_LENGTH(REPLACE(row_to_json(t)::text, 'null', '')))/4 from t;

Which is... quite the hack (not in a good way). It works, sort of, but it counts the string 'null' as a NULL when it is present in the actual data or in the column names. So it is incorrect in the above case.

1. You know the column names ...
For Postgres 9.6 or later, use num_nulls()
WITH t(a, b, c, d, e) AS (
   VALUES
     (NULL ,'hi',2,NULL,'null')
   , ('' ,'hi',2,3,'test')
   , (NULL ,'hi',2,3,'null')
   )
SELECT num_nulls(a,b,c,d,e)
FROM   t;

Returns your desired result exactly, works for any mix of data types.
The manual:

num_nulls(VARIADIC "any") ...   returns the number of null arguments

For Postgres 9.5 or older, convert to text[], array_remove(arr, null) and use the remaining array length for an exact count:
SELECT 5 - cardinality(array_remove(ARRAY[a::text,b::text,c::text,d::text,e::text], null))
FROM   t;

Any type can be cast to text. The cast is redundant for text columns, of course.
array_remove() requires Postgres 9.3 or later.
cardinality() requires Postgres 9.4 or later. Substitute with array_length(arr, 1) in older versions.
2. You don't know column names, but Postgres does
When building on actual tables (or other registered objects like a view or a materialized view), we can retrieve column names from the system catalog pg_attribute to fully automate with dynamic SQL. Like:
CREATE OR REPLACE FUNCTION f_num_nulls(_tbl regclass)
  RETURNS SETOF int AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT num_nulls(%s) FROM %s'
    , (SELECT string_agg(quote_ident(attname), ', ')  -- column list
       FROM   pg_attribute
       WHERE  attrelid = _tbl
       AND    NOT attisdropped    -- no dropped (dead) columns
       AND    attnum > 0)         -- no system columns
    , _tbl
   );
END
$func$  LANGUAGE plpgsql;

Call:
SELECT * FROM f_num_nulls('myschema.tbl');

Returns the count for for each row in current physical order. Nothing else, to be absolutely generic.
Related:

Table name as a PostgreSQL function parameter
psql: SELECT * ... except one column

We could also pass each row to return a single count for it using a polymorphic function. Related:

Refactor a PL/pgSQL function to return the output of various SELECT queries

3. You don't know anything: anonymous records
In the unlikely event that column names are unknown even to Postgres (like from a VALUES expression in your example), convert to a document type (json, jsonb, xml, hstore) to get a handle, like demonstrated by ypercube (comment deleted by now) and Evan.
But anonymous records do not have primary keys or any other unique attribute per definition. Count within each LATERAL subquery to defend against false aggregates. Demo with jsonb:
SELECT *
FROM  (
   VALUES
      (NULL ,'hi',2,NULL,'null')
    , (NULL ,'hi',2,NULL,'null')       -- duplicate row !!!
    , ('' ,'hi',2,3,'test')
    , (NULL ,'hi',2,3,'null')
   ) t                                 -- column names unknown
, LATERAL (
   SELECT count(*) FILTER (WHERE j.value = jsonb 'null') AS num_nulls
   FROM   jsonb_each(to_jsonb(t)) j
   ) c;

Or with json: probably a bit faster because the conversion is cheaper.
Demonstrating 3 different ways:
SELECT *
FROM  (
   VALUES
      (NULL ,'hi',2,NULL,'null')
    , (NULL ,'hi',2,NULL,'null')
    , ('' ,'hi',2,3,'test')
    , (NULL ,'hi',2,3,'null')
   ) t   -- column names unknown
, to_json(t) j
, LATERAL (
   SELECT count(*) FILTER (WHERE j1.value::text = 'null') AS num_nulls1
   FROM   json_each(to_json(t)) j1
   ) c1
, LATERAL (
   SELECT count(*) FILTER (WHERE j->>k IS NULL) AS num_nulls2
   FROM   json_object_keys(j) k
   ) c2
, LATERAL (
   SELECT count(*) - count(j->>k ) AS num_nulls3
   FROM   json_object_keys(j) k
   ) c3;

db<>fiddle here


Order by for 2 row result causes 800% performance hit The following query, run against a table of about 6 mil rows takes 25 seconds. When I remove the final order by, it runs in 3 seconds. There are no indexes on the table (it's an intermediate SSIS ETL target that is later pulled into a DW.)

It isn't caching. I've run it multiple times in any order with consistent results.

The query itself checks for gaps in sequence numbers. SSIS will use it to see if it needs to re-fetch anything.

;with edges as
(
    select
        ROW_NUMBER() over (partition by s1.SiteIDNumber order by s1.SequenceNumber) as rn,
        s1.SiteIDNumber,
        s1.SequenceNumber
    from TestPlay s1
    left join TestPlay s2 on s2.SiteIDNumber = s1.SiteIDNumber and s2.SequenceNumber = s1.SequenceNumber + 1
    left join TestPlay s3 on s3.SiteIDNumber = s1.SiteIDNumber and s3.SequenceNumber = s1.SequenceNumber - 1
    where s2.SiteIDNumber is null
    or s3.SiteIDNumber is null
),
gaps as
(
    select
        e.rn,
        e.SiteIDNumber,
        e.SequenceNumber  + 1 as StartSeq,
        e2.SequenceNumber - 1 as EndSeq
    from edges e
    join edges e2 on e2.SiteIDNumber = e.SiteIDNumber and e2.rn = e.rn+1
    where e.rn = (e.rn / 2) * 2
)
select * from gaps order by rn

Here is the only part of the Execution Plan that seems to be different:

With ORDER BY (25 sec):

No ORDER BY (3 sec):

The merge join works like a zipper - if you don't care about order, SQL Server knows that it can sort the input in any way it wants, and not have to worry about re-ordering anything. When you add the order by, in this case a merge join is no longer the best choice, because materializing and sorting the first CTE twice in the order defined by the ROW_NUMBER() expression must obviously be more expensive than it seems.

One workaround, thinking off the cuff at beer o'clock on a Friday:

;WITH ...
(
)
SELECT * INTO #x FROM gaps;

SELECT * FROM #x ORDER BY rn;

However I bet if you searched around for a fellow named Itzik Ben-Gan you would find more efficient approaches to problems involving gaps and islands (or the advice to not try to sort the way you're sorting).


How can I improve the performance of a sorted join? This feels like such a common question, I'll understand if it is closed but if so please suggest a better place I could ask. I have the following two tables of interest:
CREATE TABLE [dbo].[Sessions]
(
    [Id] [int] PRIMARY KEY,
    [DateConnected] [datetime] NOT NULL,
    [Origin] [nvarchar](max) NULL,
    [TrackerId] [int] NULL,
    [Imei] [nvarchar](max) NULL,
    [Sim] [nvarchar](max) NULL,
    [ProtocolVersion] [tinyint] NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
    [Id] [int] PRIMARY KEY,
    [RequestId] [int] NULL,
    [SessionId] [int] NOT NULL,
    [DateProcessed] [datetime] NOT NULL,
    [Direction] [int] NOT NULL,
    [Sequence] [int] NOT NULL,
    [Acknowledgement] [int] NOT NULL,
    [DateRecorded] [datetime] NOT NULL,
    [Version] [tinyint] NOT NULL,
    [Command] [tinyint] NOT NULL,
    [Flags] [tinyint] NOT NULL,
    [Checksum] [tinyint] NOT NULL,
    [Data] [varbinary](max) NULL
)

CREATE NONCLUSTERED INDEX [IX_TrackerId_DateConnected] ON [dbo].[Sessions]
(
    [TrackerId] ASC,
    [DateConnected] ASC
)

CREATE NONCLUSTERED INDEX [IX_SessionId_DateProcessed] ON [dbo].[PacketTransmissions]
(
    [SessionId] ASC,
    [DateProcessed] ASC
)
INCLUDE ([Direction], [Sequence], [Acknowledgement], [Command])

My most common query, and most expensive (quite often times out now) involves listing all packet transmissions for a particular tracker.
DECLARE @TrackerId INT = 10
DECLARE @StartDate DATETIME2 = '2018-03-10'
DECLARE @EndDate   DATETIME2 = '2018-03-12'

SELECT [PacketTransmissions].*
FROM [Sessions]
JOIN [PacketTransmissions] ON [PacketTransmissions].[SessionId] = [Sessions].[Id]
WHERE [Sessions].[TrackerId] = @TrackerId
AND [PacketTransmissions].[DateProcessed] > @StartDate
AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC

This was good at first, but now there is a lot of data, it has slowed right down. My attempt to get the query plan today took 2 minutes, and shows that it will be using a table scan, rather than the index I created. Even when I force the index, it is still very slow.
In comparison, if I choose a session first, and search only for packet transmissions recorded within that session, the query uses the index and is incredibly fast.
My most successful attempt to speed up the query has been to order the results first by session id, then by date processed, to match the index order. While this is not technically always correct, it is acceptable. However, even this has started to time out, and I feel like there is something wrong with my understanding of how to make the JOIN faster.
What can I do to improve the performance of this query?
Querying with DATETIME variables instead of DATETIME2 has simplified the query plan, however it is still very slow.

Sessions has 265,929 rows

PacketTransmissions has 32,916,233 rows
That works out to be 123.7 packets per session, on average.

Some of the sessions are for unregistered devices, so they create a session, send between one and three packets, and then the session is rejected by the server.

I will normally be debugging a registered device, so the actual number of packets per session is considerably higher, between 300 and 5000 packets per session

Some trackers may maintain the same session for a month at a time if they have connectivity

I have in the past had a bad experience with changing the clustered index to use a non-sequential key. It results in a lot of out-of-order writes, and page splits, and the insert performance drops significantly.
The problem with the actual execution plans is that I don't want to run the database at max DTU for up to an hour, and potentially have inserts fail in the meantime.

Perhaps this is crazy, but I like to try a bit of blue-sky-thinking every once in a while, so I'd consider adding the TrackerId column to the dbo.PacketTransmissions table to avoid the join completely.  Obviously, this means you need to modify the row-insert procedure for the table, which may or may not be feasible.

However, this change, combined with a simple index:

CREATE INDEX IX_PacketTransmissions ON dbo.PacketTransmissions
(
    TrackerId ASC
    , DateProcessed ASC
)
INCLUDE (Id); --not strictly required, since the primary key
              --is always included in every non-clustered index
              --I include them just to be explicit

creates a query plan using a run-of-the-mill index seek, combined with a key lookup for each row returned.  As in:

To test this, I created a minimally complete verifiable example:

USE tempdb;

IF OBJECT_ID(N'dbo.Sessions', N'U') IS NOT NULL
DROP TABLE dbo.[Sessions];
IF OBJECT_ID(N'dbo.PacketTransmissions', N'U') IS NOT NULL
DROP TABLE dbo.PacketTransmissions;
GO

CREATE TABLE [dbo].[Sessions]
(
      [Id] int
        CONSTRAINT PK_Sessions
        PRIMARY KEY CLUSTERED
    , [DateConnected] datetime NOT NULL
    , [Origin] nvarchar(max) NULL
    , [TrackerId] int NULL
    , [Imei] nvarchar(max) NULL
    , [Sim] nvarchar(max) NULL
    , [ProtocolVersion] tinyint NULL
)

CREATE TABLE [dbo].[PacketTransmissions]
(
      [Id] int
        CONSTRAINT PK_PacketTransmissions
        PRIMARY KEY CLUSTERED
    , [RequestId] int NULL
    , [SessionId] int NOT NULL
    , [DateProcessed] datetime NOT NULL
    , [Direction] int NOT NULL
    , [Sequence] int NOT NULL
    , [Acknowledgement] int NOT NULL
    , [DateRecorded] datetime NOT NULL
    , [Version] tinyint NOT NULL
    , [Command] tinyint NOT NULL
    , [Flags] tinyint NOT NULL
    , [Checksum] tinyint NOT NULL
    , [Data] varbinary(max) NULL
    , [TrackerId] int NULL
)
GO

INSERT INTO dbo.[Sessions] (Id, DateConnected, Origin, TrackerId, Imei, Sim, ProtocolVersion)
SELECT ROW_NUMBER() OVER (ORDER BY sc1.id)
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(nvarchar(max), CRYPT_GEN_RANDOM(128))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(nvarchar(40), CRYPT_GEN_RANDOM(38))
    , CONVERT(tinyint, CRYPT_GEN_RANDOM(1))
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

INSERT INTO dbo.PacketTransmissions (Id, RequestId, SessionId, DateProcessed, Direction, Sequence, Acknowledgement, DateRecorded, Version, Command, Flags, Checksum, Data, TrackerId)
SELECT ROW_NUMBER() OVER (ORDER BY s.Id)
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(3))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(2))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2017-01-01 00:00:00')
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CONVERT(int, CRYPT_GEN_RANDOM(1))
    , CRYPT_GEN_RANDOM(128)
    , s.TrackerId
FROM dbo.[Sessions] s
    CROSS JOIN (SELECT v.n
    FROM (VALUES (0), (1))v(n)) v;
GO

On my system, this creates around 700,000 session rows, and double that number of transmission rows.

The query then becomes:

DECLARE @TrackerId int = 100;
DECLARE @StartDate datetime = '2017-03-10';
DECLARE @EndDate   datetime = '2017-03-12';

SELECT [PacketTransmissions].*
FROM [PacketTransmissions]
WHERE [PacketTransmissions].[TrackerId] = @TrackerId
    AND [PacketTransmissions].[DateProcessed] > @StartDate
    AND [PacketTransmissions].[DateProcessed] < @EndDate
ORDER BY [PacketTransmissions].[DateProcessed] DESC;


Validating an HTML input string A partial solution is to get all the Notes (VARCHAR) where there is an imbalance in < and >. But how can I write a query for that?

For example:

Input: Hello World
Output: Valid HTML

Input: <Hello World
Output: Invalid HTML

Input: <Hello World>
Output: Valid HTML (Marking it Valid is OK for my use case)
But hey isn't that a valid HTML? It could be <name attribute>

Are there any better ways to check for "invalid" HTML where "invalid" is defined as HTML content with < followed some characters that is not a valid HTML Element as per specification?

Initially I thought I could check for <name followed by a  (Space) but HTML Attributes have syntax as <name attribute and closed by > at the end.

If all you want to do is confirm that the input string consists of a sequence of <...> elements:

Split the string on the < character using your favourite string splitter
The first element must be blank
Subsequent elements must contain exactly one > character

The input string is valid if every element passes the tests above.

Example

String splitter

I am using the SQL#.String_Split4k streaming table-valued function from the free edition of the SQLCLR SQLsharp library by Solomon Rutzky.

If the input string might be longer than 4000 characters, the (less efficient) SQL#.String_Split function would be used instead (it handles strings up to 2GB).

Any efficient string splitter (T-SQL or otherwise) that can return empty elements correctly with a sequence number will work.

The example below shows the sort of output SQL#.String_Split4k returns.

The first parameter is the string to split. The second parameter is the delimiter. The third parameter (SplitOption) is set to 1, which means Keep Empty Elements.

SELECT
    SS.SplitNum,
    SS.SplitVal
FROM SQL#.String_Split4k(N'<c>', N'<', 1) AS SS;

Output:

╔══════════╦══════════╗
║ SplitNum ║ SplitVal ║
╠══════════╬══════════╣
║        1 ║          ║
║        2 ║ a>       ║
║        3 ║ b>       ║
║        4 ║ c>       ║
╚══════════╩══════════╝

Table and test data

CREATE TABLE #T
(
    row_id integer IDENTITY PRIMARY KEY,
    string varchar(50) NOT NULL
);

INSERT #T
    (string)
VALUES
    ('Hello World'),
    ('<Hello World'),
    ('<Hello World>');

Solution

SELECT
    T.row_id,
    string = (SELECT TOP (1) T2.string FROM #T AS T2 WHERE T2.row_id = T.row_id),
    is_valid =
        MIN
        (
            CASE
                -- First element must be blank
                WHEN SS.SplitNum = 1 AND SS.SplitVal = N'' THEN 1
                WHEN SS.SplitNum = 1 THEN 0
                -- Other elements must contain exactly one >
                WHEN SS.SplitVal NOT LIKE N'%>%' THEN 0
                WHEN SS.SplitVal LIKE N'%>%>%' THEN 0
                -- Otherwise valid
                ELSE 1
            END
        )
FROM #T AS T
CROSS APPLY SQL#.String_Split4k(T.string, '<', 1) AS SS
GROUP BY T.row_id
ORDER BY T.row_id;

Output

╔════════╦════════════════════════════╦══════════╗
║ row_id ║           string           ║ is_valid ║
╠════════╬════════════════════════════╬══════════╣
║      1 ║ Hello World   ║        1 ║
║      2 ║ <Hello World  ║        0 ║
║      3 ║ <Hello World> ║        1 ║
╚════════╩════════════════════════════╩══════════╝


Histogram Skew on Clustered Index Problem
I've been attempting to optimize a query generated from Entity Framework, and noticed that the execution plan shows wildly inaccurate estimations. After a little digging, I noticed that the statistics objects for some of the clustered indexes is very skewed. Below is a snip of the top operators for the query, ordered by actual rows:

(source: imgh.us)
EDIT: Redacted the database name.
The third operator from the top (the one I have selected) is a clustered index seek, and when I look at the statistics object for that particular index I see an absurd amount of skew:

(source: imgh.us)
Is there a reason for this? I'm confused as to why SQL Server is only generating 3 steps for this histogram with a full scan. With 225,000+ rows in the table, I would expect to see a more steps with smaller ranges, but what I'm actually seeing is nearly every row included in one step. Rebuilding indexes and updating stats does not redistribute this.
In addition to that particular index, it appears many other clustered index statistics have a similar problem, and provide similarly bad estimations. Another operator in the above list is a clustered index scan on the WorkOrder table, and it also produces a relatively bad estimation. Though it appears this is the cardinality estimator picking 30% of the rows, so perhaps it is an issue with parameter sniffing (or something other than statistics).
Basically, my questions are as follows:

What should clustered index statistics look like?
Is the above distribution expected?
If it is expected, how do I work around these bad estimates?
If it is not expected, how do I correct these bad statistics?

EDIT2: Here is the XML Plan: https://www.brentozar.com/pastetheplan/?id=rJTjT4zze

Your statistics are fine.

It is telling you that you have

exactly one row with value 1
1 row with value 213152
1 row with value 213151
213,144 distinct values that are > 1 and < 213,151 (so basically one row for each of the 213,149 possible integer values between that with 5 missing)


unique ID for multi tabel? (MySQL) I have a table 'article' and each of these articles belongs to one of three different topics (topic_a, b and c). I also have a table for each of these topics with various fields. How can I connect these the best way?

I was thinking that, since every topic has many articles, I would add a topic_ID field to each article, but the ID would not be unique unless I use UUID. Would it make sense to use UUID in this case or is there a better way?

Another idea was to add three topic ID fields to each article and just fill the appropriate one (topic_a_ID, topic_b_ID,..) and leave the other two empty. But this also seems not right.

Any suggestions?

Based on your story mine first idea what crosses mine mind was.

**article.**
 article_id
 article_name
 ...
 ...

**topic**
 topic_id
 article_id
 topic_name
 ...
 ...

**topic_has_many_articles** (many to many relation and your connection table)
  topic_id
  article_id

You need to use correct indexing for this to scale well with large datasets.


Does AT TIME ZONE automatically take care of DST conversion? I have to convert UTC dates for some reports.  We are using SQL Server 2016 and all my report queries are SPs, so I was able to do this:

SELECT MyDateTime AT TIME ZONE 'Pacific Standard Time' AS 'MyLocalDateTime'

This returns the expected value, but I have not been able to determine if this will still work during daylight savings time.

By querying the time zone info DMV:

 select * from sys.time_zone_info

I see there is a column for 'Is DST', so I am assuming the server is tracking that.  So can I safely assume sql server will adjust the time as needed during DST?

Yes, it accounts for it:

  AT TIME ZONE applies specific rules for converting input values in
  smalldatetime, datetime and datetime2 data types, that fall into an
  interval that is affected by the DST change

As per the MSDN article on the expression. The sys.time_zone_info column supplies information on if the zone currently is in DST, but the article above indicates that the rules take into account DST given a specific date time value.

Example:

/*------------------------
SELECT SYSUTCDATETIME() AT TIME ZONE 'Eastern Standard Time';
------------------------*/

----------------------------------
2017-01-18 22:18:12.3789253 -05:00

And when we try using a date with a different DST offset:

/*------------------------
SELECT CAST('2017-07-18' AS DATETIME) AT TIME ZONE 'Eastern Standard Time';
------------------------*/

----------------------------------
2017-07-18 00:00:00.000 -04:00

It assigns a different DST offset value, being aware of whether or not the date falls into DST.


storing "replaced" records We are storing assets in an asset table, using auto Increment to assign an ID to an asset.

I have been asked what happens when an asset is replaced eg, water heater leaked, replaced with a new one.

I suggested that in the interface a button to replace the item, would create a new row, with some of the details copying over (like location) and on the old record having a replacedBy column that just lists the new ID. then when querying for all in service assets, use WHERE replacedBy IS NULL

but i was then told, they would like to keep the old ID number. i thought for a second of coping the record in entirety and "inverting" the id, eg 55 would become -55, essentially archiving it, then making changes on the original record. but then what happens if the new one is then replaced.

i could just copy the record to the next auto increment value and having a field to say it is replaced by the original but then i have an old record with a higher id than a newer record, something inside me just does not sit well with that (probably some ocd thing)

is there a way to auto decrement from 0, eg replaced items would be copied to the next negative value, so everything under 0 would be old data and everything over 0 would be current stuff?

there are a lot of asset tables so i would like to avoid having a table for each asset just to store replaced assets.

You could add one more field in your table, something like parent_id where you could store the primary key from which the new record is derived. And primary key will continue to auto-increment. I hope this will fix your confusion.


Check if an index exists by comparing columns We are currently using ALL_IND_COLUMNS to check if an index exists by its name or the columns it is using. The problem is that we can only check if the index exists with columns in a specific order.

We need to check if a table has an index with any name, but with Column1, Column2 and Column3 in any order. How can we do this?

It is possible to check if an index using a number of columns exists.

  WITH
     index_cols AS (
        SELECT
           table_name,
           index_name,
           COLLECT(column_name) AS column_names
        FROM all_ind_columns
        WHERE table_name = '&table_name'
        GROUP BY
           table_name,
           index_name
     ),
     target_cols AS (
        SELECT '&column_name_1' AS col_name FROM dual
        UNION SELECT '&column_name_2' FROM dual
     )
  SELECT
     ic.*
  FROM index_cols ic
  WHERE
     (
        SELECT COUNT(*)
        FROM target_cols tc
        JOIN TABLE(ic.column_names) icc
           ON (tc.col_name = icc.COLUMN_VALUE)
     ) = (
        SELECT COUNT(*)
        FROM target_cols
     )
  ;

Is one (a bit convoluted) way to do that.

But as aasim.abdullah noted in a comment, two indexes on the same set of columns but in different order, aren't the same. And it is possible to have both indexes side by side.


Nested MySQL FROM query with two DBs I need to display user-specific tables for users logged into a CMS. These tables are stored in another database with tables for the different user groups. For example User 1 to 4 should get data from Table1, User 5-12 from Table2 etc. Unfortunately I can only run one query because of the CMS plugin creating charts only supports one. I can pass the UserID into that query though.

Tables:

UserTable matches User IDs to Group IDs (User1 = Group99).
GroupTable matches Group IDs to table names (Group 99 = Table3).
These User specific tables (Table3) contain Datetimes and Values.

This query returns the values I need from Table3 for example:

SELECT DATE(timestamp) As Date, AVG(Value) AS Value
FROM *DB.TableX*
WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE()
GROUP BY DATE(timestamp)

DB.TableX should now be replaced with the result from the following query, which basically returns "Table3":

SELECT grouptable.tablename FROM usertable
LEFT JOIN grouptable ON grouptable.ID = usertable.groupID
WHERE User = %USERID%

When I try to nest the second query into the FROM statement of the first, I get an error "Unknown column timestamp in field list":

SELECT DATE(timestamp) As Date, AVG(Value) AS Value
FROM
  (SELECT grouptable.tablename FROM cmsdb.usertable
  LEFT JOIN cmsdb.grouptable ON cmsdb.grouptable.ID = cmsdb.usertable.groupID
  WHERE User = 2) AS SUBQUERY
WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE()
GROUP BY DATE(timestamp)

I thought that was possible but I can't figure out what's wrong. I don't want to build a seperate page for each user :-/

as described it will not work, but You can use prepared statements for this type of operations:

SET @tb_name = (SELECT grouptable.tablename FROM cmsdb.usertable LEFT JOIN cmsdb.grouptable ON cmsdb.grouptable.ID = cmsdb.usertable.groupID WHERE User = 2);

just keep in mind subquery above, must return single value, if not sure - add LIMIT 1

set @query = CONCAT('SELECT DATE(timestamp) As Date, AVG(Value) AS Value FROM ',  @tb_name , ' WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE() GROUP BY DATE(timestamp)');

prepare stmt1 from @query;
execute stmt1;

deallocate stmt1;


How to get an array as output parameter from stored procedure? This is my stored procedure:

CREATE PROCEDURE `getAttritionData`(
 IN `supid` BIGINT,
 IN `from_date` DATE,
 IN `to_date` DATE,
 OUT `attr_rate` FLOAT,
 OUT `TERM_EMP_CNT` INT
)
DETERMINISTIC
BEGIN
 DECLARE i INTEGER DEFAULT 0;
 DECLARE CNT_EMP INTEGER DEFAULT 0;
 DECLARE TOT_EMP INTEGER DEFAULT 0;
 DECLARE AVG_HCOUNT INTEGER DEFAULT 0;

 SELECT count(employee_id) INTO TERM_EMP_CNT FROM employee
  WHERE term_date between from_date and to_date AND app_role=1;
 WHILE from_date<=to_date DO
   SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP FROM employee
    WHERE sup_id=supid AND doj<from_date
    AND (term_date>from_date OR term_date='0000-00-00') AND app_role=1;
   SET from_date=date_add(from_date, INTERVAL 1 DAY);
   SET i=i+1;
   SET TOT_EMP = TOT_EMP + CNT_EMP;
 END WHILE;
 SET AVG_HCOUNT=(TOT_EMP/i)*365/12;
 SET attr_rate = TERM_EMP_CNT/AVG_HCOUNT;
END

I want attr_rate as an array, where it has 1 key and another value such as:

attr_rate[6-2015]=3.5
attr_rate[7-2015]=6.2
attr_rate[8-2015]=3.3

How do I declare an array in a MySQL stored procedure?

Array is not supported in stored procedure, you can use temporary table.

Try this code:

CREATE PROCEDURE `getAttritionData`(IN `supid` BIGINT, IN `from_date` DATE, IN `to_date` DATE, OUT `attr_rate` FLOAT, OUT `TERM_EMP_CNT` INT)
DETERMINISTIC
BEGIN
    DECLARE i INTEGER DEFAULT 0;
    DECLARE CNT_EMP INTEGER DEFAULT 0;
    DECLARE CNT_EMP_SUS INTEGER DEFAULT 0;
    DECLARE TOT_EMP INTEGER DEFAULT 0;
    CREATE TEMPORARY TABLE T1(CDATE DATE,TOTAL_EMP INTEGER, TOTAL_SUSPENDED INTEGER);
    WHILE from_date<=to_date DO
        SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP
        FROM employee
        WHERE sup_id=supid AND doj<from_date AND (term_date>from_date OR term_date='0000-00-00') AND app_role=1;
        SELECT COUNT(EMPLOYEE_ID) INTO CNT_EMP_SUS
        FROM employee
        WHERE sup_id=supid AND term_date=from_date AND app_role=1;
        INSERT INTO T1(CDATE,TOTAL_EMP,TOTAL_SUSPENDED) VALUES (from_date,CNT_EMP,CNT_EMP_SUS);
        SET from_date=date_add(from_date, INTERVAL 1 DAY);
    END WHILE;
    Select * from T1;
    SELECT *,count(CDATE),sum(TOTAL_EMP),SUM(TOTAL_SUSPENDED),((SUM(TOTAL_EMP)/count(CDATE))*365/12) AVG_HEAD_COUNT, (SUM(TOTAL_SUSPENDED)/((SUM(TOTAL_EMP)/count(CDATE))*365/12)) atr_rate FROM T1 group by MONTH(CDATE);

END

Hope your issue will be fixed.


User has db_owner through AD group but cannot create table We've setup a SQL Server 2012 Enterprise, and through certain domain user groups, users are granted access to several databases.

For instance, group 'ADB' has for several databases following rights:

Server role: public

User mapping: db_datareader, db_writer, db_owner, db_public

Now, our application sometimes needs to execute certain queries. One of them was a CREATE TABLE statement. When a user of the group 'ADB' opens the application of us, in the background the script is run to CREATE TABLE. But it doesn't do that, because the user has no right to create. I don't understand because that user is part of a domain group that is DB_OWNER.

If I run a T-SQL command that shows all the members of db_owner, I don't see the AD group 'ADB' (as I would have expected) for those databases, but I do see 'sa'. Could this be the cause of my problem?

Hundreds of databases have rights assigned through server roles and user mapping, manually through SSMS. So it will be a lot of work fixing those things, but maybe it can be done through scripting. All I want to know is, why a user can't do a create table if he is part of a domain group that is db_owner of a database?

There are so many possibilities, but here are a few things to check:

that the user is a member of the ADB group in the Active Directory.
that ADB exists as a server principal (a login). Example: SELECT * FROM sys.server_principals WHERE [name] LIKE '%ADB';
that this login has a user in the database in question: SELECT * FROM myDatabaseName.sys.database_principals WHERE [sid] IN (SELECT [sid] FROM sys.server_principals WHERE [name] LIKE '%ADB');
that the database principal (the user) is a member of db_owner (not to be confused with schema ownership)
like @mlachman points out in another answer, that the database principal hasn't been specifically denied any actions.

Also,

You don't need db_public, db_datareader or db_datawriter on ADB, if it's db_owner. Good housekeeping to clean those memberships up, and it may even help with your problem.

If it's any use to you, I've written a script that compiles all of the permissions in a database. It's available on the Downloads page of my blog, and it's completely "as-is", without any warranty or guarantee.


Configure second SQL Server Alias for named instance In my devopment environment (Win 2008 server) are running two sql server 2008 instances (default and named instance).

Already one Sql alias is configured and pointing to the default instance. I want a second alias pointing to the named instance but have no idea how to achieve this.

In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, right-click Aliases, and then click New Alias.

In the Alias Name box, type the name of the alias. Client applications use this name when they connect.

In the Server box, type the name or IP address of a server. For a named instance append the instance name.

In the Protocol box, select the protocol used for this alias. Selecting a protocol, changes the title of the optional properties box to Port No, Pipe Name, or Connection String.

http://msdn.microsoft.com/en-us/library/ms190445.aspx


Passing a literal ? to sp_MSforeachdb I am trying to search for the ? character in a string, but across all dbs on a server. Since the ? mark is a placeholder for the database name, how may I format the query so it knows I mean to search for ? and not replace it with the database name?

The WHERE clause at this time is:

WHERE DirectoryPath LIKE ''%?%''

This renders to:

WHERE DirectoryPath LIKE '%DATABASENAME%'

I need it to render as:

WHERE DirectoryPath LIKE '%?%'

Updates thanks to Martin. If you view the text for the procedure:

EXEC sys.sp_helptext N'sp_MSforeachdb';

You will see that it passes the @replacechar as NCHAR(1) (default N'?') into sys.sp_MSforeach_worker. Peeling back another layer of the onion, you will see really ugly code in that procedure that loops through every character in the command and rebuilds it. I didn't study every single line but I could not find any evidence of an ability to escape or preserve the actual question mark conditionally, unless - as Martin points out - you override it with a different character that can't naturally appear in the command. So perhaps something like:

EXEC sp_MSforeachdb N'SELECT ''Ƥ'', other_columns FROM [?].dbo.whatever
  WHERE DirectoryPath LIKE ''%?%'';', N'Ƥ';

-- Ƥ is NCHAR(420), chosen randomly

Since it is really looking for the literal ? character, a possibly easier way would be this:

EXEC sys.sp_MSforeachdb N'PRINT ''?'' + ''-'' +  CONVERT(VARCHAR(32), 0x3F)';

So extrapolating that, we can construct this ugly variation:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', other_columns FROM [?].dbo.whatever
  WHERE DirectoryPath LIKE ''%'' + CONVERT(VARCHAR(32), 0x3F) + ''%'';';

That all said, you could write your own version of sp_MSforeachdb that uses a different default value for @replacechar, so you don't have to override it manually. You probably should be writing your own version instead of relying on the built-in procedure anyway, as I point out in these posts - it is unsupported, undocumented, and actually has a very high frequency bug where it skips databases due to the cursor options used:

https://sqlblog.org/2010/12/29/a-more-reliable-and-more-flexible-sp_msforeachdb
https://sqlblog.org/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/


Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key? Which one is the best choice for primary key in SQL Server?There are some example code:

Uniqueidentifiers

e.g.

CREATE TABLE new_employees
   (employeeId   UNIQUEIDENTIFIER      DEFAULT NEWID(),
   fname      VARCHAR(20) )
GO
INSERT INTO MyUniqueTable(Characters) VALUES ('Karin')
GO

Identity columns

e.g.

 CREATE TABLE new_employees
 (
  employeeId int IDENTITY(1,1),
  fname varchar (20)
 );

 INSERT new_employees
    (fname)
 VALUES
    ('Karin');

[Material code](or Business Code,which identity of a material. e.g. customer identifier)

e.g.

CREATE TABLE new_employees(
    [ClientId] [varchar](20) NOT NULL,
    [fName] [varchar](20) NULL
 )

 INSERT new_employees
    (fname)
 VALUES
    ('C0101000001',--customer identifier，e.g.'C0101000001' a user-defined code.
     'Karin');

Please give me some advice for choosing the primary key from the three type identity column,or other choices.

Thanks!

Fabian Pascal identifies four pragmatic criteria for choosing a key: familiarity, irreducibility, stability and simplicity. He also notes that sometimes you must make trade-offs.

During the logical design phase you will identify all the candidate keys for an entity.

During the physical design phase you will decide which of them will be the primary key (the one usually used in referential integrity, in other words, foreign keys). If you don't think any of the candidate keys are suitable for the primary key then you choose to use an arbitrary key, and then decide what mechanism will be used to generate the arbitrary key, perhaps the application generates it, perhaps the server does (identity, guid with newid, sequence, something else). If your systems will be multi-master in some way then application-generation might be better, as most of the server-side mechanisms don't handle this.

The choice of primary key is determined by size and type - you will be joining on it, so you want it to be small.

During the physical phase you will also be doing index design, which includes deciding which index will be the clustered index. You want that to be small as well, since the clustered index key is included in all non-clustered indexes. You want it to be unique, otherwise SQL Server will add a uniquifier, which has a small impact on performance.

On the other hand, this is not the 70s. We now have computers that don't fill entire warehouses and take all week to run the payroll. If won't kill your performance to use a guid or a char(8) either as a clustered key or as a primary key. Unless you are UPS or AIM Healthcare (two winners of WinterCorp's 2005 TopTen survey).

Edit: I just had this link arrive in my mailbox. Simple talk Primary Key Primer for SQL Server

Edit: Joel's comment below is very important. Natural keys often aren't stable. Even government-issued keys (tax numbers, drivers licence numbers) that should be static can change. For example, ask Mrs. Hilda Schrader Whitcher about her two SSNs. You control the arbitrary key so you control its stability.

In my (admittedly limited) experience, I can't remember a time we used a natural key. I've done work in healthcare, where there were government-issued id numbers (like NZ's Community Services Card) but not every person had them, so they were not suitable for PKs.


Setting sql_slave_skip_counter is not working on Percona 5.6.24 I am currently running a master - slave replication where on my slave there is an sql error due to which my replication was stopped.

when I tried to run the following good old solution

SHOW SLAVE STATUS \G;
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS \G;

it didn't work. The Skip_Counter shows a value of 1 but when I again ran SHOW SLAVE STATUS the value was decreased to 0 but the error was still there. Along with that, Exec_Master_Log_Pos doesn't change.

is there any thing wrong with my expectation ?

EDIT

Error is as follows:

Error 'String 'abcde_abcdefgh_abcd' is too long for user name (should be no longer than 16)' on query. Default database: ''. Query: 'GRANT SELECT ON mydb.* TO 'abcde_abcdefgh_abcd'@'192.168.1.4' IDENTIFIED WITH 'mysql_native_password' AS 'PASSWORD_HASH'

My Master version is 5.7 and that on slave is 5.6. Upgrading the slave did solve the problem as the query could be executed on slave as well but my question is still there. Why couldn't skip counter solve the replication blockage ?

From the release notes for 5.7.8:

"The maximum length of MySQL user names has been increased from 16 characters to 32 characters, which provides greater flexibility in choosing the user name part of MySQL account names. The change affects permitted user names in these contexts: ..."

followed by a long list of "Account Management Notes".  That was in addition to some "Incompatible Changes" in 5.7.6.

I guess that is one reason for advising against the Master running a newer version than the Slave.

It should be possible to rummage through the binlog using mysqldumpbinlog and find the pos of the next command.  Then do CHANGE MASTER ... to change just the position.

(Meanwhile, I am surprised that the SKIP... failed.)

Bottom line:  Previously, it was often safe to mix Master/Slave versions.  But 5.7 has so many significant changes that I suspect this is just one of many potential problems.


How to send alert email to multiple recipients whenever SQL SERVER authenticated user is login in SSMS? I need to trace on SQL SERVER authenticated user whenever he/she is logging in to the system, automatically send alert mail like this user is login with this IP and this time.

I have just one solution for this problem but this is not so proper because i have to see that manually.

Below solution is not work for alert mail because i have to run manually.

SELECT
   DISTINCT
    @@SERVERNAME ServerName ,
    DB_NAME(database_id) DatabaseName ,
    s.login_name ,
    s.[host_name] ,
    c.client_net_address ,
    c.connect_time
FROM    sys.dm_exec_connections AS c
        INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE   c.auth_scheme = 'SQL'
    AND program_name IN ( 'Microsoft SQL Server Management Studio',
                          'Microsoft SQL Server Management Studio - Query' )
    AND c.connect_time >= DATEADD(DAY, -1, GETDATE())
    AND login_name <> 'prcommon';

Thanks.

Here I got the answer of this question:

First i made on Table for storing history of SqlServerLoginHistory:

CREATE TABLE AuditDetail
(
ServerName VARCHAR(255),
DatabaseName VARCHAR(255),
LoginName VARCHAR(255),
HostName VARCHAR(255),
ClientNetAddress VARCHAR(255),
ConnectionTime DATETIME2
)

After that made one logon trigger.

CREATE TRIGGER [connection_limit_trigger]
ON ALL SERVER
FOR LOGON
AS
BEGIN
INSERT INTO AuditDetail
SELECT
       DISTINCT
        @@SERVERNAME ServerName ,
        DB_NAME(database_id) DatabaseName ,
        s.login_name ,
        s.[host_name] ,
        c.client_net_address ,
        c.connect_time
FROM    sys.dm_exec_connections AS c
        INNER JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id
WHERE   c.auth_scheme = 'SQL'
        AND program_name IN ( 'Microsoft SQL Server Management Studio',
                              'Microsoft SQL Server Management Studio - Query' )
        AND c.connect_time >= DATEADD(DAY, -1, GETDATE())
        AND login_name <> 'prcommon' and not exists (select 1 from Auditdetail where auditdetail.connectiontime = c.connect_time);

END;
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER
GO

After that made on Simple trigger (after insert) on AuditDetail table to send alert mail .

CREATE TRIGGER auditdetailtrigger
ON AuditDetail
AFTER INSERT
AS
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Admin',
                            @recipients = 'abhalala@stackoverflow.com',
                            @body = 'Customise your code',
                            @subject = 'New User Login With Sql Server Authentication';
END


Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675) Some of our Internal DBs Oracle installed in Linux and Windows servers have been highlighted with the issue Oracle TNS Listener Remote Poisoning (CVE-2012-1675).

  This security alert addresses the security issue CVE-2012-1675, a vulnerability in the TNS listener which has been recently disclosed as "TNS Listener Poison Attack" affecting the Oracle Database Server. This vulnerability may be remotely exploitable without authentication, i.e. it may be exploited over a network without the need for a username and password. A remote user can exploit this vulnerability to impact the confidentiality, integrity and availability of systems that do not have recommended solution applied.

All our DBs are 11.2.0.4, but reading all the notes from Oracle or third parts seems that this Oracle's version is not affected.

Anyway,Following the Doc ID 1600630.1 from Oracle Support, I made a change in the listener.ora adding the below string:

VALID_NODE_CHECKING_REGISTRATION_listener_name = ON

We are using NessusScan to highlight the Vulnerabilities on our servers. After i made the change above, we ran the scan and there is still one server highlighted with this error:

  The remote Oracle TNS listener allows service registration from a
  remote host. An attacker can exploit this issue to divert data from a
  legitimate database server or client to an attacker-specified system.

  Successful exploits will allow the attacker to manipulate database
  instances, potentially facilitating man-in-the-middle, session-
  hijacking, or denial of service attacks on a legitimate database
  server.

Checking the server and Oracle Listener there is nothing wrong or any errors, it just come up with the Scan with the above error.
Trying to fix this issue, I found this page:

https://community.oracle.com/thread/4008690?start=0&tstart=0

but mine is not a XE version, my version is:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Please, if anyone had to face this issue with Oracle 11.2.0.4, could share how it has been fixed would be great.

So a scanner program shown some nice text message without any facts (timestamp, addresses, how it determined the listener was vulnerable).
Yet you say you saw nothing on the server.

The listener logs service registrations. It also logs rejected tries with VNCR enabled.

So here is my listener without the parameter set:

[oracle@o61 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = o61.balazs.vm)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
[oracle@o61 ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:45:22

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/o61/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-OCT-2018 22:45:22
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/o61/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Now I set VNCR (not even in listener.ora, but dynamically):

[oracle@o61 ~]$ lsnrctl show valid_node_checking_registration

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:46:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
LISTENER parameter "valid_node_checking_registration" set to
The command completed successfully

[oracle@o61 ~]$ lsnrctl set valid_node_checking_registration on

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:46:43

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
LISTENER parameter "valid_node_checking_registration" set to on
The command completed successfully

From a different host, but the same subnet (which does not matter know because this level of this parameter rejects requests even from the same subnet):

[oracle@o62 ~]$ nslookup o61.balazs.vm
Server:         192.168.1.2
Address:        192.168.1.2#53

Name:   o61.balazs.vm
Address: 192.168.1.61

[oracle@o62 ~]$ nslookup o62.balazs.vm
Server:         192.168.1.2
Address:        192.168.1.2#53

Name:   o62.balazs.vm
Address: 192.168.1.62

[oracle@o62 ~]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:fc:79:ce brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.62/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever

From a database instance, I try to register that instance into the listener on the other host:

[oracle@o62 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 26 22:49:12 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set remote_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))';

System altered.

Then, still no services:

[oracle@o61 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2018 22:49:50

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=o61.balazs.vm)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-OCT-2018 22:45:22
Uptime                    0 days 0 hr. 4 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/o61/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=o61.balazs.vm)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

And in the log of the listener I see this:

[oracle@o61 ~]$ tail -10 /u01/app/oracle/diag/tnslsnr/o61/listener/trace/listener.log
Fri Oct 26 22:49:18 2018
Listener(VNCR option 1) rejected Registration request from destination 192.168.1.62
26-OCT-2018 22:49:18 * service_register_NSGR * 1182
TNS-01182: Listener rejected registration of service ""
Listener(VNCR option 1) rejected Registration request from destination 192.168.1.62
26-OCT-2018 22:49:21 * service_register_NSGR * 1182
TNS-01182: Listener rejected registration of service ""
Fri Oct 26 22:49:50 2018
WARNING: Subscription for node down event still pending
26-OCT-2018 22:49:50 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=o61.balazs.vm)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0

As you can see the registration attempt was rejected, works as intended. I still find it difficult to believe that you configured VNCR correctly and the scanner found the listener vulnerable.

Maybe the name of the parameter was mistyped, or the listener was not restarted. Or the scanner reports a false positive for whatever reason.

Anyway you can simply test and confirm just as I did above.


Does Oracle RAC require that the Ethernet aliases be in a different IP range than the base addresses? Let's say I have 3 nodes running RAC. The base addresses (eth0 interfaces) for those nodes are in the 192.168.1.0/24 range.

From what I've seen RAC requires that you create Ethernet alias interfaces (eth0:0) on each node, with addresses in a different range. But is that a requirement? Isn't it possible to create eth0:0 with just other addresses within 192.168.1.0/24?

From what I've seen RAC requires that you create Ethernet alias
  interfaces (eth0:0) on each node, with addresses in a different range.

Thats not correct. If eth0 is the interface for the public network, then the VIPs and SCAN VIPs must be on the same subnet, that is exactly what you are asking for. Also its not you, but Oracle Grid Infrastructure that creates and manages these virtual IPs, you just specify the name and address for them.

Furthermore, the private virtual IPs (called HAIP) on the private network (lets say eth1) will be created in the 169.254.0.0/16 subnet, no matter what.

So here is a sample configuration for a 3 node cluster:

Public addresses:

node1, eth0: 192.168.1.1/24
node2, eth0: 192.168.1.2/24
node3, eth0: 192.168.1.3/24

VIPs, with names defined in DNS or hosts file:

node1-vip, eth0:0: 192.168.1.11/24
node2-vip, eth0:0: 192.168.1.12/24
node3-vip, eth0:0: 192.168.1.13/24

Private addresses (note the different subnet), names are not necessary to be defined:

node1-priv, eth1: 192.168.2.1/24
node2-priv, eth1: 192.168.2.2/24
node3-priv, eth1: 192.168.2.3/24

SCAN VIPs (11.2.0.1 or above), with one single name defined in DNS:

192.168.1.21/24
192.168.1.22/24
192.168.1.23/24

Can run on any node as eth0:X. Number of SCAN VIPs is independent of number of nodes.

HAIP (11.2.0.2 or above), no names, the IPs are dynamic, so I have just put some random IPs here to complete the example:

node1: eth1.1: 169.254.190.49/16
node2: eth1.1: 169.254.203.17/16
node3: eth1.1: 169.254.243.66/16


Service accounts for SQL Server 2012 only allow pre-Windows 2000 name? I've just set up SQL Server 2012 with domain service accounts. I found that the account name would not validate unless I used the shorter user name.

Is there any way of using the full names?

Confusion comes from the fact that user account objects have two names in Active Directory: User Principal Name (UPN) and Security Accounts Manager ID (SAMID). Both of these must be unique in the forest.

The UPN is composed of the user logon name concatenated with an at symbol concatenated with one of the UPN suffixes that are set up in the AD forest. It is the "User logon name" entry in the dialog above. For example administrator@adatum.com.

The SAMID is composed of the NetBIOS name of the domain concatenated with a backslash concatenated with the NetBIOS name of the user account. It is the "User logon name (Pre-Windows 2000)" entry in the dialog above. For example, ADATUM\Administrator.

Many places in Windows accept either name; many do not. For example, logging on to a non-AD machine such as Windows NT4 requires the use of the SAMID. For example, logging on across a forest trust may require the use of the UPN.

The user account name part of the SAMID is also used to generate user folders (local profile, home folder, roaming profile, etc).

As for your problem, the service account should, as far as I know, accept either SAMID or UPN. You do, however, have to specify the full UPN. In other words sqlservice@adatum.com, not just sqlservice. I'll do some testing here once my VMs are started.

OK, after some testing (SQL Server 2008 R2 and SQL Server 2012), it seems that SQL Server Configuration Manager ust doesn't like the UPN. I kept getting a "specified network password is not correct" error. Google found me this StackOverflow issue. As that page states, changing the service account in the Services MMC can use both UPN and SAMID.

Historically, changing service accounts using a tool other than SQL Server Configuration Manager was bad as it didn't put the new service accounts in the correct groups. Newer versions of SQL Server don't use groups for registry and folder permissions but use virtual accounts instead. Despite that, I think my advice is still to always use Configuration Manager and to use specify service accounts using their SAMID.

As an aside, you may be better off using Managed Service Accounts (if your AD supports it). They remove the need for you to deal with password expiry and SPN registration.


Can I add an Arbiter node in the same server of my Config-Server? I'm using MongoDB and I want to know if I can add an Arbiter node in the same server that I'm hosting my Config-Server. Is it possible?

Let's say that I have 3 sharded clusters with 6 servers (2 per shard), 3 config-servers (each one will have arbiter for each shard) and 1 server as mongos (router). Is this good enough?

Also, what is the suggested specs for those Config-Arbiter servers would be?

Yes. You can add an Arbiter and a Config Server in the same machine.

Generally, you should avoid setting 2 DB process in the same machine because:

Its harder to provision/estimate the machine resources for 2 process running simultaneously
Its usually cheaper to have 2 low-end machines than 1 high-end (horizontal x vertical scaling).
Some resources can't scale in your environment. E.g: network bandwidth.

However, if you want to share resources only with an Arbiter and Config nodes, since they require much less resources then a Primary node, this configuration is quite good.

The following article shows how you could set up your environment with nice images:
http://www.kchodorow.com/blog/2010/08/09/sharding-and-replica-sets-illustrated/

Setup: 3 replica sets

M = Master
S = Slave
A = Arbiter

Configuration: 3 servers

C = Config Server
No letter = mongos

Since this configuration mixes things, you can achieve high availability and sharding using just 3 servers.


Automating timestamp fields in PostgreSQL I'm trying to define the following table in PostgreSQL:

posts
- uuid
- title
- created_at
- updated_at
- deleted_at

The idea is that i never delete anything, so i change the deleted_at instead of removing the row and use where deleted_at is null for normal behavior.

Is there a way of automating those timestamps?

Different columns,

created_at this simple, just give it a DEFAULT now()
updated_at use the spi module which provides a higher-order trigger than can be applied to any table, see this post, also see row-modification-time
deleted_at how would you automated that anyway? What would that even look like? Just set it when you delete the post.


Inefficient queries on partitioned tables Consider a scenario in SQL Server that includes a partitioned table with over 500 partitions. The number of partitioning column values in a temp table are less than 10. We join the temp table to the partitioned table directly on the partitioning column. (Not using $PARTITION, just purely column values.) The query includes joins to other dimension tables in a typical star-schema type way.

Furthermore, there are no non-clustered indexes on the table. The leading column in the clustered PK (ignoring the partitioning key id which is always leading) is a column that joins to one of the star-schema dimension tables and will include a filter. However, the filter on the dimension table would not be selective enough to cause an index seek, if there was an index, if the partitions were just a single table.

So, given the above, why is it that queries on this partitioned table do not always use partition elimination? When would it ever be more efficient to scan a partitioned table than just the partitions required?

At worst a partitioned table with one partition would be the same, ignoring the cost to determine the partition elimination etc.

It depends on the query, so if you have a specific problem you'll need to give us details for the query (and possibly table structure) in order to get specific help.

Generally speaking though:

If the filter is not sargable then it is no help to the query planner in the same way as references to indexes where the clause is not sargable - for instance if a function or sub-squery that can not be simplified down to a single value for all possible rows is involved.
If there are other filters on columns covered by non-partitioned (or unaligned) indexes the query planner may consider searching that way to be more efficient than using the partitioning rule.


BCP - Different Schema I have a transactional Replication (Non-SQL Server Publishers - Oracle) implemented for one SQL Server Database. Let's say Publishers: A, B
Subscriber: C
Note:

I can not modify the Oracle DBs.
The Publishers have the same DB schema.
On the subscriber I have the same tables, with one column more "Publisher" to identify where that line comes from.

I used the After-Snapshot Script to create and populate this column for the Initial Load for the Publisher A.
Also, created a (After-Snapshot) script for the publisher B to populate the column.
It works when I do the first snapshot for A, and my bulkload fails for B with the following error:

Replication-Replication Distribution Subsystem: agent AgentName failed. The process could not bulk copy into table '"dbo"."EMployees"
Error Log: Error: 14151, Severity: 18, State: 1.
Replication Monitor Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

Anyone has an idea? Is it due the schema difference between B and C, since when the A does the bulkload there is no table and first I create the table, then do the initial load, the do the alter table to add the column, update values to be ready for the second bulk load?

I'm not sure of the limitations of transactional replication, but with BCP you can create a view on the table that contains only columns that exist in the BCP file, and load into that view rather than the table. How do you define the data for the publisher column at the moment?


Oracle 12c on Ubuntu 12.04 I just installed Oracle database 12c on an Ubuntu 12.04. I followed this tutorial Installing Oracle 12c on Ubuntu 12.04 (64 bit)..a hard journey, but it’s worth it! by GemsOfProgramming (kudos to the guy who did it by the way). I haven't done much more than that.

After the installation I tried to connect sqldeveloper to the database, something I already did with 11g & Win7 and 11g & Ubuntu, however I'm getting this error:

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection

I tried executing the netac command but it didn't work as a command I had to execute the bin, and the output was:

oracle@abdou-pc:/oracle/base/dbhome_1/bin$ ./netca

Oracle Net Services Configuration: No protocol specified No protocol specified Error: null

and here is the log:

http://pastebin.com/ir4ecG3u

thought this may also be of use:

root@abdou-pc:/home/abdou# oraenv

ORACLE_SID = [root] ?

ORACLE_HOME = [/home/oracle] ?

ORACLE_BASE environment variable is not being set since this

information is not available for the current user ID root.

You can set ORACLE_BASE manually if it is required.

/usr/local/bin/oraenv: 223: [: unexpected operator

My Oracle installation is in /oracle/base/dbhome_1

I don't know what other info I should post, and I don't wanna flood you people, so I'm here and I can post any info you need on demand.

It seems to me that my Oracle db isn't even launched, and neither are the listeners, and there might be a problem with my environment variables.  I don't have much experience with the configuring part of the databases, and I know that Oracle isn't supported on Ubuntu ... but I like Ubuntu and it's just for learning purposes.

To check if database or listener is running on Linux, you could check if Oracle processes are running, for example ps aux | grep pmon(or grep lsnr for listener).
Pmon is process monitor, process responsible for "housekeeping", like freeing buffer cache or some resources that are not in use anymore. It is always running if the instance is up ( but this process does not guarantee that you can connect, because the database could be started in mount mode, anyway it is a start of a troubleshooting ).

It seems that you didn't set ORACLE_SID, ORACLE_BASE, ORACLE_HOME and PATH variables. On link that you provided, environment variables are set at the runtime. You could permanently set them in your .bashrc or bash_profile file.

Add these lines to your .bashrc file(it is in your users home directory, for example /home/oracle/.bashrc).

ORACLE_SID=name_of_your_database

ORACLE_BASE=/oracle/base/

ORACLE_HOME=/oracle/base/dbhome_1

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH

Reload new bash profile with command - source .bashrc and try again and read Oracle installation documentation for more detalis.

However, since Ubuntu is not supported OS for Oracle, there is no way to know for sure what the problem really is or was. If installation is for learning purpose, easier for you will be to install some supported OS in a VirtualBox for example, or to use Oracle prebuild VMs.


fundamental difference between Primary/Clustered and Secondary/Non-Clustered index Are

Primary Index and Clustered Index similar in concept
Secondary Index and Non-Clustered Index similar in concept

And it looks like curiosity didn't actually kill the cat :-). So I found this book Database System Concepts (Sixth Edition) online and it's freely available to be read. I've scratched it a bit and it turns out that it has the needed definition inside:

Primary index: in a sequentially ordered file, the index whose search
key specifies the sequential order of the file. Also called
clustering index. The search key of a primary index is usually but
not necessarily the  primary key.
Secondary index: an index whose search key specifies an order  different from the sequential order of the file. Also called  non-clustering index.

So there you have it. Now I suppose you were reading another book (by same author(s)) which was with tests inside (Q&A) and not this one with theory. I'd say you should use them both at the same time.
Now, I'm not sure that a general book would be the best way to start. Personally I'd start with any freely available kit (SQL Server Express, MySQL Lamp, Oracle XE, SQLite...whatever sounds good for your ear) and play with concepts and also their physical implementation. I'd say going also with examples will work better than just pure theory.


SQL Server 2016 - Is the Microsoft R Client any different from a standalone Microsoft R Server? Along with the integrated R services on SQL Server 2016, Microsoft is also offering enterprise level Microsoft R Server as a standalone installation.
Some of the documentation on MSDN shows that the standalone MS R Server is different from a data science Microsoft R Client.
This is a picture from the MSDN page (bottom left): https://msdn.microsoft.com/en-us/library/mt696069.aspx

The data science client link takes you here: https://msdn.microsoft.com/en-us/library/mt696067.aspx

But the set-up wizard comments as if they're both the same thing.

If anyone has any clarity on this, please let know.

Looks like I found an answer to my own question on digging further.

While Microsoft R standalone Server addresses the in-memory limitations of open source R by adding parallel and chunked processing of data, Microsoft R Client is in-memory bound; i.e. it can only process datasets that fit into the available local memory. However, it can operate on large volumes of data if the compute context is set to an instance of Microsoft R Server like the SQL Server R Services or R Server for Hadoop etc.

Microsoft R Client has almost all of the architectural features as a MS R Server - DistributedR, ScaleR (RevoScaleR package), ConnectR. DeployR is unavailable in the MS R Client.

Microsoft R Client also has the Math Kernel Library for the non-scaleR functions, and can use up to 2 threads for ScaleR functions in a local compute context.

One advantage that the MS R Client has on the MS R Server is that it is free for everyone. MS R Server (standalone) requires a commercial license.

More details here: The Microsoft R: A Product Comparison


Lot of log files in $FSLOG generating. How to stop? I'm using SQL Server 2012 Express Edition and I have a problem with huge amount of files generated in:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\MyDatabaseStream_Filegroup_Data\$FSLOG\

It is generating roughly 150 files per minute and taking so much disc space on C Drive. Size of MyDatabaseStream is growing rapidly quick and making C Drive full (around 140GB)

How can I stop generating those files?

Make a backup. Those are not log files - where did you get this idea from?

Yu basically store some data in the file system - MyDatabaseStream_Filegroup_Data\$FSLOG\

Database FIleStream feature. VERY nice if you (a) know what you do there and (b) need it.

Either you back up - thzen surplus can be deleted - or you move it to another disc or.... you disable file stream storage.


Removing physical fragmentation If I have inherited db that has grown to 400GB 1 MB chunk at at time. Is an offline defrag the only way to remove all the physical fragmentation. Another issue I have is that the data and log files are mixed and on the wrong RAID drives.

Can I kill 2 birds with 1 stone by backing up the db, delete it and restore it using WITH MOVE to put the files on the correct RAID drives. Will this eliminate the physical fragmentation?

No, backup / restore will keep all the fragmentation. Probably better to add a filegroup with files in the new location, and recreate all of your user tables on the new filegroup (by recreating the indexes with DROP_EXISTING, and as an online operation if possible). You won't be able to eliminate the original files entirely, but if you've moved all the user objects, you should be able to shrink the primary data file down to system objects only.

If you then want to move the primary MDF file and log file to the new RAID drives, you can do this with ALTER DATABASE - take the database offline, alter the individual file locations, physically move the files, then bring the database back online again.


How to prevent a PostgreSQL trigger from being fired by another trigger? I have 2 triggers on one table; one works for INSERTs :

CREATE TRIGGER "get_user_name"
AFTER INSERT ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "add_info"();

This updates some values in the table.
And one for UPDATEs (to fill a history table):

CREATE TRIGGER "set_history"
BEFORE UPDATE ON "field_data"
FOR EACH ROW EXECUTE PROCEDURE "gener_history"();

The problem is that when I insert a new row in the table the procedure "add_info"() makes an update and therefore fires the second trigger, which ends with an error:

ERROR:  record "new" has no field "field1"

How can I avoid this?

(Obvious error in the trigger logic aside.)
In Postgres 9.2 or later, use the function pg_trigger_depth() that @Akash already mentioned in a condition on the trigger itself (instead of the body of the trigger function), so that the trigger function is not even executed when called from another trigger (including itself - so also preventing loops).
This typically performs better and is simpler and cleaner:
CREATE TRIGGER set_history
BEFORE UPDATE ON field_data
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE gener_history();
The expression pg_trigger_depth() < 1 is evaluated before the trigger function is entered. So it evaluates to 0 in the first call. When called from another trigger, the value is higher and the trigger function is not executed.


Why didn't this statistics object on a filtered index auto update? In a SQL Server 2012 SP1 instance, I have a filtered index on the PK column of a table that looks like this:

CREATE INDEX [RF_IXF_Orders_OrderNumber] ON [dbo].[Orders]
(
    [OrderNumber] ASC
)
WHERE [OrderSource]='MO'
AND [Cancelled]=(0)
AND [NumItems]>(0)
AND [OrderDate]>'2014-05-15';

The Row Count (dm_db_partition_stats.row_count) of the index is 8416, the Rows Changed (sysindexes.rowmodctr) is 16803 (193.6%), and Auto Update Statistics is True. There have been 8400 user_scans and 50,088 user_updates. The last user_scan was today, yet the statistics haven't been updated in about a week.

Why wouldn't the statistics have auto updated?

Fatal flaw in filtered indexes/statistics: churn is measured by % of row count for the table, not the index/statistic. Paraphrased from my blog post here:

  Filtered indexes don't benefit from auto-updating of stats based on a percentage change of the subset of the table that is identified by the filter predicate; it is based (like all non-filtered indexes) on churn against the whole table. This means that, depending on what percentage of the table is in the filtered index, the number of rows in the index could quadruple or halve and the statistics won't update unless you do so manually. Kimberly Tripp has given some great information about this (and Gail Shaw cites an example where it took 257,000 updates before statistics were updated for a filtered index that contained only 10,000 rows):http://www.sqlskills.com/blogs/kimberly/filtered-indexes-and-filtered-stats-might-become-seriously-out-of-date/Also, Joe Sack has filed a Connect item that suggests correcting this behavior for both filtered indexes and filtered statistics:http://connect.microsoft.com/SQLServer/feedback/details/509638

You shouldn't rely on auto-update statistics for filtered objects. If the cardinality is important and the subset changes a lot more than the whole table, you will need to manage these manually.


Why does `row_to_json(...)` accept a sub-query? I recently encountered this example (via a tutorial and other answer):

 SELECT row_to_json(x)
 FROM (SELECT id, title FROM my_table) x;

I'm confused about the use of row_to_json(x). I thought it only accepted rows, but it seems to also accept a sub-query. I found this counter-intuitive.  According to the documentation, it accepts a record: row_to_json(record [, pretty_bool]). Other examples online use a row() constructor: row_to_json(row(col1, col2)). Does this mean a sub-query is the same as a record and a row value/composite value?

Given next example:

CREATE TABLE tbl (id int, title text);

INSERT INTO tbl VALUES
(1, 'Title 1'),
(2, 'Title 2'),
(3, 'Title 3');

  Does this mean a sub-query is the same as a record and a row value/composite value?

Let me select some records from tbl:

SELECT id, title FROM tbl;

id | title
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

Now using a subquery:

SELECT * FROM (SELECT id, title FROM tbl) t;

id | title
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

As you can see both queries return same rows, then you can use both queries to generate a JSON result.

SELECT row_to_json(x)
FROM   (SELECT id, title FROM tbl) x;

| row_to_json                |
| :------------------------- |
| {"id":1,"title":"Title 1"} |
| {"id":2,"title":"Title 2"} |
| {"id":3,"title":"Title 3"} |

dbfiddle here

As @ypercube has pointed out, when you executes:

SELECT tbl FROM tbl;

you get this result:

| tbl           |
| :------------ |
| (1,"Title 1") |
| (2,"Title 2") |
| (3,"Title 3") |

same output as using:

SELECT ROW(1, 'Title 1');

| row           |
| :------------ |
| (1,"Title 1") |

It is a (named) composite type. (Have a look at docs about TYPE)

  By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type — either the row type of a table, or a composite type created with CREATE TYPE AS

Having a look at row_to_json() function:

+------------------------+----------------------------------------------------+---------------------------+---------------------+
| row_to_json(record [,  | Returns the row as a JSON object.                  | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
|          pretty_bool]) | Line feeds will be added between level-1 elements  |                           |                     |
|                        | if pretty_bool is true.                            |                           |                     |
+------------------------+----------------------------------------------------+---------------------------+---------------------+

Uses a row as example: row_to_json(row(1,'foo'))


Allow access to RackSpace database from Vagrant box we have two developers using Vagrant (which has it's own internal ip like: 10.0.2.0/24). On RackSpace we have a percona database instance. We would like to allow read/write access to that database from those vagrant boxes. I've never done this from vagrant boxes and was hoping someone could lend support.

The below commands are what I've issued thus far:

sudo iptables -A INPUT -p tcp -s 10.0.2.0/24 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT

sudo iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

sudo invoke-rc.d iptables-persistent save

First, you want to find out what external IP address the Vagrant boxes NAT to:

What is my IP - Google

Then add an iptables rule for that IP address.

Then you will want to grant access from your MySQL database to the host (The Vagrant box)

GRANT ALL PRIVILEGES
ON database.*
TO 'user'@'vagrantboxremotehost'
IDENTIFIED BY 'password';

You can also use

TO 'user'@'%'

To allow from any IP, but that is not recommended.


Using Files and Filegroups I am updating a database to use multiple filegroups. As it stands, it uses just the PRIMARY filegroup.

I have read that it is recommended to create a second Filegroup (called 'Data') and mark that one as the default. Will all database objects (tables, indexes, etc) automatically switch to this new one?

Secondly, does it matter how many files are created for each filegroup? Should I create more than a single file for each filegroup or will this make any difference performance wise?

Thanks!

For me, this is a question of simplicity-of-design vs performance-and-scalability.

If I am designing a non-critical database that won't see a huge amount of traffic, then I typically would go with a single file per database since it is easy to configure and maintain.

However, if your recovery point objective is to be able to recover the database to within several minutes of any failure, and your recovery time objectives are to be able to recover within as short a time as possible, I would carefully plan the filegroups and associated files as:

The main filegroup should only contain the system tables.  This filegroup can then be very quickly restored or replaced if necessary without affecting business data.
A primary filegroup to store business critical data that can be restored quickly enough to meet the recovery time objective and get the business back up-and-running ASAP.  I would create as many files for this filegroup as I have separate and distinct drive subsystems on high-speed devices.  For instance, if I had 3 separate SSD raid arrays, I might create 3 files to spread the load across the arrays evenly.
A secondary filegroup to store historical yet business relevant data that is not critical to get the business up-and-running and yet still needs to be in the database for some reason.  Typically this would be on one or more files, depending on performance and cost requirements.

If there is a requirement to store many file-based pieces of data, for instance images or documents, with relationships to data stored in SQL Server, I would use FILESTREAM, which requires its own filegroup.


SQL Bulk Insert Impersonation issue I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.)

I am having some issues with using Bulk Insert when the data file is on a network share when using Windows Authentication. What is known is that the SQL Server service account has access to the network resource, which is shown by logging into SQL Server with a SQL account and doing the Bulk Insert. I also have rights to the files on the share, as shown by the fact that I put the files there.

Now, when connecting to SQL Server with Windows Authentication and running the Bulk Insert I get the following error (emphasis mine):

  Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file
  "\\[server]\[share]\[filename]" could not be opened. Operating system error code 5(Access is denied.).

I found this snip at BULK INSERT (Transact-SQL)\Security Account Delegation (Impersonation), which says, in part:

  To resolve this error [4861], use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

Finally, after much searching, I found this TechNet article, How to Configure the Server to be Trusted for Delegation, and we tried the unconstrained delegation and I rebooted the SQL server, but it still does not work.

What the heck am I missing???

OK, here is the answer to my particular problem.  While the commenters above were very helpful and had some good suggestions, like manually adding the SPN, there was one final part missing. You know, one of those that has to be completed on a dark stormy third Thursday of the month, only when the moon is waxing half way...

After a few days after this posting, I basically posted the same question on the Microsoft SQL forums to get a fresh prospective. Long story short, I had to have my network admin run adsiedit.msc and have him edit the service's domain account user. The userAccountControl attribute, which is a number that represents a bitmap fields had to be updated. I had to take a single existing number, 0x10200 (NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD) and OR in 0x80000 (TRUSTED_FOR_DELEGATION) and OR in 0x1000000 (TRUSTED_TO_AUTH_FOR_DELEGATION) for a grand total of 0x1090200!

Now, isn't that user friendly! I honestly don't know why that when we setup the account in the GUI for delegation that it did not set these bits too, but it didn't.

I now have Bulk Insert running correctly for a trusted connection (Windows Authentication) and impersonating correctly on two out of three of my SQL Servers. Not sure why it is not one the one, but that is for a different day...


Prevent SQL from apparently executing an expensive sub query I have a stored procedure of the form:
CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN

    SELECT  A.Value1,
            B.Value1,
            C.Value1,
            ...
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
END

Even when it gets called with @SearchString set to N'' the query takes a minute and half to execute.  If I remove the "potentially expensive query" parts then the query returns the expected data virtually instantaneously.
From what I've read, the above shouldn't evaluate anything in the ELSE part of the outer CASE statement as @SearchString is N'' so why does it act as though it is?
If this is working as expected then how do I code this to force SQL not to execute the "potentially expensive query" parts?
I'm trying to understand why they appear to affect the main query despite my efforts to ensure that they aren't called at all.

If you add OPTION (RECOMPILE) to the very end of the WHERE clause so it looks like this, does the procedure run faster now?
WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
OPTION (RECOMPILE)

If so, then you likely have a parameter sniffing issue which could be causing bad execution plans to be generated with poor cardinality estimates. If you update your post with your execution plan, then we can pinpoint exactly how that's happening.
If that's the case, this is a good article on Parameter Sniffing and OPTION (RECOMPILE).
Note I usually only use OPTION (RECOMPILE) for debugging to quickly confirm a parameter sniffing issue and try to resolve the underlying problem instead. There are use cases for OPTION (RECOMPILE) however it's a trade-off for minor performance hits since the query gets recompiled every time it's executed.
Brent Ozar has an awesome series of blog posts on Parameter Sniffing issues. Too many to individually link to be honest, so here's the root page that links the main important ones: Parameter Sniffing


Get slave status using sql query inside if-else condition How can I stop the slave if it is already running using sql query statements only?

So far I tried to do something like below but getting sql syntax error. I think it is due to the borders of result in conditional statement.

if ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS
      where VARIABLE_NAME = 'slave_running')=ON)
     then
         STOP SLAVE;
end if;

Why not simply do STOP SLAVE?  If not "slave_running", it will have no effect.


Msg 2534 : DBCC CHECKDB allocation errors We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server.

The backup Job have been failing since migrating, due to DBCC Errors (Check database integrity task).

I tried to run a DBCC CHECKDB using the repair_rebuild option on the production database yersterday, and the a subsequent DBCC CHECKDB showed no errors.
The DBCC CHECKDB against the production databas  is showing again error messages, the backup is done, but of course the maintenance plan fails: I have (a long list of)  the following error messages:

Table error: page (1:1100029), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100030), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data) due to invalid allocation (IAM) page(s).
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100031), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100200), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100201), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
There are 14 rows in 3 pages for object "searchfull_dummy_1049".
CHECKDB found 0 allocation errors and 6 consistency errors in table 'searchfull_dummy_1049' (object ID 373210913).
DBCC results for 'foldersRecherche'.
There are 0 rows in 0 pages for object "foldersRecherche".
DBCC results for 'wf'.
There are 0 rows in 0 pages for object "wf".
DBCC results for 'searchfull_dummy_1663'.

I tried to use yesterday's backup , restored the database on the same server with a new db name, and ran a DBCC Update usage(0) on the database, then DBCC CHECKDB showed no more errors on the newly restored database.

I applied the same procedure to the production database but this did not fix the issue on the production database. I'm getting the same errors as above.

If the sequence is:

DBCC CHECKDB reported errors.
REPAIR_REBUILD fixed the database, no errors reported.
DBCC CHECKDB reporting errors again.

You probably have failed or failing disks in an array or some other component of the IO subsystem is broken, or breaking. Personally, I'd want off the problem server ASAP!

Take a tail-log backup.
Restore last known good full backup and diff/log sequence to a different server.
Switch service to the second server.
Get a fresh cuppa and start diagnosis on the problem server.

Addendum: Root cause of the problem:

as it turned out, I found that as we are using Diskeeper, we are (for corporate reasons) still using an old version : 14.0.896. With this version we run into messages like :

The operating system returned error 1784(failed to retrieve text for this error. Reason: 15100) to SQL Server during a write at offset 0x000000010a4000 in file 'D:\Application-Data\MSSQL\Data\<Database>.mdf:MSSQL_DBCC17'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Server <Server>, Line 1
During redoing of a logged operation in database '<Database>', an error occurred at log record ID (2349664:1503:4). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 1823, Level 16, State 1, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Server <Server>, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Server <Server>, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Server <Server>, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details

Found an explanation on Paul Randal's blog here

Link to DiskKeeper Fix found here

Interesting Diskeeper white paper on database defragmentation here