How to quickly cascade rare delete to a large table?

The GIN index certainly can be used for the cascading delete (you will need to create extension btree_gin before creating the index).

How much space it will save you is hard to predict, it would depend on how much duplication there is in the values of those columns.

Having 3 separate GIN indexes will also work, and is likely slightly smaller than the one combined index.  Each value in a combined index has to "remember" which column it came from, which takes up extra space.

If the btree indexes are bloated, whatever is causing that might cause the gin index to get bloated as well.


Proper Shutdown of SQL Servers in a Cluster

Does anyone see any issues with that?

You are correct in the steps but your approach is way too much for a little work.

For disk maintenance, why would you shutdown the entire cluster ?

Just suspend that disk (which requires maintenance) and then once it is done resume the node.

Basically, SHUT DOWN CLUSTER will stop all the roles and services on all the nodes of the cluster. The Cluster UI will ensure that all the roles and services are shutdown gracefully.

Seems like you have posted the same question at SQLServerCentral.com

Refer to How to Properly Shutdown a Failover Cluster or a Node


Select historical prices for an exact datetime - MySQL

Duration goes to 1 sec when I'm selecting name column. But when I use this query, time is only 0.094 sec:

SELECT h.* FROM (SELECT * FROM (SELECT id, item_id FROM price_list x WHERE start_date <= '2013-12-15 12:14:18' ORDER BY start_date DESC) a GROUP BY item_id) b LEFT JOIN price_list h ON h.id = b.id

Thank you, Ryan!


Mongos not returning existing data in shards

issue flushRouterConfig command, and give a try. It enforces mongos to flush cached metadata which is out of date.

Let me know if it doesn't work.


Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included

Now option "use page locks when accessing the index" caused a lot of
  pagelocks, so it was unchecked

When you play with fire, you get burned. Have you considered what alternative will be used? With page locks disabled, there are two possibilities: use row lock granularity or use rowset granularity. Since there must be at least one row per page, using row granularity will turn into locking at least as many individual locks as page lock, but much more likely more, many many many more locks. So it would turn a bad situation into a far worse situation. Using  rowset granularity will 'heal' the lock manager health as only one lock will be acquired, at the cost of throwing all concurrency down the drain. Do no mess with settings you don't understand. BTW, your question 'do row locks cause key locks' clearly expose the level of your understanding of locking as inadequate to be trusted in changing such important settings, no offense intended.

The important question is why was the query taking page locks? Page locks are the hallmark tellsign of scans: the engine chooses a high granularity because it knows it has a lot of rows to look at. Your post is missing critical information needed to understand the deadlock, please post the entire original deadlock XML, along with the complete schema definition.

As for the question: the 'rowlocks' resource is 'keys' in B-Trees and 'rowids' in heaps.


Granting [NT AUTHORITY\SYSTEM] sysadmin on SQL server - Compliance/Best Practice

I assume this is some sort of IT management or monitoring agent, otherwise it shouldn't be running on your SQL Server.

If you don't run any other code as SYSTEM on the server, there's not really a lot of extra risk over and above running 3rd party code with Windows administrative privileges.  If the service runs as any local administrator, it can get access to SQL Server.

If possible you should do both of:

1) Grant the service the least privileges is needs to run

2) Provision the login and grant permissions to the per-service SID instead of the service account.

So if the service is has a Name of 'someservice' (not DisplayName), create a login like:

create login [nt service\someservice] from windows

And grant the permissions to that.  Then other services running under the same service account won't be able to connect to SQL Server.


migrating legacy databases from SQL Server 2000 via SQL Server 2008 to SQL Server 2012: users and logins

You can use the following to list orphaned sql users:

USE foo
go

EXEC sp_change_users_login 'Report';

and

USE foo
go

EXEC sp_change_users_login 'update_one', 'foouser', 'foouser';

to fix each one.


What is the difference between Completion and Success in SQL Server Maintenance Plans?

The execution result, in your case, success, completion, or failure is a precedence link that can create a relationship between two tasks. From BOL

  A precedence link between two tasks establishes a relationship between the tasks. The second task (the dependent task) executes only if the execution result of the first task (the precedent task) matches specified criteria. Typically the execution result specified is Success, Failure, or Completion

If you wanted to say, have your plan execute a statement that was not designed around success or failure you could use completion to establish a relationship and proceed to the next task.

Example: Purge all records of type 'A' from database [ABC], then execute a database backup
 - You don't care how many records, if any, were found or deleted. Just run the statement. When it is done, back up the database

Example: Reorganize the index's of database [ABC] only after successfully checking the integrity. If the integrity check fails, do not reorganize the index's

Maint. Plans

Precedence Constraints


Postgres settings reported differently in pgsql

I see the same difference on my test database:

show shared_buffers;
 shared_buffers
────────────────
 768MB

select name, setting from pg_settings where name = 'shared_buffers';
      name      │ setting
────────────────┼─────────
 shared_buffers │ 98304

Then watching the numbers for a short while, I set up the following query:

SELECT 98304 * 8192 / (1024 * 1024);
 ?column?
──────────
      768

So, to me it looks like that pg_settings reports these sizes in 8 kb pages (8192 refers to this fact).

Note that there is a third way to check settings, namely the current_setting() function:

SELECT current_setting('shared_buffer');
 current_setting
-----------------
 768MB


Configuring Dynamics GP's (SQL Server) Disks

While I understand you want to follow Microsoft guidelines, in my opinion a lot of the Dynamics best practices are outdated.

I personally don't work with GP but with AX and I tend to follow the SAN vendor guidelines for SQL Server rather than the guidance that Microsoft defined in the days of direct attached storage.

I haven't worked with EMC a lot but they clearly have a SQL Server best practices document which states:

  Most SQL server database environments can benefit from storage pool
  based  configurations

I'd just run some benchmarks before migrating to the new hardware and double-check with the SAN vendor and go with their recommendations.

For example, the recommendation says to use RAID1 for tempdb, but if you can get your hands on some nice NVMe PCI express cards those will perform lots and lots faster even if they aren't the "recommended" setup.


I initially just wanted to point at this as a comment, but my rep is too low. So excuse me if this is not a fully fledged answer. (Anyone feel free to edit or hint in comments)

Looking at the documentation at datastax it appears that data will be evently divided by nodes, depending on the "automagical" tokens. From my understanding it's simple as that a new vnode with new tokens just takes an even portion of each node, like all the other nodes already do. Hell, this is even what the doc says basically word by word:

  Rebalancing a cluster is no longer necessary when adding or removing nodes. When a node joins the cluster, it assumes responsibility for an even portion of data from the other nodes in the cluster. If a node fails, the load is spread evenly across other nodes in the cluster.

And to address your question:

  To further specify my question for virtual nodes does the cluster re-evaluate tokens for all nodes just because a new host has been added?

Sort of. The new node itself takes some partitions (evenly) from all other nodes. If you remove a node every other node will rebalance its partitions from the other nodes, making up for the lost node.


You can use a common table expression (cte).

;with cte as (
select
     row_number() over(partition by bbid order by #one.id desc) as rn ,id from #one
)
select
    t1.id
    ,t3.bbid
    ,t3.indate
    ,t3.st
    ,t1.val
from
    #One as t1
join #Two as t3
    on t1.bbid = t3.bbid
join cte
    on cte.id = t1.id
    and cte.rn = 1
order by t1.id desc


BCP woes: Unexpected EOF encountered in BCP data-file

So it turns out that the -n and the -N are not what I wanted;  I had assumed it used the db metadata to implicitly convert the incoming data, but it looks like it's expecting binary data with that.

I changed it to -c and it worked without issue.


Handling growing number of Tenants in Multi-tenant Database Architecture

At the lower end (500 tenants / 10000 users) this is how I did it. First, you have a "control" database that is global, central and contains all of the information about tenants and users (I really don't think you want to manage these as SQL auth logins). So imagine a database called "Control" with the following tables:

CREATE TABLE dbo.Instances
(
  InstanceID INT PRIMARY KEY,
  Connection VARCHAR(255)
  --, ...
);

INSERT dbo.Instances SELECT 1, 'PROD1\Instance1';
INSERT dbo.Instances SELECT 1, 'PROD2\Instance1';
-- ...

CREATE TABLE dbo.Tenants
(
  TenantID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL UNIQUE,
  InstanceID INT -- Foreign key tells which instance this tenant's DB is on
  --, ...
);

INSERT dbo.Tenants SELECT 1, 'MyTenant', 1;
-- ...

CREATE TABLE dbo.Users
(
  UserID INT PRIMARY KEY,
  Username VARCHAR(320) NOT NULL UNIQUE,
  PasswordHash VARBINARY(64), -- because you never store plain text, right?
  TenantID INT -- foreign key
  --, ...
);

INSERT dbo.Users SELECT 1, 'foo@bar.com', 0x43..., 1;

In our case when we added a new tenant we would build the database dynamically, but not when the admin user clicked OK in the UI... we had a background job that pulled new databases off a queue every 5 minutes, set model to single_user, and then created each new database serially. We did this to (a) prevent the admin user from waiting for database creation and (b) to avoid two admin users trying to create a database at the same time or otherwise getting denied the ability to lock model (required when creating a new database).

Databases were created with the name scheme Tenant000000xx where xx represented Tenants.TenantID. This made maintenance jobs quite easy, instead of having all kinds of databases named BurgerKing, McDonalds, KFC etc. Not that we were in fast food, just using that as an example.

The reason we didn't pre-allocate thousands of databases as the comment suggested is that our admin users usually had some idea of how big the tenant would become, whether they were high priority, etc. So they had basic choices in the UI that would dictate their initial size and autogrowth settings, which disk subsystem their data/log files would go to, their recovery settings, backup schedule to hinge off of, and even smarts about which instance to deploy the database to in order to best balance usage (though our admins could override this). Once the database is created, the tenant table was updated with the chosen instance, an admin user was created for the tenant, and our admins were e-mailed the credentials to pass along to the new tenant.

If you're using a single point of entry, it is not feasible to allow multiple tenants to have users with the same username. We opted to use e-mail address, which - if all users work for the company and use their corporate e-mail address - should be fine. Though our solution eventually became more complex for two reasons:

We had consultants that worked for more than one of our clients, and needed access to multiple
We had tenants who themselves were actually comprised of multiple tenants

So, we ended up with a TenantUsers table that allowed one user to be associated with multiple tenants.

Initially when a user logs in, the app will know the connection string for the control database only. When a login is successful, it can then build a connection string based on the information it found. E.g.

SELECT i.Connection
  FROM dbo.Instances AS i
  INNER JOIN dbo.Tenants AS t
  ON i.InstanceID = t.InstanceID
  INNER JOIN dbo.TenantUsers AS u
  ON i.TenantID = u.TenantID
  WHERE u.UserID = @UserID;

Now the app could connect to the user's database (each user had a default tenant) or the user could select from any of the tenants they could access. The app would then simply retrieve the new connection string, and redirect to the home page for that tenant.

If you get into this 10MM user area you propose, you'll definitely need this to be balanced better. You may want to federate the application so that they have different points of entry connecting to different control databases. If you give each tenant a subdomain (e.g. TenantName.YourApplicationDomain.com) then you can do this behind the scenes with DNS/routing without interrupting them when you need to scale out further.

There is a lot more to this - like @Darin I am only scratching the surface here. Let me know if you need a non-free consult. :-)


ok.  It looks like you have 4 tables here with nothing to join them together.  Perhaps the easiest solution at this point would be to create a mapping table called something like CarRental which would have the columns Carpoolid, cilentsid, dateinid, and dteoutid.

I think the way I would go would be to drop the datein and dateout table and put them on a table called car rental.

    CREATE TABLE [dbo].[CarRental](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CarpoolID] [int] NOT NULL,
    [ClientID] [int] NOT NULL,
    [DateIN] [datetime] NULL,
    [DateOut] [datetime] NOT NULL,
 CONSTRAINT [PK_CarRental] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CarRental]  WITH CHECK ADD  CONSTRAINT [FK_CarRental_CarPool] FOREIGN KEY([CarpoolID])
