Update statement with "case syntax" is unable to update a specific column in temp table With reviewing a stored procedure for a current SSRS migration. I noticed the case syntax listed below for the temp table (#tempShip) is not taking affect for the column "ord_status" in the temp table. The stored procedure that collects data for the temp table is rather massive so I will refrain from posting. But I believe with the code provided for the update it may do some justification.

Questions:

Can you utilize a case statement on a temp table as an update?
Is there another method I should try besides the case statement? (I don't think using a subquery with "IF" or "WHERE EXISTS with a select statement" would aid in this)

Notes:

***Stored procedure executes without any errors and temp table is populated with data as expected (But update with case statement does not have expected results)

***With doing a simple case statement as seen below the translation occurs fine

Basic query to test case translation of ord_status data in temp table. Output is correct

select ord_status,
 CASE
     WHEN RTRIM([ord_status]) = 'N' THEN 'New'
     WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
     WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
     ELSE ''
 END
 from #tempShip

***End of stored procedure where temp table is being updated

UPDATE  [#tempShip] SET
        [#tempShip].[DUP] = 1,
        [#tempShip].[ord_status] =
           CASE
                 WHEN RTRIM([ord_status]) = 'N' THEN 'New'
                 WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
                 WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
                 ELSE ''
           END

        FROM #tempShip
         INNER JOIN
    (
        SELECT X.order_N_ext, COUNT(X.order_N_ext) AS dup
        FROM #tempShip AS X
        GROUP BY X.order_N_ext
    ) AS Y
         ON #tempShip.order_N_ext = Y.order_N_ext
    WHERE Y.dup > 1;
    ----- -- mbs 3/30/2017  #22844 - add parameter @ship_type
    SELECT * FROM #tempShip
    WHERE ship_type LIKE @ship_type
    -------<<<

END
DROP TABLE #tempShip

**order_N_ext

SELECT O.order_no
    , O.ext
    , CONVERT(varchar(20), O.order_no) + ' - ' + CONVERT(varchar(2), O.ext)
AS 'order_N_ext'

Data Example:

Ord_Num  BD order_N_ext Loc     Cust        Ship_date                      Ord_Status
1252868  1  1252868 - 1 RAS HQ  21115   2017-04-19 00:00:00.000 2570.63    P
1253995  1  1253995 - 1 RAS HQ  11471   2017-04-19 00:00:00.000 1270.45    Q
1254526  0  1254526 - 0 RAS HQ  24104   2017-04-19 00:00:00.000 2214.67    P

Here is the solution: (The update statements had to be rearranged so they executed on there own.)

UPDATE  [#tempShip] SET  [ord_status] = CASE
                WHEN RTRIM([ord_status]) = 'N' THEN 'New'
                WHEN RTRIM([ord_status]) = 'Q' THEN 'Printed'
                WHEN RTRIM([ord_status]) = 'P' THEN 'Picked'
                ELSE ''
                END

UPDATE                 [#tempShip] SET           [#tempShip].[DUP] = 1
FROM #tempShip
INNER JOIN
(
SELECT X.order_N_ext, COUNT(X.order_N_ext) AS dup
FROM #tempShip AS X
GROUP BY X.order_N_ext
) AS Y
ON #tempShip.order_N_ext = Y.order_N_ext
WHERE Y.dup > 1;
    ----- -- mbs 3/30/2017  #22844 - add parameter @ship_type
    SELECT * FROM #tempShip
    WHERE ship_type LIKE @ship_type
    -------<<<


Always On AG is Down when service is stopped I have SQL Server 2014 running on Windows Server 2012 R2.
There are three nodes, one is remote DataCenter. So two nodes have voting.
Failover Cluster is using file share witness on remote server.
If primary server is down, AG automatic Failover works but when SQL Server is down AG automatic failover does not work. AG waits on Resolving state.
Also:

I stopped service from configuration manager, AG Failover is success.
Stopped service from services.msc, AG Failover is failed.
Killed service from Proccess List, AG Failover is failed.

Voting;

Server         Assigned Vote       Current Vote
Node 1              1                   1
Node 2              1                   1
Node 3              0                   0

I disabled firewall for all network, nothing changed, problem continuous.
Can someone help me to solve problem?

Critical Error
The role of this availability replica is unhealthy.
The replica does not have either the primary or secondary role.

Failover is from node1 to node2. Both are set to synchronous and automatic failover. Nodes are not in a paused state. I have installed all nodes as stand alone instances.

It could be the issue described in INF: AlwaysOn – The secondary database doesn’t come automatically when the primary instance of SQL Server goes down by Arvindh Kalidasan - Support Engineer, Microsoft GTSC.

In this blog we would discuss about behavior of AlwaysOn availability group where the secondary database doesn't come automatically when the primary instance goes down. The secondary database goes into Resolving state. On the failover cluster manager the resource appears in fail state.
[...] we found that if we stop SQL Service manually on primary replica, it fails over to second node only once. Any further attempts of stopping SQL Service (to test auto failover) would not cause failover.

The workaround posted there is:

[...] to have the value set to a higher number for "Maximum Failures in the specified period".

Maximum Failures in the specified period: set to 60
Period (Hours): set to 1


Oracle: How to get a list of CTXSYS.CONTEXT indices? I can get a list of indices by querying USER_OBJECTS.

How can I distinguish which of these indices are of type CXTSYS.CONTEXT?

USER_INDEXES (and ALL_INDEXES) contains this information:

select index_name, table_name, index_type
from user_indexes
where ityp_owner = 'CTXSYS'
  and ityp_name = 'CONTEXT'


How to design table to allow marking records as related to each other? My web app's main MySQL table has tens of thousands of rows. Pretend it has 2 columns: ID, Content.
I'm trying to design a new feature that allows rows to be marked as related to each other.
Obviously I could add a 3rd column: Related_IDs. It could either be a JSON array or could be a simple CSV string.
But that feels wrong. I'd need to update the Related_IDs field of each row of a group of N rows to be a comma-separated list of IDs of all of the rows in the group except itself. Every time any one of those records becomes unrelated, I'd need to iterate through the group and clean up that field for each.
What is a smarter approach?

A bridge table
related_id  Related_TO_ID
     13           2
     13          12

If 1-->2  is equal to 2-->1 you need one row else you need two.
Now You can get all ids that are related to 13
And if you want to exclude some ration ships you can add a column that indicates that 13 is never related to 42
SELECT Related_TO_ID FROM brdgetable WHERE related_id   = 13

Why you never should save data in delimitedcolumns you can read here


Does pg_export_snapshot work across replicas? This is probably of little importance, but it's possible in PostgreSQL to retrieve the transaction snapshot with pg_export_snapshot and restore it with set transaction to start a second transaction with the same level of visibility (see the documentation).

I'm interested to know if that works on a hot-standby or is specific to a single server? Could you export a snapshot on the standby and then use it with set transaction on the master? I doubt it.

I just tried it, and it is not possible, because exported snapshots are not replicated.

Snapshots are persisted in the pg_snapshots subdirectory of the data directory, and you will see that they don't show up on the standby.


How to bulk insert data in vertical into sql server? My data in below format. I have to insert into sql server table.

agency    NO
booknbr   06000330
bookdttm  2006-07-19 10:56:00
arrdttm   2006-05-07 05:42:00
aj_id     A000009454
casenbr   06006640
off1      619
off2      634
arrplace  DENNYS RESTAURANT
howarr    O
juvstat
ko
remarks
armed
federal   N

agency    NO
booknbr   06000331
bookdttm  2006-07-24 12:11:00
arrdttm   2006-07-16 16:11:00
aj_id     A000003215
casenbr   06010336
off1      641
off2
arrplace  219 W GLENCOVE AV
howarr    V
juvstat
ko
remarks
armed
federal   N

agency    NO
booknbr   06000332
bookdttm  2006-07-25 15:11:00
arrdttm   2006-06-13 22:47:00
aj_id     A000009455
casenbr   06008615
off1      624
off2
arrplace  113 JULIE DR
howarr    V
juvstat
ko
remarks
armed
federal   N

You can use BULK INSERT and provide the FIELDTERMINATOR and ROWTERMINATOR values like below which will insert the data into the correct columns. The only downside is the data also includes the column name. A simple cursor to clean this up and the data looks fine.

You would want to insert into a staging table as all data types are VARCHAR on insert, then you could insert into a final data table that has correct data types after cleaning.

Create Table to Store Data:

CREATE TABLE dbo.SampleCSVTable
(
    agency    VARCHAR(255),
    booknbr   VARCHAR(255),
    bookdttm  VARCHAR(255),
    arrdttm   VARCHAR(255),
    aj_id     VARCHAR(255),
    casenbr   VARCHAR(255),
    off1      VARCHAR(255),
    off2      VARCHAR(255),
    arrplace  VARCHAR(255),
    howarr    VARCHAR(255),
    juvstat   VARCHAR(255),
    ko        VARCHAR(255),
    remarks   VARCHAR(255),
    armed     VARCHAR(255),
    federal   VARCHAR(255),
)
GO

BULK INSERT the data:

BULK INSERT dbo.SampleCSVTable
FROM 'D:\SQLDATA\SQL01DEV\SampleData.csv'
WITH
(
    FIELDTERMINATOR = '\n',
    ROWTERMINATOR = '\n\n'
)
GO

Clean the data (this cursor loops through each column and removes the column name from the inserted data):

DECLARE @ColName NVARCHAR(255),
    @SqlCmd NVARCHAR(255)

DECLARE C1 CURSOR FOR
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SampleCSVTable'

OPEN C1

FETCH NEXT FROM C1 INTO @ColName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SqlCmd = 'UPDATE dbo.SampleCSVTable SET [' + @ColName + '] = LTRIM(RTRIM(REPLACE([' + @ColName + '], ''' + @ColName + ''', '''')));'
    EXEC sp_executesql @SqlCmd

    FETCH NEXT FROM C1 INTO @ColName
END

CLOSE C1

DEALLOCATE C1

Query the data:

SELECT * FROM dbo.SampleCSVTable

Results:

agency  |   booknbr     |   bookdttm            |   arrdttm             |   aj_id       |   casenbr     |   off1    |   off2    |   arrplace            |   howarr  |   juvstat |   ko  |   remarks |   armed   |   federal
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NO      |   06000330    |   2006-07-19 10:56:00 |   2006-05-07 05:42:00 |   A000009454  |   06006640    |   619     |   634     |   DENNYS RESTAURANT   |   O       |           |       |           |           |   N
NO      |   06000331    |   2006-07-24 12:11:00 |   2006-07-16 16:11:00 |   A000003215  |   06010336    |   641     |           |   219 W GLENCOVE AV   |   V       |           |       |           |           |   N
NO      |   06000332    |   2006-07-25 15:11:00 |   2006-06-13 22:47:00 |   A000009455  |   06008615    |   624     |           |   113 JULIE DR        |   V       |           |       |           |           |   N


SSAS / MDX Query - Add an extra dimension when using Order I wish to add an extra dimension to this query, so that I'm able to see the total amount of an item sold, per month/year:

SELECT
 NON EMPTY
 Order([Item].Children, Measures.[Total Line Value], DESC) ON COLUMNS,
 { [Total Line Value] } ON ROWS
 FROM        [Sales Analysis]

What I am looking for is something like so:

SELECT
 NON EMPTY
 Order([Item].Children, Measures.[Total Line Value], DESC), [January 2010].Children ON COLUMNS,
 { [Total Line Value] } ON ROWS
 FROM        [Sales Analysis]

This will eventually allow me to see the total sales of items for a particular month and year, but this throws an error as I'm not sure how to achieve it.

EDIT:

To clarify. The first query above works. It returns the grand total of items sold in order from highest to lowest BUT its for all time. I wish to restrict this to just show the order from highest to lower for just 1 month only, that being January 2010. I have a time field in my cube where it's possible to select the month and year like so: [January 2010].Children - I'm able to view this in excel, I just need to be able to view this in my application.

I have a similar query, this one returns all the sales, for each day of January 2010:

SELECT
NON EMPTY   { [January 2010].Children } ON COLUMNS,
            { [Total Line Value] } ON ROWS
FROM        [Sales Analysis]

I just want to split this down further to show each individual item's sales for that month, in order from highest to lowest.

I'm not quite sure what you're asking, but I think you want to do the following (correct me if I'm wrong):

You want to see a breakdown of sales by month, one row for each month, with a total for all months at the bottom.
You want to see the items across the columns, ordered by the item with the largest sales volume to the smallest.

select non empty
       order ([Item].[Item].Children
             ,([Measures].[Total Line Value]
              ,[Date].[Month].[All])
             ,BDESC)
    on Columns
       non empty
      ,[Date].[Month].Children
    on Rows
  from [Sales Analysis]
 where [Measures].[Total Line Value]

The columns are each individual item, sorted by the total Line Value over all months, i.e. from all-time best seller to all-time lowest seller, with items having no sales at all excluded.

The rows are the months for all time where data exists.  You can use a set definition to specify any date range you want.  Note that this assumes the existence of a dimension called [Date].

This query will show the items in order of total sales across the columns and the sales for each month by item down the rows.  I'm not 100% sure if this is what you want; feel free to clairfy your question if I'm not on the right track.


Access 2013 update rows from multiple rows I need help to construct an update query to populate MynewField with contents from multiple fields and rows where the MapID is the same.

RowID---MapID-----Extension-----  Name------    MynewField
7        318        #222                         [#222Tom]
22       318                       Tom           [#222Tom]

You can't write a single query to do that in Access but a 'make table query' and a separate 'update query' combo will work. Change to suit your needs.

    select MyTable.RowID
        ,[t1].[MaxOfExtension] & [t1].[MaxOfName] as NewValue
    into t2
    from MyTable
    inner join (
        select MyTable.MapID
            ,Max(MyTable.Extension) as MaxOfExtension
            ,Max(MyTable.name) as MaxOfName
        from MyTable
        group by MyTable.MapID
        ) as t1
        on MyTable.MapID = t1.MapID;

    update MyTable
    inner join t2
        on MyTable.RowID = t2.RowID
    set MyTable.MyNewField = [t2].[NewValue];


Are 2+ Foreign Keys a Bad Idea in any Association / Junction table? (First time poster, very very long time lurker. I cannot find an answer to this question anywhere, so I now come out from the shadows to ask the experts).

Is a junction table with say 4 foreign keys (4 tables), a good idea for tables that are only to be Read as a Lookup and not changed regularly, and which contain static data and would only be changed by an admin and not a 'user'?

I'll give a simple example.

Say we have four tables each has only two fields - an ID number field  (Primary Key) and a 'Name' (text) field: tblCarModelName, tblCarType, tblManufacturer, tblColor.

And then we have a junction table (tblCars) which is designed as follows:

Car_ID (PK)
CarModelName_IDFK (FK)
CarType_IDFK (FK)
Manufacturer_IDFK (FK)
Color_IDFK (FK)

The table could look like (please note the IDFKs will be integers not strings as illustrated here):

Car_ID, CarModelName_IDFK, CarType_IDFK, Manufacturer_IDFK, Color_IDFK
1,Hilux,Truck,Toyota,White
2,SLK300,Sedan,Mercedes,Blue
3,SLK500,Sedan,Mercedes,Silver
4,Prius,Coupe,Toyota,White
....

So the table ends up looking like a spreadsheet. But why would I want to do this?

Makes data entry and table maintenance easy.
I can still query the table for a particular say 'Manufacturer'
because of the foreign key reference.
I can paste data structured in the same way from Excel directly into
the table.
And also, more importantly, many-to-many relationships are
handled in this example.
If I offer this table the exact same way in a user interface form, I
know when the user chooses from this list, say ID = 3 and that they
have chosen "SLK500,Sedan,Mercedes,Silver" from the list.

Is this design ideal? Feedback is appreciated.

What is this model called anyway?

There is nothing wrong (in principle) with a three way (or more) intersection table, as long as it properly describes your relation.

However: Your particular table is not in third normal form (3NF).

You should generally normalize to 3NF unless you have good reason not to.

The problem is that some of your data depends on part of the key, instead of on the whole key.  This means your table is only in second normal form (2NF).

Consider this:  Your table would allow a record that says a Prius is a Toyota and another record that says a Prius is a Chrysler.  Obviously that's not good.

You want to remove the hierarchy of make and model into a separate table.

Similarly, the type of a car is dependent on the model, but not on the colour.

So what you need to do is separate these columns too.  What you really need are four tables:

Manufacturers (manufacturer name)
Models (model name, FK to Types and FK to Manufacturers)
Types (type name)
Cars (FK to Models, colour)

This would give you a normalized schema with no redundancy and very little risk of data corruption due to insert, update and delete anomalies.


MySQL - Grant user permission to create table as long is it has a prefix in the name I'd like to grant a user permission to create tables in a database so long as the table has a particular prefix, let's say example_.

I've tried the following:

$ mysql -u root -p
mysql> CREATE DATABASE example;
mysql> CREATE USER xyz@localhost IDENTIFIED BY 'password';
mysql> GRANT CREATE ON example.`example_*` to xyz@localhost;

However, when I log in to example as xyz I'm unable to create tables:

$ mysql -p -u xyz example
mysql> CREATE TABLE example_table (id INT);
ERROR 1142 (42000): CREATE command denied to user 'xyz'@'localhost' for table 'example_table'

Is this even possible? If so, how?

There is not a wildcard facility of that sort.

Don't prefix columns with the table name.    If you need qualification, do tablename.columnname.

Don't prefix tables with the table name.  If you need qualification, do dbname.tablename.

If necessary, move all the examples into a separate db and GRANT suitable permissions to suitable users for that database.


Check difference between results not listed and identify which day is the date I have the following query:

SELECT
    V.CHAPA,
    F.NOME,
    V.DATA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 9132
        AND BATIDA IS NOT NULL
        AND V.CODCOLIGADA = 1
        AND  YEAR ( V.DATA ) = 2016
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA
          ORDER BY V.DATA ASC

It returns me:

1234    JOAO DA SILVA   2016-09-02 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-03 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-04 00:00:00.000 2
1234    JOAO DA SILVA   2016-09-05 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-06 00:00:00.000 4

>>> 1234    JOAO DA SILVA   2016-09-07 00:00:00.000 2
>>> 1234    JOAO DA SILVA   2016-09-09 00:00:00.000 4

1234    JOAO DA SILVA   2016-09-10 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-11 00:00:00.000 2
1234    JOAO DA SILVA   2016-09-12 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-13 00:00:00.000 4

>>> 1234    JOAO DA SILVA   2016-09-14 00:00:00.000 4
>>> 1234    JOAO DA SILVA   2016-09-16 00:00:00.000 4

As you can see in places marked with >>> there is no record that date, eg the date jumps 7 to 9 date and the date 14 jumps to 16, it ta correct because that day had no change.

What do I need to know: How many ente day on 08 and 15 which are the days that has no movement because this period can not exceed 7 days.

Another vestment I need is: At the same query:

SELECT
    V.CHAPA,
    F.NOME,
    V.DATA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 9132
        AND BATIDA IS NOT NULL
        AND V.CODCOLIGADA = 1
        AND  YEAR ( V.DATA ) = 2016
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA
          ORDER BY V.DATA ASC

I need to identify what day of the week is the date, and list only Sunday.

How could proceed with these two cases?

  Note: I'm sorry if you have any clerical error and because I am not
  good at English and used a translator.

Check out my answer to the following question on StackOverflow

https://stackoverflow.com/questions/3093924/select-all-months-within-given-date-span-including-the-ones-with-0-values/3111441#3111441

You could easily apply this method to your problem, and return data for all the days between a range, including those with no data content.

Dave


MAIL DBCC CHECKDB RESULTS I want to regularly run DBCC CHECKDB on my production database. My database is real-time and DBCC CHECKDB is very expensive query, that`s why I want to write a maintenance plan to execute the query on nights when the activity is low. I want to set the query results to be sent as mail in order to check it when I am able. My question is, how can I set the query results to be sent to my e-mail when it is executed ?

I'm sure there may be other ways of doing this, but at my shop, I run DBCC CHECKDB via SQLCMD bat files and use the -o parameter to route the output to a flat text file.  Then, in the same bat file, I use SQLCMD to invoke

EXEC msdb.dbo.sp_send_dbmail

and attach the results of the DBCC

This process could be scheduled via either Windows Task Scheduler or you could create a Sql Agent job to run 'Operating System (CMDEXEC) to invoke the SQLCMD.

If you wanted to loop through each online database and run CHECKDB, you could create a utility stored procedure like this with the DBCC options that you want to use. This example skips TEMPDB, but I run a separate automated task for that database alone due to frequent timeouts.

It prints the database name, start time and end time.

Create PROCEDURE [dbo].[DBCC_CheckAllDatabases]
AS
BEGIN
    DECLARE @Message VARCHAR(max);
    DECLARE @databaseList as CURSOR;
    DECLARE @databaseName as NVARCHAR(500);
    DECLARE @tsql AS NVARCHAR(500);

    SET @databaseList = CURSOR  LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
          SELECT QUOTENAME([name])
          FROM sys.databases
          WHERE [state] = 0 and [name]<>'tempdb';
    OPEN @databaseList;
    FETCH NEXT FROM @databaseList into @databaseName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
       SET @Message = 'Starting DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
       RAISERROR(@Message,0,1) WITH NOWAIT
       SET @tsql = N'DBCC CheckDB(' + @databaseName + ') WITH ALL_ERRORMSGS,NO_INFOMSGS,PHYSICAL_ONLY;';
       RAISERROR(@tsql,0,1) WITH NOWAIT
       EXECUTE (@tsql);
       SET @Message = 'Completed DBCC for ' + @databaseName + ' -> (' + convert(varchar,getdate(),121) + ')'
       RAISERROR(@Message,0,1) WITH NOWAIT

       FETCH NEXT FROM @databaseList into @databaseName;
    END
    CLOSE @databaseList;
    DEALLOCATE @databaseList;
END

You could also utilize Aaron Bertrand's InEachDB process, if you don't like my example for looping through the databases.


What is meant by DDBB? Today we got a term DDBB. I am not sure whether it refer normal database or database driver in certain programming language.

Google search did not help me much in this. And I am sure it is not 'Dirty Dozen Brass Band'.

BBDD are Bases de Datos in this funny way in which Spanish refers to plural for acronyms (EEUU, Estados Unidos = United States).
http://forum.wordreference.com/showthread.php?t=390785


Why 2 allocation units during online index rebuild to different file group? I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done yet.

Strangely, the table appears to have too many allocation units!

Old file group, IN_ROW_DATA (normal)
Old file group, ROW_OVERFLOW_DATA (normal)
New file group, IN_ROW_DATA (normal)
New file group, ROW_OVERFLOW_DATA (normal)
New file group, IN_ROW_DATA (again!)

So two IN_ROW_DATA in the destination file group! One of them has data compression as NONE, one of them has PAGE. I'm doing the index rebuild WITH (DATA_COMPRESSION = PAGE, ONLINE = ON). The table is not partitioned.

You can see for yourself:

SELECT idx.name
, au.allocation_unit_id, au.type_desc, au.container_id, au.filegroup_id, au.used_pages, au.root_page
, par.index_id, par.rows, par.data_compression_desc
FROM sys.system_internals_allocation_units au
LEFT JOIN sys.partitions par ON (au.container_id = par.hobt_id AND au.type IN (1, 3)) OR (au.container_id = par.partition_id AND au.type IN (2))
LEFT JOIN sys.indexes idx ON par.object_id = idx.object_id AND par.index_id = idx.index_id
LEFT JOIN sys.objects obj ON par.object_id = obj.object_id
WHERE obj.name = 'X'
ORDER BY obj.name, idx.name, par.partition_number, au.filegroup_id, au.type_desc

File group 2 is the destination. Both IN_ROW_DATA allocation units have lots of pages in them so it is not like one of them is some kind of dummy. Also, the table is taking more than 2x the space that it should at this point. Clearly, the DATA_COMPRESSION setting didn't take!

Question: Why are there two allocation units in the same partition? Why doesn't my DATA_COMPRESSION setting cause the data to be compressed?

Edit: Lines 1 and 4 seem to correspond to the same data. They are both in the destination and they have the same row count. It seems like all rows are written twice - once compressed and once uncompressed. I can confirm the space is being used (or at least written and marked as allocated).

Edit 2: The INSERT DML plan for my table under rebuild shows an insert into 3 HOBT's. Not just 2 which would be expected during an online index rebuild. There are no non-clustered indexes defined. Here is the plan:

Edit 3: The rebuild has completed. The uncompressed (huge) partition is gone. The only thing left is the compressed one. Unfortunately, the allocated extents of both have been intermixed during writing. Using a visualizer tool that I have written, it looks like this:

(This is a crop of <1% of the whole allocation bitmap). Black = allocated, White = free (formerly the temporary partition).

That is a terrible outcome for sequential IO! So I switched out the table to an empty copy of it to be able to perform an ONLINE = OFF rebuild on it to a fresh file group (it is an insert-only table so I can switch it out whenever I want). That fixed things: The table is now contiguous and no temporary partition was seen.

Still, the question remains: Why does the ONLINE = ON CI rebuild cause all these nasty effects? How to fix it?

How Online Index Operations Work:

  Temporary mapping index

  Online index operations that create, drop, or rebuild a clustered
  index also require a temporary mapping index. This temporary index is
  used by concurrent transactions to determine which records to delete
  in the new indexes that are being built when rows in the underlying
  table are updated or deleted. This nonclustered index is created in
  the same step as the new clustered index (or heap) and does not
  require a separate sort operation. Concurrent transactions also
  maintain the temporary mapping index in all their insert, update, and
  delete operations.

Disk Space Requirements for Index DDL Operations

  If a clustered index is created, rebuilt, or dropped online, a
  temporary nonclustered index is created to map old bookmarks to new
  bookmarks. If the SORT_IN_TEMPDB option is set to ON, this temporary
  index is created in tempdb. If SORT_IN_TEMPDB is set to OFF, the same
  filegroup or partition scheme as the target index is used. (My note: default is OFF)

Index ID 254 is indeed the mapping index. The Online Indexing Operations in SQL Server 2005 whitepaper has more details.


SQL Server 2012 AlwaysOn cannot connect using the Listener IP I have setup a Windows network in an Amazon in a VPC. I have setup two SQL servers and configured Always On. Everything works OK, except that I cannot connect to the SQL Server using the AAG Listener IP from any other computer in the network except from the Primary Node.

I also cannot ping the Listener IP or the Cluster IP.

For the servers the IPS are specified on the NIC.

DC: 10.0.0.100
SQL1: 10.0.0.90
SQL2: 10.0.0.92
Cluster: 10.0.0.80
AAG ListenerL: 10.0.0.82

Any ideas?

I got a response from Amazon

"Cluster IPs aren't supported in VPC at this time."


How to merge records in SQL I have a table with multiple records for each ID.

For example, there may be three or more records for one Class ID): one record with a status of "approved" and two more records with status NULL, or one record with the approved field populated with some numerical value and another two records will approved set to null, or one record with the requested field set to some numerical value and two other records with requested set to be null.

As output, I want one record with status "approved", and with approved and requested populated with their numerical values.

The question via an example:

Now, I want a single record with column T approved  and COLUMN W,X WITH 12 VALUE

EXAMPLE:

My guess is the question is something like:

I have a table that looks something like this:

+----+----------+----------+---------+
| ID |  Status  | Approved | Request |
+----+----------+----------+---------+
|  1 | NULL     | 5        | NULL    |
|  1 | Approved | NULL     | NULL    |
|  1 | NULL     | NULL     | 10      |
+----+----------+----------+---------+

And you are looking to combine all these rows into just the NON-Null values for each column.  Something like:

+----+----------+----------+---------+
| ID |  Status  | Approved | Request |
+----+----------+----------+---------+
|  1 | Approved |        5 |      10 |
+----+----------+----------+---------+

For the purpose of the examples I will show below I have created the below table.  (For the sake of cleanup ease on my side I am using Table Variables instead of a physical/temp table.)

DECLARE @Table TABLE
(
    ID INT,
    [Status] VarChar(20) NULL,
    Approved INT NULL,
    Request INT NULL
)

INSERT INTO @Table (ID, [Status], Approved, Request)
VALUES
(1, NULL, 5, NULL),
(1, 'Approved', NULL, NULL),
(1, NULL, NULL, 10)

There are a two ways to achieve this result that I am aware of:

1) Using Sub Queries in the SELECT statement.  Something like below:

SELECT T.ID,
T.[Status],
(
    SELECT TOP (1) temp1.Approved
    FROM @Table temp1
    WHERE temp1.ID = T.ID
    AND temp1.Approved IS NOT NULL
    --If Needed You can add additional WHERE clauses
    --Or you can add some kind of ORDER clause to get the value you want
) AS Approved,
(
    SELECT TOP (1) temp1.Request
    FROM @Table temp1
    WHERE temp1.ID = T.ID
    AND temp1.Request IS NOT NULL
    --If Needed You can add additional WHERE clauses
    --Or you can add some kind of ORDER clause to get the value you want
) AS Request
FROM @Table T
WHERE ID = 1
AND [Status] = 'Approved'
--If Needed You can add additional WHERE clauses

We are simply running a second and third select, selecting a single value (the TOP (1) to avoid an error where a subquery returns more than one result), and having that display as the result for that column.  The benefit of this option is it guarantees that we only get one row per instance of something in the table WHERE ID = 1 AND [Status] = 'Approved'.

2) Another option I am aware of is using additional JOINS.  Something like below:

SELECT T.ID,
T.[Status],
App.Approved,
Req.Request
FROM @Table T
    LEFT OUTER JOIN @Table App
        ON App.ID = T.ID
        AND App.Approved IS NOT NULL
    LEFT OUTER JOIN @Table Req
        ON Req.ID = T.ID
        AND Req.Request IS NOT NULL
WHERE T.ID = 1
AND T.[Status] = 'Approved'

This approach allows you to get more than just a single value for that row.  (Say there was some additional 5th column and you wanted that value from the "Request" row.  You can just add a Req.[ColumnName] in the SELECT).  This does have a possibility for returning multiple rows per ID = 1 AND [Status] = 'Approved' combination, if there are more than one row with ID = 1 AND Request IS NOT NULL OR ID = 1 and Approved IS NOT NULL.

There is a 3rd option using the XML STUFF:

Which allows you to combine multiple rows of the same column into a single "cell" using concatenation.  This is explained in great detail via this Stack Overflow Question.

Hopefully that give you what you were looking for.


How to query against exact values in XML column set I have a table that contains 80+ sparse columns along with a column set column, this is a brief example:

DROP TABLE IF EXISTS #ColumnSet
GO

CREATE TABLE #ColumnSet
(
    Id        INT          NOT NULL
  , Value1    VARCHAR(100) SPARSE NULL
  , Value2    VARCHAR(100) SPARSE NULL
  , Value3    VARCHAR(100) SPARSE NULL
  , Value4    VARCHAR(100) SPARSE NULL
  , AllValues XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
GO

INSERT INTO #ColumnSet
(Id, Value1, Value2, Value3, Value4)

VALUES
(1, 'POSITIVE', NULL, NULL, NULL),
(2, 'NEGATIVE', NULL, 'NEGATIVE', NULL),
(3, NULL, NULL, 'NEGATIVE', 'POSITIVE'),
(4, 'NEGATIVE', NULL, 'THIS IS NOT A POSITIVE RESULT', NULL)
GO

I want to query the column set to identify rows where any of the columns has a value of POSITIVE.

Using the value method on the column set will concatenate all the values together into one string and I could use LIKE but I don't want results where the value is within another string.

SELECT
    *

FROM
    #ColumnSet

WHERE
    AllValues.value('/', 'nvarchar(4000)') LIKE '%POSITIVE%'

Are there alternative methods of querying a column set to achieve the above? Using APPLY along with the nodes method provides the same concatenated string output though my syntax could be incorrect.

The required output :

id
1
3

Specifying the text() node before the predicate will be more efficient than having text() in the predicate.

select *
from #ColumnSet as C
where AllValues.exist('*/text()[. = "POSITIVE"]') = 1

Query plan with text in the predicate AllValues.exist('*[text() = "POSITIVE"]') = 1


SQL Server 2008 R2 with huge amount of transactions I am currently developing a project with a huge amount of database transactions.

My estimate is around 70,000 inserts/updates/deletes/selects every 2 minutes, since I am not there yet, I can only estimate, but in the near future, it'll get there.

Since I am not a DBA, I'd like to hear your opinion.

I am running on a Windows Server 2008 R2 and I thought I can use SQL Server 2008 R2, the questions is, how many processors and what type exactly of SQL Server I need to use? there are plenty like Enterprise, Standard, Web Edition, etc.

Any suggestions folks?

It is difficult to estimate server load based on "number of transactions" because cost of a transaction can vary greatly depending on the design of your DB and the nature of the query.  Enterprise and Datacenter editions are your versions to consider for situations with high volume and high scalability. Microsoft has comparison charts showing what the processor/memory limitations are for each, extra features, and virtualization restrictions. (Search: SQL Server version comparison)

What type of application is this supporting and for what business type?  Will it be performing strictly transactional OLTP tasks or are you looking to support reporting and analysis with (a separate instance of) the same product?


Linked servers on SQL Server 2008 R2 through linked tables in MS-Access? We have a really old data source (file-based) in Visual FoxPro, that we currently use via linked table through an ODBC data source.  Recently, we started having an issue because the latest update to our Anti-virus Suite is stopping it from connecting in this manner.  There are other ODBC data sources which do not use the same driver which do not have this issue.

Also the Visual FoxPro driver ODBC driver is being shoehorned in using the registry because it's no longer supported by Windows 7.

Is adding my Visual FoxPro data source as a linked server in SQL server and then in the MS-Access database, relinking the effected tables to it a possible solution to this issue?  Note: the MS-Access databases use VBA and queries to retrieve and manipulate the data.

You should be able to get away with this by creating views in SQL Server that are effectively wrappers for tables in the linked server. You will of course have to deal with the extra layer of security configuration for the linked server, thus ensuring that the Access users are connecting to the external data with the proper credentials (whether it be something per-user, or just a global set of credentials that's used for everybody accessing the FoxPro data).

Keep a close eye on performance, since Access is going to be generating queries meant for SQL Server, and it tends to be a bit chatty to begin with. SQL Server is then going to generate queries for fetching the data from FoxPro. With that many layers of query generation, performance could be a crapshoot for large tables, and it'll be pretty difficult to diagnose or improve it.

If the FoxPro data is mostly static historic data, then I'd recommend bringing it into SQL Server. If there's still read/write activity happening, then you'll have to either settle for the linked server approach, or periodically refreshing the data in SQL Server via SSIS or some other ETL.


Is it better to use multiple one-to-many relationships, or polymorphic? I've got the following tables:

Clicks table
Websites table
Ads table

Both Ads and Websites have many Clicks (i.e. both models have a one-to-many relation with Clicks). In such cases, is it better to use multiple one-to-many relationships, or would a Clickable polymorphic setup be better?

The question you should ask yourself is whether or not a website click is really the same as an ad click.  Do they have exactly the same predicates? (other than what type of parent they have)  Similarly, when you access click data, will both types often be considered together or are they always treated separately?

The reason that this is important is that you want to draw a distinction between a true subtyping situation and simply having two distinct tables with similar schemas.  Someone with a programming background might be tempted to say: "These two things look alike, so they should be like implementations of an Interface."  This leads you to a polymorphic design.

However, the considerations for entity subtyping are not quite the same as the considerations for code re-use.  If there is no reason to comingle both types of clicks in a single query, then there isn't necessarily a good reason to comingle both types of clicks in a supertype table.  There may be a trade-off to be made between a bit of code reuse and keeping your data organized for convenient and logical retrieval.


What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer) I'm at a crossroads where I need to decide if I'm going to stick with bigserial as my primary key, or change to uuid (non-auto-generating—my API server will generate the ID using uuid v4 and insert it).
I've spent hours researching bigserial vs uuid primary keys, and it seems no one can agree on what the disadvantages of uuid would be (if any). My database isn't that complicated: it's a series of tables with pretty basic relationships, I'm typically only inserting one row at a time, I have a handful of jsonb fields I'm using here and there. Write speed/frequency will only be significantly high on one table in particular.
The reason I started looking into UUIDs wasn't because I think I'll ever run out of bigint keys (9 quintillion, if I recall), but more from a standpoint of obfuscation. Right now I'm having to hash the IDs on the front-end to avoid showing the user DB IDs in the URL (e.g. /things/2732). Using Hashids, I can instead have URLs like /things/To2jZP13dG. But I thought I could go a step further and just use UUIDs which don't give any clues as to # of records. What I don't like is the added effort of having to encode IDs before passing them to the back end and decoding them there, and then when querying batches of 50-100 items to return to the client, having to mass encode all those IDs before returning them to the client.
One argument for random UUIDs (uuid v4) was:

If your primary key is an incrementing ID, those are stored physically next to each other. That database page can have contention as many people are writing to it.  Random IDs prevent contention by spreading the writes all over the DB

But then I found a contradictory statement here:

Regular random UUIDs are distributed uniformly over the whole range of possible values. This results in poor locality when inserting data into indexes - all index leaf pages are equally likely to be hit, forcing the whole index into memory. With small indexes that's fine, but once the index size exceeds shared buffers (or RAM), the cache hit ratio quickly deteriorates.

I know the folks at Heroku are fans of using UUIDs as primary keys. For whatever it's worth, I wasn't planning on having Postgres auto-generate the IDs at all. Instead, my API server would generate a v4 UUID and pass that to the database (this would allow my API server & front end client to be more efficient and not have to always use RETURNING id statements in my queries).
Does anyone have a canonical answer on the true cost of INSERT statements when using uuid as the primary key?

It is easy enough to benchmark this, but the INSERT performance of UUIDs will be worse, because they are bigger and slower to generate.
But it doesn't sound like you are building a high performance application anyway (then you probably wouldn't be using JSON), so it probably won't make much difference.
Finally, you want to use UUIDs for security reasons (which I won't discuss here). Security always puts a penalty on performance and usability, so consider it as the price you are paying for security.


Execution of a Java program by a trigger. I want to make an update trigger execute a Java program.

I have a table T1 with a column named Flag.  Whenever Flag changes, I want to run a trigger that results in an execution of a Java program.

Is this possible?

Yes, using the MySQL User-Defined Function (see MySQL 5.5 FAQ: Triggers) and installing the lib_mysqludf_sys

Then, for example, you can write your own trigger calling the sys_exec like this:

delimiter |
 CREATE TRIGGER testtrigger BEFORE UPDATE ON T1
  FOR EACH ROW BEGIN
   DECLARE result int(10);
   IF NEW.Flag <> OLD.Flag THEN
     SET result = sys_exec('/path/to/javabin -jar your.jar');
     -- other kind of works and checks...
   END IF;
 END;
|

The result contains the exit code of the external program

There are other useful functions in this library:

sys_eval : executes an arbitrary command, and returns it's output.
sys_get : gets the value of an environment variable
sys_set : create an environment variable, or update the value of an existing environment variable
sys_exec : executes an arbitrary command, and returns it's exit code

More info here

Try it on a dev env and...

  Be very careful in deciding whether you need this function. UDFs are
  available to all database users - you cannot grant EXECUTE privileges
  for them. As the commandstring passed to sys_exec can do
  pretty much everything, exposing the function poses a very real
   security hazard.

But anyway, I agree with the proposal of Remus Rusanu


DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table partitioning. Does this depend on how many data reside in the table? Is one operation safer than the other?

in both ways you will end up using exchange partition and probably a series of split partitions. If your table does not have many dependencies like constraints and triggers, the route using redefinition can be easier because it buys you a lot of time. You prepare the new table as a copy and exchange the object id's on the end phase. If there are a lot of dependencies, you have to recreate them using different names.
The operation will take some time, depending on the size.
If you go for safe .... in both cases, the operations fail or succeed.
How is the load on the table? Does it receive a lot of updates/inserts/deletes during the migration? Do you have downtime available? I would go for simplicity if possible using downtime and not using redefinition.


VarChar to NVarChar causing truncate error I am importing some data from a temporary table which has a column defined as VARCHAR(150).  The destination tables column is defined as NVARCHAR(200).  I'm using an INSERT INTO statement, but when I run the SQL statement commenting out this column the query runs correctly.  When I uncomment this line, I get:

  Msg 8152, Level 16, State 4, Line 26

  String or binary data would be truncated.

  The statement has been terminated.

Any ideas as to why this is happening?

INSERT INTO table (col1, col2)
SELECT column1
      ,column2
  FROM tmpTable

col1 is defined as NVARCHAR(200) and col2 is a DateTime.
column1 is defined as VARCHAR(150) and column2 is a DateTime.

When I comment out column1 and col1 the import statement runs fine.

Well this won't happen unless

a trigger is firing on the target table, and the error happens there
the INSERT does some processing that adds enough data to make it too wide

Also, what happens with this please? This can not fail unless there is some processing.

INSERT INTO table (col1, col2)
SELECT LEFT(column1, 200)
      ,column2
  FROM tmpTable


SQL Server Transaction Rollback: does log shrink or grow? When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size?

Do all the entries generated during and related to the transaction get deleted again (in both DB and LOG) or does the undo result in the same number of entries in the log, just undoing what was done before, growing the LOG file further?

Since deleting might be difficult in case of overlapping operations, I tend towards the latter, still I wonder and could not find out.

When cancelling a transaction, be it e. g. via failure or manually when it runs too long, what is the effect on the transaction log and its size?

While the transaction is open and working toward its goal, it'll reserve some log space in case it needs to rollback. Generally then, a rollback shouldn't change the size of the log as the space has already been reserved, though it is just an algorithm so it could reserve too much or too little and you won't know until it's too late. There is no way to change this behavior, currently.

There have been some announcements with SQL Server 2019 that will change the way this works if you turn on new features, FYI.

  Do all the entries generated during and related to the transaction get deleted again (in both DB and LOG) or does the undo result in the same number of entries in the log, just undoing what was done before, growing the LOG file further?

It doesn't have to be one-to-one as compensation records are created to undo the effects of the transaction, thus cancelling out the changes. Nothing is physically deleted, the opposite (compensation) operation is performed for each item in the transaction. Which compensation records and how many, depends, but the types should stay the same (the anti-type of the original).


MySQL Database Instance size We found 3 different size of a MySQL Database Instance as follows:

Mysqldump output
Data directory size (even if we exclude size of bin-logs,error-logs)
SUM (data_length + index_length ) calculated from information_schema.tables

Why we faced these differences and what could be used as my Instance size?

Please help.

Dump output is raw SQL statements, numbers and everything else is represented as text so the size is in no way relevant (one int is 4 bytes, but as text 1 is probably 1 byte and maximal int value (signed) = 2147483647 is 10 bytes and minimal int value = -2147483648 is 11 bytes).
I suppose those are all ibdataX, *.ibd, *.myi, *.myd, *.frm. Both MyISAM and InnoDB tables may contain some empty space (for example after deleting some rows) which will not be automatically reclaimed by the filesystem, InnoDB files contain not only actual row data but old versions of records, redo logs and other information needed for transactions.
The free space mentioned before is not accounted for here, the same applies for redo logs probably.

In the end it depends on your needs for the size - do you need to know how much space to allocate for backups? #1 or #2 may be what you need, depending on your backup strategy and after accounting for compression. Do you need to know how big disks/cloud storage to pay for - #2 is probably the best bet.

I personally do not see much use for #3 in general, but you can use it to get some average storage needed for a record so you can project future grow of specific tables.

I am sure I missed some differences but hopefully nothing big.


Postgres 11 - Range Partitioning on a Date Column with NULL values We have a table structure with parent and child records inside the same table. They are referenced via a column (parentId) which references the parent (and null on the top parent records). Also, we have a requested date column we do all of our querying by but the only records which have a value for this are the parent records.
Since all of our queries are filtering on the parent results initially and use this date, we want to paritition the table on this date range but understand this isn't possible due to there being null values on the records for the child records.
Aside from creating a "dummy" value (some arbitrary date that would never be caught in our filter) on each and every child record in the database, what other option do we have? Is it not possible to somehow create a separate partition for all of the null values that we never query for anyway?

PostgreSQL 11 allows you create a default partition for range-partitioned tables, which will receive the NULL values.
You can add a check constraint demanding the column be NULL, if you want to prevent other rows not covered by existing partitions from going into that default table.
create table for_nulls PARTITION OF parent_table (check (date_requested is null)) DEFAULT;


Which application should I use to create a relational database from an excel file? I have an excel inventory of network assets (inaccurately sold as a 'CMDB') that is becoming difficult to manage. I need to import the data into some kind of database but I dont know which one to use. It has to be easy to update - i.e add/modify/delete records and also be easy to show which records have been updated and when

It would be really good if I could show relationships between items for example, one line showing a the record for a router and another line showing which LAN switches are connected to it.

thanks

Well if you know how to modify/insert/delete data by using some basic SQL queries and would like to have remote access to the database from other machine, then MS SQL Server can be a good choice. You can download the Express version here for free.


'The computer must be trusted for delegation and the current user account must...' error when trying to connect to sql server 2008 Our admin set up a brand new Windows 2012 R2 server with Visual Studio 2015. When I try to connect to an existing sql server 2008 database (running on a windows 2008 r2 server) from Visual Studio, using Tools -> Connect to database..., I can enter all the information, the dialog retrieves the list of databases, Test Connection works, but when I click ok I get:

  The computer must be trusted for delegation and the current user account must be configured to allow delegation

Both servers are under the same domain (development) and I am local admin on both of them. My account though is on a different domain that has access to the development domain.

Any ideas on how to fix this? Are they referring to the windows 2012 server or the database server in the message?

Just as a side note, I can connect successfully from another windows 2008 r2 server using the same windows account.

Update: the connection that fails uses windows authentication.

According to KB3000850, this affects domain-joined Windows 8.1 computers after installing security update 2992611 or update 3000850.

I added the following value under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\Protect\Providers\df9d8cd0-1501-11d1-8c7a-00c04fc297eb

ProtectionPolicy = 1 (DWORD)

Now I can connect.

I found some other postings about this:

https://blog.sqlauthority.com/2017/04/01/sql-server-system-security-cryptography-cryptographicexception-error-generating-xml-document/

https://social.technet.microsoft.com/Forums/windows/en-US/47faab6b-d717-4068-bee4-c694811e0066/credential-manager-problems-error-0x80090345?forum=w8itpronetworking


SQL server 2016/2017 in terms of better performance Which SQL version or edition should we use out of SQL server 2017std/ent or 2016std/ent in terms of better performance gain when you are dealing with SP's (approx 1000 or more)in single database which are equipped with complex joins over tables, views, udfs and also having SORT and ORDERBY clauses in many of the SPs.
Thanks

For version, 2017.  Later versions have additional features and improvements, so for performance always prefer the latest version.

Enterprise Edition can scale up to larger machines:

and has more sophisticated High Availability and Disaster Recovery features.  But they have the same query optimizer and support the same programability features.

In general large databases, and databases with very demanding throughput requirements may perform better on Enterprise Edition, but smaller databases on smaller servers will perform the same.

See generally Editions and supported features for all of the differences.


SQLite3 'OR IGNORE' leaves empty primary key IDs I use 'INSERT OR IGNORE INTO' in my main table which works fine but leaves empty primary key IDs when records are ignored.
This leads to big jumps in my primary key numbering because I re-scan part of the same input data every time.
Can I avoid this and re-set the ID to the last used value before next call?
thanks

Apparently, leaving out the autoincrement from the key definition does the job. The key still autoincrements itself but not when IGNORE is triggered. I found this solution here: https://stackoverflow.com/questions/47120708/sqlite-autoincrement-and-insert-or-ignore-will-produce-unused-autoincrement-key


SQL Server CREATE and DROP TABLE Permissions in Stored Procedure Only I'm attempting to create a stored procedure that's able to create and drop tables. A given user will have EXECUTE permissions on that stored procedure. Is there any way that the user would be able to successfully execute the stored procedure without having the database-wide permissions granted as well for creating and dropping tables? Some initial research has shown that certificates might be a way to solve this but I'm not sure if even those are suitable for creating and dropping without database-wide permissions being granted. In Oracle this is solvable through granting the owning schema rights, but I don't think this concept is mirrored in SQL Server.

Another possible solution that comes to mind would be using EXECUTE AS in the stored procedure to some user that has access to what we need it to, maybe even dbo. This would then let the user execute just that stored procedure but the procedure would have all the permissions it needs to operate. We'd ideally not use this though as this is a vendor stored procedure so we don't want to touch it if we don't have to. Any other ideas would be welcome.

So we have common language let's say I'm using abc for user and test_proc for procedure.

Create a stored procedure that uses EXECUTE AS OWNER to run the procedure in question.


Insert with OUTPUT correlated to sub query table I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with a foreign key. Each FinancialInstitution needs the Id of its corresponding Person. So, for each new line inserted in Person, the id of this new line (IDENTITY) has to be copied back into the corresponding line of FinancialInstitution.

The obvious way of doing this is an iterative T-SQL code. But I'm interested in knowing if it's possible to do it only with set-based operations.

I imagined the inner-level of such a request would be something like:

INSERT INTO Person (Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
OUTPUT inserted.Id, FinancialInstitution.Id
SELECT Id, Street, Number, City, [State], PostCode, CountryId, PhoneNumber, Fax, Email
FROM FinancialInstitution;

Unfortunately, it seems OUTPUT can't correlate that way...

I guess you could (ab)use MERGE for this. First create a (temporary) table:

CREATE TABLE tempIDs
( PersonId INT,
  FinancialInstitutionId INT
) ;

Then MERGE into Person (instead of INSERT), so you can use columns of the tables involved in the OUTPUT clause:

MERGE INTO Person
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (Street1, Number1, City1, ...)
  VALUES (fi.Street, fi.Number, fi.City, ...)
OUTPUT inserted.Id, fi.Id
  INTO tempIDs ;

Then use the temp table to UPDATE FinancialInstitution:

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

Test at: SQL-Fiddle


Flashback feature in Oracle 11g? I am using Oracle 11gR1. As a part of a project, I have to execute multiple update statements by disabling a few constraints (triggers and check constraints) which could result in a corrupted database.

If the database gets corrupted, I have to re-import it entirely, which is an additional task that is time consuming.

I have very little knowledge about the Oracle Flashback feature. Will Flashback help me to avoid these unnecessary imports? Can I set some stable point and switch back to there if anything goes wrong?

Can someone please give me brief overview on this feature?

If it fits my requirements, I will try to learn.

Firstly, you must enable flashback. In SQL*Plus as SYSDBA:

shutdown immediate;
startup mount;
alter database flashback on;
shutdown immediate;
startup;

Next before your load operation, create a restore point:

create restore point before_load;

Now do you work, and when you're done:

shutdown immediate;
startup mount;
flashback database to restore point before_load;
shutdown immediate;
startup;

And your data is back as it was. You can repeat this as many times as you like, and when you are done, drop the restore point. HOWEVER the commenters are correct; you should leave the constraints enabled and just defer them.


Is it a bad practice to always create a transaction? Is it a bad practice to always create a transaction?

For example, it is a good practice to create a transaction for nothing but one simple SELECT?

What is the cost of creating a transaction when it is not really necessary?

Even if you are using an isolation level like READ UNCOMMITTED, is it a bad practice?

Is a bad practice to create a transaction always?

It depends on what context you are talking here. If it is an update, then I would highly recommend using TRANSACTIONS explicitly. If it is a SELECT then NO (explicitly).

But wait there is more to understand first :
Everything in sql server is contained in a transaction.

When the session option IMPLICIT_TRANSACTIONS is OFF and you explicitly specify begin tran and commit/rollback then this is commonly known as an Explicit Transaction. Otherwise you get an autocommit transaction.

When  IMPLICIT_TRANSACTIONS is ON an Implicit transaction is automatically started when executing one of the statement types documented in the books online article (e.g. SELECT / UPDATE / CREATE) and it must be committed or rolled back explicitly. Executing a BEGIN TRAN in this mode would increment @@TRANCOUNT and start another "nested" transaction)

To switch which mode you're in, you'd use

SET IMPLICIT_TRANSACTIONS ON

or

SET IMPLICIT_TRANSACTIONS OFF

select @@OPTIONS & 2

if above returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

how much is the cost of creating a transaction when is not really necessary?

Transactions are needed to take the database from one consistent state into another consistent state. Transactions have no cost as there is no alternative to transactions.
Refer: Using Row Versioning-based Isolation Levels

Even if you are using an isolation level read_uncomitted. Is a bad practice? because it shouldn't have problems with locking.

READ_UNCOMMITED isolation level will allow dirty reads by definition i.e. One transaction will be able to see uncommitted changes made by other transaction. What this isolation level does is, it relaxes the over head of locking - method of acquiring locks to protect Database concurrency.

You can use this on a connection/query level, so that it does not affect other queries.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Found an interesting article by Jeff Atwood describing Deadlocks due to Dining Philosophers Puzzle and describing  read committed snapshot isolation level.

EDIT:

Out of curiosity, I did some test measuring the impact on T-log with Perfmon counters like Log Bytes Flushed/Sec, Log Flush Waits/Sec (No. of commits per sec that are waiting on LOG flush to occur) as below graph :

sample code :

create table testTran (id int, Name varchar(8))
go

-- 19 sec
-- Autocommit transaction
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
---------------------------------------------------
-- 2 sec
-- Implicit transaction
SET IMPLICIT_TRANSACTIONS ON
declare @i int
set @i = 0
while @i < 100000
begin
insert into testTran values (1,'Kin Shah')
set @i = @i+1
end
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF

----------------------------------------------------
-- 2 sec
-- Explicit transaction
declare @i int
set @i = 0
BEGIN TRAN
WHILE @i < 100000
Begin
INSERT INTO testTran values (1,'Kin Shah')
set @i = @i+1
End
COMMIT TRAN

Autocommit Transactions: (Edited as highlighted by @TravisGan)

Insert took 19 secs.
Every Autocommit will Flush the T-log buffer to the disk due to autocomit (after @TravisGan highlighted, and I missed that to mention) .
The CHECKPOINT process will be completing fast as the amount of dirty log buffer required to be flushed will be less as it runs quiet often.

IMPLICIT & Explicit Transaction:

Insert took 2 secs.
For EXPLICIT transaction, the log buffers will be flushed only when they are full.
Contrary to Autocommit transaction, in EXPLICIT transaction, the CHECKPOINT process will take longer duration as it will have more log buffers to flush (remember that log buffers are flushed only when they are full).

There is a DMV sys.dm_tran_database_transactions that will return information about Transactions at database level.

Obviously, this is more sort of a simplistic test to show the impact. Other factors like disk subsystem, database auto growth settings, initial size of the database, other processes running on the same server\database, etc will have influence as well.

From the above tests, there is near to no difference between Implicit & Explicit transactions.

Thanks to @TravisGan for helping to add more to the answer.


Bulk load data and provide row-by-row feedback We want to allow our users to import CSV files containing many thousands of records to "pre-load" a particular table in our system. It's not strictly a one-time operation, it may be performed multiple times as they get feedback, tweak the CSV, and re-import.
I understand that this is not a lot of data, relatively speaking. But, we want the operation to be atomic (this is a multi-user system other users may also be adding or modifying data) and we want to provide per-row feedback (like "Row 123 is a duplicate of item 'abc'").
I know that statements like COPY or tools like pg_bulkload can do the work very quickly, but this is user-interactive via our web app and we require row-by-row feedback.
Our naïve approach was to run sequential INSERT statements and to catch exceptions. Even though we're re-using a single connection, this makes separate calls and uses a separate transaction. This is taking way too long.
for(var i = 0; i < rows.Count; i++)
{
    try
    {
        connection.Execute("INSERT INTO table1 VALUES (...)");
        // format a "successful" response
    }
    catch (PostgresException e)
    {
        // format a "failed" response based on exception details...
    }
}

We tried creating a single INSERT statement with multiple VALUES, but this does not provide row-by-row feedback and it stops on the 1st error (I found no useful way to use ON CONFLICT here).
We also tried sharing a transaction and running sequential statements but the transaction is "aborted" on the 1st error.
using(var tx = connection.BeginTransaction())
{
    for(var i = 0; i < rows.Count; i++)
    {
        try
        {
            connection.Execute("INSERT INTO table1 VALUES (...)", tx);
            // format a "successful" response
        }
        catch (PostgresException e)
        {
            // format a response based on the type of failure...
        }
    }
}

I'm beginning to think that we need to run this entire operation within the database in SQL. We have only one constraint right now, a NOT NULL UNIQUE "name" column. So the statuses would be something like...

"OK": the row inserted fine (return the new PRIMARY KEY "id" column)
"Conflict": the name in this row is a duplicate of an existing row either a row seen earlier in the CSV or already in the table (return the duplicate name)
"Bad Request": the name in this row is missing, empty, or all whitespace

We also need to return the index (in insert order, I guess) in order to approximate the line number in the original CSV file. I'm thinking we need to INSERT into a temporary table and use something like row_number().
Given a CSV like this (name + some example columns, just to make the point)
name,region,code,set
A1,A,100,1
A2,A,101,1
A1,A,102,1
,B,200,2
C1,C,300,3

I would need responses like this:

index
id
name
status

1
100
A1
OK

2
101
A2
OK

3
[null]
A1
Conflict

4
[null]
[null]
Bad Request

5
102
C1
OK

Am I on the right path? Is this a workable solution or is there some other technique that would work better?
Does it make sense to work the queries out by status? Like, rather than try to process one record at a time, can I try to find all of the bad requests 1st, then all of the duplicates, then all of the new rows (and insert them)? I'm fine with this response, we can always reorganize them in the application code.

index
id
name
status

4
[null]
[null]
Bad Request

3
[null]
A1
Conflict

1
100
A1
OK

2
101
A2
OK

5
102
C1
OK

Believe me, I'm asking around about the requirement, but it seems stuck for now &emdash; they really want to provide the user with specific row-by-row feedback.
If I insert into a temporary staging table, can I run 3 set-based operations? One for bad requests, one for duplicates, and one for new records?

You have no solution. You can speed it up (bulk load into temporary tables, then execute the actual insert from there), but your requirement ot absolute row by row feedback basically throws and set operation out of the window. And if it has to be atomic, it has to be atomic. Time optimization is all you CAN do.

Now, going from a temporary taging table (that can be ordered) with one complex scipt that then inserts the feedback into the staging table - is about the best yo ucan do. What it does is eliminate the latency to the server and that is the best yo ucan hope for.

Standard practice is to avoid any row by row processing, but your requirement just is what it is.


MySQL query optimization with aggregate ORDER BY I have an analytics table that stores metrics aggregated the day level. I need to ask questions such as "For a given date range and client, what are the most popular items."

The schema is as follows:

CREATE TABLE `t1` (
    `imps` bigint(20) DEFAULT NULL,
    `n_id` int(11) NOT NULL,
    `b_id` bigint(20) NOT NULL,
    `date` date NOT NULL,
    `m1` bigint(20) NOT NULL,
    `m2` bigint(20) NOT NULL,
    `is_b` tinyint(1) NOT NULL,
    `i_id` varchar(128) NOT NULL,
    KEY `idx1` (`b_id`,`date`,`i_id`,`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(This table is split into 256 partitions by HASH(b_id), in case that matters)

A representative query:

SELECT i_id, n_id, SUM(m1), SUM(m2)
FROM t1
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
ORDER BY (SUM(m1) + SUM(m2)) DESC
LIMIT 10 OFFSET 0

And the EXPLAIN output:

+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | t1    | ref  | idx1          | idx1 | 8       | const | 1395772 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+-------+-------+----------------------------------------------+

On a table with ~110M records these queries take > 10s to execute. I believe the SUM function in the ORDER BY clause forces the entire resultset satisfied by the WHERE clause to be written to disk, hence the "Using temporary; Using filesort" messages.

Do I have any recourse in improving the performance of these queries short of redesigning the schema and application?

Edit: Here is a SQLFiddle with the schema and dummy data: http://sqlfiddle.com/#!2/beb63/1

Did you tried in the following way:

SELECT * FROM (
SELECT i_id, n_id, SUM(m1) AS sum1, SUM(m2) sum2
FROM t1
WHERE b_id=1234 AND date>='2013-06-01' AND date<='2013-08-31'
GROUP BY i_id, n_id
LIMIT 10 OFFSET 0)x
ORDER BY sum1 + sum2 DESC

I'm in SQL Server and there it's helping me.


Matching 3 columns in row - if one column is null then exclude it from comparison A   B   C    Equal
1   1   1    Y
2   2   NULL N

Hi, I have 3 columns which I need to compare : Columns A,B,C.
If these 3 columns are the same then column 'Equal' = 'Y'

But, if a column is null, then it must be excluded from the comparison, e.g. in the example Row 2 has a null, but the remaining columns are all the same, so the Equal should be yes - because all non-null values are equal

http://www.sqlfiddle.com/#!3/dedd9/2

I thought I could use isnull(C,A) = A but it does not work. Could anyone help please

 declare @t table
 (
 A decimal,B decimal, C decimal
 )

 insert Into @t(A,B,C) values (1,1,1)
 insert Into @t(A,B) values (2,2)

 select A,B,C,'Equal' = case when ISNULL(A,B) = B and ISNULL(B,C) = C and ISNULL(C,A) = A then 'Y' else '' end
 from @t

Assuming you want all NULLs to count as equal, I think this will work. But I haven't thoroughly tested it, or even thought about it for very long...

 select A,B,C,'Equal' =
    case when COALESCE(A,B,C,0) = COALESCE(B,C,A,0)
          and COALESCE(B,C,A,0) = COALESCE(C,A,B,0)
     then 'Y' else '' end
 from @t

COALESCE will pick the first non-NULL value from its arguments, so when testing A against B and then B against C, in each case we look 'forward' for a non-NULL value, with a fallback for when they're all NULL


CDC system tables missing from backup I am migrating a database that uses CDC from SQL Server 2008 R2 to SQL Server 2012.

There are CDC-related tables under the "System Tables" branch in Object Explorer:

cdc.ddl_history
cdc.lsn_time_mapping
cdc.captured_columns
cdc.index_columns
cdc.change_tables

After backing up the database and restoring it on a different server, the CDC-related tables are not there.

I tried to copy them across using SSIS, but they are not visible in the "Export data" wizard GUI. It appears the only way of actually copying them across would be to export them manually or by outputting SELECT * FROM... results to a file.

How can I ensure the Change Data Capture related tables are restored properly?

If you are restoring a CDC-enabled database onto a different instance, you must ensure the server principal that "owns" the database in question is present on the target instance prior to performing the restore.  You must also ensure you use the KEEP_CDC option when restoring the database.
From the MSDN page on restore options:

KEEP_CDC should be used to prevent change data capture settings from being removed when a database backup or log backup is restored on another server and the database is recovered. Specifying this option when restoring a backup with the NORECOVERY option is not permitted.

Restoring the database with KEEP_CDC will not create the change data capture jobs. To extract changes from the log after restoring the database, recreate the capture process job and the cleanup job for the restored database. For information, see sys.sp_cdc_add_job (Transact-SQL).

For information about using change data capture with database mirroring, see Change Data Capture and Other SQL Server Features.

I just restored a test CDC-enabled database onto my SQL Server 2012 instance that was created on my test SQL Server 2008R2 instance, without creating the login that owns the database on the 2012 instance.  An error was shown during the restore:

Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262
Could not update the metadata that indicates database CDCTest is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_MScdc_ddl_database triggers] 'drop''. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.

The login must also be a member of the sysadmin server role prior to restoring the CDC-enabled database.
You will need to re-create the owner of the database in the target instance using the same SID if the owner is a SQL login (if the owner is a Windows login, the SID will naturally be the same if the Windows login is a domain-member).
You'll need to perform something like the following to restore your database:
/*
    obtain the identify of the login that "owns" the database by looking at the
    UserName column in the output from the following command:
*/
RESTORE HEADERONLY FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'

/*
    This login is the owner of the database
*/
CREATE LOGIN CDCTestLogin
WITH PASSWORD = 'LozierPituophisUnconsciousShelduck4'
    , SID = 0x2ECDACB721D7E84E8A28DCFE1C758799;

/*
    Ensure the login is a member of the 'sysadmin' server-level fixed role.
*/
EXEC sp_addsrvrolemember @loginame = 'CDCTestLogin', @rolename = 'sysadmin';
GO

/*
    Restore the database, with the KEEP_CDC option
*/
RESTORE DATABASE CDCTest FROM DISK = 'D:\SQLServer\Temp\CDCTest.bak'
WITH MOVE 'CDCTest' TO 'D:\SQLServer\MV2012\Data\CDCTest.mdf'
    , MOVE 'CDCTest_log' TO 'D:\SQLServer\MV2012\Logs\CDCTest_log.LDF'
    , REPLACE
    , KEEP_CDC;


Handling data encoding issues while loading data to SQL from Script (Notepad++) I'm pretty sure this is not a SQL Server problem.

I already asked a question HERE with an awesome explanation, BUT I still can't explain to the guys where I work that it has nothing to do with SQL Server collation or etc.

the situation:

We create scripts ( INSERTS/DELETS ) using ANSI.
We send these scripts to our clients.
Out clients run these scripts via SQLPLUS, SSMS, or any other method.

When they use something that is using ANSI, there's no problem.

BUT we would like to FORCE soomething, to tell them something like
"hey, you are running this script with a different encode, and some characters will be worngly formated, like an ã would be xE3.

is there a way to force a script to run on it's native encode ( the encode that I created )?

I really have no idea how to dead with this problem. We want to sent a script, and we want to be sure that this script will run with A being A, Ã being Ã.

One option might be to have them run the script via sqlcmd with the codepage options:

sqlcmd -f <codepage> | i:<codepage>,o:<codepage>

Alternately, save the source files with UTF-8 encoding (code page 65001), with a byte-order mark.


Is it possible to specify a different collation for the data vs the entities within a single database? I have a database with a case sensitive collation because the data inside of it should be treated as case sensitive in the context of how it's used.
The entities and their column names within that same database don't need to be case sensitive and we'd prefer that they weren't for querying purposes.
Is it possible to keep the data itself as case sensitive but allow the entities to be case insensitive within the same database?

Yes.  Turn on partial database containment, and you’ll get a case-insensitive catalog collation along with the collation of your choice for the data.

In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. This collation is the same for all contained databases on all instances of SQL Server and cannot be changed.
The database collation is retained, but is only used as the default collation for user data. By default, the database collation is equal to the model database collation, but can be changed by the user through a CREATE or ALTER DATABASE command as with non-contained databases.

See https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database-collations?view=sql-server-ver15
And note that Azure SQL Database always uses contained database collations.


PostgreSQL Client Certificate TLS error PostgreSQL 9.4.6
OpenSSL 1.0.2f

If my server or client certificate use SHA256, I get the following error on the client:

psql: SSL error: tlsv1 alert decrypt error

And the following log message on the server:

LOG:  could not accept SSL connection: unknown message digest algorithm

If I generate the client key with SHA1, it works fine:

openssl x509 -req -in user.csr -CA root.crt -CAkey server.key -out user.crt -CAcreateserial -days 365 -sha1

How can I get it to work with SHA256?

This can happen if PostgreSQL is compiled against an older version of openssl than the version used to generate the keys.

Recompiling with the correct openssl library fixes the problem.


Gaining visibility into long running INSERT INTO ... SELECT I have a large SQL insert statement that updates a summary table. The process normally runs hourly, and takes about 5 to 10 minutes to calculate uniques over that period. I "foolishly" chose to run it over a period of 15 days, and I'm wondering if there's anything I can do to gain visibility into where it's at. The process has been running for 9 hours already. I would simply like to know if it's still the SELECT query that's running or if it's inserting data: I need to know if I should stop it and do smaller batches, or if I should just let it finish.

I know the server's been at it for 9 hours because of that statement:

select
    now() - xact_start
  , procpid
  , client_addr
  , client_port
  , current_query
from pg_stat_activity
where xact_start is not null
order by 1 desc

The actual query is:

INSERT INTO summary_show_unique_personas(period, show_id, persona_id, interactions_count)
  SELECT
      date_trunc('hour', created_at) AS period
    , show_id
    , persona_id
    , COUNT(*)
  FROM
          twitter_interactions
    JOIN  show_bindings        USING(interaction_id)
    JOIN  twitter_personas     USING(screen_name)
  WHERE
        created_at >= '__PERIOD_START_AT__' AND created_at < '__PERIOD_END_AT__'
    AND interaction_created_at >= '__PERIOD_START_AT__' AND interaction_created_at < '__PERIOD_END_AT__'
  GROUP BY
    1, 2, 3;

Postgres doesn't allow dirty reads so we need some other means of checking if rows are being inserted from a second transaction, such as:

select pg_relation_size('summary_show_unique_personas');

The value returned by this query will be increasing if inserts are currently happening (unless, I think, they are filling up space freed up by previous delete statements).


Safe alternative to exec(sql) I've found some exec(sql) statements buried in the code.  They're there for good reason, because these statements couldn't be written directly, however they are an obvious attack vector.

Is there a safe alternative to exec(some sql)?  Something that will be paremetrised correctly, including table names in statements?

For certain types of parameterization, it's not going to matter if you use EXEC() or sp_executesql, because some things can't be parameterized anyway. For example, you expressed in the comments (please update your question to be more specific about your requirements!) that you are parameterizing table names, but these can't be parameterized because they need to expressed literally to SQL Server (it can't tokenize that and swap at runtime).

To protect yourself from table name vulnerabilities, you can easily protect yourself like this:

DECLARE @tablename sysname; -- pretend this is a parameter
SET @tablename = N'this_is_not_a_table';

-- in procedure body:
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @tablename)
BEGIN
  RAISERROR(N'Table does not exist.', 1, 11);
  RETURN;
END
SET @sql = N'SELECT ... FROM dbo.' + QUOTENAME(@tablename) + ...;
-- EXEC(@sql) or EXEC sys.sp_executesql @sql

Now, I prefer to use sp_executesql always, partly because it promotes using strongly-typed parameters (avoiding SQL injection issues as well as double-single-quote issues), but also because in some cases you will be passing parameters for both parameterizable and unparameterizable values (yes I made those words up). More info:

Bad Habits to Kick : Using EXEC() instead of sp_executesql
Protecting Yourself from SQL Injection - Part 1
Protecting Yourself from SQL Injection - Part 2

Now the only thing you have to worry about is if someone is able to create tables and can create a table named sys.objects; DROP TABLE foo; -- - but if you have someone you don't trust but has the ability to create tables in your database...


If a transaction is "committed" , is it then saved for sure? If a transaction is committed successfully, can I then be 100% sure that it has been written to the database AND to the log files? Can I be sure that the data is SAVED ?

Today our log files had reached the limit of the drive and we got a lot of errors.  Furthermore, some other services crashed. We increased the disk and restarted the server.

During startup, the server did a "database recovery" -- can I be sure that everything is okay again ?

If a client application has issued a COMMIT and got back a success code then the transaction is guaranteed by the engine to be durable. It is guaranteed that all changes done in a transaction are going to be visible, even after a crash. Furthermore the recovery also guarantees that any transaction not committed will be rolled back in case of a crash.

For more details I recommend reading the ARIES paper.

Write-ahead logging and recovery cannot make guarantees if the underlying storage hardware has faults (corruption). And any engineering product may contain defects.

Your application, like all other applications, needs to be carefully written to behave correctly in presence of crashes (errors). There is no magic.


Postgres ERROR: tuple concurrently updated I have a large table test in which in user_id 2 have 500000 records. So I want to delete this record in chunks of 100 records but it is given error.
Here is my query:

delete from test where test_id in (select test_id
from test where User_id = 2 limit 100 )

  ERROR: tuple concurrently updated

What is the issue. How can i solved it.

Your plain subselect fetches up to 100 rows, but does not lock them against write access. Concurrent transactions can update or delete one or more of those rows before DELETE can lock the rows (at least with the default isolation level READ COMMITTED). This would result in your error message.

To defend against this race condition, lock the rows in the SELECT with FOR UPDATE (or other options):

DELETE FROM test t
USING (
   SELECT test_id
   FROM   test
   WHERE  User_id = 2
   ORDER  BY test_id  -- acquire locks in consistent order!
   LIMIT  100
   FOR UPDATE         -- lock rows
   ) x
WHERE x.test_id = t.test_id;

Do this in a consistent order to prevent deadlocks between multiple transactions collecting locks in different order and ending up blocking each other out.

There are even better solutions. The best option would be with FOR UPDATE SKIP LOCKED in Postgres 9.5 or later:

Postgres UPDATE ... LIMIT 1

Consider upgrading to a current version of Postgres.


duplicate key error index MongoDB I have rocket.chat server connected to LDAP.
When one of users try logging to their account he get logged to someone other account, it's probably because indexes. My logs look like this:

  [34mI20161126-12:07:28.992(1) rocketchat_ldap rocketchat_ldap.js:341 [31mLDAP ➔ Search.error Search by id 31353036 returned 2 records
  [34mI20161126-12:07:28.999(1) rocketchat_ldap rocketchat_ldap.js:780

  [31mLDAPSync ➔ error { [MongoError: E11000 duplicate key error index:
  rocketchat.users.$emails.address_1 dup key: { : "some@one.com" }]
  name: 'MongoError',   message: 'E11000 duplicate key error index:
  rocketchat.users.$emails.address_1 dup key: { : "some@one.com" }',
  driver: true,   index: 0,   code: 11000,   errmsg: 'E11000 duplicate
  key error index: rocketchat.users.$emails.address_1 dup key: { :
  "some@one.com" }' }

Can I rebuild that indexes or something else to resolve this problem?

I am not familiar with Rocket.Chat.

Will dare to suggest to check that MongoDB collection's indexes with db.collectionName.getIndexes() and see if there is an index with the email address field as unique index. If so, then, drop it with db.collectionName.dropIndex("indexName").


Compare same tables in 2 different databases and 2 different servers I have 2 servers, one named DB2 (cloud), and one named OUS (on prem).

I have 2 databases, one named version1 (cloud), and one named version2 (on prem).

A person is adding a column in xyz entity on the version1 (cloud) database, and this new column in xyz entity must be added to version2 (on prem) database.

I am facing the issue of schema mismatch due to unmatched column, and
I need a SQL query to address this issue.

Alter table add column like this:

ALTER TABLE dbo.version2 ADD xyz VARCHAR(20) NULL;

https://docs.microsoft.com/en-us/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-2017


Query takes long time to execute while there is an index configured We have a table with 190M records. there is an index on a fairly unique column but still, if I run a query on the table selecting top 10 with only the indexed column in the where and select clause it still takes ages to complete.
If I turn on live query plan sql server shows the 'waiting for query plan" waiting spinner. If the query finally finishes and if I run it again it then completes instantly. Even if I run DBCC FREEPROCCACHE before So it feels like the creation of the query plan is taking all the time but then i cannot reproduce by running DBCC FREEPROCCACHE before the query. After a while the same issue will reoccur.

Note: The database has 2 filegroups. Indexes and system databases are stored on SSD disk while normal table data is stored on a non SSD disk.

Perhaps auto-update stats kicked in. AUTO_UPDATE_STATISTICS_ASYNC ON is your friend in that case. Check the index stats date to see if it coincides with the slowness:
SELECT
    STATS_DATE(
          OBJECT_ID(N'YourSchema.YourTable')
        , INDEXPROPERTY(OBJECT_ID(N'YourSchema.YourTable'),N'YourIndexName','IndexID')
    ) AS LastStatsDate;

-- dan-guzman
If it isn't plan generation (SET STATISTICS TIME ON can be revealing), or the query processing per se (reading just a few rows with a pretty unique index), then the natural thing to suspect is blocking. - tibor-karaszi

There are several ways to monitor for statistics update events (profiler, extended events...). SQL Server 2019 introduces the WAIT_ON_SYNC_STATISTICS_REFRESH wait type, which can appear in the wait information section of 'actual' execution plans.
Sentry One Plan Explorer captures statistics building and refreshing (statman) and includes this information in the wait info.


mysql performance tuning innodb_buffer_pool_size and warming the cache (select * vs select count(*)) I have a couple of questions about tuning a MySQL (5.5) database. It seems the clients database server has never had any performance tuning done. Upon reading around, it seems the one thing is to set innodb_buffer_pool_size to "80% of the available RAM". Now, the physical size of the database is about 5Gig, and the available RAM on the server is 32Gigs. It seems to make no sense to me to make the innodb_buffer_pool_size = 25G.

At the moment I have set

innodb_buffer_pool_size: "8G"
innodb_buffer_pool_instances: "8"
innodb_log_files_in_group: "2"
innodb_log_file_size: "1G"

Which, reading around (and running mysql-tuner script) seems about sane.

My question is: Is it a bad idea to have a buffer_pool_size greater than the size of the database itself. Is it bad to effectively have all the data in the buffer_pool ?

Warming the cache

The other part is warming the cache. Reading other posts on the net, the suggestion is to run a bunch of select count (*) from <table> to warm it. If I do that, I get the amount of RAM at point (1) in the picture below.

If instead I run select * from <table> I get a heap more RAM used. Which, I assume means that I have more data loaded into the cache. Which seems good to me.

Is it better to run select * or select count(*) for warming the cache?

Warmup

If you have a secondary index on the table, SELECT COUNT(*)... will use that.  So the first "warming" fetched that index.  The second "warming" fetched the data; note that the hump is higher, since the data is bigger?

That is, COUNT(*) works only for an InnoDB table with no secondary keys.

This might work:  SELECT COUNT(*) FROM t FORCE INDEX(PRIMARY);

You are overdue for an upgrade.  With 5.6.3, there is innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, which is better than your warmup script; it efficiently restores the buffer pool to whatever was in it.

Buffer pool size

5GB of data; 32GB of RAM --> 8GB sounds like a good buffer_pool_size:

Less than 5GB probably leads to extra I/O (depending on "working set size").
8GB leaves enough room on top of the 5GB for the "Change Buffer" and other overhead.
8-25GB -- the rest would be idle.
over 25GB -- threaten to do swapping, which would be really bad for performance.


Parallel replication is not working on a 5.7 slave from a 5.7 master Note: Following are the parameters that we turned ON on the 5.7 replica for parallel replication to happen:

slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 10
log_slave_updates = 1
backup retention enabled for 1 day on the slave

On the 5.7 master, the following parameters were set:

binlog_order_commits = 1
binlog_group_commit_sync_delay = 50

We can see from watching the following query that only single threaded replication is happening:

select * from performance_schema.threads where name="thread/sql/slave_worker";

By executing the above query we saw that only one of the slave workers are actually committing to the database and for all the other threads it shows a status of "Waiting for an event from Coordinator"

Would like to know why exactly parallel replication is not working

MySQL can only use as many threads as are actually needed for replication given your database workload. The fact that those threads are visible would imply that parallel execution is in effect. Take a look at what a "SHOW PROCESSLIST" yields. More specifically, look at what the "time" column for the replication threads shows.


How to speed up full text query on a table with 4 million rows? (MariaDB) I have an InnoDB full-text table that serves the Ajax-powered search box at the top of my website.  I generate it with a daily script that pulls data from a dozen entity tables on the site and amalgamates them all into one FT table for searching.
To give users the best experience (IMHO) I take whatever their input is, clean certain characters out of it (all full-text modifiers, for example), and then prepend every term with + and append them all with *.
So a search for "stack overflow" becomes +stack* +overflow*
The column that I'm searching on the FT table is small, with a typical character length of 30 characters.  Event names, people's names, geographical locations, that sort of thing.  Not huge passages of text.
It works, but queries take on the order of 1 second to be returned.
EDIT: just after posting I've rebuilt the index and it's down to 0.4 seconds now - but I'd still like to improve it, if possible.
How could I change that to 0.1 seconds, or is that a pipe dream?
My server is a dual Xeon with 16 cores/32 threads and 128GB of memory.  I serve a million pages or so each month, and rarely see server load above 1-2, with plenty of spare memory.
I wonder if I can somehow force this table to reside permanently in memory (rebuilding it after a server reboot or MySQL restart only takes 30 seconds or so), and if that would help?  Or maybe MySQL is already holding it in memory - how can I check?
I'm happy with the query itself, I don't think there's much that I can improve about it, but I know very little about how to maximize server potential through configuration.
FWIW SELECT VERSION() gives me 10.3.20-MariaDB-log.

How big is the dataset?  SHOW TABLE STATUS.

It sounds like it is much less than innodb_buffer_pool_size, so it will probably reside in RAM once it is loaded.

There is no automatic way to get it loaded.  You could write a script to access every row in the data and every index.  But that is tricky and slow.

On the other hand, you can keep it loaded (at least across graceful shutdowns) by turning ON these:  innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_shutdown.


mysql... compare and match items in same column I have a column full of server names. For example;

LouServer01
VgsServer01
LouServer02
VgsServer02

I need/want to be able to create a query that can match those two names based on everything after Lou/Vgs and give me something that looks like this:

Louisville  | Vegas
____________|____________
LouServer01 | VgsServer01
LouServer02 | VgsServer02

Here is the query you need

SELECT
    Louisville.name LouisvilleServer,
    Vegas.name VegasServer
FROM
(
    SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    WHERE LEFT(name,LOCATE('0',name)-1)='LouServer'
) Louisville
INNER JOIN
(
    SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    WHERE LEFT(name,LOCATE('0',name)-1)='VgsServer'
) Vegas
ON Louisville.num = Vegas.num;

Here is a sample table

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS servers;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE servers
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     PRIMARY KEY (id),
    ->     KEY (name)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO servers (name) VALUES
    -> ('LouServer01'),('VgsServer01'),
    -> ('LouServer02'),('VgsServer02');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM servers;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | LouServer01 |
|  3 | LouServer02 |
|  2 | VgsServer01 |
|  4 | VgsServer02 |
+----+-------------+
4 rows in set (0.00 sec)

mysql>

Here is the query you need executed

mysql> SELECT
    ->     Louisville.name LouisvilleServer,
    ->     Vegas.name VegasServer
    -> FROM
    -> (
    ->     SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    ->     WHERE LEFT(name,LOCATE('0',name)-1)='LouServer'
    -> ) Louisville
    -> INNER JOIN
    -> (
    ->     SELECT name,SUBSTR(name,LOCATE('0',name)) num FROM servers
    ->     WHERE LEFT(name,LOCATE('0',name)-1)='VgsServer'
    -> ) Vegas
    -> ON Louisville.num = Vegas.num;
+------------------+-------------+
| LouisvilleServer | VegasServer |
+------------------+-------------+
| LouServer01      | VgsServer01 |
| LouServer02      | VgsServer02 |
+------------------+-------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!