Difference between Handler_write and Innodb_data_writes Handler write is the number of requests to insert a row in a table.
InnoDB data writes is the total number of data writes.

Questions are

Does inserting a row count as a disk write?
If I do an insert operation in MYISaM engine which will get incremented?
If I do an insert operation in InnoDB engine which will get incremented?
If Handler_write is the number request to insert a row then why does it gets incremented every-time I run select queries.

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195556 |
+--------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195557 |
+--------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE as value from information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME="Handler_write";
+--------+
| value  |
+--------+
| 195558 |
+--------+
1 row in set (0.00 sec)

A "write" is distinct from a "disk write".

Writes include writes to tmp tables.

This pattern avoids having to do subtracts (for Handler%, not for Innodb%):

FLUSH STATUS;
perform some query;
SHOW SESSION STATUS LIKE 'Handler%';

Innodb_% values are only GLOBAL; Handler% are SESSION, then added to the GLOBAL tallies.

Handler% is done at an engine-agnostic level; Innodb% is done in the InnoDB engine.  (This should answer you engine questions, but I have not double checked.)

Fraction of read requests that had to hit disk =
  Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
Possibly the numerator needs to be Innodb_pages_read or Innodb_data_reads.

Sorry that I don't have answers to all your questions.


Setup error :Attempted to perform an unauthorized operation SQL Server 2014 Installation of SQL Server 2014 on Windows 10 Home.

From the log details:- It seems modifying this registry key is causing the problem

HKEY_LOCAL_MACHINE@SYSTEM\CurrentControlSet\Services\FltMgr

    5:02 Slp: Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
(01) 2017-05-19 12:15:02 Slp:     Message:
(01) 2017-05-19 12:15:02 Slp:         Attempted to perform an unauthorized operation.
(01) 2017-05-19 12:15:02 Slp:     HResult : 0x84bb0001
(01) 2017-05-19 12:15:02 Slp:         FacilityCode : 1211 (4bb)
(01) 2017-05-19 12:15:02 Slp:         ErrorCode : 1 (0001)
(01) 2017-05-19 12:15:02 Slp:     Data:
(01) 2017-05-19 12:15:02 Slp:       WatsonData = HKEY_LOCAL_MACHINE@SYSTEM\CurrentControlSet\Services\FltMgr
(01) 2017-05-19 12:15:02 Slp:       DisableRetry = true
(01) 2017-05-19 12:15:02 Slp:     Inner exception type: System.UnauthorizedAccessException
(01) 2017-05-19 12:15:02 Slp:         Message:
(01) 2017-05-19 12:15:02 Slp:                 Attempted to perform an unauthorized operation.
(01) 2017-05-19 12:15:02 Slp:         HResult : 0x80070005
(01) 2017-05-19 12:15:02 Slp:         Stack:
(01) 2017-05-19 12:15:02 Slp:                 at Microsoft.SqlServer.Configuration.Sco.InternalRegistryKey.OpenSubKey(String subkey, RegistryAccess requestedAccess)
(01) 2017-05-19 12:15:02 Slp: ----------------------------------------

I know about this thread Setup error :Attempted to perform an unauthorized operation

I did the same :- Tried changing the registry setting to full control for everyone via safe mode. Disabled defender, antivirus and firewall. But no luck.

Seems something to do with FILESTREAM in SQL server. Any idea on how to get rid of this issue?

In my Case, creating an Administrator Windows Account and installing SQL Server worked!
Also you can simply activate the built-in windows Administrator Account by running the net user administrator /active:yes command in command prompt(Admin Mode), then log in as Administrator. Now You can Install SQL Server without problem.

Afterwards you can use SQL Server from your own normal windows account after installation.

I would recommend deactivating Administrator account by running net user administrator /active:no command in command prompt(Admin Mode)


SQL 2016 Always encrypted for all data? I have been tasked with encrypting all data on an SQL database (SQL 2016 standard).

However this doesnt support TDE (Transparent Data Encryption) when I try I get the below error.

  Transparent Data Encryption is not available in the edition of this
  SQL Server instance. See books online for more details
  on feature support in different SQL Server editions.

Product Version:          13.0.4001.0
Product Name:             SQL Server 2016
Product Level:            SP1
Product Edition:          Standard Edition (64-bit)

So I am guessing my only option is to use "always encrypted"?
I have opened the wizard and can only pick columns one at a time, is there a way to select all columns in all tables and encrypt them?

AlwaysEncrypted is not designed to be a solution for encrypting all of the data in the database.  There are quite a number of limitations, such as those documented in T-SQL Tuesday #69 : Always Encrypted Limitations and the official Always Encrypted (Database Engine).  For example, you can't encrypt columns with IDENTITY property--so right there is one reason it's unlikely anyone is going to encrypt an entire production database with AlwaysEncrypted.

AlwaysEncrypted is a solution for encrypting a few columns in a table that hold truly sensitive data, such as Social Security numbers, tax IDs, birth dates, credit card numbers, etc.

Additionally, the client application is what decrypts the data when AlwaysEncrypted is used.  Accordingly, you won't be able to use it with a third-party application unless it specifically supports AlwaysEncrypted.  If it is an in-house application, it would need to be modified to support AlwaysEncrypted.

If you need all of the data at rest to be encrypted to comply with regulations, you'll have to get an Enterprise Edition license to use TDE, encrypt it at the storage layer, or use a different database engine.


Modeling Conversion Events in a Data Warehouse How should I model conversion events?

For instance, if a sales opportunity goes from "stage: prospecting" to "stage: validating", should that be treated as a changing dimension, or a conversion event in the fact table?

Max

This is commonly represented with an accumulating snapshot fact table or timestamped accumulating snapshot fact table. Accumulating snapshot tables model events in progress for business processes that involve a predefined series of steps. The timestamped.

You'll have a date key in the table that represents the date the opportunity hit each stage. If you need to reproduce what pipeline looked like on a given day or track the fact that an opportunity went into and out of a stage multiple times, you might need to consider the timestamped accumulating snapshot.


Encryption/Decryption with stored procedure / view I'm working on a project that uses Entity Framework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first fields that require encryption.

We plan on using Symmetric Key for cell level encryption for that specific field. For the developers to continue to use Entity Framework, I wanted to make a stored procedure to handle inserts for this table which would encrypt the cell needed, and a View to open the key and decrypt the field.

I plan on encrypting the stored procedure & view so the meta data isn't visible by looking at the definition.

Is this a sound plan? Am I overlooking anything?

Note that encryption of modules in SQL Server is not really encryption; it is more like weak obfuscation. Anyone with a search engine and a few minutes to kill can reverse engineer your objects, so encrypting them is almost completely pointless. DENY VIEW DEFINITION is far more effective here (as well as not giving your developers sysadmin and other privilege escalation).

As for the data, SQL Server 2016 will have Always Encrypted (read about it here), it may make a lot of your work easier if you can wait for that. One of the big plusses is that the app can send the data through the provider already encrypted, so it can't be sniffed out by trace or other man-in-the-middle attacks. Be aware of limitations, too, though:

Always Encrypted Limitations
Performance Impact of Always Encrypted
Always Encrypted Performance : A Follow-Up


How can I find out the last time a MySQL table was analyzed? I'm interested in this for InnoDB mostly, but also for MyISAM.

I looked around in information_schema and did not see this data anywhere.

I'm using MySQL 5.5.16.

There is no immediate control data to tell you that, but there are a few mechanisms you can setup.
MECHANISM #1
If you have binary logging enabled, simply do a grep -i "analyze table" against all the binary logs using the output from mysqlbinlog.
MECHANISM #2
If you have the general log enabled, simply do a grep -i "analyze table" against the general log file and locate the timestamp just about the command.
MECHANISM #3
You should schedule a cronjob that runs ANALYZE TABLE against all tables that have high-write, high-update, high-delete volume. That way, there is no guess work.
MECHANISM #4
Try setting innodb_stats_on_metadata to have a measure of predictability as to when an InnoDB table needs ANALYZE TABLE. (See my Mar 26, 2012 post  :
When are InnoDB table index statistics updated? )
CAVEAT
In the past, I have often stated that running ANALYZE TABLE table against InnoDB is useless.

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Aug 04, 2011 : Optimizing InnoDB default settings
Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down

Hopefully, MECHANISM #4 is probably what you need.


Master and Slave having same id's I am working as a  remote DBA for some of the production servers.

There was replication from Master to Slave.

There is something changed in the scenario they have made one of the Slaves as master now..

when i saw the error log of the machine which is master now I got an error.I was unable to understand what is this..

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it.

but all are working well now.Please help to understand what was that.

Thanks..!

Here is something interesting about MySQL Replication:

When it comes to Master/Slave Replication

Master DB Server must have server-id defined in my.cnf
Master DB must have binary logging enabled (define log-bin)
Slave DB Server is not required to defined server-id in my.cnf

If, all of a sudden, you find yourself having to reverse the DB Server's roles (making the old Slave the new Master, and making the old Master the Slave), the requirement to define server-id and log-bin must also hold true.

BTW a DB Server without server-id being set has a default value of 1. If server-id was define as 1 on the Master and the Slave did not have server-id defined, the Slave would also have had server-id being equal to 1. Activating the Slave as the new Master with any trace remnants of the old replication setup wmay have attempted to complete the processing of any remaining SQL entries in the relay logs. Those entries carried the server-id of 1. Bringing those entries over to a new machine without server-id define would have caused mysqld to complain about processing relay logs entries with the same server-id.

Perhaps, someone forgot to define the server-id on the Slave before making it the new Master and trying to engage Replication afterwards. Adding server-id to my.cnf and a quick restart of mysqld would have eliminated this weird situation without giving it a second thought.


Query not showing in dba_hist_active_sess_history The application team claims there was a statement that took about 30s to execute at about a certain time.

I tried to find out what queries had been executed in during this time with the following statement:

select a.sql_id, sample_time from dba_hist_active_sess_history a, dba_hist_sqltext b
        where sample_time between to_date('20170330:13:15','yyyymmdd:hh24:mi')
        and to_date('20170330:13:25','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
        order by 2;

However, only statements concerning rman, or the cloud control agent show up and no statement that seems to be from the application.

Could it be that some queries executed do not show up in the dba_hist_active_sess_history table and where else could I look for it?

Afer checking that you are indeed on the correct database and if ASH- and AWR reports for both instances don't show any long running queries it's time to get in touch with your application developers. Try to find out which statement exactly is slown and why.

I had similiar issues with various applications and application environments and issues I encountered where

jdbc:thin behaviour different form jdbc:oci
issues with different ojdbc driver versions
code changes that split a transaction in multiple small connections resulting in the database doing nothing but opening and closing connections
configuration issues with connection pools

So you might have to look in different locations.


SQL Server high availability fn_hadr_backup_is_preferred_replica returns 0 zero on secondary I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary.  If I run:

SELECT availability_groups.name,
   dm_hadr_availability_replica_states.role_desc,
   UPPER(availability_groups.automated_backup_preference_desc) as 'preference'
FROM sys.databases databases
  INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
  INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
  INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = 'EXT_CONFIG_DB'

on the secondary, I get:

name            role_desc   preference
SP-EXT-CONFIG   SECONDARY   SECONDARY

This is as expected.  However if I then run:

SELECT sys.fn_hadr_backup_is_preferred_replica('EXT_Config_DB') as 'is_preferred_replica'

I get:

is_preferred_replica
0

This is wrong and should be set to 1. As the fn_hadr_backup_is_preferred_replica bit is set to 0 the Ola job doesn't backup or hence truncate the log.  Searches seem to suggest lowercase instance names may be the issue, but all my instance names are upper case.

Can anyone suggest a resolution to fn_hadr_backup_is_preferred_replica returning 0 instead of 1?

This is wrong and should be set to 1.

If you want it set to 1, then you should configure your backup preferences properly.

The reason it is not returning a 1 is because it shouldn't be based on your current preferences. Right now, all of your replicas have the exact same backup priority which means we're going to next look at replica names. Since UK-DR-SQL001\WSS sorts first, that should be the secondary (given that UK-SB-SQL01\WSS is your primary) that the function returns a 1 on.

If you want the backups to happen on a different server, then set the priority appropriately.

Here is an extremely simple repro showing that given your current settings, UK-DR-SQL001\WSS should be the replica which a 1 is returned.

CREATE TABLE #Replicas
(
    ReplicaName VARCHAR(40) NOT NULL,
    BackupPriority INT NOT NULL
)
GO

INSERT INTO #Replicas(ReplicaName, BackupPriority)
Values('UK-DR-SQL001\WSS', 50)
,('UK-SB-SQL01\WSS',50)
,('UK-SB-SQL02\WSS',50)
GO

-- current case UK-SB-SQL01\WSS is the PRIMARY
-- prefer secondary is set
-- default collation assumed

SELECT ReplicaName, BackupPriority
FROM #Replicas
WHERE ReplicaName <> 'UK-SB-SQL01\WSS'
ORDER BY BackupPriority DESC --most important first
, ReplicaName ASC

  I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary

It seems to be that this isn't setup or running on the DR server, or a backup would at least be attempted.


How does ORDER BY really work? I have one question that confusing me.

By logical query processing, the SELECT clause comes before the ORDER BY clause, and in theory, every step in a query creates a virtual table. So, how is it even possible to order result by a column that is not listed in the SELECT clause, when the SELECT clause generates a virtual table with the selected columns?

In case your query has a GROUP BY clause, then the behavior become normal, but in the absence of a GROUP BY clause, it seems a little tricky. Does anybody have an explanation?

The column specified in the ORDER BY is always retrieved from its underlying table even the specified column is not specified in the select list. An example to show the behavior,

Execute the T-SQL below and include action execution plan,

USE AdventureWorks;

SELECT Title, LastName
FROM Person.Person
WHERE PersonType = 'EM'
ORDER BY FirstName;

In execution plan, the output list of the clustered index scan operator shows 'Title', 'FirstName', and 'LastName'. Then the result set is sorted in the sort operator before returning the ordered result with the specified select column in select operator.


How can I fill in a foreign key value after creating the first entry of the table the foreign key is referencing? I am seeking to create an online store so I got to the database definition. I want to have as few tables as possible, so I've got them narrowed down to:

Users
Products
Orders
Cart_Items

I got to the following schema: image

The logic flows like this: For each product added in the cart, a new Cart_Item entry is created. But the Cart_Item entry needs to fill in the foreign key that references the Order table, which is cartItemOrderID. Since there's no order created, it'll have to be null at first. Once the user adds all their desired items in the cart, they place the order, and a new Order is created, so now I have the orderID. How would I pass this orderID to the entries in the Cart_Item table, since there's no way to tell they were placed by the current user?

I'm missing something huge but I can't put my finger on it.

I think your main challenge is the requirement that the Order is in between your User and Cart_Item.

I am assuming you are planning on storing items put in the shopping cart in Cart_Item (items planned to be purchased but not currently purchased.  Also there is only 1 list of items pending purchase per user).  And then items actually purchased by the user (coming from he shopping cart) will land in Order.  In my opinion, there should not be any kind of link between the "order" and the "shopping cart".  If there was, you would need to store some kind of Cart_ItemId on the Order table, not an orderId on Cart_Item.  If you were to keep a Cart_ItemId, you would then have to have some kind of shopping cart history, which to me, seems redundant since that data would be saved in your "order history" though Order. You may have another purpose that I am not thinking of so let me know if I am missing something.

Assuming my understanding of what you are looking for is correct.  I believe this below schema is much closer to giving you what you are looking for, and getting you around this Foreign Key issue. (I apologize, your relationship diagram was so much nicer than mine.  Trying to make it work in Visio instead):

Transferring data from your Cart to Your Order could look something like this:

CREATE PROCEDURE [FinalizeOrder] (@UserID INT, @OrderDetails VarChar(500))
AS
BEGIN
    INSERT INTO Order_Item
    (
        userID,
        orderProductId,
        quantity,
        price,
        [date],
        details
    )
    SELECT
    CI.userID,  --userID,
    CI.cartItemProductID,   --orderProductId,
    CI.quantity,    --quantity,
    P.price,    --price,
    GETDATE(),  --[date],
    @OrderDetails --details
    FROM Cart_Item CI
        INNER JOIN Product P
            ON CI.cartItemProductID = P.ProductId
    WHERE CI.userID = @UserID

    DELETE FROM Cart_Item
    WHERE CI.userID = @UserID
END

Now if it was me, I would create Order and OrderItems tables.  That way you don't duplicate data like the User, or address information on each item.  I know you want to limit tables that are created, but I think that will help you a lot.  If that is a route you end up going, than you will have a schema a little like this:

For a quick example.  Lets pretend I am a user (UserID 13 because that is my favorite number).  I fill out a cart with the 5 items.  The records from the Cart_Item table looks like:

+------------+--------+-------------------+----------+
| CartItemId | UserID | CartItemProductID | quantity |
+------------+--------+-------------------+----------+
|          1 |     13 |               123 |        1 |
|          2 |     13 |               456 |        2 |
|          3 |     13 |               789 |        5 |
|          4 |     13 |            123456 |       77 |
|          5 |     13 |                56 |       45 |
+------------+--------+-------------------+----------+

When I hit the last "submit" in the check out process we would then write 1 record to Order and 5 rows to Order_Item.  Those rows would look something like:

[ORDER]
+----------+--------+----------+--------------------------------------------------------+
| OrderId  | UserId |   Date   |                        Details                         |
+----------+--------+----------+--------------------------------------------------------+
| 98564745 |     13 | 4/6/2020 | Order Placed by Kirk Saunders on 4/6/2020 for 5 items. |
+----------+--------+----------+--------------------------------------------------------+

[ORDER_ITEM]
+----------+--------------------+----------+-------+
| OrderID  | OrderItemProductID | Quantity | Price |
+----------+--------------------+----------+-------+
| 98564745 |                123 |        1 |     1 |
| 98564745 |                456 |        2 |   1.5 |
| 98564745 |                789 |        5 |  7.98 |
| 98564745 |             123456 |       77 | 85.99 |
| 98564745 |                 56 |       45 |  1065 |
+----------+--------------------+----------+-------+

A Stored Procedure to handle this might look like:

CREATE PROCEDURE [FinalizeOrder] (@UserID INT, @OrderDetails VarChar(500))
AS
BEGIN
    DECLARE @OrderId INT

    INSERT INTO [Order]
    (
        userID,
        [date],
        details
    )
    VALUES
    (
        @userID,    --userID,
        GETDATE(),  --[date],
        @OrderDetails --details
    )

    SET @OrderId = scope_identity()

    INSERT INTO Order_Item
    (
        OrderId,
        OrderItemProductID,
        Quantity,
        Price
    )
    @OrderId,   --OrderId,
    CI.cartItemProductID,   --OrderItemProductID,
    CI.Quantity,    --Quantity,
    P.Price --Price
    FROM Cart_Item CI
        INNER JOIN Product P
            ON CI.cartItemProductID = P.ProductId
    WHERE CI.userID = @UserID

    DELETE FROM Cart_Item
    WHERE CI.userID = @UserID
END

Hopefully this helps.  Let me know if I need to explain something better or if this doesn't answer your question.


Why does it take so long for Postgres to return a sequence number? I have an application that does bulk loads into a large table (100 million rows). I am using Postgres' COPY FROM functionality to load data from a flat file. The target table has a primary key of id.

To get the bulk insert to work, I created the ids for each row in the load file using:

 SELECT  nextval('apps_id_seq'::regclass)
 FROM    "apps"
 ORDER   BY "apps"."id" ASC
 LIMIT   1

Unfortunately, I am no seeing this query take in excess of 150 seconds. It's causing a whole lot of backups, because some of these files have tens of thousands of rows in them.

Yet when I run that at the command line, I get the return in thousandths of a millisecond. Here is an explain analyze:

                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.64 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)
   ->  Index Only Scan using apps_pkey on apps  (cost=0.57..15886651.40 rows=228128608 width=4) (actual time=0.015..0.015 rows=1 loops=1)
     Heap Fetches: 0
 Total runtime: 0.030 ms

What could it be that causes the delay? The delay is being reported from the NewRelic service.

I have studied your question hard, but can't make sense of the procedure you describe. (You might work on the description some more.)

