SQL Server 2019 setting Force Encryption with a script I have to update 50+ servers to use encrypted connection strings. Is there a way to script setting Force Encryption and TrustServerCertificate (e.g Powershell, or ???) so that I don't have to open Configuration Manager and set on each box?

Yes use dbatools in particular the command Enable-DbaForceNetworkEncryption


Can't connect psql on macOS Install

$ brew install postgresql
Updating Homebrew...
==> Downloading https://homebrew.bintray.com/bottles/postgresql-12.1.mojave.bottle.tar.gz
Already downloaded: /Users/user/Library/Caches/Homebrew/downloads/525a3637cd6f7b7a7e0bfe5adb102483ae01f4be31269aaf4348f6f4291f8a86--postgresql-12.1.mojave.bottle.tar.gz
==> Pouring postgresql-12.1.mojave.bottle.tar.gz
==> /usr/local/Cellar/postgresql/12.1/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgres
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Summary
  /usr/local/Cellar/postgresql/12.1: 3,217 files, 37.3MB

Check status

$ brew services list
postgresql     started user /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Connecgt

$ psql
psql: error: could not connect to server: FATAL:  database "user" does not exist

$ psql -U postgres
psql: error: could not connect to server: FATAL:  database "postgres" does not exist

It already inited database, why postgres does not exist?

This guide works very well:

https://gist.github.com/ibraheem4/ce5ccd3e4d7a65589ce84f2a3b7c23a3

It's necessary to create db and role manually since they aren't exist.


I you don't care about the time in the result it's pretty easy:

select article_id, sum(amount)
from stock_history
where amount_at <= '2016-03-01 00:00:00'
group by article_id

If you want time, you need to determine which time you should present. If you want the same time as in the where clause you can just add it since it is a constant:

select article_id, '2016-03-01 00:00:00', sum(amount)
from stock_history
where amount_at <= '2016-03-01 00:00:00'
group by article_id

Assuming it is the last time before this time you can map all times to this time with a derived table and use that:

select a.article_id, b.amount_at, sum(a.amount)
from stock_history a
join ( select article_id, max(amount_at) as amount_at
       from stock_history
       where amount_at <= '2016-03-01 00:00:00'
       group by article_id
) as b
    on a.rticle_id = b.article_id
   and a.amount_at <= b.amount_at
group by a.article_id, b.amount_at;


Confused Logins and Users in SQL Server In SQL Server, when setting permissions with a Grant statement, we do it on Users, not Logins. For example:

CREATE LOGIN login1 WITH PASSWORD = 'sssssssss';
use mydb;
CREATE USER user1 FOR LOGIN login1
grant delete on table1 to user1

So, users may or may not be able to do a certain task on a certain table.
But, when connecting to a database from JDBC and etc, we give the login name and the password (login1 and 'sssssssss' in here) in the connection string, not the User name!

So where do these grant statements show themselves?! We never mentioned User1 in the code! And, if we define two users for same login, each with different permissions, and then connect to database from JDBC, which of these user permissions are considered?

A SQL Server login is used to authenticate the principal at the server level, and to provide server level permissions.

A database user maps to zero or one login and is used to provide a security context within the database.  Database permissions are assigned to database principals, including roles, not directly to logins.

User names are generally not specified in connection strings.  The exception is when a user is authenticated at the database level, and therefore has no login.


How to get the name of the database a stored procedure is executed in within that stored procedure while it's executing? Weird question?...maybe, but I have a need. :)

I have a stored procedure that I want to use universally in any database.
The stored procedure generates some dynamic SQL and then executes that SQL in a database that is passed in as one of the parameters in this procedure.

BUT I want to make the database parameter optional and when no database name is passed in, I want the dynamic SQL to execute within the same database that the procedure itself was called from. (Please keep in mind this procedure could be executed across databases and not within the same database that the procedure itself lives in.)

You can easily tell the dynamic SQL execute in a specific database by dynamically building a [database].sys.sp_executesql command:

USE your_database;
GO

CREATE PROCEDURE dbo.DatabaseNameOptional
    @db sysname = NULL
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(); /* other stuff */'

  DECLARE @exec nvarchar(770) = COALESCE(@db, DB_NAME())
    + N'.sys.sp_executesql';

  -- alternatively, just leave DB_NAME() out of it:

  --DECLARE @exec nvarchar(770) = COALESCE(@db, N'')
  --  + N'sys.sp_executesql';

  EXEC @exec @sql;
END
GO

Try it out:

USE your_database;
GO

EXEC dbo.DatabaseNameOptional;
GO  -- output = your_database

EXEC dbo.DatabaseNameOptional @db = N'master';
GO  -- output = master

USE tempdb;
GO

EXEC your_database.dbo.DatabaseNameOptional;
GO  -- output = your_database

EXEC your_database.dbo.DatabaseNameOptional @db = N'master';
GO  -- output = master

In the execution context of the procedure, though, no, I don't think there's any way to determine where the call originated from (or to run in that context). That's the benefit of using a system-marked procedure in master - if that's the functionality you want, you need to decide if "putting objects in master" is ickier than "not getting what I want."


Case of irrecoverable error in transaction According to an exam question,which may lead to an irrecoverable error:

1.A transaction writes a data item after it is read by an uncommitted transaction.
2.A transaction  a data item after it is read by an uncommitted transaction.
3.A transaction reads a data item after it is written by a committed transaction.
4.A transaction reads a data item after it is written by an uncommitted transaction.

The answer is given as the 4th option.I was thinking that the 3rd option should be the answer because the transaction has been committed so nothing can be done while rollback can be used in 4th.It depends on the transaction which is reading.

Case 3 is the normal case for most reads in the database - think about it, the only data you're supposed to find in a database is committed data.

Case 4 is a problem because the transaction A (the first one) is reading data written by transaction B (the second), but B has not committed yet. If A acts on that data (and eventually commits), but B rolls back (or does another update on that data before committing), A will have "acted on" data that was never committed, i.e. never existed.

Scenario: Alice has $300 in her checking account, Bob has $250, negative balances are not allowed.

Transaction A: transfer $700 from Alice to Peter
Transaction B: transfer $500 from Bob to Alice

TB starts, update's Alice's balance with +$500 (then goes off to sleep). TA starts.

In case 3, TA only reads committed data. So it sees Alice's starting balance of $300, and will roll back when it realizes that Alice's account would be overdrawn. All good. You can run each step of those transactions in the order you want if you read only committed data, neither transfer will post.

In case 4, TA would read the uncommitted balance $800 from TB, do the transfer and commit - it believes Alice has the funds. System state is corrupt at that point. TB can't commit without breaking the "no negative balances allowed" rules. It can't rollback without also breaking that rule.


Primary Index VS Unique Index Performance I have the following table:

