The answer is really use Hierarchical query model from Oracle. http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

So in my case:

select *
from tfinal
start with request = &search
       and type = 'CNAME'
connect by prior response = request;

The solution is simpler than what I envisioned.  Here is the real data and the query itself

select a.request,a.response,level from dnstest a  left join dnstest b on b.request=a.response||'.'  start with a.request='www.microsoft.com' connect by prior a.response=a.request||'.';

The data looks like

1,1431606892,www.microsoft.com,CNAME,toggle.www.ms.akadns.net.
2,1431606892,toggle.www.ms.akadns.net,CNAME,www.ms.akadns.net.
3,1431606892,www.ms.akadns.net,CNAME,www.microsoft.com-c.edgekey.net.globalredir
.akadns.net.
4,1431606892,www.microsoft.com-c.edgekey.net.globalredir.akadns.net,CNAME,c.edge
key.net.globalredir.akadns.net.
5,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.154
6,1431606892,www.google.com,A,173.194.121.48
7,1431606892,c.edgekey.net.globalredir.akadns.net,A,23.6.72.194


Filtering a MySQL table on multiple columns, including one with spatial data

You are saying that some type_keys are associated with thousands of rows; some with only a few?

An off-the-wall thought:

if ( SELECT id FROM edges
         WHERE type_key = ? AND deleted IS NULL
         LIMIT 100, 1 ) is NOT NULL
then
   SELECT * FROM edges ... FORCE INDEX(type_key) ...
else
   SELECT * FROM edges ... IGNORE INDEX(type_key) ...

Caveats:

The IF statements require either using a Stored Procedure or moving the IFs into your app code.
I don't know if the idea will work.
The choice of 100 needs tuning.


I think that mirroring is not the the best solution in this scenario, and discontinuation announcement is not even the main reason, especially if you're planning to maintain the test environment only for limited amount of time.

Mirroring is a high-availability solution that was designed to provide hot/warm standby db in case of failure, or offload reporting to exact copy of the database (with snapshots). So the following properties of mirroring are simultaneously its' drawbacks:

Both databases will be fully synchronized; you cannot select part of the db for mirroring, and may end up with high volumes of not needed data being transfered from principal database, reducing performance and increasing storage requirements for mirror.
Changes in principal db are reflected instantaneously on mirror db, impacting production db and increasing network traffic (as opposed to single after-hours updates).
You can only read from mirror db (through snaphot), so you won't be able to insert your own test data.

So if you don't need to update the test db from production environenment very often, the simplest solution would be to just manually restore full backups on your test db. You can even schedule your own COPY_ONLY backups and restores if you need your test db to be up to date.

You can also go with Merge Replication, which has these advantages over Mirroring:

Synchronization schedules: it won't impact production db during working hours
Filtering: you can choose what is being replicated
Conflict resolution: you can apply your own logic for handling conflict, if you want to populate your db with own test data along with production data.

Snapshot Replication and Transactional Replication are also a viable options here.

Hope this helps, and let us know what you finally chose.


Python version issue on Cassandra 3.11.2

I had the same problem as you.
I had installed python 2.7 version as mentioned at Prerequisites and the error no longer appears


MySQL Replication: Seconds Behind Master super high

When you see the Seconds_Behind_Master that high, I look at the following:

Relay_Log_Space: 8179978166

You have  7.6182GB of relay logs to process.

Master_Log_File: mysql-bin.000173
Relay_Master_Log_File: mysql-bin.000093

This tell me that you have read up to mysql-bin.000173, but you are currently processing things from the mysql-bin.000093.

This also tell me you have about 80 binary logs on the Master, each about 100 MB.

The Seconds_Behind_Master is simply the NOW() minus the TIMESTAMP set at mysql-bin.000093 (Relay_Master_Log_File) position 91173210 (Exec_Master_Log_Pos).

As long as Slave_SQL_Thread is Yes, the relay logs get processed

Relay_Log_Space will decrease each time a relay log is done
Exec_Master_Log_Pos will increase until current relay log is done, then resets to the beginning of the next relay
TIMESTAMP keeps increasing, which make Seconds_Behind_Master decrease (NOW() minus the TIMESTAMP set at Relay_Master_Log_File position Exec_Master_Log_Pos)

This is what happens when replication is off for 486330 seconds (5 days 15 hours 5 minutes 29 seconds) and you run start slave;

Look at your SHOW PROCESSLIST;. The IO Thread has been up for 66530 seconds (18 hours 28 minutes 50 seconds). This means someone or something started replication 18 hours 28 minutes 50 seconds ago.

You stated in your question that you have set up replication for the production server. This means that you ran the mysqldump 5 days 15 hours 5 minutes 29 seconds ago and started replicating from the production master 18 hours 28 minutes 50 seconds ago.

If you had setup the Slave the same day you got the mysqldump from the Master the replication load would be a lot less. Notwithstanding, replication is working normally provided Slave_IO_Thread and Slave_SQL_Thread both say Yes.


What is the signifigance of a fillfactor of 20?

@ 20 % you're consuming 5 times the disk actually needed for the index. I'm sure there's some business case where that might make sense, but I've never seen it. If you aren't rebuilding these regularly (rebuild returns to original fill factor level, in this case 20%), I'd be interested to see how full the indexes are after a month of operation. General ref: http://msdn.microsoft.com/en-us/library/ms177459.aspx


Found it:

If I restart using

service mysql restart

instead of

sudo /etc/init.d/mysql restart

It restarts fine, and I can then login to MySQL.


You need to get away from MySQL 5.1 ASAP

MySQL 5.0 Active Support ended on December 31, 2009 - Now in Extended Support Phase
Per the MySQL Support Lifecycle policy, active support for MySQL 5.0 ended on December 31, 2009. MySQL 5.0 is now in the Extended support phase.

Versions have changed rapidly since Oracle stepped in

MySQL 5.1.61 released 2012-01-11
MySQL 5.1.60 released 2011-11-18
MySQL 5.1.59 released 2011-09-16
MySQL 5.1.58 released 2011-07-05
MySQL 5.1.37 released 2009-08-02 ( pretty aged by now )

MySQL 5.1 will soon reach its EOL (End of Life) in terms of Active support and go to Extended support (EOL : TBD). Naturally, that support is commerical. It would not hold my breath for that kind of support for the Community versions.

MySQL 5.5.20 was just released 2012-01-11 and 5.5.19 was just a month ago. As with any upgraded product, always read release notes to see what changes and improvements have come about since the previous stable release.


Does MySQL or MariaDB support a LineString constructor that takes points?

No, as a general rule the constructors for MySQL are only available with non-ST_ prefixes.

So you'll want LineString(pt1,pt2)

SELECT LineString(Point(0,0), Point(0,1));


PostgreSQL - Who or what is the "PUBLIC" role?

Right from the manual:

  The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.


sum of count in where clause (or having) in the same table with group by

First limit the result to 500 (or whatever) and then GROUP BY:

SELECT
    binno,
    count(*) AS total
FROM
    ( SELECT binno
      FROM products
      WHERE location = 1
        AND sku = '4'
      ORDER BY binno             -- the order may be different
      LIMIT 500
    ) AS lim
GROUP BY
    binno ;

Optimal index for this query:

INDEX (location, sku, binno)


How to insert in table from remote stored procedure without creating a distributed transaction?

For SQL Server 2008 and up, you can disable promotions for the linked server.  It's on the Advanced property tab, or you can script it like:

EXEC master.dbo.sp_serveroption
    @server=N'YourLinkedServer',
    @optname=N'remote proc transaction promotion',
    @optvalue=N'false'

For SQL Server 2005, you can add an OLE DB linked server. That allows you to enter detailed options in the connection string.

Here's how to configure such a linked server.  Click Server Objects, then right click Linked Servers and choose New Linked Server....  Configure the server as follows:

Tab "General":

Linked server: enter name of linked server
Check Other data source
Select provider Microsoft OLE DB Provider for SQL Server
Product name: Enter anything here, like ZzzZZzzzz
Enter provider string
Data Source=<server or ip>,1433;Network Library=DBMSSOCN;Initial Catalog=<database name>;Enlist=false;

Tab "Security":

Choose Be made using this security context
Enter username and password

The Enlist=false in the connection string should prevent distributed transactions.


Oracle 10g express database link to Oracle 8i

Metalink 207303.1 is the Client/ Server Interoperability Matrix which will be the definitive documentation.  Since the express edition of 10g is based on Oracle 10.2, a database link to an 8.1.7 database should be supported.  Database links to earlier versions of 8i (8.1.5 or 8.1.6) are not supported from a 10.2 database.  For general sanity, I'd strongly suggest that the 8i side be running at least the 8.1.7.4 patchset but that isn't strictly required.


pgAdmin4 version 3.1 how to update to the last version

I found the solution, I just recreated the configuration file in the apache2 directory and restarted the httpd daemon.

This ticket can be close.


unknown SET option beginning "oracle_sid..."

These commands should be run from the OS command line not the SQL prompt.


There is a 10 minute time out for inactive cursors, and it can be overridden, but be careful: "immortal" cursors can become a problem if enough of them accumulate on the server over time.  So, it would be a good idea to close your cursor correctly from time to time and avoid that if possible.  You should also make sure that it is in fact the cursor timeout that is hurting you here (and not a TCP timeout for example).

The option to turn off the timeout involves basically setting the noTimeout flag.  In Java that means using addOption on the cursor and setting QUERYOPTION_NOTIMEOUT.


Should I pause mirroring when I reboot the witness server?

You must do this in order if you want to update the Principal, Mirror and Witness:

Switch from high-performance mode to high safety mode (if you are using this mode)
Remove the Witness
Upgrade the Witness (anytime between 2 and 9, see below)
Upgrade the Mirror(s)
Wait until Mirror(s) is(are) synchronized and manually fail Primary over
Upgrade the Primary
Upgrade the Witness (if not already done between 2 and 9)
Switch back to high-performance mode (if required)
Add the Witness


Transfering data from one MongoDB server to another while eliminating users/authentication

Yes, just db.system.users.remove({}); for each database.

It seems a bit harder to drop the system.users collection, see: https://jira.mongodb.org/browse/SERVER-4302 but it shouldn't hurt anything if it's empty.

