SQL Server backup status report We have 30-40 SQL server instances in different servers (2012 with AlwaysOn). Is there any kind of report that can be made which captures the daily backup status and sends a mail, perhaps, or at least can be appended in an Excel sheet manually and then sent later? Any help would be appreciated.

There are various ways to build a report with that information. You could do this with PowerShell or TSQL using linked servers or SSIS.

There are sample scripts on the web and this forums site for pulling backup status for a server or given database. Just need to come up with method to pull it all together for a central report in your environment.

Update

It will depend on what you mean by daily backup status. I will also assume this is going to be a manual process, but for the most part I would generally follow the steps below, for starters:

Use PowerShell:

Build T-SQL query with desired dataset I wanted for a single server.
Create PowerShell script that has function to execute that query (use Invoke-SqlCmd or System.Data.SqlClient)
Export that data to CSV (e.g. $reportdata | Export-Csv -path MyFile.csv -Delimiter "|")
Import that CSV file into Excel

I wrote this function sometime back for an SSIS package to provide to a client as an example, but it should work in your case of just passing in the query to execute in order to get an Excel file. It works the same as if you went into Excel to the Data ribbon and selected "From text" to get external data:

function SqlTo-Excel {
    [cmdletbinding()]
    param (
        [string]$server,
        [string]$database,
        [string]$csvFile,
        [string]$excelFile,
        $query,
        [string]$delimiter = '|'
    )

    if (Test-Path $excelFile)
    {
        Remove-Item $excelFile -Force
    }
    if (Test-Path $csvFile)
    {
        Remove-Item $csvFile -Force
    }

    $sqlcn = New-Object System.Data.SqlClient.SqlConnection
    $sqlcn.ConnectionString = "Data Source=$($server);Database=$($database);Integrated Security=SSPI;"
    $sqlcn.Open()

    $sqlcmd = $sqlcn.CreateCommand()
    $sqlcmd.CommandText = $query
    $sqldata = $sqlcmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($sqldata)
    $dt | Export-Csv -Path $csvFile -NoClobber -NoTypeInformation -Delimiter "|"
    $sqlcn.Close()

    $xl = New-Object -ComObject "Excel.Application"
    $xl.Visible = $false
    $wb = $xl.Workbooks.Add()
    $ws = $wb.WorkSheets.Item(1)

    $txtConnector = ("TEXT;" + $csvFile)
    $connector = $ws.QUeryTables.Add($txtConnector,$ws.Range("A1"))
    $qTable = $ws.QueryTables.Item($connector.name)

    $qTable.TextFileOtherDelimiter = $delimiter

    $qTable.TextFileParseType = 1
    $qTable.TextFileColumnDataTypes = ,2 * $ws.Cells.Columns.Count
    $qTable.AdjustColumnWidth = 1

    $qTable.Refresh()
    $qTable.Delete()

    $wb.SaveAs($excelFile,51)
    $xl.Quit()

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($ws)

    Remove-Variable xl,wb,ws,qTable,connector,txtConnector,q,csvFile,excelFile
}

Use T-SQL

This could go anywhere from just using Registered Servers in SSMS to get the results for all servers at one time; or from a single server that can talk to them all creating linked servers to each one.

To automate a T-SQL option only, would require a bit more if you want to email it, but you could dump all the data into a single table and then BCP it out to Excel possibly. I am not much for using BCP when I have PowerShell to get it straight into the format I want.


Why bulk loading a Vertica database table does not load all lines? I have a small csv file I would like to bulk load into a Vertica 6.1 database table, this file has 92 lines.

dbadmin=> COPY games FROM '/tmp/games.csv' DELIMITER ',';
 Rows Loaded
-------------
          44

The file is 92 lines, but Vertica only loads 44 lines.
As a test, I tried loading the same file in MySQL, and all 92 lines loaded fine.

I verified the file has 92 lines.

[root@localhost tmp]# cat /tmp/games.csv | wc -l
92

I opened the file and verified it has no unusual characters.

Any ideas?

Try option ABORT ON ERROR - will output a reason for rejection.

COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' ABORT ON ERROR

Check rejected and exception files or define it in COPY:

COPY 'table name' FROM '/path/to/data.txt' DIRECT RECORD TERMINATOR ',' REJECTED DATA 'path' EXCEPTIONS 'path'

https://community.vertica.com/vertica/topics/bulk_loading

(Original URL is dead; you can try this one instead)


How can I use date_trunc in postgresql to round weekend days to the following weekday? SELECT DATE_TRUNC('day', trade_date + INTERVAL '1 DAY') from trade_table;

I have dates that are all on Monday through Friday, but when I date_trunc a date that is on Saturday (e.g. taking weekday plus 1), it is rounded to Saturday.  I want it to be rounded to the following Monday rather than Saturday.  How can I do that?

If I understand right you want to add one day and if the result is Saturday or Sunday add 2 or 1 more days, so the result is the next working day. Then extracting first the day of the week (dow) and using a CASE expression,  will do:

select date_trunc('day',
                  trade_date + ( case extract(dow from trade_date)
                                   when 5 THEN 3     -- when Friday, add 3
                                   when 6 THEN 2     -- when Saturday, add 2
                                   else 1            -- any other day, add 1
                                 end
                               ) * interval '1 day' )
from trade_table ;


Using sys.diana and sys.pidl to parse PL/SQL It seems to me that it should be possible to parse existing PL/SQL with the packages  sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to unwrap a wrapped package (and that is useless for my purposes).

So, does anyone know about a pointer into the right direction?

The official documentation covering unusual errors describes DIANA:
PL/SQL is based on the programming language Ada. As a result, PL/SQL uses a variant of Descriptive Intermediate Attributed Notation for Ada (DIANA), a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA is used internally by compilers and other tools.

At compile time, PL/SQL source code is translated into machine-readable m-code. Both the DIANA and m-code for a procedure or package are stored in the database. At run time, they are loaded into the shared memory pool. The DIANA is used to compile dependent procedures; the m-code is simply executed.

I see why you are interested.  Access to the compiler underlying PL/SQL is tantalizing if you like looking under the hood.  Nonetheless I would be reluctant to invest a lot of time
when the same goal can be accomplished by parsing with PL/SQL.

As someone who has used your site many times I can only thank you for having an inquiring mind.


Trying to count the number of character in a string I've created this MySQL function to count the number of characters (str) in a string(search):

drop function if exists find_str;

delimiter //

CREATE FUNCTION `FIND_STR`(search VARCHAR(255), str CHAR(1))
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
BEGIN
SELECT ROUND (
        (
            CHAR_LENGTH(search)
            - CHAR_LENGTH( REPLACE ( search, str, "") )
        ) / CHAR_LENGTH(str)
    ) AS count INTO @count;
return @count;
END//

delimiter ;

If str is space, the function always return null, but I'm not sure why.

find_str('Hello', 'l') => 2 // correct
find_str('Hi there', ' ') => null // incorrect, it's supposed to be 1
find_str('Hi there', space(1)) => null // also incorrect, it's supposed to be 1

I think the problem has to do with the definition of CHAR(..).  It includes trailing blanks to fill out the space indicated.  But the actual string is does not have any trailing spaces.  So ' ' becomes ''.

So, change from str CHAR(1) to str VARCHAR(1).


script to show all the permissions for a table inside my database I have a procedure called spGen2_tblIE_SchedProcess_Update.

when I run this script

select * from sys.objects
where name = 'spGen2_tblIE_SchedProcess_Update'

Inside the same database I have a table called tblIE_Step

when I run the following script

select * from sys.objects
where name = 'tblIE_Step'

then I want to see all the permissions of the objects.

when I check my stored procedure
I run this script:

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc, 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc
    , *
From sys.objects as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where p.object_id = 87671360

and I get the permissions:

But when I run the same for my table, I don't get anything:

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc
    , 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc
    , *
From sys.objects as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where p.object_id = 389576426

I know there are logins and groups that belong to roles that are have db_reader and db_writer permissions.
However, they are not showing here.

How can I change my script so that it would show me all the permissions for this table?

I found a good script to see the permissions on a database object.

SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as +
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE 1=1
    AND o.name IN ('YOUR TABLE NAME')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs
ORDER BY dpr.name

the script above works for a database object.
however,
when I add permissions on the schema where the db object is, the above script does not pick that up, for example:

grant delete on schema::dbo to [db_webUser]
GO

so for that case I found this script here that caters for that as well:

------------------------------------------------------------------------------------------------------------------
-- http://schottsql.blogspot.co.uk/2011/02/quickly-script-permissions-in-sql-2005.html
------------------------------------------------------------------------------------------------------------------

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1
  AND so.name = 'ItemStock'

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
where 1=1

order by [Permissions T-SQL]
GO

when I run the script above I get the following result, which includes the permission on the schema that reflects on my table.


Why can I select multiple columns from dual? When I execute this query:

select 2+2, 2+2, 2+2 from dual;

I get multiple columns but, as per definition, dual only has one row and one column. So what is the reason behind it?

The number of columns in a table has nothing to do with the number of columns in the projection of a SELECT statement.  You can always add additional computed columns to the projection (at least practically...  theoretically, you might eventually hit some sort of logical limit).

The fact that you are querying dual here is irrelevant.  You can do exactly the same thing with any table.  The projection in this query

SELECT e.*, 2+2 computed_one, 2+3 computed_two, 2+4 computed_three
  FROM emp e

returns all the columns in the emp table along with three additional computed columns computed_one, computed_two, and computed_three.  The fact that dual always has exactly one row makes it useful when you want the result to have exactly one row but other than that, there is no logical difference whether you use dual or a single-row, single-column table that you create yourself.


Availability Group - Deleting old backup files I'm using Azure VMs with a SQL Server Availability Group, and Ola Hallengren' scripts. Backups are working without issues, however I've noticed that old backup files are not being removed from the server.

To remove the files, I'm running this command;

DECLARE @BackupDir NVARCHAR(4000) = (SELECT BackupDir = dbo.fn_SQLServerBackupDir())
DECLARE @Date DATETIME = DATEADD(DAY, -4, GETDATE())

