Reduce impact on SQL logs Sharepoint and SQL Server issue as below

When we change setting on SharePoint (Get-SPEnterpriseSearchServiceApplication).QueryLogSettings.CleanupDays = 30

we basically tell SharePoint to purge all data from Search_LinksStore that is older than 30 days.

What it actually does, it starts (after we start respective SPTimerJobs) stored procedure UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup and passes number of days as a parameter, here is manual start example:

EXEC UAT_SP_CF_Search_LinksStore.dbo.proc_MSS_QLog_Cleanup 30;
GO

When this stored procedure runs, it goes trough the tables and performs SELECT and DELETE, but due to enormous number of rows even small reduction on DB has huge impact on sql transaction logs (they generate 200+GB for only 10GB od DB reduction).

In this case, it tried to delete older than 30days but it always hangs due to low disk space on log partition (i even expanded to 400GB ...) and it cannot be done this way.

I will need help of SQL guys and any of SharePoint guys.

Is it possible to create another stored procedure (or modify this one) to have chunks of delete in smaller amount of rows, that way we can reduce impact on sql logs (but it will be performed longer, but i think we can sacrifice time for the sake of log size)?

    SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_MSS_QLog_Cleanup](
    @Days int
)
AS
    DECLARE @TooOld datetime
    SET @TooOld = DATEADD(day, -@Days, GETUTCDATE())
    BEGIN TRANSACTION
    DECLARE @OldPageImpressions table (
        pageImpressionId bigint NOT NULL);
    DECLARE @MinId bigint = (SELECT MIN(pageImpressionId) FROM MSSQLogPageImpressionQuery WHERE searchTime >= @TooOld )
    IF (@MinId IS NOT NULL)
    BEGIN
        DELETE FROM
            MSSQLogPageImpressionQuery
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionResult
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionBlock
        WHERE
            pageImpressionId < @MinId
        DELETE FROM
            MSSQLogPageImpressionQueryRule
        WHERE
            pageImpressionId < @MinId
    END
    ELSE
    BEGIN
        TRUNCATE TABLE MSSQLogPageImpressionQuery
        TRUNCATE TABLE MSSQLogPageImpressionBlock
        TRUNCATE TABLE MSSQLogPageImpressionResult
        TRUNCATE TABLE MSSQLogPageImpressionQueryRule
        TRUNCATE TABLE MSSQLogResultDocs
        TRUNCATE TABLE MSSQLogPageClick
        TRUNCATE TABLE MSSQLogO14PageClick
    END
    DELETE FROM
        MSSQLogPageImpressionQuery
    OUTPUT
        DELETED.pageImpressionId
    INTO
        @OldPageImpressions
    WHERE
        searchTime < @TooOld
    DELETE FROM
        dbo.MSSQLogPageImpressionResult
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionBlock
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogPageImpressionQueryRule
    WHERE
        pageImpressionId IN (SELECT pageImpressionId FROM @OldPageImpressions)
    DELETE FROM
        dbo.MSSQLogResultDocs
    WHERE
        lastReference < @TooOld
    DELETE FROM
        dbo.MSSQLogPageClick
    WHERE
        clickTime < @TooOld
    DELETE FROM
        MSSQLogO14PageClick
    WHERE
        searchTime < @TooOld
    COMMIT TRANSACTION

GO

Please, try to analyze your backup strategies.