The authentication part is enabled when you run mongod with -auth, so don't put that in your startup script or config file (comment out auth=true, or put auth=false if you're running 2.0+).


Database schema for inventory management for multiple people

I suggest this data model:

CREATE TABLE entity (
   entity_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name text NOT NULL UNIQUE
);

CREATE TABLE item (
   item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name text NOT NULL UNIQUE
);

CREATE TABLE inventory (
   entity_id bigint NOT NULL REFERENCES entity (entity_id),
   item_id bigint NOT NULL REFERENCES item (item_id),
   qualtity integer NOT NULL CHECK (quantity > 0),
   PRIMARY KEY (entity_id, item_id)
)

CREATE INDEX ON inventory (item_id);

If you know that the entity and item names can never change and are unique, you can use the name as natural primary key.

Whenever you store a new datum, you do it as follows:

INSERT INTO inventory (entity_id, item_id, quantity)
   VALUES (42, 123, 12)
ON CONFLICT DO UPDATE
   SET quantity = inventory.quantity + 12;

That will allow efficient searches and joins.

Don't create multiple tables. If the tables will become large, or you need an efficient way to delete old items, you could use partitioning. How that is best done depends on your queries and the number of distinct entities.


using mongorestore for a db present in my computer

How about reading then manual.
Simples form of that command is mongorestore  directory/ where mongod is running localhost at port 27017 and that 'directory' have those dump files.


Decrypting Previously Encrypted Data Returns NULL

This sounds like a case where the key_guid in the encrypted data does not match the key_guid for the symmetric key used to encrypt it.

When data is encrypted using a symmetric key and is then saved in a varbinary column, the first 16 bytes are the Key_guid of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT can automatically locate the correct symmetric key.

Can you run the script below and verify that this is the case? And, did you encrypt and decrypt the data using the same database?

select
    name,cast(key_guid as varbinary(max))
from sys.symmetric_keys
where
    name ='key_dbKeys'

select distinct table.data from table
where
    table.data is not null

After attempting to recreate the problem, there could be a problem opening the symmetric key prior to inserting the data or, after reviewing the documentation for DECRYPTBYKEYAUTOCERT, you need to cast the return, which is varbinary, to varchar. I'm also posting the working script that is based on what you have developed. I had to use a different password.

declare  @clean_data varchar(100)

set  @clean_data='Hello World'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword!';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'P@ssword!'
   WITH SUBJECT = 'Database encryption key',
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;
create table EncryptedData
( secretmessage varbinary(max))

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'P@ssword!'

insert into EncryptedData (secretmessage) values(EncryptByKey(Key_GUID('key_dbKeys'), @clean_data))

CLOSE SYMMETRIC KEY key_dbKeys;

SELECT cast(DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'P@ssword!', secretmessage) as varchar(100)) FROM EncryptedData


It is always a recommended practice to keep the parameters to be the same data type. This is to prevent potential implicit conversion issues with predicates which can lead to the query optimizer not being able to utilize statistics, or indexes, which leads to poorer performance, and can lead to serious CPU and storage performance issues.


Have you checked your ORAPWD file? Maybe the initial originator of the database set entries=1. That would allow 1 user to logon as SYSDBA and the second user would not be able to logon as SYSDBA. Just a thought  ;-)


Create a Function in sql server without specifying return_data_type

You can't.

The return datatype must be specified in a function. It is not an optional part of the grammar. You can return sql_variant though.


list of views that depend on a given table and column

SQL Server provides a system view, sys.sql_dependencies, which maps the dependencies consumed by objects such as views.
So, to view all the tables and columns associated with the views in your database, use the following query:
;WITH deps
AS
(
    SELECT sd.object_id
        , sd.referenced_major_id
    FROM sys.sql_dependencies sd
    WHERE sd.class = 0
        OR sd.class = 1
    GROUP BY sd.object_id
        , sd.referenced_major_id
)
SELECT ViewName = v.name
    , ReferencedObjectName = o.name
    , ReferencedColumns = STUFF((SELECT N', ' + c.name
                                 FROM sys.columns c
                                    INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                        AND c.column_id = sd1.referenced_minor_id
                                 WHERE c.object_id = sd.referenced_major_id
                                 ORDER BY c.object_id FOR XML PATH('')
                                 ), 1, 2, N'')
FROM sys.views v
    INNER JOIN deps sd ON v.object_id = sd.object_id
    INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
ORDER BY v.name;

Consider this simple test:
DROP VIEW dbo.my_view_2;
DROP VIEW dbo.my_view;
DROP TABLE dbo.my_table;
DROP TABLE dbo.my_table_2;
CREATE TABLE dbo.my_table (Column_I int NOT NULL, Column_J int NOT NULL);
CREATE TABLE dbo.my_table_2 (Column_J int NOT NULL, Column_K int NOT NULL, Column_L int NOT NULL);
GO
CREATE VIEW dbo.my_view AS SELECT mt.Column_I FROM dbo.my_table mt INNER JOIN dbo.my_table_2 mt2 ON mt.Column_J = mt2.Column_J;
GO
CREATE VIEW dbo.my_view_2 AS SELECT mt.Column_I FROM dbo.my_table mt;
GO

The results of the query above show:
╔═══════════╦══════════════════════╦══════════════════════════════╗
║ ViewName  ║ ReferencedObjectName ║      ReferencedColumns       ║
╠═══════════╬══════════════════════╬══════════════════════════════╣
║ my_view   ║ my_table             ║ Column_I, Column_I, Column_J ║
║ my_view   ║ my_table_2           ║ Column_J                     ║
║ my_view_2 ║ my_table             ║ Column_I, Column_I, Column_J ║
╚═══════════╩══════════════════════╩══════════════════════════════╝
This would limit the output to only views that depend on a specific table:
;WITH deps
AS
(
    SELECT sd.object_id
        , sd.referenced_major_id
    FROM sys.sql_dependencies sd
    WHERE sd.class = 0
        OR sd.class = 1
    GROUP BY sd.object_id
        , sd.referenced_major_id
)
SELECT ViewName = v.name
    , ReferencedObjectName = o.name
    , ReferencedColumns = STUFF((SELECT N', ' + c.name
                                 FROM sys.columns c
                                    INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                        AND c.column_id = sd1.referenced_minor_id
                                 WHERE c.object_id = sd.referenced_major_id
                                 ORDER BY c.object_id FOR XML PATH('')
                                 ), 1, 2, N'')
FROM sys.views v
    INNER JOIN deps sd ON v.object_id = sd.object_id
    INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
WHERE o.name = N'my_table_1'
ORDER BY v.name;

This could be used to show views that depend on any column in the database with a specific name column_j:
;WITH deps
AS
(
    SELECT sd.object_id
        , sd.referenced_major_id
    FROM sys.sql_dependencies sd
    WHERE sd.class = 0
        OR sd.class = 1
    GROUP BY sd.object_id
        , sd.referenced_major_id
)
SELECT ViewName = v.name
    , ReferencedObjectName = o.name
    , ReferencedColumns = STUFF((SELECT N', ' + c.name
                                 FROM sys.columns c
                                    INNER JOIN sys.sql_dependencies sd1 ON c.object_id = sd1.referenced_major_id
                                        AND c.column_id = sd1.referenced_minor_id
                                 WHERE c.object_id = sd.referenced_major_id
                                 ORDER BY c.object_id FOR XML PATH('')
                                 ), 1, 2, N'')
FROM sys.views v
    INNER JOIN deps sd ON v.object_id = sd.object_id
    INNER JOIN sys.objects o ON sd.referenced_major_id = o.object_id
WHERE EXISTS (
        SELECT 1
        FROM sys.columns c
        WHERE c.object_id = sd.referenced_major_id
        AND c.name = N'Column_J'
        )
ORDER BY v.name;


Big table size in PostgreSQL while number of rows is small

Autovacuum doesn't try to release space back to the OS. That's often counterproductive to do, since PostgreSQL would just have to allocate it again later, and allocating new storage is slower than re-using already allocated storage.

If you want to release space back to the operating system you can use VACUUM FULL on the table(s) in question, or the whole database. This will actually make tables with lots of inserts/updates slower unless you also set a FILLFACTOR of less than 100 first so there's space reserved.


Does innodb_buffer_pool_size accept "*GB" values?

Using GB or MB wont work. However, any variable that you needs to be defined as a numeric value can use the shorthand byte suffixes K, M or G.

From the docs:

  For variables that take a numeric value, the value can be given with a
  suffix of K, M, or G (either uppercase or lowercase) to indicate a
  multiplier of 1024, 1024^2 or 1024^3. (For example, when used to set
  max_allowed_packet, the suffixes indicate units of kilobytes,
  megabytes, or gigabytes.)

http://dev.mysql.com/doc/refman/5.6/en/program-variables.html

So, if you wanted 1024MB then you would use 1024M or 1G


Please visit https://stackoverflow.com/questions/18341607/drop-schemabinding-from-all-views-in-a-database

One way would be to highlight the Views node in Object Explorer, then hit F7 (View > Object Explorer Details). On the right will be all of the views; unfortunately you can't filter or add a new column to sort based on whether the view does have SCHEMABINDING. So you'll have to manually select the ones you want (or just select all except the System Views folder), then right-click, Script View As > DROP and CREATE to > New Query Editor Window. Now you can just search that script for SCHEMABINDING, delete or comment out each instance, and then run the script. If some of the views you selected did not have SCHEMABINDING in the first place, then no big deal - they'll just get dropped and re-created too.

(This is much easier, in my opinion, than trying to search and replace CREATE VIEW with ALTER VIEW - since there could be any number of whitespace characters between CREATE and VIEW, and you'll still have to comment or remove the SCHEMABINDING. Though it might not hurt, as part of this exercise, to ensure that the syntax conventions here are consistent, and fix views that don't adhere, e.g. changing CREATE______VIEW to CREATE_VIEW (underscores to represent spaces).)

Note that this will obviously delete any indexes that are associated with these views.