CREATE TABLE `actions` (
  `user_id_from` int(11) unsigned NOT NULL,
  `user_id_to` int(11) unsigned NOT NULL,
  `action_type` int(11) NOT NULL,
  `counter` smallint(11) unsigned NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`user_id_from`,`user_id_to`,`action_type`),
  KEY `user_id_to` (`user_id_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Currently it has around 1.3 Billion Rows, Data size is 109GB and Index Size is 44.5GB.

The INSERT queries on this table come in chunks of ~1k rows all with the same user_id_to. Most of the SELECTS have user_id_to in the WHERE condition, user_id_from is also used but less frequently, and results are mostly sorted by counter DESC and grouped by user_id_from.

Currently both INSERTs and SELECTs are running much slower than I'd expect. I have a theory a possible improvement will be to replace between the two indexes and making the new indexes:

  PRIMARY KEY (,`user_id_to`, `user_id_from`,`action_type`),
  KEY `user_id_from` (`user_id_from`)

The hope is that all writes will happen in the same area on the disk, and index changes therefore will also be minimal, and same thing for fetching the data when having one specific user_id_to as a WHERE condition, all data will be in the same place on disk, so it will run much faster.

Does my theory hold water? Any other suggestion?

UPDATE: My Queries:

SELECT COUNT( DISTINCT user_id_from) FROM actions WHERE user_id_to=:user_id

SELECT locations_users.*, location_country, countryCode, location_state, location_city
                    FROM actions INNER JOIN locations_users ON actions.user_id_from = locations_users.user_id
                        INNER JOIN countries ON locations_users.countryId = countries.countryId
                        LEFT JOIN states ON locations_users.stateId = states.stateId
                        LEFT JOIN cities ON locations_users.cityId = cities.cityId
                    WHERE actions.user_id_to = :user_id

SELECT actions.counter, actions.user_id_from, actions.action_type
            FROM actions
            LEFT JOIN users ON actions.user_id_from=users.user_id
            WHERE actions.user_id_to=:user_id AND users.something>50
            ORDER BY actions.counter DESC LIMIT 40

Indexes with the columns in the order given:

actions: INDEX(user_id_to, user_id_from) -- 'covers' first query
actions: INDEX(user_id_to, counter) -- may subsume ORDER BY and LIMIT in 3nd query

Either index will be useful for 2nd query.  DROP KEY user_id_to (user_id_to) as redundant when you add my two suggestions.

For various reasons, it may be better to change to PRIMARY KEY(user_id_to, user_id_from, action_type).  This would

replace the first index suggested above
mostly eliminate the need for the second index above, thereby speeding up inserts (by having fewer indexes)
If you need user_id_from in the WHERE, but without user_id_to, then you would need some index starting with user_id_from.  (I don't yet see a SELECT that begs for such.)

In summary (for actions indexes):

PRIMARY KEY(user_id_to, user_id_from, action_type)
INDEX(user_id_from, ...) -- if needed.

"action_type" -- Do you really need 4 bytes for it?  How many distinct values are there?  If very few, consider an ENUM, otherwise a TINYINT UNSIGNED or SMALLINT UNSIGNED (1,1,2 bytes, respectively).  Shrinking 4 bytes to 1 would save 8GB (3 bytes * 2 copies (data+secondary key) * 1.3B rows).

There is a perfectly nice 2-byte countryCode; don't use a separate Id:

countryCode CHAR(2) CHARACTER SET ascii

How many "locations" are there?  If not millions, I would not bother normalizing city, state, country.  If there are a lot of locations rows in each city, then consider normalizing only city, then have the state and country spelled out in the city table.  (I oppose "over-normalizing".)

If users has PRIMARY KEY(user_id), I have no further suggestion for indexing that table.

The 3rd query says LEFT JOIN users and users.something....  You probably did not want LEFT.


Several stays: many-to-many relationship Could you please help me with the following problem? I'm a total novice in DB design.

I have a table with researchers who visit our university. They come for some period of time (from, till). Now, how can I save in the database if somebody came several times? I have an entry already for a particular person and need to add some other stays and be able to see them in a form.

My idea was: this must be a many-to-many relationship. Ok. Then I need a third table Researcher/Stay. And then I don't know what to do next. In Stay-table "from+till" is a key. I haven't seen examples with double-value key in a many-to-many relationship and don't know what to do next.

Based on your diagram above what you have is a one to many, not a many to many relationship.  You have your Researcher table and your Stay table.  If you add the researcher id in the stay table to the primary key you have a relationship of:

Researcher.ResearcherId -> Stay.ResearcherId

Where a given researcher can have multiple stays.  For example

Researcher John Smith has an Id of 2.

In the stay table you would have

From       Till       ResearcherId
1/1/2013   1/5/2013   2
2/6/2013   3/1/2013   2

etc


Does MySQL store point datatypes as LAT LNG or LNG LAT? I'm used to seeing location format as latitude followed by longitude, but using libraries, I believe I understand MySQL to store it as POINT(LNG LAT), the reverse order. Is my library in the wrong, or is this the actual format? I can't seem to find this detail in the MySQL documentation.

Constructor Syntax

Looking up the spatial function reference, you'll see

Point(x, y)
Constructs a Point using its coordinates

This isn't entirely correct. All GIS implementations must do (x,y) for projected coordinates which is  (long,lat). But, on geodidic cordinate systems there is some disagreement about what to do. MySQL (and SQL Server) do (lat,long) but PostGIS maintains (long,lat) everywhere.

This is touched on in the spec OpenGIS®
Implementation Standard for Geographic
information - Simple feature access - Part 2: SQL option,

  For geodetic CRSs having an ellipsoidal 2-D coordinate system, the two-dimensional ellipsoidal coordinate system axes are geodetic latitude, positive northwards, and geodetic longitude, positive eastwards. Axis direction shall be ‘north’ and ‘east’ respectively.
  For geodetic CRSs having a three-dimensional ellipsoidal coordinate system, the name and abbreviation of the horizontal axes in a WKT string shall follow the requirements in (ii). The vertical axis name shall be ‘ellipsoidal height’; the vertical axis abbreviation shall be ‘h’ and should be included when abbreviations for the horizontal axes are included.

Note the above words are found verbatim in Geographic information - Well-known text representation of coordinate reference systems

Even as far back as the 1.1 spec,

  A spatial reference system, also referred to as a coordinate system, is a geographic (latitude-longitude), a projected (X,Y), or a geocentric (X,Y,Z) coordinate system.

That said, it seems PostGIS and Oracle and many third party libraries maintain (x,y,[z]) for all types of points. This is in violation of the spec for WKT, but is a pretty common convention. For example GeoJSON also does this,

  Point coordinates are in x, y order (easting, northing for projected
     coordinates, longitude, and latitude for geographic coordinates)

And, (lat,long) ordering is explicitly defined by EPSG for SRSID 4326,.

  A 2D or 3D coordinate system in which position is specified by geodetic latitude, geodetic longitude and (in the three-dimensional case) ellipsoidal height, used in Geographic CRSs.

You may also consider checking out Paul Ramsey's (Captain PostGIS) blog on this subject entitled, "Let's Call the Whole Thing Off"

Nicities

As a special note, MySQL brings two new functions to the mix,

ST_Longitude()
ST_Latitude()

These functions stand aside from ST_X(), and ST_Y() and require a geographic SRS or they throw an exception and error.

Storage

From the docs

  Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. (Internal format is like WKB but with an initial 4 bytes to indicate the SRID.)

Storing is defined here.

Table 11.1 WKB Components Example

Component       Size    Value
Byte order      1 byte  01
WKB type        4 bytes 01000000
X coordinate    8 bytes 000000000000F03F
Y coordinate    8 bytes 000000000000F0BF


SQL Joins : How can i get latest record from 2nd table (one to many relationship) I have two tables

1.Product  table having columns

prod_code
alert_limit
prod_description

2.Daily_data table having columns

prod_code
used_today
date
stock_left

How should i join these two tables to get recent records of each product on the basis of date ?
I have tried

SELECT p.*, d.*
FROM product p
JOIN daily_data d ON (p.product_code = d.prod_code)
LEFT OUTER JOIN daily_data d2 ON (p.product_code = d.prod_code AND
(d.date < d2.date OR d.date = d2.date AND d.prod_code < d2.prod_code))
WHERE d2.prod_code IS NULL;

but the above is giving me one recent row of the last product added

row_number()

select * from
(
select *
     , row_number() over (partition by table2.prod_code order by table2.date desc) as rn
from table1
join table2
  on table1.prod_code = table2.prod_code
) tt
where rn = 1


How to verify that postgres 9.3 db has no corruption? I have a postgres 9.3 db running on ubuntu server.
About a month ago we've had some problems with the hardware on the server which was resolved by our VPS hosting company.

The problem was resolved fairly quickly and everything seemed to be working fine.
We have backups running using barman that is on a different server - and backups and restores were working (I checked).

The first sign of data corruption was few days ago: I decided to do a full pg_dump on our DB as I do every once in a while, and it failed (invalid page header in block...) - The data seemed to have been corrupted a long time ago - around the time of the hardware problem (that was the date on the corrupted record). I resorted to finding the corrupted record and I deleted it and restored it manually.

After that I was able to do a full pg_dump.

In order to check for additional corruption - I've set up different db server from a backup and run pg_repack on all the tables to verify that I'm able to rebuild all the indexes and tables.

My questions are:
1. How can I be certain that I don't have any additional corruption in my DB?
   2. What can I do to periodically check my data integrity?
3. What else can I do to verify the integrity of our DB besides dumping the whole DB and re-indexing it (which I already did)?

P.S - I don't have block checksums enabled.

If you have initialized your cluster with checksum, you can control this while backuping with pg_backrest.

  PostgreSQL has supported page-level checksums since 9.3. If page checksums are enabled pgBackRest will validate the checksums for every file that is copied during a backup.

You will find more information about cheksums here: https://www.postgresql.org/docs/current/static/app-initdb.html#APP-INITDB-DATA-CHECKSUMS

You can also make a pg_dump to /dev/null (if you don't have enough space to keep a logical dump). This should ensure you that you can read each data block.

PGDG had a discussion this year beacuse some wanted to activate checksum by default. You can find it there: http://www.postgresql-archive.org/Checksums-by-default-td5940158.html


MySQL trigger and values not supplied in update query there is a way in mysql / mariadb triggers to know if a value is present  (set) in the update query?

For example:

    create table MyTest (
    col1 varchar(45),
    col2 varchar(45)
);

insert into MyTest values ( 'val1','val2');

So when I execute

update MyTest set col1 = 'val1.1'

in the trigger I would like to know that col2 is not set.

Thank You

UPDATE (I changed also table schema for better explain my problem)

I have setup a trigger like this

CREATE DEFINER=`root`@`localhost` TRIGGER `MyTest_BEFORE_UPDATE` BEFORE UPDATE ON `MyTest` FOR EACH ROW
BEGIN
    IF (NEW.col2 <=> OLD.col2) THEN
        SET NEW.col2 = 'triggered';
    END IF;
END

If I call

UPDATE MyTest set col1 = 'val1.3'

table will be (this is ok)

+------+---------+
|col1  | col2    |
+------+---------+
|val1.3|triggered|
+------+---------+

but if i call

UPDATE MyTest SET col1='val1.4',col2='val2.4'

first time table is ok

+------+---------+
|col1  | col2    |
+------+---------+
|val1.4|val2.4   |
+------+---------+

but a second update like this

UPDATE MyTest SET col1='val1.5',col2='2.4'

table will be:

+------+---------+
|col1  | col2    |
+------+---------+
|val1.5|triggered|
+------+---------+

instead i would like it should be

+------+---------+
|col1  | col2    |
+------+---------+
|val1.5|val2.4   |
+------+---------+

There is some way  i can check this?

Thanks

The Boolean expression

NEW.col2 <=> OLD.col2

can be used in an IF statement (or elsewhere).  Notes:

It tests for whether col2 is changing, it does not test whether you specified col2 without changing it.
NULL is properly handled because of the <=> operator.

UPDATE (based on further details)  See if this comes closer:

IF ( NEW.col1 <=> OLD.col1  -- testing only col1 for changes
 AND NEW.col2 IS NULL)      -- avoiding clobbering col2
    NEW.col2 = 'triggered';

(You haven't yet specified enough use cases to provide a complete solution, but maybe I have given you enough building blocks?)


Update value equal to the count of not appearance on the last X records I have a MySQL table where a number between 0 and 99 is inserted every now an then. Every time a new value is inserted I need to count how many numbers between 0 and 99 does not appears in the last 100 previous records and update not_in_last_100_repetitions.

You can assume the structure of my table is:

num (int, primary, auto-increment) | winner (varchar) | not_in_last_100_repetitions (int)

Study the example below:

mysql> create table test(id serial primary key, val int, cnt int);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into test (val)
    -> select 10*rand()
    -> from (
    -> select 1 union all
    -> select 2 union all
    -> select 3 union all
    -> select 4 union all
    -> select 5 union all
    -> select 6 union all
    -> select 7 union all
    -> select 8 union all
    -> select 9 union all
    -> select 10
    -> ) dummy;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
+----+------+------+
10 rows in set (0.00 sec)

mysql> create trigger cnt_absent_in_last_10 before insert on test
    -> for each row
    -> set new.cnt = (select 10 - count(distinct val) from (select * from test order by id desc limit 10) dummy);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test (val)
    -> select 10*rand()
    -> from (
    -> select 1 union all
    -> select 2 union all
    -> select 3
    -> ) dummy;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+------+
| id | val  | cnt  |
+----+------+------+
|  1 |    2 | NULL |
|  2 |    7 | NULL |
|  3 |    6 | NULL |
|  4 |    0 | NULL |
|  5 |    2 | NULL |
|  6 |    2 | NULL |
|  7 |    1 | NULL |
|  8 |    1 | NULL |
|  9 |    3 | NULL |
| 10 |    9 | NULL |
| 16 |    8 |    3 | -- absent in 1..10: 4,5,8
| 17 |    4 |    2 | -- absent in 2..16: 4,5
| 18 |    6 |    2 | -- absent in 3..17: 5,7
+----+------+------+
13 rows in set (0.00 sec)

mysql>


Merge Replication from SQL 2008 R2 to SQL 2005 Express: SQL Server version (10.50) is not supported I'm setting up a (one way) pull, RMO merge replication via web sync from my main database (SQL Server 2008 R2) and clients running SQL Server 2005 Express or 2008 Express (I must support both). I've set up the publication to support SQL 2005. When syncing to SQL 2008 Express, all is fine.

The problems arises when I use SQL 2005 Express. The error occurs on the first attempt at connecting. Sorry about the Norwegian text in the error message:

System.ApplicationException: [My custom text] --->  Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server test-db01. ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: This SQL Server version (10.50) is not supported.
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.CheckServerVersion(ServerVersion version)
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   --- Slutten på sporingen av intern unntaksstakk ---
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
   ved Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
   ved Microsoft.SqlServer.Management.Common.ServerConnection.get_TrueName()
   ved Microsoft.SqlServer.Replication.ReplicationObject.CommonConstructor(ServerConnection connectionContext)
   ved Microsoft.SqlServer.Replication.ReplicationObject.set_ConnectionContext(ServerConnection value)
   ved Farry.FarryClients.FarryClientServices.FarryOfflineComponents.DbSync.MergeReplication.DropSubscription() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryOfflineComponents\DbSync\MergeReplication.cs:linje 83
   --- Slutten på sporingen av intern unntaksstakk ---
   ved Farry.FarryClients.FarryClientServices.FarryOfflineComponents.DbSync.MergeReplication.DropSubscription() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryOfflineComponents\DbSync\MergeReplication.cs:linje 129
   ved Farry.FarryClients.FarryClientServices.FarryNetClient.Offline.DbSyncManager.AddSubscription() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryNetClient\Offline\DbSyncManager.cs:linje 142
   ved Farry.FarryClients.FarryClientServices.FarryNetClient.Offline.DbSyncManager.Work() i C:\Farry\Farry\FarryClients\FarryClientServices\FarryNetClient\Offline\DbSyncManager.cs:linje 181

As the publication is set up to support 2005, what must I remember (which I probably haven't) to make syncing to 2005 work?

Try to change setting,

Step 1: Launch the DFS Management Console (on the cluster node ‘PrimaryNode’).

Step 2: Click on the ‘New Replication Group…’ action.

Step 3: Select the type of replication group.

Step 4: Select the name and domain for the replication group.

Step 5: Specify the branch office file server (replication member)

Step 6: Select the folders to replicate from the branch office server.

Step 7: Specify the hub server (other replication member)

Step 8: Specify the path to the replicated folder on the hub server.

Step 9: Configure the replication schedule and bandwidth utilization.

then you apply and hope your problem will be solve.

http://www.techyv.com/questions/2008-r-2-replication-errors


Postgres Query to retrieve locations within a given distance I have the following sql query that works fine in MYSQL but it gives me an error in Postgres:

SELECT * ,
        ( 3959 * acos( cos( radians('13.1') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('53.1') ) + sin( radians('13.1') ) * sin(radians(lat)) ) ) AS distance
FROM person
HAVING distance < 10
ORDER BY distance LIMIT 20

**ERROR**:  column "distance" does not exist
LINE 1: ...(radians(lat)) ) ) AS distance FROM person HAVING distance <...

You can repeat the expression in the filter condition, or you can avoid that by using a subquery:

SELECT *
FROM (SELECT *,
             3959 * acos(
                       cos(
                          radians('13.1')
                       ) * cos(
                          radians(lat)
                       ) * cos(
                          radians(lng) - radians('53.1')
                       ) + sin(
                          radians('13.1')
                       ) * sin(radians(lat))
                    ) AS distance
      FROM person) AS q
WHERE distance < 10
ORDER BY distance LIMIT 20;


Create table statement in MYSQL I am trying to create the following table in MYSQL and is giving errors,

CREATE TABLE CUSTOMER (
CUS_ID INT NOT NULL AUTO_INCREMENT,
CUS_NAME VARCHAR,
CUS_DOB DATE,
CUS_ADDR VARCHAR,
CUS_EMAIL VARCHAR,
CUS_TEL VARCHAR,
CUS_PW VARCHAR,
CUS_JOINDATE DATETIME,
CUS_LASTACCESS DATE)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' CUS_DOB DATE, CUS_ADDR VARCHAR, CUS_EMAIL VARCHAR, CUS_TEL VARCHAR, CUS_PW' at line 3

There are two things wrong with it.

Firstly, you need to specify maximum data lengths for the VARCHAR columns.

Secondly, the AUTO_INCREMENT column has to be the PRIMARY KEY.

The following DDL statement will work:

CREATE TABLE CUSTOMER (
  CUS_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CUS_NAME VARCHAR(32),
  CUS_DOB DATE,
  CUS_ADDR VARCHAR(128),
  CUS_EMAIL VARCHAR(64),
  CUS_TEL VARCHAR(16),
  CUS_PW VARCHAR(32),
  CUS_JOINDATE DATETIME,
  CUS_LASTACCESS DATE
);

Obviously you'll need to change the VARCHAR types to reflect the sizes needed for your data.


How to design a database with one-to-many and then many-to-many relationship? I'm not very specialized with database design so much and I'm designing a database for the delivery company.

The situation is that we have two main actors, customer and truck driver. The customer will ask for a delivery and the truck driver must answer with an offer or a rate for that customer. The difficult part is, how to design a relationship between them, so the customer can request for a delivery and truck driver can give a good rate for that request. The customer should receive many offers from many drivers and drivers can receive many requests from other customers as well.

I thought about having Customer, Truck Driver, and Request as entities
and Request_Driver for the many-to-many relationship.

This is a many to many relationship. You will have a Customer table and a TruckDriver table.

You will also have a table called Delivery or similar which will manage the relationship between the two.

The delivery table would have the columns

CustomerID (foreign key, referencing the PK in the customer table)
DriverID (foreign key, referencing the PK in the driver table)
Rate

If one customer can recieve an offer from the same driver more than once, it may also be worth adding a column DeliveryID as the Primary Key on the table, otherwise you can use a composite key of CustomerID, DriverID

There may be other information you want to include in your delivery table such as date of offer and a flag as to whether the offer was accepted or not but that is not specified in the question.


JDBC pooling Oracle driver Socket read timed out I run Java EE application on Glassfish server v3 together with Oracle 12 DB on the same machine under Windows Server 2012 64bit. I use latest ojdbc7 driver.

Connection pool config:

<jdbc-connection-pool validation-table-name="DUAL" steady-pool-size="20" statement-cache-size="100" associate-with-thread="true" statement-timeout-in-seconds="30" idle-timeout-in-seconds="60" max-wait-time-in-millis="2000" validate-atmost-once-period-in-seconds="20" datasource-classname="oracle.jdbc.pool.OracleDataSource" pool-resize-quantity="5" max-pool-size="60" res-type="javax.sql.DataSource" name="dbPool" is-connection-validation-required="true">
  <property name="driverClass" value="oracle.jdbc.OracleDriver"></property>
  <property name="user" value="xxx"></property>
  <property name="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"></property>
  <property name="password" value="xxx"></property>
  <property name="portNumber" value="1521"></property>
  <property name="databaseName" value="orcl"></property>
  <property name="serverName" value="127.0.0.1"></property>
  <property name="oracle.jdbc.ReadTimeout" value="300000"></property>
  <property name="oracle.net.CONNECT_TIMEOUT" value="10000"></property>
</jdbc-connection-pool>

After 2 or 3 hours, when there is more than 1 user (3-5) using my application, it stops responding and I get this in glassfish logs

javax.enterprise.resource.resourceadapter.com.sun.enterprise.resource.allocator|_ThreadID=152;_ThreadName=Thread-2;|RAR5038:Unexpected exception while creating resource for pool dbPool. Exception : javax.resource.spi.ResourceAllocationException: Connection could not be allocated because: IO Error: Socket read timed out
[...]
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: IO Error: Socket read timed out

From the database side it looks like this

Fatal NI connect error 12560, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
  Time: 13-JUN-2014 03:14:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12560

TNS-12560: TNS:protocol adapter error
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (3404) as a result of ORA-609

When I just reset db listener everything works ok for next 1-2 hours (depends on application load). So temporary solution is to run bat script from windows scheduler to reset the listener every 1h.

I tried everything I could find - applied these parameters:

- Sqlnet.ora:
SQLNET.INBOUND_CONNECT_TIMEOUT=180
SQLNET.EXPIRE_TIME=5
- Listener.ora:
INBOUND_CONNECT_TIMEOUT_LISTENER_IPC=120

But still without success

Since the client is at the same server as the database you could try the IPC protocol instead of TCP as a workaround.
It is not only independent of the TCP traffic conditions but also MUCH quicker than TCP.

Your connection string would be something like

jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY = LISTENER)))(CONNECT_DATA=(SERVICE_NAME=orcl)))

As you are using connection pooling, your only verification should be dead connection (SQLNET.EXPIRE_TIME).
Oracle has several notes explaining it's functionality but it basically says it's deppendent of the TCP network layout:

Dead Connection Detection (DCD) Explained - Doc ID 151972.1.
Common Questions About Dead Connection Detection (DCD) - Doc ID 1018160.6

It could be a profile configuration problem (the user connected is not allowed to stay inactive for more than x seconds, the IDLE_TIME limit).
You could verify your profiles with:

set linesize 1000
set pagesize 1000
select *
from dba_profiles
order by profile;

Also get your network checked.


Restore DB from SQL Server 2008 R2 to 2014 Express I'm trying to restore a database backup made in SQL Server 2008 R2 to the 2014 Express version. Is this even possible? If I choose restore from device and select my backup file, I see no database in the drop down.

Yes, you can, as long as the database is < 10 GB and doesn't use any features not supported by Express (you didn't mention what edition was being used in 2008 R2).

Stop using the broken point-and-click UI, and instead use a proper RESTORE DATABASE command:

RESTORE DATABASE DBFrom2008 FROM DISK = 'C:\backups\2008backup.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'C:\...\database_name.mdf',
  MOVE 'log_file_name'  TO 'C:\...\database_name.ldf';

If you get errors from that (after replacing all of the obvious bits that are specific to your database and system), post those.


How to grant permission to view database audit logs to non-sysadmin login I have a non-sysadmin user which must be provided with an ability to view "server audit logs"

Tried to grant below permissions to this login / user:

grant ALTER ANY server AUDIT  to [login]
grant ALTER ANY database AUDIT  to [user]

But if he runs below, he is still getting error message:

select top 100 * from fn_get_audit_file(Path:\\...)

Msg 297, Level 16, State 1, Line 44
The user does not have permission to perform this action.

So, is there an ability to grant non-admin user permission to view audit logs ?

Thanks!

Reference:

sys.fn_get_audit_file (Transact-SQL)
View a SQL Server Audit Log

  Microsoft recommends viewing the audit log by using the Log File
  Viewer. However, if you are creating an automated monitoring system,
  the information in the audit file can be read directly by using the
  sys.fn_get_audit_file (Transact-SQL) function. Reading the file
  directly returns data in a slightly different (unprocessed) format.
  See sys.fn_get_audit_file for more information.

In order to use sys.fn_get_audit_file users requires the CONTROL SERVER permission.

You can deny privilege on a principle who has been assigned CONTROL SERVER permission which is not possible in case of SYSADMIN.

More about this:

Security Questions: Difference between sysadmin and CONTROL SERVER Permission by Jason Strate.


stack data into a single column SELECT * would result in:

course_id + group
-----------------
   1      + SE123
   1      + BA134
   2      + SE213
   3      + SE223
   3      + BA344
   4      + SE222

I would like it be like this:

course_id + group
---------------------
   1      + SE123/BA134
   2      + SE213
   3      + SE223/BA344
   4      + SE222

How can I do that?

For Oracle 11.2+ you can do this with LISTAGG. Here is a demonstration.

Setup:

DROP TABLE t1;
CREATE TABLE t1 (Course_Id Number(1), Group_Id Varchar2(20));

INSERT INTO t1 VALUES (1,'SE123');
INSERT INTO t1 VALUES (1,'BA134');
INSERT INTO t1 VALUES (2,'SE213');
INSERT INTO t1 VALUES (3,'SE223');
INSERT INTO t1 VALUES (3,'BA344');
INSERT INTO t1 VALUES (4,'SE222');

Query:

SELECT Course_ID, LISTAGG(Group_ID, '/') WITHIN GROUP (ORDER BY Group_ID DESC)
FROM t1 GROUP BY Course_ID;

Output:

COURSE_ID GROUPS
--------- --------------------
        1 SE123/BA134
        2 SE213
        3 SE223/BA344
        4 SE222

Here are some other string aggregation techniques for Oracle if you aren't on 11.2 yet.


INSERT gives Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column I am facing issues with my table structure :

My_Table_Name1

CREATE TABLE `My_Table_Name1` (
  `twitter_id_str` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `twitter_screen_name` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  UNIQUE KEY `twitter_id_str` (`twitter_id_str`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

For database I also have same charset and collation:

CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I'm trying to insert an emoji into this table:

insert into My_Table_Name1 values("2","")

However, I get an error:

  Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column 'twitter_screen_name' at row 1 0.00027 sec

How to solve this?

Thanks in advance.

The problem appears to be in the Client.

When the client connects to the MySQL server, it needs to announce that the bytes in the client are utf8mb4.  This can be done in several ways:

In the connection parameters (client-dependent).
SET NAMES utf8mb4.

\U+1F600 is the Unicode representation for that Emoji.
\xF0\x9F\x98\x80 is the equivalent Hex.
F09F9880 is the UTF-8 (utf8mb4) in Hex.

Avoid the Unicode representation.


Query to get count of inserted records hourly wise in postgresql? I have a table that looks like:
Table name Demo
id         creation_date
1          2021-03-06 12:27:48.811
2          2021-03-06 03:56:48.875
3          2021-03-06 03:58:23.567

I need the count of records hourly wise in PostgreSql.

Postgres can truncate a date to a certain precision:
SELECT DATE_TRUNC('hour', creation_date) FROM demo

So all you have to do is group and count it:
SELECT DATE_TRUNC('hour', creation_date), COUNT(*)
FROM demo
GROUP BY DATE_TRUNC('hour', creation_date)


Ambiguous column error on single table I have the following code that is generated by an unsupported app

SELECT ID, TYPE, ID FROM my_table t1 ORDER BY TYPE, ID

I can't change this, now it used to work fine on SQL2008R2 or below on DB's in compatability mode 80 but due to infrastructure/licensing changes we require to move to a more modern version of SQL which no longer allows for mode 80 and now generates this error:

  Msg 209, Level 16, State 1, Line 1
  Ambiguous column name 'ID'.

I'm aware it's because of the duplicated reference to the ID column but I can't alter what the app generates as TSQL, is there anything I can do at the DB\server level to have this execute without error?

PS: Yes we are working to replace this app

It's not precisely because there are duplicate entries in the SELECT list, it's because the ORDER BY mentions a column that is listed twice in the SELECT list, and SQL Server doesn't know which one you mean (think about the case where you could say SELECT ID, ID = 1-ID..., where which one you mean is important).

The right fix is (in order of my personal preference):

remove the second instance of ID
give the second instance of ID a different alias (like RedundantID)
use ordinal - ORDER BY 1 (would work, but definitely not recommended)

If you can't fix the code in the app, and can't get support from the vendor, I'm not sure there's much else you can do except move back to the copy of the database running on a version that can run in the ancient compatibility level required to support this syntax.


Will SSDT-BI work with SQL Server 2008 R2? Sorry if this is redundant, but due to the crazy naming of the tools, it's hard to find the answer to the question.

Question 1

Will SSIS packages, reports and so forth built with Microsoft SQL Server Data Tools - Business Intelligence (SSDT-BI) for Visual Studio 2013 work on SQL Server 2008 R2?

Question 2

I'm currently using SQL Server Business Intelligence Development Studio (BIDS) for Microsoft Visual Studio 2008. I want to potentially upgrade to Data Tools - Business Intelligence for Visual Studio 2013.

I assume I would need to A) purchase a new copy of Visual Studio 2013 and then B) download the free SSDT-BI software? That's assuming SSDT-BI for VS2013 works for 2008 R2.

Step A in Question 2 is not necessary.  The SSDT-BI package contains Visual Studio 2013 shell and will install it for you if it isn't there (see the additional information).

As for will it work?  Kinda.  You will be able to run packages which load data into SQL Server 2008 R2, but you will not be able to take advantage of the project deployment model released in SQL Server 2012.

The reason why I answered Kinda is that you may have some problems running new packages using dtexec on your old server.  Microsoft's guidance on this isn't much more than a stopsign... So YMMY (and please post back here with your experience!)


Converting outer apply with Correlated subquery in SQL Server to SQLite I need to convert a query in SQL Server to SQlite and I have replaced the outer apply to left outer join, but how to limit the subquery to return only 1 in this situation?  And how to pass the reference value from outer table into sub query?

I have highlight the things that i am not able to convert it into Sqlite, can anybody please give some advise ?

SQL Server:

SELECT  a.id,
        a.CodeValue ,
        a.TotalAmount ,
        InvoiceRevised.RevisedInvoiceId,
        InvoiceRevised.BillNumber AS RevisedInvoice,
        patientlookup.RegistrationNumber AS RegistrationNumber,
        patientlookup.PatientName AS PatientName,
FROM dbo.Invoices a
OUTER APPLY (
SELECT TOP 1 dbo.Invoices.Id AS RevisedInvoiceId,CodeValue AS InvoiceNumber,
    dbo.SCSInvoiceRels.CreatedOn AS InvoiceTime,
    dbo.Invoices.CreatedBy AS InvoiceCreatedBy,
    dbo.Invoices.TotalAmount,dbo.Invoices.Discount,
    dbo.Invoices.RoundAmount
    FROM Invoices
    INNER JOIN dbo.SCSInvoiceRels ON dbo.Invoices.Id = dbo.SCSInvoiceRels.InvoiceId
        AND dbo.SCSInvoiceRels.RecordStatus = 1 AND dbo.Invoices.RecordStatus = 1 AND  dbo.SCSInvoiceRels.SCDetailId IN (
            SELECT SCDetailId FROM dbo.SCSInvoiceRels WHERE InvoiceId = a.id)
) InvoiceRevised
OUTER APPLY (
SELECT TOP 1 pr.CodeValue AS RegistrationNumber,pa.Name AS PatientName FROM dbo.PatientRegs pr
INNER JOIN dbo.Patients pa ON pa.id = pr.PatientId
INNER JOIN dbo.SCDetails SC ON pr.id = SC.RegId
INNER JOIN dbo.SCSInvoiceRels SCb ON SC.id = SCb.SCDetailId
WHERE SCb.InvoiceId = a.Id
) patientlookup
WHERE RecordStatus = 0 AND  UpdateOn IS NOT NULL

SQLite:

SELECT  a.id,
        a.CodeValue ,
        a.TotalAmount ,
        InvoiceRevised.RevisedInvoiceId,
        InvoiceRevised.BillNumber AS RevisedInvoice,
        patientlookup.RegistrationNumber AS RegistrationNumber,
        patientlookup.PatientName AS PatientName,
FROM dbo.Invoices a
Left outer join (
SELECT dbo.Invoices.Id AS RevisedInvoiceId,CodeValue AS InvoiceNumber,
    dbo.SCSInvoiceRels.CreatedOn AS InvoiceTime,
    dbo.Invoices.CreatedBy AS InvoiceCreatedBy,
    dbo.Invoices.TotalAmount,dbo.Invoices.Discount,
    dbo.Invoices.RoundAmount
    FROM Invoices
    INNER JOIN dbo.SCSInvoiceRels ON dbo.Invoices.Id = dbo.SCSInvoiceRels.InvoiceId
        AND dbo.SCSInvoiceRels.RecordStatus = 1 AND dbo.Invoices.RecordStatus = 1 AND  dbo.SCSInvoiceRels.SCDetailId IN (
            SELECT SCDetailId FROM dbo.SCSInvoiceRels WHERE InvoiceId = a.id)
) InvoiceRevised
Left outer join(
SELECT pr.CodeValue AS RegistrationNumber,
       pa.Name AS PatientName,
       SCb.InvoiceId as InvoiceId
FROM dbo.PatientRegs pr
INNER JOIN dbo.Patients pa ON pa.id = pr.PatientId
INNER JOIN dbo.SCDetails SC ON pr.id = SC.RegId
INNER JOIN dbo.SCSInvoiceRels SCb ON SC.id = SCb.SCDetailId
) patientlookup on patientlookup.InvoiceId = a.Id
WHERE RecordStatus = 0 AND  UpdateOn IS NOT NULL

SQLite does not support lateral or correlated joins.  You can work around that using a join condition.  For example:

create table t1 (id int, name text);
create table t2 (id int, t1id int references t1(id), name text);
insert into t1 values (1, 'a'), (2, 'b'), (3, 'c');
insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1');

Now we'd like to look up at most one row from t2, sorted by name.  Here's one approach that will work in SQLite:

select  *
from    t1
left join
        t2
on      t2.id =
        (
        select  id
        from    t2
        where   t1.id = t2.t1id
        order by
                name desc
        limit   1
        )

Example at SQL Fiddle.


MySQL - using result of Procedure in Trigger to Insert in new table I'm guessing I'm doing something that's prohibited by MySQL, but can't see what.

Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to create a joining table to hold each of the ID's (e.g. item_id, item_type_id, item_group_id). At present there is no real relationship between the various lists. This table needs to be automatically populated each time I add a new item.

To get the matching entry, I need to use a CASE statement to match the first letters of the item_name, and get the relevant entry from the other table(s).

My plan is to have a set of Procedures (one for each table I need to join):

DELIMITER $$
CREATE PROCEDURE pr_get_item_type_id( item_name varchar(45) )
BEGIN
    SET @itemTypeID := (SELECT
            CASE
                WHEN LEFT(`item_name`,6)  = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = 'type1')
            #repeats multiple times . . .
            ELSE 0
            END AS `item_type_id`);
    SELECT @itemTypeID;
END;
$$
DELIMITER ;

This works fine.

I then have a trigger:

DELIMITER $$
CREATE TRIGGER tr_item_details
    AFTER INSERT
    ON `item` FOR EACH ROW
    BEGIN
        SET @itemId := new.`id`;
        CALL pr_get_item_type_id(new.`item_name`); #-->this Sets var @itemTypeID

        INSERT INTO `item_details` (`item_id`,`item_type_id`) VALUES (@itemID,@itemTypeID);
    END;
$$
DELIMITER ;

I then test this as follows:

INSERT INTO item (id,item_name) VALUES (1000,'abcdef')

But this results in:

Error Code: 1415. Not allowed to return a result set from a trigger

I tried setting the variable inside the trigger instead of the procedure:

SET @itemTypeID = (CALL pr_get_item_type_id(new.`item_name`));

But that didn't work either.

Is there a way to do this?

Example Tables:

CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `item_name` varchar(45) NOT NULL DEFAULT 'UNKNOWN', PRIMARY KEY (`id`));
CREATE TABLE `item_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `item_type_name` varchar(45) NOT NULL DEFAULT 'UNKNOWN', PRIMARY KEY (`id`));
CREATE TABLE `item_details` (`item_id` int(10) unsigned NOT NULL,`item_type_id` int(10) unsigned NOT NULL,`item_group_id` int(10) unsigned NOT NULL,PRIMARY KEY (`item_id`,`item_type_id`,`item_group_id`));
INSERT INTO `item_type` (`item_type_name`) VALUES ('type1'),('type2');