Also, You can analyze your DATABASE MODE (FULL, Bulk-Logged, Simple)
(https://msdn.microsoft.com/en-us/library/ms189275.aspx)

Also, you can check log space with:

DBCC SQLPERF(LOGSPACE);
GO

check this: https://msdn.microsoft.com/en-us/library/ms190674.aspx


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

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

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

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

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

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

....

Clustered PK on identity column is good because:

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

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

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

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

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

PK and Clustered index are 2 differences things:

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

Now we end up with 2 questions:

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

It depends on how:

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

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

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

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

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

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

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

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

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

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

A couple of points to consider.

While an index (clustered or not) on a monotonously increasing value saves you page splits during mass inserts, it creates a new hot spot at the tail end of the index. Though it may not be a problem with a single thread bulk insert, it will definitely increase contention for a multithreaded application inserting new tuples at a high rate, as the threads will constantly compete for access to the last page of the index.

Clustering the table based on a surrogate (identity) PK is rarely beneficial. Such a primary key is mostly used to either access individual tuples, one at a time, or scan the entire index for joins. In either case it doesn't matter whether the index is clustered or not (with the exception of merge joins, may be, but how frequent are they?)

I think that you will benefit most from a clustered index that covers queries asking for a key range scan and additional predicates referencing other columns.


Need database of categories for products and services Need to develop a database schema that associates products and services into industry-standard categories.  I know that both the schema and data are out there, yet I'm not finding it.

There are databases that list products (and contain millions of records), but I'm not finding a good database that lists corresponding categories.  For example, individual cell phones might be in a "cell phone" category which, in turn might be in the "consumer electronics" category.  What would be good sources of these types of categories?

Classification is a really tricky thing -- a company that specializes in cell phones might have many categories of cell phones, while ones that don't might stop at a broader category.  Even within a given company, they might have multiple ways of looking at things, and so have multiple classification schemes / taxonomies / thesauri / ontologies / knowledge organization systems in use.

Just like any database, you have to know what sort of questions you're going to ask of your classification scheme, and from there, you can design what's going to fit it best.

A few registries / repositories / lists that I know about :

Taxonomy Warehouse
TaxoBank
JISC Directory of Metadata Vocabularies
TemaTres Vocabulary Server
Controlled Vocabulary Examples

You might also check to see if whatever organizations there are for your industry have any standard classification scheme / thesauri / ontologies / whatever.

Update : I don't know if the Global Product Classification might help or not.

C.Scott,

In the industry stantard, as you said, everything is repertoried somewhere, please find some example here :

Mobile Identification Number(MIN) http://en.wikipedia.org/wiki/Mobile_identification_number
Vehicule Identification Number(VIN) http://en.wikipedia.org/wiki/VIN
International Book Number (ISBN) http://en.wikipedia.org/wiki/ISBN

I hope it can serve you as a start.


Bandwidth comparison between log shipping and transactional replication Which technique uses more network bandwidth:

Log shipping
Transactional Replication

Can some one share any benchmarks for the same ?

What would be the Memory and I/O impact on the Primary server when we use any one of the technique ?

Thanks,

Piyush Patel

It depends on your workload.  As a baseline, use Performance Monitor and log the network counters when not using Log Shipping or Transactional Replication and measure for 1 hour.  Then setup Log Shipping and use Performance Monitor to log the network counters for 1 hour.  Then setup Transactional Replication and use Peformance Monitor to log the network counters for 1 hour.  The network bandwidth that Log Shipping and Transactional Replication consumes is the difference between the collections and the baseline.  The same can be done for memory and I/O.

I have measure the performance using PerfMonitor and outcome of it as below:

           20 docs ||   20 docs ||   200 docs

Log shipping:         9.88 MB ||    10.53 MB ||  28.55 MB

Replication:          6.14 MB ||    6.80 MB ||    17.71 MB

Log Shipping With:    7.86 MB ||    9.66 MB ||    18.17 MB

Compression

So Replication and Log shipping with compression takes almost same network bandwidth. I was measuring these things to know which technique help us to reduce the bandwidth cost from PROD to DR env.

Thanks,

Piyush Patel


Index performance on ON versus WHERE I have two tables

@T1 TABLE
(
    Id INT,
    Date DATETIME
)

@T2 TABLE
(
    Id INT,
    Date DATETIME
)

These tables have a non-clustered index on (Id, Date)

And I join these tables

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
    t1.Id = t2.Id
WHERE
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

This can also be written as

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
    t1.Id = t2.Id
    AND
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

My question is, which of these two queries gives the better performance and why? Or are they equal?

The performance will be the same. The optimizer will recognize this and create the same plan.

On the other hand I wouldn't say they are equal. The first form in the question is far more readable and generally expected.

For an example using some tables I have at hand you can see the execution plan is exactly the same no matter how I write the query.

You should be able to determine the query plans for your own tables and data set so you can see what happens in your situation though.

SELECT * FROM salestable , custtable
WHERE salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable
JOIN  custtable
ON salestable.custaccount = custtable.accountnum
AND salestable.dataareaid = custtable.dataareaid

SELECT * FROM salestable JOIN custtable
ON salestable.custaccount = custtable.accountnum
WHERE salestable.dataareaid = custtable.dataareaid

Gives these execution plans

With INNER JOINs, it is a style issue.

However, it becomes much more interesting with OUTER JOINs. You should explore the differences between queries with OUTER JOINs and conditions in both the ON clause and the WHERE clause. The result-set is not always the same.
Is, for example,

OUTER JOIN dbo.x ON a.ID = x.ID ... WHERE x.SomeField IS NOT NULL

the same as

INNER JOIN dbo.x ON a.ID = x.ID AND x.SomeField IS NOT NULL


Mysql: 1000 select queries vs 1 query ( select .. in ) I have just read about N+1 queries problem. So I have the doubt about the performance of 2 ways.
The way 1: I have 1000 queries like that:

select * from topic where user_id = 1
select * from topic where user_id = 2
...
select * from topic where user_id = 1000

I supposed the topic table has 1.000.000 rows and each query uses binary search, so I think the Big O's way is:

1000 * log2(1.000.000)

The way 2: I have 1 query:
select * from topic where user_id in (1,2,..., 1000)
I don't know the algorithm of 2nd way. So can you explain me the algorithm of (select .. in) query. Is there any case the 1st way is better than 2nd way?

to get the same result, you need to UNION the result and you can't union 10000 selects. the limit is 63 table references per query
Further IN clause has also a limit of elements you can use.

The number of values in the IN() list is only limited by the max_allowed_packet value.

see manual
As you have many ids, you should try
select t1.*
from topic t1
      INNER JOIN
     (SELECT  user_id FROM topic WHERE user_id <= 1000) t2 ON t1.user_id = t2.user_id

On big numbers for the IN you will get faster results.

Linear:
with a(id) as
(values(1),(2),(3))
select t.* from topic t join a on t.id=a.id


Unable to view MySQL db stored procedure We have many stored procedures created with SQL SECURITY DEFINER by user A
Now user B is responsible for maintaining some of the stored procedure. However user B is unable to view/update the stored procedure from workbench since the definer is set to A. Is there an way to give A and B access to the stored procedure at the same time ?

use following command:
GRANT EXECUTE ON dbname.* TO 'user'@'%';


Is a composite index also good for queries on the first field? Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?

Additionally, I created an index on A, but Postgres still uses the composite index for queries on only A. If the previous answer is positive, I guess it doesn't really matter, but why does it select the composite index by default, if the single A index is available?

It certainly is. We discussed that in great detail under this related question:

Working of indexes in PostgreSQL

Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (much less common) 4 bytes on a 32-bit OS. If you are not sure, check pg_controldata. It also depends on data types of indexed columns (some require alignment padding) and actual content.
An index on, say, two integer columns (4 bytes each) typically ends up to be exactly as big as an index on just one, where another 4 bytes are lost to alignment padding.
Update for Postgres 13: The new index deduplication changes this. Repeated index values can be compressed. An index on (a,b) is often less duplicative, so it gains less from deduplication.
If a is a unique column, deduplication doesn't change much. (There can still be duplicative entries due to the MVCC model, only one visible to any transaction at the same time, so adding b can still have a minor effect.)
Apart from that, there is really no downside for the query planner to use an index on (a,b) - compared to an index on just (a). And it is generally preferable for multiple queries to use the same index. The chance for it (or parts of it) to reside in (fast) cache grows when shared.
If you already maintain an index on (a,b), then it doesn't make sense to create another index on just (a) - unless it is substantially smaller. The same is not true for (b,a) vs. (a). Follow the link in the first line for more on that.
Coming from the opposite direction, when you need an additional index like that on (a,b), then consider dropping an existing index on just (a) - if possible. Often not possible as that's the index of a PK or UNIQUE constraint. Since Postgres 11 you might get away with just appending b to the constraint definition with the INCLUDE clause instead. Details in the manual.
Or create the new index on (b,a) instead to cover queries on just b additionally. For only equality conditions the order of index expressions in btree indexes does not matter. It does, though, when involving range conditions. See:

Multicolumn index and performance

There are potential downsides to including additional columns in an index, even if that only uses space otherwise lost to alignment padding:

Whenever the additional column is updated, the index now needs an update, too, which might add cost to write operations and create more index bloat.
HOT updates (Heap Only Tuple) on the table are not possible while any index column is involved.

More on HOT updates:

Redundant data in update statements

How to measure object sizes:

Measure the size of a PostgreSQL table row

It is in the case if you just use just first in the predicate.

It will do scan if you use first columns of composite key and non-key column of composite key.

To trick it you can just dummy predicates like this and then non-key column:

[A,B] is your index, [C] - another column

To utilize index you write as:

SELECT
    A,B,C,D,E
FROM
    test
WHERE
   A=1
AND
   B=B
AND
   C=3

  ...why does it select the composite index by default, if the single A index is available?

It will use index only in the case if there are one or two predicates [A] Or [A],[B]. It will not use it in the order [B],[A] or [A],[C]. To be able to utilize index with additional column [C], you need to enforce index by ordering predicates as [A],[B] and [C].


Set database offline with rollback immediate takes an hour to complete Every night we take our production database offline, detach it, and then re-attach it to sever any active connections before start of our backup procedures. The first command is:

USE master
ALTER DATABASE thedb SET OFFLINE WITH ROLLBACK IMMEDIATE

This command used to take 20 seconds to two minutes earlier to complete which was acceptable. From past few weeks it has been taking 15 minutes to over an hour to complete. There hasn't been any change in database usage that would cause this problem, and it only happens about 3/4 of the time.

As far as I know, we have not changed any settings that would cause this. My understanding was WITH ROLLBACK IMMEDIATE severed all connections and the database goes offline immediately. I have tried investigating the following:

recovery interval is set to 0 so checkpoints happen every few minutes and only take a second to complete
Statistics do not update asynchronously
no large queries are running just before the database goes offline
checking sp_who2 while the database going offline shows no users connected to it
the database's log file is very small, ~30 MB

We are currently running MSSQL Server 2012(Standard Edition) in simple recovery mode with no mirroring. The database is about 300 GB in size.

Any help is appreciated, thank you!

I believe what you are seeing is completely normal.

  Every night we take our production database offline, detach it, and then re-create it to sever any connections as the start of our backup procedures

Why do you do this ?. This is not the correct thing to do always and this is precisely why you are facing the issue. You can take backup of the database when it is online and this is how you should backup SQL Server database. The full backup which you will take when database is online would include all the committed changes done to database when backup finishes. Keeping in single_user and restricted_user mode before the activity may not solve your problem completely.

  This command used to take 20 seconds to two minutes to complete which was acceptable. For the past few weeks it has been taking 15 minutes to over an hour to complete.

You also said that there is no long running query, for that I would say triple check. Alter database requires exclusive access on the database and it may be blocked by some other query, have you checked that.

Did yo checked for any transaction running with begin transaction and may be have missed commit. Run DBCC OPENTRAN() to see for any open transactions. Errorlog would have more information on this

The other thing I see is sometimes when you run alter command there are hardly any queries running which needs rollback and so the command finishes quickly while other days there are some command running which needs to be completely rolled back before database can be made offline. Depending on amount of rollback that needs to be done the time varies.

I would not suggest you to put database in offline mode, I do not see any use in this.

First of all I don't understand why database is kept in Simple recovery mode, if your database is production database then it is highly recommended to keep database in full recovery mode and take differential as well as log backup based on the volume of transaction as well as based on business requirement of your SLA for losing data or recovery point. You can understand more here:

https://www.sqlshack.com/backup-and-restore-sql-server-disaster-recovery/

In case, you are currently taking only full backup in the night and no other backup(Differential backup, as Log backup can't be taken considering recovery mode is Simple), if your database crashes during the day - you will lose all changes made to your database after last successful full backup. So, my first advice would be to change recovery mode and take Differential as well as log backup.

On your point of severing connections before taking backup - its not required at all, you can very much take hot backup and this can be taken when database is in multi-user mode and users are actively working(although backup is advised to run in less traffic hour). And if its hard and fast to close all the connection before backup then you should put your database in single user mode before turning it offline using provided command.

In order to check any active transaction, you may use very famous stored procedure sp_whoisactive from Mr. Adam Machanic, this would give you all the information about running session and many other details which is not feasible to get using sp_who or sp_who2. You can schedule this procedure just before your backup and redirect the output to a table for referring it later.You may also used DBCC OPENTRAN() to check any active transaction as suggested in above answer.

Overall the issue seems to be here with the process which is being followed for taking the backup. Please let us know if this helped.


How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer? The average Input/Output Operations per Second a disk can sustain is a very important factor in the tuning of MySQL / InnoDB databases, as it drives the speed with which Dirty InnoDB Buffer Pool pages are flushed back to the disk.

Is there a method or a formula I can use to estimate the average I/O Operations per Second my RAID 10 disk array can run on?

tl;dr Yes you can do a rough and ready calculation but benchmarking is advisable if you need accuracy. There are many factors beyond raw disk performance that can influence IOPs, in particular the RAID controller.

If you have the technical specifications for the disk, the quick calculation to determine IOPs is: 1 / (average latency + average seek time) = random IOPs.

The contributory factors:

Average latency is the time taken for the disk sector requested to rotate under the drive head.
Average seek time is how long it takes the drive head to locate itself over the disk track requested.
Rotational speed (quoted in revolutions per minute) isn't used directly in the calculation but affects the above. It is also the headline figure quoted in any drive specification.

While there is variation across drive models, you can apply a rough and ready figure to each of the common rotational speeds.

7200 RPM ~75 IOPs
10K RPM ~125 IOPs
15K RPM ~175 IOPs

With the majority of enterprise class RAID controllers, your read performance for common RAID levels (1/10/5/6) will broadly be in line with IOPs * number of disks. Cheaper controllers may impose a penalty. Write performance however is significantly effected by the chosen level:

RAID 1 & 10 = IOPs / 2
RAID 5 = IOPs / 4
RAID 6 = IOPs / 6

For example, a RAID 10 array of 10 15k disks equates to:

(175 * 10) = 1750 IOPs read
(175 * 10) / 2 = 875 IOPs write

Of course, most workloads are a mix of read and write so its necessary to consider the r/w ratio to determine the capacity needed.

Use this calculator:
Disk IOPs calculator

of course YMMV due to controller, caching, IO size, etc.  16KiB for data and 128KiB for log is a good estimate.


Pairwise intersections I have a table with two columns, let's say FirstName and LastName.
I need to get another table, which for every pair of FirstName's from the first one contains a count of the common LastName's.

Is this even feasible to do in SQL?

There are much more unique LastName's than FirstName's, if this affects efficiency of the query.

A toy example, input:

FirstName, LastName
John, Smith
John, Doe
Jane, Doe

Output:

FirstName1, FirstName2, CommonLastNames
John, John, 2
John, Jane, 1
Jane, Jane, 1
Jane, John, 1

As this relation is reflexive and symmetrical, it's OK if the result is just one of the triangles (e.g, the one above the diagonal).

I'm going to use MS SQL Server to do this since I have a copy to hand.  I believe most any of the majors would do it similarly.

First a sample table, with data.  I use a table variable but it's the same for any flavour of table.

declare @t table (FirstName char(10), LastName char(10));

insert @t(FirstName,LastName)
values ('John','Smith'),('John','Doe'),('Jane','Doe');

You can get all pairs by doing a self-join:

select
    a.FirstName, a.LastName, b.FirstName, b.LastName
from @t as a
cross apply @t as b;

Using CROSS APPLY avoids having to jump through hoops to find a join condition for an ON clause.

Next you need something to count.  This is where the CASE statement comes in.  The case returns an integer value per pair of first names, which is what gets counted. (If I'm reading your question correctly you want where the LastNames match so that's the comparison I have.  Hopefully it's obvious how to modify this if I'm wrong.)

select
    ...
    case
        when a.LastName = b.LastName then 1
        else 0
    end
...etc.

Add in a SUM() and GROUP BY and you get your answer:

select
    a.FirstName,
    b.FirstName,
    sum(
    case
        when a.LastName = b.LastName then 1
        else 0
    end
    ) as CommonLastNames
from @t as a
cross apply @t as b
group by a.FirstName, b.FirstName;

That was an interesting challenge.  Using a list of US cities, I came up with this solution (in MySQL):

SELECT  city_a, city_b,
        COUNT(DISTINCT state)
    FROM (
        ( SELECT a.city city_a,
                 b.city city_b,
                 a.state            -- This line differs
            FROM       us a
            CROSS JOIN us b
            WHERE a.state = b.state
              AND a.city != b.city   -- Added (to avoid noise)
              AND a.city < 'M'    -- to speed up test
              AND b.city < 'M'
        )
        UNION ALL
        ( SELECT a.city city_a,
                 b.city city_b,
                 b.state            -- This line differs
            FROM       us a
            CROSS JOIN us b
            WHERE a.state = b.state
              AND a.city != b.city   -- Added (to avoid noise)
              AND a.city < 'M'    -- to speed up test
              AND b.city < 'M'
        )
        ) ab
    GROUP BY 1, 2
    HAVING   COUNT(DISTINCT state) > 1
    ORDER BY COUNT(DISTINCT state) desc

INDEX(state, city) helps with performance.

The results:

+----------+------------+-----------------------+
| city_a   | city_b     | COUNT(DISTINCT state) |
+----------+------------+-----------------------+
| Franklin | Bedford    |                     4 |
| Lebanon  | Franklin   |                     4 |
| Franklin | Lebanon    |                     4 |
| Hudson   | Franklin   |                     4 |
| Columbia | Clinton    |                     4 |
| Clinton  | Columbia   |                     4 |
| Franklin | Hudson     |                     4 |
| Bedford  | Franklin   |                     4 |
| Lebanon  | Farmington |                     3 |
| Hanover  | Kingston   |                     3 |
...
(25.17 sec)

It might have taken 4 times as long to include the entire alphabet.  There were only 4K rows in the table, so this is not a fast task.

"Proof" of the results:
mysql> SELECT city, state FROM us WHERE city IN ('Franklin', 'Bedford');

+----------+-------+
| city     | state |
+----------+-------+
| Bedford  | IN    |
| Franklin | IN    |
| Bedford  | MA    |
| Franklin | MA    |
| Bedford  | NH    |
| Franklin | NH    |
| Bedford  | OH    |
| Franklin | OH    |
| Franklin | TN    |
| Bedford  | TX    |
| Franklin | WI    |
+----------+-------+
11 rows in set (0.00 sec)


Dropped Database Connections in SQL Server 2008R2 We are having an annoying intermittent problem where database connections from our client application on a variety of stations will get dropped.  We have applications that use ODBC, Borland Database Engine, and ADO and all get dropped.  When connections do get dropped, it isn't everyone - some remain connected while others get dropped.  Looking in the logs of one of the applications, I see messages like the following:

TCP Provider: The semaphore timeout period has expired
TCP Provider: An existing connection was forcibly closed by the remote host
Query timeout expired
TCP Provider: No such host is known

I don't see any error messages in the SQL error log or in the Windows events on either the client or the SQL Server.  Is there anyway to determine if this is a database or network issue from the SQL Server side of things?  For example, is there something I can turn on to monitor database connections and get reports when they are dropped?  This happens maybe once or twice a day but not at any specific time (not when backups run or something).

The issue seems to be network related and out of scope for this forum.

It can be SQL related, not just OS and hardware:

https://support.microsoft.com/en-us/kb/2491214


How To Add Days And Time For Instructor Schedule Problem

I need to make a database design (ERD) to display a schedule for every instructor, including the following:

day of course (e.g. Saturday or Monday)
time it will give course (period from 12 PM TO 03 PM)
what course it will give (C# or SQL)

Example

Instructor Martin gives course C#, Monday and Saturday, for period 12 pm - 03 pm, and course SQL on Sunday and Wednesday for period 12 pm - 03 pm.

Details

I designed the following tables:

Instructors table (InstructorID, InstructorName)
Courses table (CourseID, CourseName)
Instructors_courses table (instcourseID, InstructorID, CourseID)

Relationship between Instructors table and Courses table is many to many so I do another table Instructors_Courses.

Questions

How to represent days and times for every course added

Can I add table for time and table for days and make relation with Instructors_courses table by adding day id and time id (one to many)

OR do it programming from user interface.
course start date and course end date these two fields how to represent in table Instructors_courses.

I can added but it will repeated with every course are this correct or what.

Try making the instructor_course table like this

CREATE TABLE instructor_course (
    InstructorCourseID int IDENTITY (1,1) CONSTRAINT pk_instructor_course PRIMARY KEY CLUSTERED NOT NULL,
    DayName char(10) NOT NULL,
    CourseID int CONSTRAINT fk_CourseID_instructor_course REFERENCES Course(CourseID) NOT NULL,
    InstructorID ...
    StartTime time NOT NULL,
    EndTime time NOT NULL,
    StartDate date NOT NULL,
    EndDate date NULL);

Then you could also add some constraint to the table if you want to enforce a rule that two courses can't be timetabled at the same time. If a course has multiple sessions during the week each will be a row in this table.

How about doing something like this:

CREATE TABLE [dbo].[ScheduledCourses]
(
    [Course] INT NOT NULL REFERENCES [dbo].[Course]([Id]),
    [Instructor] INT NOT NULL REFERENCES [dbo].[Instructor]([Id]),
    [StartOn] DATETIME2(0) NOT NULL,
    [EndOn] DATETIME2(0) NOT NULL,
    PRIMARY KEY ([Course],[Instructor],[StartOn],[EndOn])
);

Now you need to ensure that not only do the start and end dates for a course and instructor not be the same, you need to ensure that they don't overlap as well. For instance I could do this:

INSERT [dbo].[ScheduleCourses]
VALUES (1,1,'01/01/2016 12:00:00', '01/01/2016 15:00:00');
INSERT [dbo].[ScheduleCourses]
VALUES (1,1,'01/01/2016 13:00:00', '01/01/2016 16:00:00');

And as per the normal key rules, it would be valid - but logically it is not. They may not have the same start and end date and time but they do overlap - to get around this you can implement a CHECK constraint to enforce the logic:

CREATE FUNCTION [dbo].[CheckScheduledCourseDoesNotOverlap](
    @course INT,
    @instructor INT,
    @startOn DATETIME2(0),
    @endOn DATETIME(0)
)
RETURNS BIT
AS
BEGIN
DECLARE @overlaps BIT = 0;
IF EXISTS(SELECT * FROM [dbo].[ScheduledCourses]
           WHERE [Course] = @course
             AND [Instructor] = @instructor
             AND(@startOn BETWEEN [StartOn] AND [EndOn]
              OR @endOn BETWEEN [StartOn] AND [EndOn]))
BEGIN
    SET @overlap = 1;
END
RETURN @overlap;
END
GO

This function checks that a new record does not overlap and existing record. Then, you can apply a check constraint to your table like so:

ALTER TABLE [dbo].[ScheduledCourses]
ADD CONSTRAINT [CK_ScheduledCourses]
CHECK ([dbo].[CheckScheduledCourseDoesNotOverlap]([Course],[Instructor],[StartOn],[EndOn]);
GO

This should meet your requirements. I wouldn't personally store the day name within the table, instead I would use the DATEPART function to return it in the query instead.

I haven't had chance to test the code and this is just off the top of my head so be aware when you give it a go.


Displaying the list of all tables in all database How can I display a list containing all the tables of all the database in a SQL Server 2008 R2 server?

You need a query for each database against sys.tables.

select 'master' as DatabaseName,
       T.name collate database_default as TableName
from master.sys.tables as T
union all
select 'tempdb' as DatabaseName,
       T.name collate database_default as TableName
from tempdb.sys.tables as T
union all
select 'model' as DatabaseName,
       T.name collate database_default as TableName
from model.sys.tables as T
union all
select 'msdb' as DatabaseName,
       T.name collate database_default as TableName
from msdb.sys.tables as T

You can use sys.databases to build and execute the query dynamically.

declare @SQL nvarchar(max)

set @SQL = (select 'union all
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
exec (@SQL)

Just enter:

SELECT db_name(), name FROM sys.tables;

Copy and paste the list.


How to find the columns and tables used in a stored procedure (SQL Server) I want to find all Tables alongside the columns used in a special Stored Procedure?

Can anyone help me here?

By the code below I found the tables but I want columns too

SELECT DISTINCT
o.id ,
o.name AS 'Procedure_Name' ,
oo.name AS 'Table_Name' ,
d.depid FROM
sysdepends d ,
sysobjects o ,
sysobjects oo WHERE
o.id = d.id AND
oo.id = d.depid ORDER BY
o.name ,
oo.name;

Update: You can get the information from a non-deprecated dynamic management function rather then the older dynamic management view, as pointed out by dfundako in the comments below. This would be the recommended approach as the function is available in all currently supported SQL Server releases and likely to be so in at least the next few releases too.

To update my previous example:

CREATE PROCEDURE test_depends
AS BEGIN
    SELECT TOP 5 ou_id, prop_text FROM tr_n_ou WHERE prop_name = 'display_name'
    SELECT * FROM tr_n_org WHERE parent_display_name = 'Global'
    EXEC some_stored_procedure
END
GO

SELECT ISNULL(referenced_schema_name+'.','')+referenced_entity_name+ISNULL('.'+referenced_minor_name,'') AS Dependency
     , ISNULL(so.type_desc, sn.type_desc) AS DependencyType
     , CASE WHEN ISNULL(so.type_desc, sn.type_desc) IN ('USER_TABLE', 'VIEW')
            AND referenced_minor_name IS NOT NULL
            THEN 'Column in '
            ELSE ''
            END
     + ISNULL(so.type_desc, sn.type_desc) AS DependencySubType
FROM sys.dm_sql_referenced_entities('dbo.test_depends', 'OBJECT') sr
LEFT OUTER JOIN
     sys.objects so
     ON  so.object_id = sr.referenced_id
LEFT OUTER JOIN
     sys.objects sn
     ON  sn.name = sr.referenced_entity_name
     AND sr.referenced_id IS NULL

GO
DROP PROCEDURE test_depends

The caveats about ad-hoc SQL still apply.

Also if a procedure references something that is user schema dependent the referenced ID will be NULL in the DMF which is why I'm doing the extra lookup by name in this example. If the EXEC line specified a schema (i.e. EXEC some_schema.some_procedure) then the ID would have a value so the ID lookup would work, otherwise there could be several procedures with the same name in different schemas and the one called will depend on run-type details. Note also that the name lookup will return multiple rows for the same dependency in this case if there are multiple procedures of the same name.

________________________________________

Previous answer:

You can get this information, mostly, from sys.sql_dependencies but be aware that this is marked for deprecation so may not be available in future versions of SQL Server.

To test in one of your databases do something like:

CREATE PROCEDURE test_depends
AS BEGIN
    SELECT TOP 5 column1, column2 FROM some_table WHERE column3 = 'some value'
    SELECT * FROM another_table
    EXEC some_stored_procdure
END
GO

SELECT sd.*, so.name, sc.name
FROM   sys.sql_dependencies sd
LEFT OUTER JOIN
       sys.objects so
       ON  so.object_id = sd.referenced_major_id
LEFT OUTER JOIN
       sys.columns sc
       ON  sc.object_id = sd.referenced_major_id
       AND sc.column_id = sd.referenced_minor_id
WHERE sd.object_id = (SELECT object_id FROM sys.objects WHERE name = 'test_depends')

GO
DROP PROCEDURE test_depends

You will see that is correctly lists the individual columns for the first table the procedure references, all columns in the second table as that selects *, and the procedure it calls.

Note that this does not deference views so you need a recursive query to get down to the base tables if that is your requirement, and you'll need to dig further too if a procedure calls other procedures.

Note again that sys.sql_dependencies is flagged as deprecated. I ran the above on a 2008r2 instance, the documentation on MSDN seems to list it only as far as 2014 so it may not be present in 2016 (I don't have access to any instances above 2012 in my current location in order to test this) though Kris notes in comments below that the view is still present in 2016. The replacement (sys.sql_expression_dependencies) doesn't list individual columns, at least in my test on a 2008r2 instance (the is only one entry per table with the minor ID 0 is both cases).

Another important thing to consider is that this view can't contain any references made by ad-hoc SQL run via EXEC (<string>) if you have such evilness in your procedures.

If you find tables name so you can find theirs columns name by following query:

SELECT *
FROM Your_DB_Name.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Your_Table_name'


Does SQL Server Only Perform Calculations In A SELECT List Once? Take the following example:

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

Would CalculationA and CalculationB, each be calculated twice?
Or would the optimizer be clever enough to calculate them once and use the result twice?

I would like to perform a test to see the result for myself, however, I am not sure how I could check something like this.

My assumption is that it would perform the calculation twice.
In which case, depending upon the calculations involved, might it be better to use a derived table, or nested view? Consider the following:

SELECT TableB.ColA,
       TableB.ColB,
       TableB.ColA + TableB.ColB AS ColC,
FROM(
      SELECT <CalculationA> As ColA,
             <CalculationB> As ColB
      FROM TableA
    ) As TableB

In this case, I would hope that the calculations would only be performed once?

Please can someone confirm or refute my assumptions?
Or instruct me on how to test something like this for myself?

Thanks.

Most of the information you will need is going to be in the execution plan ( and the plan XML).

Take this query:

SELECT COUNT(val) As ColA,
       COUNT(val2) As ColB,
       COUNT(val) +  COUNT(val2) As ColC
FROM dbo.TableA;

The execution plan  (opened with sentryone plan explorer) shows what steps it went through:

With the stream aggregate aggregating the values for EXPR1005 & EXPR1006

If we want to know what these are, we could get the exact info on these expressions from the query plan XML:

<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="COUNT([Database].[dbo].[TableA].[val])">
<Aggregate AggType="COUNT_BIG" Distinct="false">

With the first compute scalar computing ColA & ColB:

And the last compute scalar being a simple addition:

This is reading it as the data flows, in theory you should be reading it from left to right if going over the logical execution.

In that case, EXPR1004  is calling the other expressions, EXPR1002 & EXPR1003. In turn these are calling EXPR1005 & EXPR1006.

  Would CalculationA and CalculationB, each be calculated twice? Or
  would the optimizer be clever enough to calculate them once and use
  the result twice?

Previous tests show that in this case ColC is simplified as an addition of the calculations that are defined as ColA & ColB.

As a result, ColA & ColB are only calculated once.

Grouping by 200 distinct values

If we are grouping by 200 distinct values (val3) the same is shown:

SET STATISTICS IO, TIME ON;
SELECT SUM(val) As ColA,
       SUM(val2) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA
GROUP BY val3;

Aggregating down to these 200 distinct values in val3

performing the sums on val & val2 and then adding them together for ColC:

Even if we are grouping on all but one non-unique value, the same addition should be seen for the compute scalar.

Adding a function to  ColA & ColB

Even if we change the query to this:

SET STATISTICS IO, TIME ON;
SELECT ABS(SUM(val)) As ColA,
       ABS(SUM(val2)) As ColB,
       SUM(val) +  SUM(val2) As ColC
FROM dbo.TableA

The aggregations are still not going to be calculated twice, we are simply adding the ABS() function to the resultset of the aggregation, wich is one row:

Ofcourse, running SUM(ABS(ColA) & SUM(ABS(ColB)) will make the optimizer unable to use the same expression for calculating ColC.

If you want to go deeper in when this happens I would nudge you towards Query Optimizer Deep Dive - Part 1 (until Part 4) by Paul White.

Anther way to dive deeper into query execution phases is by adding these hints:

OPTION
(
    RECOMPILE,
    QUERYTRACEON 3604,
    QUERYTRACEON 8605
);

This will expose the input tree as created by the optimizer.

The adding of the two previous calculated values to get ColC is then translated to:

AncOp_PrjEl COL: Expr1004

ScaOp_Arithmetic x_aopAdd

    ScaOp_Identifier COL: Expr1002

    ScaOp_Identifier COL: Expr1003

This information is already present in the Input Tree, even before the simplification phase has taken place, showing that the optimizer immediately knows that it does not have to perform the same calculation twice.

If both Expressions are deterministic, the optimizer will reuse the previously calculated results and simply add them. If either expression is nondeterministic, that expression MUST be recalculated.


Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows? I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction.

As per the calculations, several thousand transactions per day and this table would hit few million entires withing next few months.

1) When querying and writing data, would server perform very slow?

2) Is there any best way to handle the situation?

I have seen a billion-row table hum along fine.

I have seen a thousand-row table cause the bones of the server to creak and groan.

It all depends on the queries.

"several thousand transactions per day" -- No Problem.  "several thousand transactions per second" -- This gets somewhat challenging, but probably doable.

What to do?

Provide SHOW CREATE TABLE
Provide the main queries.
Provide the main transactions.
Don't do COUNT(*)
Don't do anything that requires hitting all 50M rows -- ask for help in reformulating such queries.

If this is really a financial institution, be sure to hire a security consultant.  You do not want the hassle you will get if the data is lost/stolen.

The obvious answer is: COUNT(*). There is no optimisation for this, it just counts all entries in the primary key.

Of course anything requiring a full table scan or a full index scan will be really slow.

Optimised queries should not be slow. Slower? Yes, because indexes will be quite big. You can always partition the table, and your queries will read smaller portions of indexes (but don't expect any type of parallelisation).

Of course to be sure about the performance you should run a test. If you have the applications queries written in a binary log or slow log, replay the log with some tool or a custom script. If you don't have, write some realistic queries and make a test with sysbench (some trivial lines of lua language are required).


Displaying user's password I need to know if there is a way in SQL*Plus to display the password of a certain username?

At my job, sometimes the developers ask me what happened to their account, and what their password is. When I try to display the username and password, the password shows as blank.

Could you please help?

What you are asking is impossible, unless you brute-force the password hash for the user.

How do you expect a system to be secure if passwords are stored in plain text?

You can user this:

SELECT name,
       ctime,           -- create time
       ptime            -- change
FROM   sys.user$
WHERE  name = '<USER NAME>';

Of course there is no (easy) way to decrypt Oracle passwords.


How to backup Sharepoint databases I am responsible for backups of normal DBs, but SharePoint is handled by others. Bit worried that we miss something here.

Is SharePoint backup handled completely through some tool in SharePoint, or should I also make regular SQL Backups of the databases outside SharePoint?

Open SQL Server Management Studio and connect to the database server. Right-click the database that you want to back up, point to Tasks, and then click Back Up.


Active Directory authentication with SQL Server on Linux containers I have  a problem and hope someone can help me. I have SQL Server as docker container. I want our developers able to login in SSMS (SQL Server Management Studio) using their Active Directory users. I followed this procedures which was Microsoft Doc page but it didn't work. The log file in container shows about error:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: 192.168.1.52]
Error: 17806, Severity: 20, State: 14.
SSPI handshake failed with error code 0x80090304, state 14 while establishing a connection with integrated security; the connection has been closed.
Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The Local Security Authority cannot be contacted [CLIENT: 192.168.1.52]

My container run successfully and I can connect to it using sa user but, I can't login using Windows Authentication.
This is my docker run command:
# docker run -itd -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<pass>" -p 1433:1433 --name msdb \
--user root \
-v /root/mssql:/var/opt/mssql/ \
-v /root/mssql/krb5.conf:/etc/krb5.conf \
--dns-search <full domain> \
--dns 192.168.1.49 \
--add-host <dc.mydomain>:192.168.1.49 \
--add-host <mydomain>:192.168.1.49 \
--add-host <my domain.com>:192.168.1.49 mcr.microsoft.com/mssql/server

Also, I checked this checklists but the problem didn't resolve and I got the same error again.
I hope some one can help me how can I resolve it.
Thanks

@Martin Cairry, Yes, client is windows 10 and its joined the domain, ssms verion is 18.5. all users have domain admin privilege. Also when I login to ssms with sa user, I can add my domain user in login section but it can't login using windows authentication.


How to find the date a login password was last changed in SQL Server 2008? I'm porting stored procedures from Sybase 15 to SQL Server 2008.

In Sybase this statement calcs an expiration date by adding a number of days to syslogins.pwdate (Date the password was last changed) and works fine:

SELECT @l_pwd_date = dateadd( day, @l_pwd_max_expire, pwdate)
FROM master.dbo.syslogins
WHERE name = @v_user

...but throws this error when trying to compile it in SQL Server 2008:

  Msg 207, Level 16, State 1, Line 21 Invalid column name 'pwdate'.

syslogins in SQL Server 2008 no longer includes that column pwdate.

Does anyone know where could I find equivalent column in SQL Server 2008 or a workaround to find the date a login password was last changed?

Use the PasswordLastSetTime option of the LOGINPROPERTY.

SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');

select name, create_date from sys.sql_logins


Permission denied with Copy command when using network share I am running the following Copy command via npgsql (.Net) from a client on the network:

COPY work_narcra00_2017_q2.a2_aevent FROM '//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp' WITH CSV

I am getting this error:

ERROR: could not open file "//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp" for reading: Permission denied
SQL state: 42501

I also tried the command from PgAdmin III and I get the same error.

The obvious answer is the file/folder permissions, but both the share and NTFS permissions have read/write permissions to Everyone.

Another post suggested adding permissions for Network Service but that makes little sense for accessing a network share because I cannot add the DBServer/NetworkService account to the client computer's share (though I tried adding the local Network Service account just in case)

Moving the file to a local folder on the server of course works, but that is not a viable long-term option right now.

Edit: I can see and edit the file just fine from within Windows using Notepad.

With COPY from a named file, the file is read on the server by the user who owns the database cluster (usually a service account) not on the client.  So the server (both machine and account) is what needs to have access to it. If your file is readable on the client, you can use COPY...FROM STDIN in conjunction with the support functions described here.

Postgres COPY TO command works correctly if we specify the file name as shown below.
copy (SELECT ROW_TO_JSON(t) FROM (SELECT * FROM film) t) TO 'C:/Users/xyz/abc/data.xls'


SQL Server performance issue (CXPACKET wait type) I have a performance issue with production SQL Server 2012 which has 24 cores and 32 GB of RAM. From wait type I can see CXPACKET is on the top based on the following SQL Server metrics, should I reduce MAXDOP or "cost Threshold for Parallelism" to avoid more parallelism and reduce CXPACKET wait type?

Any help would be appreciated.

General consensus seems to tend torwards setting Cost Threshold for Parallelism to 50 and go from there.
Why Cost Threshold For Parallelism Shouldn’t Be Set To 5

This even includes official reccomendations from VMWare regarding SQL Server Setup with vSphere. Section 4.5 of the Guide "Architecting Microsoft SQL Server on VMware vSphere®" states:

  The default value is 5 which is usually considered too low. [...] The recommendation instead is to increase the CTFP value from 5 seconds to approximately 50 seconds to make sure only large queries run in parallel. Set the MAXDOP according to Microsoft’s recommendation for the number of cores in the VM’s NUMA node (no more than 8).

The usage of of the unit "seconds" should be taken with a grain of salt because on modern systems and versions of SQL Server it will not reflect the actual duration in seconds.

Regarding MAXDOP there is a comprehensive guide found here: Configure the max degree of parallelism

So in your case I would start with the following settings and test them out:

Cost Threshold for Parallelism: 50

Maximum Degree of Parallelism: 8


Point in time recovery from the same database - what does it mean? I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short.

SQL Server allows me to restore to point in time in the past when restoring from database to itself. Does this even make sense? When I tried it did not yield expected results. Why is this even allowed if it doesn’t work? Can it have any practical purposes?
When playing with the LDF files I discovered a curiosity: Consider having a database in simple mode. Then I switch it to full recovery mode. Then I insert some 10000 rows into the database, check the LDF and find out that the LDF file size did not increase. When I take a full backup and then insert 10000 rows again into the database only then the LDF file increases. Is this some sort of optimization? NOTE: I had LDF file shrinked before both operations.

Thanks for all explanations.

The process of restoring a database only ever goes forward in time, never backward 1. So restoring a database to a point in time involves starting from a full backup, and then applying transaction log backups up to/containing the point in time to which you wish to restore.

The idea is that the ability exists to revert the database to a specific state that existed previously. This could be required if the entire database is lost (disaster) and it has to be reconstituted from nothing but the backups; or, "something bad" could have happened, either maliciously, or due to corruption, and it has to be restored in-place.

If you're having issues with getting this to work, we'll need more information about how you're going about doing it.
As mentioned in the comments, a database in FULL or BULK_LOGGED remains in SIMPLE mode internally until a full backup is taken, which gives a "base" from which the database can be restored. I've seen this called "pseudo-simple" mode.

The behaviour you saw with regards to the log file size was likely due to the above, although it depends on how the rows were INSERTed. In SIMPLE (and pseudo-simple) recovery, if the log file starts getting full, a CHECKPOINT will be initiated, which will internally free space in the log file as part of the process. So if the INSERTs were done in separate transactions, this would not grow the log. Doing all the INSERTs in a single transaction would grow the log, as the log records required to roll back the whole transaction cannot be cleared until the transaction commits or rolls back.

1 Not counting things like restoring in STANDBY mode, crash recovery, etc.

Point in time restore does work.  If you have a perfectly good database at 11pm and a bunch of bad changes are made at 11.10pm, then a log backup is taken at 11:15pm you can restore you database to the point in time before the 11.10pm changes using your full/diff backup plus the log backup using a time parameter.
Don't worry about LDF file sizes unless their size becomes a space issue - just concentrate on making sure you have good backups.


Powershell Script Error Connecting To SQL Server Problem
I have a PowerShell script that pulls user information (Domain and/or SQL users, their groups and other information) and spits it out to an Excel/csv file. This script worked in my virtual lab environment; however, when executed from client issued laptop or when I remote directly into server I get the following error:

Error connecting to server  [Server Name]

Notes
I have also gone into SSMS and opened PowerShell directly from within and get same error.
I mentioned my virtual lab environment. That is a virtual server cluster I have on my personal laptop. When I execute the script on my virtual environment it executes fine and spits out the Excel file. This environment is a 2 node cluster using Windows Server 2012. My SQL Server is the 2012 Developer version. Powershell version 4.0.
Environments
Client environment varies. On desktop using Windows 7, Powershell 5.0 and SQL Server 2014. The particular server I am connecting to is Windows 2008 R2 and SQL Server 2008 R2 and Powershell 4.0
Script
    clear
Write-Host -ForegroundColor Green "Starting"

$MaximumErrorCount = 15000
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

$today=(get-date -f yyyyMMdd-HHmmss)

#a list of instances you want to send the query to
$instances = Get-Content "C:\Users\Jimmy.Johns\Desktop\Instance_list\instance.txt"
$query = 'TSQL. Left out full code for space/formatting'
$databasename = "master"
$instances |
ForEach-Object {
try {
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
     Invoke-Sqlcmd -ServerInstance $_ -Database $databasename -Query $query -QueryTimeout 15   | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today.csv
} Catch {
    $_ | Out-Null
    "Error connecting to server  $server" | Export-Csv -Append -Path C:\Users\Jimmy.John\Desktop\Output\ListPermisions-$today--Errors.csv
    Write-host "Error connecting to server  $server"
 }
}

Write-Host -ForegroundColor Green "Done"

I have read http://www.sqlshack.com/connecting-powershell-to-sql-server/ and used connection string and still will not connect.
Update
After changing $_|out-null with $error[0].exception and running from the client desktop I got the following error:

Could not file or assembly 'Microsoft.SQLServer.BatchParser, Version= 12.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system could not load the file specified.

I also got this error when I ran the edited script inside the server.

Import-Module : The specified module 'ActiveDirectory' was not loaded because no valid module file was found in any module directory.

This particular issue I was able to resolve. I went and installed Active Directory Module Feature on the server thus removing the Import-Module- Active Directory error.

Even though I already had Microsoft.SQLServer.BatchParser assembly installed on the client desktop, I had read a few posts on StackOverflow how the reinstalled SSMS and it worked. So I re-installed SSMS 2014 with SP 2 and it still gave me the same error. My Processor Architecture was x86. So I also installed SQL Server 2014 SMO for 64-bit on my client issued desktop. I have been able to connect to the server and got the scripts running.


Leading zeros are truncated when I spool to CSV I have spooled a file to CSV using sqlplus. Everything works great but there is one  issue. I have mobile numbers which start with 0, for example 04589 and 02586.

After the spooling is complete, 0s are truncated and the end results are 4589 and 2586.

I want the output to display in CSV as:

04589
02586

String are written between "" in the CSV. So your datatype is not string type, or the tool you use for viewing the CSV automatically converts and trims it.

[oracle@o71 ~]$ sqlplus -S bp/bp
set markup csv on
set echo off
spool 1.csv
select '04589' as phone from dual
union all select '02586' from dual;

"PHONE"
"04589"
"02586"

exit
[oracle@o71 ~]$ cat 1.csv

"PHONE"
"04589"
"02586"

[oracle@o71 ~]$

In Excel:  Data>Import from CSV (rather than just double-clicking the CSV file to open it).


Why are there execution plan differences between OFFSET ... FETCH and the old-style ROW_NUMBER scheme? The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very common?

One would assume that the optimizer recognizes both and optimizes them (trivially) to the fullest.

Here is a very simple case where OFFSET ... FETCH is ~2x faster according to the cost estimate.

SELECT * INTO #objects FROM sys.objects

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #objects
) x
WHERE r >= 30 AND r < (30 + 10)
    ORDER BY object_id

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

One can vary this test case by creating a CI on object_id or adding filters but it is impossible to remove all plan differences. OFFSET ... FETCH is always faster because it does less work at execution time.

The examples in the question do not quite produce the same results (the OFFSET example has an off-by-one error).  The updated forms below fix that issue, remove the extra sort for the ROW_NUMBER case, and use variables to make the solution more general:

DECLARE
    @PageSize bigint = 10,
    @PageNumber integer = 3;

WITH Numbered AS
(
    SELECT TOP ((@PageNumber + 1) * @PageSize)
        o.*,
        rn = ROW_NUMBER() OVER (
            ORDER BY o.[object_id])
    FROM #objects AS o
    ORDER BY
        o.[object_id]
)
SELECT
    x.name,
    x.[object_id],
    x.principal_id,
    x.[schema_id],
    x.parent_object_id,
    x.[type],
    x.type_desc,
    x.create_date,
    x.modify_date,
    x.is_ms_shipped,
    x.is_published,
    x.is_schema_published
FROM Numbered AS x
WHERE
    x.rn >= @PageNumber * @PageSize
    AND x.rn < ((@PageNumber + 1) * @PageSize)
ORDER BY
    x.[object_id];

SELECT
    o.name,
    o.[object_id],
    o.principal_id,
    o.[schema_id],
    o.parent_object_id,
    o.[type],
    o.type_desc,
    o.create_date,
    o.modify_date,
    o.is_ms_shipped,
    o.is_published,
    o.is_schema_published
FROM #objects AS o
ORDER BY
    o.[object_id]
    OFFSET @PageNumber * @PageSize - 1 ROWS
    FETCH NEXT @PageSize ROWS ONLY;

The ROW_NUMBER plan has an estimated cost of 0.0197935:

The OFFSET plan has an estimated cost of 0.0196955:

That is a saving of 0.000098 estimated cost units (though the OFFSET plan would require extra operators if you want to return a row number for each row).  The OFFSET plan will still be slightly cheaper, generally speaking, but do remember that estimated costs are exactly that - real testing is still required.  The bulk of the cost in both plans is the cost of the full sort of the input set, so helpful indexes would benefit both solutions.

Where constant literal values are used (e.g. OFFSET 30 in the original example) the optimizer can use a TopN Sort instead of a full sort followed by a Top.  When the rows needed from the TopN Sort is a constant literal and <= 100 (the sum of OFFSET and FETCH) the execution engine can use a different sort algorithm which can perform faster than generalized TopN sort.  All three cases have different performance characteristics overall.

As to why the optimizer does not automatically transform the ROW_NUMBER syntax pattern to use OFFSET, there are a number of reasons:

It's almost impossible to write a transform that would match all existing uses
Having some paging queries automatically transformed and not others could be confusing
The OFFSET plan is not guaranteed to be better in all cases

One example for the third point above occurs where the paging set is quite wide.  It can be much more efficient to seek the keys needed using a nonclustered index and manually lookup against the clustered index compared with scanning the index with OFFSET or ROW_NUMBER.  There are additional issues to consider if the paging application needs to know how many rows or pages there are in total.  There is another good discussion of the relative merits of the 'key seek' and 'offset' methods here.

Overall, it is probably better that people make an informed decision to change their paging queries to use OFFSET, if appropriate, after thorough testing.

They modified the query optimizer to add this feature in.  Meaning they implemented mechanisms specifically to support the offset ... fetch command. In other words for the top query SQL Server has to do a lot more work.  Thus the difference in query plans.


SQL Server: Simple database restore with overwrite What I'm trying to do is backup one of our production databases and restore them as our dev database (overwriting what's been there).

That's how I back up:

backup database [authfx] to disk = N'f:\db_backups\authfx\authfx-latest.bak'
  with
    noformat,
    init,
    name = N'authfx Latest Full Database Backup',
    skip,
    norewind,
    nounload,
    stats = 1;

Now when I try to restore other database using

restore database [dev-authfx] from disk = N'f:\db_backups\authfx\authfx-latest.bak'
  with
    file = 1,
    nounload,
    replace,
    stats = 1;

it barks all over the place:

Msg 1834, Level 16, State 1, Line 10
The file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf' cannot be overwritten.  It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 10
The file 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf' cannot be overwritten.  It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx_log' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 10
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.

I understand that I need to add with move and specify where original data and log files go for the dev database, but is there any way I can omit this and just say: restore this database from this other backup, do not touch anything else and overwrite what's been here previously.

Unfortunately not. SQL Server will always attempt to restore to exactly what is recorded in the backup file, unless you explicitly specify otherwise (with MOVE in this case).

The REPLACE option only works if you're replacing files owned by the database you're restoring (which makes sense, You may have said you're fine killing DB_A by restoring over it, but SQL doesn't really know how you feel about killing DB_B as well).

If this is something you'll be doing regularly then, then write a small sql script with everything stated, save it and use that every time you want to do the restore. This is how I do our overnight restores for dev/UAT/Training from prod.

RESTORE DATABASE DB_NAME ... WITH RECOVERY, REPLACE;

-- will restore your "DB"(overwrite) ALWAYS!

Do not listen for "DB_A & DB_B as well". "DB" will be your restored database name.
Use RECOVERY for the state "ON-LINE" and REPLACE for overwriting existing database files.
Use MOVE for the different file location (different from original one). No other case.
No other options use! Any "SKIP, NOREWIND, NOUNLOAD" is no need. It's for tapes only.
All your mistake was in the same old_physical_file_names for the "new_physical_files" which really belongs to another database (child mistake for admins to multiple DB cloning on the same resource).
Make every time new SUBFOLDER for cloning DB and take a patience at all.
Or use MOVE option.

Good luck.


multi valued attribute as primary key I am designing a database system for my current project and got this situation.
I am having a functional dependency as

  D -> E

Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination. And Unique combination of D will define unique value of E. So i am interested in knowing what is the best way of achieving this requirement.

Just to be clear, you can have multi-value primary keys. But PRIMARY KEY means all values in the composite key are NOT NULL.

CREATE TABLE d ( i int, j int, k int, PRIMARY KEY (i,j,k) );

INSERT INTO d (i,j,k) VALUES (1,2,null);
ERROR:  null value in column "k" violates not-null constraint
DETAIL:  Failing row contains (1, 2, null).

So, you must be unique on (i,j,k). That makes sense. This can provide a linear map to E in your example

  Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination.

That's where you've lost me. That doesn't make sense. If they're all NOT NULL the only combination that matters is (i,j,k)

If you want them such that reordering is also not allowed, for instance. Such that these are rejected..

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
INSERT INTO d (i,j,k) VALUES (1,2,3), (2,1,3);

We can do that too using an EXCLUDE constraint and sort with an array.

CREATE TABLE d (
  i int,
  j int,
  k int,
  PRIMARY KEY (i,j,k),
  EXCLUDE (sort(ARRAY[i,j,k]) WITH =)
);

Now we get our error.

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
ERROR:  conflicting key value violates exclusion constraint "d_sort_excl"
DETAIL:  Key (sort(ARRAY[i, j, k]))=({1,2,3}) conflicts with existing key (sort(ARRAY[i, j, k]))=({1,2,3}).

First of all, you forgot to mention the requirement itself.

Multivalued attributes cannot work as primary keys. Actually, multivalued attributes violate First Normal Form. So the good idea is to create a separate table and push multivalued attribute into it. Use fictional primary key (i.e. an integer number) for your main table and link each set of D values to the necessary record.

Look here for the example.

BTW, if any unique combination of D will define unique value of E, you can use E as primary key.


adding node to existing availability group Currently have a 2 node availability group, trying to add a 3rd.

Added Node3 to the Windows Cluster. Node3 appears as UP in "Nodes" on the Failover Cluster Manager console. However I have the following message on Cluster Events:

    Cluster network name resource 'CAUBC-CLamf' encountered an error enabling the network name on this node. The reason for the failure
    was:   'Unable to obtain a logon token'.

  The error code was '1326'.

  You may take the network name resource offline and online again to
  retry.

Notably, the Cluster Core Resources console doesn't have the IP address for the new cluster node.

Another source of confusion here is found in the cluster log file:

  Logon failed for user CAUBC-CLamf$

Where is this user coming from?

EDIT: despite this error, I have been able to add the new node to the availability group and automatically seed the databases. I don't get why the error persists, however.

In addition, only the new node's ip address has registered in DNS - the listener/cluster resource ips are not in DNS

Is it necessary to add the new node's secondary ip to the Cluster Core Resources, in a multi-subnet setup?

I think this is normal behavior as only one cluster IP will be up at one time. When trying to force the secondary or third IP up you should get the error message you stated.


Can we copy data from 1 text file to 2 postgresql tables? I have a text file with some data and I have two similar tables in my postgresql database. Is it possible to copy a part of the data present in the file, say first 10 lines, to the first table and the remaining in the second table?

The SQL COPY command itself has no provision for that. But you can specify PROGRAM as input. The manual:

  When PROGRAM is specified, the server executes the given command and
  reads from the standard output of the program, or writes to the
  standard input of the program. The command must be specified from the
  viewpoint of the server, and be executable by the PostgreSQL user.

Then, assuming you are on a Linux system, you can COPY the first 10 lines with:

COPY tbl1 FROM PROGRAM 'head -n10 /path/to/file/my.csv';

To COPY the rest, starting with line 11:

COPY tbl2 FROM PROGRAM 'sed -n 11,\$p /path/to/file/my.csv';

Or, to COPY without comments with leading # / only comments (asked in comment):

COPY tbl3 FROM PROGRAM 'awk ''!/^#/'' /path/to/file/my.csv';
COPY tbl4 FROM PROGRAM 'awk ''/#/ { sub(/^#/, ""); print }'' /path/to/file/my.csv';

The last command removes the leading # from comments (but not additional whitespace).

Consult the man page or your preferred online source about the shell commands head, sed and awk ...

As always, you need the necessary privileges. Consider the psql meta command \copy if you are not superuser nor member of the pg_execute_server_program role.

Related:

Using Postgres COPY TO for data export without overwriting file


Index Maintenance on Indexed View using Ola Hallengren Script I’m using Ola Hallengren's below script in my environment and it fails with the error:

USE DBA
EXECUTE dbo.IndexOptimize
    @Databases = ‘USER_DATABASES’,
    @FragmentationLow = NULL,
    @FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = ‘ALL’,
    @PageCountLevel = 0 ,
    @LogToTable = ‘N’

  Msg 50000, Sev 16, State 1, Line 153 : Msg 2706, Table ‘_abcd’ does not exist. [SQLSTATE 42000]

‘_abcd’ actually is a view and the stored procedure is unable to update statistics on view and it fails.
we've indexes on this view
Please guide me how can I solve this issue

I run Ola's scripts from SQL Agent, and the jobs that use them are owned by an ID that has sysadmin server role.  You could also ask Ola for his thoughts.

Havent really tried to debug the script, but i can see just from the parameters what it essentially does.
There are many scripts online that you can use to do just that
The one that i use pretty often is this:

DECLARE @MaxFragmentation TINYINT=30
,@MinimumPages SMALLINT=1000
,@SQL nvarchar(max)
,@ObjectName NVARCHAR(300)
,@IndexName NVARCHAR(300)
,@CurrentFragmentation DECIMAL(9, 6)
DECLARE @FragmentationState TABLE
(
SchemaName SYSNAME
,TableName SYSNAME
,object_id INT
,IndexName SYSNAME
,index_id INT
,page_count BIGINT
,avg_fragmentation_in_percent FLOAT
,avg_page_space_used_in_percent FLOAT
,type_desc VARCHAR(255)
)
INSERT INTO @FragmentationState
SELECT
s.name as SchemaName
,t.name as TableName
,t.object_id
,i.name as IndexName
,i.index_id
,x.page_count
,x.avg_fragmentation_in_percent
,x.avg_page_space_used_in_percent
,i.type_desc
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'SAMPLED') x
INNER JOIN sys.tables t ON x.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON x.object_id = i.object_id AND x.index_id = i.index_id
WHERE x.index_id > 0 AND i.type_desc <> 'NONCLUSTERED COLUMNSTORE' and i.type <4
AND alloc_unit_type_desc = 'IN_ROW_DATA'
DECLARE INDEX_CURSE CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(x.SchemaName)+'.'+QUOTENAME(x.TableName)
,CASE WHEN x.type_desc = 'CLUSTERED' THEN 'ALL'
ELSE QUOTENAME(x.IndexName) END
,x.avg_fragmentation_in_percent
FROM @FragmentationState as x
LEFT OUTER JOIN @FragmentationState y ON x.object_id = y.object_id AND y.index_id = 1
WHERE (
x.type_desc = 'CLUSTERED'
AND y.type_desc = 'CLUSTERED'
)
OR y.index_id IS NULL
ORDER BY x.object_id
,x.index_id
OPEN INDEX_CURSE
WHILE 1=1
BEGIN
FETCH NEXT FROM INDEX_CURSE INTO @ObjectName, @IndexName
,@CurrentFragmentation
IF @@FETCH_STATUS <> 0
BREAK
SELECT @SQL='ALTER INDEX '
+@IndexName+' ON '+@ObjectName
+CASE WHEN @CurrentFragmentation <= 30
THEN ' REORGANIZE;'
ELSE ' REBUILD'
+CASE WHEN CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Data Center%'
OR CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Enterprise%'
OR CONVERT(VARCHAR(100), SERVERPROPERTY('Edition')) LIKE 'Developer%'
THEN ' WITH (ONLINE=ON, SORT_IN_TEMPDB=ON) '
END+';'
END
EXEC sp_ExecuteSQL @SQL
END
CLOSE INDEX_CURSE
DEALLOCATE INDEX_CURSE

Based on your parameters it will defragment the indexes, either rebuilding or reorganizing depending on @MaxFragmentation. Online or Offline ,depending on edition.
Hope it helps


SQL Server REVOKE requires CASCADE I have the following graph:

B granted select on D and D granted select on E and E granted select on G.
B has grant select with grant option and has granted select D with grant option.
D has grant select with grant option and has granted select E with grant option.
E has grant select with grant option and has granted select G with grant option.

When I try to revoke the GRANT SELECT on D, I get an error that requires the use of CASCADE.

Code:

EXECUTE AS USER = 'userB';
GO
REVOKE GRANT OPTION FOR SELECT ON EMP FROM userD;
GO
REVERT;

Error:

  To revoke or deny grantable privileges, specify the CASCADE option.

What I need is to remove the GRANT SELECT without removing the GRANT SELECT from E and G.

When you grant permissions to a user using the WITH GRANT OPTION clause, then subsequently revoke that permission, SQL Server will prevent the revoke unless you explicitly add the CASCADE option to the REVOKE ... statement.
This prevents a security risk whereby you grant WITH GRANT OPTION to a user, who subsequently grants rights to others unbeknownst to you.  You then revoke the rights to that user, but the damage has already been done.  This error message is thrown when you attempt to revoke permissions that have been assigned with the grant option, unless you use the CASCADE option:

Msg 4611, Level 16, State 1, Line 44
To revoke or deny grantable privileges, specify the CASCADE option.

In order to revoke permissions that have been granted using the WITH GRANT OPTION clause, you must revoke the permission using the CASCADE option. This also removes the revoked rights from all users that have been granted the rights by the user with the GRANT OPTION.  If you want those other users to retain the rights granted by the user with the GRANT OPTION, you must then manually assign those rights explicitly to those other users.
This code below is a simple repro showing the symptoms, and how to deal with it.
First, create an object we can assign rights to:
IF OBJECT_ID('dbo.TestSelect') IS NULL
CREATE TABLE dbo.TestSelect
(
    t INT
);

Create three users:
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u1')
CREATE USER u1 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u2')
CREATE USER u2 WITHOUT LOGIN;

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'u3')
CREATE USER u3 WITHOUT LOGIN;

Grant SELECT WITH GRANT OPTION to user u1:
GRANT SELECT ON dbo.TestSelect TO u1 WITH GRANT OPTION;

Grant SELECT WITH GRANT OPTION to user u2 as user 'u1' :
EXECUTE AS USER = 'u1';

GRANT SELECT ON dbo.TestSelect TO u2 WITH GRANT OPTION;

REVERT

Grant SELECT WITH GRANT OPTION to user u3 as user 'u2' :
EXECUTE AS USER = 'u2';

GRANT SELECT ON dbo.TestSelect TO u3 WITH GRANT OPTION;

REVERT

Ensure we are running as the initial user (in my case dbo)
SELECT USER_NAME();

Attempt to remove the SELECT rights from user u1 without using the CASCADE option, which will fail:
REVOKE SELECT ON dbo.TestSelect FROM u1;
--Msg 4611, Level 16, State 1, Line 44
--To revoke or deny grantable privileges, specify the CASCADE option.

Add the CASCADE option, which works:
REVOKE SELECT ON dbo.TestSelect FROM u1 CASCADE;

See if the other users have access to the test table (they don't since we've revoked access using the CASCADE option):
EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;
--Msg 229, Level 14, State 5, Line 51
--The SELECT permission was denied on the object 'TestSelect', database 'Test', schema 'dbo'.

Revert back to the 'dbo' user, so we can grant SELECT on the test object to the remaining users:
REVERT -- back to 'dbo'

GRANT SELECT ON dbo.TestSelect TO U2;
GRANT SELECT ON dbo.TestSelect TO U3;

Confirm the other users have access to the test object:
EXECUTE AS USER = 'u2';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

EXECUTE AS USER = 'u3';

SELECT *
FROM dbo.TestSelect;

REVERT -- back to 'dbo'

Clean up the test object and users:
DROP TABLE dbo.TestSelect
DROP USER u3;
DROP USER u2;
DROP USER u1;

It seems to be something to be corrected by Microsoft.
The only way I see is to drop the impacted users and recreate them with the needed permissions ...

@++ ;)


Unable to connect to Oracle from SQL Developer below is the error

  Status : Failure -Io exception: The Network Adapter could not
  establish the connection

It's connecting from my system but unable to connect from other systems on the network.
ping command is working fine and tnsping is also working. any suggestion and help is very helpful.

problem resolved thanks for the support..It wasn't the database fault...It was an network issue....

This seems to be a hostname resolution problem. Please try to connect to the IP address instead of the hostname.

If this is not the solution could you please post the output of the following command:

lsnrctl status

EDIT 1:

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = IPC)(KEY = EXTPROC1521)
    )
    (ADDRESS =
      (PROTOCOL = TCP)(HOST = 192.168.2.64)(PORT = 1521)
    )
  )
)