EXECUTE [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;

Note: The function gets the default backup location for the SQL Instance.

Whenever I run this command, it completes successfully, but no files are removed. The Cleanup process that is included in the Ola Hallengren script is also unable to remove files.

In other environments, that are not AGs, the above command, plus the Cleanup in Ola Hallengren' script, work without issues.

This leads me to think the issue is either environment related or has something to do with the fact the backups where performed on databases in an AG.

Has anyone else encountered something like this before and might be able to offer some insight?

Backups are stored on the F:\ drive, which is a local disk on the server. I have also confirmed that the correct permissions have been set. At the moment, I've set full control for Everyone. (Environment is not live yet).

I tried modifying the function to add the \ at the end of the backup path, but this did not fix the issue. I've confirmed that there are backups which meet the date requirement, too.

EDIT 1:
Here is the backup command that's running, which includes the @Cleanup parameter, that doesn't remove the files;

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@BackupType = 'Log',
@Verify = 'Y',
@Checksum = 'Y',
@Compress = 'Y',
@LogToTable = 'Y',
@CleanupTime = 0,
@AvailabilityGroupDirectoryStructure = '{DatabaseName}',
@AvailabilityGroupFileName = '{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}_{Month}_{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256'
, @ServerCertificate = 'Cert_Name'

Since you mentioned this is in an AG, are you sure you're running the command and checking the backup folder on the same server?

If you are connecting to the Availability Group primary through the AG listener, then running the xp_delete_file command will run on whichever physical server happens to be the primary replica at that time.

Try running SELECT @@SERVERNAME to verify which server you're connected to when you run the backup deletion command, then check that server's F:\ drive to see if backups were deleted or not.

You should also verify that you are getting a 0 as the return code from the extended stored procedure:

DECLARE @ReturnCode int;
EXECUTE @ReturnCode = [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;
IF @ReturnCode <> 0 PRINT @ReturnCode;

Note that the example call to xp_delete_file you provided is targeting files with a .bak extension.  The example call to Ola's backup script is targeting log backups, which will have an extension of .trn by default.  This may be contributing to the discrepancy you're seeing.

By the way, I would strongly recommend that you take backups to somewhere other than the local drive.  Not only does it represent a single point of failure for your SQL Server instance and critical database backups, but it can actually be pretty confusing with an AG when doing restores.

In particular, if your transaction log backups are being taken to two different locations (local folders on each replica), restoring to a point in time over a period that includes an AG failover will be tough.  You'll have to get the log files from both servers and make sure they get restored in the right order.  The process is much simpler if they all go to a network location like \\servername\backups\MyAgListenerName\DatabaseName\LOG.


SELECT query using an array of tuples I can match one set of conditions like so:

select ID from FOO where col1=1 and col2=2 and col3=3

But, what if I have an array, [(1,2,3), (4,5,6), (7,8,9)], how do I get the ID's in this case?

To clarify, I would like a way to match each of the tuples to the three columns.  If it was broken out it would be:

select ID from FOO where col1=1 and col2=2 and col3=3

select ID from FOO where col1=4 and col2=5 and col3=6

select ID from FOO where col1=7 and col2=8 and col3=9

If I have to loop, so be it, I thought there might be a more elegant way to do this.

My version of sqlite is 3.8.11, which ships with Python 3.5.1.

There are various ways to obtain multiple IDs with a single query in your situation.

First, you can just take the series of single SELECTs:

SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3;

SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6;

SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9;

and combine them with UNION ALL:

SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3
UNION ALL
SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6
UNION ALL
SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9;

Now you have a single statement returning all the matching IDs. It has to pass over the table as many times as you have tuples, but if the number is not big, that should not be a concern.

Another way is to use the OR operator to combine the conditions:

SELECT
  ID
FROM
  FOO
WHERE col1=1 AND col2=2 AND col3=3
   OR col1=4 AND col2=5 AND col3=6
   OR col1=7 AND col2=8 AND col3=9
;

You may also add brackets to make the intention absolutely unambiguous:

WHERE (col1=1 AND col2=2 AND col3=3)
   OR (col1=4 AND col2=5 AND col3=6)
   OR (col1=7 AND col2=8 AND col3=9)

though AND has higher precedence than OR, so the result would be the same either way.

While much simpler, the second method may not necessarily be more efficient. Internally, each member of the OR-ed expression may be implemented as a separate query, all of them being combined with UNION. So, under the covers the actual query executed could be very similar to the previous query, except UNION is not quite the same as UNION ALL, because it has the additional overhead of de-duplication of the results. If you expect your results to be unique anyway, de-duplication is certainly unnecessary.

In that case you might want to consider this method:

SELECT
  f.ID
FROM
  FOO AS f
  INNER JOIN
  (
    SELECT 1 AS col1, 2 AS col2, 3 AS col3
    UNION ALL
    SELECT 4, 5, 6
    UNION ALL
    SELECT 7, 8, 9
  ) AS p ON f.col1 = p.col1
        AND f.col2 = p.col2
        AND f.col3 = p.col3
;

The p derived table returns the argument values as a row set. That row set is joined against FOO and thus serves as a filter.

Although the last query looks more verbose even than the first one, it may turn out most efficient of the three methods.

It would have looked slightly more elegant, though, if SQLite had let you use the VALUES row constructor in the FROM clause:

SELECT
  f.ID
FROM
  FOO AS f
  INNER JOIN
  (
    VALUES
      (1, 2, 3),
      (4, 5, 6),
      (7, 8, 9)
  ) AS p (col1, col2, col3)
     ON f.col1 = p.col1
    AND f.col2 = p.col2
    AND f.col3 = p.col3
;

However, you can rewrite that using a CTE (Common Table Expression, also known as the WITH clause), like this:

WITH p (col1, col2, col3) AS
  (
    VALUES
      (1, 2, 3),
      (4, 5, 6),
      (7, 8, 9)
  )
SELECT
  f.ID
FROM
  FOO AS f
  INNER JOIN p ON f.col1 = p.col1
              AND f.col2 = p.col2
              AND f.col3 = p.col3
;

But, perhaps, the most elegant solution would have been this:

SELECT
  ID
FROM
  FOO
WHERE
  (col1,col2,col3) IN ((1,2,3), (4,5,6), (7,8,9))
;

if that syntax were supported in SQLite.

(Kudos to ypercubeᵀᴹ for the last two suggestions from the standard SQL.)

As of version 3.15, though, SQLite does support tuple comparison, as commented by a_horse_with_no_name, just not the specific syntax in the previous query. You can use the IN predicate with it only if you are matching against a subquery rather than an explicitly specified tuple list (thanks CL. for the suggestion):

SELECT
  ID
FROM
  FOO
WHERE
  (col1,col2,col3) IN (VALUES (1,2,3), (4,5,6), (7,8,9))
;

Or you can compare the tuples using the = predicate:

SELECT
  ID
FROM
  FOO
WHERE
  (col1,col2,col3) = (1,2,3)
  OR
  (col1,col2,col3) = (4,5,6)
  OR
  (col1,col2,col3) = (7,8,9)
;


SQLite - What are .acd, .ard and related file types? I'm using a SQLite database, and the main database file is:

Config.acd

and there are also files named:

Config.acd-shm
Config.acd-wal

What is the .acd file type, what are those variants (acd-shm, acd-wal), and why is the main file not a .db file?

I also have the following files, what are these:

Raw.ard
Raw.ard-shm
Raw.ard-wal

The -wal and -shm files are used to store data when the DB is in WAL mode.

SQLite does not require any specific file name or extension. These file names are chosen by the program that created the database.


Why is this SQL update query returning rows? I'm running the following update command

UPDATE table
SET [End] = '2018-12-17'
WHERE (ID = '5b010204-c12c-4b38-8d28-affe11684da0')

This will give the following result:

What can cause this on SQL server 14?

This is due to an UPDATE trigger that has a SELECT in it. Modify the trigger so that it doesn't return any rows.


SQL Server Multiple JOINS with Table Value Function - query never ends I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends.

Issue Details

Table valued function:

CREATE FUNCTION [dbo].[GetIndicator]
(
      @indicator varchar(50),
      @refDate datetime
)
RETURNS
TABLE
AS
RETURN
    SELECT
        T1.Id
        , T1.ColINT_1
        , T1.ColNVARCHAR_1 collate DATABASE_DEFAULT as ColNVARCHAR_1
        , T1.ColNVARCHAR_2
        , T1.ColSMALLDATETIME_1
        , T1.ColDECIMAL_1
        , T1.ColDECIMAL_1

    FROM TABLE2 T2
    JOIN TABLE3 T3
    ON T2.COLFKT3 = T3.Id
        AND T3.ReferenceDate = @RefDate
        AND T3.State != 'Deleted'
    JOIN TABLE4 T4
        ON T2.COLFKT4 = T4.Id AND T4.Name=@indicator
    JOIN TABLE1 T1
        ON T2.COLFKT1=T1.Id

Query:

DECLARE @RefDate datetime
SET @RefDate = '30 April 2014 23:59:59'

SELECT DISTINCT OTHERTABLE.Id As Id
    FROM
            GetIndicator('ID#1_0#INDICATOR_X',@RefDate) AS OTHERTABLE
        JOIN GetIndicator('ID#1_0#INDICATOR_Y',@RefDate) AS YTABLE
            ON OTHERTABLE.SomeId=YTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=YTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_Z',@RefDate) AS ZTABLE
            ON OTHERTABLE.SomeId=ZTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=ZTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_W',@RefDate) AS WTABLE
            ON OTHERTABLE.SomeId=WTABLE.SomeId
            AND OTHERTABLE.DateOfEntry=WTABLE.DateOfEntry
        JOIN GetIndicator('ID#1_0#INDICATOR_A',@RefDate) AS ATABLE
            ON OTHERTABLE.SomeId=ATABLE.SomeId
            AND OTHERTABLE.DateOfEntry=ATABLE.DateOfEntry

Other details:

SQL server version: 2008 R2
If I execute the table function code outside the query, with the same args, the execution time is less the 1s.
Each table function call return between 250 and 500 rows.

Try changing the function to a non-inline table value function, with a clustered index. This will store the tvf's result set in an internal temp table, which may help you. A draft of what I mean:

CREATE FUNCTION [dbo].[GetIndicator]
(
  @indicator varchar(50),
  @refDate datetime
)
RETURNS @out TABLE (
    Id int NOT NULL,
    ColINT_1 int NOT NULL,
    -- ... and so on
    PRIMARY KEY CLUSTERED (Id)
)
AS

BEGIN;

    INSERT INTO @out
    SELECT
    T1.Id
    , T1.ColINT_1
    , T1.ColNVARCHAR_1 collate DATABASE_DEFAULT as ColNVARCHAR_1
    , T1.ColNVARCHAR_2
    , T1.ColSMALLDATETIME_1
    , T1.ColDECIMAL_1
    , T1.ColDECIMAL_1

    FROM TABLE2 T2
        JOIN TABLE3 T3
            ON T2.COLFKT3 = T3.Id
        AND T3.ReferenceDate = @RefDate
        AND T3.State != 'Deleted'
    JOIN TABLE4 T4
    ON T2.COLFKT4 = T4.Id AND T4.Name=@indicator
    JOIN TABLE1 T1
        ON T2.COLFKT1=T1.Id;

    RETURN;

END;

Essentially, I think it's a performance issue you're facing. Here's a simplified view of what happens: SQL Server "expands" the inline table value functions, pretty much like it would with a view. This way, you get 12 joins in a single query:

1. othertable
   join table2, table3, table4
   join table2, table3, table4
   join table2, table3, table4
   join table2, table3, table4

A table value function that isn't inline, however, stores its output in a form of temporary table that it returns to the caller, so it isn't expanded the same way.

1. (join table2, table3, table4) -> store in #temp1
2. (join table2, table3, table4) -> store in #temp2
3. (join table2, table3, table4) -> store in #temp3
4. (join table2, table3, table4) -> store in #temp4
5. join othertable, #temp1, #temp2, #temp3, #temp4

Here, the workload is split up into multiple elements. Some situations will benefit from an inline table-value function, whereas others (like yours) will suffer a performance penalty. It has to do with how many tables you're joining, the amount of data in each table, indexing, etc.


MySql One-to-zero to one-to-one I have a table Persons with id as pk and auto increment. Then I have a table Addresses with id as pk and fk to the Persons id. But now person can exist without an address, and I don't want a person to exist without an address. I am looking for a standard way to solve this problem. I am using MySql and InnoDB.

You have many state changes you want to happen in order for your interaction to be considered a success.

This pretty much defines the "atomic" goal of a transactions.

begin; --start the transaction
insert into person values (...);
insert into address values (....);
commit; --finish the transaction

This ensures that if there was an error any where along the way all transaction activity is rolled back.  It's an "all or nothing" operation so you don't end up in an in between state you define as undesirable.


Get updated rows without triggers or timestamps I'm working on a project with a client. The client has a MySQL Database with a few large tables (~100 million records each).
I've pulled the initial data.
I am required to sync the client's database to my database.
Now whenever the client inserts a row in any of the tables, I should sync it to my table. However, there are some (very strict) restrictions.
I cannot create triggers on client's tables.
The tables may not have a dateTime column for me to recognise the creation time of a row.
I do not wish to run a comparison between client's table vs mine(which will obviously take days just to identify a few 100 new insertions)

Is there any other way I can extract the newly inserted rows from client's tables?

Take a copy of the clients binary logs.  Act as if you're doing a disaster recovery.

https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

The relay log may be an alternative.


Adding an ID column based on two other columns I have a query which returns output as follows:

 [Location] [Reference] [Year] [Int1] [Int2]
    England           1   2015     13    201
    England           1   2015     12     57
    England           1   2015      4     14
    England           2   2015     18     29
    England           2   2015     18     29
    England           1   2016     32     67
    England           1   2016     43     11
    England           2   2016     10     56

Where the int1 and int2 columns represent some calculated data regarding the locations.
I am trying to represent the data in a report and realized I could use an ID column of the form:

 [ID][Location] [Reference] [Year] [Int1] [Int2]
    1   England           1   2015     13    201
    2   England           1   2015     12     57
    3   England           1   2015      4     14
    1   England           2   2015     18     29
    2   England           2   2015     33     12
    1   England           1   2016     32     67
    2   England           1   2016     43     11
    1   England           2   2016     10     56

Which is incremented for every row which has the same Reference and Year values.

I have so far tried to achieve this using the Ranking functions as in the example below but with little luck.

SELECT Location, Reference, Year, Int1, Int2,
DENSE_RANK() OVER (PARTITION BY Year ORDER BY Reference ASC) AS rnk
FROM Value

Returns

 [Location] [Reference] [Year] [Int1] [Int2][rnk]
    England           1   2015     13    201    1
    England           1   2015     12     57    1
    England           1   2015      4     14    1
    England           2   2015     18     29    2
    England           2   2015     33     12    2
    England           1   2016     32     67    1
    England           1   2016     43     11    1
    England           2   2016     10     56    2

So my question would be is there a way to use the rank functions, Rank, DENSE_RANK etc in order to achieve this? Failing that what might my best option be?

It has been suggested that a CURSOR may help me order the data in this way but after reading up on its use I would like to determine whether a set based approach is available.

I tried to create an SQLFiddle for this question but it seems I can't create a SQL Server version at the moment. I've created the this one using MySQL 5.6 so you at least have access to the schema. Apologies.

You just need

SELECT Location,
       Reference,
       Year,
       Int1,
       Int2,
       ROW_NUMBER() OVER (PARTITION BY Year, Reference
                              ORDER BY Int2 DESC) AS [Id]
FROM Value

To give your desired results (online demo).

In the event of tied Year,Reference,Int2 this will arbitrarily assign a sequential numbering between the tied rows.


How and when does SQL Agent update next_run_date/next_run_time values? I've been working on code in T-SQL to add new schedules to a SQL Agent job  using the sp_add_jobschedule proc in the msdb database.  When I add a new schedule (typically a run-once at a specific date/time) and immediately look at the values in sysjobschedules and sysschedules, I can see that the new schedule has been added and is tied to the job_id for my SQL Agent job. However, the values for next_run_date and next_run_time have 0 in them.  When I come back and look at them again in 2 or 3 minutes, they still show 0's in them.  However when I come back another 5 or 10 minutes later, it now correctly shows the date and time values corresponding to the next scheduled run.

So my questions are:

How often do these values get updated?
What process is it that updates these values?
If I were to add a schedule that was, say, 1 minute in the future, does that mean that the job will not run since the next_run_date/time haven't been updated yet?

Example of the code I use to add a new schedule:

exec msdb.dbo.sp_add_jobschedule @job_id = @jobID
                    , @name = @JobName
                    , @enabled = 1
                    , @freq_type = 1
                    , @freq_interval = 0
                    , @freq_subday_type = 0
                    , @freq_subday_interval = 0
                    , @freq_relative_interval = 0
                    , @freq_recurrence_factor = 0
                    , @active_start_date = @ScheduleRunDate
                    , @active_end_date = 99991231
                    , @active_start_time = @ScheduleRunTime
                    , @active_end_time = 235959

where @jobID is binary(16) that holds the job_id of the job in question, @ScheduleRunDate and @ScheduleRunTime are INTs with the date and time respectively.

Short Answer
It looks like the data in msdb.dbo.sysjobschedules is updated by a background thread in SQL Agent, identified as SQLAgent - Schedule Saver, every 20 minutes (or less frequently, if xp_sqlagent_notify has not been called and no jobs have run in the meantime).
For more accurate information, look at next_scheduled_run_date in msdb.dbo.sysjobactivity. This is updated in real-time any time a job is changed or a job has run. As an added bonus, the sysjobactivity stores the data the right way (as a datetime column), making it a lot easier to work with than those stupid INTs.
That's the short answer:

It could be up to 20 minutes before sysjobschedules reflects the truth; however, sysjobactivity will always be up to date. If you want a lot more details about this, or how I figured it out...

Long Answer
If you care to follow the rabbit for a moment, when you call sp_add_jobschedule, this chain of events is set into motion:
msdb.dbo.sp_add_jobschedule == calls ==> msdb.dbo.sp_add_schedule
                                         msdb.dbo.sp_attach_schedule

msdb.dbo.sp_attach_schedule == calls ==> msdb.dbo.sp_sqlagent_notify

msdb.dbo.sp_sqlagent_notify == calls ==> msdb.dbo.xp_sqlagent_notify

Now, we can't chase the rabbit any further, because we can't really peek into what xp_sqlagent_notify does. But I think we can presume that this extended procedure interacts with the Agent service and tells it that there has been a change to this specific job and schedule. By running a server-side trace we can see that, immediately, the following dynamic SQL is called by SQL Agent:
exec sp_executesql N'DECLARE @nextScheduledRunDate DATETIME
  SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2)
  UPDATE msdb.dbo.sysjobactivity
    SET next_scheduled_run_date = @nextScheduledRunDate
    WHERE session_id = @P3 AND job_id = @P4',
N'@P1 int,@P2 int,@P3 int,@P4 uniqueidentifier',
20120819,181600,5,'36924B24-9706-4FD7-8B3A-1F9F0BECB52C'

It seems that sysjobactivity is updated immediately, and sysjobschedules is only updated on a schedule. If we change the new schedule to be once a day, e.g.
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,

We still see the immediate update to sysjobactivity as above, and then another update after the job is finished. Various updates come from background and other threads within SQL Agent, e.g.:
SQLAgent - Job Manager
SQLAgent - Update job activity
SQLAgent - Job invocation engine
SQLAgent - Schedule Saver

A background thread (the "Schedule Saver" thread) eventually comes around and updates sysjobschedules; from my initial investigation it appears this is every 20 minutes, and only happens if xp_sqlagent_notify has been called due to a change made to a job since the last time it ran (I did not perform any further testing to see what happens if one job has been changed and another has been run, if the "Schedule Saver" thread updates both - I suspect it must, but will leave that as an exercise to the reader).
I am not sure if the 20-minute cycle is offset from when SQL Agent starts, or from midnight, or from something machine-specific. On two different instances on the same physical server, the "Schedule Saver" thread updated sysjobschedules, on both instances, at almost the exact same time - 18:31:37 & 18:51:37 on one, and 18:31:39 & 18:51:39 on the other. I did not start SQL Server Agent at the same time on these servers, but there is a remote possibility that the start times happened to be 20 minutes offset. I doubt it, but I don't have time right now to confirm by restarting Agent on one of them and waiting for more updates to happen.
I know who did it, and when it happened, because I placed a trigger there and captured it, in case I couldn't find it in the trace, or I inadvertently filtered it out.
CREATE TABLE dbo.JobAudit
(
  [action] CHAR(1),
  [table] CHAR(1),
  hostname SYSNAME NOT NULL DEFAULT HOST_NAME(),
  appname SYSNAME  NOT NULL DEFAULT PROGRAM_NAME(),
  dt DATETIME2     NOT NULL DEFAULT SYSDATETIME()
);

CREATE TRIGGER dbo.schedule1 ON dbo.sysjobactivity FOR INSERT
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'I', 'A';
GO
CREATE TRIGGER dbo.schedule2 ON dbo.sysjobactivity FOR UPDATE
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'U', 'A';
GO
CREATE TRIGGER dbo.schedule3 ON dbo.sysjobschedules FOR INSERT
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'I', 'S';
GO
CREATE TRIGGER dbo.schedule4 ON dbo.sysjobschedules FOR UPDATE
AS
  INSERT dbo.JobAudit([action], [table] SELECT 'U', 'S';
GO

That said, it is not hard to catch with a standard trace, this one even comes through as non-dynamic DML:
UPDATE msdb.dbo.sysjobschedules
  SET next_run_date = 20120817,
      next_run_time = 20000
 WHERE (job_id = 0xB87B329BFBF7BA40B30D9B27E0B120DE
 and schedule_id = 8)

If you want to run a more filtered trace to track this behavior over time (e.g. persisting through SQL Agent restarts instead of on-demand), you can run one that has appname = 'SQLAgent - Schedule Saver'...
So I think that if you want to know the next run time immediately, look at sysjobactivity, not sysjobschedules. This table is directly updated by Agent or its background threads ("Update job activity", "Job Manager" and "Job invocation engine") as activity happens or as it is notified by xp_sqlagent_notify.
Be aware, though, that it is very easy to muck up either table - since there are no protections against deleting data from these tables. (So if you decided to clean up, for example, you can easily remove all the rows for that job from the activity table.) In this case I'm not exactly sure how SQL Server Agent gets or saves the next run date. Perhaps worthy of more investigation at a later date when I have some free time...


What's the difference between data definition language and data description language? Are they the same thing? Which one does DDL stand for? Here's what my understanding is, but could potentially be incorrect:

Data definition language:
Contains names and types and field length etc. of the attributes in a table

A database about a database - stores metadata

DBMS refer to these during the running and maintenance of a db

Data description language:

Function used by RDBMS to craete a database from nothing

Defines a database's table using Validation rules, field types, auto-incrementing requirements, primary kets etc.

Results in a final table design, aka  a schema

It's likely that I've gotten it all messed up, would appreciate some wisdom on it. Thanks.

DDL (Data Definition Language): Used for defining data structures and schema like CREATE and ALTER commands.

DML (Data Manipulation Language): Used for managing data with schema objects like SELECT commands.

DCL (Data Control Language): Used to control data like REVOKE and GRANT commands.

TCL (Transaction Control Language): Used to manage the changes made by DML statements like COMMIT.

Reference Link


Database design - do I need another table? I am trying to make a database that follows a form that the company uses.
When a client walks in the members of staff have to fill in a form and the data is recorded. The form on paper is 10 pages long. The first time a client is seen the entire form is filled in and the client gets a clientID.

I have split the form into sections that make sense like accommodation and employment. I know I can link these tables together with the clientsID. That's the simple part.

Now when a client returns the form comes out again but this time only certain parts are filled in, what ever the clients needs are. The records for most parts don't need updating but a new record needs inserting. what would be the best way around this.

So at the moment I have for example a table called client with an id and name
another table called accommodation with clientid and address
and another table employment with clientid and employer.

But how do I go about it when a client comes in to let us know he has a new employer. I cant update the current one as that is needed but I would need to add new record for the client.

Would this mean I would have to add a look up table for all my current tables?

Another option is to just add StartDate and EndDate fields to your "Employment" table.
Then when a client comes in with a change in Employer you update the EndDate for his previous Employer and add a new record for the new Employer with no EndDate. You can always look up his current employment by checking which record for that ClientID has no EndDate. If you don't need start dates or end dates you could just add a field that's basically a flag to indicate whether this is present or past employment.


How to handle failed Index Optimization (Ola Hallengren) step in SQL Azure Elastic Job Agent? I have created a SQL maintenance job using Azure Elastic Job Agent with the following step:

EXEC jobs.sp_add_jobstep @job_name = @jobName,
@step_name = 'Optimize indexes and statistics',
@command=N'
    EXECUTE dbo.IndexOptimize
    @Databases = ''USER_DATABASES'',
    @FragmentationLow = NULL,
    @FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'',
    @FragmentationHigh = ''INDEX_REBUILD_ONLINE'',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @MinNumberOfPages = 10,
    @TimeLimit = 3600,
    @UpdateStatistics = ''ALL'',
    @OnlyModifiedStatistics = ''Y'',
    @SortInTempdb = ''Y'',
    @MaxDOP = 1,
    @LogToTable = ''Y''
',
@credential_name = @jobStepCredName,
@target_group_name= @targetGroupName,
@retry_attempts  = 0,
@step_timeout_seconds = 3600,
@max_parallelism = 1 -- IMPORTANT! We don't want to run index optimization on multiple databases at the same time

The code uses stored procedure dbo.IndexOptimize provided by Ola Hallengren.

The job is scheduled to run daily at 5 am and fails once or twice per week. The reason for failure is the internal issue to the Azure Elastic Job Agent: "Jobs service restarted while this task was in progress.". The service from Azure is still in preview so internal service errors are expected.

My current solution is to set @retry_attempts to some number greater than 0 so that Job Agent can retry the step, however I'm not sure if that is a good idea to retry a failed step for index optimization.

In particular, I'm not sure what would happen to the INDEX REBUILD, INDEX REORGANIZE, OR UPDATE STATISTICS processes if they would be cancelled or killed.

So to summarize I have the following questions:

Is this a good idea to retry index maintenance if step fails?
What happens when INDEX REBUILD, INDEX REORGANIZE, OR UPDATE STATISTICS processes fail or terminate.

I appreciate your feedback on this matter.

In your case you should add the @Resumable = 'Y' option to your job and either restart or just wait for the next schedule to run the job and finish, depending on time and concurrency issues, as running index rebuild while many users are using the database can be an issue.

Statistic and index rebuilds were transactional in earlier versions so if an alter index rebuild statement failed the results were rolled back and the old object used but in 2017 onward there is a new option WITH (RESUMABLE=ON) that allows you to resume the index rebuild later.

You should also look at the @WaitAtLowPriorityMaxDuration and @LockTimeout parameters in Ola's scripts as those can help in minimizing blocking.


Database Stuck 'In Recovery' with command XTP_DB_RECOVERY We recently converted a table to memory optimized data. Our backups have all ballooned (3x300GB files to 3x600GB files for Full, 3x50GB to 3x250GB for Diffs), and startup has been getting progressively slower.

In an attempt to stave off these issues we converted the table in question to SCHEMA_ONLY durability, but now the database will not leave 'In Recovery' status.

The errorlog originally was updating on recovery status every 20 seconds, predicting analysis would take ~9days to complete, after roughly an hour however, the updates ceased.

SP_WHO2 shows only one process accessing the DB in question with the command XTP_DB_RECOVERY, however SELECT * FROM sys.dm_db_xtp_checkpoint_files returns no rows.

Do I have any recourse? Or way to see the estimated time remaining for this XTP_DB_RECOVERY command?

It's in a restoring state so you need to create and log a backup. (see below)

    BACKUP DATABASE [earnings]
TO DISK = N'c:\sql\earnings.bak'
    WITH NOFORMAT, NOINIT, NAME = N'earnings-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    BACKUP LOG [earnings]
TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'
    WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07',
SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once we have the backups, we will restore the backups.

In order to restore the full and log backup we need to use the NORECOVERY option for the full restore. So, if we just restore the full backup as follows:

RESTORE DATABASE [earnings]
FROM DISK = N'c:\sql\earnings.bak' WITH NORECOVERY, NOUNLOAD, STATS = 10

The database will now be in a restoring state.  If we forget to restore additional backups, the database will be stuck in this mode.

object explorer
To finalize the restore and access the database we need to restore the log backup as follows:

RESTORE LOG [earnings]
FROM DISK = N'c:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'
SQL Server database in RESTORING state after doing backup log with NORECOVERY

Another reason your database can be in restoring state is when you backup the tail of the log using the NORECOVERY option as shown below.

    BACKUP DATABASE [earnings]
TO DISK = N'c:\sql\earnings.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'earnings-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    BACKUP LOG [earnings]
TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'
    WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07',
SKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 10

This will cause the database to change to a restoring state.

To fix this you can restore the database backups as shown above.

Make a SQL Server database in RESTORING state accessible without restoring backups
If the database is stuck in the restoring state and you don't have additional backups to restore, you can recover the database using the following command:

RESTORE DATABASE [earnings] WITH RECOVERY

Once you issue this command, the database will be useable, but you won't be able to restore any additional backups for this database without starting all over again with the full backup.

Read up on "SQL Server Database Mirroring" also.


Would you use MongoDB in high-transaction environment? using other words:

does the one-write-lock-per-database policy (it's not a "bug" but a "feature" by design) affect contention making its usage in certain kind of workloads less then optimal?

if not how do you maintain that kind of workload?

would you use an async-write-queue  at the application level? (roll-backing would be difficult with such a structure though)

MongoDB can survive high concurrency writes, so long as you engineer for it.

INSERTs are about the same size.
Very little UPDATE going on.
Shard to add write concurrency scalability.

The things that will kill you on write scalability will be updates that go past the built in padding factor, as that triggers a double write as the block is relocated to a larger free spot. If your document sizes are within a narrow range and updates don't change the size much, it can scale quite far.


Execution plan showing missing index but query is fast While looking at an actual execution plan, it's showing missing indexes even though the query is taking less than 1 second.

SELECT
    Account.AccountID,
    Account.Name
FROM
    account
    LEFT OUTER JOIN accountfeaturesetting afs
ON afs.accountid = account.accountid
and afs.featureid = 'Schedules'
 and
afs.settingid = 'EditReasons'
WHERE
    ISNULL(afs.Value, '0') = '0'
    AND EXISTS
(SELECT 1 FROM program WHERE program.AccountID = account.AccountID
 AND program.Active = 1
AND (program.ScheduleEditReasonFlags <> 0
OR program.ScheduleEditReasonFields <> 0))
    AND account.IsMaster = 0
    AND account.BeginDate IS NOT NULL

The execution plan is showing:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Account] ([IsMaster],[BeginDate])
INCLUDE ([AccountID],[Name])

Do we need to create the index even though query is only taking 1 second? On what basis should an index be created?

I am going to run this query as a daily job.

You don't need to add the index right now.  All the "missing index" message means is this might be helpful.  It might not be honestly, or there might be an index that helps even more.

As to when you need to add the new index?  Well let's say you have 100 rows in the account table but it's not really in use yet.  In a couple of months you are up to 10,000 rows and the query is now taking half an hour or more.  At that point you might consider adding the index.  And when I say consider, I mean test it in a test environment and see if it actually improves things.

Other considerations include:

How many indexes do I already have on this table? If you have 10+ indexes then I'd be more concerned than if you had say 2.
How important is this query?  You say you are running it every night.  At 1 second that's fine (assuming you only run it once).  If on the other hand it's part of a process and being run 1000's of times each night then it might be more of an issue.  If it get's to a point where it's taking long enough to cause you issues (causing problems with another process, or running into times when users are in the system etc) then you need to look at performance and adding indexes.


Copy Database option not found in SQL Server Management Studio 2008 R2 I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2.

Can anyone tell me whats going on please?

That's because you are clicking on the wrong thing.  Don't right-click Databases, but you need to actually right-click the database that you want to copy, and under Tasks there is an option for Copy Database....  In other words, right-click on Database1 and then you'll see this option under Tasks.

As per @AaronBertrand's comment, though, it is highly recommended not to use the Copy Database Wizard.  Just go with a backup and restore.  It'll be less of a headache.

Edit: It seems as though this option is not available for SQL Server Express edition.  When connecting to a SQL Server Express instance, you cannot run the Copy Database wizard. This is due to the fact that the two primary means of executing this task are unavailable in SQL Server Express Edition. First, the Detach/Reattach method relies on SQL Agent (presumably to copy the data and log files from source to destination), and SMO has been removed from 2008 R2, so that method is also unavailable in Express 2008 R2.


Fastest way to disable indices and drop constraints and rebuild SQL Server table Below are some T-SQL commands that we're running on a table with a few billion records.  Most of the database's size is taken up by 5 tables like this.  What is the quickest way to perform these steps without causing any issues?  It takes more than an hour just to run the 1st command.  Would it just be easier to drop the entire table and re-create it?  Or is that not possible and safe with so much data?  Can anyone think of any other ideas to speed this up?  We are just trying to truncate the data, and then rebuild the table from scratch in our ETL process.

        DROP INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] WITH ( ONLINE = OFF )
        ALTER TABLE OBS_FACT DROP CONSTRAINT OBS_FACT_PK
        ALTER INDEX ALL ON OBS_FACT disable;

        -- add new data to OBS_FACT table via ETL process

        ALTER TABLE [dbo].[OBS_FACT] ADD CONSTRAINT [OBS_FACT_PK] PRIMARY KEY NONCLUSTERED
        (
            [ENCOUNTER_NUM] ASC,
            [CONCEPT_CD] ASC,
            [PROVIDER_ID] ASC,
            [START_DATE] ASC,
            [MODIFIER_CD] ASC,
            [INSTANCE_NUM] ASC
        ) ON [PRIMARY]

        CREATE CLUSTERED INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT]
        (
            [CONCEPT_CD] ASC
        );

        -- REBUILD indexes on OBSERVATION_FACT
        ALTER INDEX ALL ON OBS_FACT REBUILD