Also note that the generate scripts wizard will also allow you to select all of the views, but not filter on whether they have SCHEMABINDING, and the wizard won't script ALTER but you can choose DROP and CREATE in Advanced settings. You'll still need to adjust the output to remove the SCHEMABINDING and, depending on options selected, you may need to remove any CREATE INDEX statements for indexed views that will now fail.


How to multi-match two tables efficiently?

3-table scheme is the most compatible. 3rd table will look like:

CREATE TABLE jointable
(
    obj_id,
    cat_id,

    PK (obj_id, cat_id),
    FK (obj_id) REF obj (id),
    FK (cat_id) REFcat (id)
);


Connecting to Active Active Cluster Services

First off, there is no such thing as an "active/active" cluster.  That is a huge misnomer, and can lead to a large amount of confusion.  What you have is two failover cluster instances in a Windows failover cluster with at least two nodes.

Secondly, this will not be possible if you are dealing two failover cluster instances.

  I wish to now have the application connect to SQLINST2 whenever SQLINST1 fails

Which nodes are the possible owners of the cluster group SQLINST1?  I'm guessing both nodes would be able to own the FCI (failover cluster instance)?  If so, it'd be a rather corner-case where both either node is still up, with SQLINST1 down but SQLINST2 is still alive and well (it could happen, just would be a typical failure).

Something you could consider, seeing as you are using SQL Server 2012 is AlwaysOn Availability Groups.  What you could use here (provided that the rest of the functional capabilities meet your implementation requirements:  AGs are a very robust technology) is an availability group listener to act as the client abstraction (it's just a virtual network name within the AG cluster resource group of the WSFC).

It does sound as though you want/need automatic failover in this case.  Unfortunately, automatic failover between a FCI replica and another node isn't currently supported, do you'd need to revert to standalone instances for your AG replicas.  Not necessarily a bad thing, but surely worth thinking about in the initial stages of infrastructure design.


Drill Down Functionality for Data Entry in Database

One of my favorite approaches to problems like these in the context of data warehousing usually takes the form of an agile design practice, where the granularity of a system does not need to always be "rolled up" from the lowest level right away, but can be, as stated in the title of the topic "drilled down" into, as the finer granularity details become available.

Assuming you have some table Expense with columns Expense_PK, CompanyName and TotalExpenses,  I would simply create a new table, ExpenseDetail, containing columns ExpenseDetail_PK, Expense_FK, Taxes and OtherExpenses, adding a detail record when one was available.

The nuance of doing this is to be stalwart in maintaining the detail record properly.  Should the nature of OtherExpenses be revealed to be solely comprised of Adjustments, Dues and Assessments, it is often far better to extend the detail record with the additional atomic columns for each of the now determinant fees than to add another OtherExpensesDetail table to the mix.  On the other hand, in Canada anyway, the Taxes column would pose a more than reasonable opportunity for a TaxesDetail table, defined TaxesDetail_PK, ExpenseDetail_FK, GST, PST, so that Company A could provide additional tax breakdowns in the form of Federal and Provincial taxes, where as theoretical Company C could simply be allocated the aggregated Taxes value in the detail record if that was all that was available or were operating in a province that only applied a singular harmonized sales tax ( HST ).

That said, I feel there are many ways of going about problems like these, but this is at least one example of something that is quite versatile and works quite well for what it is.


How much does an index need to narrow the results of a search in order to be useful?

The diversity of a column's data is known as selectivity.  Selectivity is useful to know when determining whether an index will be useful, but it is not the only thing that determines the speed benefit.  Other factors include how fast the storage the index is on compared to the table, how much of the table/index is already cached, how large the index is in comparison to the table, and several other things.

Knowing the data-type of the column does not necessarily help us determine how selective an index on the column will be.  Even a column constrained to two values might use those values for only a few rows and have the remainder NULL.  On the other hand, a column that could have many distinct values could have the same value in every single row.  Even with your id column where all the rows would have unique values, if you are searching for rows with an id >= 10, the index probably wouldn't be useful even though it is highly selective.

You can't use selectivity alone to determine whether an index will be useful or not because even if it returns 100% of the rows, if the index includes all the data necessary for the query it will be faster than using the table.  On the other hand, for a small table it may be faster to query the whole thing even if the row being sought only represents 1% of the total.

Determining what indexes should be created is less about looking at the table structure than it is looking at the important queries and what data they need to retrieve.


Why does SQL Server start REDO phase from minLSN instead of last CHECKPOINT?

The Database Checkpoints doc says that

  For performance reasons, the Database Engine performs modifications to
  database pages in memory-in the buffer cache-and does not write these
  pages to disk after every change. Rather, the Database Engine
  periodically issues a checkpoint on each database. A checkpoint writes
  the current in-memory modified pages (known as dirty pages) and
  transaction log information from memory to disk and, also records the
  information in the transaction log.

So the CHECKPOINT is not directly related to transactions, instead it's related to dirty pages which might not belong to the same transaction and that explains why the recovery process of a database must consider the MinLSN instead of the last CHECKPOINT as you suggested.

If you take a look at the structure of the Transaction Log Physical Architecture, you'll see that a checkpoint can happen in a VLF that is in front of the oldest active VLF as the following picture ilustrates:

With that in mind, think that a really long transaction (T1) started using Virtual Log 3 and only completed by the end of Virtual Log 4. During the execution of T1 another transaction happened (T2), but this one started and completed after the CHECKPOINT. Now imagine a crash happened. If the recovery process would consider as starting point the CHECKPOINT, it would be able to redo T2, but not to grant a consistent state to the recovered database for the crash of the database happened after the CHECKPOINT but before T1 had completed, leaving half a transaction registered when it should be rolled back.

You can also see that example on the picture in your question where the Oldest uncommitted Tx started before the Last Checkpoint.


Back when SQL Server first implemented the WEEK date/part, they had to make a choice. I don't think there was really much consciousness about it, except to align to the most common standard at the time - remember this was at a time where conforming to the standards was not a top priority (else we'd not have things like timestamp, IDENTITY and TOP). They later added ISO_WEEK (2008 I believe) because the workaround in the meantime was to write your own, slow, crappy scalar UDF - in fact they even created a really bad one and put it in the official documentation (it has since been removed as far as I can tell).

I don't know of a way to make DATEPART(WEEK pretend it is DATEPART(ISO_WEEK - I think you will have to change the code (and if you are using source control, this shouldn't be very hard - how many places are you performing this calculation? Have you thought about computing it somewhere so your code doesn't have to be riddled with it? Since you're changing the code now, this might be the time to consider this...).

And if you really want the answer to why? I think you'll have to grab some of the original developers to determine why they chose the default that they did. Again, I think it wasn't an actual "F the standards!" choice, but rather, "What standards?"

There is some information here that may be useful:

https://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/iso-week-in-sql-server


One of the problems with the regexp_substr() calls in your procedure is: sometimes, they return incorrect values - which will be assigned to the 3 variables (a,b,c).  Suppose that there always are 3 "tokens" (first/middle/last name), delimited by '/'.  Each token can either be a "name" or null.  Let's generate some test data, and apply the regexes that you have used in your procedure (Oracle 12c):

create or replace view test_data
as
select
  rownum as id
, dt.combinations
, regexp_substr( dt.combinations, '[^/]+', 1, 1) var_a
, regexp_substr( dt.combinations, '[^/]+', 1, 2) var_b
, regexp_substr( dt.combinations, '[^/]+', 1, 3) var_c
from
(
  select
    decode ( T1.token, null, '/', T1.token || '/' )  ||
    decode ( T2.token, null, '/', T2.token || '/' )  ||
    decode ( T3.token, null, '' , T3.token )  as combinations
  from
    ( select null as token from dual union select 'sharmi' from dual ) T1
  , ( select null as token from dual union select 'devi' from dual )   T2
  , ( select null as token from dual union select 'elango' from dual ) T3
) dt ;

Selecting from the view gives us:

select * from test_data;

ID  COMBINATIONS        VAR_A   VAR_B   VAR_C
1   sharmi/devi/elango  sharmi  devi    elango
2   sharmi/devi/        sharmi  devi
3   sharmi//elango      sharmi  elango        -- incorrect (should be: var_a and var_b)
4   sharmi//            sharmi
5   /devi/elango        devi    elango        -- incorrect (should be: var_b and var_c)
6   /devi/              devi                  -- incorrect (should be: var_b)
7   //elango            elango                -- incorrect (should be: var_c)
8   //