EDIT 2:

It turned out to be an firewall problem. See comments below.

If you are done with all above solutions, Please run Sqldeveloper as "Run As administrator" and test

Imran Barolia


"Table doesn't exist in engine" after moving tables from one VPS to another I have two VPS's running right now. One, which I will refer to as the "old" server, runs on Debian 7 and gives this output for mysql --version:

mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2

The other, which I will refer to as the "new" server, is running on a brand new installation of Debian 8, and gives this output:

mysql Ver 15.1 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I am trying to move my databases & tables from the old server to the new server.  I thought that I could do this by simply copying the /var/lib/mysql/ directory from the old server and overwriting the same directory on the new server. However after doing this, I now receive this error in phpMyAdmin on the new server when I click on any of the tables or databases:

#1932 - Table 'phpmyadmin.pma__tracking' doesn't exist in engine

However I can clearly see that it exists:

Is there a solution to this? I'm not sure what I did wrong. Thanks!

You can copy files from one server to the next to move your database. To do this, you must make sure that both instances of your DB are stopped. I also recommend using rsync to copy your database files. In addition to making sure that both database servers are stopped before moving the files, you will most likely need to change the ownership of your files. sudo chown --recursive mysql:mysql /var/lib/mysql before starting your new instance.

There are other options available to move your database. If your database isn't terribly large, mysqldump is probably the most simple. I have attached the documentation for mysqldump below as provided by MariaDB, as you are using MariaDB.

