Postgres: multiple selects that exclude results from previous ones I have a table of widgets that have a number of connections with potentially overlapping modes. For instance, widget A may have 10 connections, and 5 of those can run in mode A and all 10 can run in mode B. A connection has at least one supported mode. I'm trying to figure out how to design a query that returns a list of devices that support a given number of connections with particular modes.

The simplified schema looks like this:

table widgets
  id
  name

table connections
  id
  widget_id

table modes_connections
  connection_id
  mode_id

table modes
  id
  name

I need to return widget_ids that satisfy filters similar to:

2 connections with mode A AND
2 connections with mode B AND
1 connection with mode C

I can't just join everything together because the first filter for mode A must exclude those results from the other filters, similarly the mode B filter must exclude those results from the mode C filter, etc.

Also, I'm not sure how to prioritize results so connections with the least number of modes have preference. Consider the case where there are 3 connections that support modes A, B, C and two that support mode B. In the filter example above, the mode B filter should select the only-mode-B connections, allowing the A,B,C mode connections to satisfy the requirements for modes A and C.

I'm totally at a dead end. Any suggestions or pointers would be appreciated. Redesigning the schema is also an option.

I'm stumped myself.  Is this what you're looking for?

select * from (
  select id, name, acnt, bcnt, ccnt,
         row_number()
         over (partition by id order by (acnt+bcnt+ccnt)) RN
    from (
      select w.id, w.name,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'A'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  ACNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'B'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  BCNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'C'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  CCNT
         from widgets w
    )
    where acnt >= 2 and bcnt >= 2 and ccnt >= 1
) where RN = 1


Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server Where/When is the use of RBAR buffering related to ASYNC_NETWORK_IO waits negotiated between the Client/SQL Server, or Is it always utilized as in that is just how SQL Server spools data to the client?

Is "Row By Agonizing Row (RBAR)" a feature of ODBC or .NET Data Components, also, does anyone know if it can be turned off or overridden?

This stems from a SP that was tuned to run in 5 minutes, but when put in a report it falls in and out of ASYNC_NETWORK_IO for another 5 minutes for a total time of 10 minutes. In the worst case scenario, 800K records are returned, however, I would think the data would be "pushed" more quickly than what I am seeing. This is based on the fact that a similar report with no Column grouping seems to pull data quickly and returns the same amount of data. I know that grouping brings reports to a crawl, but please help me work with this issue if possible. Why would one query on a report with no grouping,with the same amount of records,"dumped" to a excel file, finish in 2 minutes, whereas, column grouping causes the client to buffer. Is there a way to force SSRS to buffer all the data locally, then render?

I found that using a "shared memory connection" will share the buffer, however, that is only valid if the client and SQL Server share the same server.

Also, the query plan for the SP is the size of a roadmap but it is a good plan. It uses no cursors and is entirely a set based query.

With .NET SqlClient (and every other client library) there is no setting that forces the client to fetch and cache the entire resultset. And SSRS will read the query results as it renders reports, and whether it reads the entire resultset at once will depend on the report design and the rendering destination.

You may be able to get SSRS to read the results more quickly if you use Shared Dataset Caching.

But it's not obvious that there's a problem that requires fixing.  You have a report that consumes 800K rows.  Forcing it to fetch all that data at the beginning is going to be expensive on the report server, and may not make anything better on your SQL Server.


Partition by ascending and descending performance I have a table, and for a given set of fields a, b and c, I need to get the first and last rows ordered by d and e, and am using ROW_NUMBER to get these rows. The relevant part of the statement is...

ROW_NUMBER() OVER (PARTITION BY a,b,c ORDER BY d ASC, e ASC) AS row_number_start,
ROW_NUMBER() OVER (PARTITION BY a,b,c ORDER BY d DESC, e DESC) AS row_number_end