I believe I have found the solution, and that is to convert the Procedure to a Function.

DELIMITER $$
    CREATE FUNCTION fn_get_item_type_id( item_name varchar(45) )
    RETURNS INT DETERMINISTIC
        RETURN (SELECT
            CASE
                WHEN LEFT(`item_name`,6)  = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = 'type1')
            ELSE 0
            END AS `item_type_id`);
    $$
DELIMITER ;

From there I can simply Select it into a variable in the Trigger.


Force using an Index in Inner Join SELECT I have the following query:

select  field1, field2 from table1 t1 with (index(IndSearch))
inner join table2 t2 ON t2.Id = t1.Id
where t1.date between '04/01/2019' AND '05/01/2019'
AND t2.field3 IN(....) AND t2.field4 IN(...)

Table1 has clustered index in t1.Id
IndSearch is a nonclustered index with (t1.Id, t1.date and other fields)
Table2 has a nonclustered index with (t2.id, t2.field3, field4)
Table1 contains more than 5M rows
Table2 contains more than 11M rows

The problem is the query analyzer always chooses the index in Table2. That option is like 7 seconds slower because there are more rows in that table.

I would like to hint the query analyzer to use the index IndSearch, searching with the date field. I have done some tests and that search is faster.

I did try using with (index(IndSearch)), however that did not work.