https://mariadb.com/kb/en/mariadb/mysqldump/

Another option to consider, a bit more complex but a little more flexible for size, would be Percona's xtrabackup.

https://www.percona.com/software/mysql-database/percona-xtrabackup

Check that the copied files does have the proper user:group (e.g. mysql:mysql).


Is it better to create another table to possible null values in relational databases? I'm designing a relational database to use in MySQL. I have the next situation: In one table there are fields that in the most of situations are NULL. The fields would be like:

Table name: tabla

Fields:
  idtabla not null,
  text (varchar(n)) not null,
  image (mediumblob) {this can be null}

The doubt: Is better to create another table and when be necessary to use the image, query the new table?
Why is your solution, better for design? MySQL's time response, ease to make queries, etc?

You are touching on an almost philosophical argument here: should NULLable values be allowed at all given they violate the "closed world" assumption of the model relational databases are derived from (see relevant sections of http://en.wikipedia.org/wiki/Null_(SQL) and a number of other questions here such as Why shouldn't we allow NULLs? for more notes on that). To avoid unknown values splitting properties that might be unknown (or simply not applicable in all circumstances) into their own relation (table) so that you don't have values that are unknown but instead if a value is unknown it simply doesn't exist. While this satisfies the theory, there are practical implications that make it less desirable:

A JOIN operation is not free in most RDBMSs, searching for the properties in the extra table(s) increases the work the engine needs to do to satisfy your queries.
If the existence of properties is not codependent (i.e. each proerty can be unknown irrespective of the others) then to carry this implementation to its conclusion you sometimes end up needing a table for each property.
The extra joins add to the complexity of your queries and can reduce maintainability.
Updates also become less intuitive in places: blanking a property now becomes a DELETE operation and updating one may be either an INSERT or an UPDATE.

Of course there are circumstances where that first point is reversed and breaking properties off improves efficiency:

Most RBDMSs use page based storage and breaking optional information off from core data means you can fit more core data rows in a given page. Depending on your data size, RAM, and storage infrastructure this could be significant in reducing the amount of IO required for some large queries (but often isn't: try not to "over optimise" around this point, at least without running good benchmarks to make sure things are improving not worsening).
This is of course assuming that your queries are designed so they pick up only what they need so the engine won't need to care about the drawing in the extra information anyway.
Similarly there is less writing to do if you are only updating core information in an INSERT or UPDATE, and if you have constraints or triggers around those extra properties this processing is potentially avoided on each row change.

Your example looks like it is storing images (or at least large bits of data in blob type columns. There are two extra considerations here:

Most database engines store large data "off page" anyway, so if you avoid SELECT * you get the rows-per-page bonuses "for free" anyway.
Does moving them elsewhere allow you to share the large data between multiple entities rather than storing the same blob more than once, or is the information (if present) unique to a given row?

tl;dr: So no hard and fast answer I'm afraid. My advice would be to do whatever best fits your conceptual model of your data and therefore requires less thought on your part to maintain. This usually, but not always, means using NULLable columns rather than separate tables. Unless your data is truly massive the performance differences are going to be negligible.

If you create a new table then you would probably need a "JOIN" to get the image data. Joins can decrease performance of RDBMS. But in your case as there would be only one join so it won't have that much effect on over-all query performance.


Monitor source SQL code of inserts into table I have a table with rows which should not be there, means the data is wrong according to business logic. And there is a lot of possible sources the insert statement of the "bad" row is coming from. I'm not able to repro this it's just happening from time to time. And I wanna know which exact SQL statement and module (eg. procedure/trigger / ad-hoc statement) is the source of that insert. To catch the culprit.

Using Profiler or Extended Events is not the way - because you don't see the data (just statements) and while I'm not able to repro this I cannot correlate the output with the data.

I've tried collect info from sys.dm_exec_input_buffer(@@SPID,0) inside my after triggering on insert (made for logging purposes, collecting every insert in that table). But this is too wide. It's giving just initial SQL statement, let say outer stored procedure, but the insert statement could be invoked in some nested stored procedure or trigger.

I've tried to collet output of sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) - see the whole code below. But this is too narrow in opposite - it is giving me statement of my collecting after trigger.

Is there a way to catch something like it?

SELECT
OuterSQL = CAST(T.text as NVARCHAR(255))
,InnerSQL = CAST((SUBSTRING(t.text,(s.statement_start_offset/2) + 1,
          (CASE
             WHEN s.statement_end_offset = -1
               THEN DATALENGTH(t.text)
             ELSE s.statement_end_offset
           END - s.statement_start_offset)/2 + 1)) as NVARCHAR(255))
FROM sys.dm_exec_connections
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T
    INNER JOIN sys.dm_exec_query_stats s ON sys.dm_exec_connections.most_recent_sql_handle = s.sql_handle
WHERE session_id = @@SPID;

You haven't included version or edition info, so if you're running SQL Server 2016 SP1 or higher (or Enterprise Edition for earlier versions), try using SQL Server Audit to trace this information.

You can set up a Database-level Audit to capture INSERT action on that table, then query the audit files to identify the calling application and statements.

SQL Audit: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017

Audit Action Groups: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017

Also, Profiler and Extended Events should be able to capture this information for you. Which trace events are you capturing?

If you use the template TSQL_Replay trace and filter it appropriately, you should be able to see the calling code, including the explicit values passed to the stored proc or INSERT statement.

I am not really sure what you mean by bad rows, Is it something which is junk data coming from external source or from some internal deliberate attempt. In any case, I think you need to be careful about granted access to users at your database. Most of users should be having read access only so that they can't perform any DML operation i.e. insert/update/delete. For those users, who are supposed to be performing DML operations, should be doing it through execute permission on stored procedure/functions and not through direct write access on tables.

I am not sure how is your application architecture however above explains the ideal(effective) way of handling business logic and avoid any issue with bad/wrong data inserts. Sysadmin access will be only with DBA who will not know much about data and their work is more on administration side.

Having said above, if its external party then get your firewalls checked by network administrator, in case its internal - check the access. And if this is coming from legitimate application then raise this with application team and get the business validation in place at application layer instead of finding the source of data after insert at database.

One more thing to note here - if bad data is coming through any user object i.e. procedure/trigger/function that means they need to be enhanced and business logic needs to be in place to error out when data is not updated/inserted in-line with business logic.

I hope above helps.


Getting SELECT to return a constant value even if zero rows match Consider this select statement:

SELECT *,
       1 AS query_id
FROM players
WHERE username='foobar';

It returns the column query_id with value 1 along with a player's other columns.

How would one make the above SQL return at least the query_id of 1 even if the select finds no rows that match?

BTW, it's PostgreSQL 8.4.

SELECT col1,
       col2,
       col3,
       1 AS query_id
FROM players
WHERE username='foobar'
union all
select null,
       null,
       null,
       1
where not exists (select 1 from players where username = 'foobar');

Or as an alternative (might be faster as no second subselect is required):

with qid (query_id) as (
   values (1)
)
select p.*,
       qid.query_id
from qid
  left join players as p on (p.useranme = 'foobar');

You can re-write the above to a more "compact" representation:

select p.*,
       qid.query_id
from (values (1)) as qid (query_id)
  left join players as p on (p.useranme = 'foobar');

But I think the explicit CTE (with...) is more readable (although that is always in the eyes of the beholder).