Often times, the drop index command causes this error in SQL Server Management Studio, if you try to re-launch SQL Server Management Studio in a different Window.

  Lock request time out period exceeded (Microsoft SQL Server, Error:
  1222)

If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful.

If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your Clustered Index order in the first place.

However, if you want to disable your indexes, then that would be as follows:

-- Disable indexes on OBSERVATION_FACT
-- If you're dropping, don't disable. If you're disabling, don't drop...
ALTER INDEX ALL ON OBS_FACT DISABLE;

-- Truncate your table
TRUNCATE TABLE dbo.OBS_FACT;

-- ETL Process here....

-- REBUILD indexes on OBSERVATION_FACT
-- Or recreate them if you've dropped them in step 1
ALTER INDEX ALL ON OBS_FACT REBUILD WITH (ONLINE = ON);

Creating following by immediately rebuilding your indexes as per your script, is pointless, as creating builds the index. Why then immediately rebuild?


How to store hierarchical dimension for timeseries data I have a requirement to store data about a load of Searches. Each time someone searches we want to record it for analyzing later on.

As part of the search the user can select one or more Categories to search within. We would like to record the categories selected and the hierarchy that the category was in.

However, the categories can change and move over time. We are likely to want to analyze both by the parent categories that the child categories were in at the time of the search, and also by the parents that the categories are in at the time of analysis.