Why would you generate sequence numbers by hand, when you can just have Postgres generate them automatically? Per documentation:

  If a list of columns is specified, COPY will only copy the data in the
  specified columns to or from the file. If there are any columns in the
  table that are not in the column list, COPY FROM will insert the
  default values for those columns.

Bold emphasis mine. The default value for a serial column is the next id from its sequence.

Are you sure you are not doing a lot of redundant work in a very expensive fashion?


Does it make sense to have CASE .. END in an ORDER BY? Queries like SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end are possible, but: Is this a good practice?

It is common to use parameters in the WHERE part of queries, and to have some computed columns in the SELECT part, but not that common to parameterize the ORDER BY clause.

Let's say we have an application that lists pre-owned cars (à la CraigsList). The list of cars can be sorted by price or colour. we have one function that, given a certain amount of parameters (let's say price-range, colour, and sorting criteria for instance) returns a set of records with the results.

To make it concrete, let's assume cars are all in the following table:

CREATE TABLE cars
(
  car_id serial NOT NULL PRIMARY KEY,  /* arbitrary anonymous key */
  make text NOT NULL,       /* unnormalized, for the sake of simplicity */
  model text NOT NULL,      /* unnormalized, for the sake of simplicity */
  year integer,             /* may be null, meaning unknown */
  euro_price numeric(12,2), /* may be null, meaning seller did not disclose */
  colour text               /* may be null, meaning unknown */
) ;

The table would have indices for most columns...

CREATE INDEX cars_colour_idx
  ON cars (colour);
CREATE INDEX cars_price_idx
  ON cars (price);
/* etc. */

And have some commodity enumerations:

CREATE TYPE car_sorting_criteria AS ENUM
   ('price',
    'colour');

... and some sample data

INSERT INTO cars.cars (make, model, year, euro_price, colour)

VALUES
    ('Ford',   'Mondeo',   1990,  2000.00, 'green'),
    ('Audi',   'A3',       2005,  2500.00, 'golden magenta'),
    ('Seat',   'Ibiza',    2012, 12500.00, 'dark blue'),
    ('Fiat',   'Punto',    2014,     NULL, 'yellow'),
    ('Fiat',   '500',      2010,  7500.00, 'blueish'),
    ('Toyota', 'Avensis',  NULL,  9500.00, 'brown'),
    ('Lexus',  'CT200h',   2012, 12500.00, 'dark whitish'),
    ('Lexus',  'NX300h',   2013, 22500.00, NULL) ;

The kind of queries we are going to make are of the style:

SELECT
    make, model, year, euro_price, colour
FROM
    cars.cars
WHERE
    euro_price between 7500 and 9500
ORDER BY
    colour ;

We would like to have queries of this style in a function:

CREATE or REPLACE FUNCTION get_car_list
   (IN _colour    text,
    IN _min_price numeric,
    IN _max_price numeric,
    IN _sorting_criterium car_sorting_criteria)
RETURNS record AS
$BODY$
      SELECT
          make, model, year, euro_price, colour
      FROM
          cars
      WHERE
           euro_price between _min_price and _max_price
           AND colour = _colour
      ORDER BY
          CASE WHEN _sorting_criterium = 'colour' THEN
            colour
          END,
          CASE WHEN _sorting_criterium = 'price' THEN
            euro_price
          END
$BODY$
LANGUAGE SQL ;

Instead of this approach, the SQL in this function could be generated dynamically (in PL/pgSQL) as a string, and then EXECUTEd.

We can sense a few limitations, advantages and disadvantages with either approach:

Within a function, finding which is the query plan for a certain statement is difficult (if possible at all). Yet we tend to use functions mostly when going to use something often enough.
Errors in static SQL will be caught (mostly) when the function is compiled or when it is first called.
Errors in dynamic SQL will only be caught (moslty) after the function is compiled, and all the execution paths have been checked (i.e.: the number of tests to perform to the function can be really high).
A parametric query like the one exposed will probably be less efficient than a dynamically generated one; yet the executor will have a harder job parsing / making query tree / deciding every single time (which could impact efficiency in the opposite direction).

Question:

How to "get the best of both worlds" (if possible)? [Efficiency + Compiler checks + Debugging easily + Optimizing easily]

NOTE: this is to be run on PostgreSQL 9.6.

General answer

First, I want to address ambiguity in the premise:

  It is common to use parameters in the WHERE part of queries, and to have some computed columns in the SELECT part, but not that common to parameterize the ORDER BY clause.

Computed columns in the SELECT part are hardly ever relevant for the query plan or performance. But "in the WHERE part" is ambiguous.

It's common to parameterize values in the WHERE clause, which works for prepared statements. (And PL/pgSQL works with prepared statements internally.) A generic query plan often makes sense regardless of provided values. That is, unless tables have a very uneven data distribution, but since Postgres 9.2 PL/pgSQL re-plans queries a couple of times to test whether the generic plan seems good enough:

PostgreSQL Stored Procedure Performance

But it's not as common to parameterize whole predicates (including identifiers) in the WHERE clause, which is impossible with prepared statements to begin with. You need dynamic SQL with EXECUTE, or you assemble the query strings in the client.

Dynamic ORDER BY expressions are somewhere in between both. You can do it with a CASE expression, but that's very hard to optimize in general. Postgres might use indexes with a plain ORDER BY, but not with CASE expressions hiding the eventual sort order. The planner is smart, but not an AI. Depending on the rest of the query (ORDER BY may be relevant for the plan or not - it's relevant in your example), you may end up with a sub-optimal query plan all the time.
Plus, you add the minor cost of the CASE expression(s). And in your particular example also for multiple useless ORDER BY columns.

Typically, dynamic SQL with EXECUTE is faster or much faster for this.

Maintainability should not be a problem if you maintain a clear and readable code format in your function body.

Fix demo function

The function in the question is broken. The return type is defined to return an anonymous record:

RETURNS record AS

But the query actually returns a set of records, it would have to be:

RETURNS SETOF record AS

But that's still unhelpful. You'd have to provide a column definition list with every call. Your query returns columns of well-known type. Declare the return type accordingly! I am guessing here, use actual data types of returned columns / expressions:

RETURNS TABLE (make text, model text, year int, euro_price int, colour text) AS

I use the same column names for convenience. Columns in the RETURNS TABLE clause are effectively OUT parameters, visible in every SQL statement in the body (but not inside EXECUTE). So table-qualify columns in queries in the function body to avoid possible naming conflicts. The demo function would work like this:

CREATE or REPLACE FUNCTION get_car_list (
    _colour            text,
    _min_price         numeric,
    _max_price         numeric,
    _sorting_criterium car_sorting_criteria)
  RETURNS TABLE (make text, model text, year int, euro_price numeric, colour text) AS
$func$
      SELECT c.make, c.model, c.year, c.euro_price, c.colour
      FROM   cars c
      WHERE  c.euro_price BETWEEN _min_price AND _max_price
      AND    c.colour = _colour
      ORDER  BY CASE WHEN _sorting_criterium = 'colour' THEN c.colour     END
              , CASE WHEN _sorting_criterium = 'price'  THEN c.euro_price END;
$func$  LANGUAGE sql;

Do not confuse the RETURNS key word in the function declaration with the plpgsql RETURN command like Evan did in his answer. Details:

Difference between return next and return record

General difficulty of the example query

Predicate on some columns (even worse: range predicates), other columns in ORDER BY, that's already hard to optimize. But you mentioned in a comment:

  Actual result sets can be in the order of several 1.000 rows (and thus, sorted and paginated in smaller chunks server-side)

So you'll add LIMIT and OFFSET to these queries, returning the n "best" matches first. Or some smarter pagination technique:

Improve performance for order by with columns from many tables

You need a matching index to make this fast. I don't see how this could possibly work with CASE expressions in ORDER BY.

Consider:

Optimize a query with small LIMIT, predicate on one column and order by another
Can spatial index help a "range - order by - limit" query


What should be architecture of multi client web application? My question is not related to a specific technology rather it's about design methodology that should be chosen. My company is about to create a web application whose users will be corporate companies. The application will store data of all the signed up/registered companies including financial data. We are unsure what should be the architecture of the application specifically from database point of view.

I can think of two approaches:

Single database for all the companies with same codebase. The company is identified using company table.
Separate Database for each company with same codebase or may be different codebase for each company/client.

Both of the approaches have their pros and cos. If single database is used then data size will increase very fast and performance issue can be encountered due to load by multiple company users hitting the same database. The benefit of this approach is that schema is very easy to manage schema. The separate database on the other hand has benefit of reduced performance overhead, privacy of company data. But the hard part is that a single change in schema needs to be replicated in all the company databases.

These are the things we are aware of. As part of our policy, we would not sell the code to the company/client. Only license will be bought by them.

We are only interested in relational databases and would not be going for NoSQL. Plus the number of client/companies will not be limited and can grow to any number.

What is the better way to design database architecture of this scenario. I know there are thousands of applications that might have faced this scenario before development, but this is my first time :)

So would really like your input on what is the better approach for that.

Thank you very much!

Let me start with: you miss 3: Mutliple databases each containing data for a number of clients - allows both scale out (important) as well as not having possibly hundreds of thousands of databases.

But even then, you really have some bad logic - basically it is a lot less a problem than you think:

  If single database is used then data size will increase very fast and
  performance issue can be encountered due to load by multiple company users
  hitting the same database.

Ah, yes, and if you have a dozen databases and your server is too slow the exact same happens. Also data size increase is identical unless you have a lot of shared data that you only need to load once.

  The benefit of this approach is that schema is very easy to manage schema.

No. You deal with a product, so schema management should be fully automated. After all, you will likely have to manage schemata of a dozen or two database copies ANYWAY - development, a separate schema possibly per developer, some for testing, then quality control. Unless you plan everything to happen manually db changes will use change scripts and be automated.

And once automated - managing 1000 copies of a database is not really much more a problem, or?

  The separate database on the other hand has benefit of reduced performance
  overhead, privacy of company data.

No. I mean, yes, it has less performance overhead. But this is cent wise and irrelevant - I doubt the overhead is more than a couple of percent. Privacy CAN be important, it makes a lot of scenarios a lot easier to separate customers. But only if you have separate web servers for every customer (or a good maintenance window) - otherwise you have to update all databases at exactly the same moment as code. Have fun with that.