The execution plan shows two sort operations, one for each. These sort operations make up over 60% of the total cost of the statement (we're talking tens of millions of rows here, the partitions will usually have 1-100 records per partition, mostly under 10)

so it would be good if I could get rid of one of them. I tried to create an index to replicate the sort; this eliminated one of the sort operations but not the latter. (Note that any index created would only be of use for this process, and would be recreated daily as part of an ETL process.)

From inspecting the execution plan, I believe the problem is that when doing a partition by statement, SQL Server insists on ordering by the partitioning columns on an ascending basis. Logically it doesn't matter if you order ascending or descending, and if the optimiser understood this then it could just read the same index backwards to work out row_number_end.

Is there any way I can make the optimiser see sense here, or can someone suggest an alternative approach to reach the same end goal?

Example table and index

CREATE TABLE dbo.Test
(
    a integer NOT NULL,
    b integer NOT NULL,
    c integer NOT NULL,
    d integer NOT NULL,
    e integer NOT NULL
);

CREATE INDEX i1 ON dbo.Test (a, b, c, d, e);

1. Apply solution

SELECT
    DT.a,
    DT.b,
    DT.c,
    FL.d,
    FL.e
FROM
(
    -- Could be an indexed view
    SELECT
        T.a,
        T.b,
        T.c
    FROM dbo.Test AS T
    GROUP BY
        T.a,
        T.b,
        T.c
) AS DT
CROSS APPLY
(
    (
        -- First
        SELECT TOP (1)
            T2.d,
            T2.e
        FROM  dbo.Test AS T2
        WHERE
            T2.a = DT.a
            AND T2.b = DT.b
            AND T2.c = DT.c
        ORDER BY
            T2.d ASC,
            T2.e ASC
    )

    UNION ALL

    (
        -- Last
        SELECT TOP (1)
            T3.d,
            T3.e
        FROM  dbo.Test AS T3
        WHERE
            T3.a = DT.a
            AND T3.b = DT.b
            AND T3.c = DT.c
        ORDER BY
            T3.d DESC,
            T3.e DESC
    )
) AS FL;

Execution plan:

2. Row Numbering Solution

An alternative, possibly better if there are a small number of rows per group on average: (improved based on a suggestion by Martin Smith)

Query

SELECT
    TF.a,
    TF.b,
    TF.c,
    TF.d,
    TF.e
FROM
(
    SELECT
        T.*,
        rn = ROW_NUMBER() OVER (
                PARTITION BY a,b,c
                ORDER BY d ASC, e ASC)
    FROM dbo.Test AS T
) AS TF
WHERE
    TF.rn = 1

UNION ALL

SELECT
    TL2.a,
    TL2.b,
    TL2.c,
    TL2.d,
    TL2.e
FROM
(
    -- TOP (max bigint) to allow an ORDER BY in this scope
    SELECT TOP (9223372036854775807)
        TL.a,
        TL.b,
        TL.c,
        TL.d,
        TL.e
    FROM
    (
        SELECT
            T.*,
            rn = ROW_NUMBER() OVER (
                    PARTITION BY a,b,c
                    ORDER BY d DESC, e DESC)
        FROM dbo.Test AS T
    ) AS TL
    WHERE
        TL.rn = 1
    ORDER BY
        -- To allow the optimizer to scan the index backward
        -- (Workaround for PARTITION BY being assumed ASC)
        TL.a DESC,
        TL.b DESC,
        TL.c DESC,
        TL.d DESC,
        TL.e DESC
) AS TL2;

Execution plan:

3. Using LEAD

This is based on the idea that the first row is row number 1, and the last row is the row before the row numbered 1:

SELECT
    L.a,
    L.b,
    L.c,
    L.d,
    L.e
FROM
(
    -- Add LEAD(1) on numbering
    SELECT
        N.*,
        next_rn = LEAD(N.rn, 1, 1) OVER (
            PARTITION BY N.a, N.b, N.c
            ORDER BY N.d, N.e)
    FROM
    (
        -- Numbered
        SELECT
            T.*,
            rn = ROW_NUMBER() OVER (
                PARTITION BY T.a, T.b, T.c
                ORDER BY T.d, T.e)
        FROM dbo.Test AS T
    ) AS N
) AS L
WHERE
    -- This row is first, or the next one is
    L.rn = 1
    OR L.next_rn = 1;

Execution plan

To summarize the comment discussion:

If you need additional columns returned, simply add them to the queries in the appropriate places and ensure they are included in the index.
Once created, queries can potentially benefit from indexes many times. With an explicit sort in the execution plan, the sort happens on every execution. Also, index creation sorts are allowed to dynamically acquire more memory as needed; that is not the case for regular sorts - they get a fixed allocation based on optimizer estimates, and that's it. Exceed the allocation, and the sort spills to disk.
The apply approach is optimal for relatively large groups. The estimate is 2 rows per iteration but the 'actual' is over all iterations (SSMS design decision). A very large number of iterations is bad for the apply.


Store the resulting list of a SELECT in a variable to count AND use the result I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the COUNT(ID) is bigger then a specified value, an UPDATE is issued for another Table WHERE the above IDs are used (SELECT ID FROM TableA WHERE something.. @cur_var)..

The idea is to have the SELECT stored in a variable (pseudo-code idlist = SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var). Then the length of this list is used in the IF (pseudo-code IFlength(idlist)>100.
In this case the list is used again in the WHERE statement (pseudo-code again, UPDATE TableB SET value = @cur_var WHERE ID IN idlist)

I hope to be specific enough, I'd like to save the result of a SELECT in a variable. Then use the result in WHERE IN variable and the number of rows in the variable to use in an IF.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

EDIT

I've tried to keep the procedure as reasonable as possible without giving away any private information..

My intention is to remove the duplicate code. The SELECT used in the IF and the SELECT used in the WHERE from the UPDATE are the same, except that I first need the COUNT(ID) and then the actual IDs

DECLARE @cur_id int;
SET @cur_id = 0;

DECLARE Cur CURSOR FOR
    --this is actually a more complex SELECT but basically gives a list of IDs
    SELECT ID FROM TableC ORDER BY ID DESC

    OPEN Cur;
    FETCH NEXT FROM Cur INTO @cur_id;

WHILE @@FETCH_STATUS = 0
BEGIN

    --here I check if the count of found IDs (with value=NULL and IN the SELECT) is bigger than 5
    IF
    (
        SELECT COUNT(ID) FROM TableA WHERE value=NULL AND ID IN
        (
            SELECT DISTINCT(ID) FROM TableB WHERE ID = @cur_id
        )
    ) > 5
    BEGIN
        --if its bigger, set the value for every ID that is part of the ID list (the same select, but above the count, below no count)
        UPDATE TableB
        SET value = @cur_id
        WHERE ID IN
        (
            SELECT ID FROM TableA WHERE value=NULL AND ID IN
            (
                SELECT DISTINCT(ID) FROM TableB WHERE ID = @cur_id
            )
        )
    END

    FETCH NEXT FROM Cur INTO @cur_id;
END

EDIT 2:

This is the closest possible solution for the duplicate SELECT i have above..

DECLARE @TempIDs TABLE
(
   ID int
);

INSERT INTO
    @TempIDs
--this is the select i have twice in the procedure above
SELECT
    ID
FROM
    TableA

SELECT COUNT(*) from @TempIDs
SELECT * FROM @TempIDs

In this case I the SELECT only once, but followed by two other SELECTS later, once using * and once COUNT(*).

Is there no way to use @TempIDs like this? (pseudo-code):

IF COUNT_ROWS(@TempIDs) > 5
BEGIN
    UPDATE TableB SET value = "wow" WHERE ID IN (@TempIDs)
END

If you want to put a query result in a table variable and iterate through the rows that can be easily done. Be careful with your loops of course.

DECLARE @Value int, @Label varchar(25)
DECLARE @Tmp table (value int, label varchar(25))

INSERT INTO @Tmp (value, label)
    SELECT 1, 'One'
UNION SELECT 2, 'Two'
UNION SELECT 3, 'Three'

WHILE (SELECT count(1) FROM @Tmp) > 0
BEGIN
    SELECT TOP 1 @Value = value, @Label = label FROM @Tmp ORDER BY value
    DELETE FROM @Tmp WHERE value = @Value
    SELECT @Value 'value', @Label 'label'
END


Can I improve this query? SELECT *
FROM   SameLogTable
WHERE  ID_Table IN (SELECT ID_Table-1
                    FROM   SameLogTable
                    WHERE  <SameCondition>) OR
       ID_Table IN (SELECT ID_Table
                    FROM   SameLogTable
                    WHERE  <SameCondition>) OR
       ID_Table IN (SELECT ID_Table+1
                    FROM   SameLogTable
                    WHERE  <SameCondition>)

This query runs on a Logging Table, and I want to select particular events, but also the event preceding and following those events.
This solution feels ugly and inefficient, what would be the better way to write the for this?

Example: If I am interested in lines with ID 4, and 23 of a LogTable, I want to get the following result:

ID    Column1    Column2    ...
3     ...        ...
4     ...        ...
5     ...        ...
22    ...        ...
23    ...        ...
24    ...        ...

These are all lines from the Same LogTable, except I specify Lines 4 and 23 using a WHERE , and I want the Select to automatically return rows 3,5 for 4 and rows 22, 24 for 23.

To Summarize the results:

MyQuery:     16s
UNION ALL:    4s
Join:        ~0s

Thanks for the replies!

Assuming that ID is the primary key of the table (or has a unique constraint), you can use this variant:

SELECT t.*
FROM   SameLogTable AS t
  JOIN ( SELECT id
         FROM   SameLogTable
         WHERE  <SameCondition>
       ) AS c
       ON t.id = c.id -1
       OR t.id = c.id
       OR t.id = c.id +1 ;

Note: if the id have gaps, which is very probable, the above will not work as expected and neither will your original query. ROW_NUMBER() can help you for that:

; WITH cte AS
( SELECT t.*,
         rn = ROW_NUMBER() OVER (ORDER BY t.id)
  FROM   SameLogTable AS t
)
SELECT t.*
FROM   cte AS t
  JOIN ( SELECT rn
         FROM   cte
         WHERE  <SameCondition>
       ) AS c
       ON t.rn = c.rn - 1
       OR t.rn = c.rn
       OR t.rn = c.rn + 1 ;


Does the order of columns in a columnstore index matter? I have a table with ~200 million rows and ~15 columns in it. I am planning to create a COLUMNSTORE index on my table.

Will there be any change in performance based on the order of columns that I use in the columnstore index? If yes, what is the logic behind it?

No, order does not matter. Each column is considered individually.
From the SQL Server team (emphasis added):

Typically you will want to put all the columns in your table into the columnstore index.  It does not matter what order you list the columns because a columnstore index does not have a key like a B-tree index does.  Internally, the data will be re-ordered automatically to get the best compression.


MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN Question

Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in Query #2.

car_trims ~50k rows

PK: (car_trims.id), index on (car_trims.horsepower_peak)

car_makes ~100 rows

PK: (car_makes.id)

Query #1

SELECT car_trims.*
FROM car_trims
GROUP BY car_trims.id
ORDER BY car_trims.horsepower_peak DESC
LIMIT 0, 200

Execution time: .0026 seconds

EXPLAIN:

Query #2

SELECT car_trims.*
FROM car_trims
STRAIGHT_JOIN car_makes ON car_makes.id = car_trims.make_id
GROUP BY car_trims.id
ORDER BY car_trims.horsepower_peak DESC
LIMIT 0, 200

Execution time: .2533 seconds

EXPLAIN:

UPDATE:

I've been continuing to work on this and I believe the index is not being utilized in Query #2 because of the mixing of GROUP BY and ORDER BY. According to the MySQL Docs,

  "In some cases, MySQL cannot use indexes to resolve the ORDER BY ...
  [for example, when] ... the query has different ORDER BY and GROUP BY
  expressions."

Query #1 does mix GROUP BY and ORDER BY and so theoretically the index should not be used according to the docs but I believe that may not apply if the GROUP BY is being ignored entirely due to only 1 table being selected from and grouping on the primary key.

Also, my actual original query is not quite as simple as the example provided here. The crucial difference: usage of GROUP_CONCAT in the SELECT requiring the aforementioned GROUP BY in order to prevent grouping on all rows (i.e. getting a 1-row result). The solution to that issue is using a DEPENDENT SUBQUERY, as discussed here: https://stackoverflow.com/questions/7381828/indexing-with-group-by-order-by-and-group-concat

Query 1:  Since id is the PRIMARY KEY, it is unique.  Hence the GROUP BY id does nothing.  Remove it.  This may make it run faster.
Query 2 does not use any columns other than id from car_makes.  The only thing that the JOIN does is to verify that there is a row in car_makes for the make_id.  You probably don't need that check, so get rid of car_makesin that query.  That will simplify things.  Note that currently there is a "filesort".  Withoutcar_makes`, that step will probably go away.
As for "why can't it use the same index" -- The STRAIGHT_JOIN forces it to look at the other table first.  This effectively turns the second table into
WHERE     make_id = ...
GROUP BY  id
ORDER BY  horsepower_peak DESC

To optimize such, it must first filter on make_id.


Why is this frequent SQL not in the SQL Server procedure cache? I frequently query the SQL Server procedure cache (dm_exec_query_stats, dm_exec_sql_text, dm_exec_query_plan) to get execution stats and execution plans.

We have a new application (with a new development team, etc.) and none of its SQL is showing up in the procedure cache.

The cache has SQL from Idera and from internal SQL Server processes;
Idera is reporting some long-running SQL from the application;
I can see application SQL in the sessions in the activity monitor.

But nothing from this application in the procedure cache. So I am almost blind here.
This is SQL Server 2017 - but I do not have this issue with other SQL Server 2017 databases.
This application is .Net (using Core .Net SqlClient Data Provider to connect) and this is a bit of an oddity for us, as most of our applications use Java, Tomcat and JDBC.

I have seen articles saying that RECOMPILE hints/options will not save the SQL in the procedure cache. I don't think the dev team is deliberately doing that. Is it possible that there is a setting in the provider that is forcing that?

Can anyone suggest avenues of investigation for this?

There's nothing in the Core .Net SqlClient Data Provider (AKA System.Data.SqlClient) that would explicitly prevent plans from being cached.

It would be helpful to know exactly what queries you're running to check the plan cache.

For instance, depending on whether the developers are parameterizing their queries, you could see entries in sys.dm_exec_cached_plans with different objtype values:

"Prepared" - the query had parameters added using the SqlParameter classes
"Adhoc" - the query did not have any parameters, or the parameters were embedded directly into the query text

But I expect that would be true of other data providers as well.

Since you're on SQL Server 2017, another option available to you is to enable Query Store for this database, and then see if the expected SQL text is being captured there.  This will include even queries that use OPTION (RECOMPILE) (see Erin Stellato's blog post Queries with OPTION (RECOMPILE) and Query Store for details).  This could help confirm whether or not developers are using that hint.

Finally, you could use Extended Events to temporarily capture executing queries while the application is running.  A very simple way to do this is with Erik Darling's sp_HumanEvents.  Here's an example usage from the site, which runs for 20 seconds, capture all queries against the "YourDatabaseName" database that ran for at least 1 second:

EXEC dbo.sp_HumanEvents
    @event_type = 'query',
    @query_duration_ms = 1000,
    @seconds_sample = 20,
    @database_name = 'YourDatabaseName';


Why my indexes monitoring status change to 'no'? On one of my production Oracle database 11g Standard Edition I have a lot (a very lot of) indexes. Some of them (majority) are created by the application installer, others are created by me when I identify a request who need some dba work.
I'm pretty sure that some of them aren't used at all and I want to identify them.

For that task, I alter all my table & index setting MONITORING status.

After a while (several weeks) I watched about used status :

SELECT index_name, table_name, monitoring, used,
       start_monitoring, end_monitoring
  FROM v$object_usage
 ORDER BY index_name;

I notice that a lot of indexes have now a NOMONITORING status ...

I search to understand what happened and why about this status.

Except the regular ALTER INDEX index_name NOMONITORING USAGE query, what is likely to change this? Does anyone know a process (a standard Oracle's in-dictionary process) that remove the MONITORING status?

One possible reason:

Bug 16311211 : REBUILDING INDEX ALTER SETTING THE MONITORING TO OFF

The bug is said to be fixed on version 12.2 and there are one-off patches for 11.2.0.3 and 11.2.0.4 as well.


Postgresql Database on external HD My laptop has a small hard drive. I want to load a big data set. I have moved the entire /usr/local/var/postgres to the external hd, and symlinked it back. Now I'm getting this error,

    psql: could not connect to server: Socket operation on non-socket
    Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I have now found the problem.  Changing the default data directory causes Postgres to refuse connections to the server. The online Manual is a little [sic!] unspecific about "config files in the same directory"…

The solution I used (with psql) is to:

    CREATE TABLESPACE tablespacename OWNER username LOCATION '/Path/to/Folder'

then,

    CREATE DATABASE databasename OWNER username TABLESPACE tablespacename

and finally

    psql databasename < /Path/to/databasedump.sql

NOTE to future visitors of this question; please see the warning below from Craig Ringer regarding how to solve this problem properly.

Do NOT create a tablespace on a removable external drive, especially if you have other data in that PostgreSQL install you care about.

If the external drive is missing then PostgreSQL will fail to start, and be extremely difficult to repair.

I've submitted a patch to add more prominent warnings about this in the latest version's manual.

You had the right idea the first time - move the data directory in its entirety. You just didn't follow through with the full process. The PostgreSQL manual can't cover it all, because it's very dependent on details of the operating system, version, etc.

Since you symlinked the datadir you don't need to change your launchd configuration to point to the new datadir. I'd say your problem was probably filesystem permissions. What you should do in a situation like that is open up Console.app and check the system logs for messages relating to PostgreSQL startup. You should also check PostgreSQL's own log files. It can also be helpful to:

sudo -u postgres ls /path/to/data/directory/

to make sure PostgreSQL has the rights to "see" what's in the data directory in the new location.


Why public is not mentioned as a fixed role in system database in SQL Server? While running

select * from sys.server_principals

for public role, in the column is_fixed_role is shown 0

But the documentation states that it is a fixed server role.

Documentation Link

Although it is stated that public is a little bit different from other roles because we can assign permissions to it. Anyway it is mentioned as a fixed role.

Can anyone explain this dilemma?

Public is just an odd duck.

But to apply some retroactive continuity to the documentation you might say that all these built in roles are "fixed" or unchangeable in ways that normal roles are not.  The roles marked as is_fixed_role have fixed permissions and changeable membership.  Public has fixed membership (everyone) and changeable permissions.

The column in sys.server_principals and sys.database_principals should be documented to clarify this.


Stored procedure slows down after upgrade from SQL Server 2008 to SQL Server 2016 Post migration from SQL Server 2008 to SQL Server 2016, one of our stored procedure is showing performance degradation. It works fine few days, and then gets slow taking up to 5 minutes. Interestingly, at times SP gets back to working normal again.

On clearing the query cache of stored procedure (DBCC FREEPROCCACHE), it starts to work fine (but only for few days).

We have tried to look into execution plan, and it looks like when the performance is fine – the smaller table join happens first to filter the records, whereas while the stored procedure is slow, the smaller table join happens in the end resulting into slow performance. The clustered index seek shows up "Expected Number of Rows"= 4 and "Actual Number of Rows" ~ 60000000.

Appreciate any inputs.

Make sure your statistics are getting updated. Also check your index fragmentation and your plan for maintaining indexes.

Since you are on SQL Server 2016, I think you should look into the Query Store feature. You turn it on by running

ALTER DATABASE YOURDATABASE SET  QUERY_STORE = ON;

Once this is turned on, the Query Store will keep the execution plans. There are several searches you can use, one being "Regressed Queries". Once you find your query, you can look at the different execution plans and force the query to use the more efficient plan with the GUI tools.

To force your stored procedure to recompile it's plan the next time it is executed, you can run

 EXEC sp_recompile 'YOURSTOREDPROCEDURE';

If you would like SQL Server to recompile the procedure plan each time it is executed, you can run

 EXECUTE YOURSTOREDPROCEDURE WITH RECOMPILE;

Here are some links for further reading and understanding.

The SQL Server 2016 Query Store: Overview and Architecture

Monitoring Performance By Using the Query Store

Recompile a Stored Procedure


Give something like the below a try...

You'll obviously need to plug in your variables for your environment, check the data types (may need to add logic to keep leading zeros?), change from the final temp tables to your regular table(s), etc.

Works fine for me for import from XML files to temp tables without deleting the files afterwards but adding logic to delete files from the UNC path shouldn't be too difficult with another xp_cmdshell command.

DECLARE @folder AS VARCHAR(1000) = '\\servername\sharename\folder\subfolder1\'
DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)

-- create global temp table
IF OBJECT_ID('tempdb..##XMLImport') IS NOT NULL
    DROP TABLE ##XMLImport

CREATE TABLE ##XMLImport (
    R00000010 VARCHAR(7)
    ,R00000020 VARCHAR(1)
    ,R00000030 INT
    ,R00000040 INT
    ,R00000050 INT
    ,R00000060 INT
    )

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command

