How do you index alphanumeric foreign keys on large fact tables? I have a field that is an alphanumeric field that ideally is a encrypted field for a non-unique identifier. It's used to relate other rather large fact tables in a many-to-many relationship. I do not have the related dimension for this field because there is no other attributes for this FK.

Example: Abcdefgh12345

This field is in a rather large and growing data warehouse where the Fact tables are clustered on time and non-clustered on keys like these.

The column is VARCHAR(50) and only vary between 45 and 50. Would have to check, but I would assume the collation is SQL_Latin1_General_CP1_CI_AS. I use no FK for optimization reasons. All controlled by the ETL.

Fragmentation

Due to the type of key, it makes it hard to index. It's fragmentation is managed by a series of tests I did recently that showed 75% fill factor at least manageable with reducing the fragmentation on daily incremental loads for a least a week until a full rebuild may be needed, which is fine once a week.

Performance

With the reducing of the fill factor from 100% to 75%, the inserts and reads have become slower. The records have grown bigger as expected too. Any indexes with includes are pretty much driving the performance into the ground on inserts, but of course helping queries out that need them 10x better.

Question

Does anyone have any good experience working with alphanumerics in a data warehousing environment? How it's handled and index now is fine, but I think it could be better. I was dabbling with the idea of ripping out the keys, forming a new dimension and adding a more manageable key during the ETL process.

Assuming that your assumption is correct regarding the VARCHAR(50) field using a collation of SQL_Latin1_General_CP1_CI_AS, then you should consider altering those alphanumeric "code" fields in each of the tables where it exists, to have a collation of Latin1_General_BIN2. Since the value is derived from an algorithm, the casing of any alpha characters should be consistent so you don't need to worry about case insensitive searching. Using a binary collation will perform better than a non-binary collation (case insensitive or even case sensitive) since it doesn't need to handle any culturally-aware linguistic rules.

Also, since you have tried FILLFACTOR settings of both 100 and 75 and see pros and cons in each case, you should try a setting of 90 to see if that helps.

I would try each of those changes separately (first one, then add the other) so that you can test their effects individually. That way you know how impacting each change was.

I think in general the system would benefit from replacing the 45 - 50 byte key with a 4 byte key (assuming you use INT since this is dimensional data, though even an 8 byte BIGINT would be an improvement). But, adding a new dimension table and switching out the VARCHAR(50) to be an INT (or BIGINT) requires changes in both the data model and the code, whereas the first two changes affect only the data model and are fairly minimal at that.


LSN numbers query regarding Differential backups We have the following backup regime in place which I am not 100% sure about.

Full backup each night at 18:00 taking approx. 6 hours
Transaction log backup upon completion of full generally about 10 mins.
Differential backups each hour taking approx. 20 mins.

When querying the msdb.backupset table, I understand that the first_lsn will only be reset by a transaction log backup, but am slightly confused that the last_lsn entry is incremented each time the differential backup runs.