Privacy can be important. It also allows easy import and export of data. As well as easily transporting data to a separate instance in case of a bug so people can look at it.

It also will likely use a lot more resources on the web server and database server as connection management will use a lot more connections because they are not reusable. Unless you program partially around it.

  But the hard part is that a single change in schema needs to be replicated in
  all the company databases

Only if your development process is really backward and 1990 or so. Agile and DevOps already demand this to be solved, so this is a long solved problem and you have it anyway. Not sure how your team works, but I am in a smallish team (around 6 developers) in a project at the moment. One production database. Total db copy count is around 20 - multiple test environments, every developer has a separate database for his work, quality control, hallway testing, all have their own copy of the database. As such, schema management is fully automated. I could not care less about having 1000 other copies to run the scripts on. It is a little more scripting (and the time and parallelism requirements are an issue) but the core problem is solved anyway. If you do not have this, I fear your product will have manually maintained databases and the result of that will be a horrifically botched development process.


How to Import mongodb database from development Environment to local env by using Commmand? I have lots of collections in my development server. I need to use the same data for testing scenarios. I am trying to import all the data of development environment to local by command is it possible?

I successfully imported to a file by using below command:

mongoimport -d dbName -c collectionName dataFile.js

But how can we do same to import DB from development environment to local Env command-line?

The mongoimport tool is only intended for importing simple data in text formats (JSON, CSV, TSV).

To backup and restore a full database (including index definitions & collection options) typical approaches are to use either:

Filesystem snapshots
mongodump / mongorestore

The MongoDB manual has a section on Backup and Recovery describing supported approaches for different types of deployments (standalone, replica set, sharded cluster).


pgAgent Job's "last result" is continuously "running" I am using PostgreSQL-9.3 on Ubuntu-14.04 and using "/usr/share/pgadmin3/pgagent.sql" I have configured my pgAdmin3 to use pgAgent. postgres is my maintenance Db and I have run the pgagent.sql content into it's SQL editor to bring the "Jobs" node in pgAdmin3-gui.
In order to check how does this pgAgent works, I made a sample Db and then a test table under it. I have properly configure the "Steps" and "Schedule" parameters. But after starting this newly created job from the Job node using "right-click and run now", my step is running just once and then it's not terminating after the execution. I am sure that my SQL and other parameters are right since I have seen the table modification after the very first run of the step but then the step is not terminating. It's a simple insert SQL command.
Any help regarding why the step is not terminating?

This was a bug in pgagent, it has been fixed in http://git.postgresql.org/gitweb/?p=pgagent.git;a=commitdiff;h=f9bf1ccb27ebcfce00e7a6d467bc0e1b5ee9555e.


Why does the varchar datatype allow unicode values? I have a table with a varchar column. It is allowing Trademark(™), copyright(©) and other Unicode characters as shown below.

Create table VarcharUnicodeCheck
(
col1 varchar(100)
)

insert into VarcharUnicodeCheck (col1) values ('MyCompany')
insert into VarcharUnicodeCheck (col1) values ('MyCompany™')
insert into VarcharUnicodeCheck (col1) values ('MyCompany░')
insert into VarcharUnicodeCheck (col1) values ('MyCompanyï')
insert into VarcharUnicodeCheck (col1) values ('MyCompany')

select * from VarcharUnicodeCheck

But the definition of varchar says, it allows non-unicode string data. But the Trademark(™) and Registered(®) symbols are Unicode characters. Does the definition contradicts the property of varchar datatype? I read couple of links like first one and second one. But still I could not understand why it allows unicode string when the definition says that it allows only non-unicode string values.

But the Trademark(™) and Registered(®) symbols are Unicode characters.

You are wrong here. Your strings contain only ascii characters.
Here is a simple test that shows you that your characters are all ascii (+ some extended ascii with ascii codes between 128 and 255):
declare @VarcharUnicodeCheck table
(
col1 varchar(100)
)

insert into @VarcharUnicodeCheck (col1) values ('MyCompany')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany™')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany░')
insert into @VarcharUnicodeCheck (col1) values ('MyCompanyï')
insert into @VarcharUnicodeCheck (col1) values ('MyCompany')

select *,
        right(col1, 1)as last_char,
        ascii(right(col1, 1)) as_last_char_ascii
from @VarcharUnicodeCheck;

Here you can clearly see that all your characters are 1-byte encoded:

Yes they are not pure ascii characters but they are Extended ASCII.
Here I show you real Unicode character Trademark(™) and its code and binary representation:
declare @t table (uni_ch nchar(1), ascii_ch char(1));
insert into @t values (N'™', '™');

select unicode(uni_ch) as [unicode of ™],
       ascii(ascii_ch) [ascii of ™],
       cast(uni_ch as varbinary(10)) as [uni_ch as varbinary],
       cast(ascii_ch as varbinary(10)) as [ascii_ch as varbinary]
from @t;

Finally, you can see that Trademark(™) Unicode character has 8482 code and not 153:
select nchar(8482), nchar(153)


PostgreSQL is using more memory than I'd expect I have PostgreSQL configured mostly how pgtune recommended, and the settings are supposedly conservative. PostgreSQL is using more memory for a single connection than the settings would suggest is allowed, causing my Linux system to run out of RAM. Here are my settings for a system with 64GB of RAM:

# All set by pgtune...
default_statistics_target = 50
maintenance_work_mem = 5GB # ... except I increased this one...
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 32GB # ... and decreased this one
work_mem = 384MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 15GB
max_connections = 80

Postgres is running a huge UPDATE query right now using the process on top:

PID   USER      PR   NI VIRT    RES    SHR   S  %CPU %MEM     TIME+ COMMAND
21857 postgres  20   0 43.381g 0.042t 0.015t S   1.0 68.9 605:40.48 postgres
20623 postgres  20   0 15.475g 0.015t 0.015t S   0.0 24.3  64:25.95 postgres
20624 postgres  20   0 15.457g 7.021g 7.013g S   0.0 11.2   0:41.90 postgres

RES is ~42GB for that one. Combined with those other two processes, that makes ~64GB of RAM used, so something is probably page faulting. Shouldn't it be less than or equal to 32GB per process? I thought maybe the work_mem was not included in effective_cache_size (documentation doesn't explicitly state it), but that couldn't take 10GB of RAM by itself unless I had 26 joins at once, and I have at most 4. So I'd expect at the very most 34GB RES for one process. What am I missing here?

If table being updated by your huge UPDATE statement has foreign-key constraints on it, then it will queue up every row updated so that it can validate the constraint at the end of the UPDATE statement.  To maintain this queue, it will use as much memory as it needs to, regardless of the settings of work_mem or anything else.  That is probably where your 43GB is going.  If you don't have enough memory to deal with this, you may need to break up your update into smaller transactions, or drop the constraint and re-add it later.

You are probably over-counting the amount of memory actually used.  The "RES" column reported by top will report the amount shared memory (roughly, shared_buffers, with a little more for locks and other overhead) once under each process which has touched that shared_memory.


Create a monthly job to export from SQL Server 2014 to Excel There are a lot of questions here asking "how to export from SQL Server to Excel", but every answer, together with stuff I know, is giving me problems (64bit system here).

We have a developer that monthly, runs a query 6 times (changing the year from 2010, 2011 etc.). Then, he copies and pastes the results into an Excel spreadsheet.

I'm having problems exporting this data from SQL Server 2014 to Excel.

This is the query:

SELECT
R.numero,
R.ano,
R.data 'data abertura',
R.datahora_conclusao  'data conclusão',
R.datahora_emissao 'data emissão',
S.[status],
(ins.INSTITUTO +
            ' - ' + dir.DIRETORIA +
            (CASE WHEN NOT nuc.NUCLEO IS NULL THEN  ' - ' + nuc.NUCLEO  ELSE '' END) +
            (CASE WHEN NOT equ.EQUIPE IS NULL THEN ' - ' + equ.EQUIPE ELSE '' END)) AS UNIDADE,
N.codigo 'codigo natureza exame',
Case E.cod_estadopreservacao when 1 then 'Preservado' when 2 then 'Não Preservado' else 'Laborátorio' end 'Local Exame',

u.nome 'Perito Designado'
FROM tbReps R
LEFT Join tbExames E on E.cod_rep = R.cod_rep
LEFT JOIN tbUsuarios U ON U.cod_usuario = R.cod_perito_primeiro
LEFT JOIN tbUnidades               AS uni WITH(NOLOCK) ON R.cod_unidade = uni.cod_unidade
LEFT JOIN lstUnidadesInstitutos AS ins WITH(NOLOCK) ON uni.cod_instituto = ins.cod_instituto
LEFT JOIN lstUnidadesDiretorias AS dir WITH(NOLOCK) ON uni.cod_instituto = dir.cod_instituto AND uni.cod_diretoria = dir.cod_diretoria
LEFT JOIN lstUnidadesNucleos AS nuc WITH(NOLOCK) ON uni.cod_diretoria = nuc.cod_diretoria AND uni.cod_nucleo = nuc.cod_nucleo
LEFT JOIN lstUnidadesEquipes AS equ WITH(NOLOCK) ON uni.cod_nucleo = equ.cod_nucleo and uni.cod_equipe = equ.cod_equipe
LEFT JOIN lstNaturezasExame N ON R.cod_naturezaexame = N.cod_naturezaexame
LEFT JOIN lstStatus S ON S.cod_status = R.cod_status
WHERE  year(r.data) = 2016 and r.data <= '2016-04-30 23:59:59'
order by data

I'm trying to think of something to import this directly into an Excel file, separated by year. I thought it would be a good idea to create 6 tables (2010...2011...2012 ) and use the Task > Export process, and then delete them. But I think there's a better (and more correct) way to do this.

Which OLE.DB provider should I use? Is there a script to do this? Some procedure that I don't know?

I'm using with (nolock) everywhere for test purposes only.

PowerShell is the way to go:

# Original Author : Bill Fellows (http://billfellows.blogspot.com/2011/03/powershell-export-query-to-csv.html)
# Modified by     : Kin Shah (http://dba.stackexchange.com/users/8783/kin)
                    # Added the -NoTypeInformation to remove "#TYPE System.Data.DataRow" from the header !

# http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html
$server = "servername\INSTANCE1"  #YourServer Name e.g. server1\instance1
$database = "your_db_name"    #your database name
$query = "select some columns from table inner join other table on table.id = othertable.id"