-- create cursor
DECLARE filecurs CURSOR
FOR
SELECT REPLACE(filenameswithfolder, @folder, '') AS filenames
FROM @filesinafolder
WHERE filenameswithfolder IS NOT NULL

OPEN filecurs

FETCH NEXT
FROM filecurs
INTO @file

IF @file = 'FILE NOT FOUND'
    GOTO exitprocessing

WHILE @@fetch_status != - 1
BEGIN
    SET @sql = 'DECLARE @X XML

                    SELECT @X = P
                    FROM OPENROWSET(BULK ''' + @folder + '' + @file + ''', SINGLE_BLOB) AS Products(P)

                    DECLARE @iX INT

                    EXEC sp_xml_preparedocument @iX OUTPUT
                        ,@X

                    SELECT *
                    INTO #XMLResults
                    FROM OPENXML(@iX, ''/*/*'', 2) WITH (
                            R00000010 VARCHAR(7)
                            ,R00000020 VARCHAR(1)
                            ,R00000030 INT
                            ,R00000040 INT
                            ,R00000050 INT
                            ,R00000060 INT
                            )
                    EXEC sp_xml_removedocument @iX

                    INSERT INTO ##XMLImport
                    SELECT R00000010
                        ,R00000020
                        ,R00000030
                        ,R00000040
                        ,R00000050
                        ,R00000060
                    FROM #XMLResults'

    --PRINT @sql

    EXEC sp_executesql @sql

    -- process next file
    FETCH NEXT
    FROM filecurs
    INTO @file
END

exitprocessing:

-- clean up
CLOSE filecurs

DEALLOCATE filecurs

SELECT *
FROM ##XMLImport


To normalize or not to normalize for few distinct values Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'.

Would you store the party_type in the party table (e.g. party_type = 'Person') or normalize it (e.g. party.party_type = 1 and party_type(id, name) = (1, 'Person'))?
And why?

If you are implementing party-role-relationship model or part of it, having party_type as a separate entity is very important. You may have many more tables with foreign key to party_type (for instance, to limit applicability of certain roles to particular party type[s], or to enforce relationships between different types of parties, etc).  Using check constraints in any form (including enums) in such cases would hide the logic and make maintenance harder .


Most Efficient / Easiest Way to Capture Multiple Entries in Same Field I have been tasked with replicating a database that has been for some time used along with Cold Fusion and cleaning it up for use with a more modern web portal.

In an effort to fork out various lookup tables, I've ran into a particular column whose field may have up to eight different e-mail addresses for personnel (delimited by semicolons).

The reason for the multiple e-mail addresses is those individuals map to the same case / issue #.

What is the best way for me to parse through the current table and "tokenize" each e-mail address in order to copy it and other fields over to a new lookup table whereby each e-mail address gets its own row in the new lookup table?

In the meantime, the "legacy" database used with Cold Fusion will be in use until I've migrated things over satisfactorily.

Also, I'm a total newb to SQL so any links to similar problems to enhance your answer would be greatly appreciated.

There are multiple ways of doing this, for example a short and easy version:

drop table bad_design purge;
create table bad_design (c1 varchar2(200 char));
insert into bad_design values ('a@a.com; b@b.com; c@c.com; d@d.com; e@e.com; f@f.com; g@g.com');
insert into bad_design values ('z@z.com; y@y.com; x@x.com; w@w.com; v@v.com; u@u.com; t@t.com');
insert into bad_design values ('o@o.com');
commit;

Then:

select
  trim(column_value) c1
from
  bad_design,
  xmltable(('"' || REPLACE(c1, ';', '","') || '"'))
;

a@a.com
b@b.com
c@c.com
d@d.com
e@e.com
f@f.com
g@g.com
z@z.com
y@y.com
x@x.com
w@w.com
v@v.com
u@u.com
t@t.com
o@o.com

Which you can simply insert into a new table if needed. Other methods:

https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle


SQL Server uses only 1 CPU of 4 available I've two machines with SQL Server 2012 Express SP1 with default configuration settings for CPU usage.

I'm using this query on my work machine:

SELECT cpu_count
FROM sys.dm_os_sys_info

It returns "4", which is true.

The same query on production machine returns "1" (that machine has 4 processors too).
There's no additional CPU usage settings in both cases, except defaults.

Production machine is a virtual machine, which, possibly, was reconfigured after SQL Server installation.

Is there any way to reconfigure SQL Server for using of 4 processors?

SQL Server Express is limited to 1 CPU only

(to add more details it's limited to one socket or four cores, whichever is less)

see http://msdn.microsoft.com/en-ie/library/cc645993.aspx


Distributed transaction and Read Committed Snapshot Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?

Yes RCSI is supported for use with distributed transactions as documented in Books Online:

Choosing Row Versioning-based Isolation Levels


Indexed View over multiple partitioned tables and ALTER TABLE SWITCH Is it possible to have an indexed view that combines 2 partitioned tables and still use ALTER TABLE SWITCH to move data out of both base tables? I'm receiving the following error when trying this and can't seem to figure out how to solve it (if it's even solvable):

  ALTER TABLE SWITCH statement failed. Table 'im_db.dbo.SM_MEDIA_NEUTRAL_STATE_FACT' is not aligned with the index 'IX_V_STATE_RSN_AGG_BASE' on indexed view 'V_STATE_RSN_AGG_BASE'. The table is partitioned on column 'START_DATE_TIME_KEY', but the index on the indexed view is partitioned on column 'start_date_time_key', which is selected from a different column 'START_DATE_TIME_KEY' in table 'SM_RES_STATE_REASON_FACT'. Change the indexed view definition so that the partitioning column is the same as the table's partitioning column.;

I've tried changing the partition column but it doesn't matter which I use (from which base table).

It seems you are partitioning on one of the grouping columns of the indexed view aggregation.
In this case, the indexed view will be aligned with only one of the partitioned tables, which is one with the exposed grouping column you're partitioning on.

The Partition Switching When Indexed Views Are Defined books online topic states:

  Where the view references several tables (using joins, subqueries,
  functions, and so on), the indexed view is partition-aligned with only
  one of the partitioned tables.

Consequently, you'll be able to SWITCH partitions in/out of only one of the tables. Below is a demo script.

CREATE PARTITION FUNCTION PF_START_DATE_TIME_KEY(int)
    AS RANGE RIGHT FOR VALUES(1);
CREATE PARTITION SCHEME PS_START_DATE_TIME_KEY
    AS PARTITION PF_START_DATE_TIME_KEY ALL TO ([PRIMARY]);
CREATE TABLE dbo.DATE_TIME_DIM(
      START_DATE_TIME_KEY int
    , DATE_TIME datetime2(0)
    );
CREATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_RES_STATE_REASON_FACT(
      START_DATE_TIME_KEY int NOT NULL
      , CorrelationID int NOT NULL
      , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
CREATE TABLE dbo.SM_RES_STATE_REASON_FACT_STAGING(
      START_DATE_TIME_KEY int NOT NULL
    , CorrelationID int NOT NULL
    , Col1 int NOT NULL
    ) ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
GO

CREATE VIEW dbo.V_STATE_RSN_AGG_BASE
WITH SCHEMABINDING
AS
SELECT
      SM_RES_STATE_REASON_FACT.START_DATE_TIME_KEY
    , SUM(SM_MEDIA_NEUTRAL_STATE_FACT.col1) AS col1
    , SUM(SM_RES_STATE_REASON_FACT.col1) AS col2
    , COUNT_BIG(*) AS CountBig
FROM dbo.SM_MEDIA_NEUTRAL_STATE_FACT
JOIN dbo.SM_RES_STATE_REASON_FACT ON
    SM_MEDIA_NEUTRAL_STATE_FACT.CorrelationID = SM_RES_STATE_REASON_FACT.CorrelationID
GROUP BY SM_RES_STATE_REASON_FACT.START_DATE_TIME_KEY;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V_STATE_RSN_AGG_BASE ON
    dbo.V_STATE_RSN_AGG_BASE(START_DATE_TIME_KEY)
    ON PS_START_DATE_TIME_KEY(START_DATE_TIME_KEY);
GO

--this SWITCH succeeds because the indexed view partitioning column is from the SM_RES_STATE_REASON_FACT table
TRUNCATE TABLE dbo.SM_RES_STATE_REASON_FACT_STAGING;
ALTER TABLE dbo.SM_RES_STATE_REASON_FACT
    SWITCH PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1)
    TO dbo.SM_RES_STATE_REASON_FACT_STAGING PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1);
GO

--this SWITCH fails because the indexed view partitioning column is not from the SM_MEDIA_NEUTRAL_STATE_FACT table
TRUNCATE TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING;
ALTER TABLE dbo.SM_MEDIA_NEUTRAL_STATE_FACT
    SWITCH PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1)
    TO dbo.SM_MEDIA_NEUTRAL_STATE_FACT_STAGING PARTITION $PARTITION.PF_START_DATE_TIME_KEY(1);
GO


SSAS - Inferred Dimension Attributes - are they necessary? I have a cube with fact tables that have a parent-child relationship: Person-Case. Some dimensions are inferred from the parent, e.g. State in the example below. Person does not have a foreign key to the State dimension, but Case does, and through the Person-Case relationship the State is inferred. By doing it this way, I end up with lots of "redundant" dimension attributes from these inferred dimensions.

If I want to query the cube, for instance to obtain persons by state, I have to use the dimension attribute of the fact dimension that I am querying, but it is not possible to mix them with attributes from other dimensions. In other words, the red relationship below is not created, and if I drop the State dimension by itself into the Pivot control, I don't get a breakdown by state. I have to use the inferred State attribute of the Person dimension.

Is there a better way of doing this, so that State exists only once in the cube? Is it not valid to have hierarchical facts in a single cube?

I'm not sure I understand your question fully, so please clarify if necessary.

If you want to query the cube to get persons by state, you should add a Referenced relationship between Person and State, using Case as the Intermediate dimension.

Select the empty box at the intersection of Person and State and click on the button
Select Referenced from the relationship type dropdown
Select Case as the Intermediate dimension
Selecting the matching attributes in the two attribute dropdowns
Click OK.

Deploy and process the cube, and it should work the way you want.  Then you can get rid of the (State - Person) dimension.


mysql multiple column index Cardinality order How does Multiple-Column Indexes work in MySQL? How is it stored internally?
consider a table with Multiple-Column Indexes on two columns (X,Y).

If X has High-cardinality than Y i.e., X is less duplicated than Y, what should be the order of multiple-column index X,Y or Y,X.

If it is X,Y there is going to be lesser number of rows with same X value. will this improve the performance because B-tree look up of X will be faster?

Not sure if this answers your question, but I would not bother so much about the individual cardinality of X and Y. For index design I would start with the predicates that the index should assist with. If you have queries like:

where X = ?
where Y = ? and X = ?

go with (X, Y). If you have queries like:

where Y = ?
where Y = ? and X = ?

go with (Y, X). If your queries are like:

where X = ?
where Y = ?
where Y = ? and X = ?

go with (Y, X) and (X, Y). If your queries are like:

where X = ?
where Y = ?

go with (X) and (Y)

I assume here that the cardinality for each column is greater than 1. For cardinality in general I consider the full key cardinality (all involved columns together) to be of more importance than the individual cardinality.


List all column names for multi-joined table I have a slightly more complicated join:

SELECT person.given_name, person.family_name, person.age, person.sex, person.state,
       person.zip, person.street, person.residential_number,
       c1.name as citizen_of_country,
       c2.name as hospitalization_country,
       c3.name as infection_country

FROM patients

    JOIN person on patients.person_id = person.id
    JOIN country c1 on person."citizenOf_country_id" = c1.id
    JOIN country c2 on patients.hospitalized_in_country_id = c2.id
    JOIN country c3 on patients.infected_in_country_id = c3.id;

.. and I'd like to somehow get the name of all columns in this new table. Based on some answers I found, I tried

SELECT DISTINCT column_name FROM information_schema.columns WHERE table_schema = 'public' AND [code];

Where [code] is the block above.. But it didn't work, giving the error "Subquery must return only one column". I'm new to databases so I'm not sure how to handle this correctly.

I'd like to somehow get the name of all columns in this new table.

You can create a temporary view, which registers the row type. Then query either the information schema like you tried or the system catalog pg_attribute:

CREATE TEMP VIEW tmp_v1 AS SELECT ...;  -- your query here

SELECT attname
FROM   pg_attribute
WHERE  attrelid = 'pg_temp.tmp_v1'::regclass;

Returns the desired list of column names.

Catalog tables or information schema? See:

Query to return output column names and data types of a query, table or view

The temporary view is only visible inside your current session and dropped at its end. (Or you can drop it manually.)

In psql you could use the meta-command \gdesc:

test=# SELECT 1 AS foo \gdesc
 Column |  Type
--------+---------
 foo    | integer

The manual:

  \gdesc

  Shows the description (that is, the column names and data types) of
  the result of the current query buffer. The query is not actually
  executed; however, if it contains some type of syntax error, that
  error will be reported in the normal way.

  If the current query buffer is empty, the most recently sent query is described instead.

So you can even execute a bare \gdesc after running the query to get a "table description" for the executed query.


Finding films where 2 specific actors appeared together. (sakila db) I'm following a book which states that if we wanted to show a table for the films where Cate MacQueen and Cuba Birch both appeared, I should run the following command:
select f.release_year, f.title, concat(a1.first_name," ", a1.last_name)
from film f
    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join film_actor fa2
    on f.film_id=fa2.film_id

    inner join actor a2
    on fa2.actor_id=a2.actor_id

where ((a1.first_name="CATE" and a1.last_name="mcqueen")
    and (a2.first_name="cuba" and a2.last_name="BiRcH"));

My question is why the need for the second inner join  film_actor fa2 ? Why can't we just do
select f.release_year, f.title, concat(a1.first_name," ", a1.last_name)
from film f

    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join actor a2
    on fa1.actor_id=a2.actor_id

where ((a1.first_name="CATE" and a1.last_name="mcqueen")
    and (a2.first_name="cuba" and a2.last_name="BiRcH"));

?
I tried my way, but it just returned an empty set, instead a 2-row table like in the book.

In the first case you get a combination of all pairs of actors for each film. Try this query:
select f.film_id, fa1.actor_id, fa2.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f
    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join film_actor fa2
    on f.film_id=fa2.film_id

    inner join actor a2
    on fa2.actor_id=a2.actor_id
where f.film_id = 82;

In the second case you just join the same actor details twice:
select f.film_id, fa1.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f

    inner join film_actor fa1
    on f.film_id=fa1.film_id

    inner join actor a1
    on fa1.actor_id=a1.actor_id

    inner join actor a2
    on fa1.actor_id=a2.actor_id
where f.film_id = 82;

So there are no rows which correspond to your where conditions (a1.first_name="CATE" and a2.first_name="cuba")
Additional explanation:
SELECT * FROM film - you get the list of films (one row per film)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id - you get the list of actors for the film (multiple lines for each film and each line contains one actor id)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id - you get the same rows as above but with additional details for each actor (each line is still related to only one actor, there is no pairs of actors)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id JOIN actor a2 ON fa1.actor_id=a2.actor_id - the same results as above, you just joined the same details for the same actor twice
SELECT *
FROM film f
JOIN film_actor fa1 ON f.film_id = fa1.film_id
JOIN film_actor fa2 ON f.film_id = fa2.film_id

After the first join you get the list of actors for the film. And on the second join you add the same list of actors to each line from the above result and get the list of all pairs of actors for each film (as you can see on screenshot above)


Which keyword I should use? I have two tables:

I1 (A, B, C)
I2 (A, C)

I2 has some tuple of data that I1 doesn't have.

Now I want to create a table I3 that only has the data (A, C) that both exists in I1 and I2.

Which keyword I should use to create this table?
Use intersect?

Assuming that the combination (a,c) is unique in both table, then this is a case of a simple join plus a create table as

create table i3
as
select i1.a, i1.c
from i1
  join i2 on i2.a = i1.a and i2.c = i1.c;

If (a,c) is not unique you will have duplicates in the resulting table. This can be avoided by using the intersect operator:

create table i3
as
select a, c
from i1
intersect
select a,c
from i2;

Note that create table ... as select is ANSI SQL but some DBMS (e.g. SQL Server) don't follow the standard and use a different syntax to create a table based on a select statement.


How do I access ETW Traces on SQL Server for linux? I have SQL Server running in a docker image. I have an extended event session running on it. I can connect to it fine and view live data with SSMS. I can use T-SQL to view the histogram target. If this was a windows box, I could use logman.exe to view and manage the event trace session. How do I do it in SQL Server for Linux?

I am absolutely sure that the ETW is getting created.

SELECT
    xes.name AS [Session Name],
    CAST(xest.target_data AS xml) [Histogram XML],
    xest.target_name
    FROM sys.dm_xe_session_targets xest
    INNER JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'etw_classic_sync_target';

<ETWTarget truncated="0">
  <DefaultETWSession>
    <Name>XE_DEFAULT_ETW_SESSION</Name>
    <Path>C:\Users\Client\Temp\XEEtw.etl</Path>
    <BufferSizeKb>128</BufferSizeKb>
    <FileSizeMb>20</FileSizeMb>
  </DefaultETWSession>
  <DefaultXESession>
    <Name>XE_DEFAULT_ETW_SESSION</Name>
  </DefaultXESession>
</ETWTarget>

However, if I execute find / -iname XEEtw.etl inside bash on the docker container I get no results. I know that SQL Server on Linux is running its own internal copy of Windows NT. I just don't know if that ETW is exposed.

How do I access ETW Traces on SQL Server for linux?

Unfortunately, ETW (Event Tracing for Windows) is not available on Linux.  It is a Windows-only function.  So for the time being you are stuck with the SQL and filesystem options for view the EE data.

The .NET Core team chose LTTng for trace support when .NET apps are running on Linux.  You could explore that as an option for correlating OS events with those recorded by SQL Server.

After your update (super interesting that ETW events might be firing in Drawbridge), I see that the .etl file is getting created on a file path that probably doesn't exist on your Linux-based Docker container.  You should try setting a known-good file path when you create the XE session:

ADD TARGET package0.etw_classic_sync_target
    (SET default_etw_session_logfile_path = N'/temp/sqletw.etl' )


Convert existing subscriber Database to a publisher database. Current publisher will be subscriber We have a transactional replication setup. The publisher is on a separate SQL Server and the distributor and the subscriber together on the other server. The replication was done using restore from backup from the publisher.

Now, I have to make my subscriber database as my main database, which will also be my publisher. And the server that have my current publisher database will now be my subscriber. I have several websites using the database said. I stopped the replication agent and tried to 'point' the websites in the subscriber.

However, insert commands are failing on my subscriber (even after stopping replication) with errors relating to primary keys which is also set as auto-increment. I checked the table schema in the subscriber database and the auto increment as well as the identity specification for that certain column is set to yes.

My question is what are the changes need to be done if I want my current subscriber to be a publisher (or main database for that matter)? Will i need to take backup again from my new publisher if I will make a subscriber from my old publisher database?

If it helps, our current database is now 100 GB in size. There is a table having 40m records and changing a schema, to drop and and recreate the whole table 'may' not be an ideal option for us now.

Best option for you will be to reestablish replication with reverse role but avoid a full snapshot and re-sync to minimize downtime.
I will outline high level steps here and if you need further details let me know.

Once downtime start (confirm app servers are not making any new connection/writing to publishing articles):

Continue running the distribution agent jobs till undelivered command count is zero.
Stop the distribution agent job/jobs.
Remove replication (publisher and subscriber only) but keep the distributor as is.
Add remote distributor to new publisher if it is a separate instance (old subscriber).

USE [Master]
GO
exec sp_adddistributor @distributor = N'instance name where distribution database reside', @password =
N'xxxxxxxxx'
GO

Reinstall replication with 'replication support only' when running sp_addsubscription. See details here.
Run snapshot jobs. It will not resync your table schema+data, rather create system objects if any missing.

  replication support only  Provides automatic generation at the
  Subscriber of article custom stored procedures and triggers that
  support updating subscriptions, if appropriate. Assumes that the
  Subscriber already has the schema and initial data for published
  tables. When configuring a peer-to-peer transactional replication
  topology, ensure that the data at all nodes in the topology is
  identical. For more information, see Peer-to-Peer Transactional
  Replication.

  Not supported for subscriptions to non-SQL Server publications.

You can use this query to find undelivered commands in distribution database.

SELECT ss.srvname AS publisher,
       [name]     AS Agent,
       publication,
       a.publisher_db,
       subscriber_db,
       art.article,
       art.destination_object,
       art.source_object,
       undelivcmdsindistdb,
       delivcmdsindistdb,
       s.article_id,
       agent_id,
       a.publisher_id,
       subscriber_id
FROM   distribution.dbo.msdistribution_status AS s WITH (nolock)
       FULL OUTER JOIN distribution.dbo.msdistribution_agents AS a WITH (nolock)
                    ON s.agent_id = a.id
       JOIN master.dbo.sysservers AS ss WITH (nolock)
         ON a.publisher_id = ss.srvid
       JOIN distribution.dbo.msarticles AS art WITH (nolock)
         ON s.article_id = art.article_id
WHERE  subscriber_db <> 'virtual'
       AND subscriber_db = 'SubscriberDBName'
ORDER  BY undelivcmdsindistdb DESC
COMPUTE sum(undelivcmdsindistdb)


innodb_file_per_table and ibdata files I am looking at mysql installation (Version 5.7.19). The mysqld is running and has innodb_file_per_table set to on. Looking at the tables using a mysql client shows up that tables all have engine "InnoDB" set. There are files ibdata1 and ibdata2 in innodb_data_file_path and also the .ibd files. I wondering now since it was my newbee understanding that innodb_files_per_table obsoltes the ibdata* files? Am I wrong or is the presence of the .ibd files and ibdata* files at the same time an indication for a mis-configured database?

ibdata1 stores InnoDB dictionary, undo segment(s), and doublewrite  buffer. So, ibdata1 is needed regardless innodb_file_per_table setting.


Using PostgreSQL 8.4, how to convert bytea to text value in postgres? In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly fine i.e. results in Octet format String. See below example,

Input String : \n\t\f\b\p\k\j\l\mestPrepared

Output String : \\012\\011\\014\\010pkjlmestPrepared

Output String is inserted in the database. Now I retrieve that data from the database in a java code using JDBC. How I can unescape the string back to its original value ?

I thought of two possible approaches,

Change the database retrieval query and pass this field to any String manipulation function of postgres i.e. which can convert bytea to text.
Do the decoding in Java code.

I can understand that the approach 1 will be more efficient. I have tried almost all the functions listed here but nothing is working. Please Help!!

I am using version 8.4 of postgres on a linux machine.

For followers, since this seems to be the canonical question for "converting bytea to text" (i.e. so you can actually see it in pgAdmin etc.) . Here's how to just get it viewable (assuming your_column_name is
type bytea)
select encode(table.your_column_name, 'escape') as your_alias_name from table_name


TempLog.ldf Grows Too Large We have a stored procedure that we run regularly. It blows up the size of TempLog.ldf from nothing to 150 GB in the course of 3-4 hours. What's going on and why is it so big? TempDB.mdf is a constant 40GB. That's a fine size, and it never gets bigger. The Recovery model for TempDB is set to Simple. AutoShrink is off.

The procedure does not use any explicit transactions. It works through roughly 500 chunks of data. Some are very small, only a couple hundred rows. Some are very large, 1 - 5 Million rows. One chunk has 16 million rows.

For each chunk of data the procedure:

Reads its rows from a persistent table into a #temp table 1.
Copies those rows from #temp_table_1 into another #temp table 2,
processing them as it goes.
Creates a non-clustered index on #temp_table_2.
Does a number of updates on #temp_table_2.
Deletes rows from #temp table 2 where a specific column contains a null.
Copies rows from #temp_table_2 into a "persistent" table.
Truncates the #temp tables.

As I said, the size of TempLog.mdf consistently stays at 40GB which is fine. I need to know why TempLog.ldf gets so large and what I can do to mitigate the problem. I tried adding a manual checkpoint for TempDB at after the #temp tables have been truncated. That doesn't appear to make a difference.

Questions:

What goes on in TempLog.ldf?
Does creating an index on a #temp table take a lot of space in TempLog.ldf?
Why isn't any of this space released from TempLog.ldf when the procedure ends?
Can I inspect the contents to determine what's taking so much space?
Any other ideas about what might be going on?

You can observe transaction log usage in Tempdb with DMVs, and then possibly instrument your procedure and monitor its transaction log usage in tempdb to see if you can account for the log file growth and possibly identify what's contributing to it.

EG:

select database_transaction_log_bytes_used /1024. log_mb_used
from sys.dm_tran_database_transactions dt
join sys.dm_tran_current_transaction ct
  on dt.transaction_id = ct.transaction_id
where database_id = 2


Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2? We have an test database configured with our web application. Due to internal web application error the entries were get updated in the database. Is it there any way to recover the updated records?

My problem in brief :

My web application form has five fields such as name, applicant number, date, d.o.b, address. The database also configured with same columns. Now due to internal web application's error the applicant's number has been duplicated and whenever the second number is generated (let me say the data with applicant number 12123 is placed in the database and due to duplication of 12123 the database rows get updated) it gets updated.. sadly we didn't have any backups(no backups at all).

Now we are in need to recover the data of updated data's from the database is it possible to recover updated records..

My database recovery model was preselected as full

Any third-party tools to recover the data? Or any other methods to recover the updated rows from Microsoft SQL Server 2008 R2?

If you've ever taken a backup, and your recovery model is BULK LOGGED or FULL, then you might have some luck. Unfortunately if never took a backup before you had your problem, then you are probably bang out of luck (as Martin Smith pointed out in his comments below).

You can check whether you've ever taken a backup by looking at:

SELECT db_name(database_id) as 'database', last_log_backup_lsn
FROM sys.database_recovery_status

If the database you've lost data from has a NULL for last_log_backup_lsn, then the database is still in auto-truncate mode and your changes have possibly be lost forever.

If this is a server that you can take offline, I'd suggest shutting down the server and taking a copy or two of the .mdf/.ldf files first, just in case you can get something useful out of them.

There are third party tools like Apex SQL Log or Toad for SQL Server which will allow you to interrogate the .ldf files directly and hopefully see what happened to the data, plus they can usually interrogate transaction log backup files as well. Unfortunately they're not cheap, but you maybe able to get the functionality that you need from a trial version (I don't use either so I can't be sure about this).

If you find another tool that allows you to look at the transaction log backups, but not .ldf files, you should be able to:

Take a full backup
Take your first a transaction log backup (probably uncompressed to maximise the chance that tools will work with it)

Then use the backup from step 2.

Unfortunately, your mileage may vary, so I'd suggest doing what you can with trial versions of tools before spending lots of money on a tool that might not get your data back.


Count the nodes in a binary tree structure I need to count the left and right nodes in a binary tree structure (output grouped by joiningDate), given a particular starting node parent id (pid).

The tree is stored in the table shown below:

For example using pid = 4 you would get 2 cid (5 and 11 ) then you would use those as the new pid (5, 11). When cid is null or the full tree has been traversed count all the placement = L and placement = R. Other placements like "M" should be ignored.

Illustration:

Expected output for a selected starting node of 4:

+-----------+-------------+-------+
| placement | joiningDate | Total |
+-----------+-------------+-------+
| L         | 2015-02-02  |     3 |
| R         | 2015-02-02  |     1 |
| L         | 2015-08-21  |     4 |
| L         | 2015-12-12  |     1 |
+-----------+-------------+-------+

This is most easily implemented in SQL Server using a Recursive Common Table Expression.

Table definition

DECLARE @binaryUser AS TABLE
(
    id          integer NOT NULL,
    joiningDate date NOT NULL,
    placement   char(1) NOT NULL,
    pId         integer NOT NULL,
    cId         integer NOT NULL,
    referBy     integer NOT NULL
);

Data

INSERT @binaryUser
    (id, joiningDate, placement, pid, cid, referBy)
VALUES
    (4, '20150202', 'L', 4, 5, 4),
    (6, '20150202', 'R', 5, 8, 4),
    (8, '20150202', 'R', 4, 11, 4),
    (9, '20150202', 'L', 5, 10, 4),
    (25, '20151212', 'L', 8, 9, 4),
    (31, '20150821', 'R', 8, 12, 4),
    (33, '20150821', 'R', 12, 13, 4),
    (36, '20150821', 'R', 9, 14, 4),
    (37, '20150821', 'M', 9, 15, 4),
    (38, '20150821', 'L', 10, 16, 4),
    (39, '20150821', 'M', 4, 17, 4);

Solution

This is presented as a script, but it is trivial to convert it to a stored procedure. The basic idea is to traverse the tree recursively, then count the rows found.

DECLARE @pId integer = 4;

-- Recursive CTE
WITH R AS
(
    -- Anchor
    SELECT
        BU.joiningDate,
        BU.cId,
        BU.placement
    FROM @binaryUser AS BU
    WHERE
        BU.pId = @pId
        AND BU.placement IN ('L', 'R')

    UNION ALL

    -- Recursive part
    SELECT
        BU.joiningDate,
        BU.cId,
        R.placement
    FROM R
    JOIN @binaryUser AS BU
        ON BU.pId = R.cId
    WHERE
        BU.placement IN ('L', 'R')
)
-- Final groups of nodes found
SELECT
    R.placement,
    R.joiningDate,
    Total = COUNT_BIG(*)
FROM R
GROUP BY
    R.placement,
    R.joiningDate
OPTION (MAXRECURSION 0);

SEDE Demo

Output:

╔═══════════╦═════════════╦═══════╗
║ placement ║ joiningDate ║ Total ║
╠═══════════╬═════════════╬═══════╣
║ L         ║ 2015-02-02  ║     3 ║
║ R         ║ 2015-02-02  ║     1 ║
║ L         ║ 2015-08-21  ║     4 ║
║ L         ║ 2015-12-12  ║     1 ║
╚═══════════╩═════════════╩═══════╝


What is the optimal way to select the parent and child rows on a table with a self-relationship? Sample data from the genres table:

id   | genre_name     | genre    | parent_id
---------------------------------------------
2    | Blues          | MAIN     | NULL
6    | Folk           | MAIN     | NULL
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10

Why doesn't the statements #1 and #2 (with the reflexive join) NOT return the parent?

-- Statement #1 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id OR (pd.id= NULL AND pg.id = 10)
    WHERE pg.id = 10;
-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10

-- Statement #2 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id
    WHERE pg.id = 10 AND pg.parent_genre_id IS NULL;

-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10

-- Statement #3 returns both parent and child rows.
(SELECT
    id,
    genre_name,
    genre_type,
    parent_genre_id
    FROM `genres`
    WHERE id = 10)
UNION -- Get all sub-genres (children)
(SELECT
    cg.id,
    cg.genre_name,
    cg.genre_type,
    cg.parent_genre_id
    FROM `genres` AS pg
     JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id
    WHERE pg.id = 10);

-- OUTPUT:
id   | genre_name     | genre    | parent_id
---------------------------------------------
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10

The #3 statement seems like a bad use of UNION to me.

Warning, bad pun intended:

Now the term self-relationship sounds like it implies a certain thing people do with their hands below their belt; but in any case I would like to know why the first two selects do not cum up with the intended results. Rather disappointing after the query reached climax. Uh, I mean returns the results.

After the answer given below, I ended up using the UNION (no JOIN of course):

(SELECT
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
    FROM `genres`
    WHERE `id` = 10)
UNION -- Get all sub-genres (children)
    (SELECT
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
        FROM .`genres`
        WHERE `parent_genre_id` = 10);

See the comment on the question about #1 and #2.

I don't believe MySQL supports recursive CTEs, so a union is likely to be the best way to do this.

The self joins are only going to work where the parent key is not null, because NULL will never evaluate as equal to anything (unless MySQL has weird NULL semantics).  In order to capture the parent the union is a reasonable approach.  Left joins for this are barking up the wrong tree because you can only specify them as a join predicate for the child and you're trying to include data from a parent row that actually has no join against itself.

Another approach would be to make parent_genre_id equal to id on the pop (id = 10) row, so the self join would also pick up the parent.  You can tell the parent because the id and parent_genre_id keys are the same value.


GETDATE() uniqueness when used on INSERT Recently I was reading this blog post:
http://blogs.msdn.com/b/sqlazure/archive/2010/05/05/10007304.aspx

which contains this section:

  Picking a Clustered Index

  There are a couple of strategies for picking
  your clustered indexes; one of the easiest and best is to add another
  column of data type datetime and use that column for your clustered
  index. Here is what you need to do:

  Add the column as data type datetime
  I usually call it Date
  Set the Default Value to GetDate().
  Make it non-null.
  Create your clustered index on it before you insert data into you.

My question is would this ever create two identical values for the Date? Does this answer change if parallelism is in use? (Assume value never specified, always comes from GetDate())

I believe I'm correct in assuming it wouldn't matter due to the behind-the-scenes uniqueifier being added, right? But I'm interested anyway.

I'm asking from a SQL2008R2 perspective but would be interested if the answer differs for any version of SQL Server from 7.0 up.

GETDATE() is not guaranteed to be unique, no. Especially if it is a datetime, where milliseconds are rounded up or down, and of course when not all data is coming from the same user, you are almost guaranteed to have collisions.

Of course, a clustered index does not need to be unique, since SQL Server will make it so if it isn't (but only when it needs to). If you need to identify a specific row yourself (as opposed to just the uniquifier for SQL Server's internal usage), and there are no other candidate key columns (this is possible with things like event logging tables), you could add a non-clustered primary key that is, say, an IDENTITY column. Or if you really want web scale - and care more about insert performance than storage or any subsequent use of the data - you could use a uniqueidentifier column populated with NEWID().

Example

Let's take a look at an example, and see the differences.

USE tempdb;
GO

-- rely on uniqifier

CREATE TABLE dbo.Test1
(
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test1(g);

-- use an IDENTITY column

CREATE TABLE dbo.Test2
(
  i INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test2(g);

-- use a GUID

CREATE TABLE dbo.Test3
(
  n UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
    PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test3(g);
GO

Insert speed

I populated all three tables with about 500,000 rows, using the following script:

SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
INSERT dbo.Test1(g)
  SELECT DATEADD(SECOND, ABS([object_id])/1000, GETDATE())
  FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test2(g)
  SELECT DATEADD(SECOND, ABS([object_id])/1000,
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test3(g)
  SELECT DATEADD(SECOND, ABS([object_id])/1000,
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();

Results:

Uniquifier:    2.26 seconds
IDENTITY:      3.89 seconds
GUID:          5.06 seconds

Scan Speed

SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test1;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test2;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test3;
SELECT SYSDATETIME();

Results:

Uniquifier:  3.89 seconds
IDENTITY:    4.21 seconds
GUID:        4.85 seconds

Space used

Looking at simple results from sp_spaceused:

EXEC sp_spaceused N'dbo.Test1';
EXEC sp_spaceused N'dbo.Test2';
EXEC sp_spaceused N'dbo.Test3';

Results:

                      Rows      Reserved    Data      Index size
Test1 - Uniquifier    513300    14024 KB    13880 KB       96 KB
Test2 - IDENTITY      513300    29072 KB    15792 KB    13200 KB
Test3 - GUID          513300    49936 KB    21960 KB    27928 KB

The uniquifier takes up less space than the IDENTITY column (and obviously both take less than the GUID), since it is only needed for collisions (and probably other optimizations I'm not aware of, such as compression).

We can also look at the index pages for the clustered index on the datetime column (index id = 1) and the non-clustered primary key (index id = 2):

DBCC TRACEON(3604,-1);

-- Uniquifier
DBCC IND('tempdb', 'dbo.Test1', 1); -- 1,747 index pages
-- no second index for this table

-- IDENTITY
DBCC IND('tempdb', 'dbo.Test2', 1); -- 1,987 index pages
DBCC IND('tempdb', 'dbo.Test2', 2); -- 1,637 index pages

-- GUID
DBCC IND('tempdb', 'dbo.Test3', 1); -- 2,764 index pages
DBCC IND('tempdb', 'dbo.Test3', 2); -- 3,472 index pages

Page/Row Contents

And finally, we can take a look at a specific page to see what is stored in a row. I simply took the first row from each of the clustered index DBCC IND results from above (your page id values will almost certainly be different):

DBCC PAGE('tempdb',1, 153, 1);
DBCC PAGE('tempdb',1, 199, 1);
DBCC PAGE('tempdb',1, 217, 1);

Uniquifier - pay particular attention to length/record size:

Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Record Size = 15
--------------^^

Memory Dump @0x000000000F7EA060

0000000000000000:   10000c00 0333ba00 fba20000 020000††††.....3º.û¢.....

Slot 1, Offset 0x6f, Length 23, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
--------------^^
Memory Dump @0x000000000F7EA06F

0000000000000000:   30000c00 0333ba00 fba20000 02000001 †0....3º.û¢......
0000000000000010:   00170001 000000††††††††††††††††††††††.......

IDENTITY seems to have 4 extra bytes in the clustered index:

Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Record Size = 19
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001000 a735ba00 fba20000 03020000 †....§5º.û¢......
0000000000000010:   030000†††††††††††††††††††††††††††††††...

Slot 1, Offset 0x73, Length 27, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
--------------^^
Memory Dump @0x0000000011DAA073

0000000000000000:   30001000 a735ba00 fba20000 04020000 †0...§5º.û¢......
0000000000000010:   03000001 001b0001 000000†††††††††††††...........

GUID has an extra 16 bytes in the clustered index:

Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Record Size = 31
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001c00 393aba00 fba20000 f3233e73 †....9:º.û¢..ó#>s
0000000000000010:   a36e114b b1229a80 a5cb090a 030000††††£n.K±".¥Ë  ....

Slot 1, Offset 0x7f, Length 39, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
--------------^^
Memory Dump @0x0000000011DAA07F

0000000000000000:   30001c00 393aba00 fba20000 c7bb2544 †0...9:º.û¢..Ç»%D
0000000000000010:   4ad3574f a2c2029f e4abc9d7 03000001 †JÓWO¢Â.ä«É×....
0000000000000020:   00270001 000000††††††††††††††††††††††.'.....

Conclusion

Seems to me, by all accounts, you are better off letting the uniquifier do its own thing (assuming you don't expect to be able to differentiate between two rows with the exact same date/time value). The only time you can get in trouble with this, if any single value is duplicated 2,147,483,648 times, at which point you will overflow the uniquifier range of integer.


SQL Server Replication between a domain and VLAN I have a requirement to replicate data between two sql server 2014 instances, one of the instance is in our production domain and second instance is in a separate VLAN.

Is it possible to configure replicaiton ? if so, is there any way to open connection only unidirectional , from production domain to VLAN ? does it require bidirectional network connection like subscriber (here sql instance in VLAN) should also need access to publisher (here production domain) ?

thanks in advance.

Is it possible to configure replicaiton ?

Yes.

  if so, is there any way to open connection only unidirectional , from production domain to VLAN ?

Packet-flow must, of course, work in both directions.  If the Distributor can connect to both the Publisher and Subscriber, you can use Push Subscriptions.  With a Push Subscription all the processes run on the Distributor, and the Subscriber would never need to connect to the Publisher or Distributor.


How do I configure PostgreSQL 8.3 to log bind parameters? I'm running into some unique constraint problems with my database. I can't reproduce them, but see error messages from our production system:

exception 'UniqueViolation' with message
    'SQLSTATE[23505]: Unique violation: 7 ERROR:
    duplicate key value violates unique constraint...'

When I check the log files, I see the statement causing the exception.

Unfortunately, I don't see what the bind parameters for the SQL statement are.

The documentation for 8.3 seems to indicate that I could log the bind parameters, but I cannot determine how to turn on logging.

Can anybody help me figure out what to change in postgresql.conf?

I'm not sure how to make those visible in 8.3 except with this:

log_statement = all

That's going to put a lot of data in to your logs though, so you may not want to keep it turned on for long.  Also make sure you have the process ID included in your log_line_prefix if you want to match everything up easily.


Log Shipping Backup Successful Restore Errors We have a Log Shipping scenario where the log Backups are backed up from the Primary server directly to a Third server (using a UNC path), the backups are then copied from here to the Secondary server for restore.

Occasionally we're getting an issue where on attempt to restore at the Secondary server we get the following error:

  Error: The media family on device '[path]\[dbname]_20170113011502.trn' is incorrectly formed.
  SQL Server cannot process this media family.

When I look at the Primary server logs there is nothing to suggest that the backup failed

  Log was backed up. Database: [dbname], creation date(time):
  2016/06/11(11:55:52), first LSN: 1165941:106198:1, last LSN:
  1166536:6235:1, number of dump devices: 1, device information:
  (FILE=1, TYPE=DISK:
  {'[path]\[dbname]_20170113011502.trn'}).

I'm willing to accept that there might be a disk error or something that's causing the corruption; though I can't see anything in any server logs to suggest network or disk errors.

Three initial questions:

1) If there is a disk write issue for the backup why does SQL Server not see this at backup time?

2) If a trn backup like this fails is there any short cut to keeping the Secondary DB in line that doesn't involve a new full backup and restore from more recent successful log backups. My assumption is that if SQL thinks a log backup was written successfully any transactions in that backup would be missed if we used CONTINUE_AFTER_ERROR or some thing like that.

3) I also assume it's not possible to "redo" a log backup for a particular point in time?

Tricky to answer without more info.

There are many reasons why there may be a disk issue that doesn't actually cause the backup to fail.

Have you got any errors in the log related to disk activity? Have you tested those log backups yourself?
Can you try restoring them all manually to a test environment and checking database inegrity after if it succeeds?
For that matter, do you dbcc checkdb regularly (apologies if this is obvious, just worth mentioning).
It's tricky answering this part of the question without more info. -

Addendum:
Researching that error seems to only ever point to a version mis-match, but that seems implausible if it's only happening once and all the other backups usually work fine. Worth checking this and asking the question - are both primary and secondary on exact same versions? How often does this happen?
This sqlservercentral goes into this.

This earlier stackexchange post is similar, and suggests the same thing. Ruling out the version problem (which from what you've said I'm assuming it's not), then the other points are pointing to corruption.

Just to check again, how often has this happened and how long have you been using this configuration?

If CheckDB is successful on your database, log shipping has been in place a while and this usually runs fine and this is the first such failure, I might be tempted to put it down to a fluke (I know, not ideal) unless you can find anything more out or someone else weighs in.

Short answer is yes.

Take a differential backup and restore that to the secondary. Brent Ozar has described how to do this.

Even shorter answer - no.

A log backup is a very specific thing, once the backup is complete, the log is different (and ever changing). There only way to do anything point-in-time is with log backups. If you need reading material, Paul Randal has a nice article on Technet that goes a bit into how different backups actually work (and it's fairly accessible/not too long).


Sql Server Full Text Search one table for text values in another table Is it possible to run a full text search on an FTS-indexed column for all values in another table?

Conceptually it'd look like

select d.* from Docs d, Tags t where CONTAINS(d.fulltext, t.tagvalue)

I'm rather new to MSSQL FTS, though I know that the equivalent would work in postgres

select d.* from docs d, tags t where to_tsquery(t.tagvalue) @@ to_tsvector(d.fulltext)

Thanks for the help!

Yes, but not directly.  You would have to build dynamic SQL strings, one row for every join set (pair of Docs and Tags rows), and then execute those strings.  That's probably not practical.

In the FROM query, if you had 1,000 records in Docs and 1,000 records in Tags, the result set would have 1,000,000 rows.  I'm assuming you knew that and just dummied up some quick T-SQL - obviously this wouldn't work in production.


Delayed durability for reporting database I have a small database which basically run a couple of reporting SPs. Data gets extracted from an OLTP database and refreshed everyday and most tables in this database get truncated everyday.
The Logic in one of the SPs is that it uses alot of update statements. I can see the transaction log grows by many GBs when it runs. The database is in simple recovery mode. So my diagnoses is that all the update statements are making it slow.
My question is that will changing the setting to delayed durability have an effect on the speed of the SP?

My question is that will changing the setting to delayed durability have an effect on the speed of the SP?

If the stored procedure makes many small updates without a transaction then delayed durability might speed it up.  But batching the inserts into a larger transaction would be a better idea.


PLSQL: BEFORE INSERT Trigger I am attempting to write my first trigger statement. I am trying to create a trigger that performs a SELECT to get my MAX_TOW_WEIGHT for a LOCO_CLASS, then compare that weight to the TRAIN_WEIGHT. If the comparison fails (that is, TRAIN_WEIGHT > MAX_TOW_WEIGHT) the code would need to throw a user-defined exception, which the code which executed the INSERT or UPDATE would need to be written to handle. I have done the following which is most probably wrong though it is an attempt:

CREATE TRIGGER LOCOWEIGHT
BEFORE INSERT
ON Locomotive_Class
REFERENCING NEW AS New
FOR EACH ROW
   BEGIN
      SELECT Train_Weight FROM locomotive
   IF (MAX_TARE_WEIGHT > MAX_TOW_WEIGHT) THEN
       RAISE cError;
EXCEPTION
WHEN cError THEN
      RAISE_APPLICATION_EXCEPTION('Train weight has exceeded max tow weight');
END;

Tables for reference:

CREATE TABLE Locomotive
 (Loco_No integer PRIMARY KEY,
  Serial_No integer UNIQUE,
  Loco_Class_No integer
    REFERENCES Locomotive_Class(Loco_Class_No),
  Loco_name varchar2(20));

CREATE TABLE Locomotive_Class
 (Loco_Class_No integer PRIMARY KEY,
  Max_Tow_Weight float NOT NULL,
  Loco_Class_Len float NOT NULL);

Several things.

1) Your trigger doesn't look right - I think you need the trigger to be on the locomotive table, rather than the locamotive_class table, and have a newly inserted/updated train have its maximum weight checked against the maximum weight for the class.

2) There is no train_weight column in the locomotive table.

Assuming the above is true, this is the trigger code you require:

CREATE OR REPLACE TRIGGER LOCOWEIGHT
BEFORE INSERT OR UPDATE ON locomotive
REFERENCING NEW AS New
FOR EACH ROW
DECLARE
  MAX_WEIGHT NUMBER;
BEGIN
  SELECT max_tow_weight INTO MAX_WEIGHT
  FROM locomotive_class
  WHERE loco_class_no = :new.loco_class_no;

   IF :new.train_weight > MAX_WEIGHT THEN
       RAISE_APPLICATION_ERROR(-20000,'Train weight has exceeded max tow weight');
  END IF;
END;

Along with:

alter table locomotive add ( train_weight number ) ;


Multiply and divide dates I had a recent DBA.SE question that involved dates that were used as the x-axis in a graph: Interpolate dates along a line.

It was centred around a road_condition table:

COND_ID ROAD_ID   COND_DATE  CONDITION
------- -------   --------   ---------
      1     100   84-11-01          18
      2     100   09-01-01           6
      3     100   12-06-19           4
      4     100   15-04-29           4

      5     200   92-04-29          20
      6     200   17-04-05           3

Note: I consider cond_date to be the X-axis, and condition to be the Y-axis.

In my an attempt to answer my own question, I tried to do things like use cond_date as X1 in a calculation:

y1 - ((y2-y1)/(x2-x1)) * x1 as y_intercept

However, obviously I can't just use a date in a calculation that involves multiplication or division; dates are not traditional numbers.

As a work around, I stripped the date of its month and day: extract(year from cond_date) and performed the calculations from there on the resulting integer (the year). However, by doing this, I of course lose valuable precision in the number. This compromises the output of the calculation.

How can I multiply and divide dates, without losing precision?

Slope is a numeric concept.  It does not make sense to have a slope with time unless you already have a unit for it.  Fortunately we have date arithmetic in Oracle that can help you out.  To do this you need to convert to a numeric quantity as you have noted.

Date arithmetic works with addition and subtraction and this can create scoped numeric representations of this for your purposes.  Slope is easy enough (no modifications there since you are subtracting dates first).  The intercept is more interesting.  The question is at what date x = 0 is at.  If you subtract that date from x1, then you will get a numeric result that you can use for your multiplication factor.


Is it possible for SQL Server to miss rows on a select * where PK = "value" query We have just migrated an application to an SQL Server 2008 R2 from SQL Server 2005 and moved from a physical to virtual hosting environment.

I have a very strange error in our application and I was wondering whether anyone has seen a condition that could result in the following condition:

Table structure is very simple

CREATE TABLE [dbo].[TableName](
[PKCol] [nvarchar](8) NOT NULL,
[DataCol] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [PKCol] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TableName]
WITH CHECK ADD CONSTRAINT [FK_OtherTable] FOREIGN KEY([PKCol])
REFERENCES [dbo].[OtherTable] ([PKCol])
GO

ALTER TABLE [dbo].[TableName] CHECK CONSTRAINT [FK_OtherTable]
GO

Query is:

SELECT PKCol, DataCol FROM TableName
WHERE PKCol = 'SomeString'

This query, for the last 5 years has returned the row for the tested strings.  For a very short period of time (1 second in 2 weeks) this query is returning 0 rows (for a query string that exists 1 second before and 1 second after the query returns zero rows).  If it helps there are a few dozen rows in this table and a search for ANY of them returns zero rows when the "condition" is occurring (as in the select query for different strings returns zero rows).

There are no inserts, deletes or updates being executed against the table.  It is possible that a transaction is occurring on another table where this table is the foreign key.

The query is NOT returning any form of SQL error, it is a valid response from SQL which appears to indicate zero rows, which then affects the behaviour of the application code.

Any clues where I should start to look and/or KB/Hotfixes I should be looking at?

nvarchar(8) PK NOT NULL

  ... their PKs return null ...

This cannot happen, under any condition. Therefore I must conclude that your 'simplified' repro has simplified things beyond the limit of what you understand. Please post the exact table definition (including all indexes) and the exact query you issue.

  Null is what comes back from the linq .FirstOrDefault() query in C#.

OK, that changes things. So, if we take your word for it, you see committed rows being skipped. Under dirty read isolation (or NOLOCK hint) this is a common phenomenon, see Previously committed rows might be missed if NOLOCK hint is used. Under higher isolation levels is rather uncommon to encounter missing rows, but not impossible, as Brent and Martin indicate in their comments.

So what are you to do? After all if you wanted to program against an eventually-consistent model you'd choose something cheaper and maybe faster. First ensure you are not under a dirty read isolation level. Anomalies at higher levels are rather uncommon, so lets make sure you're not in the common bucket. If you are positively satisfied that the read occurs under a higher than dirty reads isolation level, then you need to isolate the phenomenon. Again, post here the exact table definition and the exact query you run.

Read Understanding how SQL Server executes a query.


SQL Server Transaction Backups vs Logs I've inherited a moderately-sized business-critical SQL Server 2008 database and am trying to wrap my head around backup planning. (I'm a developer, not a DBA.)

The way our system is set up right now there are two backup systems:

Weekly full backups (.bak) and hourly transaction log (.trn) backups. We keep several sets of these backups and they are regularly shipped offsite.
SQL Server logs (.ldf), with recovery model set to Full. This file is on a separate drive from the main .mdf file, but is otherwise not backed up.

In the event of an emergency restore (or when restoring backups to a dev machine), my procedure is to use the .bak files and then apply the .trn files. We have a script that makes this procedure relatively straightforward.

My questions:

Is it possible to restore the database from the .ldf file? What is it even for?
Is it needlessly redundant to have both of these transaction logs?
Is it important to back up the .ldf file?

No, it it's not possible to restore a database from an ldf file.   The ldf file would be restored along with the mdf files.

No, it's not redundant as they have two different purposes.

It's important to take full backups, and transaction log backups. Only having a copy of the ldf file doesn't help you restore the database.

As to what a ldf file is for, the ldf is the transaction log.  Think of it as a circular buffer that records changes to your database.  When you update a row, the change is immediately written to the ldf.  At some point in the future (usually less than five minutes), the modified data is written to the mdf file.

If the server crashed or there was a power failure, when SQL starts, it reads the ldf and re-applies (REDO) those changes.

Additionally, if you have a transaction that hasn't been commited and the sever crashes, all changes made by that transaction have to be undone to make the database consistent. The ldf file has that task as well. (UNDO)

I mentioned above that the ldf file is circular.  Taking a transaction log backup (.trn)  copies out a portion of the ldf file.  After a trn file is safely created, sql can reuse that portion of the ldf file.  The series of trn backups create a chain that together record every modification made to the database. Of course, if you never took a tranaction log backup, the ldf file would grow and grow and grow.

In a disaster scenario, restoring the full backup gets you a copy of the database as of the time the full backup finished.  You can then restore the trn files in order and bring the database current to any point in time including up to the last trn backup.

I'm glossing over some important details, but the gist is the that ldf is a working file that records recent changes to the database.  The trn files are copies of parts of the ldf made under the assumption that you will keep then safe so that sql can reuse the space in the ldf and if disaster strikes, you'll have them in an alternate location.


Backup Files with Append option growing huge Vs Better Options We have close to 5 Databases .

Below are the .mdf and .ldf sizes .

NOTE : Along with this we do also have the FileStream enabled for these Databases .

Kindly suggest me on below :

As of now we are using the third party tool for Backing up the data for these databases .

Most importantly they are appending backup files and now they have grown close to 800 GB .

After all , now I am just thinking of proposing the idea of daily individual files instead of appending the backup files daily and letting them grow huge .

I am thinking of it for two reasons .

For Restore of the Database from backup file in a little comfortable way , than restoring a huge file .
Next , I am thinking it will be easy to purge the backup files easily , I mean the old backup files .
Backup file maintenance , we can maintain last 5 days or so and remove the previous ones .

Please let me know :

What will be the disadvantage of my above plan .
How is it different from appending backup files and daily individual backup files with Date Time Stamp appended to the filename daily basis .
Will the Database backup also includes the FileStream Data ?
How better I would have thought than what I am thinking now . Which is more recommended and better , Overwrite / Append / Individual Backup Files with Date-Time Stamp ?

Thanks for all your time .

Don't use append, it just makes that single file bigger and bigger, which makes it harder to manage. Also, if you need to restore from it, now you have to figure out which file number you need from inside (and more often than not, people don't realize there are multiple files, and they just run a restore from it and get the default file, not the one they expected).

I strongly recommend always creating independent full backups, and having the date/time embedded in the filename is useful for manual inspection / cleanup. Thankfully, you can parameterize the output filename, so you can build it dynamically with the current date (and time where appropriate). Date alone is useful when you're only doing single daily full backups and exact time is unimportant; times are likely more useful for log/diff backups. Here's a full backup with both date and time:

DECLARE @file nvarchar(512) = N'C:\temp\foo_'
    + CONVERT(char(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(char(8), GETDATE(), 108),':','')
    + N'.bak';

BACKUP DATABASE foo TO DISK = @file
WITH INIT, COMPRESSION, STATS = 20;

For me this created the following backup file:

C:\temp\foo_20180403_082056.bak

And yes, this backup will include Filestream data.


Users running ANSI scripts are having problems with special characers Long story short. How can I fix users environments, to make them run our scripts using ANSI encode?

The problem is, we send them scripts to run on their databases using ANSI encode.

But some of them are returning to us with this problem:

We tried to say "please upgrade your software to run our scripts using ANSI encode" but most of them can't do this.

Is there a way for us, developers ( not me at all, I'm the DBA trying to help here ) to do something ?? WE have this problem since ever. we read all the internet and we couldnt fix this issue.

Thanks

First question would be: "What does ANSI encoding mean?"

Answer depends on your Windows, see this list:  National Language Support (NLS) API Reference (column "ANSI codepage")

Let's assume you have a "western" Windows, then ANSI encoding most likely means Codepage 1252

In this case you must set your NLS_LANG environment value accordingly to NLS_LANG=.WE8MSWIN1252

See also https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600

As far as I remember SQL scripts must not have any BOM (Byte Order Mark) at the beginning.


Can't return data from SQL/Plus and Toad but can from SQL Developer This one has me stumped. We are running an Oracle 11g on Amazon EC2 and I can connect and query fine with SQL Developer but with Toad and SQL/Plus I can connect but when I try to do a simple select out of v$session for example it gives me an ORA-03135 after a while. Here is what I have looked at/tried so far (in no particular order):

Looking in the listener.log file and seeing that all three connect fine
Turning the windows firewall completely off on the server (nothing changed)
TNSPing from my machine resolves
Port scanned to make sure port 1521 was open
Used telnet to make sure 1521 was open
Compared listener setup to oracle db on another project

Sounds like the network buffer is being filled but not flushed.  SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters.

SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach.  SQL*Developer uses jdbc behind the scenes to do its fetching.

hth!


SQL SSIS syntax question What do you call the following expression?

(TableName == NULL) ? "0"  : TableName

I have a sense of what it does, but would like to look up the whole syntax definition, however as there is no real descriptor in the expression I can't seem to find out what it's called.

It's what is known in programming as a Ternary Operator. It's common in C/C++, JavaScript and PHP (among other languages).

As tombom states, the idea is that it is a short-hand if statement:

<condition> ? <true-case-code> : <false-case-code>;

SQL Server 2012 and above has it's own Ternary Operator in the form of the IIF statement.

IIF(@type = 2, 1, 0)


Time oriented design in Postgresql I've been reading Snodgrass's "Developing Time-Oriented Database Applications in SQL" and I'm consulting other documents as well, but I'm willing to find more modern stuff about time-oriented design for databases and the tools available for that in Postgresql.

Also found this document.

I would be grateful just with any link or advice on the topic discussing:

Temporal and bitemporal tables
Coalescing
Validity, applicability time, transaction time
Primary keys, constraints and referential integrity in temporal DBs
Design and good design practices for temporal DBs
Native Postgresql solutions for all of the above

I have still more to read from the book and I've discovered some SQL:2011 issues about time, but they'are not implemented in Postgresql.

There's still some things I don't know how to handle or understand, for example, I found this article mentioning this:

CREATE TABLE travel_log (
 id serial PRIMARY KEY,
 name varchar(255),
 travel_range daterange,
 EXCLUDE USING gist (travel_range WITH &&)
);

which I understand it could be useful for not having overlapping rows, but even though I searched the Postgresql docs, I only read what the EXCLUDE does and that gist is just some type of INDEX, but I can't figure out which kind of syntax is the "WITH &&" thing and what does it provide.

I would be happy just having resources where I could learn about all this. Thank you very much.

The WITH keyword defines the operator which is used to test for "equality" between two rows.

A regular unique index is just a special case of an exclusion constraint. If you were to define a "normal" unique constraint using an exclusion constraint, you would use = as the operator, e.g: exclude using btree (id with =) instead of unique (id)

The && for range types is the "overlaps" operator which tests if two ranges, well, overlap.

e.g.

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01')

returns true (as the ranges overlap)

while

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01')

will return false as there is no overlap.