What is the best way of storing this kind of data?

Currently I have a stream of JSON API calls (we need to support 50 a second) which look like this:

{
    "timestamp":"2018-03-08T11:22:33.456+0000",
    "searchString":"This is a search",
    "categories":[
         "Cat D/Cat B/Cat A",
         "Cat E/Cat B/Cat A"
    ]
}

Categories D and E were selected, Category B is the parent of D & E and Category A (one of several roots) is the parent of B.

We are currently just storing the time stamp and search string in a flat table in a normal OLTP style SQL Server DB:

CREATE TABLE SearchEvent (
    id int PRIMARY KEY,
    searchString nvarchar(100),
    timestamp datetimeoffset
)

SQL Server has a hierarchyid type defined to deal with this kind of system.

Consider this example:

IF OBJECT_ID(N'dbo.Categories', N'U') IS NOT NULL
DROP TABLE dbo.Categories;
GO
CREATE TABLE dbo.Categories
(
    CatID int NOT NULL
        CONSTRAINT PK_Categories
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , hid hierarchyid NOT NULL
    , CategoryName varchar(30) NOT NULL
);

INSERT INTO dbo.Categories (hid, CategoryName)
VALUES ('/', 'Root')
    , ('/1/', 'A')
    , ('/1/2/', 'B')
    , ('/1/2/3/', 'C')
    , ('/1/2/4/', 'D')
    , ('/1/2/5/', 'E');