# powershell raw/verbatim strings are ugly
# Update this with the actual path where you want data dumped
# change here to the path that you want
$extractFile = @"
C:\SQLtoExcel.csv
"@

# Use windows authentication !!
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# dump the data to a csv
# http://technet.microsoft.com/en-us/library/ee176825.aspx
# -NoTypeInformation will remove the first #TYPE System.Data.DataRow
$DataSet.Tables[0] | Export-Csv $extractFile  -NoTypeInformation

See also:

Multi-Tabbed Excel Through Powershell by Kevin Feasel


PostgreSQL duplicate key violates unique constraint error CentOS 6.2 64bit Server
PostgreSQL 8.4.9 installed via yum

I am seeing errors from time to time in PostgreSQL's logs about a table called events.  The events table is used to stores information generated by triggers in Zabbix everytime a specific event occurs on a server that is being monitored.

Zabbix is the only application using the database server.

Here is the error:

INSTRUCTION :  insert into events (eventid,source,object,objectid,clock,ns,value,value_changed) values (6393428,2,3,38,1353354000,0,1,0)
ERROR:  duplicate key violates unique constraint « events_pkey »

There is no sequence associated with the table events

Here is the table structure for events

                               Table « public.events »
    Colonne    |  Type   |          Modificateurs           | Stockage | Description
---------------+---------+----------------------------------+----------+-------------
 eventid       | bigint  | non NULL                         | plain    |
 source        | integer | non NULL Par défaut, 0           | plain    |
 object        | integer | non NULL Par défaut, 0           | plain    |
 objectid      | bigint  | non NULL Par défaut, (0)::bigint | plain    |
 clock         | integer | non NULL Par défaut, 0           | plain    |
 value         | integer | non NULL Par défaut, 0           | plain    |
 acknowledged  | integer | non NULL Par défaut, 0           | plain    |
 ns            | integer | non NULL Par défaut, 0           | plain    |
 value_changed | integer | non NULL Par défaut, 0           | plain    |
Index :
    "events_pkey" PRIMARY KEY, btree (eventid)
    "events_1" btree (object, objectid, eventid)
    "events_2" btree (clock)
Référencé par :
    TABLE "acknowledges" CONSTRAINT "c_acknowledges_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE
    TABLE "alerts" CONSTRAINT "c_alerts_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE

Question

What steps can I take to resolve this error?

Thank you

Updated 11/26

I tried to run the ALTER TABLE ALTER COLUMN statement that dezso sent me, but I get an error about the bigserial type.

zabbix=# ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;
ERREUR:  le type « bigserial » n'existe pas

I can however create a new table with that data type

zabbix=# \connect postgres
psql (8.4.13)
Vous êtes maintenant connecté à la base de données « postgres ».
postgres=# CREATE TABLE test(x bigserial);
NOTICE:  CREATE TABLE créera des séquences implicites « test_x_seq » pour la colonne serial « test.x »
CREATE TABLE
postgres=# \ds
              Liste des relations
 Schéma |    Nom     |   Type   | Propriétaire
--------+------------+----------+--------------
 public | test_x_seq | séquence | postgres
(1 ligne)

How should I go about changing eventid to bigserial?

Update 11/27

I have tried another method to add the sequence to the column eventid

create sequence eventid_seq;
alter table events alter column eventid set default nextval('eventid_seq');
select (max(eventid) +1) as nexteventid from events;
SELECT setval('eventid_seq', 6645754);

That worked, but when I turned Zabbix back on, the eventid value continued to increase but the sequence didn't.  What am I doing incorrectly?

(I try to compile an answer from the comments.)

It looks like there is a problem in the process with which your application generates the value for the primary key.  Generally it is wiser to leave this to the DBMS: define a sequence and leave it fill the PK values.  If you have a sequence, you have at least to options: either set the value like

$eventid = exec(SELECT nextval('your_sequence')) // pseudocode!

and use this in the INSERT statement, or completely leave it to PostgreSQL by omitting PK from the INSERT:

INSERT INTO events (source, object, [...])
VALUES (2, 3, [...]);

(Personally I usually opt for the second solution.)

You can change to this behaviour by setting the data type of your PK column to bigserial (in case of an integer, it would be just serial):

ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;

This will raise a notice which tells you that a sequence called (probably) events_eventid_seq is generated.  You can use it as described above.

EDIT (2012-11-27)

You can set the sequence by a query like

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

In order to let the sequence produce the next value for eventid, you must not set a value explicitly in your INSERT.  If you do, it will take the manually supplied value and won't advance the sequence.  This will cause the same unique violation error later.

Regarding ALTER TABLE: as you could see, there is some discrepancy between ALTER TABLE - type does not exists and CREATE TABLE.  Are you sure you try this on the same database?  I can't remember the older versions, but already 8.3 had the bigserial shorthand (it's not a real type).  (Giving it further thought, it is quite possible you can't use it in ALTER TABLE, since it is a shorthand...)  You can solve the problem by following the manual:

CREATE SEQUENCE events_eventid_seq;

ALTER TABLE events
ALTER COLUMN eventid TYPE bigint;

ALTER SEQUENCE events_eventid_seq OWNED BY events.eventid;

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));


Conditional WHERE clause I have, from what I can tell, a unique question. I would like to know if there's a way to construct a query in which the existence of the actual where clause itself is conditional.

I'm writing a stored procedure where I would like to pass in a filter (e.g. Col1=1 or Col2=3) and my query looks something like the following:

set @filter = 'Col1=1 or Col2=3' --assume these variables have been declared already
select * from @tbl where @filter --I need to use a table variable

Note: I tried using sp_executesql and sp_sqlexec but neither take a table variable. I would also like to avoid using an if-else statement. Please do not suggest either of these as your answer. :)

Dynamic SQL won't be able to see your table variable unless you also declare it and populate it within the same dynamic SQL scope. A #temp table will work fine, and I'm not sure why you "need" to use a table variable, but you can always do this:

SELECT * INTO #tbl FROM @tbl;

Anyway assuming you can change your process:

CREATE TABLE #tbl(Col1 INT, Col2 INT);

INSERT #tbl SELECT 1,4
  UNION ALL SELECT 2,3
  UNION ALL SELECT 5,8;

DECLARE @sql NVARCHAR(MAX);
DECLARE @filter NVARCHAR(255);

SET @sql = N'SELECT Col1, Col2 FROM #tbl';

SET @filter = N'Col1 = 1 OR Col2 = 3';

SET @sql = @sql + COALESCE(' WHERE ' + @filter, '');

EXEC sp_executesql @sql;

DROP TABLE #tbl;

Results:

Col1        Col2
----------- -----------
1           4
2           3

As an aside, you cannot do things like this:

select * from @tbl where @filter

You need to build such statements dynamically. SQL Server won't see @tbl or @filter as entities or where clauses.


Accept remote TCP/IP request from aLL IPs in SQL server I have problem with access to SQL server with TCP/IP.

I would like to SQL instance accept client from all network adapter that machine has.

I go to configuration manager console and enable TCP/IP, set TCP port to 1433 (or any other port) , set “TCP Dynamic Port” to Blank and set “Listen All” to true. After these changes, I restart SQL server service and starting SQL service will fail. For starting SQL service I have to change “Listen All” to false and then service will start successfully.

But after disabling “Listen ALL” you have to set Adapter IP in “IP Addresses” tab and SQL will accept request only from that IP and if you don’t specify any IP, SQL doesn’t accept any TCP/IP request.

Now I need to know why enabling “Listen ALL” will lead to service failure. And how can I resolve this problem?

I ran “netstate –a” and I saw no application has port 1433 on TCP.

When “Listen ALL” was disable and I don’t added any specific IP Yet, I check SQL browser service(UDP 1434) with “portquery.exe” and SQL browser response doesn’t include any TCP port.

After I added 1 specific IP SQL browser service include TCP 1433.

And as I mentioned if I enable “Listen ALL” , SQL service will failure and after restart it can’t start.

After service restart failure I checked windows event viwer and I saw 4 Error as Following :

1.TDSSNIClient initialization failed with error 0xd, status code 0x10. Reason: Unable to retrieve registry settings from TCP/IP protocol's 'IPAll' configuration key. The data is invalid.

2.TDSSNIClient initialization failed with error 0xd, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The data is invalid.

3.Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

4.SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Finally , How I can Listen to all IPs?

Thanks

I found the reason of problem.

I don't know for what SQL or other services had changed the value type of TcpPort fields in registry (HKEY_LOCAL_Machine\Software\Microsoft\Microsoft SQL Server\Instance ID\MSSQLServer\SuperSocketNetLib\TCP\IPALL\TCPPort ).

It's converted to Binary or DWORD type.

but the solution : I removed TcpPort value and then create a new string value and named it TcpPort and set it to 1433 and then service started successfully.

there is no need to say that I checked that field over and over and it doesn't has any whitespaces.


How do I dictate the order of preferred replica on a Always On cluster? I have a problem, I look after a massive estate. the newer AO clusters I built myself and they work fine. I have inherited once AO cluster which is often problematic. However, migrating isn't an option ATM.
My current problem is I notice Netbackup wouldn't take backups from the secondary. After investigation, I found it was trying to take the backup from the DR instance. I've set it to take from the Primary for now.
Current behaviour
AUTOMATED_BACKUP_PREFERENCE = PRIMARY

preferred replica results
DR = 0
primary = 1
secondary = 0
AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY

preferred replica results
DR = 1
primary =  0
secondary = 0
The behaviour I need it to be is how can I get it to be,
AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY

preferred replica results
DR = 0
primary =  0
secondary = 1
However I can't find anything on Microsoft to point me in the right direct?

You can set the backup Priority to be higher on your non-DR node