Instead of using a procedure, you could use a function (original code: see https://community.oracle.com/thread/925201?start=0&tstart=0 , accessed on 2018-01-06) that returns a table of varchar2, like so:

create or replace type split_tbl as table of varchar2(32767);

create or replace function split3
(
    p_list varchar2,
    p_del varchar2 := '/'
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row( substr( l_list,1,l_idx-1 ) );
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split3;
/

For testing this function, you could use something like (explanation below):

declare
  combination_ varchar2(128) := '' ;
  result_ split_tbl ;
  type labels is varray(3) of varchar2(16);
  n_label labels := labels(' first name: ',' middle name: ',' last name: ');
begin
  for i in 1 .. 8
  loop
    select
      ' [test case ' || to_char(id) || ']  string -> ' || combinations
    into combination_ from test_data where id = i;

    select * bulk collect into result_
    from table ( split3( ( select combinations from test_data where id = i ) ) ) ;

    dbms_output.put_line( combination_ ) ;

    for token in 1 .. 3
    loop
      dbms_output.put( n_label(token) ) ;
      if result_(token) is null then  dbms_output.put_line( 'null' ) ;
      else dbms_output.put_line( result_(token) ) ;
      end if;
    end loop;

    dbms_output.put_line( '-------------------' ) ;
  end loop;
end;
/

The anonymous block uses an array of varchar2 values containing labels.  It picks up each of our test cases (from the test_data VIEW), calls the "split" function, and labels (and outputs) its return values.

-- output (anonymous block):
[test case 1]  string -> sharmi/devi/elango
 first name: sharmi
 middle name: devi
 last name: elango
-------------------
 [test case 2]  string -> sharmi/devi/
 first name: sharmi
 middle name: devi
 last name: null
-------------------
 [test case 3]  string -> sharmi//elango
 first name: sharmi
 middle name: null
 last name: elango
-------------------
 [test case 4]  string -> sharmi//
 first name: sharmi
 middle name: null
 last name: null
-------------------
 [test case 5]  string -> /devi/elango
 first name: null
 middle name: devi
 last name: elango
-------------------
 [test case 6]  string -> /devi/
 first name: null
 middle name: devi
 last name: null
-------------------
 [test case 7]  string -> //elango
 first name: null
 middle name: null
 last name: elango
-------------------
 [test case 8]  string -> //
 first name: null
 middle name: null
 last name: null
-------------------

One of the advantages of the "split" function is: it can also handle more than 3 tokens.  Eg

SQL> select * from table(split3(
  2  'João/Alexandre/Duarte/Ferreira/Fernandes'
  3  ))
  4  ;
COLUMN_VALUE
João
Alexandre
Duarte
Ferreira
Fernandes


Don't. Instead update to PostgreSQL 9.2.23, which includes a better fix.

If you really must patch it: clone PostgreSQL's source code from git, check out 9.2, apply patch, compile.

git clone git://git.postgresql.org/git/postgresql.git
cd postgresql
git checkout REL9_2_STABLE
patch -p1 < ~/Downloads/pg_dump_grouplock.patch
./configure ...

See the PostgreSQL documentation for compile instructions.


Nothing here immediately looks like a Sharding issue. Sharding will have a more significant impact on your Reads than your Writes. There are other areas, for Write performance, I would explore prior to spinning up more resources. There is also a larger topic of how your collection is sharded. You could easily, as per your description, have some documents be updated more than others. You could easily have hotspots in an improperly sharded cluster thus sharding will provide you little performance benefit.

MongoDB has changed since your question was posted. Circa 2014; you can now better leverage bulk operations. In other words, one request could carry more operations. That itself may already resolve the presumed issues you are referring to and eliminate the need for a middleman. If it does not, then yes you could use your proposed middleman approach. I work on a project that utilizes such a solution to pump much more data into a 3 shard development environment with an eye towards 17 shards. That is mainly due to C# driver inefficiencies.

Prior to a new shard, I would first explore setting your read preference to SecondaryPreferred. This will allow your primary node in each RS to leverage more resources for Write operations.

One could also argue you should reexamine how your documents are modeled. You may benefit from consolidating some documents if the Read operations tend to query a lot of pseudo-related data across many documents.


Postgresql streaming replication slow on macOS

You can  checked max_standby_streaming_delay (30s default) or max_standby_archive_delay (30s default) and recovery_min_apply_delay and checkpoint_flush_after (32 default).


alter public database link to private in oracle

No.  You'll need to create a new private database link.  Public database links (and synonyms) are owned by a different user than private database links (and synonyms) and you can't alter an object to change the owner.


Question marks instead Hebrew characters after mysql dump and import

I think your problem is that you have exported your dump file with ANSI character set, to take a dump file using UTF8, issue your mysqldump command with option --default-character-set=utf8 and check the results. (AFAIK the same options for mysqldump work for Aurora as well)

PS : After your comments, I dug the problem a bit more. The problem comes from different configurations for character-set-system (which is utf8), character_set_server (which is latin1) with character-set-client (which is utf8mb4). Based on this link to documentation, this difference might cause issues in data input and may result in bad format in output. You can check the validity of this root cause by changing your character sets for different components.


Attached database to SQL Server 2016 and now won't work in SQL Server 2014

When you attached the mdf file(s) to the SQL Server 2016 instance the database was upgraded. The files were marked as being of the new version. There is no downgrade path in SQL Server.

The best option is restore from backup. Else, you many be able to script the schema and data and build a new database on the 2014 instance which is identical to the previous one. Here's one way of achieving this. Be aware of the things the tool may not script, like users, permissions, synonyms etc.

See also: How to move a database from SQL Server 2012 to SQL Server 2005


Significant performance differences executing alter and grant statements between users with same permissions

TL;DR; Check database triggers
It turns our this was a very narrow, unique issue - but one that others might experience in a similar setup.
The performance issue was being caused by a database trigger that was called on grant, create, and alter statements, and based on the user, executed additional (sometimes heavy) logic.
These triggers were running extra logic for the accounts I was testing with, but not the original account.


Can I use only 1 user for SQL Server replication?

If you use 1 user, you typically end up giving that user more access than it needs. By having the example use 4 different users, it helps demonstrate the good practice of using dedicated accounts for particular purposes. If one is compromised, there’s less impact on the overall system. If the example used just one user, everyone would assume it had to be that way.


Is a table without a primary key normalized?

If a relation does not have any candidate key (and the primary key is just one of the candidate keys), then it can have duplicate rows, so in fact it is not a relation! (since relations are always sets).

In that case, it is more correct to call it a table, not a relation, as you did in your question, and note that several RDBMS in effect can manage not-relations, allowing tables without any constraint of uniqueness, even if this case is very uncommon, and produces problem (anomalies) when operating on the data.

But in this case talking about normal forms is not appropriate: all the normalization theory is based on the fundamental assumption that the objects of interest are relations, not multisets. In fact this theory is based on the (somewhat discussed) Universal Relation Assumption, that assumes that all the relations of a database are a subset of a projection of such relation, that contains all the attributes in every relation. And that object is in fact a relation (i.e. a set), not a multiset.

The fact that, when talking about data of the Relational Data Model, sometimes we exchange the two terms, tables and relations, does not means that they are actually synonym, and this difference is fundamental when we talk about normalization theory. Remember that in the books, when some normal form is introduced, it is always said something like:

  A relation is in xxx normal form if...


You can create the table - Transactions: po_id, client_id, transaction_type, transaction_purpose_id (you can create the table transaction purposes (products, services, taxex and so forth) amount_paid, transaction_status (processing = 1, proccessed = 2, failed = 3, unknown = -1 and so forth).

Now, a clien can pay for an order however he wants. Taxex can be paid as well.


According to the official documentation:

  The buffer pool extension size can be up to 32 times the value of max_server_memory. We recommend a ratio between the size of the physical memory (max_server_memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 may be optimal.

If you have, say, 64 GB of physical RAM, your Max Server Memory setting would be 54272 MB (53 GB), which means your BPE should be a maximum of 848 GB (53 * the ratio of 16) or less, with 424 GB or 212 GB recommended for optimal size.

When you configure the BPE size with ALTER SERVER CONFIGURATION, that is the size of the cache, and is how large it will be. It will not grow unless you change it.

So make sure you pick the right size to begin with, and make sure your SSD is fast enough.

In summary (from the comments):

The maximum file size of BPE should be set as a part of the initial file creation, and this file size should be in a 1:4 ratio of Max Server Memory to Buffer Pool Extension, as per Microsoft's recommendation.
In practice, the BPE size is approximately the size of your flash storage, less the size of existing TempDB on that drive. In other words, you would use the leftover space on your PCIe/NVMe flash disk after sizing TempDB.


Database : what design to point on two possibilities?

It seems, that you don't need the actor_type table at all, since there is just those two types. As the column and table names are already human readable, even more actor types could be added the same way.

The actor type is neither necessary for ruling out which one this actor is, since the other(s) can be left NULL. Only one being NOT NULL can also be made mandatory with constrains, e.g.

ALTER TABLE actor
ADD CONSTRAINT OnlyOneActor
CHECK (
   (CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
     + CASE WHEN person IS NOT NULL THEN 1 ELSE 0 END)
   = 1
)

However, this won't actually work as supposed in MySQL: Reference Manual states that

  The CHECK clause is parsed but ignored by all storage engines.

You can achieve the same functionality in MySQL by using a TRIGGER instead, e.g.

DELIMITER //
CREATE TRIGGER OnlyOneActor BEFORE INSERT ON actor
FOR EACH ROW BEGIN
  IF (
     (CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
       + CASE WHEN person IS NOT NULL THEN 1 ELSE 0 END)
     != 1) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Only one type of actor allowed.';
  END IF;
END//
DELIMITER ;


Tiny query used to be sub-second speed, now takes 60+ seconds

the sub-select by itself still takes less than a second and returns
  about 20 rows ... Any suggestions?

In that case you may as well materialize it into a #temp table and join onto that instead.

SELECT BS.[database_name],
       DATEDIFF(HH, MAX(BS.backup_finish_date), GETDATE())                 AS last_backup,
       DATEDIFF(MI, MAX(BS.backup_start_date), MAX(BS.backup_finish_date)) AS last_duration
INTO   #BS1
FROM   msdb.dbo.backupset BS
WHERE  BS.type = 'L'
GROUP  BY BS.[database_name];

SELECT D.name AS [database_name],
       D.recovery_model,
       BS1.last_backup,
       BS1.last_duration
FROM   sys.databases D
       LEFT JOIN #BS1 BS1
         ON D.name = BS1.[database_name]
ORDER  BY D.[name];

DROP TABLE #BS1 ;


Lets take your problem step by step:

  It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts.

Whenever you do large updates/inserts to you tables, highly recommend to update stats and reorg/rebuild indexes. That way query optimizer does not select or produce bad plans on wrong estimates.

  In order to fix the problem I run the Database Tuning Advisor and I added some Indexes and Statistics.

Never do that without understanding your workload and proper testing the recommendations. Refer to Don’t just blindly create those “missing” indexes! by Aaron Bertrand.

  I also created a maintenance plan to rebuild indexes daily.

I would recommend you to look at the fragmentation ratio of the indexes and accordingly reorganize them or rebuild them. Best is to use SQL Server Index and Statistics Maintenance as it is best free software out and implemented widely.

  Next, life forced me to clean up the table in matter, and the amount of rows in it is even smaller now than it was before but the timeouts are still happening. So, I removed all indexes that I created and now the website is much more stable but from time to time I still see some timeouts.

Same as above. Now the optimizer has wrong statistics and hence inefficient query plan can be produced. Best is to UPDATE STATISTICS and mark that table for recompile using sp_recompile , so next time the optimizer will generate new plan based on updated stats available.

Also read up on : Slow in the Application, Fast in SSMS?


Sort for specific column by keep other column grouped


I'm not an RD Gateway Expert, but I think you don't have to and you can't use the RD Gateway Server for Database Connections through ODBC.
If you want to access an SQL Server from Outside, you will be better with a VPN Server.

The RAS Gateway could fit your needs -> https://docs.microsoft.com/en-us/windows-server/remote/remote-access/ras-gateway/ras-gateway).

Alternativly, you can connect with RDP through your RD Gateway Server to a RDP Server inside your company and start your application with the ODBC connection.

Best Regards,
Björn


You want to do the BEGIN TRANSACTION prior to the BEGIN TRY:

BEGIN TRANSACTION
BEGIN TRY
    DELETE FROM dbo.LocalSummeryRealTable;
    INSERT INTO dbo.LocalSummeryRealTable
    SELECT S.*
    FROM  #LocalSummery' AS S;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
       IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
END CATCH

Also, please schema-qualify references to database objects such as tables, views, stored procedures etc.  Also, you should get in the habit of adding semi-colons to the end of each statement.

SET XACT_ABORT ON; is not necessary if you are explicitly rolling back the transaction inside the CATCH block.


You can use the IF() function, VERSION() function and Dynamic SQL:

SET @dynamic_func_name = CONCAT(IF(LEFT(VERSION(),3)>'5.6','ST_',''),'GeomFromText');
SET @sql = CONCAT('CALL ',@dynamic_func_name,'(wkt,srid)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

I used the left 3 character of the VERSION() to get the major release. Why ?

If LEFT(VERSION(),3) returns 5.7 or more, @dynamic_func_name will be ST_GeomFromText
If LEFT(VERSION(),3) returns 5.6 or less, @dynamic_func_name will be GeomFromText


Why RDS PostgreSQL `MaximumUsedTransactionIDs` not decreasing even after data base seems correctly `VACUUM FREEZE`'d?

Activate autovacuum in the parameter group and when it hits the configured level (min 100.000.000) it'll start and MaximumUsedTransactionIDs will go down drastically.


For how long postgres will keep alive connection after changing credentials?

A PostgreSQL instance will keep a client connection up and running until the connection either reaches a specified client timeout or the client (application) closes the connection.

A change of password has no effects on existing connections and will only affect new connections.

There is no mechanism that constantly checks if the connection is still "password valid". A client connection is a one time authentication and if it was valid at the time of authentication will keep on being valid until closed.


show con_name not working in SQLDevelopper

SHOW is a SQL*Plus command, but SQL Developer does support all of the SQL*Plus commands

you run them through the 'script engine' which is done using F5 or the 2nd execution button on the toolbar

you will need to be on a newer version of SQL Developer as we've vastly increased the amount of SQL*Plus support we offer


SQL Server 2008 and constant expressions

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant Functions to get a grasp what is 'constant' in SQL to start with.

See http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/ for an example when SQL Server does not do boolean short-circuit.


If you want previousRank to end with a comma

SET @r=0;
UPDATE ranks SET
    previousRank=CONCAT(previousRank,rank,','),
    rank= @r:= (@r+1)
    ORDER BY points DESC
;

If you do not want previousRank to end with a comma

SET @r=0;
UPDATE ranks SET
    previousRank=IF(previousRank='',rank,CONCAT(previousRank,',',rank)),
    rank= @r:= (@r+1)
    ORDER BY points DESC
;

Give it a Try !!!


How do transactions help resilience to system failure?

If I understand your question, the "resilience to system failure" is handled by the server to ensure that the transaction either:

Succeeds in committing the transaction.

Or:

Ensures that a non-committed transaction rolls back to the previous state.

In other words, the transaction completes successfully or else (in a failure) all the in-flight changes are undone since the transaction is incomplete.


MySQL Explain: Using index, using temporary, using filesort. Can this query be improved?

Here is your original query

SELECT
    el.object_id,
    leo.object_desc,
    COUNT(el.object_id) as count_rows
FROM
    event_log el
    LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY
    el.object_id
ORDER BY
    count_rows DESC,
    leo.object_desc ASC
;

It looks as if it is as optimized as it is going to to get

I would change you query

SELECT
    el.object_id,
    leo.object_desc,
    SUM(ISNULL(leo.object_id)=0) as count_rows
FROM
    event_log el
    LEFT JOIN lookup_event_objects leo ON leo.object_id = el.object_id
GROUP BY
    el.object_id
ORDER BY
    count_rows DESC,
    leo.object_desc ASC
;

This might get you a more accurate count, especially for event logs with no lookup objects.


You could create tables for Attributes and its values. A simplified schema below:

CREATE TABLE [dbo].[AttributesList](
    [Id]            INT IDENTITY(1,1) NOT NULL,
    [ObjectType]    VARCHAR(32) NOT NULL,  -- wich objects have the attribute
    [AttributeCode] VARCHAR(32) NOT NULL,  -- short attr identifier
    [Kind]          INT NOT NULL,          -- kind of the attribute's value
    [Name]          VARCHAR(80) NOT NULL
)

CREATE TABLE [dbo].[AttributesValues](
    [Id]            INT IDENTITY(1,1) NOT NULL,
    [ObjectId]      INT NOT NULL,  -- object.object_id
    [Kind]          INT NOT NULL,          -- AttributesList.Id
    [Code]          VARCHAR(32)  NULL,  -- for short strings
    [Flag]          INT          NULL,  -- for int values
    [Info]          VARCHAR(250) NULL,  -- for long strings
    [Amount]        MONEY        NOT NULL  -- for money
)

You'll be able to add as many attributes for some ObjectType as you need, and store their values in AttributesValues table in a column that depends on [AttributesList].Kind value.


There are some issues worth clarifying on your end. For example, is the 4kb a single 'blob' or a set of entities/rows, and if it's a set of rows, why exactly do you need to batch it up into 4kb chunks. Otherwise it's better to let Postgres handle the 'chunking' for you. On a different note, the best place to start with locks is the select ... for update skip locked query and from there you can build a work-queue/ pub-sub atop Postgres


Update datatype of primary key without deleting the table

You cannot change datatypes of Key fields (Primary or Foreign). You can't even change a datatype if the field is used in an index. So...

If there is no data in these tables (obviously much easier):

Drop Foreign Key on [Addresses] that references [State]
Drop Primary Key on [State]
ALTER TABLE [State] DROP COLUMN [StateID];
ALTER TABLE [State] ADD [StateID] INT IDENTITY(1, 1) NOT NULL;
Recreate Primary Key on [State]
ALTER TABLE [Addresses] ALTER COLUMN [StateID] INT NOT NULL;
Recreate Foreign Key on [Addresses] that references [State]

If there is data in these tables (and you have a maintenance window):

ALTER TABLE [State] ADD [StateID2] INT IDENTITY(1, 1) NOT NULL;
ALTER TABLE [Addresses] ADD [StateID2] INT NOT NULL CONSTRAINT [DF_Addresses_StateID2] DEFAULT (0);
Update the [Addresses] table to match the new IDs in the [State] table

UPDATE addr
SET    addr.StateID2 = st.StateID2
FROM   Addresses addr
INNER JOIN [State] st
        ON st.StateID = addr.StateID

Drop Foreign Key on [Addresses] that references [State]
Drop Primary Key on [State]
sp_rename [State].[StateID] -> [StateIDold]
sp_rename [State].[StateID2] -> [StateID]
Recreate Primary Key on [State].[StateID]
ALTER TABLE [Addresses] DROP CONSTRAINT [DF_Addresses_StateID2];
sp_rename [Addresses].[StateID] -> [StateIDold]
sp_rename [Addresses].[StateID2] -> [StateID]
Recreate Foreign Key on [Addresses].[StateID] that references [State].[StateID]
Update Stored Procedure(s) or whatever processes/code insert (and possibly update if the StateID field can change) into the [Addresses] table
After you are sure that all data is there and matches, then you can drop the [StateIDold] field in both the [State] and [Address] tables.

If there is data in these tables (and you do not have a maintenance window):

If there is no maintenance window then you have to not only deal with contention on the table (and adding / removing fields requires a SCHEMA lock), but you also need to update the process that adds data to the [Addresses]

Build copies of both tables [Addresses2] and [State2] with the desired structure (but no FK)
Copy the data over to the new tables
make sure no data in [Addresses] can change (this is very temporary)
rename current tables (e.g. [Addresses] -> [AddressesOld])
rename new tables (e.g. [Addresses2] -> [Addresses])
Add FK
Be sure to update code that inserts into Addresses
Allow changes in [Addresses] again

For a more detailed explanation of this "no maintenance window" approach, please see the article I wrote about it on SQL Server Central (free registration required): Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!


Where am I going wrong while trying to install sample database in MySQL?

The answer was, the file was not in the Folder C:/SampleDB
the screenshot is from the temporary unzipped file.
After creating the foldeer and copying  the files, all was in order.


SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Since you are using SQL Server 2008 R2 you can simply create your SQL Agent Job with the step configured as a "PowerShell" type, instead of trying to use the CmdExec. You only need to include the "meat" of your script. Since you are using the PowerShell type of a SQL Agent step it has already called the powershell.exe and imported the SQLPS module for you. So an example:

With your code I believe you can simply configure the step as shown below:


(MySQL) How to change the results of a view according to the user that's viewing it?

A hint from the current_user() docs:

  For stored procedures and functions and views defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER() returns the object's invoker.

On the other hand, in PostgreSQL you can do the same (with the current_user function, which behaves exactly as desired), and with good design and config the performance wouldn't be any worse - however, I don't think that this feature would make migrating to PostgreSQL any sense (especially given that your app code is closed...)


You're right, excuse any errors or agreement error.

My question is a concept so much, but explain.

I have users who have their logins, but there is a login that has some privileges that also and the same User used in important processes in the environment and is in source code.

At one point in the past knew the password for this login, and the more I say to access the environment with their respective login and password in some cases, they use this login with privileges.

As I have not, by default, a native audit Oracle configured in my environment, I wonder if through tables and views I can map out what such User made using a PL SQL DEVELOPER.
The worst and I have no time available to make this change, let offline the database, adjust and available.


Is it better to separate a big query into multiple smaller queries?

I am going to disagree on large and complicated queries with datagod here.  I see these only as problems if they are disorganized.  Performance-wise, these are almost always better because the planner has much more freedom in how to go about retrieving the information.  However, large queries do need to be written with maintainability in mind.  In general, I have found that simple, well-structured SQL to be easy to debug even when a single query goes on for 200+ lines.  This is because usually you have a pretty good idea of what kind of problem you are dealing with so there are only a few areas in the query that you have to check.

The maintenance problems, IME, come in when the structure of SQL breaks down.  Long, complex queries in subselects impairs readability and troubleshooting, as do inline views, and both of these should be avoided in long queries.  Instead, use VIEWs if you can (note if you are on MySQL, views do not perform all that well, but on most other db's they do), and use common table expressions where those don't work (MySQL doesn't support these btw).

Long complex queries work pretty well both from a maintainability and performance case where you keep your where clauses simple, and where you do as much as you can with joins instead of subselects.  The goal is to make it so that "records aren't showing up" gives you a few very specific places in the query to check (is it getting dropped in a join or filtered out in a where clause?) and so the maintenance team can actually maintain things.

Regarding scalability, keep in mind that the more flexibility the planner has, that's a good thing too....

Edit:  You mention this is MySQL, so views are unlikely to perform that well and CTE's are out of the question.   Additionally the example given is not particularly long or complex so that's no problem.


From ask.libreoffice, as linked on SuperUser

  With the native postgreSQL connector the connection string is a little
  different. It is not a URL at all, but rather just a text string made
  up of propertyname=value segments, separated by a space.

  The required fields (properties) are, database name and host (or
  hostaddr). If the postgreSQL database is using a non standard port
  (5432) then that must be included also.

  So, if I want to connect to a database, using the default postgres
  configuration, named postgres on my local machine the simplest
  connection string would be: dbname=postgres host=localhost

  Now let's say that there is an older postgres engine running using the
  standard 5432 port and I want to connect to a newer one using port
  5433, and that I want to connect with my user name db_user, with a
  password of libreoffice and I don't want to type that password in each
  time I connect, then the connection string might look like:

dbname=postgres hostaddr=127.0.0.1 port=5433 user=db_user password=libreoffice

  Note that I've used the hostaddr, instead of host here, bypassing any
  need to do a DNS lookup.

  Finally, with the native connector all options in the connection
  string are accepted - you can find a full list of those here:
  PostgreSQL Docs Just remember that it is propertyName=value
  separated by spaces in Base.


Enable Oracle Database options in an existing database

No, you don't have to reinstall the Oracle software. According to the section "Oracle Database 11g Release 2 (11.2.0.1) New Features" in the Database Installation Guide 11g Release 2:

  Oracle Universal Installer no longer provides the custom installation
  option of individual components. Use the chopt tool, a command-line
  utility that is located in the ORACLE_HOME/bin directory, to configure
  the database options.

For example, to enable Oracle Real Application Testing, you would first shut down your database, and then issue chopt as follows:

chopt enable rat

For more information on how to enable and disable other database options with chopt, consult "Enabling and Disabling Database Options" section in the Database Installation Guide 11g Release 2.


Transfer relational data from one DB to another production DB currently populated with data - MySQL

You'll have to remap them to a non-conflicting insertion range, and to create that you'll need to adjust the AUTO_INCREMENT setting on each affected table.

For example, alter the AUTO_INCREMENT values on the affected tables to introduce some headroom. If you need to insert ~100 records into a table with the increment set to 2500, then set it to 2700. That way you have 200 non-conflicting IDs to work with.


SQL Server 2012 always-on backup, full vs copy-only

Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?

NO -- You can add t-log backups to a restore of a COPY_ONLY backup


Should I normalize my string arrays, or just keep it a simple string?

Well you could attack this in the manner you previously stated, or you could go another route.

Typically, in this situation, I would create three tables.

Table 1 - Cards: This would contain the basic card information.

Table 2 - Types: This would be one record for each possible type (i.e. one record for green, one for blue, one for creature A, one for creature B, and so on.)

Table 3 - CardTypes: This would have two columns, one Foreign Key to Cards and one Foreign Key to Types. The combination of the two would be the Primary Key.

The advantage to this approach is that you don't have to store a large amount of plain-text, as if a card has multiple properties they would each go one-and-one in Types, and then a relationship to each property would go in CardTypes. It also means if two cards are blue, then only one Type is store for blue, and two (small, mind you) entries are in CardTypes.

The downfall is the obvious overhead. Your programme must be aware of the three tables.

The advantages depend entirely on how often you are querying the tables. If you are often querying for a specific property, then the index on CardTypes would significantly improve performance, as the index would contain the necessary pointers to your cards that have that property.

In the end it all depends on how often/many queries you will be running. If you are running a significant amount you will likely be better off taking the programming overhead of creating two tables and then linking them with a third.


Why my Postgres 9.3 database is logging crazily autovacuum alerts

It's a temporary table that wasn't removed automatically when it should have.

The fix from the point of view of a DBA is to simply drop the table manually.

There are several references to this problem on mailing lists. See for instance
[bug fix] Suppress "autovacuum: found orphan temp table" message
for a discussion on this subject.


How to guarantee data consistency across multiple reads?

That is not a problem.

The serializable transaction (you should use repeatable read in this case, it performs better and does the same) effectively starts when you execute the first select, and all statements in the same transaction will see the same snapshot of the database.

So all is consistent.


Will frequent small select queries slow down frequent write queries in InnoDB table in MySQL?

The only way to know is to benchmark it.  If your query is fast, the chance of any notable impact on other queries is low, assuming your database server has sufficient disk bandwidth, multiple CPU cores, sufficient memory, and an appropriate innodb_buffer_pool_size setting for both the working dataset and the hardware.

If you don't already have a replica ("slave") server, then you should consider implementing that -- a slave server imposes a relatively minimal load on the master, the select queries could be run against the replica, and, of course, you would then have an alternate server you could use in the event of a catastrophic failure of the master server.

Use EXPLAIN SELECT ... to understand how the server is actually processing your query, and then consider whether it needs to be optimized or rewritten, particularly if it is doing things like full table scans or temporary tables.

When benchmarking your query, remember to use SELECT SQL_NO_CACHE ..., otherwise the performance may be deceptively fast if none of the underlying tables has changed between subsequent invocations.


High Unrestored log on secondary database mirror server

We have a high value of unrestored log on secondary side where value keep ranging from 5-10 GB as monitored.

That's definitely a problem even in high performance (async) mode.

Simultaneously we have a job to create snapshot on this mirror [for reporting] server every 30 minutes which completes within a minute when unrestored is within MB's but keep running for 2-3 hours with high value of unrestored log
Could someone explain why the snapshot job is running that long or how to find it and fix high unrestored value?

Creating a snapshot of a database causes the snapshot to run recovery - just as if you were to start up any database in SQL Server. Since there is 10 GB of log to go through... it's going to take a while to run recovery. Pair that with adding of more log to an already behind database and you have yourself a great recipe for never catching up and always being behind.
From BOL: Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).


Vacation Approvals Multiple requests into one request?

For 2017 you can use STRING_AGG.

DBFIDDLE

create table  #myTable
([Name] varchar(64),
 DateOfRequest date,
 ReqLine int,
 FromDate date,
 EndDate date,
 SchedCode char(2))

insert into #myTable
values
('BSMITH','20190911',1,'20190912','20190912','V8'),
('BSMITH','20190911',2,'20190914','20190915','V8')

select
  [Name],
  string_agg(DateOfRequest,char(13)) as DateOfRequest,
  string_agg(ReqLine,char(13)) as ReqLine,
  string_agg(FromDate,char(13)) as FromDate,
  string_agg(EndDate,char(13)) as EndDate,
  string_agg(SchedCode,char(13)) as SchedCode
from #myTable
group by [Name]

This won't make your DateOfRequest unique, but if you know it will be the same for each Name then you can just select it, and add it to the group by

select
    [Name],
    DateOfRequest,
    string_agg(ReqLine,char(13)) as ReqLine,
    string_agg(FromDate,char(13)) as FromDate,
    string_agg(EndDate,char(13)) as EndDate,
    string_agg(SchedCode,char(13)) as SchedCode
  from #myTable
  group by [Name], DateOfRequest


I'd look at how you have your IO stack set up, which you say nothing about in the question. It sounds like actually committing the rows is taking a really long time.  Virtual file systems on top of other file systems often display the worst characteristics of both layers.

You also should try doing an actual test run, as well as just populating a database.


Getting the employee history of Sales department from HR database in Oracle

An introduction to SQL queries but better you look for an introductory SQL text

I assume that every employee is in the job_history table and every employee and therefore job is assigned to a department. Otherwise one will loose some employees.

Let's display the columns of the tables involved:

select employee_id,first_name,last_name,department_id
from employees;

select department_id, department_name
from departments;

select job_id,employeed_id,department_id
from job_history;

select job_id,job_title
from jobs;

First a list of all employees and their departement information

select *
from employee e, departments d
where e.department_id=d.department_id;

now select only the rows from the employees that are in the department 'Sales'

select *
from employee e, departments d
where e.department_id=d.department_id
and d.department_name='Sales';

for these employees now find the jobs have already head fron the job_history

select *
from employee e, departments d, jobhistory h
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id;

Now for each job find the job title

select *
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

Now we display only the columns we need

select e.first_name,e.last_name,j.job_title
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

If we want to know in which department they worked in their job wee join another department table to get these information

select e.first_name,e.last_name,j.job_title,d2.department_name
from employee e, departments d, jobhistory h, jobs j,departments d2
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id
and h.depatment_id=d2.department_id;


MySQL replicate-rewrite-db ALTER TABLE not rewritten

It turns out I was using MySQL Workbench and it does not output a "USE" statement in its DDL update scripts, also rewriting does not occur if you do not provide a use statement.
So adding USE master; at the beginning of the script fixed it. Thanks!


What is the syntax to add a constraint on an already-created table?

Follow the example here by ypercube

ALTER TABLE table_name
  ADD CONSTRAINT only_one_is_active_value_per_name
    EXCLUDE  USING gist
    ( name WITH =,
      is_active WITH <>
    );

The ALTER TABLE command only lists examples of constraints. Constraints are actually added with the same syntax on DDL-Constraints,

CREATE TABLE foo (
  -- stuff
  EXCLUDE USING gist ( name WITH =, is_active WITH <> );
);

ALTER TABLE foo
  ADD CONSTRAINT
  EXCLUDE USING gist ( name WITH =, is_active WITH <> );


SQL Server - error running 'sp_addsubscription' when not sysadmin

This is resolved by ensuring distributor_admin (or login it is replaced by) is in sysadmin server role


commitlog broken after standby setup with pg_basebackup

This probably happens because the master doesn't keep the wal segements long enough so they are deleted before your base backup completes. You can use the option --xlog-method=stream with pg_basebackup to tell it to also stream the wal while making the backup. This way you will have all the wall segments the backup needs to start succesfully.


why my split string functions are not working with large OBJECT_DEFINITION?

I don't think your sys.all_objects has enough rows to make this work.  It looks like your split routine is reading each individual character and concatenating it.  You have more characters in your stored procedure than you have rows in sys.all_objects.

I created and populated a dbo.numbers table with 100,000 rows and modified your code to use that instead of sys.all_objects.  I also included the row_number I was on to be able to see how the 'numbers' were being consumed.

You can really see what's going on if you run with output to text.

--drop table numbers
SELECT TOP 100000
        IDENTITY(INT,1,1) AS Number
   INTO dbo.numbers
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--=============================================
-- this is the SplitString function
-- for use when we're below sql 2016
--=============================================
alter FUNCTION dbo.SplitString

    (
        @List NVARCHAR(MAX),
        @Delim NVARCHAR(255)
    )
    RETURNS TABLE
    WITH ENCRYPTION
    AS
        RETURN ( SELECT * FROM
          (
            SELECT
            convert(varchar(10),number) as num,
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY number)
              FROM dbo.numbers) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
          ) AS y
        );

DECLARE
@olddelim nvarchar(32) = Char(10) -- char(13) + Char(10),
,@newdelim nchar(1)     = NCHAR(9999); -- pencil (?)

SELECT m.*
from  dbo.splitstring( OBJECT_DEFINITION ( 727113781),  @olddelim) m


SQL Server 2012 and up support FORMAT, which enables .NET-like format strings, making this a rather easy task; just use

FORMAT(Num, '0.#####')

Working SQL Fiddle here.


I think the Notes table is missing a userID FK column (and put an index on it)
You need to identify the owner of the Note

The UserNotes table does not need an ID
just:

NoteID || UserId

That is a sufficient composite PK
And I would do it in that order for quick counting NoteID
select count(*) from UserNotes where NoteID = X would be very very fast

So what 1000 x 1000 rows.  You have 1000 x 1000 unique relationships.  It is just two Int.

One trick is have a UserID Public ID 0 for all

Just a preference but I don't use plural in a table name
Tables are used to store many rows


Two types of data, so two type of databases?

If the two families of data are related then I would generally recommend keeping the in the same database - it makes maintaining integrity easier, for instance taking a reliable and consistent backup is (depending on database engine of course) a single operation.

Of course if the two families of data are very loosely coupled anyway this need not be a concern, and using different databases or even completely different technologies for each becomes more likely to be a good solution particularly as you scale out (your first hardware scaling being too put each family of data on its own (virtual) machine. Remember though that if you do split the data over more than one storage technology you are increasing the amount of knowledge needed to maintain the system.

In MSSQL or similar you could break data with significantly different IO patterns into separate file groups in the same database and store them on different drives if needed for performance - this way all the data can be dealt with atomically for transaction or backup purposes and so forth while still being segregated for performance reasons.


Big size of database logs SQL Server 2008

You need to do log backups at least daily (we do them every 15 mintues). Read in books online about how to backup the log (not the daatbase, the log, these are two separate types of backups) and you should find directions for how to truncate the log without backing it up which you will likely need since you have let it get this size. The make sure you have a current backup of the database before you start. Then truncate the log and immediately set of regularly schedueld backups so this doesn't happen again.


In SQLCMD is there any way of shelling out to run arbitrary programs?

The !! <command> syntax lets you shell out.

The external command is handled synchronously (i.e. the next line of the SQL script will not execute until this command finishes). This is why the documentation has a note in a few places stating:

  When you use the Query Editor in SQLCMD Mode, you must be careful not to execute interactive statements. The Query Editor cannot respond to operating system prompts.

and:

  ...because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

You can test this rather easily by creating a SQL script of nothing more than:

!!pause

If you run this in SSMS, you will have to cancel the query else it will just keep running. Once it is canceled, you will see the following in the "Messages" tab:

  Press any key to continue . . . Query was cancelled by user.

Please see this MSDN page for more information.

However, this introduces an external dependency on your build process, not to mention that SQLCMD mode is not enabled by default in SSMS and cannot be programmatically enabled (though it is possible to change the default setting to always be enabled). Hence you might be better off encapsulating the JSON functionality in a SQLCLR function or stored procedure.


How to login in database if we have lost password?

You can connect to DB with local Windows authetication and change the sa password.


After SSRS to PowerBI Reporting Server migration, parameters are rendering goofy when trying to enter data

In case anyone views this a few years later, the issue was with a PowerBI Report Server update. They released a hotfix and things were fixed.


The error is saying that there’s a chance that Oracle will end up updating the same row twice because there is no unique constraint on _file_*date*_BAK.filecod.
The simple solution is to create the unique index on your logical backup table:
Create unique index _file_*date*_bak_uix on _file_*date*_BAK (filecod);

And then running the update statement exactly as you wrote it already.
This answer is now version dependent though as of Oracle 19.10. If you are sufficiently patched, Oracle will allow you to execute this update statement and only fail if you have data which would cause a single row to be updated multiple times. This behaviour currently needs to be manually enabled with fix control, either at your session level or for the system (talk to your DBA):
Alter session set "_fix_control"='19138896:1';

I expect this will become default behaviour in a future major release.


Storing data as rows as opposed to columns

This is known as the Entity-Attribute-Value design. See Best Practices for Semantic Data Modeling for Performance and Scalability for a lengthy discussion of pros and cons.

The main problem is querying becomes both difficult to express at design time and unperformant at runtime.

Better approaches are to have a true schema, properly indexed, for known attributes, and allow EAV for future, unknown, custom, non-search critical attributes.

Schema-free databases, like MongoDB, are also alternatives to EAV.


LEFT JOIN conversion to INNER JOIN, can't change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

  the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range.
Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.


Database design - summary table vs always counting over tables

I think the answer will vary depending upon what you want to achieve.

As you have mentioned that these things need to be acted on analysed, reported & displayed then I would consider that having the data in tables would be advantageous.

The decision to calculate summary data vs calc on the fly depends upoon size volume and scalability and accuracy required. A mixture of real time and batch processing might be viable and practical approach.

I would expect performance would be an important consideration. Do end users need to see their stats updated immediately? Or are you collecting this data to drive marketing analysis which can happen periodically at a later date.

Go back to your requirements. What data do you need. Who needs it. When do they need it. How fast & how often. Do they need raw data or aggregated. Detail or Summary? How much data are you expecting to collect. How long do you need to retain it? What are the storage requirements and how will that perform?

If you can answer the questions above you may narrow down your choices and your remaining options will be clearer.


Retrieving data from SQLServer mutitenant database

Set the default schema for the users. You can see this in the user properties window through SQL Server Management Studio.

Or script:

ALTER USER user1
WITH DEFAULT_SCHEMA = Sch1;


How to show two tables in one query? part and the whole

Preparation sample data:

CREATE TABLE FinalExam(id INT, title VARCHAR(128), score FLOAT);

CREATE TABLE Quiz(id INT, feid INT, quiztitle VARCHAR(128), score FLOAT);

INSERT INTO FinalExam VALUES
(1,'math',12.67),
(2,'literature',17)

INSERT INTO Quiz VALUES
(1,1,'midterm',14),
(2,1,'midterm2',11),
(3,2,'quiz1',16),
(4,1,'finalexam',13),
(5,2,'finalexam',18)

Query to get result:

SELECT id,title,score
FROM (
    SELECT id, feid=id, title, score, final=CAST(1 AS BIT)
    FROM FinalExam

    UNION ALL

    SELECT id, feid, quiztitle, score, final=CAST(0 AS BIT)
    FROM Quiz
) A
ORDER BY feid,final DESC

Now you can see data in right order: final exam first then rest of quiz grades connecting with this final exam. You can delete ALL statement to allow your query delete duplicates.


Find all occurrence of value from column b in column a

Test Data:

CREATE TABLE dbo.Table1( columnA nvarchar(255), columnB nvarchar(255),GIDNumber int, Description nvarchar(255));
CREATE TABLE dbo.Table2( columnA nvarchar(255), columnB nvarchar(255),TwrNum int,Description nvarchar(255));

INSERT INTO dbo.Table1(columnA,ColumnB,GIDNumber,Description)
VALUES(5900920132278,5900021321,1,'F-A1120010185')

,(5900920132278,5900021322,1,'F-A1120010165')
,(5900920132278,5900021323,1,'F-A1120010165')
,(5900920132278,5900021324,1,'F-A1120010165');

INSERT INTO dbo.Table2(columnA,ColumnB,TwrNum,Description)
VALUES(2000061232,5900920132278,1,'F-A1120010185');

Original query

SELECT t2.columnA , t1.columnB, t1.description
FROM Table1 as t1
LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNum;

Result:

columnA columnB description
2000061232  5900021321  F-A1120010185
2000061232  5900021322  F-A1120010165
2000061232  5900021323  F-A1120010165
2000061232  5900021324  F-A1120010165

Adding COUNT() and GROUP BY

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB
GROUP BY t2.columnB,t2.columnA;

Result:

columnB columnA amount_of_matches
5900920132278   2000061232  4

If you also need to check if GIDNumber = TwrNum

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB and t1.GIDNumber=t2.TwrNum
GROUP BY t2.columnB,t2.columnA;

As a result, if column B matches column A but the t1.GIDNumber=t2.TwrNum  does not match, it will show as no match:

columnB columnA amount_of_matches
5900920132278   2000061232  4

Extra tests:

INSERT INTO dbo.Table2(columnA,ColumnB,TwrNum,Description)
VALUES(2000061232,5900920132279,2,'F-A1120010185');

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB
GROUP BY t2.columnB,t2.columnA;

columnB columnA amount_of_matches
5900920132278   2000061232  4
5900920132279   2000061232  0

INSERT INTO dbo.Table1(columnA,ColumnB,GIDNumber,Description)
VALUES(5900920132278,5900021321,1,'F-A1120010185');

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB
GROUP BY t2.columnB,t2.columnA;

columnB columnA amount_of_matches
5900920132278   2000061232  4
5900920132279   2000061232  1

Adding the description column:

SELECT t2.columnB,t2.columnA , count(t1.columnB) as amount_of_matches, t2.Description
FROM Table2 as t2
LEFT JOIN Table1 as t1 on t1.columnA =t2.ColumnB   and t1.GIDNumber=t2.TwrNum
GROUP BY t2.columnB,t2.columnA, t2.Description;

Result:

columnB columnA amount_of_matches   Description
5900920132278   2000061232  4   F-A1120010185
5900920132279   2000061232  0   F-A1120010185


After some digging around it does seem like write access can be granted but the constraint here is there should be no leftovers i.e no empty temp table lying around after the transaction is done.

Going with the temp table solution and dropping it at the end of the transactions.


Look at the examples for hstore in the PostgreSQL documentation.

UPDATE tab SET h = h || ARRAY['b => 2'::hstore,'c => 3'::hstore];


Query to find plan details for a specific stored procedure, searching by name

Given that the sys.dm_exec_query_stats DMV returns data for the entire instance, you need to be able to get object Schema and name info across all Databases. Using ST.objectid = OBJECT_ID(N'dbo.ProcedureName'), as has been suggested, is error-prone since it will only resolve the name relative to the database in which the query is being executed:

If the object name does not exist in that Database, then it will return NULL, which will filter out all rows.
If the object name does exist in that Database but you are actually wanting that object name from a different database, then you will get an object_id value that might be correct, but could just as well point to a different object and might return one or more incorrect rows.
If an object_id value is returned, whether correct or incorrect, that particular value can exist across multiple databases and so you might get multiple rows back for different objects in different databases.

At the very least, you would need to provide a fully-qualified object name (i.e. three-part name). However, even if you do use OBJECT_ID(N'DatabaseName.SchemaName.ObjectName') the query still has the potential to run into problem #3 noted above since that object_id, even being correct, can exist across multiple Databases. So you need a second condition using the DB_ID() function to narrow down to the intended Database.

WHERE st.[objectid] = OBJECT_ID(N'DatabaseName.SchemaName.ObjectName')
AND   st.[dbid] = DB_ID(N'DatabaseName')

HOWEVER, my preference is to use the OBJECT_NAME and OBJECT_SCHEMA_NAME functions as they both accept an optional 2nd parameter for database_id. The advantage here is that you can use them in the SELECT clause when you are not narrowing down to one specific object, so that you can see the object names for all of the rows in the DMV. It also allows for filtering down to the same object across multiple Databases, which is useful when you have the same Stored Procedure in multiple Databases and want to see rows for it across all Databases, even though it likely has different object_id values across those Databases.

Hence, you could optionally add the following to the SELECT clause (I use this when Cross Applying sys.dm_exec_sql_text to DMVs such as sys.dm_exec_requests, etc):

DB_NAME(st.[dbid]) AS [DatabaseName],
OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) AS [SchemaName],
OBJECT_NAME(st.[objectid], st.[dbid]) AS [ObjectName]

And you would then update your query to have the following WHERE clause:

WHERE OBJECT_NAME(st.[objectid], st.[dbid]) = N'my_proc_name'
AND   OBJECT_SCHEMA_NAME(st.[objectid], st.[dbid]) = N'dbo' -- or whatever schema
-- AND  DB_NAME(st.[dbid]) = N'DatabaseName' -- optionally narrow down to specific DB


So a single product can potentially have multiple prices at once? If this is the case then I don't see the need for the extra table. Create the products table, including product name, description plus whatever else you need. Then create the stock table, with a FK relationship to the products table product ID with quantity and price per unit. When a sale is made it can take the price from the stock table and enter it as a sale price in the sales table and update the quantity in the stock table.

The only thing that isn't clear is where you said a stock can have multiple products. The above seems to be a better method of handling this rather than having a product with multiple stock records and stock records with multiple products.


Oracle 12 set user password Expiration date or EXPIRED(Grace)

You have to use a profile and a combination of password_life_time and password_grace_time; Lifetime will set the expiry time of password and grace_time will warn them (after expiry) as you'd expect. All you then need to do is assign the profile to user or users (using alter user).

A quick demo?

SQL> set echo on
SQL> drop user testa cascade;
User TESTA dropped.
SQL> create user testa identified by testa;
User TESTA created.
SQL> grant create session to testa;
Grant succeeded.
SQL> prompt this connection should be successful as there is no profile attached.
this connection should be successful as there is no profile attached.
SQL> conn testa/testa
Connected.
SQL> connect as a dba user
Connected.
SQL> drop profile test_profile;
Profile TEST_PROFILE dropped.
SQL> -- using profile i am setting password to expire in about 10 seconds useful for our testing
SQL> -- 0.0001158 is about 10 (10/86400)
SQL> -- grace time is set to 1 day
SQL> create profile test_profile limit password_life_time 0.0001158 password_grace_time 1;
Profile TEST_PROFILE created.
SQL> -- now you assign the profile to the user who should get warning .
SQL> alter user testa profile test_profile;
User TESTA altered.
SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
SQL> prompt this connection should show a warning
this connection should show a warning
SQL> conn testa/testa
ERROR:
ORA-28002: the password will expire within 1 days
Connected.
SQL> select account_status ,expiry_date, PROFILE, sysdate from user_users;
ACCOUNT_STATUS   EXPIRY_DATE         PROFILE        SYSDATE
---------------- ------------------- -------------- -------------------
EXPIRED(GRACE)   03/21/2017 14:56:00 TEST_PROFILE   03/20/2017 14:56:00


How to model "Courses prerequisites" for a university?

You have some Courses:

COURSES
-------
course_id
course_number
course_name

You need to combine one or more of these Courses with a logical operator:

COMBINATIONS
------------
combination_id
logical_operator (AND, OR or NOT)

COMBINATION_COURSES
-------------------
combination_id
course_id

You may also need to combine some of these combinations (so changing the COMBINATION_COURSES table):

COMBINATION_COURSES
-------------------
combination_id
course_id
sub_combination_id

where one and only one of course_id and sub_combination_id is not null.

A Course may have one of the combinations as a prerequisite (so changing the COMBINATIONS table):

COURSES
-------
course_id
course_number
course_name
prerequisite_combination_id

EDIT

So, given your original examples your tables would be populated:

COURSES
=======
course_id course_name                    prerequisite_combination_id
--------- ------------------------------ ---------------------------
        1 Artificial Intelligence                                 12
        2 Data Structures
        3 Introduction to Logic
        4 Mathematical Logic and Proofs
        5 Calculus 1
        6 Calculus 2                                              13
        7 Analytical Geometry

COMBINATIONS
============
combination_id logical_operator
-------------- ----------------
            11 OR
            12 AND
            13 AND

COMBINATION_COURSES
===================
combination_id course_id sub_combination_id
-------------- --------- ------------------
            11         3
            11         4
            12         2
            12                           11
            13         7
            13         5

Course 1 - Artificial Intelligence has a prerequisite of the Combination 12. Combination 12 is Course 2 - Data Structures AND Combination 11. Combination 11 is Course 3 - Introduction to Logic OR Course 4 - Mathematical Logic and Proofs.

Hence, the prerequisite of Course 1 - Artificial Intelligence is Course 2 - Data Structures AND (Course 3 - Introduction to Logic OR Course 4 - Mathematical Logic and Proofs).

This structure should allow you to store any prerequisite that is the any logical combination of any number of courses.


The target principal name is incorrect. Cannot generate SSPI context

My problem was that when setting up the credentials for the remote server in windows credential manager, I had not specified the domain alongside the username.

I had

sql.domain.name:1433
username
pwd

the correct version was:

sql.domain.name:1433
domain.name\username
pwd

So, specifying the fully qualified domain name in the username field of credential manager resolved the issue.


Quotes must be escaped in the OPENQUERY argument.

The return type for the TRIM function is text, so you should cast it back to varchar.

Select * from OPENQUERY(LinkedServer,
    'Select CAST(TRIM(trailing '' '' from ad.userid) AS varchar(8)) As "User ID"
    FROM alphadawg ad')


What to do with WAL files for Postgres Slave reset

If you have WAL archiving enabled on the master (archive_command is set and archive_mode is on), set a restore_command in your replica's recovery.conf to allow it to fetch WAL from the WAL archive.

If there's no WAL archive, then there's no record of needed deltas between the master and the replica anymore. So you must resync them.

Typically this is done by making a new pg_basebackup of the replica. If the replica is big, though, it can be helpful to use rsync to resync the replica from the master doing block compares. To do this, you:

pg_start_backup() on the master
Stop the replica if running
rsync the master to the replica
pg_stop_backup() on the master
Copy any additional files from pg_xlog on the master to the replica, up to the file reported by pg_stop_backup()
Start the replica

It's simpler if you have WAL archiving enabled, since you then don't have to manually copy WAL, you just set a restore_command on the replica.

All sound too complicated? Use pg_basebackup.

As for your other questions:

NEVER delete WAL from the master. Ever. Extremely bad. Hands off pg_xlog.
pg_start_backup doesn't "lock" the database. It does prevent VACUUM from cleaning up dead rows, so it can increase bloat on high write activity tables, but that's about it.


How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?

All you need is configured ODBC connection to Oracle DB

1. Install Oracle Client SW (download from otn.oracle.com). I recommend installing same version as your Oracle DB
2. Define connection in tnsnames.ora
3. Create and test ODBC connection to Oracle DB in Control Panel > Administrative Tools > Data Sources (ODBC) -  use Oracle's driver in your ORA_HOME, use connection defined in tnsnames.ora
4. Importing from defined ODBC connection to Oracle should be same as importing from Access DB