I did try using left join instead inner join. That changed the execution plan.  In fact, that modification improved the response time, now the query analyzer first do an index scan over IndSearch.

I think that did solve my problem, however I am still curious if someone has any comments or suggestions. I will appreciate if someone can explain how to achieve a similar result using query or table hints.

You can take a look in the execution plans:

Inner join (7-8 seconds duration):
https://www.brentozar.com/pastetheplan/?id=BJl-Lg5jE

Left Join (almost instantaneous):
https://www.brentozar.com/pastetheplan/?id=ryya8gqoN

The goto solution to improve queries should not be query or table hints.

You should try other solutions like changing the query, adding or changing indexes first.

If the optimizer is still making mistakes after trying other workarounds then you might try adding a query / table hint.

Table variable estimates

The table variables have low estimates (one , two rows) while 850 rows or even 16.284 rows can be returned.
This is impacting the rest of the query.

Estimated plan on the inner join query:

Actual query plan on the inner join query:

Consider changing the table variables to temporary tables:

CREATE TABLE #lTemas(TemaId int);
INSERT INTO #lTemas (TemaId)
SELECT t.TemaId FROM Temas t
INNER JOIN @lClientes lc ON lc.n = t.ClienteId
AND nEstado > 0;

or adding OPTION(RECOMPILE) to the main query.