By curiosity, why are you taking backup on the secondaries ?
There is some cases where your secondary could be out of sync with the primary (even if you set you AG to by Sync), for that reason, I usually prefer to have my backup done on my primary node.
Also, if you take the backup on the secondary node, are you also running checkdb on that secondary node ? (you don't want to end up doing backup of a corrupted secondary)


Decision Tree English Rules and Dependency Network in MS SSAS I created a Decision Tree model in Microsoft Analysis Services (SSAS, Visual Studio 2010). There are two tabs in the Mining Model Viewer tab: (1) Decision Tree that shows a tree itself, and (2) Dependency Network that shows the chart of most important variables.

On the Decision Tree tab I can click on each individual leaf and see the English Rule for that leaf. Is there a way to get ALL the rules at once as a list with the case numbers?

The Dependency Network tab has a slider that you can move to see which variables influence the decision tree most. Is it possible to get ALL important variables as a list with their "importance" number?

I have found a way to get the list of ALL English Rules from the Mining Structure.

SELECT * FROM [MyModel].CONTENT WHERE [CHILDREN_CARDINALITY] = 0

[CHILDREN_CARDINALITY] = 0 defines the DT leaves only.

The following links may be helpful: Decision Trees Model Query Examples, Mining Model Content for Decision Tree Models

"FLATTENED" keyword will split [NODE_DISTRIBUTION] outcomes into separate rows.

SELECT FLATTENED [MODEL_CATALOG],[MODEL_NAME],[NODE_CAPTION],[CHILDREN_CARDINALITY],[NODE_DESCRIPTION],[NODE_PROBABILITY],[NODE_DISTRIBUTION],[NODE_SUPPORT] FROM [MyModel].CONTENT WHERE [CHILDREN_CARDINALITY] = 0 ORDER BY [NODE_SUPPORT] DESC


Is this design in 3NF? Why to choose another design not in 3NF Having the following entities: Objects, Parts, Subparts and Items, and given the following design restrictions:

A Part belongs to one and only one Object.
A Subpart belongs to one and only one Part.
An Item belongs to one and only one Subpart.

I have implemented the following tables (look at the composite primary keys marked with *):

Object (*IDObject)

Part (*IDPart, *IDObject): IDObject is a Foreign key (the prim. key of Object).

Subpart (*IDSubpart, *IDPart, *IDObject): *IDPart, *IDObject is a Foreign key (the prim. key of Part).

Item (*IDItem, *IDSubpart, *IDPart, *IDObject): *IDSubpart, *IDPart, *IDObject is a Foreign key (the prim. key of Subpart).

1.- I have understood that this design is in 3NF, isn't it?
Assuming that--by theory--Item is a weak entity depending on Subpart; Subpart is a "weak entity" depending on Part, and Part is a "weak entity" depending on Object, these would be the reasons for those composed keys, making this design in 3NF.
But for other people, this design is redundant, and the "best" way for resolving it is through the following tables specification (look at the primary keys marked with *):

Object (*IDObject)
Part (*IDPart, IDObject): IDObject is a Foreign key, but doesn't make part of the prim. key of Part.
Subpart (*IDSubpart, IDPart): IDPart is a Foreign keys, but doesn't make part of the prim. key of Subpart.
Item (*IDItem, IDSubpart): IDSubpart is a Foreign keys, but doesn't make part of the prim. key of Item.

In this specification, the restrictions of "one and only one" would be managed by code.
2.- Why would I choose this implementation, declining to use 3NF?

Both designs are technically 3NF, depending on your data population.

What a lot of people who have only a cursory understanding of formal normalization theory don't understand is that redundancy in foreign keys isn't really redundancy by definition.  Transitive dependencies and partial dependencies apply to the way non-key attributes relate to key attributes.

If your composite keys happen to contain (or even consist of) foreign keys then whether your foreign key has the look of being denormalized isn't relevant.  (i.e. Design 1 isn't redundant in a way which is significant to the formal definition of normal forms.)

The practical issue comes down to what the values of your keys are.  If you have natural keys and the Part key consists of IDObject=123 and IDPart=1 - i.e. your IDPart values are natural number sequences which are only unique within the context of a given IDObject value, then you need to use design 1 (weak entities).

However, what a lot of people do for good (and less good) reasons is apply surrogate primary keys to every or nearly every table.  I'm not going to get into whether or why to use surrogate keys.  That is a holy war for another question.  However, if you happen to have table-wide unique (e.g. IDENTITY) values for IDPart, then your primary key for Part is more complex than it needs to be using design 1.  In that case, there is no need for IDObject in the PK of Part because the table already has a unique key just using IDPart.  In such a case, design 2 is sufficient and simpler, therefore better, as a rule of thumb.

In practice, I would say weak entities are much more common in logical models than in physical models, if for no other reason than that most physical models tend to use surrogate primary keys.


Determining primary key columns in PGAdmin, if the constraint name is generated by system I am trying to learn a new schema of a PostgreSQL database, but it's unclear from looking at pgAdmin which columns per the tables constitute the primary keys. It appears that when the tables were created, they were created with the default PRIMARY KEY (primary_key_column_name)
This results in the default naming convention when checking under the constraints in pgAdmin. How can I check to see what the actual names of the columns are for the primary key for a table?

I'm not sure I understood what you're asking, but if your question is how to determine what columns constitute a table's primary key you can query the catalog like so:
select c.conname constraint_name, o.relname table_name, a.attname column_name
from pg_constraint c
inner join pg_class o
   on c.conrelid = o.oid
inner join pg_attribute a
  on c.conrelid = a.attrelid
-- pg_constraint.conkey is an array of pg_attribute.attnum values
-- "with ordinality" clause helps keep proper column ordering
inner join lateral unnest (c.conkey) with ordinality as cols (attnum, pos)
  on a.attnum = cols.attnum
where c.contype = 'p'
order by conname, relname, pos


Insert output into another newly created table How can I insert the output of below table into newly created [DBname].[dbo].[Tablename]?
The below T-SQL query outputs two columns: server and name.
T-SQL 1
if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1
([server] nvarchar(200),
[name] nvarchar(200),
)

insert into  #temp1

SELECT  (select @@SERVERNAME) as Server,name FROM      master.sys.databases
select * from #temp1

I want to insert the output of T-SQL 1 into a newly created table [DBname].[dbo].[Tablename].I already created two columns server and name.
So far I tried like below:
Insert into [DBname].[dbo].[Tablename]

select

if object_id ('tempdb..#temp1') is not null
drop table tempdb..#temp1

create table #temp1
([server] nvarchar(200),
[name] nvarchar(200),
)

insert into  #temp1

SELECT  (select @@SERVERNAME) as Server,name FROM      master.sys.databases
select * from #temp1

I used the Stack Overflow Q & A referenced below, but somehow could not do what I need:
How to do INSERT into a table records extracted from another table

Make use of the OUTPUT clause.

Let's say you need to insert #TEMP1 data into #TAB2 when an INSERT is done on #TEMP1:

CREATE TABLE #TEMP1
([SERVER] NVARCHAR(200),
[NAME] NVARCHAR(200),
);

CREATE TABLE #TAB2 ([SERVER] NVARCHAR(200),
[NAME] NVARCHAR(200));

INSERT INTO  #TEMP1
OUTPUT INSERTED.[SERVER],INSERTED.NAME INTO #TAB2
SELECT  (SELECT @@SERVERNAME) AS SERVER,NAME FROM MASTER.SYS.DATABASES;

Now see the results in both tables:

SELECT * FROM #TEMP1;
SELECT * FROM #TAB2;

See the product documentation: OUTPUT Clause (Transact-SQL) for more information, including some important restrictions.

If you want to automate this process, you need to write an AFTER INSERT trigger. Please find a very good example exactly matching your requirement in SQL Server: Coding the After Insert Trigger in SQL Server by Vishwanath Dalvi.


Fixed database paths in stored procedures/views - is there a smoother way of doing this? Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers.

Every time we migrate to a new database server, something breaks because some obscure procedure or view didn't get fixed at the same time. Documenting them all is already underway.. I still have to ask - isn't there a simpler way of doing this?

Would it be possible for a stored procedure to extract the connection string from a table - let's say "dbo.linked-databases", and have a unique row per database being references, where one of the columns specify the connecting string? Can stored procedures then SELECT this, and expand it as a variable?

Sorry if the question doesn't make any sense, as I'm quite new with SQL.

A linked server definition includes the name of the server that you are connecting to and includes the security settings for the linked server.   If you  (assuming Microsoft SQL Server) "SELECT * FROM master.sys.databases" you will see all of your linked servers.

Therefore, if you script out all of the linked servers and recreate them on the new server, then you should be able to set up the new server just the same as the old server. (Exception is that any SQL Server logins used in linked servers will not include the passwords, so you will need to update the scripts.)

Of course, the other issue is that one of the servers that you link to will change and you need to update your linked server settings in the primary server.
From that you can determine which linked server definitions need to be changed.

I suggest that you name your linked servers according to their purpose, rather than with a server name.  (E.g. FinancialUpdates rather than AccountingSQL005.)  That way any code hidden away in stored procedures will not need to be changed when some server name changes or a database is moved to a new server.  This also suggests using separate linked servers, even if pointing to the same physical server, for different sets of data.

Just update the linked server by scripting out the definition and change the server name.  Then use sp_dropserver and sp_addlinkedserver to drop the old definition and add the new one.


Locking issue with concurrent DELETE / INSERT in PostgreSQL This is pretty simple, but I'm baffled by what PG does (v9.0).
We start with a simple table:

CREATE TABLE test (id INT PRIMARY KEY);

and a few rows:

INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);

Using my favorite JDBC query tool (ExecuteQuery), I connect two session windows to the db where this table lives. Both of them are transactional (ie, auto-commit=false). Let's call them S1 and S2.

The same bit of code for each:

1:DELETE FROM test WHERE id=1;
2:INSERT INTO test VALUES (1);
3:COMMIT;

Now, run this in slow motion, executing one at a time in the windows.

S1-1 runs (1 row deleted)
S2-1 runs (but is blocked since S1 has a write lock)
S1-2 runs (1 row inserted)
S1-3 runs, releasing the write lock
S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???
S2-2 runs, reports a unique key constraint violation

Now, this works fine in SQLServer. When S2 does the delete, it reports 1 row deleted. And then S2's insert works fine.

I suspect that PostgreSQL is locking the index in the table where that row exists, whereas SQLServer locks the actual key value.

Am I right? Can this be made to work?

Mat and Erwin are both right, and I'm only adding another answer to further expand on what they said in a way which won't fit in a comment. Since their answers don't seem to satisfy everyone, and there was a suggestion that PostgreSQL developers should be consulted, and I am one, I will elaborate.