REFERENCES [dbo].[CarPool] ([Id])
GO

ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_CarPool]
GO

ALTER TABLE [dbo].[CarRental]  WITH CHECK ADD  CONSTRAINT [FK_CarRental_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_Clients]
GO

Car rental now stores the relationship between the client and the car that they rented and for each record stores the datein and dateout.

If you dont want to script all of this out you can use the table designer to create the table by right clicking on tables and then choosing new table. You can use the database diagram to drag and drop columns in order to create your foreign key relationships by right clicking database diagrams and chosing New Database diagram.

If you use this structure then You would leave the DateIn field as NULL untill the car was returned.  Your query to find out which clients and cars have not been returned would be

SELECT carpool.brand, clients.name, clients.lastname
  from carrental
       join carpool on carpool.id = carrental.carpoolid
       join clients on clients.id = carretnal.cientid
where carretnal.datein IS NULL

In this case you dont need to update the records for people who havent returned a car.  You only update the record to contain the date when they have returned the car.

UPDATE CarRental
SET Datein = GETDATE()
WHERE Clientid = @CLientid
  AND Carpoolid = @Carpoolid


Connect to SQL Server 2008 R2 from Windows 98

Per a comment from David Browne, you should try using the built-in SQL Server ODBC driver.

One approach for doing this is to connect to SQL Server through MS Access with Linked Tables.


Query for get column number in PostgreSQL

select      ordinal_position

from        information_schema.columns

where       table_catalog = 'my_table_catalog'
        and table_schema  = 'my_table_schema'
        and table_name    = 'my_table_name'
        and column_name   = 'my_column_name'


PostgreSQL Query and/or Session CPU Usage

The auto_explain module might be what you are looking for.

The statistics collector is probably the closest you can get to the Oracle v$ views.

Some more might be available in the pg_stat_statements extension.


Return a ranking from multiple table with mySQL