Indexing

Next to these awful estimates, the query gets filtered from 127.689 rows to 4.071 rows at this point:

This could be due to the predicate on dNota

** possible fix**

Consider adding an index on dNota first

CREATE INDEX  IX_dNota_ArtId
ON dbo.NotasHist(dNota,ArtId)
INCLUDE(bTipo,idDominio,iFuente);

and removing the with (index(Buscador)) hint to see if the optimizer will use the index with the better estimates.

The main difference with the left join

The filtering is happening much sooner on the left join:

The estimates are higher

And due to the higher estimates the query is going parallel

Whereas on the inner join you are not using parallelism.

This and other considerations make the difference in execution time happen, fixing the estimates by adding option recompile or changing the table variables to temp tables would be the first thing to look into.


How to design this kind of relations in a database I have a conceptual problem and I would like to get your ideas on how I'll be able to do what I am aiming.

My goal is to create a database with information of persons who work at a place depending on their profession and skills.

I have 3 categories which can have subcategories:

Outsourcing
Technician

welder
turner
assistant

Administrative

supervisor
manager

So each person has its information and the projects they are working on, also one person may do several jobs...

I was thinking about having 3 tables (PERSON, SKILLS, PROYECTS) but I guess there is a better way of doing this.

create table Person
(
    [Person_ID]  int(10),
    [Name]       varchar(30),
    [sex]        varchar(10),
    [address]    varchar(10),
    [profession] varchar(10),
    [Skills_ID]   int(10),
    [Proyect_ID]   int(10),
    [Salary]     float
)

create table Skills
(
    [Skills_ID]  int(10),
    [Person_ID]  int(10),
    [Skills_name] varchar(10),
    [Skills_pay]  float(10),
    [Comments]    varchar(50)
)

create table Proyects
(
    [Proyect_ID]     int(10),
    [Person_ID]      int(10),
    [Proyect_name]   varchar(10),
    [working_Hours]  float(10),
    [Comments]       varchar(50)
)

Is there a way to do this in a more effcient way?  What to add or delete of this small model? I guess I am missing something in the salary - maybe I need another table for that?

You should add more tables. Instead of Skills and projects being fields in Person, you should have PersonSkills (PersonID, SkillID) and PersonProjects (PersonID, ProjectID)

Also, Salary/Skills_Pay shouldn't be a float, and sex, probably not an int.


How to identify how many instances of Oracle are installed on Linux environment Last day, I was at one training for Oracle installation and service start & shutdown. There I came to know about some Linux commands to start and shutdown an Oracle instance on a Linux machine.

To identify the instance running : The command below will return all Oracle instances which are currently running on that machine.

ps -ef | grep pmon

This command returns following results:

oracle    823     1  0 Dec03 ?        00:00:17 ora_pmon_instance1
oracle  19632     1  0 Nov17 ?        00:06:35 ora_pmon_instance2
oracle  24199     1  0 Nov20 ?        00:05:23 ora_pmon_instance3