A sample query against the table:

SELECT c.CatID
    , Hierarchy = c.hid.ToString()
    , Parent = c.hid.GetAncestor(1).ToString()
    , c.CategoryName
    , ParentCategories = STUFF((
        SELECT ', ' + c1.CategoryName
        FROM dbo.Categories c1
        WHERE c.hid.IsDescendantOf(c1.hid) = CONVERT(bit, 1)
            AND c1.hid <> c.hid
            AND c1.CategoryName <> 'Root'
        FOR XML PATH ('')), 1, 2, '')
FROM dbo.Categories c;

The results:

╔═══════╦═══════════╦════════╦══════════════╦══════════════════╗
║ CatID ║ Hierarchy ║ Parent ║ CategoryName ║ ParentCategories ║
╠═══════╬═══════════╬════════╬══════════════╬══════════════════╣
║     1 ║ /         ║ NULL   ║ Root         ║ NULL             ║
║     2 ║ /1/       ║ /      ║ A            ║ NULL             ║
║     3 ║ /1/2/     ║ /1/    ║ B            ║ A                ║
║     4 ║ /1/2/3/   ║ /1/2/  ║ C            ║ A, B             ║
║     5 ║ /1/2/4/   ║ /1/2/  ║ D            ║ A, B             ║
║     6 ║ /1/2/5/   ║ /1/2/  ║ E            ║ A, B             ║
╚═══════╩═══════════╩════════╩══════════════╩══════════════════╝

To implement this into your system, you'd reference the above table in your table of searches.  Something like:

CREATE TABLE dbo.Searches (
    id int NOT NULL
        CONSTRAINT PK_Searches
        PRIMARY KEY
        IDENTITY(1,1)
    , searchString nvarchar(100)
    , timestamp datetimeoffset
        CONSTRAINT DF_Sarches_timestamp
        DEFAULT (SYSDATETIMEOFFSET())
    , CatID int NOT NULL
        CONSTRAINT FK_Searches_Category
        FOREIGN KEY
        REFERENCES dbo.Categories (CatID)
);

To insert a search in Category C:

INSERT INTO dbo.Searches (searchString, CatID)
VALUES ('This is a search', 4);

Query:

SELECT s.searchString
    , s.timestamp
    , Category = c.CategoryName
    , CategoryParents = (
        SELECT '/' + c1.CategoryName
        FROM dbo.Categories c1
        WHERE c.hid.IsDescendantOf(c1.hid) = CONVERT(bit, 1)
            AND c1.CategoryName <> 'Root'
            AND c1.hid <> c.hid
        FOR XML PATH (''))
FROM dbo.Searches s
    INNER JOIN dbo.Categories c ON s.CatID = c.CatID;

Results:

╔══════════════════╦══════════╦═════════════════╦════════════════════════════════════╗
║   searchString   ║ Category ║ CategoryParents ║             timestamp              ║
╠══════════════════╬══════════╬═════════════════╬════════════════════════════════════╣
║ This is a search ║ C        ║ /A/B            ║ 2018-03-08 15:29:25.2267243 -06:00 ║
╚══════════════════╩══════════╩═════════════════╩════════════════════════════════════╝

If you subsequently want to reparent C, and retain the old version of C, you do this:

INSERT INTO dbo.Categories (hid, CategoryName)
VALUES ('/1/2/', 'C');

Then, when you insert into the newly re-parented category, like this:

INSERT INTO dbo.Searches (searchString, CatID)
VALUES ('This is a search, but with a different parent', 7);

You get:

SELECT s.searchString
    , s.timestamp
    , Category = c.CategoryName
    , CategoryParents = (
        SELECT '/' + c1.CategoryName
        FROM dbo.Categories c1
        WHERE c.hid.IsDescendantOf(c1.hid) = CONVERT(bit, 1)
            AND c1.CategoryName <> 'Root'
            AND c1.hid <> c.hid
        FOR XML PATH (''))
FROM dbo.Searches s
    INNER JOIN dbo.Categories c ON s.CatID = c.CatID;

Results:

╔═══════════════════════════════════════════════╦══════════╦═════════════════╦════════════════════════════════════╗
║                 searchString                  ║ Category ║ CategoryParents ║             timestamp              ║
╠═══════════════════════════════════════════════╬══════════╬═════════════════╬════════════════════════════════════╣
║ This is a search                              ║ C        ║ /A/B/C          ║ 2018-03-08 15:29:25.2267243 -06:00 ║
║ This is a search, but with a different parent ║ C        ║ /A              ║ 2018-03-08 15:29:25.2546739 -06:00 ║
╚═══════════════════════════════════════════════╩══════════╩═════════════════╩════════════════════════════════════╝


Fuzzy searching? Any Alternatives? I have a drugs catalogue table which is regularly updated. I need to see whether these drug names (e.g. ‘Paracetamol 500mg’) are referred to anywhere within a lengthy text field (up to nvarchar (2000) characters long) in another  table called Logic.
There are no discernible joins or IDs between these two tables.
There are about 1500 drug names which regularly need to be checked against the text in the Logic table.
I have tried everything from fuzzy joins to soundex but am not getting the required results

select drugname
from #drugs t1
where exists (select logic from #logic t2
              where t1.drugname <> t2.logic
                and SOUNDEX(t1.drugname) =
                    SOUNDEX(t2.logic))

Is there a simple way to do this?
Thanks in advance

Based on your comments

  It needs to run with this logic "Does drugname 1 exist anywhere within
  the Logic rows? If so, return which row (it's location within is not
  important) Then repeat the same logic with drugname 2 etc etc. Thanks

&

  The Soundex is not working. I don't think it is suitable in what I am
  trying to do. I just need a query to search each drugname within the
  dataset to see if it exists anywhere in the logic table. Thanks