The important point here is that under the SQL standard, within a transaction running at the READ COMMITTED transaction isolation level, the restriction is that the work of uncommitted transactions must not be visible. When the work of committed transactions becomes visible is implementation-dependent. What you are pointing out is a difference in how two products have chosen to implement that. Neither implementation is violating the requirements of the standard.

Here's what happens within PostgreSQL, in detail:

S1-1 runs (1 row deleted)

The old row is left in place, because S1 might still roll back, but S1 now holds a lock on the row so that any other session attempting to modify the row will wait to see whether S1 commits or rolls back. Any reads of the table can still see the old row, unless they attempt to lock it with SELECT FOR UPDATE or SELECT FOR SHARE.

S2-1 runs (but is blocked since S1 has a write lock)

S2 now has to wait to see the outcome of S1. If S1 were to roll back rather than commit, S2 would delete the row. Note that if S1 inserted a new version before rolling back, the new version would never have been there from the perspective of any other transaction, nor would the old version have been deleted from the perspective of any other transaction.

S1-2 runs (1 row inserted)

This row is independent of the old one. If there had been an update of the row with id = 1, the old and new versions would be related, and S2 could delete the updated version of the row when it became unblocked. That a new row happens to have the same values as some row that existed in the past doesn't make it the same as an updated version of that row.

S1-3 runs, releasing the write lock

So S1's changes are persisted. One row is gone. One row has been added.

S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH???

What happens internally, is that there is a pointer from one version of a row to the next version of that same row if it is updated. If the row is deleted, there is no next version. When a READ COMMITTED transaction awakens from a block on a write conflict, it follows that update chain to the end; if the row has not been deleted and if it still meets the selection criteria of the query it will be processed. This row has been deleted, so S2's query moves on.

S2 may or may not get to the new row during its scan of the table. If it does, it will see that the new row was created after S2's DELETE statement started, and so is not part of the set of rows visible to it.

If PostgreSQL were to restart S2's entire DELETE statement from the beginning with a new snapshot, it would behave the same as SQL Server. The PostgreSQL community has not chosen to do that for performance reasons. In this simple case you would never notice the difference in performance, but if you were ten million rows into a DELETE when you got blocked, you certainly would. There's trade-off here where PostgreSQL has chosen performance, since the faster version still complies with the requirements of the standard.

S2-2 runs, reports a unique key constraint violation

Of course, the row already exists. This is the least surprising part of the picture.

While there is some surprising behavior here, everything is in conformance with the SQL standard and within bounds of what is "implementation-specific" according to the standard. It certainly can be surprising if you are assuming that some other implementation's behavior will be present in all implementations, but PostgreSQL tries very hard to avoid serialization failures in the READ COMMITTED isolation level, and allows some behaviors that differ from other products in order to achieve that.

Now, personally I'm not a big fan of the READ COMMITTED transaction isolation level in any product's implementation. They all allow race conditions to create surprising behaviors from a transactional point of view. Once someone becomes accustomed to the weird behaviors allowed by one product, they tend to consider that "normal" and the trade-offs chosen by another product odd. But every product has to make some sort of trade-off for any mode not actually implemented as SERIALIZABLE. Where PostgreSQL developers have chosen to draw the line in READ COMMITTED is to minimizing blocking (reads don't block writes and writes don't block reads) and to minimize the chance of serialization failures.

The standard requires that SERIALIZABLE transactions be the default, but most products don't do that because it causes a performance hit over the more lax transaction isolation levels. Some products don't even provide truly serializable transactions when SERIALIZABLE is chosen -- most notably Oracle and versions of PostgreSQL prior to 9.1. But using truly SERIALIZABLE transactions is the only way to avoid surprising effects from race conditions, and SERIALIZABLE transactions always must either block to avoid the race conditions or roll back some transactions to avoid a developing race condition. The most common implementation of SERIALIZABLE transactions is Strict Two-Phase Locking (S2PL) which has both blocking and serialization failures (in the form of deadlocks).

Full disclosure: I worked with Dan Ports of MIT to add truly serializable transactions to PostgreSQL version 9.1 using a new technique called Serializable Snapshot Isolation.


Kick a single user out of the database I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database trying to generate reports but I only want C to stop and disconnect while A and B are still able to continue with their processes.

As a one-off you can use EXEC sp_who or EXEC sp_who2 or similar to find the SPIDs of that user's connections and then use KILL <SPID> to stop them. Be aware that any transactions they have open in which changes have been made will need to be rolled back which could potentially take some time and be I/O intensive, but if they are just running reports the effect of this should be minimal. To stop them immediately restarting the processes disable their access before doing this (using ALTER LOGIN <name> DISABLE or by adjusting their access rights more granularly).

If you find yourself needing to do this regularly you could write a procedure to automate the task, so you could just run EXEC kick_user_off '<name>';, though if you need to do this sort of thing regularly enough for it to be worth the effort automating then I suspect you have a process and/or user problem that needs fixing of which this is just a symptom.


Restoring XtraBackup from MySQL 5.6 to MySQL 5.7 We are running MySQL 5.6 and taking backups using XtraBackup 2.4. We want to migrate to MySQL 5.7 using that same backup as we have datasets well over 600GB.

Is it possible to do so and what are prons and cons that we need to worry about while we use the XtraBackup restore?

Xtrabackup is a physical backup tool, and is meant to backup and restore to the same MySQL (or MariaDB) version. It's not designed to work with migrations. It might work, it might not.

So if possible, if you want to restore the backup to a new instance, I think the best approach is to restore it to another MySQL 5.6 instance, and then upgrade that instance to 5.7 afterwards.


How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery? It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too.

In the following, I have a query that functions correctly as far returning results goes but requires a second query that is the same as the one presented here but without OFFSET and the output is just a COUNT(*) of all of the rows.

I have two objectives:

Write the query so that COUNT(*) is returned in the same query. Indeed I have been looking at help pieces such as the excellent SQL SERVER – How to get total row count from OFFSET / FETCH NEXT (Paging) with different ways of solving the problem, but then there's another piece...
Rewrite the join with a window function (e.g. OVER(PARTITION BY) or some more performant way as that query seem to generate an INDEX SCAN and INDEX SEEK on the table. The real query is a bit more complicated in the WHERE part, but it looks to me even one scan could be enough if the query were a bit more straightforward so that the COUNT and MAX could be had simultaneously with the outer query. Even this would be a win, but combined with having the overall COUNT would be even bigger.

Maybe I'm trying to chew a teeny bit more than I can chew currently, but on the other hand, maybe there is now a chance to learn something.

Here are the table and data

CREATE TABLE Temp
(
    Id INT NOT NULL PRIMARY KEY,
    Created INT NOT NULL,
    ParentId INT,
    SomeInfo INT NOT NULL,
    GroupId INT NOT NULL

    CONSTRAINT FK_Temp FOREIGN KEY(ParentId) REFERENCES Temp(Id)
);

-- Some root levels nodes.
INSERT INTO Temp VALUES(1, 1, NULL, 1, 1);
INSERT INTO Temp VALUES(2, 2, NULL, 2, 2);
INSERT INTO Temp VALUES(3, 3, NULL, 1, 3);
INSERT INTO Temp VALUES(13, 13, NULL, 1, 1);

-- First order child nodes.
INSERT INTO Temp VALUES(4, 4, 1, 2, 1);
INSERT INTO Temp VALUES(5, 5, 2, 1, 2);
INSERT INTO Temp VALUES(6, 6, 3, 2, 3);

-- Second order child nodes.
INSERT INTO Temp VALUES(7, 7, 4, 1, 1);
INSERT INTO Temp VALUES(8, 8, 5, 2, 2);
INSERT INTO Temp VALUES(9, 9, 6, 1, 3);

SELECT
    Id,
    newestTable.SomeInfo,
    newestTable.Created,
    CASE WHEN newestTable.RootCount > 1 THEN 1 ELSE 0 END AS IsMulti
FROM
   Temp as originalTable
   INNER JOIN
   (
        SELECT
            SomeInfo,
            Max(Created) AS Created,
            Count(*) AS RootCount
        FROM
            Temp
        WHERE ParentId IS NULL AND GroupId = 1
        GROUP BY SomeInfo
    ) AS newestTable ON originalTable.SomeInfo = newestTable.SomeInfo AND originalTable.Created = newestTable.Created
/*WHERE
(
    originalTable.SomeInfo = 1
)*/
ORDER BY newestTable.Created ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

P.S. Also How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql looks interesting.

<edit:

It looks like

SELECT
    Id,
    SomeInfo,
    GroupId,
    ParentId,
    MAX(Created) OVER(PARTITION BY SomeInfo) AS Created,
    COUNT(Id) OVER(PARTITION BY SomeInfo) AS RootCount,
    CASE WHEN COUNT(Id) OVER(PARTITION BY SomeInfo) > 1 THEN 1 ELSE 0 END AS IsMulti
FROM
    Temp
WHERE
(
    GroupId = 1 AND ParentId IS NULL
)
ORDER BY Created ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

gets close to there. The problem is, though, there are now two result rows and it appears to me this is due to the original INNER JOIN joining back to Temp that cull it to one row. I wonder if there is a way to apply the conditions somehow either before or after the windowing to match more closely the original query. (And this isn't the same query, to be clear. There's just so little data, hence the queries look like being close to each other.)

So it looks like to me what you are missing is the "Return only the top Created record for each instance".  So you are getting all rows, and then watever its top Created value is for the same SomeInfo record.  Unfortunately you can't just add the MAX(Created) = Created into the base WHERE clause.

If you just wrap the whole thing in a CTE you can then just add a MAX(Created) = Created into the WHERE and get what you are looking for (not that i think CTE's are the anwer for everything).

WITH CTE (ID, SomeInfo, GroupID, ParentID, Created, MaxCreated, RootCount, IsMulti)
AS
(
    SELECT
        Id,
        SomeInfo,
        GroupId,
        ParentId,
        Created,
        MAX(Created) OVER(PARTITION BY SomeInfo) AS MaxCreated,
        COUNT(Id) OVER(PARTITION BY SomeInfo) AS RootCount,
        CASE WHEN COUNT(Id) OVER(PARTITION BY SomeInfo) > 1 THEN 1 ELSE 0 END AS IsMulti
    FROM
        Temp
)
SELECT ID, SomeInfo, GroupID, ParentID,  MaxCreated AS Created, RootCount, IsMulti
FROM CTE
WHERE
(
    GroupId = 1
    AND ParentId IS NULL
    AND Created = MaxCreated
)
ORDER BY MaxCreated ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

In my quick test it has the same execution plan and does not take any additional execution time (See execution plan added below). (now it is a small result set so it is probably something you will still need to test with.)

Hopefully that is more of what you are looking for.


How to Publish Checked-In changes only in Team Foundation Server 2013 to SQL Server? I would like to ask you how to publish checked-in changes only in Team Foundation Server 2013 to SQL Server? It's a new installation of TFS 2013 - installed by me.

Currently when I create new object like table as a part of the project and don't do check-in on this table and next publish the project, such table is published anyway. I was thinking that only checked-in (approved) changes are published to SQL Server (without objects which has no approval).

It's important because I want publish only complete objects to the SQL Server. Without objects which are still in development.

If you want to publish only checked in changes while you have uncommitted development, don't use your development workspace for publishing.  Instead, create a separate workspace for that purpose.  Perform a get latest in the publishing workspace so that it includes only the latest checked in objects and then publish from there.  A separate build box is often used for this purpose in larger development environments.

EDIT:

A new workspace for publishing can be created using TFS Source Control Explorer. Select "Workspaces..." from the Workspace drop down and click add.  In the working folder mapping dialog, provide a mnemonic workspace name, specify the same source control folder you use for development but specify a new empty local folder.  Click yes when prompted to get the latest from source control.  This new workspace will contain only the latest checked-in objects.

These development and publishing workspaces share the same source control path but are independent due to the different local folders and files.  Be mindful to make changes only in the development workspace (and get latest to see other team members checked-in changes) and use only get latest to update the deployment workspace.


Export and Import MongoDB? I come from a bash and mysql environment.  I'm trying to get use to mongodb. I see there are different ways to export and import mongodbs.  Does anyone know the bash and mongodb equivalent of

bash> mysqldump -u username -p -h localhost mydatabase> mydatabase.sql

bash> mysql -u username -p -h localhost;

mysql> create database mynewdatabase; grant all privileges on mynewdatabase.* to 'username'@'localhost' identified by 'mypass'; exit;

bash> mysql -u username -p -h localhost mynewdatabase < mydatabase.sql

Look at mongodump and mongorestore here in this tutorial in the documentation for backup and restore tools


What is the purpose of the "territory" configuration parameter? This is the command that I use to create a new Db2 database in my project:

db2 create db <DATABASE_NAME> using codeset 1251 territory ru

Unfortunately, there is no documentation left and I cannot trace the origin of this specific command. I am trying to understand the purpose of the territory parameter. The Db2 documentation says:

  This parameter shows the territory used to create the database.
  territory is used by the database manager when processing data that is
  territory sensitive.

What could be the purpose of this parameter? What kind of data could be territory sensitive? What are the possible use cases where territory matters?

"Territory" is essentially equivalent to country code or locale. This setting affects default behaviour of the database when handling the date format, as well as the default SYSTEM collation (sorting) in some locales. Note that the client locale overrides this, so, when connected to a database with territory ru from a client with a US locale, the default date format will be taken from the client setting.

It is also used by federation to determine the necessary conversions when retrieving data from remote servers with different territory (locale) settings.


Master - Master setup on Postgresql 10 + Ubuntu 18.04 + Pgpool II Does anybody have experience in configuring Pgpool II with Postgresql 10 on Ubuntu 18.04?

I am trying to setup Master - Master setup on Postgresql 10 + Ubuntu. I am trying to use Pgpool II

I will have two or more mater DB servers running on different IPs and my objective is synced with each other.

I am looking for an open-source solution/s Your thoughts, suggestion and experiences are kindly welcome.
Cheers

you can set up master - slave replication using pgpool, master-master is not supported.
To set up active - active replication you will need to use tool like SymmetricDS.
Cheers!


Pull Access schema into a SQL Server table We had an assembly that used to pull a list of tables and their columns from an Access database and place the listing into a SQL Server table for later use.  For various reasons the assembly is no longer working and reworking it is not an option at this time.

Is there a way to pull a listing of table names and columns from a linked server Access database?  If not, is it possible to pull this information from an SSIS package?  The output I need is really simple:

Customer    First Name
Customer    Last Name
Customer    Email
Product     Product ID
Product     Product Name
...

The way we solved this was to use a template db to auto create views against the access linked tables.  Each view was created as

CREATE VIEW AS
SELECT *
  FROM LinkedServer...Table

Once these views had been created we could pull the column listing from sys.columns joined over to sys.views.

SELECT v.name, c.name
  FROM sys.columns c
       JOIN sys.views v ON c.object_id = v.object_id
 WHERE v.name = @viewname

We could then insert this into the table we needed to work with.


Help with database schema design I'm working on an Android application which utilizes a number of MySQL databases.

The design is as such:

Users have many recipes.
Recipes have many ingredients
Recipes have many directions

I understand how to implement joins, but I'm struggling with the schema I should be using to get the desired results. This is where I am so far:

users

user_id
user_email
user_password

recipes

recipe_id
ingredients_list_id
directions_list_id

user_recipes

user_id
recipe_id

ingredients_list

ingredients_list_id
ingredient_id
ingredient_id
ingredient_id
...

directions_list

directions_id
direction
direction
direction
...

ingredients

ingredient_id
ingredient_name

Note: The reason I'm using a separate database for ingredients is that I want to have a shared set of ingredients between all users, this way I can have an "ingredient of the week" with id 8067 or whatever.

I think I'm getting lost around the ingredient_list and directions_list part of the schema. It appears as though it may work, but I'm afraid I'm either over-complicating the problem or missing some crucial/more efficient join(s).

If this is the wrong place to ask this question, please let me know, so I can post elsewhere. Any help would be greatly appreciated!

I would suggest some reading on normalization and many-to-many relationships.

One problem you're probably running into is figuring out how many fields to include with your current ingredients and directions lists. Ingredients_list with ingredients_list_id, ingredient1, ingredient2, and ingredient3 will be insufficient for any recipe needing more than three ingredients. Bumping up the column count to N doesn't work either, when Forrest's Fancy Fruitcake needs N+1 ingredients.

Following the pattern shown in the many-to-many link, you could link ingredients back to the recipe id, where each row in ingredients_list contains one recipe_id and one ingredient_id. Then (simplifying to ignore joins) getting all ingredients for a recipe would look like SELECT [ingredient_id] FROM [ingredient_list] WHERE [recipe_id] = 1234.

Overall, I might change the schema to something like this:

users

user_id
user_email
user_password

recipes

recipe_id
user_id
recipe_name

ingredients_list

recipe_id
ingredient_id
ingredient_qty

directions_list

recipe_id
direction_text
direction_number

ingredients

ingredient_id
ingredient_name


Homebrew installed PostgreSQL could not connect, database files are incompatible with server Here's the error message I'm getting today when I attempt to log in to my localhost PosgreSQL databases using psql:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I installed PostgreSQL using Homebrew and this morning I've uninstalled and reinstalled using that utility, with no change observable.  The psql client works fine, and I can still use it to connect to databases over the network.  I only connect to my local databases once every month or two, and it worked fine before, so I'm assuming this is due to some automatic update but I don't know which.  Mac OS upgraded itself to High Sierra (10.13) recently.

The same error message popped up on here in 2014 (link) but that was a case where a guy had trouble starting the server.  In my case I believe it's running -- I've used brew services start postgresql and yes, I've restarted a few times today.  However, the file mentioned in the error, /tmp/.s.PGSQL.5432, does not exist on my system.  I think this is a case of some configuration mistake.  Anyone have a clue how to fix?

Update: I tried starting the server manually (as recommended by Evan) and I think I made a breakthrough. Here's the error I get when I try to start it manually:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.1.

Update 2: Ultimately I solved this problem by replacing my data directory and re-initializing it with an empty database using initdb as in the answer marked "correct".  To be fair, all three answers were helpful.  I was unable to update the database from the old version to the new one because I no longer had the binaries for the older version of Postgres.

If you don't have any data, then it seems like a problem with Brew. Did you run brew upgrade, there are two answers on StackOverflow that address this, I've merged both of them together to try the best of both worlds.

Backing up

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/.postgres_bak;

Making sure you have the latest version

brew update
brew upgrade postgresql

Either, not both reinitialize or try to upgrade your data-dir

Nuke it (start from scratch, leaving the old back up)

initdb /usr/local/var/postgres -E utf8

Try to upgrade it

pg_upgrade -b /usr/local/Cellar/postgresql/$myOldVersion/bin/ -B /usr/local/Cellar/postgresql/$myNewVersion/bin -d /usr/local/var/.postgres_bak -D /usr/local/var/postgres

Restart server.

brew services start postgresql

Delete the backup, if the server restarts and everything works remove the backup

sudo rm -rf /usr/local/var/.postgres_bak


Removing duplicates from a query with conditions in Access 2007 I have a table of transactions with 880,095 records that I am querying.

There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table.

Eg 1.

Transaction ID 001 is has two records
The data in both rows is identical except for Interest Rate.
Row 1 has interest rate of 5%
Row 2 has interest rate has no rate and value in the cell is 0

I would like all duplicate Transaction ID's with no interest rate to be removed. This is simple enough, the complication is the following.

Eg 2.

Transaction ID 002 has 4 records
The data in all 4 rows is identical
The interest rate in all rows is 0, ie there is no interest rate.
I would like the query to return any 1 of these rows, and remove the other 3.

In summary, I need help designing an MS Access query where duplicate transaction Id's are removed where:

Transaction ID has multiple rows, remove the row which  has a 0 Interest Rate value
Transaction ID has multiple rows, with all Interest Rates values as 0. Remove all duplicates leaving just one row.

Please let me know if more explanation is needed.

If all the "duplicate" rows differ only at the InterestRate and you never have more than one row with rate over 0, and (one more assumption) there is no negative interest rate, then you can simply:

SELECT
    TransactionID, MAX(InterestRate) AS InterestRate,
    col1, col2, ..., colN               -- all the other columns

FROM
    tableX AS t
GROUP BY
    TransactionID,
    col1, col2, ..., colN ;             -- all the other columns