select seg.seg_text,
       ifnull(nights.Night_Nb,0) as Night_Nb,
       ifnull(nights.Percentage,0) as Percentage,
       ifnull(bookings.Booking_Nb,0) as Booking_Nb,
       ifnull(bookings.Percentage,0) as Percentage
  from __Segmentations seg
 left join ( select seg.seg_id,
                    count(*) as Night_Nb,
                    round(100*count(*)/(select count(*) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as nights  on (nights.seg_id=seg.seg_id)
 left join ( select seg.seg_id,
                    count(distinct bil.bil_bookingid) as Booking_Nb,
                    round(100*count(distinct bil.bil_bookingid)/(select count(distinct bil_bookingid) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as bookings  on (bookings.seg_id=seg.seg_id)
 group by seg.seg_id;
+----------+----------+------------+------------+------------+
| seg_text | Night_Nb | Percentage | Booking_Nb | Percentage |
+----------+----------+------------+------------+------------+
| Direct   |        6 |      85.71 |          2 |      66.67 |
| Website  |        1 |      14.29 |          1 |      33.33 |
| Other    |        0 |       0.00 |          0 |       0.00 |
+----------+----------+------------+------------+------------+
3 rows in set (0.02 sec)


How to extract Procedure, Function, etc. Source Code from an Oracle Export

If you've got access to the original database, I'd go with DBMS_METADATA.GET_DDL.

You can script it with UTL_FILE so that it goes through each object (from USER_OBJECTS), uses the name and type to extract the object then write it to a file that has the appropriate naming convention.

It will be a lot cleaner than trying to split a single file.


Performance issues from RDS Aurora 5.6 to Aurora 5.7

Rate Per Second = RPS
Suggestions to consider for your 5.7.12 Aurora Parameters group
innodb_write_io_threads=16  # from 4 to enable higher IOPS to your SSD device
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_max_dirty_pages_pct_lwm=.0001  # from 0 to enable pre-flushing
innodb_max_dirty_pages_pct=.0001  # from 75 % dirty tolerated to reduce innodb_buffer_pool_pages_dirty count of 354,764 - will take hours
net_buffer_length=98304  # from 16384 to reduce count of interruptions for send/receive pkts
innodb_fast_shutdown=0  # from 1 to avoid recovery cycle on restart

This is just the beginning of your journey to improve performance.
Thank you for the opportunity to serve your team.
Not all these Global Variables are dynamic.  Will require instance stop/start.


Is there a reason to store currency amounts as fractions instead of decimals?

There's no advantage from a DBMS perspective, but it may allow better data management, if you are dealing with some unusual form of currency.  For example, if you had a currency where the basic unit is the pound, and the pound is composed of 20 shillings, and each shilling is composed of 12 pennies, it would sometimes be impossible to represent an exact number of pennies as a decimal fraction of some number of pounds.

If penny amounts were represented as approximations, round off error would begin to creep in when arithmetic operations were performed.

Such a currency may seem unbelievably quirky, but this is the way British currency worked when Elizabeth II ascended to the throne.  There may be currencies in the world today that still work in a similar way.


AutoIncrement non-primary key or Make unique composite key?

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to enforce the proper data integrity.

In SQL Server, multiple columns can participate in a unique index / constraint:

ALTER TABLE dbo.Orders
ADD CONSTRAINT UQ_Year_OrderNumber
UNIQUE ([Year], OrderNumber);

Thus there is no need to create a computed column to combine the two columns.


You should use a dynamic query.

 DECLARE @f1 NVARCHAR(10);
 set @f1= 'MyCategory'

 DECLARE @CMD NVARCHAR(MAX);
 SET @CMD = 'SELECT Field1 AS ' + @f1 + ' FROM MyTable';

 SELECT @CMD;
  --EXEC (@CMD);
GO

| (No column name)                         |
| :--------------------------------------- |
| SELECT Field1 AS MyCategory FROM MyTable |

dbfiddle here


How can we select data from another table during an insert?

You want to drop the values part of the query and do an INSERT\SELECT.

This is really basic SQL and you can read more about it in BOL.

INSERT INTO wtdocversion (col1, col2, col3)
SELECT
  id,
  'col2',
  'col3'
FROM wtdocmaster;


Get the CreatedAt Timestamp when the number of rows is equal to some number

You could try along

SELECT
  id
  , createdAt
FROM (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
) AS S
WHERE rnk = 8
;

The inner SELECT groups the records by OtherId, orders and ranks them by createdAt. The outer SELECT picks the record by some number.

The CTE (common table expression) @Akina mentioned, would pull the sub-select to the top:

WITH
S (id, createdAt, rnk) AS (
  SELECT
    id
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
  FROM Table1
)
SELECT
  id
  , createdAt
FROM S
WHERE rnk = 8
;

Do not see much difference in this case. But the technique helps a lot for more complex settings as it allows to grow the statements and is usually more readable.

If, however, only a single OtherId is of interest, there is no need for the window function:

SELECT
    id
    , createdAt
FROM Table1
WHERE OtherId='A'
ORDER BY createdAt
OFFSET 7 ROWS
FETCH FIRST 1 ROW ONLY
;

On the other hand: Returning either a specific occurrence or the maximum could be done via a UNION:

WITH
S (otherId, createdAt, rnk, cnt) AS (
  SELECT
    OtherId
    , createdAt
    , RANK() OVER (PARTITION BY OtherId ORDER BY createdAt)
    , COUNT(createdAt) OVER (PARTITION BY OtherId)
  FROM Table1
)
SELECT
  otherId
  , createdAt
FROM S
WHERE rnk = 8
UNION ALL
SELECT
  otherId
  , MAX(createdAt)
FROM S
WHERE cnt < 8
GROUP BY otherId
ORDER BY otherId
;

See it in action: db<>fiddle

Please comment, if and as this requires adjustment / further detail.


Thanks Mika, I tried your solution in workbench (5.2.41). It worked after I cleared the passwords in the vault (click Manage Connections) and restarted the WorkBench.

To clear passwords stored in the vault, I did the following:

Open MySQL WorkBench the you will see the home page
Click Manage Connection at bottom left corner.
In Manage DB Connections, click Clear button besides Store in Vault...
Close it and restart WorkBench.
Double click Local instance mysql in home page, now you db will popup.


Setup replication between two Galera nodes

If you have any AUTO_INCREMENTs, I think it is impossible.

You are effectively asking for 6 Masters talking to each other.  For AUTO_INCREMENT not to mess up, you need auto_increment_increment = 6.  But Galera will insist on using 3, since there are only 3 "nodes" in [each] "cluster".


Does setting Query Optimizer Fixes to ON enable Scalar UDF Inlining on a database with compatibility level 140?

No, 'query optimizer fixes' only affects optimizer bug fixes that may result in unexpected query plan changes. It does not enable new features.

The only way to enable scalar function inlining is to set the database compatibility level to 150:

  Enabling Scalar UDF Inlining

  You can make workloads automatically eligible for Scalar UDF Inlining by enabling compatibility level 150 for the database. You can set this using Transact-SQL. For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

  Apart from this, there are no other changes required to be made to UDFs or queries to take advantage of this feature.


If you aren't changing the physical server's name you'll need to change the name that SQL Server knows back.

sp_dropserver 'SERVER2\SQLSERVER'
sp_addserver 'SERVER1\SQLSERVER', local

You then just need to put an alias in DNS so that when people try to connect to SERVER2 then are redirected to SERVER1 without knowing about it.  Specifically you'll want (or you'll want your sysadmin) to put a CNAME record into DNS pointing SERVER2 to SERVER1.


the problem is that for some reason postgres stops using the index on the column "TBL_NAME".

when the index is used, the table is filtered first, and the remaining values are all ints.
but when the index is not used, the cast will happen on all the rows, and will fail on the strings.

as a workaround, I set enable_seqscan = false, and that forces postgres to use the index, but that is not a long term solution.

I opened a bug on hive with a more detailed explanation of the problem:
https://issues.apache.org/jira/browse/HIVE-23576


How to identify how many instances of Oracle are installed on Linux environment

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.


pg_restore to create database before loading it?

Specify -d postgres on the command line.

Quote from the manual for the -C option. The database to be restored is automatically take from the backup file

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

pg_restore -h "192.168.1.212" -p 5432 -U mysuperusername -C -d postgres bp.custom


Update SQL Server Management Studio without restart

Reference:

Download SQL Server Management Studio (SSMS)

Minimize Installation Reboots

Take the following actions to reduce the chances of SSMS setup requiring a reboot at the end of installation:

Make sure you are running an up-to-date version of the Visual C++ 2013 Redistributable Package. Version 12.00.40649.5 (or greater) is required. Only the x64 version is needed.
Verify the version of .NET Framework on the computer is 4.6.1 (or
greater).
Close any other instances of Visual Studio that are open on the
computer.
Make sure all the latest OS updates are installed on the computer.
The noted actions are typically required only once. There are few
cases where a reboot is required during additional upgrades to the
same major version of SSMS. For minor upgrades, all the
prerequirements for SSMS are already be installed on the computer.


pg_restore: [archiver] could not find entry for ID 12

You need to identify in which table its throwing this error.

Are you taking full database dump or single table?

Anyhow Try this workaround.

Take schema dump alone.

pg_dump -O -s dbname > schema.sql
Restore it on the target.
psql -h host -U user -d < schema.sql
On target disable trigger on all the tables.

ALTER TABLE table_name Disable TRIGGER ALL;

Use this query to generate this statement for all the tables.

SELECT 'ALTER TABLE '
       || table_name
       || ' DISABLE TRIGGER ALL;'
FROM   information_schema.TABLES
WHERE  table_catalog = 'dbname' -- replace with your db name
       AND table_schema IN( 'public' );  -- add all your schema

Then take the data only dump.

pg_dump -U user dbname -Fc --data-only > dbname.dump

Then restore your backup.

pg_restore -h host -U user dbname < dbname.dump

To make the restore fast, use -j

pg_restore -h host -U user -j 10 dbname < dbname.dump


Is it possible to access column by index instead of it's name/alias in query?

Nope. In query you need to address columns explicit.
But you can do this inside your application.


" Occasionally it will creep up approaching 100% RAM usage; this has
  been causing a lot of problems"

That's often a red herring, as SQL Server will use all of RAM (but will give it back under OS memory pressure).

Rule of thumb leave 2GB or 5-10% of total physical memory free (Max Memory setting) whichever is larger.

Glenn Berry has a good resource here:

Suggested Max Memory Settings for SQL Server 2005/2008

Also:

A Sys Admins Guide to Microsoft SQL Server Memory
Wow… An online calculator to misconfigure your SQL Server memory!
How much memory does my SQL Server actually need?
Troubleshooting SQL Server: A Guide for the Accidental DBA
Server Memory Options


How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008

Well .. so nobody got an answer. But I think I (finally) got it. Guess what... it is somewhere in the metamodel (as usual)!

When adding an item to the subscription, the new conflict resolver references to be used by the stored procedure come from the [distribution].[MSmerge_articleresolver] table
But, for existing subscriptions, previous conflict resolver references are stored in the system tables of the publishing database, ie [sysmergearticles], [sysmergeextendedarticlesview], and [sysmergepartitioninfoview]

So we have on one side an item initialy published with SQLSERVER 2005, where the publication references the 2005 conflict resolver, as per the publishing database metamodel. On the other side, the machine will attempt to add the same item to a new publication, this time with a default reference to the conflict resolver available in the distibution database, which is indeed different from the 2005 one ....

To illustrate this, you can check the following

USE distribution
go
SELECT article_resolver, resolver_clsid
  FROM [MSmerge_articleresolver] WHERE article_resolver like '%Later Wins%'
  GO

Then,

USE myPublicationDatabase
go
SELECT article_resolver, resolver_clsid
  FROM [sysmergearticles] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergeextendedarticlesview] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergepartitioninfoview] WHERE article_resolver like '%Later Wins%'
  GO

So it seems that I should update either the references in the distribution database or the references in the publication database. Let's give it  a try!


postgres full text search modifies ts_query

If you don't want the polish library you can use the simple dictionary which will keep the y also:

karoly> select to_tsquery('simple', $$'fabry':*A$$);
+--------------+
| to_tsquery   |
|--------------|
| 'fabry':*A   |
+--------------+


How do I select 1st row of each table and insert that data to a new table

This is not a complete answer (I'm sorry), but maybe a starting point for further thinking.

For a low number of tables UNION is the best approach IMHO. In special if the list of tables are not changing.

For a bigger number of tables you might can think of using some T-SQL and iterate through a list of tables and building up your query with dynamic SQL -- with this you can select your line(s) into a temporary table maybe.

As I'm not sure where you will get your list of tables from, you could get it from some cursor out of system tables or from some hard coded list maybe.


PostgreSQL, storing encrypted data or encrypt database with pgcrypto - what is more secure?

Yes - it is certainly possible that a key used in pgcrypto function would end up in server logs or leak somewhere else.

Postgres could be configured to log long running queries (and for example password hash functions are long running by design) or even all of them. There are utilities that show current running queries. Or an error in a query can leak it in an error message.

So, if I had a choice, I'd use cryptographic functions from a frontend language.


mysqlbinlog failing with " ERROR 1032 (HY000) at line 48: Can't find record in `` " Error

PROBLEM

You are starting from the wrong log. You should start with mysql-bin.000034 not mysql-bin.000035

The timestamp on each binlog shows the last time a binlog event was written to it

mysql-bin.000033 was last written Dec 27 18:26
mysql-bin.000034 was last written Feb  2 18:58. That's 18 hrs 58 min after Feb  2 00:00
mysql-bin.000035 had to be created Feb  2 18:58

Given the timestamps mentioned, mysql-bin.000034 is the log you need to start with.

SOLUTION

You should collect the binlog events from Feb  2 00:00

Please run the following

MYCHANGES=ChangesSinceFeb2Backup.sql
BINLOGS="mysql-bin.000034 mysql-bin.000035"
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" ${BINLOGS} > ${MYCHANGES}

Restore the Feb 2 mysqldump Backup, then play all the events

mysql -u root -p db < ${MYCHANGES}

This will bring back everything up to Feb 18 15:50

If you want to do the PITR up to today at 12:45, you do this

MYCHANGES=ChangesSinceFeb2Backup.sql
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" --stop_datetime="2014-02-18 12:45:00" ${BINLOGS} > ${MYCHANGES}

SUGGESTION

Note that mysql-bin.000035 contains several days worth of binlog events.

Before you run the midnight backup, you should run

FLUSH BINARY LOGS;

That way, you can clearly identify what binary logs to use in PITR

Give it a Try !!!


I find this troubling for two reasons
REASON #1
This could be a bug that never got squashed out of MySQL 5.5.17

Nov 08, 2011 : http://bugs.mysql.com/bug.php?id=63144 (Status is Needs Triage on MySQL 5.5.17)
Mar 21, 2011 : http://bugs.mysql.com/bug.php?id=60563 (MySQL 5.5.10)
Jun 23, 2006 : http://bugs.mysql.com/bug.php?id=20662

REASON #2
You are trying to acquire a metadata lock on an InnoDB table in an RDS environment? Ouch !!!
Possible Workaround
Instead of trusting the metadata locking in RDS, ask the information_schema if the table exists already. Suppose the table is mydb.backupfiles. Run this query:
SELECT COUNT(1) FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='backupfiles';

If the answer comes back 0, run CREATE TABLE backupfiles .... Otherwise, don't run it.
Possible Solution
Upgrade to the Latest MySQL (5.6.12) or the Latest MySQL5.5 (5.5.31). Hopefully, this issue was solved in a more recent version
Give it a Try !!!
UPDATE 2013-07-11 15:16 EDT
Here is a rewrite of your bash code to check if the table already exists
    echo "CREATE DATABASE IF NOT EXISTS $DATABASE;" > /tmp/runthis.sql
    echo "use $DATABASE;" >> /tmp/runthis.sql
    SQLSTMT="SELECT COUNT(1) FROM information_schema.tables"
    SQLSTMT="${SQLSTMT} WHERE table_schema='${DATABASE}' AND table_name='backupfiles';"
    TABLE_EXISTS=`mysql -h portal-rds -u $user --password=$mysqlpw -ANe"${SQLSTMT}"`
    if [[ ${TABLE_EXISTS} -eq 0 ]]; then
      SQLSTMT="CREATE TABLE backupfiles (fileName VARCHAR(20),"
      SQLSTMT="${SQLSTMT} archiveId VARCHAR(500), checkSum VARCHAR(100),"
      SQLSTMT="${SQLSTMT} glacierVault VARCHAR(100), timeStamp date);"
      echo "${SQLSTMT}" >> /tmp/runthis.sql
    fi
    SQLSTMT="INSERT INTO backupfiles VALUES ('$archive_file_name',"
    SQLSTMT="${SQLSTMT} '$archiveID', '$CURRENTVAULT', '$checkSum', CURDATE());"
    echo "${SQLSTMT}" >> /tmp/runthis.sql
    echo "COMMIT;" >> /tmp/runthis.sql
    mysql -h portal-rds -u $user --password=$mysqlpw < /tmp/runthis.sql


You should still be able to get the result that you want using the PIVOT function.  If your values that you need to aggregate are a string, then you would use either min or max to get the final result.

Based on your sample data and the desired result, you will need to generate a distinct value that will be used when grouping in the PIVOT. I would use row_number() to create a sequenced number for each blah and custlabel, this will allow you to return multiple rows for each blah.

If you have a limited number of custlabels, then you can hard-code the query:

select blah, info1, info2, info3, info4, info5
from
(
  select cl.custlabel,
    cv.custvalue,
    l.blah
    , row_number() over(partition by l.blah, cl.custlabel
                      order by cl.custlabel) seq
  from custlabel cl
  inner join custvalue cv
    on cl.custlabelid = cv.custlabelid
  inner join linkage l
    on cv.linkageid = l.linkageid
) d
pivot
(
  max(custvalue)
  for custlabel in (info1, info2, info3, info4, info5)
) piv;

See SQL Fiddle with Demo.

But if you are going to have an unknown number of custlabels, then you will want to look at using dynamic SQL to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(custlabel)
                    from custlabel
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT blah, ' + @cols + '
            from
            (
              select cl.custlabel,
                cv.custvalue,
                l.blah
                , row_number() over(partition by l.blah, cl.custlabel
                                  order by cl.custlabel) seq
              from custlabel cl
              inner join custvalue cv
                on cl.custlabelid = cv.custlabelid
              inner join linkage l
                on cv.linkageid = l.linkageid
            ) x
            pivot
            (
                max(custvalue)
                for custlabel in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo

Both versions will give a result:

| BLAH |        INFO1 |        INFO2 |        INFO3 |        INFO4 |  INFO5 |
| test | info1 value1 | info2 value1 | info3 value1 | info4 value1 | (null) |
| test | info1 value2 | info2 value2 | info3 value2 |       (null) | (null) |
| test | info1 value3 | info2 value3 | info3 value3 |       (null) | (null) |
| test |       (null) | info2 value4 |       (null) |       (null) | (null) |


Should a SQL Server DBA know Windows Server and vice versa? Are these roles soon to become synonymous. Or at least more encapsulated/?

As a database administrator, you are part of a team whose goal is to make a job easier, or just possible, for someone. The team includes system administrators, database administrators, database programmers, application programmers, network administrators, and so in.

A valuable team member knows not only their own role but a little bit about every role in the team.

Specifically, as a database administrator, it will help you to know some or all of the following.

How to create and modify Active Directory user, group, machine, and managed service accounts.
What Kerberos Constrained Delegation is and how to set it up (including setting up Service Principal Names).
The basics of .NET programming (to help you talk to the application programmers).
The basics (and maybe the intermediates) of database design, e.g. normal forms (to help you talk to the database programmers).
Indexing (to help you get involved in database optimisation).

In addition, anyone working in an Active Directory environment will find it useful to know the basics of Group Policy.

As to whether or not you are better off being a "DBA who knows Windows" or a "Windows Admin who knows databases", that depends on the IT industry where you live. For example, I live in New Zealand (a country of 4½ million people). If you want to work in IT here then you have to be a "Windows who knows databases and email and terminal services and printing and troubleshooting and lots of other stuff." The industry is too small for specialists. :-)


Performance problem caused by joining with the result of a scalar function?

Exactly as expected.

A scalar UDF is a black box to the optimiser: no indexes can be used, the cost can't be worked out correctly.

And if the scalar UDF has table access, then you have running a CURSOR (bad in SQL Server) to do a lookup per row: it isn't a set based operation

To fix it, don't use a UDF this way. It can be written as a JOIN. There is no hint or magic

Edit: remove the UDF. For each row in ratings your query Ableitungen. It's exponential

And I've move the filters into the JOINs

With ratings as
(
    select
        ROW_NUMBER() Over (ORDER BY SEDate) lfd,
        SEDate,
        BBCode
    from  PPV
        join BB on BBRefnr = SEBBRefnr
    where SEHORefnr = @HORefnr
    and SEBBRefnr > 0
)
Select
    l.SEDate,
    l.BBCode,
    A.Code,
    D.DTRefnr
from
    ratings l
    left join
    ratings r on l.lfd = r.lfd + 1
    JOIN
    Ableitungen A ON A.Value = l.BBCode
    join
    DT d on A.code = d.DTCode and d.DTDTKRefnr = A.DTZ
where
    A.DTZ = @fDtz
    AND
   (l.BBCode <> r.BBCode or r.BBCode is null)
order by
    SEDATE;


Find double qoutes inside a string which is in double quotes in mysql

select * from table
where data like '"%"%"'

Or you could compare the length of data against the length of data when '"' is replaced with an empty string ('').


You need to use the catalog command in RMAN to make the file known to the database (don't worry, this does not depend on a recovery catalog!)


How to total additional results from alias column results in Oracle SQL?

This is simply GROUP BY on the first column:

SELECT INVOICE_DUE_DATE - INVOICE_DATE AS "Difference in Days",
     COUNT(INVOICE_DUE_DATE - INVOICE_DATE) AS "Number of Instances"
FROM customer.invoices
GROUP BY INVOICE_DUE_DATE - INVOICE_DATE;


Accidentally executed reset master on the live server

Considering all the slaves were in-sync with master while you issued reset-master, it is safe to issue change master to on slaves with newly generated first bin-log.


Optimising MySQL database with read only tables

Short answer:  You are making unnecessary work for yourself.  Use InnoDB for everything.  Forget about "readonly".

Long answer:

MyISAM is dying.
InnoDB is faster in some benchmarks than either MyISAM or MEMORY.
InnoDB "caches" things in its "buffer_pool", which is in RAM.  If you were using MEMORY to get things held in RAM, then "why bother".
Because the buffer_pool is a "cache", there is no requirement that all the data and indexes be held in memory.  Instead, only the "frequently" used blocks are kept in memory.  Since it is at a block level, it is even better than a human trying to second guess what to pin in memory (via MEMORY engine) and what not to.
Using space for MEMORY tables takes away from using RAM for the buffer_pool
Both MyISAM and MEMORY lock the entire table (when needed); InnoDB locks only rows.
True, MEMORY tables are not "persistent".  InnoDB tables are persistent.

Bottom line...

Use InnoDB for all of your tables.
Set key_buffer_size to 20M only because some system tables use that engine.
Set innodb_buffer_pool_size to 70% of RAM (or a smaller percentage if you have less than 4GB of RAM).


The database engine is pretty smart about this and does not read LOB pages unless it has to as far as I can tell, but I created a test-rig to double-check.

Test Rig

USE master
GO

SET NOCOUNT ON
GO

CREATE DATABASE bigXMLTest
GO
ALTER DATABASE bigXMLTest SET RECOVERY SIMPLE
GO
ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest', SIZE = 20GB , FILEGROWTH = 128MB )
GO
ALTER DATABASE bigXMLTest MODIFY FILE ( NAME = N'bigXMLTest_log', SIZE = 512MB )
GO

USE bigXMLTest
GO

IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
GO

CREATE TABLE dbo.largeTable (

    rowId INT IDENTITY PRIMARY KEY,

    someDate    DATETIME DEFAULT GETDATE(),
    someData    UNIQUEIDENTIFIER DEFAULT NEWID(),
    someXML     XML NOT NULL
)
GO

-- Add dummy data
;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.largeTable ( someDate, someData, someXML )
SELECT
    DATEADD( day, rn % 333, '1 Jan 2016' ),
    NEWID(),
    ( SELECT TOP 100 * FROM sys.messages WHERE language_id = 1033 FOR XML PATH, ROOT('root'), TYPE )    -- creates 24k size piece of XML on my machine
FROM cte

CHECKPOINT
GO 10

EXEC sp_spaceused 'dbo.largeTable'
GO

This creates a test table with 1 million rows, the non-XML data around 36MB and the XML data about 23GB.

Test Queries

--DROP INDEX _udx ON dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT *
FROM dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT rowId, someDate, someData    --, someXML
FROM dbo.largeTable

-- Add covering index for the query
CREATE UNIQUE INDEX _udx ON dbo.largeTable ( rowId ) INCLUDE ( someDate, someData )

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT *
FROM dbo.largeTable

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT rowId, someDate, someData    --, someXML
FROM dbo.largeTable

Test Results

Analysis

Query 1: A select *... causes a full table scan, so the database engine does a high number of LOB reads to read the XML which is stored out of row in LOB pages.  XML can be stored in-row if it's small enough, but the default is out-of-row.  This query takes 5 minutes, ie if you are reading LOB pages on a table like this, you will know about it.

Query 2 A select with column list not including the XML column, has the same number of logical reads in the top window (11,154), but no LOB reads.  The query takes less than a second.

Query 3:  After the non-clustered index has been added, a select * still causes a full table scan as expected.  This query behaves the same as Query 1.

Query 4:  After the non-clustered index has been added, it covers the select with column list query so there is a non-clustered index scan with no LOB reads and lower logical reads overall.

The LOB Reads looks like a broad categorisation as the XML storage pages are classed as TEXT_MIX_PAGE in the sys.dm_db_database_page_allocations DMV:

In summary, it looks like there would be little benefit to splitting the same as the database engine can differentiate when and when not to perform LOB reads in this admittedly simple test rig.  See here for some further details.


Get every column in database that contains only Null records

As per my comment, the actual querying of the tables to check which nullable columns are completely NULL is the more difficult part to this problem.
To get you started you can use Dynamic SQL and the system DMVs to build the queries you'll need like this:
-- Temp table to store the results for later
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (TableName VARCHAR(1000), ColumnName VARCHAR(1000), RowsCount INT, NonNullCount INT)

-- Dynamic SQL variable to execute after we build it out recursively
DECLARE @DynamicSQL NVARCHAR(MAX) = ''

-- Uses the sys DMVs to recursively build which columns and tables to query
SELECT @DynamicSQL = @DynamicSQL +
'
     INSERT INTO #Results
     SELECT ' + S.[name] + '.' + T.[name] + ' AS TableName, ' + C.[name] + ' AS ColumnName, COUNT(*) AS RowsCount, COUNT([' + C.[name] + ']) AS NonNullCount
     FROM [' + S.[name] + '].[' + T.[name] + '];
'
FROM sys.Tables AS T
INNER JOIN sys.Schemas AS S
     ON T.[schema_id] = S.[schema_id]
INNER JOIN sys.Columns AS C
     ON OBJECT_ID(S.[name] + '.' + T.[name]) = C.[object_id]
WHERE C.is_nullable = 1;

-- PRINT @DynamicSQL -- Used for debugging
-- Executes the above dynamic SQL
EXEC sp_ExecuteSQL @DynamicSQL;

-- Get the final results of which columns only contain NULLs
SELECT TableName, ColumnName
FROM #Results
WHERE RowsCount > 0 -- If the RowsCount is 0 then the table is empty so can't determine if the column only contains NULLs when there's no rows to begin with (but you can remove this predicate if you want to include empty tables too)
     AND NonNullCount = 0 -- If the non null count is 0 then the column only contains NULLs
ORDER BY TableName, ColumnName

The above theoretically should answer the question. I had to type it on my phone and from memory, so if there's any minor syntactical errors, let me know and I can easily fix. You can also switch the comments around on the line that prints the dynamic SQL with the one that executes it, to help debug any syntactical issues with the dynamic SQL.


Yes with a lot of reads and reporting an SSD will make a huge difference. From 7200 RPM drive you could expect no more than ~100 IOPS while cheapest SSD can be as minimum 5x as fast as that. With good SSD you can get to 20000 IOPS or even more.

Also random writes in SSD are a lot faster as disk does not have to move every time.


Update existing SQL Server 2008 table via Flat File using SSIS

You can follow either of the below approach :

Load the entire flatfile into a staging table and then update / insert the records based on the criteria you want. After the process is completed, you can truncate the staging table.
Look into using Lookup transform as described in Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services

Refer to this excellent series : Stairway to Integration Services for more details.


How to properly implement compound greatest-n filtering

The simple solution in Postgres is with DISTINCT ON:

SELECT DISTINCT ON (r.series)
       r.volume  AS releases_volume
     , r.chapter AS releases_chapter
     , r.series  AS releases_series
FROM   releases r
WHERE  r.series IN (
    12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499
  , 556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137
  , 1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768
  , 1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255
  , 2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606
  , 2634, 2636, 2695, 2696)
AND    r.include
ORDER  BY r.series, r.volume DESC NULLS LAST, r.chapter DESC NULLS LAST;

Details:

Select first row in each GROUP BY group?

Depending on data distribution there may be faster techniques:

Optimize GROUP BY query to retrieve latest record per user

Also, there are faster alternatives for long lists than IN ().

Combining an unnested array with a LATERAL join:

SELECT r.*
FROM   unnest('{12, 17, 44, 79, 88, 110, 129}'::int[]) t(i)  -- or many more items
     , LATERAL (
   SELECT volume  AS releases_volume
        , chapter AS releases_chapter
        , series  AS releases_series
   FROM   releases
   WHERE  series = t.i
   AND    include
   ORDER  BY series, volume DESC NULLS LAST, chapter DESC NULLS LAST
   LIMIT  1
   ) r;

Is often faster. For best performance you need a matching multicolumn index like:

CREATE INDEX releases_series_volume_chapter_idx
ON releases(series, volume DESC NULLS LAST, chapter DESC NULLS LAST);

Related:

Extremely slow query on indexed column

And if there are more than a few rows where include is not true, while you are only interested in the rows with include = true, then consider a partial multicolumn index:

CREATE INDEX releases_series_volume_chapter_idx
ON releases(series, volume DESC NULLS LAST, chapter DESC NULLS LAST)
WHERE include;


Alternatives to running query for rarely changed data everytime on large table

I am assuming from the DISTINCT that PortNames are duplicated in your table and that there are not 10 million different portnames being returned.

The minimal effort solution is to just place an index on that column:

CREATE INDEX IX_Ports_PortName ON Ports(PortName);

Of course there is still some DB load with this and storage overhead, so you may want a more sophisticated solution such as Caching, which Aaron Bertrand covers quite well in his answer.

You could also employ more Normalization: If portnames are duplicated and knowing them distinctly is important, then you could make a [PortNames] table, and use a PortNameID in the [Ports] table.  That way you could just scan the [PortNames] table which would presumably be much smaller and faster. Of course that may have additional costs and considerations of its own.


Depends on the cursor type, as documented in Concepts: Cursors:

  Forward-only

  A forward-only cursor does not support scrolling; it
  supports only fetching the rows serially from the start to the end of
  the cursor. The rows are not retrieved from the database until they
  are fetched ... Although the database API cursor models consider a
  forward-only cursor to be a distinct type of cursor, SQL Server does
  not. SQL Server considers both forward-only and scroll as options that
  can be applied to static, keyset-driven, and dynamic cursors.

  Static

  The complete result set of a static cursor is built in tempdb when the
  cursor is opened. ...

  Keyset

  The membership and order of rows in a
  keyset-driven cursor are fixed when the cursor is opened.
  Keyset-driven cursors are controlled by a set of unique identifiers,
  keys, known as the keyset. The keys are built from a set of columns
  that uniquely identify the rows in the result set. The keyset is the
  set of the key values from all the rows that qualified for the SELECT
  statement at the time the cursor was opened. The keyset for a
  keyset-driven cursor is built in tempdb when the cursor is opened.

  Dynamic

  Dynamic cursors are the opposite of static cursors. Dynamic
  cursors reflect all changes made to the rows in their result set when
  scrolling through the cursor. The data values, order, and membership
  of the rows in the result set can change on each fetch. ...

As for memory use, all data access goes through the buffer pool, see Memory Manager Architecture. There is no 'close' nor 'deallocation' occurring. Data is brought in memory as needed, it may be referenced by multiple queries, and evicted only when more free memory is required.


How about this?

set @sql = '(SELECT STUFF((
              SELECT space(2)+''and ''+space(2)+''B.''+name+'' = ''+''A.''+name
              from tempdb.sys.columns
              where object_id = object_id(''tempdb..'+quotename(@TempTableName)+''')
              FOR XML PATH ('''')),1, 5,''''))'


Will improper bin-log purging leads to deletion of mysql database

If your slave does not also act as a master to another slave, then you should not have any issues from deleting binary logs. The relay logs are important to the slave.

You need to further investigate on why mysql schema disappeared. Is mysql schema still present on the disk, even though MySQL is not showing it? If you have not stopped the slave instance yet, then you could run under same user as MySQL is running:

lsof | grep '/path_to_mysql'

You might see mysql schema tables marked as deleted in there:

(deleted)

Another possibility is that you are connecting with a user that has limited privileges and just does not see mysql schema. Run SHOW GRANTS; to see what privileges you currently have.

As Rolando pointed out, use PURGE BINARY LOGS as best practice for cleaning up binary logs. If MySQL is down, you could delete the files manually, but then you have to also delete the same file names from the index file. Be careful on master servers, as binary logs might still be needed by slaves.

And if you do need to copy mysql schema, you can do it with just these steps on the slave. Although, I would recommend executing FLUSH TABLES; on master before doing these steps.

/etc/init.d/mysql stop
scp -rp master_server:/var/lib/mysql/mysql /var/lib/mysql/
/etc/init.d/mysql start


Optimise Large Record Deletion Procedure

I prefer the next strategy: the stored routine that fill up the table on each inserted record also delete few expired ones. This look like that:

BEGIN
-- lot of code --
INSERT INTO table ...
-- lot of code --
DELETE FROM table AS w WHERE w.expire < NOW() LIMIT 3;
END

Insertion/deletion ratio is set to 1:3 just to ensure I get the reasonable  removal rate even when incoming data rate become low due to daily/weekly/monthly oscillations. It is acceptable for established bases with low expired records count. If you want to perform initial cleanup, then you have to set the LIMIT to the value that do not insult your server performance.

If you have low incoming data rate then you can create the special routine ad hoc:

CREATE PROCEDURE table_cleanup()
BEGIN
main: REPEAT
  DELETE FROM table AS w WHERE w.expire < NOW() LIMIT 1000;
  UNTIL row_count() = 0 END REPEAT main;
END

Huge DELETE will be splitted into the series of small ones that can't lock the tables for a while.


Check for spaces or zeros in all columns in all tables

You should look into querying the information schema based tables (such as ALL_TAB_COLUMNS or DBA_TAB_COLUMNS) to get a list of all tables and all columns: https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/ALL_TAB_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/DBA_TAB_COLUMNS.html#GUID-91F945AE-5778-45A6-A07E-775A006A9AA1
Then look into how to write dynamic SQL to easily generate the code you need to query every column of every table: https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/dynamic-sql.html#GUID-7E2F596F-9CA3-4DC8-8333-0C117962DB73
Here is some psuedo-code on what your thought process should be when creating the dynamic SQL (*note this is not Oracle PL/SQL syntax, so just take it as a general example on how to build dynamic SQL, and focus mostly on step 3):
-- 1) Create a temp table to hold the results
DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (TableName VARCHAR(100), ColumnName VARCHAR(100), [Value] VARCHAR(100) NULL)

-- 2) Dynamic SQL variable used to build the dynamic SQL string
DECLARE @DynamicSQL AS NVARCHAR(MAX) = ''

-- 3) Build the dynamic SQL string using the DBA_TAB_COLUMNS information schema view. First query gets any row whose column has 0 as a value, second query gets any row whose column has a NULL value
SELECT @DynamicSQL = @DynamicSQL + '
    INSERT INTO #Results
    SELECT ''' + TABLE_NAME+ ''' AS TableName, ''' + COLUMN_NAME + ''' AS ColumnName, [' + COLUMN_NAME + '] AS ColumnValue
    FROM [' + TABLE_NAME+ ']
    WHERE [' + COLUMN_NAME + '] = ''0'';'
    + '
    INSERT INTO #Results
    SELECT ''' + TABLE_NAME + ''' AS TableName, ''' + COLUMN_NAME + ''' AS ColumnName, [' + COLUMN_NAME + '] AS ColumnValue
    FROM [' + TABLE_NAME + ']
    WHERE [' + COLUMN_NAME + '] IS NULL;'
FROM sys.DBA_TAB_COLUMNS

-- 4) Execute the dynamic SQL string
EXEC sp_ExecuteSQL @DynamicSQL

-- 5) Select the final results, grouping up on TableName, ColumnName, and Value to get the counts of 0 and NULL values per column per table
SELECT R.TableName, R.ColumnName, TC.DATA_TYPE AS DataType, R.[Value], COUNT(1) AS [Count]
FROM #Results AS R
INNER JOIN sys.DBA_TAB_COLUMNS AS TC
    ON R.TableName = TC.TableName
    AND R.ColumnName = R.ColumnName
GROUP BY R.TableName, R.ColumnName, R.[Value]


How do I efficiently get "the most recent corresponding row"?

It very much depends on circumstances and exact requirements. Consider my comment.
Simple solution
With DISTINCT ON in Postgres:
SELECT DISTINCT ON (i.good, i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good, i.the_date, p.the_date DESC;

Returned rows are ordered. See:

Select first row in each GROUP BY group?

Or with NOT EXISTS in standard SQL (works with every RDBMS I know):
SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM   inventory  i
LEFT   JOIN price p ON p.good = i.good AND p.the_date <= i.the_date
WHERE  NOT EXISTS (
   SELECT FROM price p1
   WHERE  p1.good = p.good
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );

Same result, but with arbitrary sort order - unless you add ORDER BY.
Depending on data distribution, exact requirements and indices, either one of these may be faster. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

With only few rows per good, DISTINCT ON is typically faster and you get a sorted result on top of it. But for certain cases other query techniques are (much) faster, yet. See below.
Solutions with subqueries to compute max / min values are typically slower. Variants with CTEs are generally slower, yet. (CTEs improved with Postgres 12.)
Plain views (like proposed by another answer) do not help performance at all in Postgres.
db<>fiddle here
Old sqlfiddle
Proper solution
Strings and collation
First of all, your table layout is a sub-optimal. It may seem trivial, but normalizing your schema can go a long way.
Sorting by character types (text, varchar, ...) is done according to current COLLATION. Typically, your DB would use some local set of rules, like in my case: de_AT.UTF-8. Find out with:
SHOW lc_collate;

This makes sorting and index look-ups slower. The longer your strings (names of goods) the worse. If you do not actually care for collation rules in your output (or the sort order), this can be faster with COLLATE "C":
SELECT DISTINCT ON (i.good COLLATE "C", i.the_date)
       i.the_date, p.the_date AS pricing_date, i.good, p.price
FROM   inventory  i
LEFT   JOIN price p ON i.good = p.good AND i.the_date >= p.the_date
ORDER  BY i.good COLLATE "C", i.the_date, p.the_date DESC;
Note the added collation in two places.
Twice as fast in my test with 20k rows each and very basic names ('good123').
Index
If your query is supposed to use an index, columns with character data have to use a matching collation (good in the example):
CREATE INDEX inventory_good_date_desc_collate_c_idx
ON price(good COLLATE "C", the_date DESC);
Read the last two chapters of the related answer I linked above.
You can even have multiple indexes with different collations on the same columns - if you also need goods sorted according to another (or the default) collation in other queries.
Normalize
Redundant strings (name of good) bloat tables and indexes, which makes everything slower. A proper table layout can avoid most of the problem. Could look like this:
CREATE TABLE good (
  good_id serial PRIMARY KEY
, good    text   NOT NULL
);

CREATE TABLE inventory (
  good_id  int  REFERENCES good (good_id)
, the_date date NOT NULL
, quantity int  NOT NULL
, PRIMARY KEY(good_id, the_date)
);

CREATE TABLE price (
  good_id  int     REFERENCES good (good_id)
, the_date date    NOT NULL
, price    numeric NOT NULL
, PRIMARY KEY(good_id, the_date));

The primary keys automatically provide (almost) all indices we need.
Depending on missing details, a multicolumn index on price with descending order on the second column may improve performance:
CREATE INDEX price_good_date_desc_idx ON price(good, the_date DESC);

Again, the collation must match your query (see above).
Since Postgres 9.2 "covering indices" for index-only scans can help some more - especially if tables hold additional columns, making the table substantially bigger than the index.
These resulting queries are much faster:
DISTINCT ON
SELECT DISTINCT ON (i.the_date)
       i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
ORDER  BY i.the_date, p.the_date DESC;

NOT EXISTS
SELECT i.the_date, p.the_date AS pricing_date, g.good, i.quantity, p.price
FROM   inventory  i
JOIN   good       g USING (good_id)
LEFT   JOIN price p ON p.good_id = i.good_id AND p.the_date <= i.the_date
AND    NOT EXISTS (
   SELECT 1 FROM price p1
   WHERE  p1.good_id = p.good_id
   AND    p1.the_date <= i.the_date
   AND    p1.the_date >  p.the_date
   );

db<>fiddle here
OLD sqliddle

Faster solutions
If that still is not fast enough, there may be faster solutions.
Recursive CTE / JOIN LATERAL / correlated subquery
Especially for data distributions with many prices per good:

Optimize GROUP BY query to retrieve latest record per user

Materialized view
If you need to run this often and fast, I suggest you create a materialized view. I think it is safe to assume, that prices and inventories for past dates rarely change. Compute the result once and store a snapshot as materialized view.
Postgres 9.3+ has automated support for materialized views. You can easily implement a basic version in older versions.


You can check if the return value for the command, if 0 then OK else failure.

Put may put it in shell script as follows:

 mysql < dump.sql 1>err 2>&1
 if [ $? -ne 0 ]; then
    echo "dump load failed.";
 fi;

or manually run echo $? and determine the result.


Optimizing PostgreSQL query with multiple subqueries

It's probably more performant to write it this way instead of querying the same table multiple times:
SELECT
    COUNT(a.review_id)::int AS total_reviews,
    COALESCE(AVG(a.score), 0)::int AS average_score,
    SUM(CASE WHEN score = 1 THEN 1 ELSE 0 END)::int AS total_1,
    SUM(CASE WHEN score = 2 THEN 1 ELSE 0 END)::int AS total_2,
    SUM(CASE WHEN score = 3 THEN 1 ELSE 0 END)::int AS total_3,
    SUM(CASE WHEN score = 4 THEN 1 ELSE 0 END)::int AS total_4,
    SUM(CASE WHEN score = 5 THEN 1 ELSE 0 END)::int AS total_5
/* ...similar syntax for your AVG calculations */
FROM
    data.users_pro_reviews a
WHERE
    a.user_id = $1

Using the SUM function with a well crafted CASE statement becomes logically equivalent to how you were previously trying to COUNT things. You can also implement similar logic for the AVG function (which I'll probably update this answer with an example for).
As per @LaurenzAlbe, this is another example you can use leveraging the FILTER keyword:
SELECT
    COUNT(a.review_id)::int AS total_reviews,
    COALESCE(AVG(a.score), 0)::int AS average_score,
    COUNT(*) FILTER (WHERE score = 1)::int AS total_1,
    COUNT(*) FILTER (WHERE score = 2)::int AS total_2,
    COUNT(*) FILTER (WHERE score = 3)::int AS total_3,
    COUNT(*) FILTER (WHERE score = 4)::int AS total_4,
    COUNT(*) FILTER (WHERE score = 5)::int AS total_5
/* ...similar syntax for your AVG calculations */
FROM
    data.users_pro_reviews a
WHERE
    a.user_id = $1

I do have to agree this syntax feels a lot cleaner.


How to insert very big XML data file to PostgreSQL

The lo_import is successful, creating a 4GB large object.

But the hard limit for the bytea datatype is 1GB, so these contents are not transferable into a bytea.


What happens when a stored procedure executes one of two different queries based on an input parameter?

To explain what Erik mentioned I created a stored procedure using your example.

--Creating table

CREATE TABLE [dbo].[Hotel](
    [HotelID] [int] NOT NULL,
    [HotelName] [nvarchar](50) NULL,
    [Rating] [nvarchar](10) NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [HotelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
--Creating stored procedure
CREATE PROCEDURE ControlledFlow
    @Flow bit
AS
BEGIN
IF (@Flow =1)
 BEGIN
  SELECT HotelID FROM Hotel
 END
ELSE
 BEGIN
  SELECT HotelName FROM Hotel
 END
END

As soon as you make the first call (irrespective of what value you pass for @Flow parameter) you get a plan for both paths. Below pictures shows both path.

In order to have separate plan for @Flow value you will need something like this.

CREATE PROCEDURE ControlledFlow
    @Flow bit
AS
BEGIN
IF (@Flow =1)
 BEGIN
  EXECUTE SubProcedure1
 END
ELSE
 BEGIN
  EXECUTE SubProcedure1
 END
END


What happens if Always On High Availability nodes have different connection default languages?

If the application is setting the language on the session when it logs in, then the default language doesn't matter.  If it's not specifically setting the language on the session, then there is risk of date strings being converted incorrectly, and dates being returned in American English format instead of British English.

Unless there is some unstated reason, I recommend simply changing the default language on the login on the secondary to match the primary.


I think what you'll need to do is create another temp table that has the same columns but with an identity column so you can use it for joining purposes.  I also noticed that your date format had day.month.year which threw me off initially....

This will get you pretty close:

create table newtable ( cid int, fromdate datetime, todate datetime)
go

insert into dbo.newtable
values
(8 , '05/01/2012', '09/01/2012'),
(8 , '09/02/2012', '09/10/2012'),
(8 , '09/11/2012', '09/18/2012'),
(8 , '09/18/2012', '10/02/2012'),
(8 , '10/03/2012', '10/04/2013')

create table dbo.newtable2 (newIdent int identity(1,1), cid int, fromdate datetime, todate datetime, targetdate datetime)

insert into dbo.newtable2 (cid, fromdate, todate, targetdate)
select cid, fromdate, todate, dateadd(m, 1, fromdate) from dbo.newtable

select cid, fromdate, todate from newtable2 where todate > targetdate

union

select cid, min(fromdate), max(todate) from newtable2 where newident in
(
select a.newident
from newtable2 a inner join newtable2 b on dateadd(m, 1, a.fromdate) > b.fromdate and a.newident = b.newident
where
a.todate <= a.targetdate
order by a.fromdate
)
group by cid

The nested select is sorted by fromdate so you will get merged records in consecutive rows so that you can leverage MIN and MAX functions.

The only thing is the UNION will not have the rows sorted but you can always dump that into another temp table and then sort it.


Is there anyway to make sql server search the master database for SQLCLR stored procedures before the current one?

I don't know a solution (and I don't know if the CLR scaffolding was ever designed to mimic the functionality you're talking about), but one workaround could be to create a T-SQL stored procedure in master that serves as a wrapper to relay the call to the CLR version. Marking it as a system object shouldn't be necessary, as long as a stored procedure with the same name doesn't exist in the user database.

EDIT - just proved that this works fine, as much for myself as anything. First I created your assembly and added the procedure necessary to repro (I'm not going to include all the bits here for brevity):

USE master;
GO
CREATE ASSEMBLY [Justin]
    AUTHORIZATION [dbo]
    FROM 0x4D5A900003000000040... lots of data here ...;
GO
ALTER ASSEMBLY [Justin]
    DROP FILE ALL
    ADD FILE FROM 0x4D6963726F... even more data here ...;
    AS N'Justin.pdb';
GO
CREATE PROCEDURE [dbo].[sp_RAISERROR_CaughtDemo]
AS EXTERNAL NAME [Justin].[StoredProcedures].[RaiserrorCaught]
GO

Then I created a wrapper stored procedure, making sure to fully qualify the CLR procedure:

CREATE PROCEDURE dbo.sp_RAISERROR_CaughtDemo_Wrapper
AS
BEGIN
    SET NOCOUNT ON;
    EXEC master.dbo.sp_RAISERROR_CaughtDemo;
END
GO

Then I adjusted your repro code to call the wrapper from tempdb instead:

USE tempdb;
PRINT 'USING tempDb';
EXEC sp_RAISERROR_CaughtDemo_wrapper;
GO

Results:

USING tempDb
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11

So this shows that you should be able to use a wrapper like this to be able to call CLR procedures, unreferenced, from other databases.

(However, I will suggest that in general this shouldn't be a goal - you should be properly defining references with three-part names where applicable.)

I'll confess I did make one change to your stored procedure though, to prevent Visual Studio from whining at me. I changed:

while (true)

To:

while (i <= 11)

But of course this change had nothing to do with the scoping issue.


Most used tables in MySQL database server

show open tables might help you or you can view queries from general log if enabled. You can also check binary logs from mysqlbinlog utility for further analysis.

Hope it help.


MySQL consuming memory, seeking advice on how to configure it

Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=20  # from 8  mysqld takes 8 minimum, keep breathing room
read_buffer_size=1M  # from 256K to reduce handler_read_next count
table_open_cache=500  # from 80  to support 327 opened since startup
innodb_buffer_pool_size=1G # from 128M for effective 750M for growth over time
innodb_log_file_size=64M  # from 8M to be larger than innodb_log_buffer_size
innodb_io_read_threads=16  # from 3 to speed up reads
innodb_io_write_threads=16  # from 3 to speed up writes
innodb_io_capacity=1000  # from 200 to increase iops limit
max_heap_table_size=16M  # from 12M to raise RAM capacity
tmp_table_size=16M  # from 12M should be same size and will reduce created_tmp_disk_tables
innodb_buffer_pool_dump_pct=90  # from 25 for reduced WARM up time
query_cache_limit=0  # from 525312 conserve RAM QC not being used

for additional suggestions, view profile, Network Profile for contact info, including Skype ID.


Try the following solution:(Is Null Or Not Like "*[!0-9]*"):


The only candidate key of your relation is {D DA HA L NF} (perhaps with R you mean D?)

You can verify this by calculating the closure of those attributes, {D DA HA L NF}+, and seeing that it contains all the attributes, while, if you remove any one of them, the closure of the remaining set does not contains all the attributes (this is the definition of a candidate key).

The relation is only in first normal form, since the second normal form requires the absence of partial dependencies, that is of dependencies in which non-prime attributes (i.e. attributes not belonging to any key) depends only on part of a key. In this case only the last functional dependency is not partial.


How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements?

This would depend on the Storage Engine.

MyISAM : Table-level locks are performed.  Consequently, all rows are locked.
InnoDB : Row-level locks are employed. Since InnoDB is transactional, all rows are locked implcitly. Those locks would be visible when you run SHOW ENGINE INNODB STATUS\G. If you want to manually lock those rows beforehand to prevent sharing, you could run SELECT ... FOR UPDATE.


AVG with OVER - invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Probably you need

SELECT date,
        Day_Name_of_week,
        AVG(AVG(SalesItems_Breakfast)) OVER (PARTITION BY Day_Name_of_week) AS BreakfastAvg
    FROM day_Sales_total
    GROUP BY date, day_Sales_total.Day_Name_of_week

So that GROUPed BY date, day_Sales_total.Day_Name_of_week average AVG(SalesItems_Breakfast) is partitoned by Day_Name_of_week and new AVG() is calculated.


Disk space full during insert, what happens?

The actual query wasn't executed in an explicit transaction though,
  can that explain the existence of the target table?

Yes, exactly so.

If you do a simple select into outside of an explicit transaction, there are two transactions in autocommit mode: the first creates the table and the second fills it up.

You can prove it to yourself this way:

In a dedicated database on a test server in simple recovery model, first make a checkpoint and ensure that the log contains only a few rows (3 in case of 2016) related to checkpoint. Then run a select into of one row and check the log again, looking for a begin tran associated with select into:

checkpoint;

select *
from sys.fn_dblog(null, null);

select 'a' as col
into dbo.t3;

select *
from sys.fn_dblog(null, null)
where Operation = 'LOP_BEGIN_XACT'
      and [Transaction Name] = 'SELECT INTO';

You'll get 2 rows, showing you had 2 transactions.

  Are the assumptions I sketched here correct? Is this a likely scenario
  to have happened?

Yes, they are correct.

The insert part of select into was rolled back, but it does not release any data space. You can verify this by executing sp_spaceused; you'll see plenty of unallocated space.

If you want the database to release this unallocated space you should shrink your data file(s).


If I understood this correctly, you can use ROW_NUMBER:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY fieldA, fieldB ORDER BY fieldC DESC)
    FROM dbo.YourTable
)
SELECT *
FROM CTE
WHERE RN = 1
;

Here is a demo of this.

The results are:

╔════╦════════╦════════╦════════╦════╗
║ ID ║ fieldA ║ fieldB ║ fieldC ║ RN ║
╠════╬════════╬════════╬════════╬════╣
║  3 ║ A      ║ X      ║      3 ║  1 ║
║  4 ║ A      ║ Y      ║      4 ║  1 ║
║  6 ║ B      ║ Y      ║      2 ║  1 ║
║  9 ║ B      ║ Z      ║      5 ║  1 ║
╚════╩════════╩════════╩════════╩════╝


When creating asymetric key from assembly does assembly need to stay on the file system

No need to keep the file. The file is read by the CREATE ASYMMETRIC KEY statement, the key is extracted and saved in the database and that's it, all further references to it will use the key from the database.

As a general rule databases try to avoid dependencies on file system because they have to work on backup restore scenarios, there could be involved in log shipping or database mirroring etc. For example when you create an assembly in SQLCLR the assembly is loaded into the database and this becomes the master copy, not the one in the file system. Is all about making the database a self contained entity. Now of course I know in the real world this story has some holes, but the CREATE ASYMMETRIC KEY is not one of them :)


I believe I was right there at the beginning. Here is what I ended up using. Please advise if anyone sees any improvements. Thanks!

WITH CTE_GAPS AS
(SELECT P.Employee, pp.FirstName, pp.LastName,
    LAG(P.PostDate) Over(Partition by P.Employee Order By
P.Employee,P.PostDate) GapStart ,
    PostDate GapEnd,
    (DATEDIFF(DAY,LAG(P.PostDate) Over(Partition by P.Employee Order By
P.Employee,P.PostDate),P.PostDate)-1) GapDays
FROM dbo.PRTH P INNER JOIN dbo.PREH pp ON P.PRCo = pp.PRCo AND P.Employee =
pp.Employee)

SELECT  P.Employee,
P.FirstName,
P.LastName,
P.GapStart,
P.GapEnd
FROM Master.dbo.Calendar C CROSS JOIN CTE_GAPS P
WHERE
GapDays>29 AND
C.Calendar_Date
    BETWEEN DATEADD(DAY,1,P.GapStart ) AND DATEADD(DAY,-1,P.GapEnd)
    GROUP BY P.Employee, P.GapStart, P.GapEnd, P.FirstName, P.LastName


Add MS SQL Server to virtual machine in Azure

SQL Azure Transparent Data Encryption is possible, but it is currently still in preview.

If you do not want to accept the supplemental terms of use for this then you can simply install SQL Server on the VM you just created or provision a new machine and use one of the numerous pre-built images that are available.

I would recommend you simply spin up a new machine as it is relatively painless, offers configuration by Microsoft, and ensures you have a SQL Server License since it is bundled with the VM image's pricing.

If you install software on a standalone VM you need a license for that software since the basic VM image is likely priced for and only includes a Windows license. There are a lot of plans and maybe you have a MSDN license... you'd have to check the details that pertain to your situation/subscription etc. to know that aspect of the decision for sure.


"Cut" records from one table into another in one oracle sql statement

Try (works on sql server)

INSERT T2 (
      col1,col2
) SELECT col1,col2
      FROM    (
           DELETE T1
           OUTPUT
                   DELETED.col1,
                   DELETED.col2
           WHERE rownum<15000
      ) AS RowsToMove;


SQL Server 2016 Log shipping restore job stuck

It probably could be for various reasons-

Compare the volume of data that might would have generated additional log backup size as compared to normal days. Compare the difference between good and bad durations vs amount of data u see.
I am not sure what is the recovery mode- do you allow secondary in read mode by keeping standby ? If yes I have seen issues and slowness based on LS logic to keep switching between no recovery mode and standby mode.
You need to have baseline for other metrics like CPU memory and storage numbers for good vs bad run.


Named and Default Instances on single SQL Cluster

What will be the consequences if I create a Named instance on the above existing setup. will the idea work ?

It should work like any other instance. Lot of people have multiple instances of SQL FCI running peacefully.

  do we need separate IP in the same VLAN for named instance and add it to cluster resources ? Also will fail-over happen as before ?

Yes, you would need a separate IP, which will be used for virtual network name. You would also need separate storage for the new SQL FCI. They all can use same network though. Failover should  work just fine and manually you would have control to just failover the FCI you like.


SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database?

The document control management system needs to perform typical document control management system tasks...for many years (at least two decades).

The end of extended support for SQL Server 2014 Service Pack 3 is 7/9/2024.  That's 5 years from now.  So I think your first step should be to see if / when the vendor intends to get certified on a more modern version of SQL Server.  This is more important if you are subject to compliance rules that require software to recieve vendor support and security updates.

As far as other things to share with executive stakeholders, it's hard to make general statements, because you certainly won't be comparing systems that have the exact same schema with the exception of the primary key data type.

If you are comparing this product to a similar one that uses surrogate (integer) keys, assuming the varchar(50) primary keys are also clustered indexes (the default for SQL Server), issues you might raise would be:

You will likely need to pay for increased disk space for the database

the varchar(50) key will take up more space on disk than the integer key
this space increase is multiplied by any nonlclustered indexes on these tables, since the clustering key is included in them automatically
it's common to say "disk is cheap," but some very smart folks have disagreed with that assessment: Disk Is Cheap! ORLY?

Because of the above, you will also spend more on disk space for backups
You will likely need to pay for more RAM

since these tables and indexes are bigger on disk, they will be bigger in RAM too

Some reports and screens might be slower to load

index scans and seeks will have to consume more pages, and would take longer than they equivalent scan of an integer-keyed-table

Some reports and screens might be faster to load

since the text of this serial number would be stored in any table that references it as a foreign key, you might be able to avoid joins sometimes

Notice all of the hedge words above like "might" and "likely" - like I said, I don't think you can make a lot of useful general statements about this situation.


Show application name instead of process in warning (PostgreSQL)

Just include %p and %a in your log_line_prefix and set log_disconnections = on.

Then you get the application name and process ID logged whenever a session disconnects. That will enable you to find in the log which application name belongs to which session. This will work unless sessions are in the habit of changing their application_name.

For active connections, just examine pg_stat_activity.


Trying to count the number of character in a string

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

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


It says like syntax error, which you missed the ,

SELECT
    Customer.CustNo,
    Customer.FirstName,
    Customer.LastName,
    `Order`.OrderNo,
    `Order`.OrderDate,
    Employee.EmpNo,
    Employee.FirstName,
    Employee.LastName,
    Product.ProdNo,
    Product.ProdName,
    CONCAT('$', ProductInOrder.Qty * SUBSTRING(Product.Price, 2)) AS `Order Cost`
From Customer, `Order`, Employee, Product, ProductInOrder
WHERE
    `Order`.Employee_EmpNo IS NOT NULL                          AND
    STR_TO_DATE(`Order`.OrderDate, '%m/%d/%Y') = '2007-1-23'    AND
    `Order`.Cost > 150
ORDER BY `Order`.Cost DESC;


What is Atomic Relation in First Normal Form

1NF requires that every attribute position in every tuple in every relation contains a single value of the appropriate type. The types can be arbitrarily complex. In fact, the types can be relations. (CJ Date's book Database in depth: relational theory for practitioners treats this issue in a way that's pretty easy to understand.)

"Atomic" has never really meant "indivisible", which is why that term is finally falling out of favor. Loosely speaking, "atomic" means if a value has component parts, the dbms either ignores the existence of those parts, or it provides functions to manipulate them. For example, a timestamp value has these parts.

Year
Month
Day
Hours
Minutes
Seconds
Milliseconds

That kind of value is obviously divisible, and all database management systems provide functions to manipulate those parts. They also provide a way to select a timestamp as a single value. (Which, of course, it is.)


Partition By A Column When The Value Isn't Continuous From One Record To The Next

Answer

Try this column definition:

, rank() over (partition by RowIndex - PreviousScanIndex order by example.RowIndex) as Testing

Explanation

This assumes - as you've stated in your question - that the rowset is ordered by RowIndex.

The problem breaks down to: how can you get a value that is constant from row to row (so we can partition by that constant) unless the difference in PreviousScanIndex row value is more than 1.

After playing around with windowed functions, partition clauses, lead and lag, it became clear it's not so easy to reference a prior row - especially with the error "Windowed functions cannot be used in the context of another windowed function or aggregate."

... but then the answer became quite simple - if you accept a couple of assumptions.

The key assumption that makes this work is that RowIndex will always increment by 1 and PreviousScanIndex will always increment by 1 also, within the partition you are attempting to define. If both values always increment by 1 within a partition, then one value minus the other will be a constant - until either value increments by some number other than 1 which, in this case, is your PreviousScanIndex jumping up by two.

The other assumption is RowIndex always increments by 1.

In fact, your PreviousScanIndex column could jump up by any number other than 1. Say it jumps up by 3, but then after this it continues to increment by 1, per row - then that is fine. Your jump by 3 simply yields a different answer to the equation RowIndex - PreviousScanIndex, but after that, the result is again the same, row by row, so long as both columns increment only by 1, from row to row.

Full query including your test data

Here is the full SQL with your test data:

select example.PreviousScanIndex, example.OperatorPartitionIndex, example.RowIndex

, rank() over (partition by RowIndex - PreviousScanIndex order by example.RowIndex) as Testing

from (values (1, 2, 1),(2, 3, 2),(3,4,3),(4,5,4),(6,7,5),(7,8,6),(8,9,7),(9,10,8),(11,12,9),(12,13,10),(13,14,11),(14,15,12),(16,17,13),(17,18,14),(18,19,15),(19,20,16),(25,26,17),(26,27,18),(28,29,19),(29,30,20)) as example(PreviousScanIndex, OperatorPartitionIndex, RowIndex) order by RowIndex

Postscript:

Extending the problem:

Order by a column where the value increments unevenly from row to row

We can actually extend this solution.

What if your RowIndex was not guaranteed to increment by exactly 1 from row to row?

Put another way - what if you wanted to sort your rows on some column, like OperatorPartitionIndex, that does not have the same incremental value from row to row?

Well actually you can do it. You simply select your source data in a CTE, then, in a second CTE you select all your source data and rank it based on the column of interest. This creates a new ranking column which does increment by 1, and we are back to the original problem, solved above.

The final step, then, is to select from this second CTE and apply the partition as described above.

Below is a full example. In this case the results will look the same as with the original question - because your OperatorPartitionIndex column is in exactly the same sequence as your RowIndex column. The important thing to note is that in the below solution we are sorting rows on OperatorPartitionIndex - and the values in this column are not evenly incremental - but we can still partition the results as required.

with SourceData as (
    select example.PreviousScanIndex as PreviousScanIndex, example.OperatorPartitionIndex as OperatorPartitionIndex, example.RowIndex as RowIndex
from (values (1, 2, 1),(2, 3, 2),(3,4,3),(4,5,4),(6,7,5),(7,8,6),(8,9,7),(9,10,8),(11,12,9),(12,13,10),(13,14,11),(14,15,12),(16,17,13),(17,18,14),(18,19,15),(19,20,16),(25,26,17),(26,27,18),(28,29,19),(29,30,20)) as example(PreviousScanIndex, OperatorPartitionIndex, RowIndex)
),
ResortedSourceData as (
select
    SourceData.PreviousScanIndex, SourceData.OperatorPartitionIndex, SourceData.RowIndex
    , rank() over (order by OperatorPartitionIndex) as NewRowIndex
from SourceData
)
select ResortedSourceData.PreviousScanIndex, ResortedSourceData.OperatorPartitionIndex, ResortedSourceData.RowIndex, ResortedSourceData.NewRowIndex

, rank() over (partition by ResortedSourceData.NewRowIndex - ResortedSourceData.PreviousScanIndex order by ResortedSourceData.NewRowIndex) as Testing2

from ResortedSourceData order by NewRowIndex

Note: Rows are ordered by NewRowIndex which is defined in the second CTE as:

, rank() over (order by OperatorPartitionIndex) as NewRowIndex

and here you see we've ordered rows by OperatorPartitionIndex, QED.


Calling/Scheduling multiple SSIS jobs for ETL process - what is one fails?

Have you considered using a SQL job to run the packages?
If each package fails when an error occurs, you can have your job exit immediately.

Just an idea...


Virtual Private Database: Create Policy function/s so that a user can view his own records or records inserted by other users with the same company ID

According to the question, all you need to do is attach a predicate on the query to customer table as shown below.

select * from customer where company_id in(select company_id from user_info where Admin='admin_user');

Right?
If yes, you have to create a function which should return a predicate as:

company_id in(select company_id from user_info where Admin='admin_user')

Well, I have done a small demo for your requirement. I have created doramail as an ADMIN user who owns the tables user_info, customer and a function called Hello_Comp_ID.

SQL> conn / as sysdba
SQL> create user doramail identified by doramail;

User Created.

SQL> grant connect, resource to doramail;

Grant succeeded.

SQL> conn doramail/doramail

Connected.

SQL> create table user_info(Company_Id number, Admin varchar2(20));

Table created.

SQL> create table customer(company_id number, name varchar2(20));

Table created.

SQL> insert into user_info values(100, 'MKarki');

1 row created.

SQL> insert into user_info values(200,'VKarki');

1 row created.

SQL> insert into user_info values(300,'CKarki');

1 row created.

SQL> insert into customer values(100,'Joe');

1 row created.

SQL> insert into customer values(200,'Jimmy');

1 row created.

SQL> insert into customer values(300,'Cashy');

1 row created.

SQL> insert into customer values(300,'Tim');

1 row created.

SQL> Commit;

Commit complete.

SQL> select * from user_info;

COMPANY_ID ADMIN
---------- --------------------
       100 MKarki
       200 VKarki
       300 CKarki

SQL> select * from customer;

COMPANY_ID NAME
---------- --------------------
       100 Ricky
       100 Joe
       200 Jimmy
       300 Cashy
       300 Tim
SQL> Create or Replace FUNCTION Hello_Comp_ID( P_SCHEMA IN varchar2, P_OBJECT IN varchar2) Return varchar2 IS
  2    V_User Varchar2(20);
  3    V_Predicate Varchar2(200);
  4  BEGIN
  5   V_User := SYS_CONTEXT('USERENV','SESSION_USER');
  6    If V_User = 'DORAMAIL' then
  7      V_Predicate := '';
  8    Else
  9     V_Predicate := 'Company_Id in(select company_id from user_info where admin='||q'[']' || V_User || q'[']'||')';
 10    END IF;
 11    RETURN  V_Predicate;
 12  END;
 13  /

Function created.

SQL> exec DBMS_RLS.ADD_POLICY('Doramail','customer','secure_select','doramail','Hello_Comp_ID','SELECT,INDEX');

PL/SQL procedure successfully completed.

SQL> select * from customer;

COMPANY_ID NAME
---------- --------------------
       100 Ricky
       100 Joe
       200 Jimmy
       300 Cashy
       300 Tim
SQL> conn / as sysdba
Connected.
SQL> create user "MKarki" identified by mkarki;

User created.

SQL> grant connect, resource to "MKarki";

Grant succeeded.

SQL> conn doramail/doramail
Connected.
SQL> grant select on customer to "MKarki";

Grant succeeded.

SQL> conn "MKarki"/mkarki
Connected.
SQL> select * from doramail.customer;

COMPANY_ID NAME
---------- --------------------
       100 Joe
       100 Ricky

Since the user MKarki belongs to company_id 100 he only can see records where company_id is 100.

In your case there might be problem in returned predicate. You can change:

V_Hello:='SYS_CONTEXT('||q'['CompAdmin']'||
','||q'['Company_ID']'||')
='||q'['Company_ID']';

To this:

V_Hello:='SYS_CONTEXT('||q'[']' || CompAdmin || q'[']' ||
','|| q'[']' || Company_ID || q'[']' ||')
='||q'[']' || Company_ID || q'[']';

If you are still having trouble then you can view the predicate being attached to your query using explain plan command. And then you can change the predicate(if there are any logical/syntactical errors in the predicate) in the function.

SQL> explain plan for select * from doramail.customer;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2021122684

--------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |   100 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |           |     2 |   100 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMER  |     5 |   125 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| USER_INFO |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COMPANY_ID"="COMPANY_ID")
   3 - filter("ADMIN"='MKarki')

Note
-----
   - dynamic sampling used for this statement (level=2)

20 rows selected.


What the graph shows is pg_dump probably starting at 19:45 and having a normal memory consumption until 20:00. At 20:00, it appears that ~30 GB are suddenly allocated and not much is released until 23:15 when presumably the Out-Of-Memory condition occurs.

From the comments, the kernel says:

hive-psql kernel: [15614.408169] Killed process 2693 (pg_dump) total-vm:38363104kB, anon-rss:34020140kB, file-rss:24kB, shmem-rss:0kB

The anon-rss of pg_dump at about 36GB shows that it is indeed pg_dump that over-allocates. There is something wrong in pg_dump needing that much memory.

Your database surely has many large objects, some of them may be very large (the theorical maximum per object is 2TB) but pg_dump processes large objects by:

opening a cursor on SELECT oid FROM pg_largeobject_metadata ORDER BY 1 and reading it by chunks of 1000 entries: that should never blow up.
for each large object, loop on lo_read to fetch the contents into a buffer of 16384 bytes: that should never blow up.

The fact that the table itself weighs 390 GB should not be a reason for pg_dump to do large allocations.

  But, first time pg_dump was killed at "retreiveing large objects"

Assuming pg_dump displays this with the -v options (by the way, you did not mention what options were passed), it should display "reading large objects" instead.