You could use CROSS APPLY + LIKE to only get the drugname's that have a match with the logic column. It won't be sargable but should get the job done. If you need to add the logic to identify the row, multiple of the same drugname's could be returned.

Query

SELECT drugname,A.logic
FROM #drugs t1
CROSS APPLY
(
SELECT logic
FROM #logic t2
WHERE t1.drugname <> t2.logic
AND t2.logic LIKE '%' + t1.drugname + '%'
)
AS A;

Test data

CREATE TABLE #drugs(id int identity(1,1) PRIMARY KEY NOT NULL,
                    drugname varchar(20));
INSERT INTO #drugs(drugname)
VALUES('Paracetamol 500mg'),('Paracetamol 1000mg');

CREATE TABLE #logic(id int identity(1,1) PRIMARY KEY NOT NULL,
                    logic varchar(max));
INSERT INTO #logic
VALUES('BlablaParacetamol 500mg'),('__Paracetamol 1000mg__'),('...');

Result

drugname    logic
Paracetamol 500mg   BlablaParacetamol 500mg
Paracetamol 1000mg  __Paracetamol 1000mg__

If you don't care about the logic column and only want one row for each drugname that has one or more matches.

SELECT drugname
FROM #drugs t1
CROSS APPLY
(
SELECT TOP(1) logic
FROM #logic t2
WHERE t1.drugname <> t2.logic
AND t2.logic LIKE '%' + t1.drugname + '%'
)
AS A;


Log shipping - failed over to secondary, how to fail back SQL 2012

I've been researching and testing Log Shipping as a DR solution. I got the two servers setup. Log shipping is in place and verified to be working correctly. I failed over by restoring the last translog and recovering the database. All this testing was done on VMs on my laptop. At this point everything looks good.

My question now is, how do you fail back? What if this were a true test of the DR in Production. Ok I proved it worked, the test is over, now I need to fail back. Is there a best practice?

Check this article -- Reversing Log Shipping! Now what you need to do is to take the log backup with NORECOVERY on the primary and restore this log on the secondary with RECOVERY. This will preserve the log chain.


How to identify fragmentation level of the table data itself not the table indexes, and then defrag Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself.
Also defragmentation solution is found for table indexes but for table data can not find.

I am not talking about physical fragmentation of the database files either, nor vertical/horizontal fragmentation of tables.

I have a database (SQLServer 2008 R2) with about 20 tables (among about other 20 tables) that are continuosly being inserted and deleted their records (which do not have indexes).  Those tables will cause fragmentation of themselfs and the other tables inside the same filegroup.

So even if I make the filegroup files big to hold the data and reduced physical fragmentation due to auto grow, the internal fragmentation eventually will be increased, generating performance drops.

I hope my question gets picked for an answer.

Tables in SQL Server can be either organised with a clustered index or have no CI in which case they are a heap.

You need to look at sys.dm_db_index_physical_stats. Despite the name this also does analysis of heaps too (though logical fragmentation does not apply to these, pages cannot be out of logical order as there is no "correct" ordering in a heap).

For tables with a CI the clustered index has an index_id of 1. The leaf level of the CI is the table. For a heap this is given an index_id of 0.


Task generated T-SQL shows unknown property IsMemoryOptimized I have modified a Maintenance plan on SQL Server 2008 using SSMS 2014, particularly Reorganize index task - I have added another two databases for this task.

When I wanted to check if the T-SQL is OK, the View T-SQL button shows the error:

  unknown property IsMemoryOptimized

Could (should?) that be fixed somehow?

Converting comment to answer so as to close this question:

Most probable reason that error has been witnessed in SQL server 2014, could be you are not updated to the latest cumulative updates.

  unknown property IsMemoryOptimized

has been reported as bug, may not be somewhat similar to you're situation, but CU has been suggested.

However, for now, please try to make the changes using the SQL server 2008/R2 and see if that works.


Is there a way to force sp_send_dbmail to use ANSI, or to not include the Unicode prefix? This is pretty trivial, but if someone has the answer handy, I'd appreciate it.

I use the msdb..sp_send_dbmail stored procedure as a lightweight way to distribute simple reports.  This often entails producing a CSV.  However, the stored procedure includes the Unicode little-endian prefix FF FE.  This confuses Excel, causing it to not automatically parse the CSV into columns.

There are a number of workarounds: use a real reporting platform (we have Crystal, but it's never worked reliably); send the file to a little app that converts the file to ANSI and sends it on; use tab delimiters (my favorite); or educate the recipients about how to work with CSVs (just kidding, we all know that's not possible).

However, I'd love to just flip a switch somewhere and tell SQL Server to stop creating the attachments as Unicode, as I have no such need. I haven't found such a setting yet; is this possible?

Thanks!

EDIT

Based on Rohan's answer, this script in a SQL Agent job step almost works:

$reportQuery = "EXEC Routine..TopTenReviewsReport @DoNotLog = 1"
$filePath = "\\SQLBI001\RawData\TopTenReviewsReport.csv"
$mailQuery = "
  EXEC msdb..sp_send_dbmail
  @profile_name = 'X',
  @recipients = 'X',
  @subject = 'X',
  @file_attachments = '" + $filePath + "'"

Invoke-Sqlcmd -query $reportQuery | export-csv $filePath
$result = Invoke-Sqlcmd -query $mailQuery

The file now has a "#TYPE System.Data.DataRow" prefix, instead of 0xFF FE, but it's now ANSI, so that's something.

I think you can generate the reports using powershell and then use DBMail to mail the attachments

Write-host "Running SQL Query to export to ANSI CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileAnsi
Write-host "Running SQL Query to export to Unicode CSV file"
Invoke-Sqlcmd -Query $dbQuery -ServerInstance $instanceName | export-csv $targetFileUnicode -encoding "unicode"

$dbMailQuery1 = "execute msdb..sp_send_dbmail
@profile_name = 'msft_profile',
@recipients = 'seths@microsoft.com',
@subject = 'Email with Attachment - ANSI attachment',
@body_format = 'TEXT',
@body = 'This email has ANSI file attached to it',
@file_attachments = 'c:\temp\result_ansi.csv' "

Source: http://blogs.msdn.com/b/sqlagent/archive/2011/07/06/email-query-output-as-ansi-unicode-file-attachment-using-powershell-and-database-mail.aspx


ORA-22285 Error while trying to register an xsd schema on oracle database Here is the code for user "system"

create or replace directory xml_mape as 'D:\app\Gusts\xml_mape';
GRANT READ,WRITE ON DIRECTORY xml_mape to lietotajs;

Im trying to access the xsd file "dok1_shema.xsd"on the directory "xml_mape"
And here is the code for user "lietotajs"

begin
  DBMS_XMLSCHEMA.REGISTERSCHEMA(
    SCHEMAURL => 'http://localhost:8080/public/dok1_shema.xsd',
    SCHEMADOC => bfilename ('xml_mape', 'dok1_shema.xsd'),
    LOCAL     => TRUE,
   GENTYPES  => FALSE,
   GENTABLES => TRUE,
   OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML);

  end;

I get the following error:

Error starting at line : 1 in command -
begin
  DBMS_XMLSCHEMA.REGISTERSCHEMA(
    SCHEMAURL => 'http://localhost:8080/public/dok1_shema.xsd',
    SCHEMADOC => bfilename ('xml_mape', 'dok1_shema.xsd'),
    LOCAL     => TRUE,
   GENTYPES  => FALSE,
   GENTABLES => TRUE,
   OPTIONS   => DBMS_XMLSCHEMA.REGISTER_BINARYXML);

  end;
Error report -
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 744
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 131
ORA-06512: at line 2
22285. 00000 -  "non-existent directory or file for %s operation"
*Cause:    Attempted to access a directory that does not exist, or attempted
           to access a file in a directory that does not exist.
*Action:   Ensure that a system object corresponding to the specified
           directory exists in the database dictionary, or
           make sure the name is correct.

Nothing i try to change seems to have any impact.

Unless specified otherwise, directory objects names are stored as uppercase strings, and you need to reference them like that in such cases:

SQL> !ls -l /home/oracle/hello.txt
-rw-r--r--. 1 oracle oinstall 6 Jan  9 14:05 /home/oracle/hello.txt

SQL> create or replace directory hello as '/home/oracle';

Directory created.

declare
  v_file bfile;
  ret integer;
begin
  v_file := bfilename('hello', 'hello.txt');
  ret := dbms_lob.fileexists(v_file);
end;
  8  /
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEEXISTS operation
ORA-06512: at "SYS.DBMS_LOB", line 803
ORA-06512: at line 6

declare
  v_file bfile;
  ret integer;
begin
  v_file := bfilename('HELLO', 'hello.txt');
  ret := dbms_lob.fileexists(v_file);
end;
  8  /

PL/SQL procedure successfully completed.

SQL>


Preloading a Java library in DB2, similar to ORACLE's loadjava Edit to add details to clarify, based on Chris' comment:

A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when deploying on the server it fails, since it cannot resolve these dependencies at runtime.

Does DB2 have any functionality to automatically preload a library / Java stored procedure?
I'm looking for something similar to ORACLE's loadjava:

loadjava -u myuser/MYPW -resolve mail-1.4.3.jar
loadjava -u myuser/MYPW -resolve aws-java-sdk-1.3.8.jar

Ok, what I believe you are after is creating Java routines (ie, stored procedures implemented in Java and stored within DB2).

You will want to follow the documentation in this section on Java routines. Pay special attention to the Creating, Building, and Deploying sections.

And even though this is an older article, you may wish to check out this article "Solve common problems with DB2 UDB Java stored procedures" on developerworks. It also discusses the when/where of using Java routines in DB2, how you go about creating them, and common problems encountered.


If XML child element value contains string return other child element value at same level I have a database table named Warehouses that contains rows of data related to specific warehouses. One column in that table is named itemStats and contains XML data in the following format:

<itemStats xmlns="http://www.blahblahblah.com/blah">
  <itemCounts>
    <item>
      <name>Toaster Oven Device</name>
      <count>6</count>
    </item>
    <item>
      <name>Washing Machine</name>
      <count>2</count>
    </item>
    <item>
      <name>Microwave Oven</name>
      <count>4</count>
    </item>
  </itemCounts>

I'd like to query the table to check that column in each row for the existence of some string (example: Oven) and if it finds it, return the count associated with it. So if I had the above data in itemStats  for a given row in Warehouses, I'd like it to return the following along with other matches from other rows:

Name                Count
=========================
Toaster Oven Device     6
Microwave Oven          4

I've attempted several times using SQL value() and nodes() but am having trouble producing the desired results. I know the [1] is wrong in the below query, but I'm not sure how to reference a dynamic location:

;WITH XMLNAMESPACES (N'http://www.blahblahblah.com/blah' as X)
SELECT itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:name)[1]', 'nvarchar(max)') as Name,
       itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:count)[1]', 'int') as Count
FROM Warehouses
WHERE itemStats.exist('(/X:itemStats/X:itemCounts/X:item/X:name[contains(., "Oven")])') = 1

Use nodes() and value() to get the names and count in a derived table and filter your rows in the main query.

declare @T table(itemStates xml not null);

insert into @T (itemStates)
values ('<itemStats xmlns="http://www.blahblahblah.com/blah">
           <itemCounts>
             <item>
               <name>Toaster Oven Device</name>
               <count>6</count>
             </item>
             <item>
               <name>Washing Machine</name>
               <count>2</count>
             </item>
             <item>
               <name>Microwave Oven</name>
               <count>4</count>
             </item>
           </itemCounts>
         </itemStats>');

with xmlnamespaces(default 'http://www.blahblahblah.com/blah')
select T.Name,
       T.Count
from (
     select I.X.value('(name/text())[1]', 'nvarchar(max)') as Name,
            I.X.value('(count/text())[1]', 'int') as Count
     from @T as T
       cross apply T.itemStates.nodes('/itemStats/itemCounts/item') as I(X)
     ) as T
where T.Name like '%Oven%';


Export subset of data for development environment I'm developing an application that is using SQL Server for storage. For development/testing purposes I need a subset of production data that will be anonymised, converted into insert into table.. statements and checked into version control.

So far I've been doing

select top 100 *
into tempTableName
from TableName

then removing any personal data:

update tempTableName set
firstname = 'Joe',
Surname = 'Doe',
Email = 'joe@doe.com',
etc...

Then generating scripts with data and tick only tempTableName.

That takes a fair chunk of time and error-prone - especially the bit where I remove personal data (think what would happen if I put wrong table name into the update).

Is there a better way to get sample data from production into scripts?

I've use the script in this post:
Updating multiple rows with random values from another table
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f58c3bf8-e6b7-4cf5-9466-7027164afdc0/updating-multiple-rows-with-random-values-from-another-table?forum=sqlgetstarted

Generate the random data (another table) first:
http://www.generatedata.com/


psql denies access to file when using COPY FROM When I do this:

COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV;

On this:

-rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv

psql tells me this:

ERROR:  could not open file "/my/file.csv" for reading: Permission denied

How can I read my file??  Thanks!

--- Update

It looks like something called apparmor is installed by default in Ubuntu.
Seems to have the same functionality as SELinux, mentioned in the comments.

--- Update

After removing apparmor, I still have the same problem.  selinux is not installed.

Regarding the comment below about access, the COPY FROM is being run from a superuser account (it gives a different error message if not) and the file permissions, copied above, I understand as "readable by everyone".

--- Update

I tried to get to the file under the postgres user.  It gets stuck at a particular place in the tree

drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm

postgres@dexter:/home/peter/PyPacks$ cd phm

bash: cd: phm: Permission denied

I suppose I will just put the file somewhere else, but this is strange!

drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm

postgres@dexter:/home/peter/PyPacks$ cd phm

bash: cd: phm: Permission denied

Directories need to be executable to be able to cd into them (or use files within them). All the sub-directory from / to the /home/peter/PyPacks/phm need to be executable by the user you want to use for this to work.

Try at least chmod 711 phm (which should give you rwx--x--x), or perhaps chmod 755 phm (rwxr-xr-x). r for directories is the ability to list their content, not to go into them (or use files/directories within them).


Tuning a query with: index, window function? Description

Environment: Centos 6-x64, Postgres Plus Advanced Server 9.3.
I have a table with 4 columns with data as below:

id    code_id            effective_date                 name
24      12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
430     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
466     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468     234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

And I want to get all row with condition: group by code_id and max(effective_date) < current_timestamp. So, the result I want:

id     code_id             effective_date                name
24       12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429      215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465      233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468      234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

My coding

create table win_a (
   id int not null primary key,
   code_id int,
   effective_date timestamp with time zone,
   name text
);

insert into win_a
select a,  a/2,  now() + trunc(random()  * 100) * '1 day'::interval, 'dtsc_' || (a/2)::int
from generate_series(1, 500) a
ORDER BY random() ;

create index win_a_indx on win_a using btree ( code_id, effective_date desc);

-- query 1
select a.*
from (
select id, code_id, effective_date, name
     , rank() over (partition by code_id order by effective_date desc, id) as rank
from win_a
where effective_date < current_timestamp
) a
where rank = 1 ;

-- query 2 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a
join (
    select code_id, max(effective_date) as max_ef
    from win_a
    where effective_date < current_timestamp
    group by code_id ) b
on a.code_id = b.code_id and a.effective_date = b.max_ef;

-- query 3 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a
where (code_id, effective_date) in
     (select code_id, max(effective_date) as max_ef
    from win_a
    where effective_date < current_timestamp
    group by code_id );

Question

Can I use index with window function? (with query 1, I try an index but Postgres still uses a seq-scan instead of an index-scan)
How can I improve my coding?

Simplest with DISTINCT ON - and also fast for data distribution with few rows per code_id:

Select first row in each GROUP BY group?

SELECT DISTINCT ON (code_id) *
FROM   win_a
WHERE  effective_date < current_timestamp
ORDER  BY code_id, effective_date DESC, id;

And add a NOT NULL constraint to the column effective_date or you have to make that:

...
ORDER  BY code_id, effective_date DESC NULLS LAST, id;

And adapt the index accordingly:

CREATE INDEX win_a_idx on win_a (code_id, effective_date DESC NULLS LAST, id);

Explanation:

Unused index in range of dates query

Note also that I added the id column!
However, Postgres may not use the index at all. For few rows per code_id, and while you select all of them, a sequential scan may be fastest in any case.

For many rows per code_id other query techniques may be faster:

Optimize GROUP BY query to retrieve latest record per user

For your case - unless you have a separate table holding all possible code_id:

WITH RECURSIVE cte AS (
   (  -- parentheses are required
   SELECT *
   FROM   win_a
   WHERE  effective_date < current_timestamp
   ORDER  BY code_id, effective_date DESC NULLS LAST, id
   LIMIT  1
   )
   UNION ALL
   SELECT w.*
   FROM   cte c
   ,      LATERAL (
      SELECT *
      FROM   win_a
      WHERE  code_id > c.code_id  -- lateral reference
      AND    effective_date < current_timestamp -- repeat condition
      ORDER  BY code_id, effective_date DESC NULLS LAST, id
      LIMIT  1
      ) w
   )
SELECT *
FROM   cte
ORDER  BY code_id;

This is actually going to use the index.

As for your question 1: a test table with just 500 rows is not very useful. Postgres will use a sequential scan in any case. An index is not going to be faster for a small table like that.


Connecting to an SQL Azure Database from SSMS - IP Restriction I am trying to connect to a new SQL Azure Database via SQL Server Management Studio. The client box running SSMS is hosted on a production DB so we restrict access on 1433 to just a handful of IPs using IP Sec. The client IP is configured on the Azure Portal for access to the Azure DB. I can only get access to the Azure DB from the client by opening 1433 publicly, which I'm not keen to do on account of their being a production DB on there.

I appreciate the cloud does not provide us with the certainty in terms of stable IPs. But we're only using this connection to perform some various admin tasks and we're happy to change the IP from time-to-time, as required.

But how can I know what IP Azure is responding on? When I ping {myserver}.database.windows.net I get an IP but when I add this to the IP Sec rule it still doesn't work. Is Azure responding on a different IP? If so how can I come to know what this IP address is as SSMS only throws a generic network error?

Additional Information:

The box running SSMS is not hosted within Azure. We are currently running this from another datacenter

You don't want to connect to Windows Azure SQL Database by using an IP address, you will want to use the logical servername you are assigned (something like fooxxxbar.database.windows.net).

Also, WASD only uses port 1433, you can't configure that (not today, anyway).

The firewall rules for WASD are for the client connections. When you first create your SQL database you will be prompted to create firewall rules and your current IP address will be listed as an example of an IP to allow.

HTH


Are we a good fit for SQL Server Enterprise and table partitioning? We currently have a production database that contains millions of records (14 million in the largest table).

One function of the database is to provide time sheet information so a single table could have 2 million time sheets but be joined across 5 other "detail" tables and each of those detail tables have between 500k and 14m records.

Also, we are running some legacy software that cannot be changed or replaced anytime soon.  The problem we are having is that there are some queries (also legacy and cannot be changed) that unfortunately are performing massive table scans.  So looking up 100 time sheets is causing millions of rows to be scanned.

I'm not a DBA by trade but I believe I have indexed everything I can possibly index with as much logic and research as I know how.  Plus I believe I have setup proper re-indexing jobs, etc.

We are running SQL Server 2008 (10.0.5500.0) on a machine with 32GB RAM (16GB devoted to SQL Server).  There isn't much else on the machine so most of the resources are devoted to SQL Server.

Our next plan is to possibly add more RAM to the machine and maybe bump the RAM used by SQL Server to 32GB.  Our database size is around 56GB on disk.

My question is, do you think we should look into upgrading to SQL Server 2014 Enterprise so that we can partition our time sheet tables?  I'm new to table partitioning but from what I understand it could be used to perhaps partition our tables by site and year?  So that 14m rows would be split out by 10-20 sites and then further split by year so that queries would scan 10k-20k rows instead of 14m.  Or am I just totally missing the mark here?

Any other advise would be welcomed.

Thank you.

My question is, do you think we should look into upgrading to SQL
  Server 2014 Enterprise so that we can partition our time sheet tables?

No. Absolutely not. On a pocket change server of 8 cores you would be spending ~$50k and be unlikely to see any benefit.

I would suggest trying, in this order:

Increase the memory allocation to SQL Server. You said the server is dedicated but have only half the RAM allocated. 32GB server, allocate 28GB.
Upgrade the server memory to [size of database] + 4GB.
Post the problem queries on dba.se.
Engage an expert.


what is the downtime of mongodb cluster when upgrade to 3.0 I am following  http://docs.mongodb.org/manual/release-notes/3.0-upgrade/ to upgrade mongodb cluster from 2.6 to 3.0. The downtime is less than 1 second? because it takes less than 1 sec to elect one secondary to become primary. Am I right? I need to double check the whole process and the downtime...

thanks.

If you follow the upgrade process it is designed to avoid downtime -- for example using rs.stepDown() to gracefully step down the replica set primaries rather than abruptly triggering an election by shutting down the server.

Of course there is no guarantee if something doesn't go to plan, but you should test your upgrade in a QA/staging environment before upgrading production.


How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time? I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected data. This post is related to this other question (from me too).

So basically I have 3 tables:

default_recipient: stores user_id and message_id for not to repeat messages at default_messages table so when I reply to a message or send multiple users messages user_id and message_id references are stored at this table
default_messages: stores messages fields among sender user_id
default_profiles: stores fields related to users

Since the message is just one for sender and for receiver, then when one of the sides wants to delete and the other does not, then messages can't be physically deleted. So I mark the message with a flag deleted = 1.

If the same message_id has deleted = 1 in default_messages and also in default_recipient, then the message will be deleted physically because both parts sender and receiver deleted the message from their inbox.

Now I have these queries just for testing and see results:

1) select sender_user_id, deleted from default_messages;
2) select * from default_recipient;
3) SELECT DISTINCT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE msg.sender_user_id = 1 OR rcp.user_id = 1
        AND msg.deleted = 0
        GROUP BY (p.id)
        ORDER BY msg.date DESC;