Where instance1, instance2 and instance3 are 3 installations of Oracle on Linux, which are currently running.

My query: So, ps -ef will only show processes currently running. Suppose, instance3 is down and you need to start that instance. But, you don't know how many Oracle instances there are on the machine.

How would you get to know that this instance is down?

In Windows, there is a way called services, where you could came to know that these installations are done on Windows for Oracle.

There's no fool-proof way, but here's a list of ideas for you:

/etc/oratab:

Little script to give you a list of SIDs in the oratab:

cat /etc/oratab | grep -v '^#\|^\s*$' | cut -d: -f 1

$ORACLE_HOME/dbs

You can adapt the above script to look in all $ORACLE_HOMEs listed in the oratab, and search for initSID.ora and spfiles for any instances:

for ORACLEHOME in `cat oratab | grep -v '^#\|^\s*$' | cut -d: -f 2 |uniq`
do
  ls -1 $ORACLEHOME/init*.ora | sed -n 's/init\(.*\).ora/\1/p'
  ls -1 $ORACLEHOME/spfile*.ora | sed -n 's/spfile\(.*\).ora/\1/p'
done

tnsnames.ora / listener.ora

Another option is to look in each $ORACLE_HOME/network/admin and analyse the tnsnames.ora and listener.ora files to see which instances have been configured.

Already Running Instances

You've already covered this with your pmon search. You can also use lsnrctl status to see which instances the listener is currently servicing requests for.


Could anyone explain this code to me？Leetcode #180 select distinct Num as ConsecutiveNums
from (
  select Num,
    case
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

I found this in
https://leetcode-cn.com/problems/consecutive-numbers/comments/
It is really difficult to understand, especially this sentence
(select @prev := null,@count := null) as t

Why can variable declaration be put there?

But it works in leetcode

Yes. This code is syntactically correct, so it does not produce errors. But it is not deterministic -- two executions of it can return different outputs whereas source data is the same. Forget this code. It is non-deterministic - i.e. it is wrong.
The code is processing order-dependent see online demo.  But ORDER BY clause is absent - so the query is wrong.


Logins aren't syncing across Availability Groups We have 2 servers in an AlwaysOn group.

While the user accounts within each synchronized database exist on both servers, the database instance level logins only exist on one of the servers. Ie DBINSTANCE->Security->Logins are missing on one server.

Therefore when there is a failover, I get login failures on the second server (which doesn't have the corresponding instance level logins).

How do I overcome this issue? Was I supposed to set up the user account in a special way?

My understanding is that if you aren't using Contained Databases, you will have to ensure logins are created on other instances manually.
Something like the script from SQLSoldier, originally posted on the article Transferring Logins to a Database Mirror, should do the trick.


Security or performance risks using SQL CLR Are there any particular security or performance risks in using the CLR in SQL Server ?

The question, as Remus pointed out, is too generic to get an answer as the answer depends on the context of what functionality is to be used and how will it be used.

Regarding "Security":

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = SAFE, then there aren't any issues that I have ever been able to find. And SQLCLR is "safer" than using xp_cmdshell or the wonderful (that was sarcasm) sp_OA* procs (or even Extended Stored Procedures, but hopefully nobody is creating any of those anymore).

If you would like a walk-through of what "SAFE" means in practical terms, please see this article: Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies) (free registration required).

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = EXTERNAL_ACCESS, then there are certainly risks, again, depending on what functionality is being used. If you write a routine to read directories and file names (i.e. read-only), then it is just a matter of what should be seen and not seen. If you are writing code that allows for deleting a file, the risk increases. But what can be done with those external resources is controlled by:

whether or not you are using Impersonation:

no Impersonation means accessing external resources is done via the "Log on as" account of the SQL Server service. Whatever that account has access to, your SQLCLR function will be able to do.
using Impersonation means that the Login in SQL Server that is running the function, if that Login corresponds to a Windows Login, can do whatever that particular Windows Login is allowed to do. If the Login in SQL Server is a SQL Server Login, then attempting to use Impersonation will get an error.

What permissions are set up on the external resource. For filesystem access, this is controlled through ACLs on NTFS drives.

If you are asking about anything that can be done in an assembly marked with PERMISSION_SET = UNSAFE, that is fairly open-ended. A lot of functionality is deemed only usable in UNSAFE assemblies because they are issues of stability and/or consistent behavior more than security or performance. For example, in an UNSAFE assembly it is possible to have a writable static variable. This is typically not a good thing to do since the SQLCLR classes are shared across all sessions. If your intention is to share data in memory across all sessions and have planned for race conditions (and done plenty of testing), then you should be fine as you are expecting this behavior. But if you simply wanted a writable static variable to cache a value for a particular session to not have to look it up again or calculate it again, and were not aware that other sessions are reading that value and possibly over-writing it, well, that would be an issue.

But if you are worried about someone writing to the Registry, but yet don't have any code that actually writes to the Registry, then you probably don't need to worry about this ;-).

If you would like a walk-through of how EXTERNAL_ACCESS and UNSAFE work in practical terms, and the difference between setting TRUSTWORTHY ON (not preferred) vs using an Asymmetric Key- or Certificate- based Login, please see this article: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (free registration required).

Regarding "Performance":

This is all a matter of what you are trying to do and how you go about doing it. There are some things that are much faster in SQLCLR, and some things that are slower. But just like with T-SQL, it is possible to take a somewhat simple and/or efficient task and make it complicated and/or inefficient by doing things incorrectly. But using SQL CLR is not inherently, by its very nature, slower.


Sub query to return same number of records and join i have this query

SELECT DISTINCT (a.AssetID) AS [AR BarCode],au.[Asset_barcode] AS [Audit BarCode],a.AssetRegisterID, a.Description AS [Asset Description],
      a.LocationRoomID,g.[RoomNo] AS [AR RoomBC],
      h.Description AS [AR Building]
FROM dbo.AssetRegister AS a LEFT OUTER JOIN
     dbo.LocationRoom AS g ON g.LocationRoomID = a.LocationRoomID LEFT OUTERJOIN
     dbo.Audit AS au ON au.Asset_barcode = a.AssetID LEFT OUTER JOIN
     dbo.Building AS h ON h.BuildingID = g.BuildingID
ORDER BY a.AssetRegisterID ASC, g.[RoomNo] ASC

Which works and return the 29771 records i need but when i add au.[Room_barcode] i get 37633 records which i dont know what is happening.

I want to compare AssetID's from 2 tables dbo.AssetRegister and dbo.Audit. Also the RoomBarcodes. Because Assets in the dbo.AssetRegister is the base data and after the audit process assets were found in different rooms.

Please help

Simplify the problem. Find a register / room combo that has multiple barcodes:

SELECT a.AssetID, g.RoomNo, MIN(au.Room_barcode), MAX(au.Room_barcode)
FROM dbo.AssetRegister AS a
  LEFT OUTER JOIN dbo.LocationRoom AS g
  ON g.LocationRoomID = a.LocationRoomID
  LEFT OUTER JOIN dbo.Audit AS au
  ON au.Asset_barcode = a.AssetID
GROUP BY a.AssetID, g.RoomNo
HAVING COUNT(*) > 1;

Then pick one of those AssetID / RoomNo combinations and look closer at the data:

SELECT a.AssetID, g.RoomNo, au.Room_barcode
FROM dbo.AssetRegister AS a
  LEFT OUTER JOIN dbo.LocationRoom AS g
  ON g.LocationRoomID = a.LocationRoomID
  AND g.RoomNo = <room number from above>
  LEFT OUTER JOIN dbo.Audit AS au
  ON au.Asset_barcode = a.AssetID
WHERE a.AssetID = <asset ID from above>;