This seems to imply that the differential backup also contains transaction log entries (which I don't believe it does).

Is this entry in msdb.backupset just to give information to a restore to which transaction log backup to use to preserve the chain of transactions ?

Finally, if we only have a transaction log backup once a night and differentials throughout the day, we are still looking at potentially a maximum of 24 hours data loss despite hourly differentials.

Apologies if this seems like a lot of questions but trying to get this clear in my head.

Thanks for listening.

While not exactly answering your LSN questions, I think you definitely need to revisit your Recovery Point Objective (RPO).  How much data are you willing to lose?  Assuming good hourly differentials, you should be able to restore back to the latest hour (currently), but you wouldn't really be able to take advantage of any transaction log backups. I'd definitely suggest increasing the frequency of transaction log backups - maybe every 15 minutes.  If they take 10 minutes to complete, then that would most likely be the most data you'd lose - assuming your other backups are good (full and diff).

All backups have enough of the log to be able to restore a consistent database by applying committed changes after the backup started and rolling back transactions that had not completed before the backup completed.

Check out Paul Randal's excellent post on Understanding Logging and Recovery in SQL Server.


Find ownership for a given temp table? Is there a good way to find out which user and process owns a given user created temp table in tempdb?

You can use this query, courtesy of Paul White:
SELECT SUSER_NAME(CONVERT(INTEGER, OBJECTPROPERTYEX(t.OBJECT_ID, 'OwnerId')))
FROM tempdb.sys.tables AS t
WHERE t.name LIKE N'#TempTableName%';


After running VBA, I lose exclusive access to the database I wrote some VBA to import an excel spreadsheet easily into a table in my database. This way, the user just clicks a button on a form, and answers some questions about the file to be imported. The problem is that after the import is completed, I try to modify anything else in the database and I'm presented with the message "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." I then have to exit the database and reopen it to make any changes.
I've done this before without problems on other versions of Access. So I don't know if I have a typo someplace, or something has changed from Access 2013 to 2016 that's causing the problem.
Currently, the Database is located on my local machine. Ultimately, this will be moved to a SharePoint site and split into 2 access files. I don't want to do that until I have it mostly working because I don't have anyplace I can upload to SharePoint without a lot of people having access to it.

Private Sub ImportNewTB_Click()
On Error GoTo ImportNewTB_Click_Err 'If an error occurs anywhere along the way, make sure you still clean up the memory before quiting

Dim OwssvrFile As DAO.Database 'This is the open connection to the file
Dim OwssvrInfo As DAO.Recordset 'This is the recordset for the teachers information
Dim fileName As String 'This is the name of the file being opened
Dim dbs As DAO.Database
Dim CurrentTBDB As DAO.Recordset 'This is to make a connection to our current table
'Dim ExcelHdrs(0 To 20) As Variant 'This is an Array with the headers from the Excel file.
Dim numRecords As Integer
Dim WksName As String
Dim TimeStamp As Date

fileName = getOpenFile() 'Use the function I built in the Module
If fileName = "" Then GoTo ImportNewTB_Click_Exit 'If they didn't select anything, then just give up on life and exit

WksName = InputBox("Enter the name of the worksheet: ", "Worksheet Name", "owssvr")
numRecords = InputBox("Enter the number of records in the Worksheet: ", "Num Records", 2412)
WksName = WksName & "$A1:BE" & numRecords + 1

'Once we have a real file, open it already
Set OwssvrFile = OpenDatabase(fileName, False, True, "Excel 12.0; HDR=YES;")
'Create Recordset from the excel file.
Set OwssvrInfo = OwssvrFile.OpenRecordset(WksName)
OwssvrInfo.MoveFirst 'Goto the first line of the recordset

Set dbs = CurrentDb
Set CurrentTBDB = dbs.OpenRecordset("SELECT * FROM CurrentTB")

TimeStamp = Now()

Do
    With CurrentTBDB
        .AddNew
        .Fields!EntryDate = TimeStamp
        .Fields!ProjectName = OwssvrInfo.Fields(0)

        .Update
    End With

    OwssvrInfo.MoveNext 'All of that for entry 1, only 2000 more lines to go
Loop Until OwssvrInfo.EOF

' Tidy up, This closes everything out and releases the memory
ImportNewTB_Click_Exit:
On Error Resume Next 'Basically this says, if there's an error, I don't care, do this anyway
MsgBox "Input Complete!"
OwssvrInfo.Close
OwssvrFile.Close
CurrentTBDB.Close
Set CurrentTBDB = Nothing
Set dbs = Nothing
Set OwssvrInfo = Nothing
Set OwssvrFile = Nothing
Exit Sub

ImportNewTB_Click_Err: 'This produces an error message if one exists
MsgBox Err.Number & " " & Err.Description, vbCritical, "Error!"
Resume ImportNewTB_Click_Exit 'Make sure we still clean up before leaving
End Sub

To make a long story short... Run Compact and Repair. I thought I had already attempted that. Perhaps I had before I read Scott's post. But after fixing that and then Running Compact and Repair, everything is working exactly the way it should be. I don't typically have to do that for the first time on a database until I've done a LOT more work, I'm guessing because this Database has close to twenty thousand entries, and most of the ones I work with have less then 1000, it makes a huge difference. Lesson learned. Always learning, always growing.


Is there anything wrong with linking two identical primary keys in two different tables? As you can see here, in OrderInfo and OrderNumber, I have a relationship set. But I'm not sure what kind. I was hoping to set a relationship so if I deleted an OrderNumber, it would cascade delete the corresponding record in OrderInfo, but it won't let me. It gives me this nasty '

  can't cascade delete on identity column

error, so after removing cascade delete, it let me save it appropriately. I'm not sure what key to key means, but I know key to infinity is one to many?

It looks like your model is a one-to-one relationship between OrderNumber and OrderInfo, with OrderInfo.OrderID as the OrderInfo primary key and as a foreign key referencing OrderNumber.OrderID.

The error messages suggests you have defined OrderInfo.OrderID as an IDENTITY column. It should not be IDENTITY - the assigned OrderID value from thre related OrderNumber row should be used when inserting the related OrderInfo row to maintain the foreign key relationship. That should allow you to specify DELETE CASCADE.


Which fillfactor is used by ALTER INDEX REBUILD? From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen.

For tables I know to get rows added fillfactor 0 isn't a good choice. Is there a way to rebuild an index, so that the last explicitly index is used ?

BTW: I think that that should be the default setting.

When a new index is built is uses the default fill factor by default, unless a different fillfactor is specified.

When an index is rebuild it will use the same fill factor that was specified when the index was build or last rebuilt unless a different fillfactor is specified.


Adding a database with Service Broker to an existing Availability Group on a new replica Server 1 - SQL Server 2016 (Primary in existing AG)
Server 2 - SQL Server 2016 (Secondary in existing AG)
Server 3 - SQL Server 2016 (Offsite Secondary in existing AG)
Server 4 - SQL Server 2017 (New server, destined to become new primary)

An ~800GB database is happily running on the 2016 AG. It has Service Broker enabled and is running fine.

The plan is to restore the database onto Server 4, add it to the AG, then manually failover so Server 4 is the primary in order to upgrade Servers 1, 2 & 3 to SQL 2017.

Server 4 has a different drive configuration to the other Servers so in order to add the database to the AG, I am using dba-tools Restore-DbaDatabase command. The database must be left in NORECOVERY mode in order to finally add it to the AG.

The problem here is that Service Broker must be enabled via ENABLE_BROKER on the DB before it is added to the AG whereas this is not possible because of the NORECOVERY state of the DB. Also SET TRUSTWORTHY ON must be set.

Is there a way to achieve this without tearing down the whole AG?

I'm really trying to avoid this because it takes a long time to copy and restore backups onto the offsite server (Server 3) when initially joining the AG.

This guide has proved useful for the rest of the process.

Both SET TRUSTWORTHY ON and ENABLE_BROKER are database settings. When you backup and restore a database, these settings will be included in the bak-file and can be restored on the new SQL instance.
Probably, you'll need to make some server level configurations though, such as a service broker endpoint as described here

Edit: Just to clarify, when restoring the backup you don't need to specify the ENABLE_BROKER option. The broker settings will be synced from the primary replica as soon as you join the database to the AG. You only need to restore the database using NORECOVERY.

On a side note, when you add your SQL Server 2017 instance to the AG and failover to it you won't be able to fail back. This leaves your AG with a single point of failure (the new primary replica).
Consider upgrading all your secondary replicas before failing over to your new instance. After the failover, upgrade your (previous) primary replica. More information about rolling upgrades can be found here


SSDT table column order I realized that the behaviour of SQLPACKAGE.EXE changes depending on what changes have been made to a table schema:

Adding a new column at the end of the table: "Table is changed"
Removing a column from table: "Table is changed"
Adding a column in between existing columns or just changing their order: "The recreation of the table was started"

(I did the translations myself, they maybe inaccurate but similar).

Step No. 3 takes forever during publishing process. I wonder why and what exactly is going on. I suspect the table structures are recreated entirely. I would not want this, I woul`d basically not care very much about the columns orders in the different target systems. Is there any way to prevent this time consuming process?

Edit:

Meanwhile I found that many people are struggeling with the same problem. SSDT does not have any option to ignore the column order of the target tables. That makes it re-arrenge all the data as described above. This is time consuming and entirely useless, but there is no choice if you want to use SSDT.

See & please upvote here:

Visual Studio Uservoice Item

Microsoft Connect Item

MSDN Forum Discussion

You are correct, the only way to "rearrange" the order of the columns is to create a table with the new structure and push the old data into it, drop the old table and then rename the new table (or some variation of that).  It requires copying all the data in the table and some drops and renames.


I have deleted a table, by exporting over the same table, and selecting the 'drop destination table before insertion' I understand this might not be the best forum, but I could sure use some help right about now. If not, please suggest a forum where I can.

I was trying to backup a table, by using the export wizard. I accidentally seleected the same table as source and destination. Add insult to injury, I chose the drop destination table before inserting option. Needless to say, I now have an empty table.

I am sure there are (must?) be some backups, but the support team is not available to help right now. Is there anything I can do to recover the said table?

Thank you.

As it turns out, we did have backups. I restored the backup on another server, and just copied the required table to the production database. Of course, I did it during such a time, where I was sure that no new records would have been saved. Just to be sure, I stopped the web application before doing this.

The idea came from the answer in the question that was linked by Darin Strait in the comments.


Triggers vs Views in MySQL We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the necessary data is stored/cached alongside in the initial table.

I am a strong advocate of triggers that would help me keep all the data up2date absolutely transparently. There are other decision makers in the company who are against using triggers (for several reasons) and suggest building a VIEW instead in hopes that the VIEWs are already optimized for better data retrieval.

Is this the case in MySQL?

This depends on the MySQL version that you intend to use.
If your view is merely a Join of 10 tables, without any grouping and aggregate calculations, why do you need de-normalization at all?
If your view involves grouping and aggregates, you'll have to test the views performance against the bare queries and against a denormalized design with triggers. There is nothing better than testing with your data and your queries. However, as MySQL Performance Blog article suggests: MySQL VIEW as performance troublemaker, views are more of a way to write compact queries and reuse of code than a performance improvement. Occasionally they even have a negative impact on performance:

It is also very dangerous if you assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts.

But note that this a rather old article (2007) and it certainly doesn't take into account the recent improvements on optimizer, made in MariaDB 5.3 and 5.5 (which can be used as drop-in replacements of MySQL 5.1 and 5.5). This section is relevant to your question:

Optimizations for derived tables and views

No early materialization of derived tables (e.g. subqueries in a FROM clause) and materialized views (EXPLAIN is always instantaneous)
Thanks to Derived Table Merge optimization, mergeable derived tables are now processed like mergeable VIEWs.
Derived Table with Keys optimization gives the optimizer an option to create indexes over materialized derived tables
Fields of merge-able views and derived tables are involved now in all optimizations employing equalities

Another feature that  MariaDB has implemented is VIRTUAL PERSISTENT columns which can be helpful for having certain calculations stored in tables, without using the (evil) triggers.
(Sidenote: one problem in MySQL with triggers is that when mixed with cascading Foreign key constraints, they are not activated by cascading actions MDEV-11472).
MySQL 5.6 also has improvements on the optimizer regarding views and derived tables but I have no idea when a stable release will be out.
To summarize, I think you should carefully consider which version you will implement your database on and if possible test the various design options in the available versions (MySQL 5.1 (and 5.5), MariaDB 5.3 (and 5.5), MySQL 5.6(under development)).


MySQL Row size too large (> 8126) After I upgraded MySQL from 5.6 to 5.7 on Ubuntu 14.04, I started to have this problem on certain action. As I understand, the problem is that I am trying to get row that is too big from the table.
So, I managed to solve this problem by limiting columns on select part which was originally using select *.

However, I want to solve this problem by changing the setting. (Because I might be able to face a situation where many columns for select are required)

I have tried to change

innodb_strict_mode = 0
max_allowed_packet = 512M
innodb_log_file_size = 128M
innodb_log_buffer_size = 256M
innodb_file_per_table = ON

all these values but without any luck.

Any advice or suggestion would be appreciated. Thank you

Plan A: (see answer by @hmikael)

Plan B: Change innodb_page_size to 32K.  However, this is a non-trivial task since it applies to all InnoDB tables.  Doing so would change the limit to ~16KB.

(Note: going to page size of 64KB won't get past 16KB limit per row.)

Plan C: Switch to MyISAM.  This is not advised for various reasons.

Plan D: build a "parallel table" or "vertically partition".  That is, split off some of the columns into another table.  Suggest moving rarely-used and/or bulky columns into the new table.  Both tables would have the same PRIMARY KEY (though at most one would say AUTO_INCREMENT).

Plan E: Shrink datatypes where practical.  That is, don't blindly use VARCHAR(255).  (This will also help with performance in a few situations.)  Don't use a 4-byte INT for a true/false flag; use a 1-byte TINYINT.  Etc.

Plan F: Normalize common strings.  (A 3-byte MEDIUMINT UNSIGNED is usually a lot smaller than a string.)

Plan G: Don't splay an array across columns.

(Some "Plans" can be used together.)


Paging in SQL Server: Is an order by clause required? (currently, rows are being missed) We have some code that pages through a SQL result. (Currently running on SQL Server 2008)

We notice that when paging is done, some rows are not returned. Let me clarify this a bit:

nHibernate generates SQL queries.
We are doing paging.
If we page by 100, the way nHibernate generates the successive SQL queries is:

TOP 100  // gives us first 100
TOP 200  // gives us 2nd 100 of this block
etc

The above, without an ORDER BY / sorting at nHibernate level, has the end result that some rows never surface to nHibernate. We surmise this is due arbitrary sorting by SQL, so that rows are "moving around" within the pages (and thus "hide" from our application code).

If we do the nHibnerate query as a single shot (returning all rows), we see all the data. (This query below is generated by nhibernate.)

Will adding an order by clause (aka nHibernate sorting) help?

SELECT top 33
 ... <field list>
FROM
  salesOrder this_ left outer join [Item] Item2_ on this_.ItemId=Item2_.ItemId
WHERE this_.AccountId = @p0
and this_.ModifiedAt > @p1
and this_.ModifiedAt <= @p2

Using TOP(n) without ORDER BY is going to return you n rows in whatever way Sql Server cares. As per the documentation:

  If the query has no ORDER BY clause, the order of the rows is arbitrary.

In addition, there is no WITH TIES. Consider using it too, lest you might miss some rows too:

  WITH TIES  Specifies that additional rows be returned from the base
  result set with the same value in the ORDER BY columns appearing as
  the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified
  only in SELECT statements, and only if an ORDER BY clause is
  specified.

  The returned order of tying records is arbitrary. ORDER BY does not
  affect this rule.

Consider update to Sql Server 2012/2014. You could enjoy new features like automatic paging with OFFSET FETCH.


Combining query results in mongodb I have a collection invites which i am using to store some invite data.Below are two rows of data i captured from my collection invites

{
        "_id" : "n4nECpQFnBFiKkt6N",
        "createdAt" : ISODate("2016-10-11T11:59:16.988Z"),
        "lat" : -1.2861292178882062,
        "lng" : 36.89101696014404,
        "eventop" : "0800123456",
        "eventtitle" : "test2",
        "eventtime" : ISODate("2016-10-11T11:59:16.988Z"),
        "invited" : "0800123456",
        "hour" : "0",
        "minute" : "1",
        "pmam" : "pm",
        "eventduration" : "60",
        "eventtype" : "4",
        "eventcategory" : "hackathon",
        "eventstatus" : 11
}

{
        "_id" : "LtFB4WMu4xGC7Kyxf",
        "createdAt" : ISODate("2016-10-11T11:59:16.939Z"),
        "lat" : -1.284970795243023,
        "lng" : 36.89183235168457,
        "primaryowner" : "0800123456",
        "eventtitle" : "test2",
        "eventtime" : ISODate("2016-10-11T11:59:16.939Z"),
        "hour" : "0",
        "minute" : "1",
        "pmam" : "pm",
        "eventduration" : "60",
        "eventtype" : "4",
        "eventcategory" : "hackathon",
        "eventstatus" : 11
}

I want to fetch some data from the collections invites. This is the data i want to get.

I want to get data(all fields) where invited field is 0800123456 and eventtype is 3 and eventstatus is 11. I also want to get data where eventtype is 4 where eventstatus is 4 and the field primaryowner exists.

db.invites.find({invited:"0720069005",eventtype:"3"},{eventstatus:"11"},{$and: [{eventtype: "4"},{eventstatus:"11"},{primaryowner: { $exists: true }}]}).pretty()

This is my query.It returns some data but i am not sure whether its correct.

From your question, I couldnot clearly understand what is the required business scenario, but with the sample data provided I have given an pseudo code and the respective mongodb implementation of that pseudo code.

if invited field is "0800123456"
   if event type is 3 and event status is 11
     select matching documents
or if event type is 4 and event status is 11 and primary owner exists
     select matching documents

Mongo DB Query

db.collection.find({
  $or: [
    {
      $and: [
        {
          "invited": "0800123456"
        },
        {
          "eventtype": {
            $eq: "3"
          }
        },
        {
          "eventstatus": {
            $eq: 11
          }
        }
      ]
    },
    {
      $and: [
        {
          "eventtype": {
            $eq: "4"
          }
        },
        {
          "eventstatus": {
            $eq: 11
          }
        },
        {
          primaryowner: {
            $exists: true
          }
        }
      ]
    }
  ]
})

If this is not matching your scenario, then write your own query with the help of this sample code.

Hope it Helps!


ERROR: canceling statement due to conflict with recovery I got the error below:
ERROR: canceling statement due to conflict with recovery.
Detail: User query might have needed to see row versions that must be removed.;
nested exception in org.postgresql.util.PSQLException:ERROR:canceling statement due to conflict with recovery.
Detail: User query might have needed to see row versions that must be removed.

This error occurs on the standby, when we use long query.
We use PostgreSQL version 12.0. One master and two standby servers.
Could you please give us the solution?

Some old row versions were removed by VACUUM on the primary server, but these row versions could still be required by the query on the standby.  So there is a conflict between the running query and the startup process applying changes that come from the primary.
PostgreSQL waits and delays replay for max_standby_streaming_delay, then cancels the query. So to avoid that problem, set max_standby_streaming_delay to -1 on the standby.
You can read more in my blog.


Delete and Insert performance SQL Server I have the below stored procedure that is taking close to an hour to complete. The result set is about 200K machines.

All I am doing is extracting a set of ids from a linked server, deleting those ids in a local server and then extracting all details for those ids from a remote server and inserting them. The INSERT takes 90%, DELETE table 9% and SELECT INTO 1%.

id is the Primary Key/Clustered Index.

    DECLARE @ProcessDate DATETIME
    SET @ProcessDate = CONVERT(VARCHAR(10), DATEADD(DAY, -3,
                                                    GETDATE()), 111)

    SELECT  DISTINCT id
    INTO    #temp_machines
    FROM    remote_server.db.dbo.table1
    WHERE   dt_modify >= @ProcessDate

    DELETE  FROM local_server.db.dbo.table2
    WHERE   id IN ( SELECT id  FROM  #temp_machines )

    INSERT  INTO local_server.db.dbo.table2
    SELECT  *
    FROM    remote_server.db.dbo.table1
    WHERE   id IN ( SELECT id  FROM  #temp_machines )

Any suggestions on improving performance?

First step: Make sure there's something to tune and that your query isn't just being blocked. You can do this with free tools like sp_WhoIsActive or sp_BlitzFirst (disclaimer, I'm a contributor to the First Responder Kit).

Second step: Don't use local variables.

Third step: Maybe indexing the temp table will help.

Fourth step: Check your wait stats using sp_BlitzFirst (same disclaimer about me contributing to it, blah blah blah). It could be that the query as written is fine, but you're running into some other issue, like tempdb contention.


Where can I find the first standardization of SQL, SQL-86? This question is different but similar to this request looking for SQL-89.

The first draft of SQL is labeled SQL-86. Numerous references are made to it. Is this available to download? Wikipedia doesn't even have a page for it. I'm interested in it for historical reasons.

It seems to also be called

ANSI X3.135-1986
CAN/CSA Z243.47-88
ISO 9075:1987
SQL/1
NBS FIPS 127

The spec is cited in documents from the period as coming from ANSI Technical Committee X3H2.

I know there is webstore.ansi.org, but I can not find the X3.135-1986 document. I can however find X3.168-1989 spec which is available under X3.135-1992 Searching for both X3.168, and X3.135 do not permit me to buy the 1986 spec.

HTML SQL-86 Google Groups

I found some references about an HTML version of SQL-86.

  The new URL for the HTML translation of SQL-86 is:

     http://speckle.ncsl.nist.gov/~sql/

  The reason for this move is that I am taking a job in another lab and
  I had to get it off of my PC hard drive.

  DWF

  Unfortunately this link is broken, but I believe the OP is still working at NIST >as a Computer Scientist.

  I've sent him a request via LinkedIN, and now I'm waiting for a reply.

The answer:

  To Joan and others interested in SQL-86,

  The 'HTML version' was a demonstration project that was never completed. Your best bet is to try to obtain the ANSI specification through interlibrary loan.

  V/R,
  —
  David Flater
  National Institute of Standards and Technology, U.S.A.

comp.databases Newsgroup Posting

I've found new references dated 12 Oct 89 about ANSI X3.135-1986: it's from newsgroup comp.databases where someone else was interested on this document. There is a response by Merrill Holt to the initial inquiry. Merrill Holt claims to be "a member of the X3H2 SQL and X3H2.1 RDA committees.", that seems likely as they're writing from a Sybase email address. He/she says,

  The ANSI and ISO documents are identical (ISO
  9075:1987 == ANSI X3.135-1986 and ISO 9075:1989 == ANSI X3.135-1989).
  All of these standards are known informally as SQL 1 as compared to future drafts which are referred to as SQL 2 and 3.

SQL 1   SQL 86  ANSI X3.135-1986   ISO 9075:1987
        - level 1 DML & DDL
        - level 2 DML & DDL
        SQL 89  ANSI X3.135-1989   ISO 9075:1989
        - level 1 DML & DDL
        - level 2 DML & DDL
        - IEF - new for 89

The answer points out to this company that may have the document:

Global Engineering
2805 McGaw Ave
Irvine, CA 92714

It turns out ISO does mention ISO 9075:1987 however they have it listed as obsolete and retired and are not currently selling it.

US Army Corps of Engineers

And I've found one available exemplar at:

  US Army Corps of Engineers
  Engineer Research and Development Center Library
  3909 Halls Ferry Road, Vicksburg, MS.
  erdclibrary@ask-a-librarian.info

  I'm not sure this is the original book covers.

Linda Hall Library

I've found one exemplar at Linda Hall Library

Other potential names

It seems some sources references this as ISO/TC 97/SC21/WG3 N117. However, I'm not sure how to read this or if this designation refers to the document or not. This source helps break down the acronyms.

TC refers to "Technical Committee"
SC refers to "Sub Committee"; 21 is Systems
WG is usually working group.
N117 may be a document or something published like a SQL spec.


Postgresql MVIEW refresh from Oracle Materialized View Log Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is not a good practice to query a log, is it even possible to use an MVIEW log from Postgresql just like an Oracle MVIEW uses a log?

If that's not possible, is a dblink from Postgresql to Oracle possible?

The implementation of materialized views in Oracle and Postgres is different. What you're asking would require a level of integration of Postgres into Oracle's proprietary API - to register as a known client MV so that the log correctly updates itself - that I don't believe exists. It would also require a functioning database link to the Oracle database.

This extension looks like it can help Postgres connect to Oracle; I haven't used it myself, but it looks like it should work (although it also appears to have some limitations): https://pgxn.org/dist/oracle_fdw/

At that point, you would need to query the source tables directly, rather than existing MV Logs. Oracle maintains those logs automatically, truncating them or removing rows when it determines that all registered MVs have refreshed a certain set of changes. If there are no registered MVs, then the logs will grow out of control forever. It won't be long before they severely impact the performance of your updates and the Oracle DB (storage requirements, backups, memory, etc.). There are also situations in Oracle where an entire MV log can become invalid and require a complete refresh of the MV to reset. My point is that without the proper client registration, the data in those logs is pretty much useless, or worse than useless: harmful to maintain.


Login failed for user Error: 18456 State: 10 In one of my vb.net applications I am getting the error Login failed for user 'user'.
But the user has full permission (sysadmin). Also this error happens intermittently.

Error log has the following:

  Login failed for user 'user'. Reason: Failed to open the explicitly specified database. [CLIENT:
  192.168.1.69] Error: 18456, Severity: 14, State: 38.
  Login
  failed for user 'user'. Reason: Password validation failed with an
  infrastructure error. Check for previous errors. [CLIENT:
  192.168.1.61]
  Error: 18456, Severity: 14, State: 10.
  Login
  failed for user 'user'. Reason: Password validation failed with an
  infrastructure error. Check for previous errors. [CLIENT:
  192.168.1.88]Error: 18456, Severity: 14, State: 10.

I saw  this article.

But in my case all database are set to autoclose OFF.

Also the user exist and has sysadmin permission.

Also KB #925744 is for SQL Server 2005 as mentioned in the article. But I am using SQL Server 2008 R2.

update:

I just saw this article. In that there was query to find ring buffer.In my case i got 0x139F as error code.when i run  NET HELPMSG 5023 i got following.

The group or resource is not in the correct state to perform the requested operation.

What does this mean? Whether it will be reason for the error?

The state 38 error is probably a red herring: focus on the state 10 errors.

While KB Article #925744 says it is for SQL Server 2005, that is simply because of the date it was written. The symptom it causes would affect all versions of SQL Server that have been released since then in similar or identical ways.

Now, if user is a Windows login, perhaps you are somehow trying to authenticate as a Windows login without specifying the domain, or perhaps you are specifying the domain but the domain controller is unreliable or on an unreliable network connection. You should post your actual connection string in the question (fudging our real names/passwords of course).

You should also consider creating a new, separate login with the same permissions, and see if you intermittently get the same error with that other user as well - my guess this is an architectural problem in some way as opposed to a problem with just this user.

Another possibility is if your .net application is using Windows auth and the underlying password has been changed. Seems like a long shot, but check out the article and there is a hotfix available: KB Article #2545850.


Long queries on PostgreSQL hot standy Is there a way to convince PostgreSQL to execute long queries on a hot standy?

It kills the query with User query might have needed to see row versions that must be removed. I understand that, but I am doing a query on a table that I know never gets any row deleted... Is there a way to force porstgres to do it?

Thanks to Amacvar on reddit for pointing me to the answer:

max_standby_archive_delay and max_standby_streaming_delay have to be set for longer period than the longest query you want to run on the hot-standy.

From https://www.postgresql.org/docs/current/static/hot-standby.html

  It is important that the administrator select appropriate settings for max_standby_archive_delay and max_standby_streaming_delay. The best choices vary depending on business priorities. For example if the server is primarily tasked as a High Availability server, then you will want low delay settings, perhaps even zero, though that is a very aggressive setting. If the standby server is tasked as an additional server for decision support queries then it might be acceptable to set the maximum delay values to many hours, or even -1 which means wait forever for queries to complete.


Cross Database transactions with Availability Groups Recently we are working on a POC to get Always on work and happened to see this article in BOL

http://technet.microsoft.com/en-us/library/ms366279.aspx

This article suggests that there would be logical inconsistency when we are dealing with Synchronous mode too, but  will this actually be the case?

Consider for example databases A and B on which the transaction is running and A is in High-safety mode and B is not mirrored. The log of A has to go to Mirrored database then the Primary database commits eventually two phase commit(transaction on B) succeeds but article suggests that log will not be transferred in the first place and results in commit on B which is contradictory. Please help me in understanding Whether the statement suggested in above article is true. If yes how can it be :).

PS :Please let me know if I need to provide more information around this.

The article isn't well worded but here's what I think it's saying

Beginning scenario - DatabaseA is in an AG and is currently primary on Server1 and can run on Server2.  DatabaseB can only run on Server1

T1 - Transaction begins
T2 - Row inserted into DatabaseA (log not yet sent to Server2 though)
T3 - Row inserted into DatabaseB
T4 - Transaction commit called
T5 - DatabaseB commits transaction (since the two DBs commit individually)
T6 - DatabaseA fails over before log is sent

Since the log was never sent DatabaseA wouldn't have the row but DatabaseB would.  If synchronous mode is being used that can't happen because the commit can't be called until the log from the row is sent.  However, it may be possible for the commit on DatabaseB to happen and DatabaseA to fail over before the commit happens there.  This may lead to a rollback in DatabaseA instead of a commit.  I can't say with certainly that you can get into the same situation with synchronous mode but if you can that's how it would likely happen.


Why are some of my migrated tables showing "The given key was not present in the dictionary" in WebMatrix's interface? I'm having an issue while using the SQL Server Migration tool to convert Access accdbs into SQL Server 2014 Express. I am converting the tables using SQL Server Migration Tool and I have a connection string to the SQL Server 2014 Express database in WebMatrix. All of the tables show up, but, for some reason, many of the tables, when I try to open them in WebMatrix, show an error "The given key was not present in the dictionary. Like so:

During the conversion, it shows "0" errors and proceeds as though everything is okay. This seems to be an issue with WebMatrix, because, not only are the tables openable in SQL Server Management Studio (showing all rows), but they are also queryable, meaning that the information is there:

It just won't show in WebMatrix's interface if you try to open the whole table.

I would like for this to work in WebMatrix and was wondering if anyone could help or has encountered this before. I try to search Google, but nothing relevant comes up. That's usually the case when trying to research WebMatrix specific problems.

Here is what you need to do

Open the table in definition view
Select a cell in the column which is empty.
Add an entry of that data type (I picked the datetime in an example where this happened to me)

When done, you should be able to click the data button and see all the data.


How to copy data into same table based on existing data in sql? It is possible to copy a set of rows based on Date column and insert in same table with different date?

For example : I have 5 rows with Date column value '201839'
I need to copy those 5 rows in '201840' as well as '201841'

Or can we create a view that have a copy of 201839 rows as 201840 in sql.

One more solution is to run sub query for two time with different updated Date_column value. It will select data from same table and updated with different date_value.

INSERT into TABLE_NAME (Column1, Column2, Date_column)( SELECT (Column1, Column2, '201840') FROM TABLE_NAME where Date_column = '201839');

INSERT into TABLE_NAME (Column1, Column2, Date_column)( SELECT (Column1, Column2, '201841') FROM TABLE_NAME where Date_column = '201839');


Question about encrypting multiple MariaDB schemas on the same server I have 1 server running 1 installation of MariaDB

In that installation of MariaDB, I have a CustomerService database and WorkFlow database.

Can I encrypt CustomerService with one key and WorkFlow with another key so that users accessing WorkFlow cannot see CustomerService or vice/versa?

Or do I need another installation of MariaDB?

In the documentation it says each table can have a manually specified encryption key number.

ALTER TABLE my_db.my_encrypted_table
   ENCRYPTED=YES
   ENCRYPTION_KEY_ID=3;

If each database1 has all its tables having a different encryption key number from database2 tables' then they are using different keys.


Create A View Using Dynamic Sql I'm trying to create a dynamic database creation script.

There are a lot of steps and we create this database often so the script looks something like this.

 DECLARE @databaseName nvarchar(100) = 'DatabaseName'
 EXEC('/*A lot of database creation code built off of @databaseName*/')

This is all well and good except for one view that we'd like to create in @databaseName.

I've tried four different ways to create this view without success:

My first thought was to simply set the database context and then create the view in one script. Unfortunately, this didn't work because CREATE VIEW must be the first statement in its query block (details).

--Result: Error message, "'CREATE VIEW' must be the first statement in a query batch"
EXEC
('
    USE [' + @databaseName + ']
    CREATE VIEW
')

To get around (1) I tried to set the context separately so that CREATE VIEW would be the first command in the EXEC. This did create the view but did so within my current context and not @databaseName. It seem that the effects of calling USE in EXEC only persist until the end of that EXEC statement (details).

--Result: The view is created in the currently active database rather than @databaseName
EXEC ('USE [' + @databaseName + ']')
EXEC ('CREATE VIEW')

Next I tried putting everything back into one script but included a GO command in order to make CREATE VIEW the first command in a new query block. This failed because GO isn't allowed within an EXEC script (details).

--Result: Error message, "Incorrect syntax near 'GO'"
EXEC
('
    USE [' + @databaseName + ']
    GO
    CREATE VIEW
')

Finally I tried to specify the target database as part of the CREATE VIEW command. In this case the script failed because CREATE VIEW doesn't allow the database to be specified as part of its creation (details).

--Result: Error message, "'CREATE/ALTER VIEW' does not allow specifying the database name as a prefix to the object name"
EXEC ('CREATE VIEW [' + @databaseName + '].[dbo].[ViewName]')

Any suggestions? I think this should be a common use case but Google wasn't able to help me.

This worked for me Mark

EXEC [OtherDatabase].[sys].[sp_ExecuteSQL] N'CREATE VIEW [SM].[vMyView] AS SELECT ...'

I ran this from the context of the master database and successfully created a new view in the OtherDatabase.


List JSON array in MySQL as rows I have a column AddressIdentifiers of JSON type in my MySQL table Customers and the data sample looks like:

[
  {
    "code": "123",
    "identifier": "0219d5780f6b",
    "type": "BILLING",
    "info": null
  },
  {
    "code": "240",
    "identifier": "c81aaf2c5a1f",
    "type": "DELIVERY",
    "info": null
  }
]

I am using query:

SELECT JSON_EXTRACT(AddressIdentifiers, '$**.identifier') As Identifier, JSON_EXTRACT(AddressIdentifiers, '$**.type') As AddressType FROM Customers WHERE CustomerId = 10003;

to get output like:

Identifier   AddressType
------------------------
0219d5780f6b  BILLING
c81aaf2c5a1f  DELIVERY

But it does not give output like table rows instead gives concatenated values. How can we get the output from the JSON array like rows (as above)? Please advise.

I am using MySQL version 5.7.17.

Thanks,
Prabhat

EDIT-1:
I think if we join this Customers table with Address table (having columns: Id, identifier, Address, City, Pincode) that has the identifier as key then may be we can pull these values as row for each address?

So output will be something like:

 Identifier   AddressType   Address  City
    -----------------------------------------
    0219d5780f6b  BILLING      Address-1  Hyderabad
    c81aaf2c5a1f  DELIVERY     Address-2  Delhi

I have this solution for MySQL 5.7, where you have to do the work manually. In the case of MySQL 8.0+ you can simply use JSON_TABLE

SELECT
    JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].Identifier')) AS Identifier,
    JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].AddressType')) AS AddressType,
FROM
(
    SELECT @row := @row + 1 AS N FROM
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2,
    (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1,
    (SELECT @row:=0) T0
) Numbers -- Natural numbers from 1 to 100
INNER JOIN Customers C ON Numbers.N < JSON_LENGTH(C.AddressIdentifiers)


How does the transaction log record a transaction? We all know what SQL Server's transaction log records information about the transaction. But what, in a full recovery model, is actually recorded in the transaction log? You'll say: THE TRANSACTIONS!
Ah, but there could be several ways for SQL Server to record the transactions. So the question is, how does it record the transactions?
Here are the following ways I envisage it is working:

It merely makes a record of which statement was run. In this case, the transaction log contains the actual text of the DML statement, something like this:
"INSERT INTO test_database.dbo.Test_People (FirstName,LastName) VALUES ('James' ,'Smith')"

In this case the transaction log contains the whole of what is quoted above.
Then, in a transaction log restore (after a full backup restore), SQL Server will actually execute the statement contained in the transaction log, against the database, to update it.

It stores a record of the entire row of data, with all its values (even those values that were not updated), and the date at which the row was modified/inserted.

Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting it or overwriting the same row in the table with the data in the transaction log.

It stores a record of the entire data page, with all its values (even those values that were not updated), and the date at which the data page was modified/updated/deleted.

Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting the page or overwriting the page with the same pageID.
Or, perhaps it is none of the above. Does anybody know?

I could not put this as comment so here it is for you. It is not compete answer to your question because your question can either be correctly answered by person working in Microsoft or Paul Randal(I guess). All I can say for all paractical purposes every information is logged.

You can read the contents of log file using undocumented command

select * from fn_dblog(Null,Null)

If you run it you can see lot of information related to database, pages,extents,locks ect. But it would be difficult for you to extract information from it as it requires a level of expertise to decipher the output.

If you read Books Online document SQL Server Transaction Log architecture and Management it says

Many types of operations are recorded in the transaction log. These
 operations include:

  •The start and end of each transaction.

  •Every data modification (insert, update, or delete). This includes
  changes by system stored procedures or data definition language (DDL)
  statements to any table, including system tables.

  •Every extent and page allocation or deallocation.

  •Creating or dropping a table or index.

Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

AFAIK there is no information about query which was fired but the changes which query made is written in transaction log. Changes made to page, extents , locks that were taken, resources that were locked.


SQL SERVER ERRORLOG FILE UTILIZING THE FREE SPACE I'm stuck up in one of the issue.
I'm Using SQL SERVER 2008 R2.
The SQL SERVER Log file is utilizing the disk space.
When ever I clear the space in the DRIVE, the SQL SERVER Error log file starts to utilize the free space.
Log file path is available in C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50. WINCC\MSSQL\Logs\ERRORLOG
Kindly, help me to resolve the issue.

You can close the existing log file by running
sp_cycle_errorlog
Which will create a new empty file
After that just delete the old log files
You should probably look what errors are being logged & try and fix those so the new error log doesn’t fill up so fast
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql?view=sql-server-ver15


Export each row of a table into a separate text file I have a table in my database that contains data with three columns: id, name, and notes. The notes column contains unstructured text data that I want exported for each row as its own text file. This means if I had 5 rows in my table, then I would export 5 .txt files. Here is my attempt so far:

do $$
  declare
    arow record;
    files varchar;
  begin
    for arow in
    select id, name, note from multi_text
    loop
      files := '/tmp/'||arow.id||'_'||arow.name||'.txt';
      COPY (select note from multi_text where id = arow.id) TO files (FORMAT CSV);
    end loop;
  end;
$$;

Currently, there is a ERROR:  syntax error at or near "files". I am using PostgreSQL in pgadmin. Any help is much appreciated!

You have to use dynamic sql, so something like this:

do $$
  declare
    arow record;
    files varchar;
  begin
    for arow in
    select id, name, notes from multi_text
    loop
      files := '/tmp/'||arow.id||'_'||arow.name||'.txt';
      execute format('COPY (select notes from multi_text where id = %L) TO %L (FORMAT CSV)',  arow.id,files);
    end loop;
  end;
$$;

It is possible that a maliciously crafted value of arow.name could cause the value in "files" to climb out of the /tmp/ directory and write a file someplace else, which could lead to security breaches.  If arow.id is a text type (rather than an integer for example) it would be even easier for a maliciously crafted value of that to do so.


Slow query that includes a WHERE clause with SUBSTRING I have the following query in SQL Server 2016, as there is no code for the "enter code here" column, I had to do a search with SUBSTRING. But as expected it is slow, the query ends in 00:03:37.

with cte as
(
    -- De Incidente para Requisição
    select
           cdchamado                       AS [Chamado]
          ,'De Incidente para Requisição'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço'
    union
    -- De Requisição para Incidente
    select
           cdchamado                       AS [Chamado]
          ,'De Requisição para Incidente'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente'
)
select * from cte

I thought about making a temporary table and inserting only the data I need, so this table would update itself through a job in SQL Server Agent. But I do not know if it is the best solution.

You could create two computed columns for this:

ALTER TABLE [dbo].[hd_acompanhamento]
ADD IsServiceRequest AS
    CASE WHEN SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente' THEN 1
    ELSE 0 END;

ALTER TABLE [dbo].[hd_acompanhamento]
ADD IsIncident AS
    CASE WHEN SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço' THEN 1
    ELSE 0 END;

And then index them:

CREATE INDEX IX_IsServiceRequest
ON [dbo].[hd_acompanhamento] (IsServiceRequest);

CREATE INDEX IX_IsIncident
ON [dbo].[hd_acompanhamento] (IsIncident);

Then you should be able to rewrite your query like this:

with cte as
(
    -- De Incidente para Requisição
    select
           cdchamado                       AS [Chamado]
          ,'De Incidente para Requisição'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        IsIncident = 1
    union
    -- De Requisição para Incidente
    select
           cdchamado                       AS [Chamado]
          ,'De Requisição para Incidente'  AS [Tipo de Alteração]
          ,dtacompanhamento                AS [Alterado Em]
    from
        [dbo].[hd_acompanhamento]
    where
        IsServiceRequest = 1
)
select * from cte

This will let your query seek to those specific rows, rather than scanning the whole table running the substring function.

You may also want to add the columns cdchamado and dtacompanhamento as included columns for the index to avoid a key lookup.  I didn't include them by default, because I'm not sure of their data types.  If they are big text values, it might be better to just leave them as key lookups.

Another option if you only want the overhead of one computed column / index, you could combine them like this:

ALTER TABLE [dbo].[hd_acompanhamento]
ADD ServiceRequestType AS
CASE
    WHEN SUBSTRING(dsacompanhamento,45,36) = 'requisição de serviço para incidente' THEN 1
    WHEN SUBSTRING(dsacompanhamento,45,36) = 'incidente para requisição de serviço' THEN 2
    ELSE 0
END;

Then you could index just that one column, and use ServiceType = 1 and ServiceType = 2 in the two queries being union'd together.  Hat tip to Erik Darling for that idea.


Select total number of records from common table expression We are using common table expressions to temporarily save the search results.

  WITH Results (
                 ------
                  )
  AS
    (
          select ---
        )

 SELECT * FROM Results

        WHERE Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount

Here i want to get the toatal number of records in the Results common table expression in a variable @RowCount. Is there any way to get it without using table variable or temp table.

No, because you can't mix assignments and result set generation

But what you can do is make the total row count into an extra column

WITH Results AS
(
    select
        ---,
        COUNT(*) OVER () AS TotalRowCount -- this
    ...
)
SELECT
   *
FROM
   Results
WHERE
   Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount


PgAdmin III - Number of lines returned during insertion Not crucial question here, just curiosity.

I am inserting data via multiple scripts in a base running in PostgreSQL.

I wanted to test the time requested to end the insert, so I requested many times the number of lines in the table as follow :

SELECT COUNT(*) FROM "table";

I noticed that everytime the number of line is following a schema : last digits (hundreds, tens and units) are the same as the firsts (millions and thousands).

By example :

if there was around 39k lines, the number of lines returned was 39039,
if there was around 8518k lines, the number of lines returned was 8526518 (8518000 + 8518)

Any idea from where it come? Is it by default? or maybe a personal config?

More info :

Same behaviour on psql.
I am adding 1k inserts by connection to the base, so I don't think it's related.
When the inserts are over, the correct number of lines is returned.
I did enought requests to ensure that's not coincidence.
I am working on Windows.

I am adding 1k inserts by connection to the base, so I don't think it's related.

I think it is related. You are probably adding exactly 1001 rows in each batch.

All these numbers are multiples of 1001.


Setting UNIQUE constraint on TIMESTAMP (WITH TIME ZONE) I want to scrape a table from a website and synchronize the data into my own database i.o. to detect whenever there is a new entry available. The table has a timestamp column which tells me when the new record was created.
However, I have different tables for multiple objects so in order to know if there is a new entry, I'd need a unique-constraint on (object_id, timestamp):
object_id  | timestamp           | value
------------------------------------------
1          | 2021-01-01 00:00:00 | 100      <--|
1          | 2021-01-02 00:00:00 | 101         | error: have an
1          | 2021-01-03 00:00:00 | 102         | equal timestamp
1          | 2021-01-01 00:00:00 | 103      <--|
2          | 2021-01-10 00:00:00 | 1002
2          | 2021-01-12 00:00:00 | 1003

It feels a  bit odd to have such a unique-constraint so I was wondering if this is the only (or best) way I could do this?

There is nothing wrong with having a multi-column constraint:
ALTER TABLE mytab ADD PRIMARY KEY (object_id, timestamp);

The column order does not matter for the constraint, but if you more often search by timestamp alone, put that column first.


Can subqueries produce different execution plans than CTE's? I was talking with the bossman, and he prefers subqueries over CTE's. Personally, I loathe subqueries. He mentioned that subqueries can be faster, but I am not convinced. I ran this short test:

    with classes as
    (select top 10 Classkey from dimclass
    group by classkey
    order by count(1) desc),
    policies as (
    select CarrierKey, policykey, periodeffectivedate from dimpolicy),
    exposure as (
    select policykey, classkey  from DimExposure)

    select * from policies p
    inner join exposure x on p.PolicyKey = x.PolicyKey
    inner join classes c on x.ClassKey = c.Classkey

has an excution plan of:

https://www.brentozar.com/pastetheplan/?id=SJYJUZNHS

    select p.CarrierKey, p.PolicyKey, p.periodeffectivedate from dimpolicy p
    inner join (select policykey, classkey  from DimExposure) x on p.PolicyKey = x.PolicyKey
    inner join (select top 10 Classkey from dimclass
                group by classkey
                order by count(1) desc) c on x.ClassKey = c.Classkey

has the same execution plan:

https://www.brentozar.com/pastetheplan/?id=rJs_LbVSr

Question: Is this always the case? In the weird and wonderful world of SQL Server, the answer always seems to be it depends.

As is, I'd argue that the question isn't answerable. It's impossible to prove a negative and you won't find a guarantee in the product documentation. If you'd like an example of a technical difference between the two approaches, watch a few minutes of Paul White's Query Optimizer Deep Dive session. It is not clear how someone could translate that into a performance best practice.

I suggest approaching the issue as a matter of coding style instead of trying to find a performance best practice. Switching out a CTE for a derived table or a derived table for a CTE is not a meaningful way to rewrite a query.


Postgresql create database from backup programmaticly I got a big problems here. Let's say I got the table A (with columns like id, name etc) in the main database. So when I making INSERT into A  I need to create new database lets say B_id. I got .backup file with database sturcture which suppose to use B_id databases.

I guess I need to write trigger which activates script.

But how I suppose to wrtie the script which creates database(with name depends on id number) and applies .backup file to it? Is it even okay to do like that?

Creating a database inside a transaction is not possible, so that precludes doing it directly in a trigger, and more generally in any function called by the SQL engine.

The usual method to issue script-like operations from a trigger is to have such script running as a daemon and listening to events through the SQL LISTEN command, the events being signalled from the trigger with a NOTIFY command, possibly including a payload with parameters. If the transaction is rolled back, its notifications are not sent.

From a design point of view, this is a misuse of triggers. A trigger that "does something" is a side effect by nature and side effects are more a problem than a solution in programming. Creating and populating a new database is a massive side-effect.

Unfortunately some think that triggers being cool, they should use and abuse them no matter what problem they're trying to solve.


Search for partial matches in website domain names I have a Postgres database with information about websites and a table called sites with a column host. This already has an index for text pattern operations which is excellent for searching from the start of the domain name: WHERE host LIKE 'www.bran% but not so good for partial matches such as WHERE host LIKE '%.bran%' which result in a scan. There are currently around 7.5 million hosts so search performance is already noticeable.

Based on the suggestion below I've added a trigrm index (you need to enable to extension in order to do this:

create extension pg_trgm;

and then add a suitable index:

CREATE INDEX trgm_idx ON sites USING GIST (host gist_trgm_ops);

As with all indices this may take some time depending upon the size of your database.

Before the index my queries were taking about 180s to find 200 items in 7.5 million but this is complicated by a correlated subquery, which seems to be complicating the matter and dominating the query and suggests I should be looking at optimising something else first, specifically a date index.

So the results of the comparison: using a text_ops index takes about 2s and using a trigram about 7. I think I might have more luck if I create an index that only contains the relevant part of the host name.

This looks like a perfect use for a trigram index. As long as your queries don't get much shorter than the one shown.  If you want to search for '%.br%', then probably not much is going to help you.


SQL Server — optimization techniques for rebuilding very large table We generally use SQL Compare to generate upgrade/change scripts, and we also use the "Force column order" option by default. This basically means that the column order will be preserved going from database version A to B, so in situations where this is relevant a table rebuild becomes necessary.

Well, somebody decided it would be a good idea to sandwich a new column in-between existing columns when making a schema change to a table containing a very large amount of data (obviously ill-advised). Here are some more details of what I am faced with:

The table has about 1.3 billion rows
A clustered index exists on the table
A few non-clustered indexes exist on the table
The server the database instance is running on is very limited from a resource perspective

The script generated by SQL Compare handles this by dropping all indexes on the table, throwing the data into a temp table, dropping the table, re-creating the new table, and inserting the data from the temp table into the new table schema. I feel like this can be optimized. Here is what I came up with to do so:

bcp data out to file
disable indexes
truncate table
rebuild table based on new schema
bcp data into new table schema
enable indexes

One obvious problem is at some point the table has to be dropped and recreated if I am not mistaken since the column order is changing. Dropping the table will also drop the indexes that I would like to keep in place, wouldn't it? Has anybody been faced with a similar situation? Any advice as to whether or not my plan looks like a better one to reduce potential down time? Any ideas or advice will be greatly appreciated.

Code that relies on the order of columns is asking for trouble anyway. It should never be a problem, since as a DBA, you're profiling and identifying SELECT * statement, and ostracizing the perpetrators, correct?

When I do have to perform such an operation, due to some dumb legacy software, I would follow these steps:

rename existing table and named constraints
create table with new schema
bulk insert from old to new table. if required, break on the id and do it in batches of 100,000* records using a while loop (* - a number that suits the hardware & environment)
drop old table - reclaim space here
create indexes with proper fillfactors
create constraints (after indexes)


Can't eliminate index scan I have a query, and even using SQL Sentry, I can't eliminate the index scan.

This is the query:

SELECT TOP 30 codCliente FROM (
        SELECT t1.CodCliente, codcampo, valor, t1.chavealeat
        FROM tblCliente AS t1 WITH(NOLOCK)
            INNER JOIN tblClienteDetalhe AS t2 WITH(NOLOCK)
            ON t1.codcliente = t2.codcliente
            AND CodCampo IN(-1, 4)
        WHERE codStatus IN (0)
            AND t1.ChavePeriodo < GETDATE()
            AND t1.CodStatusLigacao = 0
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK)
                WHERE codcampo = 3 AND valor = '2'
                    AND codcliente = t1.codcliente
            )
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK)
                WHERE codcampo = 6
                    AND CONVERT(DATETIME, Valor) BETWEEN '2015-08-01' AND '2015-08-31'
                    AND DATEDIFF(DAY, Valor, GETDATE()) > 15
                    AND codcliente = t1.codcliente
            )
            AND NOT EXISTS
            (
                SELECT 0 FROM tblPesquisa WITH(NOLOCK)
                WHERE tblPesquisa.CodCliente = t1.CodCliente
            )
            AND EXISTS
            (
                SELECT codcliente FROM tblclientedetalhe WITH(NOLOCK)
                WHERE codcampo = 4 and valor = '202' and codcliente = t1.codcliente
            )
    ) AS Cliente
Pivot (MAX(Valor) FOR codCampo in ([4])) AS PivotTable
WHERE (((([4] = '202'))))
ORDER BY chavealeat;

This is the index I made:

create index IX_CHAVEALEAT_CODCLIENTE
on tblcliente (chavealeat,codcliente)
include (chaveperiodo,codstatus,codstatusligacao)

And, this is the analysis:

Why is the plan showing index scan? I'm covering all columns. I used option(recompile) to update the plan.

Is this index wrong?  The output is only codcliente.

I have 10-30 queries running at the time, and theres no wait_info problems.

Just because you have a covering index doesn't mean it won't scan, nor does it mean that a scan isn't still the best way to access the requested data.

You're returning, what, 3 million rows? Even if that's not most of the table, it still wouldn't make much sense to perform 3 million seek operations, nor is it likely there would be any benefit in seeking to the first row and performing a range scan for the rest. There is also the issue that multiple EXISTS clauses - multiple of which do not join to the index's leading key column - will not likely all be able to take advantage of the same output from the seek. And you have filters on all of the INCLUDE columns - they're not part of the key, so seeks on those columns are going to be difficult (in fact the only case where I've seen an index still be used to seek when there are WHERE clauses against non-key columns is when the column was BIT, and those scenarios did not introduce all the other variables you have above). You're also using PIVOT and aggregating, which I'm sure don't help.

Also, what table is CodCampo in (why isn't it properly prefixed with a table alias)? You may have one or both of the following two problems:

It is a disjunction (OR clause) which can discourage seeks (see Paul's answer here).
If it is in tblcliente, then your index is not covering anyway.

You could try running the query with the FORCESEEK hint (also mentioned in Paul's example) and it will either fail to produce a plan (unlikely) or you will get a plan that you can share with us and, if you also share the scan plan you get without the hint, we can give further insight into why SQL Server chose the scan instead.

As an aside (this certainly won't help you get a seek), since the estimates were far lower, I'd check that your statistics are current.

And finally, don't always assume that a scan is bad and something you need to eliminate! In a lot of cases, a scan really is the best way to retrieve the data.


Best index for case insensitive match on one field and ordering by another field I want to run a query like this:
SELECT *
FROM "core_user"
WHERE UPPER("core_user"."email"::text) = UPPER('emailsearch@example.com')
ORDER BY "core_user"."last_login" DESC
LIMIT 1;

I've tried adding two indexes:
CREATE INDEX core_user_upper_idx ON public.core_user USING btree (upper((email)::text));
CREATE INDEX core_user_last_lo_0521b8_idx ON public.core_user USING btree (last_login);

core_user_upper_idx works well if I remove the ordering clause, but in its current state explain gives
 Limit  (cost=0.29..10.33 rows=1 width=431)
   ->  Index Scan Backward using core_user_last_lo_0521b8_idx on core_user  (cost=0.29..5531.53 rows=551 width=431)
         Filter: (upper((email)::text) = 'EMAIL@EXAMPLE.COM'::text)

which doesn't perform very well.
Is there a better index for me to add here? I'm a bit unsure as to whether I should "prioritise" the email or last_login fields.

A multicolumn expression index should serve your particular query best:
CREATE INDEX ON public.core_user (upper(email), last_login DESC);

If last_login can be NULL, consider NULLS LAST - in index and query.
The rule of thumb is: equality first, range later. See:

Multicolumn index and performance
Get the last 5 distinct values for each ID


What is the impact of SHOW STATUS, and how often is acceptable to call it? I am setting up a monitoring thread in my application that should use the SHOW STATUS call of mysql to periodically calculate system / db runtime statistics.

Being relatively unfamiliar with datbase backends, my question is

  What is the impact of running SHOW STATUS against the database? And how often is acceptable to call it? Would this cause any significant db slow downs?

The rest of my application collects statistics once per minute.

SHOW STATUS has minor impact on the system.

For monitoring, you really need SHOW GLOBAL STATUS so you can get the system-wide counters, not the SESSION counters for the monitor's connection.

It is probably unreasonable to do SHOW GLOBAL STATUS more than once a second.  And, at that frequency, it has negligible overhead.  Keep in mind that you then have to do something with the hundreds of values.  If you turn around and INSERT it into the same server, that action may be more invasive.

Or is this what you are doing to see what is happening with a query?

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

Again, do it as needed; don't worry.


How to convert sql server table data into MS Access(.mdb) file I have SQL Database table with 6664055 rows, I want to convert this SQL Server table data to MS Access format(.MDB), How Can I do this?

Please have a look at the SQL Server Import and Export Wizard

SQL Server Import and Export Wizard


Running sys.dm_db_index_physical_stats in parallel I am trying to get fragmentation information on a few tables which are over 500 GB and I am using the ...DETAILED option of sys.dm_db_index_physical_stats.  I am doing this on a restore copy of the production database on our pre-Prod server so I don't care about hurting any performance on the server.

I ran it and it looks like it is running in serial and it is taking forever. Is there a way to run dm_db_index_physical_stats() in parallel? Or is any other setting interfering with it?

I guess DBCC TRACEON (8649) and OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) are not available in SQL Server 2014.

Also tried DBCC SETCPUWEIGHT(1000); from Paul White's blog here. The blog mentions about parallelism inhibitor and one of which is system tables. Is dm_db_index_physical_stats() considered a system table?

sys.dm_db_index_physical_stats

is a system table valued function.
Internally this will perform an openrowset call to an internal INDEXANALYSIS system data source.
create function [sys].[dm_db_index_physical_stats]
(
    @DatabaseId         SMALLINT        = 0,
    @ObjectId           INT             = 0,
    @IndexId            INT             = -1,
    @PartitionNumber    INT             = 0,
    @Mode               nvarchar(20)    = NULL
)
returns table
as
    return select *
        from OpenRowset
        (   TABLE
            INDEXANALYSIS,
            @DatabaseId,
            @ObjectId,
            @IndexId,
            @PartitionNumber,
            @Mode
        )
GO

More info on these internal data sources here.
You can find the same INDEXANALYSIS TVF call in the execution plan:

And you can also see that it is a multi statement table valued function.

So the query will not be able to use parallelism due to reason #1: system table access

The list changes from version to version, but for example these things
make the whole plan serial on SQL Server 2012:

...

System table access (e.g. reading from sys.tables)

The TVF call will also be serial due to reason #2: Multi statement TVF

These query features are examples that require a serial zone in the

...

Multi-statement T-SQL table-valued functions

Source


How do I rewrite my query to use less temporary space? I'm using Postgres 9.5.  I want to identify (and then delete) rows from my table that are "duplicates" in the sense that they contain three fields that are the same, namely

my_object_id
name
time_in_ms

The table is defined like so

myproject_production=> \d my_object_times;
                               Table "public.my_object_times"
      Column       |            Type             |              Modifiers
-------------------+-----------------------------+-------------------------------------
 first_name        | character varying           |
 last_name         | character varying           |
 time_in_ms        | bigint                      |
 created_at        | timestamp without time zone | not null
 updated_at        | timestamp without time zone | not null
 name              | character varying           |
 racer_id          | character varying           |
 age               | integer                     |
 city              | character varying           |
 state_id          | integer                     |
 country_id        | integer                     |
 my_object_id           | character varying           | not null
 id                | character varying           | not null default uuid_generate_v4()
Indexes:
    "my_object_times_pkey" PRIMARY KEY, btree (id)
    "index_my_object_times_on_country_id" btree (country_id)
    "index_my_object_times_on_my_object_id" btree (my_object_id)
    "index_my_object_times_on_state_id" btree (state_id)
    "my_object_times_name_idx" btree (upper(name::text) text_pattern_ops)
    "my_object_times_rev_name_idx" btree (reverse(upper(name::text)) text_pattern_ops)
Foreign-key constraints:
    "fk_rails_0fe1d25967" FOREIGN KEY (country_id) REFERENCES countries(id)
    "fk_rails_a8771b3575" FOREIGN KEY (state_id) REFERENCES states(id)
    "fk_rails_ba656ceafa" FOREIGN KEY (my_object_id) REFERENCES my_objects(id) ON DELETE CASCADE
Referenced by:
    TABLE "user_my_object_time_matches" CONSTRAINT "fk_rails_2e7860946c" FOREIGN KEY (my_object_time_id) REFERENCES my_object_times(id) ON DELETE CASCADE
    TABLE "my_objects" CONSTRAINT "fk_rails_dda3297b57" FOREIGN KEY (linked_my_object_time_id) REFERENCES my_object_times(id) ON DELETE CASCADE

I thought this query would do the job

select rt1.id, rt1.name
FROM my_object_times rt1,
     my_object_times rt2
where rt1.my_object_id = rt2.my_object_id
  and rt1.name = rt2.name
  and rt1.time_in_ms = rt2.time_in_ms
  and rt1.id > rt2.id;

But I guess I have so much data, the above query is dying with the below error:

ERROR:  could not write block 1862514 of temporary file: No space left on device

This is after pointing my temporary space (pgsql_tmp directory) at a partition that gives it 20 GB of temp space.  So my question is, how can I rewrite the above so that it does not use as much temp space?  That is fine if it takes the query a really long time to run.

Try the query using window functions which gets rid of the self join:

select id, name
from (
 select id, name, my_object_id, time_in_ms,
        count(*) over (partition by name, my_object_id, time_in_ms) as cnt
 from my_object_times t1
) t
where cnt > 1;

The partition by will still need to sort the rows, so it might be that it still needs a lot of temp space. If your server has a lot of memory, you can also increase work_mem for your session before running the statement.

Another possible way is to use an exists query instead of the self join:

select id, name
from my_object_times t1
where exists (select *
              from my_object_times t2
              where t1.my_object_id = t2.my_object_id
                and t1.name = t2.name
                and t1.time_in_ms = t2.time_in_ms
                and t1.id <> rt2.id);

An index on (my_object_id, name, time_in_ms, id) would help for this.

Check the execution plan for both to see if one is cheaper then then other.


How do I list or search all the column names in my database? I want to search for a string in the names of the columns present in a database.

I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking for a quick way to do this.

What do you recommend?

You can use following query to list all columns or search columns across tables in a database.

USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

You can make use of  information_schema views to list all objects in SQL Server 2005 or 2008 databases.

SELECT * FROM information_schema.tables

SELECT * FROM information_schema.columns

http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/


SQL Server Express averages 2-3% CPU but gets stuck at 100% sometimes Skip to the last paragraph for the summary-version
I am dealing with a SQL Server 2008 R2 Express installation. I have a fairly complex stored procedure. Depending upon the inputs, it may return 6500 records.
Each record is a web order. It returns 80 fields. Also, there is a bit of grouping to sum up line-item information and complex filtering on the SPROC itself. Almost always, this query runs in 2 seconds.
Sometimes, when the query runs with the minimal amount of filtering that will result in all 6500 rows returning, the database server enters an error state of some sort.
The CPU spikes to 100% and stays there. Sometimes it exits this state after a few minutes, sometimes it stays there until I reset the SQL Service.
If I reset the SQL Service during this error condition, then run the query again, normally I will be able to run it a hundred times without the error condition repeating. However, as the database ages (and expands) this error condition is becoming more and more common.
Here's what I know

I have analyzed the execution plan and likely have indexes on the appropriate fields in the appropriate tables

These indexes are rebuilt nightly

The error state lasts longer than it used to, on average.

Rebuilding the indexes seems to help when this error condition has just occurred (before resetting the SQL Service). I am including this here because maybe it has something to do with internal stats? More often than not, I just have to reset the sql service now.

As far as I can tell, the execution plan looks relatively healthy. You can see the busiest quarter of the plan here: http://i.imgur.com/bwAFn.png   Note: I am showing this portion due to the 3 underlined items.

I have experimented with changing the joins for those 2 of those 3 items to hash joins, but it actually seems to slow it down further.

On beefier hardware, I have not encountered this error condition. For a variety of reasons, it I need to stay on the limited hardware for the production version.

DB Properties:

My hosting resources are fairly minimal.

~2.5 Ghz single core
1.7 GB RAM
30 GB drive, 6GB Free

With that said, the DB server is "happy" almost all the time, except when this error condition is trigger by this specific SPROC.
I really can't show my code here, though I'd like to.
I wonder if some sort of internal maintenance is being triggered? Is it possible for SQL Server to enter an infinite loop? (I utilize no loops in my code).
Is there a good set of debugging tips for handling this situation:?
Sometimes when a query runs, it triggers a state where SQL Server uses 100% of CPU for 90 seconds to 20+ minutes. 98% of the time when the query runs, there is no problem at all, but this 98% is shrinking slowly over time.
2012 Update

I setup a job that rebuilds all database indexes each night.
Auto Close has been set to False

This largely mitigated the problem. However, I still do have some issues, but they come up about 5% as often, which I still have not solved.

It's probably related to  your AutoClose setting.  Almost every resource you will find recommends turning this setting OFF.
Essentially it closes connections automatically, but it can cause a lot of issues since it's not done well.  It can and does cause all sorts of mysterious performance issues.
Here's a post from Buck Woody about the reasoning.
Here's a blog post from another SQL Server MVP about a situation where it caused issues.
Step 1 should be turning that setting off, then testing for a while.
There was a request to deprecate the feature but it looks like they will be leaving it in.


How to change the schema of stored procedure without recreating it I have some Stored procedure with "SalesTraining" schema.
I want to change all SalesTraining schemas to "Sales".
Is there any better way than recreating the stored procedures?
thnx

You can use ALTER SCHEMA (Transact-SQL) if you are on SQL Server 2005 or later.

ALTER SCHEMA Sales TRANSFER SalesTraining.MainSP;


Trying to find the last time that a value has changed I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.

Records are inserted into this table periodically. The ID will always stay the same but occasionally the value will change.

How can I write a query that will give me the ID plus the most recent time the value has changed? Note: the value will always increase.

From this sample data:

  Create Table Taco
 (  Taco_ID int,
    Taco_value int,
    Taco_date datetime)

Insert INTO Taco
Values (1, 1, '2012-07-01 00:00:01'),
        (1, 1, '2012-07-01 00:00:02'),
        (1, 1, '2012-07-01 00:00:03'),
        (1, 1, '2012-07-01 00:00:04'),
        (1, 2, '2012-07-01 00:00:05'),
        (1, 2, '2012-07-01 00:00:06'),
        (1, 2, '2012-07-01 00:00:07'),
        (1, 2, '2012-07-01 00:00:08')

The result should be:

Taco_ID      Taco_date
1            2012-07-01 00:00:05

(Because 00:05 was the last time Taco_Value changed.)

These two queries rely on the assumption that Taco_value always increases over time.
;WITH x AS
(
  SELECT Taco_ID, Taco_date,
    dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date),
    qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date)
  FROM dbo.Taco
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC)
  FROM x WHERE dr = 1
)
SELECT Taco_ID, Taco_date
FROM y
WHERE rn = 1;

An alternative with fewer window function madness:
;WITH x AS
(
  SELECT Taco_ID, Taco_value, Taco_date = MIN(Taco_date)
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM x
)
SELECT Taco_ID, Taco_date FROM y WHERE rn = 1;

Examples at SQLfiddle

Update
For those keeping track, there was contention over what happens if Taco_value could ever repeat. If it could go from 1 to 2 and then back to 1 for any given Taco_ID, the queries will not work. Here is a solution for that case, even if it isn't quite the gaps & islands technique that someone like Itzik Ben-Gan may be able to dream up, and even if it isn't relevant for the OP's scenario - it may be relevant to a future reader. It's a little more complex, and I also added an additional variable - a Taco_ID that only ever has one Taco_value.
If you want to include the first row for any ID where value didn't change at all in the entire set:
;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT
  main.Taco_ID,
  Taco_date = MIN(CASE
    WHEN main.Taco_value = rest.Taco_value
    THEN rest.Taco_date ELSE main.Taco_date
  END)
FROM x AS main LEFT OUTER JOIN rest
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

If you want to exclude those rows, it's a bit more complex, but still minor changes:
;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT
  main.Taco_ID,
  Taco_date = MIN(
  CASE
    WHEN main.Taco_value = rest.Taco_value
    THEN rest.Taco_date ELSE main.Taco_date
  END)
FROM x AS main INNER JOIN rest -- ***** change this to INNER JOIN *****
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
AND EXISTS -- ***** add this EXISTS clause *****
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

Updated SQLfiddle examples


AWR dbms_workload_repository ash_global_report_text extract text from output I want to build a scheduled job in Enterprise Manager to trigger every hour the following query:
SELECT output FROM TABLE(dbms_workload_repository.ash_global_report_text(584958394, Null, SYSDATE-1/24, SYSDATE, l_wait_class=>'Scheduler'));
I want to capture Events and the percentage only if I get one with Scheduler resmgr:cpu quantum
And then I want to parse this output only out.
I get now the big varchar(320) column output when I hit the query, but I want also to build a PL/SQL package where I can add variables inside and also to parse out only the important percentage of the resmgr: cpu quantum event. Other events are not important to me.
Any help would be appreciated.
Kind regards.

The DB time can be queried from dba_hist_sys_time_model.
resmgr: cpu quantum can be queried from dba_hist_system_event.
snap_id can be matched to time periods through dba_hist_snapshot.
Instead of generating a report then parsing it, use the above views to query what you need.


Deadlock Error from SQL Server 2000 to SQL Server 2008 After migrating  data from  SQL Server 2000 to SQL Server 2008, when I am doing any a transaction in the table which find out more row on  the database, that's gives me error.

 Transaction (Process ID 59) was deadlocked on lock resources with
 another process and has been chosen as the deadlock victim.
 Rerun the transaction.

There are 3 triggers on the table (Insert,Update,Delete). How can I solve  this problem?

After upgrading to SQL Server 2008, did you run the below?

DBCC UPDATEUSGAE
UPDATE STATS WITH FULLSCAN
REBUILD ALL INDEXES

The statistics blob is changed significantly from 2000 to SQL Server 2008 and the above steps are very important after the upgrade. I have seen many ppl run into performance problems but not deadlocks. I would try doing all of the above if you haven't so far and try to reproduce the problem afterwards.

That said, what is the results of SELECT @@VERSION on the 2008 box? Are you updating one row or several rows?


Simplifying query by adding Computed Column, performance impact? The dev team came across the following query (might be incomplete; it's retyped from a screenshot) which had caused slowness:

SELECT d.catalogno, d.itemid,
  SUM(lineamount+taxamount) AS Amount,
  SUM(qty) AS Qty,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.basiccostprice
  END AS cog,
  d.salesprice,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.salesprice/d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.salesprice/d.basiccostprice
  END AS coeff
FROM custinvoicetrans t
WITH (nolock)
INNER JOIN
  pwlmy_dcatalog d
  WITH (nolock)
  ON t.itemid=d.itemid
INNER JOIN
  pwlmy_hcatalog h
  WITH (nolock)
  ON h.catalogno=d.catalogno
WHERE
  t.invoicedate>=h.effectivedatefrom
GROUP BY
  d.catalogno, d.itemid,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.basiccostprice
  END,
  d.salesprice,
  CASE
    WHEN d.costprice>=d.basiccostprice THEN d.salesprice/d.costprice
    WHEN d.costprice<d.basiccostprice THEN d.salesprice/d.basiccostprice
  END

I'm wondering... if I create a view like such:

CREATE VIEW pwlmy_dcatalog_cog AS
  SELECT *,
    CASE
      WHEN costprice>=basiccostprice THEN costprice
      WHEN costprice<basiccostprice THEN basiccostprice
    END AS cog
  FROM pwlmy_dcatalog

I should be able to simplify the query to the following:

SELECT d.catalogno, d.itemid,
  SUM(lineamount+taxamount) AS Amount,
  SUM(qty) AS Qty,
  d.cog,
  d.salesprice,
  d.salesprice/d.cog AS coeff,
FROM custinvoicetrans t
WITH (nolock)
INNER JOIN
  pwlmy_dcatalog_cog d
  WITH (nolock)
  ON t.itemid=d.itemid
INNER JOIN
  pwlmy_hcatalog h
  WITH (nolock)
  ON h.catalogno=d.catalogno
WHERE
  t.invoicedate>=h.effectivedatefrom
GROUP BY
  d.catalogno, d.itemid,
  d.cog,
  d.salesprice,
  d.salesprice/d.cog

Do you think the simplified query will have a significant impact to overall performance?

Note: I don't dare add a computed column into the pwlmy_dcatalog table; codes relying on that table might break spectacularly.

Moving the calculation to the view won't change performance - it's still done at runtime. So all you've changed is the complexity of the outer query.

In order to change performance you would need to add the computed column to the table, and persist it or index it. This means the work of the calculation is done at insert/update time instead of query time. (Persisting only works in 2005+.)

Now, other factors are at play too. For example, do you have any evidence that this calculation actually affects performance? Maybe the query performs the same without it? Also using the view may cause other performance issues, for example if it involves additional columns, calculations or tables that this query doesn't actually need.


PostgreSQL archive_command on Windows What is the best method for WAL log transfer on Windows?

I have two servers with Windows Server 2008, PostgreSQL 9.0 64bit.

Servers do not have any shared SAN disk, they are on different hosts, different subnets.

Files from the first server must be transferred in reliable way to the second server.

Archive location must be protected by password.

I have tried to use free rsync/rsyncd solution called DeltaCopy, but permissions part is hard for me. The service does not want to run under "postgres" account. It runs as System Local acount but the files are not readable by postgres. If I use incoming chmod option in deltacd.conf (aka rsyncd.conf), it "garbles" Windows file permissions, so I have problems with some actions on file's "Security" tab.

I would like some solution which is reliable, and easy to setup and maintain.

One respondent suggested xcopy - but it has no option to provide passowrd from command line or password file.

thanks

Have you considered Robocopy? It is able to copy NTFS ACLs. I guess you might need to have postgres running under the same Domain Account on source and target if possible but I'm probably out of my depth talking about such things.


How do I create a rule for a Primary Key I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together:

    p001
    b201

I know I need to create a rule and bind that rule to the primary key column.  I am little confused in creating the rule using a LIKE query:

CREATE RULE pK-rule AS @pk LIKE'[a-z][0-9][0-9][0-9]'

Can anybody suggest what I need to change?

You probably don't want to use rules and instead use constraints, in this case a check constraint.  The reason that you don't want to use rules is that rules have been deprecated means that they will be removed from SQL Server at some point in the future so it would be better to use the check constraint instead.  Something like this will do the trick.

create table t1 (c1 char(4) primary key check (c1 like '[a-z][0-9][0-9][0-9]'), c2 int)


Can Records be Replicated in an Availability Group Out of Sequence? A data warehouse ETL process is querying a read-only secondary in an availability group. The ETL process queries a single table incrementally using datetime range criteria of a minute and read committed isolation level. At the time of execution, 5 records that meet the criteria are committed on the primary, but another 3, with slightly earlier timestamps than the first 5 (but within the criteria range) are still in open transactions. Does the nature of availability groups require all transactions to be applied in LSN order (delaying the visibility of all 8 records until all are committed) or do the delayed 3 records get later LSNs and are applied as soon as they are committed, potentially after the ETL process has adjusted its date criteria?

At the time of execution, 5 records that meet the criteria are committed on the primary, but another 3, with slightly earlier timestamps than the first 5 (but within the criteria range) are still in open transactions. Does the nature of availability groups require all transactions to be applied in LSN order...

REDO requires that LSNs be applied in sequence order, this doesn't change inside or outside of an AG. When the REDO thread is working, it'll have to do the log records in order.

  ... (delaying the visibility of all 8 records until all are committed) or do the delayed 3 records get later LSNs and are applied as soon as they are committed, potentially after the ETL process has adjusted its date criteria?

There is no delaying. In Availability Groups we don't ship individual transactions. We also don't wait for them to commit.

The unit of transport is a LOG BLOCK which is a collection of LOG RECORDS, in order. These log blocks hold log records from many different transactions in the database and not just the ETL ones you have going on. Additionally, the log blocks are sent when they are closed and flushed to disk, which happens for a few different reasons and does not necessarily need to be from a commit.

This means the secondary receives information on all transactions that are going on in the database that are making changes. In this case, the secondary has some portion, if not all, of the information about the earlier transactions that are still open.

Now, this hits on another point. Readable secondary servers aren't using read committed isolation level, in fact it is mapped to snapshot isolation under the covers. This is the reason you cannot see any information on the secondary pertaining to the earlier 3 transactions. You cannot add nolock to the query, it won't let you see the data.

Since snapshot isolation is used, we're going to use the version store. This means if you started a transaction on the secondary to read information and it is still running when the open transactions on the primary are committed, it will not "see" them until the transaction is over. This is part of the isolation of snapshot.

To summarize:

Log blocks are shipped, not log records, and NOT just committed transactions
Yes, there is information already on the secondary about those transactions
Readable secondary servers have read committed (default) isolation level mapped to snapshot isolation level automatically
If you need the data on the secondary, avoid large long running transactions