4) SELECT rcp.user_id as rcpuid, rcp.message_id as rcpmid, msg.sender_user_id, msg.id as message_id FROM default_messages msg
        LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
        LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
        WHERE (msg.sender_user_id = 1 OR rcp.user_id = 1)
           AND (msg.deleted = 0 OR rcp.deleted = 0)
        ORDER BY msg.date DESC;

What I'm trying to do is get all messages from a specific user where the message aren't deleted. So taking as example user_id = 1, then I need to get those messages where default_messages.sender_user_id = 1 OR default_recipient.user_id = 1 AND default_messages = 0 AND default_recipient.deleted = 0 meaning: messages for user = 1 where deleted = 0.

Now with my data as you can see here and with queries 3 and 4 the results aren't correct and I'm looking to fix this query.

Any help is appreciated

The problem is query #4 needs a small adjustment. Now the query is:

SELECT p.*
    , rcp.*
    , msg.*
    , msg.id as message_id
FROM default_messages msg
    LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
    LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
WHERE (msg.sender_user_id = 1 AND msg.deleted = 0)
       OR (rcp.user_id = 1 AND rcp.deleted = 0)
ORDER BY msg.date DESC;


Remove duplicate terms from column This the tags column where all terms should be listed only once. Would like to remove all additional words from this column. For example the below contant:

  Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Features, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Cuisines, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Services, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Meal For Two, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Last Order/Booking (P.M), , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, BookUrTableId,

Should show as below:

  Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, East, West, Malad East, Dafftary Road, Mumbai, Maharashtra, Features, Cuisines, Services, Meal For Two, Last Order/Booking (P.M), BookUrTableId

Where i tried to run the query and insert this into a table it gave an error as the content in this column was going too long and stops the execution of the query.

Also it would be preferred if i can get this done in mysql rather than using php or any other programming language.

Please help.

As has been mentioned, the database is perhaps not the best place to accomplish this; however, it can be done, using a stored function.  You wouldn't want to rely on this in a live environment, bit it works.

If the following seems a bit primitive, that's an indication of the somewhat poor match between tool and task.

DELIMITER //

DROP FUNCTION IF EXISTS `dedup_csv` //
CREATE FUNCTION `dedup_csv` (in_str LONGTEXT) RETURNS LONGTEXT
DETERMINISTIC
NO SQL
BEGIN

-- http://dba.stackexchange.com/questions/87144/remove-duplicate-terms-from-column
-- given a comma-separated string of values, return a comma-separated string of
-- unique values found in the list

DECLARE out_str LONGTEXT DEFAULT NULL; -- pending output
DECLARE next_str TEXT DEFAULT NULL;    -- next element under consideration

dedup:
LOOP

  IF CHAR_LENGTH(TRIM(in_str)) = 0 OR in_str IS NULL THEN
    LEAVE dedup; -- no more data to consider
  END IF;

  SET next_str = SUBSTRING_INDEX(in_str,',',1);                   -- find the next element
  SET in_str = SUBSTRING(in_str FROM (CHAR_LENGTH(next_str) + 1 + 1)); -- remove that element

  SET in_str = TRIM(in_str), next_str = TRIM(next_str); -- trim the new and the rest

  IF FIND_IN_SET(next_str,out_str) OR CHAR_LENGTH(next_str) = 0 THEN -- if empty or already found
    ITERATE dedup;
  END IF;

  SET out_str = CONCAT_WS(',',out_str,next_str); -- append the new to pending output

END LOOP;

RETURN out_str;

END //

DELIMITER ;

See this live demo: http://sqlfiddle.com/#!9/f32ee/1

The output from this function, given the input above:

Lakshmi Bar & Restaurant,Dining & Leisure,Restaurants,Restaurants- Multi Cuisine,Hotel,
East#West,Malad East,Dafftary Road,Mumbai,Maharashtra,Features,Cuisines,Services,Meal For Two,
Last Order/Booking (P.M),BookUrTableId

Leading and trailing spaces and empty values are cleaned up automatically.

mysql> SELECT dedup_csv('foo,bar,,,   ,baz,foo,  foo  , bar') AS deduped;
+-------------+
| deduped     |
+-------------+
| foo,bar,baz |
+-------------+
1 row in set (0.00 sec)

Again, the database not being the ideal tool is reflected in the benchmarks, which are highly dependent on input length and number of elements.

On the short and simple string shown above, the actual runtime on MySQL 5.5 on a 1 GHz Opteron 1218 is ~ 150 µsec, while the long string in the original question requires about 2400 µsec to process... which works out to a fairly substantial amount of time for large data sets.


Load New Foreign Key in Facts Table, When Update Dimension with SCD Type 2 I'm working on the production of a Data Warehouse with SSIS, and today we create the first dimension with SCD of type 2. The fact table is linked in the DIM_CIPESSOA dimension through the idcipessoa column.

When updating the dimension, as shown below, a new PK is generated and with this we need to update the FK in the fact table to connect the lines correctly.

Dimension Table:

idcipessoa  cgccpf_AltKey  tppessoa nomepessoa        effectiveDate            expiryDate               IsCurrent
----------- -------------- -------- ----------------- ------------------------ ------------------------ ---------
88393       00000000001    1        CUSTUMER OLD      2018-04-02 11:52:47.040  2018-04-02 22:29:05.243  0
169852      00000000001    1        CUSTOMER UPDATED  2018-04-02 22:55:08.540  9999-12-31 23:59:59.997  1

Fact Table

idFact  idcipessoa  tppessoa
------- ----------- --------
1       88393       1.99

So, as in the example above, we need a strategy to align the fact table to be pointed to the new new key 169852.

No, you wouldn't update the fact table.

The fact record is tied to the version of the customer which existed between 2018-04-02 11:52:47.040 and 2018-04-02 22:29:05.243 in which they had a 1.99 tppessoa

If they generated a 3.1 tppessoa  at 2018-04-03 00:00:00.000 then your fact table would look something like

idFact  idcipessoa  tppessoa
------- ----------- --------
1       88393       1.99
2       169852      3.1

Then you can see the activity for this customer as they have changed over time.

If you wanted to have the fact table's idcipessoa updated as the dimension changes, that's not a type dimension.


SQL Server - How to grant SELECT permission on View that access data in schemas from differents owners? I need help to deal with a problem about permissions in my database.

This is the scenario:

1 Database

4 Schemas:

schemaA; owner dbo
schemaB; owner ownerX
schemaC; owner ownerX
schemaD; owner ownerX

In schemaD I have a view named viewABC, that gets information from tables and views allocated on schemas schemaA, schemaB e schemaC.

A user userX have SELECT permission for viewABC.

With the following command I grant permission:

GRANT SELECT ON schemaD.viewABC TO userX;

When userX try to execute a SELECT against the view, this way:

SELECT * FROM schemaD.viewABC;

We get the error:

  The SELECT permission was denied on object 'tableA', database
  'MyDatabase', schema 'schemaA'.

I do understand that the error happens because tableA is on a different schema, with a different owner (dbo instead ownerX), and SQLServer applies userX permissions to grant or deny access. Once userX don´t have explicit access to tableA the query execution returns error.

How to solve this without grant SELECT permission on tableA for userX?

Based on one of the suggestions that Dan Guzman gave me in your answer, and after read this question I found a workaround to my problem.

Apparently, there is no way, in scenario as described, to grant to userX SELECT permission in the viewABC without grant also SELECT permission on tableA.

The solution that I found was the following:

in schemaD I created a Table-Valued Function that return the same record set of viewABC - despite ownerX isn't owner of schemaA, he has SELECT permission on tableA
the EXECUTE AS clause was used to guarantee that any execution of the function will use ownerX permissions - in this context doesn't matter the permission that userX has on tableA
to userX was granted SELECT permission in the created function - not EXECUTE permission, since the function return a table

The Table-Valued Function sample code:

CREATE FUNCTION schemaD.udfABC ()
RETURNS @tabABC TABLE (
  fieldA INT NOT NULL, fieldB INT NOT NULL, fieldC INT NOT NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN
  INSERT INTO @tabABC (fieldA, fieldB, fieldC)
    SELECT a.fieldA, b.fieldB, c.fieldC
      FROM schemaA.tableA a
     INNER JOIN schemaB.tableB b ON a.id = b.idA
     INNER JOIN schemaC.tableC c ON b.id = c.idB;

  RETURN;
END

Granting SELECT permission:

GRANT SELECT ON schemaD.udfABC TO userX;

Now userX can use this command to get data:

SELECT * FROM schemaD.udfABC();


Can I place the attribute in the Entity, when the foreign key is also already present? I am using DBdesigner Fork for generating ER diagram.

I have the 'Company' as an entity for which can I place the attribute of 'idShares', despite of having the foreign key of Shares Entity in the Company entity.

As, it can fetch the Share entity attributes automatically from the foreign key.
So, Can I place the attribute in the Entity, when the foreign key is also already present?

If I saw that, I might expect that there are situations where the automatic attribute and the separate but seemingly identical attribute might actually be different, and this would be confusing. If the foreign key that is used to automatically get the attribute is already present on the entity, I am not sure it's necessary to add that same attribute on the entity - it seems like it would be redundant and could lead to confusion.


Where/How to generate the validation key and decryption key for SSRS View State Validation According to this Technet article, I need to :

Generate a validation key and decryption key by using the autogenerate functionality provided by the .NET Framework.

The step does not explain how to generate the validation key and decryption key.

You can generate the machine key through IIS Manager.

Use Server Manager -> Roles -> Web Server (IIS) -> IIS Manager. Click on the Web Server in the IIS Manager, then in the right pane, double click Machine Key in the ASP.NET section.