Now, if you are getting multiple room barcodes back, you have to determine if that is valid or not (we can't tell you that, this is your data after all). If it is not valid, you need to fix the data. If it is valid, then you need to decide whether you want all the barcodes in the result or, if you just want one, which one? Build actual sample data from this single Asset/Room combo, add that data to your question, and explain what results you want from that sample data.

Don't try to reduce row counts by slapping DISTINCT in front of the column list, or increase row counts by changing inner joins to outer joins. Start simple and make sure you are collecting the right data rather than whether n row(s) affected looks right.


MSSQL Transactional Replication I have an application that almost continuously works with inserting or updating data. Since multiple requests are handled asynchronous I wrote my queries like below. I used an example based on SO, but that's not what I'm actually doing.

DECLARE @rows int;
INSERT INTO [user] ([username],[reputation])
SELECT [username],[reputation]
FROM (
    SELECT [username]=:user,[reputation]=:rep
) A
WHERE A.[username] NOT IN (
  SELECT [username]
  FROM [user]
);
SET @rows = @@ROWCOUNT;

IF (@rows=0) BEGIN
  UPDATE [user]
  SET [reputation]=:rep, [updated]=GetDate()
  WHERE [username]=:user
END;

This is passed in total to the database with PHP PDO. Because of the amount of data and other processing factors it's heavy on the (cheap) VPS it's running on. It's not really a problem if these processes run slow or get delayed, but on the other hand this data should be available via a website and then the queries on the data should be quick.

I was thinking about replicating parts of the processed data to a second server and running the website on that database. But I'm wondering how that would actually work with a query like above.

I'm guessing the UPDATE query will only be in the transaction log when @rows=0, so that won't be a problem.

But would the first part only send INSERT INTO [user] ([username],[reputation]) VALUES ('Hugo Delsing', '10k') or the entire query with the WHERE NOT IN () query?

Most of the time a user would exists, so if it only runs the new inserts that won't be a problem. But if it would run the entire query each time the benefits would be small.

Obviously I could wrap the first part up in another check if exists(select 1 from [user] where [username] = :user) to make sure it only runs when there is no user, but I'm wondering if that is necessary.

Also: Would replicating be the way to go or does MS SQL offer other/better solutions for something like this?

I wen through your post and here is my response (one-by-one) on the basis of what I got.

It's not really a problem if these processes run slow or get delayed

That's good if it suits you requirement.

But on the other hand this data should be available via a website and then the queries on the data should be quick

and

I was thinking about replicating parts of the processed data to a second server and running the website on that database.

In transactoinal replication SQL server allow to create index on the tables in subscriber database. So you want to use subscriber to generate reports, you can optimize you query and create indexes (on subscriber DB) that boosts your query performance to get quick response.

But if you want to make transaction at both side INSERT,UPDATE, DELETE then you should go for MERGE Replication.

I didn't understood rest part of you post. It would be good to write proper suggestion if you clarify it.

Thanks!


Archiving of old data combined with replication to data warehouse I have a setup that I'm sure is not unheard of (this is MySQL 5.5, but upgrading):

I have a fast growing OLTP database, that I am replicating to an OLAP database. I want to start archiving the fastest growing tables in the OLTP db, but I want the OLAP db to accumulate everything without the archiving part. Is there an easy way to achieve this, or so I have invent my own procedures?

Consider the following...

The Master is collecting "raw" data in a "Fact" table.  (I am using Data Wherehouse terminology; I hope the analogy works.)
The Fact table is not replicated.  But it is PARTITIONed by date for efficient archiving.  (We can discuss that separately.)  (If you need 'some' of the Fact table replicated, we can discuss that.)
On the Master, a few "Summary Tables" ("rollups") are built and maintained.
The Summary tables are replicated.  Querying against these will be much faster than against the Fact table.

DW discussion:  http://mysql.rjweb.org/doc.php/datawarehouse
Summary Tables:  http://mysql.rjweb.org/doc.php/summarytables
Time Series pruning:  http://mysql.rjweb.org/doc.php/partitionmaint

If you want to dig deeper into these ideas, please provide more details of the application and its data.


Select where bit is not 0 I have made the following query to select a record that isn't 0.

SELECT * FROM Table WHERE Bit != 1 OR Bit IS NULL;

Why is the check Bit != 1 not enough for a nullable BIT?

See following results:

'true' != 'true' → false
'false' != 'true' → true
NULL != 'true' → null (and equivalent to false in WHERE because it is not true)

What you can do is use COALESCE to change null into 'false'

SELECT * FROM Table WHERE COALESCE(Bool, 'false') != 'true';


How to insert/combine values from multiple tables? I have multiple tables (12 for each month) that have 3 columns each ("code", "test_name", "value")

Example:

January
code | test_name | value
0001   name1       17
0002   name2       45
0005   name5       12

February
code | test_name | value
0001   name1       3
0002   name2       7
0004   name4       13

March
code | test_name | value
0001   name1       6
0006   name6       32
0007   name7       41

How do I merge or insert or combine it in sql into one table that would look like this:

Year
code | test_name | January | February | March | ...
0001   name1       17        3          6
0002   name2       45        7          0
0003   name3       0         0          0
0004   name4       0         13         0
0005   name5       12        0          0
0006   name6       0         0          32
0007   name7       0         0          41

I wrote this for SQL 2012, but it should work in 2008R2 and up.

DECLARE @January AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @January
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 17 )
    ,   ( '0002', 'name2', 45 )
    ,   ( '0005', 'name5', 12 );

DECLARE @February AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @February
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 3 )
    ,   ( '0002', 'name2', 7 )
    ,   ( '0004', 'name4', 13 );

DECLARE @march AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @march
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 6 )
    ,   ( '0006', 'name6', 32 )
    ,   ( '0007', 'name7', 41 );

WITH CTE_AllMonths AS
    (
    SELECT Code, test_name, value, 'January' AS SourceMonth
    FROM @January
    UNION ALL
    SELECT Code, test_name, value, 'February' AS SourceMonth
    FROM @February
    UNION ALL
    SELECT Code, test_name, value, 'March' AS SourceMonth
    FROM @March
    )
    SELECT Code, test_name, January, February, March
    FROM CTE_AllMonths
        PIVOT (SUM(value) FOR SourceMonth IN ([January], [February], [March])) AS PT


Constraint many to many table between two child tables New to this exchange site so I hope my question isn't inappropriate.

Creating a somewhat normalized database I had one design problem I'm not sure how to handle efficiently.

One table has a One-To-Many relationship with two other tables. These two tables then have a Many-To-Many relationship between them. Shouldn't I somehow ensure (other then in the programming insertion) that the items that are linked together belong to the same parent table.

Let's put it in a Scenario (This is a fictional scenario):

Division table where each row is a division in a company. It has One-To-Many relations with a Employee table. So each employee belongs to one and only one division.

The Division table also has One-To-Many relations with a Project table where specific projects are logged. The project also has to register how many hours a employee has logged with this specific project. I would do this with a Many-To-Many table between Employee and Project and a Hours column.

This should all work fine ( I think at least) except that theoretically nothing in the database would ensure that the Employee and Project belong to the same Division even when they should and in fact both belong to a entity in that table.

What is a good way to ensure the intention of this schema and the integrity of the data is handled by some abstract relational database. Would you construct the database in any other way or would you put some constraint on the Many-To-Many relationship?

Why must the employees working on a project strictly belong to the same division that owns the project?  That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI.  You said you have a many-to-many table associating employees with projects.  Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID)
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID)
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.


How to determine if the error was a deadlock? The following transaction has been selected as a victim of a deadlock:

public void side2() throws SQLException, InterruptedException {
    TestConnectionPool connectionPool = new TestConnectionPool();
    try(Connection connection = connectionPool.get()){
        connection.setAutoCommit(false);
        try {
            PreparedStatement update1 = connection.prepareStatement("update t2 set j=j-1");
            update1.execute();
            Thread.sleep(3000L);
            PreparedStatement update2 = connection.prepareStatement("update t1 set j=j-1");
            update2.execute();
            connection.commit();
        }catch (SQLException ex){
            System.out.println("ex.getErrorCode()=" + ex.getErrorCode());
            System.out.println("ex.getSQLState()=" + ex.getSQLState());
            connection.rollback();
        }
    }
}

Its output is as follows:

            /*
     ex.getErrorCode()=60
     ex.getSQLState()=61000
             */

What should I use to determine whether the error is a deadlock: error code, SQL State, or both?

I guess the ex.getErroCode() has returned ORA-00060: deadlock detected while waiting for resource which is deadlock error. Add ORA in front of the returned error code then search online or use Oracle supplied tool oerr as demonstrated below to determine the error details.

  An error code. This is an integer value identifying the error that caused the SQLException instance to be thrown. Its value and meaning are implementation-specific and might be the actual error code returned by the underlying data source. Retrieve the error by calling the method SQLException.getErrorCode.More...

  [oracle@orcl trace]$ oerr ora 60

  00060, 00000, "deadlock detected while waiting for resource"

  Cause:  Transactions deadlocked one another while waiting for resources.

  Action: Look at the trace file to see the transactions and resources
           involved. Retry if necessary.


What can go wrong using the same sequence across multiple tables in postgres? We are considering using a shared sequence to assign ids to primary keys for all of the tables in our database. There are about 100 of them. Only a couple are inserted to frequently and regularly. We want to rule out it being "a terrible idea for an obvious reason" before we moved to the phase of actually trying it and testing it at load.

Our peak load is of the order of 1000 inserts a second, across a couple of tables.

Our research thus far indicates that
- sequence generation speed shouldn't be an issue
- sequence fragmentation (gaps) will happen, but shouldn't be an issue
- id exhaustion won't be an issue

We're not sure if we're missing other big things. We'd be grateful for people's opinions, especially from people who have tried it before and had either positive or negative experiences.

For context - we have two main motivations for doing this.

One motivation to do this is so that we can define a bunch of dictionaries (we call them scopes) and have human readable words assigned to those ids, so we want to make sure that ids in different tables never overlap. So, in one scope, id 12345 might be assigned the value "Green" and in another it might be assigned "Verde". (Actually, we don't use it for internationalisation, but we might one day).

The other motivation is to make it easy to have several deployments in the field and know (by uniquely setting each deployment's sequence couple of most significant digits) that our deployments won't overlap primary keys. (Like a GUID lite).

Three possible issues that spring to mind are:

With any shared resource you are creating a potential bottleneck. My gut says that for your peak load this should not be an issue but I strongly suggest benchmarking any such solution in a production-like production-sized environment to be sure.
You are essentially assigning meaning to surrogate keys which defeats part of their purpose in RDB theory. A surrogate key by its nature should not have meaning beyond being a key for identifying tuples in that relation. If the entities might have meaning together and so need collision free keys, is it correct that they are being modelled separately or has something been missed in the requirements and/or data model design?
You are introducing a potential point of failure. What if a deployment doesn't get its initial sequence starting point set? You then either have a deployment blocking error or deployments start from the same place "breaking" your feature. Also, what will you do if somewhere down the line someone thinks it is a good idea to branch a deployment (in production perhaps a tenant company divests part of itself and needs to separate out the data). What if the seed somehow gets reset by a bad upgrade deployment or other migration?[0]

If none of those issues concerns you then go ahead, the idea isn't going to break anything IMO. Of course there may be better ways even if this one isn't wrong in itself.

When you say "UUID-lite" you imply that you have already considered and discounted UUIDs. Is that the case, and if so are there particular reasons for deciding they are not suitable for this project?

One possible reason for not using UUIDs is index fragmentation though the significance of that is often greatly over-stated[1]. SQL Server's answer to this is the "sequential GUID" which is pretty much equivalent to what you are suggesting if we discount assigning meaning to key values - perhaps postgres has an equivalent to that? Of course always increasing indexes can have their own performance issues (last-page contention, index stats growing stale) in some very specific high volume workloads[2].

Another common argument against UUIDs is key length: why use 16 bytes per value when 4 or 8 will suffice? If the uniqueness is truly a useful property then this will usually trump key-size concerns significantly. If key-size is a concern but you are happy to use a 64-bit INT rather than needing to keep inside 32-bits you could use your technique without adding a potential shared-resource contention issue by doing your seeded-integer-key idea per table[3] using a normal INT IDENTITY(<start>, 1)[4] column definition, though again this is adding deployment complexity (a small amount, but certainly not zero).

Human readability is sometimes cited as a problem, but that goes back to assigning meaning to surrogate keys.

Compressibility is a less common concern but one you might come across. To just about any compression algorithm UUIDs are likely to look like random (therefor uncompressible) data unless you are using something like SQL server's sequential UUIDs. This might be a concern for a very large set of links (or other block of data) that contains many entity IDs served to an application over a slow network, or if needing to use something like SQL Server's index compression features, though both these matters are essentially just restating the key size concern in a slightly different way and sequential UUIDs may help here too.

[0] this could happen for normal identity columns too of course, but as you are using a less common feature you are increasing the chance of a less experienced DBA after you missing the problem if it happens once you are off doing something new and exciting elsewhere!

[1] I'm a SQL Server guy, I suspect the potential issue is the same in postgres but for all I know it may have a different index layout that can mitigate the effect.

[2] Though again these may be SQL Server specific especially the latter of the two examples I listed

[3] Top two bytes: vary by database, next two: vary by table, remaining four: the incrementing bits

[4] That is MS SQL Server syntax, postgres syntax may vary but you shoudl see what I mean and be able to translate

tl;dr: if you find yourself reinventing the wheel, make sure that all of the existing designs really aren't suitable before starting to considering why a new one might or might not be.


Transpose two-dimensional array from n rows to 2 columns Background

Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available.

Problem

Looking to simplify the code that pivots a two-dimensional array.

Code

A working, overly-verbose example that illustrates the problem is:

SELECT
  u.aspect,
  u.preference
FROM (
  SELECT
    t.aspect_preference AS aspect,
    -- Skip every second row
    seq % 2 AS seq,
    lead( aspect_preference, 1 ) OVER (ORDER BY t.seq) AS preference
  FROM (
    SELECT
      unnest( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] ) aspect_preference,
      -- Maintain array order after unnesting to a result set
      generate_series( 1,
       (array_ndims( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] ) *
        array_length( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[], 1 ) )
      ) seq
  ) t
  ORDER BY
    t.seq
) u
WHERE
  u.seq = 1

This produces the correct results with the unnest'ed array rows pivoted into the desired columns:

aspect          | preference
----------------+--------------------
COLOUR_SCHEME   | RASPBERRY_BLISS
FONT            | TERMES_HEROS
LIST_LAYOUT     | BULLET_SNOWFLAKE

Question

What is a simpler way to write this query (e.g., fewer lines of code, fewer nested SELECTs, or fewer references to the array)?

Here is a possible solution:

WITH data AS (
    SELECT '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] AS arr
)
SELECT
    arr[i][1] AS aspect,
    arr[i][2] AS preference
FROM
    data,
    generate_subscripts((SELECT arr FROM data), 1) i
;

The WITH part is there for centralizing data input.  Otherwise it just takes all sub-arrays and selects the elements of those.  Check this on SQLFiddle.


SQL Server 2008 r2 sp1 upgrade? I've been given an SQL Server to run maintenance plans on. After running a script called sp_blitz which checks the health and security status of the server and going through the errors logs I realized that there are up to 5 databases with instances of database corruption.

To have my own understanding an experience of these errors, I set up fresh maintenance plans for backup only just to monitor what errors I will get. The result showed that some databases were not being backed up at all and when I checked what the error was, for one of the databases, I got "Failed to flush the commit table to disk in  (dbid) due to error 2601." and the error log showed "error 3041 severity 16 state 1".

I went on google to see what I could do and it said I could restore the database from a point where it was still ok. I checked the local disks for the most recent backup and that was 24/08/2015 which corresponded to what the database properties said in 'last database backup section'.

Can you please advice me on what you think my next steps should be sir or what I can do in a situation like this.

Also we are using SQL Server 2008 r2 sp1. Should I be looking to upgrade to sp3?

one of the databases, I got "Failed to flush the commit table to disk in (dbid) due to error 2601." and the error log showed "error 3041 severity 16 state 1".

The above error occurs when you have CHANGE TRACKING enabled on the database. To find what databases have Change tracking enabled, run below tsql:

SELECT d.name AS 'Database Name', ctd.*
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON d.database_id = ctd.database_id

This error was fixed in SQL Server 2008R2 SP1 +CU1.  Even with SP1+CU1, you are running an unsupported patch of SQL Server. I would suggest you to apply SQL Server 2008R2 SP3 then Security Update - KB3045316.

  A security issue has been identified in the SQL Server 2008 R2 SP3 that could allow an attacker to compromise your system and gain control over it.

Recommendations :

Take full backup of current databases with CHECKSUM.
Apply latest SP3 + Security Update (as mentioned above)
Stop using maintenance plans and use a more reliable backup solution (hint: Ola Hallengren's backup solution).


Add Decimal Point after First Digit in a Numeric Value Can any one here please advise how to add a decimal point after first digit while selecting numeric value from database?

  Example: Output is 8250 should be 8.250

I tried this:

convert(decimal(10,3),ti.Rate/1000) AS Amount

but the output returns the wrong value of 8.000. It should be 8.250.

Because all the values inside the convert call are integers it is performing integer division to get an integer result before converting to a decimal. ti.Rate/1000 is 8250/1000 which with integer division becomes 8. CONVERT then changes the type but the extra precision is already lost.

If you add a decimal place to the numeric literal 1000, SQL Server will see it as a decimal/numeric type number instead of an integer, so all you need to do is CONVERT(DECIMAL(10,3),ti.Rate/1000.0) AS Amount. Dividing an integer by a decimal/numeric (or vice-versa) results in a decimal/numeric.

In fact you can probably just do ti.Rate/1000.0 AS Amount unless you specifically need the type to be exactly DECIMAL(10,3) instead of the default (a quick test on SQL2016 shows the default to be NUMERIC(17,6), note that numeric and decimal are functionally identical as one is a synonym for the other). Or if you do specifically need DECIMAL(10,3), you might avoid extra implicit conversions by converting one of the inputs instead of the output: CONVERT(DECIMAL(10,3),ti.Rate)/1000.0 AS Amount (though the performance difference here is likely to be at best infinitesimal).


SQL Server sys.dm_tran_active_transactions long running transaction worktable today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no clue with the sp_whoIsActive oputput alone.This issue is solved but now I see that there are quite old transactions in the table with type "worktable". Here's the output for the following query

SELECT  *,
 case transaction_type
      when 1 then 'Read/Write'
      when 2 then 'Read-Only'
      when 3 then 'System'
      when 4 then 'Distributed'
      else 'Unknown - ' + convert(varchar(20), transaction_type)
 end as tranType,
 case transaction_state
      when 0 then 'Uninitialized'
      when 1 then 'Not Yet Started'
      when 2 then 'Active'
      when 3 then 'Ended (Read-Only)'
      when 4 then 'Committing'
      when 5 then 'Prepared'
      when 6 then 'Committed'
      when 7 then 'Rolling Back'
      when 8 then 'Rolled Back'
      else 'Unknown - ' + convert(varchar(20), transaction_state)
 end as tranState,
 case dtc_state
      when 0 then NULL
      when 1 then 'Active'
      when 2 then 'Prepared'
      when 3 then 'Committed'
      when 4 then 'Aborted'
      when 5 then 'Recovered'
      else 'Unknown - ' + convert(varchar(20), dtc_state)
 end as dtcState
FROM    sys.dm_tran_active_transactions
ORDER BY transaction_begin_time

The transaction_begin_time happens to correlate with the time the SQL Server Service was last restarted.

Should I be concerned about this in any way? I did have a look at https://www.sqlservergeeks.com/sys-dm_tran_active_transactions/ to learn more about the dmv. However it just mentions that worktables are used for storing temporary query results in tempdb. However my understanding is that if I kick off a query whose execution plan decides to spool to tempdb and use a worktable a new worktable is created each time it is needed. Therefore it seems odd that therer are 6 worktables so old.  I could hypothesize that these are tables reused since service restart for monitoring queries which run constantly or internal SQL server stuff. Anyway the transaction type read only seems to be harmless and I noticed no sign of blocking or version store growth.

Thanks in advance for your assistance

Martin

I see the same thing on my 2016 instance - exactly 6 worktable transactions, that started just a few seconds after the server started up.  If I join to sys.dm_tran_database_transactions, I can see that these transactions exist in tempdb (database_id = 2), as one might expect for worktables:

SELECT
    dtat.transaction_id,
    dtat.[name],
    dtat.transaction_begin_time,
    dtdt.database_id
FROM sys.dm_tran_active_transactions dtat
    INNER JOIN sys.dm_tran_database_transactions dtdt
        ON dtat.transaction_id = dtdt.transaction_id;

This also pretty closely lines up with the log messages indicating that tempdb was being cleared and started up during instance startup:

I can't find any way through the DMVs to link these "transactions" to a particular session, but I think it's safe to say they are a system process and not something to worry about.

Since you mentioned that you didn't see these in sp_WhoIsActive, it's worth pointing out that system session's are filtered out of that procedures output by default.  You can see them by passing an extra parameter:

EXEC sp_WhoIsActive @show_system_spids = 1;


How to get user name to appear in Alerts Setting up some alerting in SQL server 2017 which get dispatched by email to certain DBAs.

Some examples of alerting I am trying to configure would be for Alert number 229 -  The EXECUTE permission was denied on the object 'X'

At the moment there is no details included in the email with regards to who had their permission denied - I am wondering, is it possible to include this?

Current notifications look like:

  DATE/TIME:    7/2/2018 1:29:59 PM

  DESCRIPTION:  The EXECUTE permission was denied on the object
  'sp_Price', database 'Customisations', schema 'dbo'.

Alerts generated by the SQL Server Agent don't include this information, and you can't add it, unfortunately.

One option would be to set up an Extended Events session to capture the error_reported event and filter by the specific error number. This will allow you to capture additional info such as program name, username, client machine etc.

Log this to a file and use an Agent job to periodically query the file and alert if entries are found.

Links:

Login Failures in Extended Events: https://www.mssqltips.com/sqlservertip/3214/use-extended-events-to-get-more-information-about-failed-sql-server-login-attempts/

Extended Events Quickstart Guide: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-2017