SQL PROFILER Replay statistics event interpretation I tried capturing a replayable trace on a SQL Server 2012  production environment and then replay it against a SQL 2016 test environment.

After doing so , I got the following results :

Can  anyone help me identify what each of the below highlighted term represents :

Total events = 89
Replayed events = 39
Provider errors = 13
Internal    errors = 26
Total errors = 39
Provider hit ratio = 66.67 %
Internal    hit ratio = 33.33 %
Overall hit ratio = 0.00 %
Replay time = 0 hr 2 min 12 sec 38 ms

Will be of great help as I am not able to understand how the upgrade to 2016 might perform based on the profiler results.

I am afraid this replay will not help you at all. It will not give you any indication about performance in your new test server as all the replay events failed.

I recommend you read this when you take your trace at source, especially following sections:

Event Classes Required for Replay

Data Columns Required for Replay

I will use these terms so I do not repeat the same thing:

SQL Server 2012 production environment = Source

SQL 2016 test environment = Destination

Total events = Number of events that was captured from source
Replayed events = Number of events replayed at destination
Provider errors = Look at the textdata column for details on each provider warnings or error
Internal errors = Look at Errors and Warnings column for details
Total errors = Sum(Provider errors + Internal errors)
Provider hit ratio = Ration between Replayed events and Providers errors
Internal hit ratio = Ration between Replayed events and Internal errors
Overall hit ratio = Meaning out of 39 Replayed events all of those timed out either and Provide errors or Internal error. In your case it 13+26=39.
Replay time = Time took to replay trace at destination.

You can load your test result into a table and then you should be easily able find details about errors. As an example for Provider Errors you can use following query which I copied from this blog post.

SELECT RowNumber,
       EventClass,
       TextData,
       ConnectionID,
       SPID,
       EventSubclass,
       RequestProperties,
       RequestParameters,
       BinaryData
  FROM dbo.Trace_Replay_Results
 WHERE EventClass = 63485


Data are not saved to database - Auto creating new datafile in oracle I have some problem with Oracle database. Before it works like that, if INDX[Counter].dbf file reach its maximum size (8GB) Oracle automatically created new datafile INDX[Counter+1].dbf. But on server wasn't enough size to create new datafile. I increased space on server, I created new datafile manually:

ALTER TABLESPACE INDX2
ADD DATAFILE 'D:\oracle\product\10.2.0\oradata\profi\INDX18.DBF'
SIZE 4M
AUTOEXTEND ON NEXT 4M
MAXSIZE 8G;

Right now after INDX18.dbf reach 8GB Oracle wasn't create new datafile automatically and data are no longer stored. I had to add new datafile manually. How to set Oracle database to auto create new data file after reach maximum size of single datafile?

Best regards

In your example, you have Autoextend on with 4M as the next extension, but you also have MAXSIZE set to 8G, which means that Oracle is being told that once a tablespace reaches 8G, it will no longer automatically add space.

This is for safety purposes, as sometimes administrators want to know when a datafile is growing very large very quickly.

In your case, you could issue alter tablespace indx2 autoextend on maxsize <some number>G;, which would allow this tablespace to automatically extend to that maximum size, depending on how much space you have on your server! If you run out of actual physical disk, nothing in the Oracle database will protect you from this.

Additionally, you'll still want to periodically monitor the size of this tablespace, as you will (theoretically) still eventually reach the maximum size of the autoextend. Even if you set the maxsize to unlimited, there are still factors that determine the maximum size of a tablespace that are out-of-scope for your particular question. Just know that there is always a maximum upper limit to a tablespace, and someone will have to monitor it to take appropriate action.


PostgreSQL - how to list all UNIQUE indexes for specific database? I would like to get a list of all UNIQUE indexes (for unique column combinations) for specific database in PostgreSQL. I searched but could not find the query for this.

I found these two queries.

SELECT indexname FROM pg_indexes;
SELECT * from pg_indexes where schemaname = 'public';

but neither the one, suits my needs.
I only need just those which identify indexes for unique columns.
Thanks.

You need to use pg_index and limit to those that have indisunique. To get the actual index and table names, you need to join that table to pg_class and pg_namespace:

select idx.relname as index_name,
       insp.nspname as index_schema,
       tbl.relname as table_name,
       tnsp.nspname as table_schema
from pg_index pgi
  join pg_class idx on idx.oid = pgi.indexrelid
  join pg_namespace insp on insp.oid = idx.relnamespace
  join pg_class tbl on tbl.oid = pgi.indrelid
  join pg_namespace tnsp on tnsp.oid = tbl.relnamespace
where pgi.indisunique --<< only unique indexes
  and tnsp.nspname = 'public' --<< only for tables from the public schema


Restoring a differential backup in SQL server 2005 I have created a full back up of my database by executing the line below in my application:

Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'"

The *.bak file created successfully.

Then I restored it on another Database called test and it was ok. Then I created a differential backup by means of the following code:

Dim Query As String = "BACKUP DATABASE Ari TO DISK='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'" & "WITH DIFFERENTIAL"

and it created a *.bak file ,but when I try to restore it on the database I mentioned as test ,I receive the following error:

Please help me solve this problem.

When restoring a sequence of backups (differential & logs) you specify NORECOVERY. This leaves the database in a state that will accept further restores, rather than recover it ready for use.

RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_FULL.bak' WITH NORECOVERY;
RESTORE DATABASE MyDb FROM DISK = '<path>\MyDb_DIFF.bak' WITH NORECOVERY;

-- Repeat following for each transaction log to restore if applicable
RESTORE LOG MyDb FROM DISK = '<path>\MyDb_201303050900_LOG.bak' WITH NORECOVERY;

RESTORE DATABASE MyDb WITH RECOVERY;


Exporting a Database schema and triggers I have a small problem at work. Our DBA went on a journey of self-discovery ... I am very happy for him and I hope he finds what he is looking for. But he left us without any information about how the database he created works, the triggers he has, the connections between tables and, frankly, no documentation.

My question is whether it is possible to export all the information from the database (connections between tables, schema, triggers, fields with data type, etc .. :) in a file from TOAD or another application to mount it in some application that would reverse engineer the database to indicate that same information that we need.

I thank you very much for the help you can give me!

To find information about database object ( I am using triggers as example here )
use TOAD
choose Triggers
Right Click on Triggers
Create Script

That will give you source for all triggers

Repeat this for all TABLES, INDEXES, SEQUENCES etc. After reverse engineering a few - you will get hang of it.

Connections between tables

I believe you mean "relations between tables". If you schema is small say a few tables, then you can browse thru Parent and Child option of each table and document them.

For large number of tables, you will need to use TOAD to create Data Model Diagram; or maybe google for "Data Modeling Tools" and find one that fits your needs and budget.

You will imperatively need to refer to data model diagram many times in your new roles - so do it patiently and correctly.


ALTER DATABASE ARITHABORT configuration minimum privilege needed What is the minimum privilege needed to execute ALTER DATABASE {db_name} SET ARITHABORT ON?

I found that the privilege ALTER ANY DATABASE would suffice this, but I wonder if there isn't any other "smaller" privilege that does the same thing, because I think this permission is too broad and maybe I'll not have it in production environment.

Well, ended up I changed the user options, so new sessions start with ARITHABORT ON. It worker out for me.

Here's a link better explaining how: Make SqlClient default to ARITHABORT ON

I've used the "Server-wide" part of it.


ADD or modify column in one call (upsert) - avoid DROP calls I have this call:
ALTER TABLE mbk_user ADD login_attempts int;

I want to update it to:
ALTER TABLE mbk_user ADD login_attempts int NOT NULL;

is there a way to do "ADD or MODIFY" in one sql statement (an upsert), or do I need to drop the column first and then ADD it again? I want to avoid drop statements ideally.

If I understand your question correctly, you just want to make the column NOT NULL. This can be done with the ALTER <column_name> sub-command
ALTER TABLE mbk_user ALTER login_attempts SET NOT NULL;

Obviously this only works if there are no NULL values in that column.


System.OutOfMemoryException in client app I have a .NET client application running a very large batch of queries. I have SQL Server's max server memory configured, however when I run the query I get several messages like:

  An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

When I look at the server it has 20GB of memory free and SQL Server is only using 4.8GB (and that number is stable).

I've tried putting some GO statements in, but to no avail. I don't know where it is hitting the memory issues. None of the queries return more than a few rows.

How can I get around this memory issue?

I see someone posted a similar issue but I'm not sure how to allow SQL to write the results to a file rather than trying to put it all in the output window.

Responses to comments

The memory is set to 22528000.
A user is running a .NET application (which "calls" a SELECT statement) on his desktop. His desktop configuration is i7 processor with 32gb.
I'm not running it on SSMS.
The DBMS is SQL server 2012, SP3.

The error you are seeing is being generated at the client end. Typically this error occurs when the client runs out of memory while trying to display or consume the result set.  Try running a smaller subset of queries, and ensure the client is properly disposing objects as they are no longer required.


MongoDB Lookup Issue In collection A, we have

"batches" : [
    ObjectId("56fccffbe7c9f9cf7634dbfe")
],

and in Collection B, we have

{ "_id" : ObjectId("56fccffbe7c9f9cf7634dbfe") }

Can we perform a lookup between these 2 collections?

Yes, you can use the $lookup operator as part of an aggregation, after you $unwind the batches array. For example:

db.collectionA.aggregate[

  { $unwind: "$batches" },

  { $lookup:
    from: "collectionB",
    localField: "batches",
    foreignField: "_id",
    as: "collectionB"
  }
]


ALTER USER WITH LOGIN throws error "Windows NT user or group not found" Attempting to deprecate the procedure sp_change_users_login as recommended by MS. I tried using:

ALTER USER UserName WITH LOGIN = UserName

as demonstrated here.  This results in the error

  Msg 15401, Level 16, State 2, Line 3458
  Windows NT user or group 'UserName' not found. Check the name again.

UserName is both the user and the SQL login.  Surely I am missing something simple but I cannot see what it is.

I had 3 names that failed and one does not have a login, but the other 2 do have logins.  I can see the login and users in the Object Explorer.  However if I open the login properties the user is not assigned to the target database.  If I manually check the db box it works, but the point is that I am trying to clear up any orphaned users in my target database using SQL.

This error will happen if the login you are attempting to use does not exist.

Example:

CREATE LOGIN username WITH PASSWORD = '131fEWF$#@FR$#Vverf';
CREATE LOGIN username2 WITH PASSWORD = '131fEWF$#@FR$#Vverf';

USE tempdb;
CREATE USER username FOR LOGIN username;

ALTER USER username WITH LOGIN = username2; /*Successful*/

DROP LOGIN username;

ALTER USER username WITH LOGIN = username; /*Unsuccessful*/

You should check to ensure that the login was not removed prior to your attempt to run the ALTER USER command.


Postgresql table with one integer column, sorted index, with duplicate primary key I want to have a table in PostgreSQL with these properties:

One column with an integer
Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be deleted by a WHERE clause on the integer. The table is used like a set.
Efficient ORDER BY+LIMIT on the column/rows, and DELETING with a WHERE-clause for that integer
INSERTs should not do any query in that table or any kind of unique index. INSERTs shall just locate the best page for the main file/main btree for this table and just insert the row in between two other rows, ordered by ID. (That's why duplicates must be allowed, the check for primary keys would cause more disc seeks and can fail transactions and cause deadlocks)
INSERTs will happen in bulk (about 1000 per transaction) and must not fail, expect for disc full, etc. There must not be any chance for deadlocks.
There shall not be additional btree files for this table, so no secondary indexes
The rows should occupy not much space, e.g. have no OIDs, so that many fit in one page.

I cannot think of a solution that solves all of this.

The easiest thing would be a PRIMARY INDEX on that one column. But this won't allow duplicates.

Also I could use a secondary index, but this would cause more operations per INSERT and more disc access. I am really bound by disc seeks here.

Currently my best solution would compromise on the last bullet point: I could just a PRIMARY KEY covering the integer column and also a dummy column, like an OID, a timestamp column or a SERIAL column. Then every row (=primary key) would be unique. But this would increase disc space and reduce the amount of rows per page.

Is there any chance of having a kind of primary key together with duplicates? Can I control what the main btree file on disc is organized by?

(I posted a related question like this question on StackOverflow, but I was very unclear about my goals. I hope this doesn't count as spam, this question here is completely different)

I think you're asking how to impliment a solution you'e already decided on for a more general problem you don't describe. If you were to outline the actual problem that this is supposed to solve you might get better suggestions about how to solve it.

Working within the very limited information provided:

Update: I found your other question, which you really should've linked to. You seem to be trying to roll your own message queue. Don't do that. Read these:

https://stackoverflow.com/q/11914804/398670
https://stackoverflow.com/q/13005410/398670
https://stackoverflow.com/q/14515543/398670
https://stackoverflow.com/q/15937377/398670
https://stackoverflow.com/q/15736640/398670
https://stackoverflow.com/q/11761281/398670

Have I convinced you that you shouldn't try to do this yourself yet? Look into:

RabbitMQ
ZeroMQ
Job::Machine
ActiveMQ
PGQ
Celery

Some of what you want isn't available in current PostgreSQL versions. For example:

INSERTs should not do any query in that table or any kind of unique index. INSERTs shall just locate the best page for the main file/main btree for this table and just insert the row in between two other rows, ordered by ID.`

That'd require an index-organized table, which PostgreSQL doesn't have yet. The closest you'll get would be a one-column table with a PRIMARY KEY. With regular VACUUM on PostgreSQL 9.2 you'd be able to use index-only scans to access it most of the time.

As for allowing duplicates, you don't really seem to want to permit them at all, you're just saying you want to work around concurrency issues by temporarily permitting them.

You can remove such duplicates during INSERT so the table its self doesn't need to permit them. However, that'll cause issues with:

INSERTs will happen in bulk (about 1000 per transaction) and must not fail, expect for disc full, etc. There must not be any chance for deadlocks.

... assuming that those inserts occur concurrently from multiple transactions. You'll have races between the checks for existence and the insert that can cause insert batches to fail and have to be re-tried.

I suspect that your best bet is to have a one-column table without a PRIMARY KEY. Just create an ordinary b-tree index on it, and leave the table without a PRIMARY KEY. Since it genuinely has no primary key (the only column may have duplicates) this is entirely reasonable.

(BTW, given that SQL is supposedly all about sets, it astounds me how awful it is at "add this entry to the set if not already present").


Urgent restore operation I run a bad query on my production server and I need to perform a restore of my database.

I had a full backup (20160828) and a diff backup (29,30,31 and 01/09) and I had transaction log backup.

Is it necessary to restore all the diff backups or only the last one?

Is  it true that all restore operation I have to do must be in non recovery (the full, all the diff and all the transactionlog) except the last transaction log backup which must be restore with recovery?

Thanks in advance

Just restore the last full backup, the most recent Differential and then the transaction logs after the Diff, up to the point in time you want to restore. And Igor is right.


Error while connecting SQL Server management studio 2008 I have installed SQL Server 2008 management studio. When I connect, it shows an error. Also when I go to the SQL Server configuration manager and click on SQL Server SERVER services, this message is shown:

  There are no items in list to view

No connections are found in the SQL Server configuration manager while my local network is connected.

How can I fix this?

Sounds like you only installed the management studio and not the actual database engine.  You need to connect to a remote SQL Server instance not to your local machine as your local machine doesn't appear to have a SQL Server instance installed.

If you need your own database engine installed you'll need to run through the installer again and install the database engine.


Outer Join on all columns if possible, or a subset of columns if not possible, and sum I have two tables, call them T1 and T2 each with 4 columns A, B, C, Quantity.
I want to do an outer join. However, some ABC combinations in T1 do not match with T2, and vice versa.
My goal is to join with the following logic:
I want to join on all columns ABC if possible and return T1.A,T1.B,T1.C,T2.Quantity.
If there is no match at A,B,C then for a given T1.A, T1.B, join with T2.A, T2.B, sum(T2.Quantity). That is, I group by all T2.A, T2.B that match the T1.A, T1.B and get the sum of all T2.Quantity in that group.
I know this can be done by grouping T2 in subqueries and then left joining on T1, but could this be done with window functions?
Example:
Let T1:
A = 1
B = 2
C = 3
Quantity = 10
T2 Row1:
A = 1
B = 2
C = 4
Quantity = 20
T2 Row2:
A = 1
B = 2
C = 5
Quantity = 30
I want to return:
T1.A = 1
T1.B = 2
T1.C = 3
SUM(T2.Quantity) = 50 (sum of quantity in both rows in T2 that match T1.A,T1.B since T1.C do not match)

You will need three left joins. Semipseudo code as stuck in trafic
Select sum(coalesce(l1,l2,l3 quantity)
From t1
Left Join t2 as l1 on a,b,c
Left join t2 as l2 on a,b and l1 is null
Left join t2 as l3 on a and l1 is null and l2 is null


MySQL upgrade (5.5 to 5.7) I upgraded my MySQL version from 5.5 to 5.7 and in phpmyadmin I get this error message. I tried to reinstall and reconfigure it but I have still this problem. Can you help me? I am using Linux Mint. Thank you

It takes two steps.  You must upgrade from 5.5 to 5.6 and run mysql_upgrade, then do 5.6 to 5.7.


locking question around INSERT during UPDATE query Say I have a large table products

id, color, cost

and I want to run a query to update the cost of all 'red' items

UPDATE products
SET cost = cost + 1
WHERE color = 'red'

Let's pretend that there are so many rows that the query takes 5 minutes to finish.

What sort of lock will be placed on the table? Am I right in thinking that as the query executes, write locks will be acquired on all the rows where color = 'red', so these records cannot be updated until the UPDATE query finishes. They can still be read however (and reading them will always provide old values until the entire UPDATE transaction finishes)

What happens when the UPDATE query is started, and it is still working out which rows to lock as it's scanning the table for records with the color red? It would still allow updates to some records with the color red ?

But it won't lock the whole table, so other records can be written or updated whilst the UPDATE query is going on? What happens if a new record is inserted with a color of red. Does the price of that product get updated? surely not?

If there in an index on color then only the red colored rows will be locked. The rest of this answer assumes there is this index.

Another query looking to update the rows will either deadlock or lock wait, or success (with your query deadlocking or waiting). Depending on what locks are in place an in which order. Either way concurrent updates of the same row are prevented.

A new inserted red row will be delayed or deadlock under the concept of the isolation level being repeatable read (the default) and mysql having a gap lock covering red rows. A lower isolation level of read committed will allow an insert.

ref: isolation levels


What is a SQL Server "lop"? The tool ldfreader.exe has an option called -lopFilter

  -lopFilter      Dump only records with these LOP. (short form: -l)

But, I don't see anything explaining what a LOP is. What is known about "LOP"s?

A LOP is a "Logged Operation" they have identifiers and contexts internally.

  LOP  LopDesc (Operation), LopCtxt (LOP Context)
    1,     LOP_FORMAT_PAGE,        LCX_TEXT_MIX
    1,     LOP_FORMAT_PAGE,       LCX_CLUSTERED
    1,     LOP_FORMAT_PAGE,      LCX_INDEX_LEAF
    1,     LOP_FORMAT_PAGE,  LCX_INDEX_INTERIOR
    2,     LOP_INSERT_ROWS,        LCX_TEXT_MIX
    2,     LOP_INSERT_ROWS,       LCX_CLUSTERED
    2,     LOP_INSERT_ROWS,      LCX_INDEX_LEAF
    2,     LOP_INSERT_ROWS,  LCX_INDEX_INTERIOR
    3,     LOP_DELETE_ROWS,   LCX_MARK_AS_GHOST
    3,     LOP_DELETE_ROWS,  LCX_INDEX_INTERIOR
    4,      LOP_MODIFY_ROW,             LCX_IAM
    4,      LOP_MODIFY_ROW,             LCX_PFS
    4,      LOP_MODIFY_ROW,            LCX_HEAP
    4,      LOP_MODIFY_ROW,        LCX_TEXT_MIX
    4,      LOP_MODIFY_ROW,       LCX_BOOT_PAGE
    4,      LOP_MODIFY_ROW,       LCX_CLUSTERED
    4,      LOP_MODIFY_ROW,       LCX_OBJECT_ID
    5,   LOP_MODIFY_HEADER,            LCX_HEAP
    5,   LOP_MODIFY_HEADER,      LCX_INDEX_LEAF
    6,  LOP_MODIFY_COLUMNS,       LCX_CLUSTERED
    7,        LOP_SET_BITS,             LCX_PFS
    7,        LOP_SET_BITS,            LCX_SGAM
   10,  LOP_SET_FREE_SPACE,             LCX_PFS
   11,    LOP_DELETE_SPLIT,       LCX_CLUSTERED
   11,    LOP_DELETE_SPLIT,      LCX_INDEX_LEAF
   13,    LOP_EXPUNGE_ROWS,       LCX_CLUSTERED
   16, LOP_FILE_HDR_MODIFY,     LCX_FILE_HEADER
   18,     LOP_COUNT_DELTA,       LCX_CLUSTERED
   19,     LOP_ROOT_CHANGE,       LCX_CLUSTERED
   22,       LOP_INSYSXACT,       LCX_CLUSTERED
   22,       LOP_INSYSXACT,      LCX_INDEX_LEAF
   22,       LOP_INSYSXACT,  LCX_INDEX_INTERIOR
  128,      LOP_BEGIN_XACT,            LCX_NULL
  129,     LOP_COMMIT_XACT,            LCX_NULL
  131,       LOP_PREP_XACT,            LCX_NULL
  137,        LOP_HOBT_DDL,            LCX_NULL
  140,      LOP_HOBT_DELTA,            LCX_NULL
  141,       LOP_LOCK_XACT,            LCX_NULL
  148,        LOP_BP_DBVER,            LCX_NULL
  150,      LOP_BEGIN_CKPT,            LCX_NULL
  152,       LOP_XACT_CKPT,  LCX_BOOT_PAGE_CKPT
  153,        LOP_END_CKPT,            LCX_NULL
  211,     LOP_SHRINK_NOOP,            LCX_NULL

It looks like the LopCtxt is a kind of breakdown of the logged operation into atoms.

@sp_BlitzErik provided this blog post find which adds a lot detail.


If I move my SQLdata file from my F drive, delete the F drive, and rename the new Drive F will I have any file path problems? I have a windows server that only had 50 GB on the C drive and 4TB on the F drive. I need to exstend the C drive but the only way to do that is by deleting the F drive to reallocate space. If I move data from the F drive to a new drive, delete the F drive, and rename the new drive, will I loss the file path?

If I move data from the F drive to a new drive, delete the F drive,
  and rename the new drive, will I loss the file path?

If you're going to move everything on F:\ to a new underlying disk and rename that disk F:\ then you should be okay, but you need to ensure all permissions are correctly migrated to avoid issues during restart.

The basic process would be:

Stop SQL Server
Copy all data from F:\ to new drive using robocopy or similar tool that can bring permissions as well.
Remount F:\ as a new drive letter
Remount new disk as F:\
Restart SQL Server and verify health

Reverting should be as simple as reversing the drive letter changes if you run into issues.


Postgres has Ordered-Set Aggregate Functions for your purpose.

The special difficulty: you want rows "weighted" by cnt. If that's supposed to mean that each row represents cnt identical rows, you can multiply input rows by joining to generate_series(1, cnt):

SELECT site, dateval
     , percentile_cont('{0,.25,.5,.75,1}'::float8[]) WITHIN GROUP (ORDER BY raw)
FROM   source s, generate_series(1, s.cnt)
GROUP  BY 1, 2;

db<>fiddle here

But results differ from your expected output (except for the 0 and 100 percentile). So you are "weighting" differently ...

Aside, your original query can be simplified to this equivalent:

SELECT site, dateval, raw, sum(cnt) AS sumcnt
     , sum(sum(cnt)) OVER w AS cumsumcnt
     , sum(sum(cnt)) OVER w / sum(sum(cnt)) OVER (PARTITION BY site, dateval) AS percentile
FROM   source
GROUP  BY site, dateval, raw
WINDOW w AS (PARTITION BY site, dateval ORDER BY raw);

You can run a window function over the result of an aggregate function in the same SELECT (but not vice versa). See:

Need separate columns that calculate percentage

I added a demo to the fiddle above.

But neither explains the odd numbers in your "expected results". Those strike me as incorrect, no matter how you interpolate. Example: 22.07 in the first line for p25 does not seem to make sense - the value 23 occupies all rows up to the 27.7879 percentile after factoring in cnt according to your own query ...


See who has access to databases via AD group membership SharePoint has a button to "Check Permissions". Here you can select a user from Active Directory, and it will magically traverse groups and show you all the access the user has on the site based on what groups they are in.

This is a long shot as I've been working with SQL Server for quite a while and have never heard of this, but just in case I missed something...

Is there anything similar in SQL Server? Right now, checking permissions involves looking at the AD groups that have logins on the instance, and going through AD manually to see who is in which group, and then look at database permissions manually. A "Check Permissions" like SharePoint could save a lot of time.

just seeing which groups the user is in that also have a login on the instance would be nice

You can accomplish this using following code:

exec xp_logininfo 'dom\acc', 'all';

Or you can impersonate that login and use sys.login_token like this:

exec as login = 'dom\acc';
select distinct name
from sys.login_token
where principal_id > 0
      and type = 'WINDOWS GROUP';
revert;


Create a new database in SQL Server 2008 R2, but it is not Blank When I try to create new database in SQL Server 2008 R2, it was created completely, but it is not a blank and empty database, there are many tables that come from another database. I also tried specifying a different name for its .mdf and .ldf file, but nothing changed!

Would you please help me to know where is the problem?

Also, is there any command that would reset my database as a blank db?

New databases are cloned from database model. Probably some accident polluted your model database (maybe a DDL script run in the wrong database). You should probably clean it up.


Slower running Query with better Filter? mysql> SELECT  *
    FROM  history_uint h
    WHERE  h.itemid='1825689'
    ORDER BY  h.clock DESC
    LIMIT  2 OFFSET 0 ;
Empty set (0.00 sec)

Explain:

               id: 1
      select_type: SIMPLE
            table: h
       partitions: 2016_02_18,p2016_02_19,p2016_02_21,p2016_02_22,p2016_02_23,p2016_02_24
             type: ref
    possible_keys: history_uint_1
              key: history_uint_1
          key_len: 8
              ref: const
             rows: 1
         filtered: 100.00
            Extra: Using where

This is the problem query, which takes over 40 min to return 0 rows:

mysql> SELECT  *
    FROM  history_uint h
    WHERE  h.itemid='1825689'
      AND  h.clock>1456091116
    ORDER BY  h.clock DESC
    LIMIT  2 OFFSET 0 ;

Explain:

             id: 1
    select_type: SIMPLE
          table: h
     partitions: p2016_02_21,p2016_02_22,p2016_02_23,p2016_02_24
           type: range
  possible_keys: history_uint_1
            key: history_uint_1
        key_len: 12
            ref: NULL
           rows: 1
       filtered: 100.00
          Extra: Using index condition

Here is the table:

mysql> show create table history_uint\G
*************************** 1. row ***************************
       Table: history_uint
Create Table: CREATE TABLE `history_uint` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL,
  `value` bigint(20) unsigned NOT NULL,
  `ns` int(11) NOT NULL,
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p2016_02_18 VALUES LESS THAN (1455840000) ENGINE = InnoDB,
 PARTITION p2016_02_19 VALUES LESS THAN (1455926400) ENGINE = InnoDB,
 PARTITION p2016_02_21 VALUES LESS THAN (1456099200) ENGINE = InnoDB,
 PARTITION p2016_02_22 VALUES LESS THAN (1456185600) ENGINE = InnoDB,
 PARTITION p2016_02_23 VALUES LESS THAN (1456272000) ENGINE = InnoDB,
 PARTITION p2016_02_24 VALUES LESS THAN (1456358400) ENGINE = InnoDB) */
1 row in set (0.00 sec)

Why does one query run faster than the other? And why is there such a big difference? Seems/feels like it's doing a full table scan?

The code uses h.itemid = '1825689' and not h.itemid = 1825689 (without quotes, though it is an integer column). It's the way it comes from app, but it shouldn't make a big difference. Execution plans are the same with quotes and without, as well as run time. First one, where is only itemId returns in milliseconds, second one that has itemId and clock runs for 40+ min.

I am using MySQL 5.7.10. I have run many permutations of that query already, and the one specifically without the quotes is still running, 7min + so I am guessing quotes made no difference.

Adding a Link to Explain Plan with JSON Format

There are 3 steps in SELECTing from a PARTITIONed table.

"Partition pruning" -- deciding which partition(s) might have the data
Running the SELECT on each of those partitions
Combining the results.

Your first query could do no pruning since nothing in the WHERE clause limited which partitions to look at.

A quoted number will convert to an INT or BIGINT without trouble.  (The other direction causes trouble:  WHERE varchar_col = 123.)

The one index you have -- (itemid,clock) -- works even better without partitioning.  Do you have other queries for which you think partitioning is necessary?

You have no explicit PRIMARY KEY on that InnoDB table.  That generally hurts performance.

As for your 0sec vs 40sec, please provide EXPLAIN FORMAT=JSON SELECT ... for each of the queries.  There may be extra clues.


May a query update a source table? I work with a few tables with given data. I run a query built in Design View just like
result:![table1].[qty]*![table2].[expenses]
and I get the output in query table in field result. Is it possible to write result back to table2.resultfield? So this way my query does not only produce output table but updates one of the source tables.

You cannot update a field in the underlying table of a form when the control is bound to a function (a calculation in this case). However, you can run an update query, use VBA to update the field, use VBA to write the calculation to a bound control, or use a calculated field (MS Access 2010 and later versions only).

However, you must ask yourself if you really want to store a calculated result. It will depend on whether qty and expenses change over time and whether you need a fixed result for the current values. If qty and expenses will always be the same, just use a query to show the result of the calculation. For example:

SELECT t1.qty * t2.expenses As Total
FROM table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.MatchedID


How to join multiple columns from different tables How can you join multiple tables and display values from tables that referenced further in other tables?

select booking.booking_id,
(select customer.first_name from customer
where customer.customer_id=booking.customer_id) as First_Name,
(select customer.last_name from customer
where customer.customer_id=booking.customer_id) as Last_Name,
(select aircraft_model.aircraft_model from aircraft_model
where aircraft_model_id = (select aircraft.aircraft_model_id from
aircraft
where aircraft_modle_id = booking.aircraft_id))

from booking;

As you can see I'm trying to pass on the value of aircraft_model_id from the table booking to the aircraft_model table.

The only table connecting the aircraft_model table to the booking table is the aircraft table..

I'm completely new to sql so please do state if there are any errors.

Added in the codes for the table:

CREATE TABLE Booking (
Booking_ID            NUMBER(10)   NOT NULL PRIMARY KEY,
Company_ID            NUMBER(10)   NOT NULL REFERENCES
Penrhyn_Jet_Charter(Company_ID),
Customer_ID           NUMBER(10)   NOT NULL REFERENCES
Customer(Customer_ID),
Aircraft_ID           NUMBER(10)   NOT NULL REFERENCES
Aircraft(Aircraft_ID),
Assignment_No         NUMBER(10)   NOT NULL,
Booking_Date          DATE         DEFAULT SYSDATE,
Charter_Cost          NUMBER(14,2) CHECK(Charter_Cost > 0),
Departure_Date        DATE         NOT NULL,
Departure_Location    CHAR(3)      NOT NULL,
Arrival_Date          DATE         NOT NULL,
Arrival_Destination   CHAR(3)      NOT NULL
);

CREATE TABLE Aircraft_Model (
Aircraft_Model_ID     NUMBER(10)    NOT NULL PRIMARY KEY,
Aircraft_Model        VARCHAR2(255) NOT NULL,
No_Of_Aircrafts       NUMBER(10)    NOT NULL CHECK (No_Of_Aircrafts >
0),
Aircraft_Description  VARCHAR2(500)
);

CREATE TABLE Aircraft (
Aircraft_ID       NUMBER(10) NOT NULL PRIMARY KEY,
Company_ID        NUMBER(10) NOT NULL REFERENCES
Penrhyn_jet_Charter(Company_ID),
Aircraft_Model_ID NUMBER(10) NOT NULL REFERENCES
Aircraft_Model(Aircraft_Model_ID),
Seats             NUMBER(10) NOT NULL
);

Try this one, use INNER JOIN from booking to aircraft_model

SELECT
    b.booking_id,
    c.firstname AS First_Name,
    c.last_name AS Last_Name,
    am.aircraft_model
FROM booking b
INNER JOIN customer c ON b.customer_id = c.customer_id
INNER JOIN aircraft a ON b.aircraft_id = a.aircraft_model_id
INNER JOIN aircraft_model am ON a.aircraft_model_id = am.aircraft_model_id


Does SQL Server 2017 Express install on Dual CPU server? A customer wants to rent a dedicated Server for their site. They do not need more than SQL Express 2017, for the time being, but they have been offered a dual Xeon system.

Does SQL Express install and work on a dual CPU system, albeit using just one CPU, or do they have to ask for a single CPU server?

Sorry but I haven't found an answer to this specific question.

Will it install?

Yes.

Will it work?

Yes.

Will it use all of the horsepower the server has available?

No. The limitation of the Express Edition -- "single physical CPU or four cores" -- means that SQL will detect more than one CPU and will set the others to offline. You'll see that in the results of select * from sys.dm_os_schedulers both as "VISIBLE OFFLINE" in the status column and as "0" in the is_online column.

So you and your customer can do this, but you'll be wasting some money. The recommended approach all depends on what the price difference is between the dual-CPU system and a similar single-CPU system. If it is a small cost difference, and there is a strong possibility that they will upgrade to Standard Edition or Enterprise Edition soon (after all, you do mention that Express Edition is enough "for the time being"), you might want to advise them to start with the dual-CPU system to enable making that future edition upgrade without also having to deal with a server upgrade. If the price difference is not insignificant, go with the single-CPU system so you don't waste the cost of the second CPU.


MS Access : Navigation pane is suppressed I received a MS Access form client. It is in access 2007 format. Initially Navigation Pane was not available. Only form was visible. Then I choose options->Current database->checked Display Navigation Pane check box. It is visible now. But It is suppressed and not able to resize or access it as shown in below screenshot. I checked the code by pressing Alt+F11. I could not find script related to navigation pane. Please advise.

If you press F11 you can open and close the navigation pane. If it is stuck try grabbing the bar and sliding open manually. You do this by putting the cursor on the bar until it turns into a horizontal arrow. Seen here. Once fully open again it should work fine.


Backup on Share NFS fails - Windows Server 2008 R2 I've a network disk where backups are stored every night, but after some hour the backup fails with the following error:

  Executing the query "BACKUP DATABASE [foo] TO  DISK = N'\\XXX.XX.XX...." failed with the following error: "Cannot open backup device '\\XXX.XX.XX.XX\vol\BACKUP\BACKUP\foo\foo_backup_2019_07_04_010253_4929321.bak'. Operating system error 1450(Insufficient System Resources Exist to Complete the Requested Service.).

or else:

  ...failed: 121(the semaphore timeout period has expired.)

To mount this share NFS i've used this command:

mount -o mtype=hard XXX.XX.XX.XX:/vol/BACKUP T:

then in registry editor i've created two DWORD values (AnonymousUid and AnonymousGid) set to 0.

I've tried also to increase KeepAliveTime registry setting but it was uncessful.

If i modify the backup job in SQL Server Management Studio to store to a local disk (eg. D:) it runs without problem but then a free space problem will be raised.

There is something i can do to solve my problem?

Hope everything is clear

Backups over the network are risky as network connectivity is not redundant.  While you can setup your server to have multiple Network Interface Cards (NICs), if one were to fail while sending the stream of data establishing a connection from the secondary NIC would still cause a disruption to the original stream, which in this case, will cause a backup failure.

There are 2 things I can readily suggest you do, as follows:

Backup Locally: Make a local backup first and then use a secondary job such as robocopy, PowerShell, etc. to copy these backups over to your network share.
Stripe your backups: Make use of backup striping.  This will often times speed up the backup process significantly.  I still think you should backup locally, but striping your backup over the network may help you beat whatever network traffic is likely causing your latency issues.  Even Ola's solution supports striping in the event you're using that.

Backing up locally is most important for your TLog backups.  If enough of those fail in a row and your Tlogs fill the drive, you can run into a database halt situation which will be a service outage.  I'm not a fan of outages, nor should you be, which is why I really think you should make a backup locally, then offload a copy of those files to your network share.


SQL Indexed View for Datamart Reporting with Transactional Replication Should Indexed Views be utilized/recommended as method to auto-generate DataMart Reporting tables?

We have a Customer timeline of tables which can be simply joined on CustomerId in each tables.

Indexed views definitely should not be created in high volume OLTP environment database, due to write latency in creating tables. See Brent Ozar Indexed View
 Data Modifications .

However, we want utilize transactional replication of all tables into a separate ReportingDB on another server to create indexed views. This way indexed view will not have any performance reduction on the actual OLTP.

Example OLTP Tables:

Initial Sales tables,
Purchase order tables,
Shipping table, and
Customer Product feedback/Returns table

Yes.  It is generally safe, and often useful to create indexed views on subscriber tables in transactional replication.

You may need to drop and recreate the indexed views using custom scripts before and after initializing from a snapshot.  See Execute Scripts Before and After the Snapshot Is Applied

You can also use a different indexing scheme on the subscriber (also by using post-snapshot scripts). In particular you can make the subscriber tables Clustered Columnstore tables, or add non-clustered Columnstore indexes on them for reporting.


In a banking organization, why would account balance be declared as varchar and not decimal? I worked in two organizations where I found money related columns are defined as VARCHAR data type. I didn't get correct explanation from anyone. As the database team is totally isolated with our team.

Is there any specific purpose for defining the amount, account balance, money in varchar datatype?

We do this in our data warehouse. It's because decimal has a fixed precision and float isn't accurate past a certain number of decimal places. We need to retain the most accurate value extracted from the source as we are summing over 100 million rows and the rounding would put our figures out. However if you've got a fixed number of decimal places, then go for a decimal datatype or similar.


Does limit impact affected rows or not? I have this table:

CREATE TABLE IF NOT EXISTS `usergroups` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) unsigned NOT NULL,
      `group_id` smallint(5) unsigned NOT NULL,
      PRIMARY KEY (`group_id`,`user_id`),
      KEY `id` (`id`),
      KEY `user_id` (`user_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5496 ;

when run this query:

EXPLAIN SELECT `UserGroup`.`user_id` FROM   `usergroups` AS `UserGroup` WHERE  `UserGroup`.`group_id` = 1 limit 30

its out put is:

1       SIMPLE        UserGroup      ref  PRIMARY  PRIMARY  2        const  543      Using index

I think something is wrong! because it affected 543 rows but I think it should affect 30 rows at maximum. Is it true?

Limit does have an impact on how many rows are selected/affected. 543 is just the number of rows which match the joins and the where clause.


SQL Server database backups I am looking to do regular backups of my SQL Server databases.

Is there any reason why I should not just backup just the MSSQL.10/DATA folder? I've got a great backup solution in place from Rackspace which does incremental change based backups.

I'm guessing you are referring to a file system level backup of your data folder?

I am not familar with Rackspace for performing such a task, but have worked with other backup products that allow you to take snapshots of active or inuse files. Which it would allow you to grab a picture of the data and log files of a SQL Server database.

The problem with that is when you take a backup of those files in this manner you want to make sure the database is in a consistent state so it can be recovered, or restored. When you take a snapshot of those files while a transaction is being written to the log file, or committed to the data file, it will not necessarily have a consistent state.

When SQL Server attaches or starts a database it will read in the log file and either roll forward or back any active transactions that have not been committed to the database. If it hits an inconsistent transaction it will mark the database as suspect and you have to clean it up manually, and hope you can get it online.

Another problem that will come up with not using SQL Server native backups can depend on your recovery model. The recovery model basically dictates how your log file is managed for reuse of space. If you are in full recovery model a native full backup helps keep the log under control, taking filesystem level backups will bypass that process so you could end up with a log file out of control.

NOTE: When you determine your backup strategy for your database, you must be confident that you can use it to restore your data. No matter what suggestions you get, always try your backup plan and do a test run on your restore. If you can restore with that backup plan and it meets your recovery needs, then go with it.


Complex Oracle SQL to sum data I am building a SQL query in Oracle 10g that needs to sum overtime hours for the base table, but needs fields from the parent tables as well. I am looking for the fastest way to build the query. The Project_ID in the first three tables is the same, where the Work_ID is one-to-many to the fourth table.

Table J
Project_ID VARCHAR(6)
J_Title VARCHAR(25)

Table K
Project_ID VARCHAR(6)
K_Title VARCHAR(25)

Table L
Project_ID VARCHAR(6)
L_Title VARCHAR(25)
Work_ID VARCHAR(6)

Table C
Work_ID VARCHAR(6)
L_Title VARCHAR(25)
CHRG_DT DATE
WRK_HR NUMBER(5,2)

My best guess looks like this (It's simplified, and the date is actually passed as a variable to the stored procedure):

SELECT
 J.J_Title,
 K.K_Title,
 L.L_Title,
 L.Work_ID,
SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
FROM C,
L,
K,
J
WHERE J.Project_ID = 'ABC'
AND K.Project_ID = J.Project_ID
AND L.Project_ID = K.Project_ID
AND C.Work_ID = L.Work_ID
GROUP BY L.Work_ID,
L.Project_ID,
K.Project_ID,
J.J_Title,
K.K_Title,
L.L_Title

UPDATE: Omitted two fields in Table C. Changed Group By fields, and added Work_ID to output for clarity. Sorry for changes, I have to type this from another network, can't just cut and paste. Also trying to leave out unrelated fields.

This is the output I am getting:

J_Title K_Title L_Title Work_ID WRK_HR
------- ------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC  DEF         30
JOB_ABC KO_ABC  LO_ABC  DFG          3

This is the output I am looking for:

J_Title K_Title L_Title WRK_HR
------- ------- ------- ------
JOB_ABC KO_ABC  LO_ABC      33

I think you might be able to get the results you want with:

select j_title, k_title, l_title, sum(wrk_hr) from
(
  SELECT
   J.J_Title,
   K.K_Title,
   L.L_Title,
   L.Work_ID,
  SUM(CASE WHEN TRUNC(C.CHRG_DT) = TO_DATE('09/27/2013', 'mm/dd/yyyy') THEN C.WRK_HR END) AS WRK_HR
  FROM C, L, K, J
  WHERE J.Project_ID = 'ABC'
  AND K.Project_ID = J.Project_ID
  AND L.Project_ID = K.Project_ID
  AND C.Work_ID = L.Work_ID
  GROUP BY L.Work_ID, L.Project_ID, K.Project_ID, J.J_Title, K.K_Title, L.L_Title
)
group by j_title, k_title, l_title;


What do these error messages mean? This is running on an AWS AMI EC2 Linux instance in a PHP script using dblib and PDO to connect to remote Sybase databases (Sybase version 11.0.1.2596). The server has freetds with these settings:

Compile-time settings (established with the "configure" script)
                        Version: freetds v0.91
         freetds.conf directory: /etc
 MS db-lib source compatibility: yes
    Sybase binary compatibility: yes
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 4.2
                          iODBC: no
                       unixodbc: yes
          SSPI "trusted" logins: no
                       Kerberos: yes

Sometimes when we run queries on the sybase computers we get an error like this (the token can vary):

TDS: unexpected token 95 [0] (severity 0) [(null)]

The query sometimes runs even after this error and sometimes fails. I have searched online but I can't find what this error means or how to fix it. Here is an example of one of the queries:

-- 999 | Hammil | 2017-04-05 08:23:29
SELECT
    999 as 'site_id',
    (select rest_name from micros.rest_def) as 'location',
    now() as 'dt_run',
    emp_seq,
    employee_number as 'obj_num',
    last_name,
    first_name,
    check_number,
    xfer_from_check,
    xfer_from_type,
    xfer_to_check,
    xfer_to_type,
    check_id,
    table_number,
    table_name,
    group_number,
    cover_count,
    order_type,
    order_type_name,
    business_date,
    check_open_date_time,
    check_closed_date_time,
    table_open_date_time,
    printed_count,
    sub_ttl,
    tax_ttl,
    auto_svc_ttl,
    other_svc_ttl,
    paymnt_ttl,
    amount_due_ttl,
    sp_error
FROM
    micros.micros.v_R_guest_checks x
where
    x.check_closed_date_time is null;

Sometimes we get this error:

SQLSTATE[HY000]: General error: 20047 DBPROCESS is dead or not enabled [20047] (severity 1) [(null)]

When this happens, I can run

nc -v -w 4 xxx.xxx.xxx.xxx 2638

and it returns

Connection to xxx.xxx.xxx.xxx 2638 port [tcp/sybaseanywhere] succeeded!

but I will continue to get the error for a few minutes and then it goes away.

An unexpected token in TDS indicates a bad network packet was sent to the TCP Port Sybase is listening to.

This indicates one of two things:

an intermittent network connectivity problem where TCP TDS packets are being corrupted
you have some process attempting to connect to the Sybase instance without using TDS.

I've seen #2 happen with security scanning processes that are looking for open ports.

From your symptoms it sounds like a network connectivity issue.  Since the Sybase Anywhere server is not on the same network segment as your client machine, I'd tend to think the client needs to gracefully handle connectivity problems.


How to connect to a SQL Server installed on Windows Server 2012 running on VPS? I want to connect to my SQL server installed on a windows server 2012, that is running on a VPS(Virtual Private Server) remotely, I have done the following steps:

Enabling SQL Server to allow remote connections
SQL Server Configuration Manager>SQL Server Network Configuration>Protocols For MSSQLSERVER>TCP/IP Set Enabled
Create a new Inbound Rule for port 1443 in windows firewall

Still I cannot connect to SQL server remotely via SQL server management studio.

Thanks

In addition to using SQL Server Configuration Manager to enable TCP/IP connections, you need inbound tcp port 1433 and optionally udp port 1434 open at the firewall for SQL Server to accept inbound connections.

1433 is the default T-SQL tcp endpoint, and udp port 1434 is used by the SQL Server Discovery Protocol to locate SQL Server ports by the instance name, for example SQLSERVER\MYINSTANCE.  If you are not using a named instance, 1434 is not required.

See this Microsoft Docs page for further details.


How to remove symbol from file I am using ssis to generate some .dat files which are then sent to linux server for automated load. It looks as windows add some symbol character in file which is not easily identifiable first but if i open file on linux server i can see that symbol.

For eg. on our side file looks fine but on linux side when opened I see ÿþ is added as first charater in file.  This is UTF-16 (LE) little-endian character represented as Char(255) and Char(254) in decimal. I tried to use replace to remove this symbol but didn't work
For eg. Select replace(replace('ÿþ01CAP   00820130208',char(255),''),char(254),'')

This works fine for string but there is nothing to replace in the file generated on windows side. Once it is uploaded to linux that symbol is sticked into it..
Any ideas ? What could be wrong here ?
I tried to use diff collation in query that generates this file but no luck

Thanks,
NJ

Unicode files should have BOM, is the recommended and accepted way, specially for LE:

  In UTF-16, a BOM (U+FEFF) may be placed as the first character of a
  file or character stream to indicate the endianness (byte order) of
  all the 16-bit code units of the file or stream. If the 16-bit units
  are represented in big-endian byte order, this BOM character will
  appear in the sequence of bytes as 0xFE followed by 0xFF. This
  sequence appears as the ISO-8859-1 characters þÿ in a text display
  that expects the text to be ISO-8859-1.
  ...
  "The UTF-16 encoding scheme may or may not begin with a BOM. However, when there is no BOM, and in the absence of a higher-level protocol, the byte order of the UTF-16 encoding scheme is big-endian."

'linux' is a very generic term. You must be using some application for processing, and if the application is not recognizing the BOM it means is a bad application. Ditch it for something better.


Cannot get a message to go to a SQL Server Service Broker Queue I am trying to setup a simple SQL Server Service Broker messaging setup.  I am following the MS tutorials, but I cannot get the following code to work.  Everything runs fine, but the message does not appear on the queue.  Can anyone identify the problem.

Thanks for any help.

-- This query returns "1"
SELECT
    Is_Broker_Enabled
FROM Sys.Databases
WHERE Name = 'ActivationTest2';
GO

-- Create the message types.
CREATE MESSAGE TYPE TestRequestMessage VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE TestReplyMessage VALIDATION = WELL_FORMED_XML;

-- Create the contract.
CREATE CONTRACT TestContract (
    TestRequestMessage SENT BY INITIATOR,
    TestReplyMessage SENT BY TARGET) ;

-- Create the target queue and service.
CREATE QUEUE ActTestTgtQueue;
CREATE SERVICE ActTestTgtService ON QUEUE ActTestTgtQueue;

-- Create the initiator queue and service.
CREATE QUEUE ActTestInitQueue;
CREATE SERVICE ActTestInitService ON QUEUE ActTestInitQueue;
GO

-- Start a conversation, send the request message.
DECLARE
    @initdlghandle uniqueidentifier,
    @requestmsg nvarchar (100) ;

BEGIN TRANSACTION;

BEGIN DIALOG @initdlghandle
    FROM SERVICE ActTestInitService
    TO SERVICE 'ActTestTgtService'
    ON CONTRACT TestContract
    WITH ENCRYPTION = OFF;

SET @requestmsg = N'<RequestMsg>Message for tgt service</RequestMsg>';

-- This returns a GUID and the message text above.
SELECT
    @requestmsg AS Sentrequestmsg
  , @initdlghandle AS Handle;

SEND ON CONVERSATION @initdlghandle
    MESSAGE TYPE TestRequestMessage (@requestmsg) ;

COMMIT TRANSACTION;
GO
-- Check the queue for messages.
-- THIS RETURNS ZERO ROWS.
SELECT
    *
FROM ActTestTgtQueue;
GO

Look at the transmission_status column in sys.transmission_queue in your database:

use <testdb>;
go

select transmission_status, *
from sys.transmission_queue;
go

The status will tell what the issue is.


Azure Managed Instance - Is it possible to send Sql Logs To Log Analytics? Is it possible to stream a Managed Instances SQL logs into log analytics?

I need a single location for all Azure based monitoring so that multiple teams have visibility/access to log information for issue investigation.

The idea being that if there is ever a failover we don't have any gaps in our SQL Logs as well as having a single location to view this information.

Use the process described in Add Azure Monitor solutions from the Solutions Gallery to add the Azure SQL Analytics (Preview) solution to your Log Analytics workspace.

Once you have created Azure SQL Analytics solution in your workspace, you need to configure the Managed Instance that you want to monitor to stream its diagnostics telemetry to the solution. Follow detailed instructions on this page.

When you add the solution to your workspace, the Azure SQL Analytics tile is added to your workspace, and it appears in Overview. Select View Summary link to load the tile content

Once loaded, the tile shows the number of Azure SQL databases, elastic pools, Managed Instances, and databases in Managed instances that the solution is receiving diagnostics telemetry from.

The solution provides two separate views -- one for monitoring Azure SQL Databases and elastic pools, and the other view for monitoring Managed Instance, and databases in Managed Instances.

To view Azure SQL Analytics monitoring dashboard for Azure SQL Databases and elastic pools, click on the upper part of the tile. To view Azure SQL Analytics monitoring dashboard for Managed Instance, and databases in Managed Instance, click on the lower part of the tile.

Once the Azure SQL Analytics tile for the databases is selected, the monitoring dashboard is shown.


Tuning Mysql and Monitoring I would like to know if anybody can point me towards a tool/way for MySQL database tuning.

Actually the tuning of my.cnf is like below:

My database engine is MyISAM | Mysql 5.1.73

key_buffer = 500M
sort_buffer_size = 256M
read_buffer_size = 256M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 256M
thread_cache_size = 16
thread_concurrency = 32
tmp_table_size = 500M #default 33M!
max_heap_table_size = 500M
skip-name-resolve
skip-host-cache
myisam_recover = BACKUP,FORCE
query_cache_size=256M

My computer is:

Intel(R) Xeon(R) CPU E5620 @ 2.40GHz, 16 cores
Linux 2.6.32-5
Memory - 23.58 GB total

I also have had look at slow query but what do you recognize or recommend?

I've made the select:

SELECT
    CONCAT(ROUND(KBS/POWER(1024,
    IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
    SUBSTR(' KMG',IF(PowerOf1024<0,0,
    IF(PowerOf1024>3,0,PowerOf1024))+1,1))
    recommended_key_buffer_size FROM
    (SELECT LEAST(POWER(2,32),KBS1) KBS
    FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
    table_schema NOT IN ('information_schema','mysql')) AA ) A,
    (SELECT 2 PowerOf1024) B;

3967M

There is no way, there is only the journey! You could start here with MySQLTuner - it's a tuning script which issues recommendations - it doesn't modify anything on the server and I have read (can't find ref, sorry) that it's impact on performance while running is minimal. There's also this - but it seems to be dead.

I'll just repeat the warning that comes with MySQLTuner:

  It is extremely important for you to fully understand each change you
  make to a MySQL database server. If you don't understand portions of
  the script's output, or if you don't understand the recommendations,
  you should consult a knowledgeable DBA or system administrator that
  you trust. Always test your changes on staging environments, and
  always keep in mind that improvements in one area can negatively
  affect MySQL in other areas.

There are many books out there on MySQL - the most highly recommended one at the moment is Schwartz et al's High Performance SQL. Continuous monitoring of your database is as important as tuning, so that when things do start to go wrong, you can at least begin to point the finger in the right direction.

Another thing that is very important to remember is that your MySQL database is just part of a system and it's also critical to be monitoring the various components of that system using Linux's tools for that iostat, vmstat, sysstat &c.

You say:

  I also have had look at slow query but what do you recognize or
  recommend?

but we have no query, no tables (SHOW CREATE TABLE blah1, blah2...) - so it's difficult to help with that. This site is more for specific questions on technical issues rather than broad questions to which you might get a better answer from studying a text.

[EDIT] in response to OP's comments.

Take a look at the table of contents for Schwartz's book here. You'll see that it's a large book with a lot to say about MySQL. More importantly, it places the database in the context of a SYSTEM. I can't tell without more information, but you may need to change the key_buffer_size or your issues may not relate to that particular variable.

In the first instance, I have two recommendations for you - firstly, upgrade to 5.6. Then you'll have the Performance Schema at your fingertips allowing you to properly profile your application - after profiling, you can then proceed to tuning. Profiling is determining where (at a detailled level), your system spends its time - once you figure that out, you can then make rational logical decisions as to where to put your tuning efforts - it could be MySQL variables - it could be to increase RAM, CPU, disk throughput or network bandwidth (in conjunction with OS profiling as I mentioned above).

Secondly, I recommend that you move your system to InnoDB - it makes better use of the machine's resources and is better in almost every way  - Googling "innodb vs myisam" will give you a truckload of links - take a look. MyISAM is nearing end of life - see here for remarks by Bill Karwin and Morgan Tocker - both big hitters in the MySQL world.

Then, read the book that I've recommended (yes, I know, groan...) and then post here if you have more specific questions.

Disclaimer: the usual warnings about testing changes before implementing them on a production system apply!


Does SQL Server care about the location of accounts in Active Directory? Some maintenance is being worked on in our Active Directory here and my supervisor asked me if SQL Server cared about the location of security and service accounts.

I told him I didn't think it did but I wasn't 100% sure.

So, for service accounts and security groups used to control access to the database, does SQL Server care where they are in Active Directory?

No SQL Server doesn't care where in the OU the account and/or groups are.  Everything is done via SIDs, or in the case of the service accounts with the actual username and password to startup the service.


Performance Inserting and Updating Millions of rows into a table We have a CSV the user imports into the Application.

After inspecting the CSV, validating and applying some business logic, we got Inserts and Updates operations we should perform into a table(Subscribers)

We got around 1 millions of inserts and 1 million of updates operations.

Which will be the most effective way to execute them?

1) For the inserts we planned to put them in a CSV file and execute them as a BULK INSERT.

2) What should we do for the updates? Is there something similar to BULK INSERT?

3) Is there any other optimization we should look?

3a) Like for example disabling the indexes while doing this? We should consider the DB should be kept available as normal for the other users of the application...

3b) Suggestions ????

If you are bringing data from a CSV with a large import process, I'd recommend you utilize a staging table.  This will allow you to dump all of your imported data into the staging table that won't be queried by end-users/applications.  You can use this staging table as a source for your INSERT and UPDATE operations.

Once the data is in the staging table you can kick off a stored procedure (or multiple stored procedures, depending on how vast your business logic is) to do the necessary INSERT and UPDATE DML statements into the actual queried table.

As for performance, you need to decide what is more important for your implementation.  If you're looking to get the data in the table as quickly as possible, a very feasible route may be to disable the indexes.  But realize that OLAP performance will be impacted by that.  If you want to ensure the minimal amount of end-user impact then you would have to approach this a different way.  When you say "the DB should be kept availabel as normal" then I would assume the latter.

Performance is double-edged in this situation.  Performance for the data import will most likely take away from performance of end-user interaction with the data, and vice versa.


SQL Server - Unique Constraint Syntax Is there a functional difference between the tables built in the following two ways?
CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test_uq] ADD UNIQUE NONCLUSTERED(f1)

vs.
CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test_uq] ADD CONSTRAINT UQ_f1 UNIQUE (f1);

I believe they're both implementing a unique constraint with a corresponding non-clustered index on the [f1] column but given the different syntax (both in creating and if you script the builds afterwards from SSMS) I'm wondering if one is different (or better?) somehow.  Thanks for any feedback.

Tested - see picture below (renamed table in the second example to test_uq_2)

Not much difference really, but - in my opinion, second example is better, because it gives you the ability to name the constraint/index, while in the first example SQL Server names it automatically


How to delete global variables set in PostgreSQL I had Set a global variable using SET bc.xyz=value
but now I am not able to delete it.

Please help me in it.

This is not really a variable, but a “placeholder parameter”, and once you have set it, there is no way to “undefine” it for the lifetime of the database session.

Using it as a variable is actually abusing the concept, which is to allow specifying extension parameters in postgresql.conf even before the extension is loaded.

You could set the parameter to an empty value (or some other special value) to indicate that you want to unset it.

Alternatively, consider storing such variables in a temporary table, which will automatically be deleted as soon as the database session is done.


Warning in query plan "Cardinality Estimate" create table T(ID int identity primary key)
insert into T default values
insert into T default values

go

select cast(ID as varchar(10)) as ID
from T
where ID = 1

The query above has a warning in the query plan.

<Warnings>
  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[xx].[dbo].[T].[ID],0)" />
</Warnings>

Why does it have the warning?

How could a cast in the field list affect the cardinality estimate?

This warning was new for SQL Server 2012.

From
New "Type Conversion in Expression....." warning in SQL2012 ,to noisy to practical use

  I see what you mean. While I agree that this is noise in most cases,
  it is low priority for us to fix. We will look at it if we get more
  feedback. For now I have closed this by design.

Connect was killed and it doesn’t look like the original issue was transferred to UserVoice. Here’s a different UserVoice issue about the same problem, Type conversion in may affect CardinalityEstimate - Convert/cast on selected columns

I will provide the boring answer until someone comes along with a better one.

  Why does it have the warning?

Speculation on my part.
There is a cast on a column that is used in the where clause which make statistics of that column interesting. A change of datatype makes the statistics no good so lets warn about that in case the value from the field list might end up to be used somewhere.

  How could a cast in the field list affect the cardinality estimate?

It can't unless it is the field list in a derived table.


Can I revert a transaction after a certain number of days I have a Sql script file which does a bunch of operations. I have transaction inside the script so if something happens the whole script is rolled back.

The question I have is that say the script executed successfully and while testing the application certain errors were found.

Now I want to revert all the operations that happened inside the script after say x days. This is a transactional db and I don't have to worry about any new data addition since this is just a staging environment..All I care about is to revert all the structural or data modifications that the SQL Script did by itself. Can someone please tell me what the options are for that.

Do I need to write the exact opposite statements in the rollback script for the operations that the original script did.

Example:
Main Script      -Insert Into Customers values(1,'Test','USA')
Rollback script  - delete from  Customers where id = 1 ..and so on for other transactions(I have simplified this I have over 300 operations in the SQL Script file)

Thanks

Thanks

There isn't anything built into SQL Server that will undo statements. Your options are:

Restore from backup or from database snapshot
Buy a third party development tool like Red Gate's SQL Compare & Data Compare - which may require creating another database, making your changes, and then comparing the differences between them
Buy a log reader tool like Quest Litespeed - which can read Litespeed's transaction log backups and generate undo scripts for specific transactions
Writing your own undo script - whatever changes you plan to make, write the undo scripts yourself to undo your changes


Can I use an RMAN Backup of DB1 to replace DB2 I have a backup of an Oracle 11g database on Linux, that is a version that is 'prescrub' needed by other users.  This is for an ACTIVE database that is now scrubbed, so I do not want to use it as the 'active' database for the clone.

I have a second database that is no longer needed, to which I want to overwrite with the 'prescrub' backup of DB1.  Can I restore the PRESCRUB backup of DB1 over DB2?  If so, what are the appropriate commands or modifications needed?

Sure you can, you can duplicate a database using RMAN backups. For example:

shut down DB2
delete all datafiles, redo logs, controlfiles of DB2
start DB2 in NOMOUNT
with RMAN, connect to DB1 as target, DB2 as auxiliary:

rman target sys/password@DB1 auxiliary /

duplicate the database until the required time, and rename files on the fly (if required), for example::

duplicate database to db2
until time "to_date('2016-02-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')"
db_file_name_convert (/oradata/DB1, /oradata/DB2)
logfile
'/oradata/DB2/redo01.log' SIZE 50M,
'/oradata/DB2/redo02.log' SIZE 50M,
'/oradata/DB2/redo03.log' SIZE 50M;


Provisioning SQL Server 2019 I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with.
My build has to run two versions of the same 35Gb database.
My intention is to split the 410Gb drive on the new box up as follows:
L:\ to hold log files. Based on a rule of 25% of the size of the database = 9Gb per DB = 18Gb).
T:\ to hold tempdb files. 4 data files (one per logical processor) at 1.5Gb each with a log file of 3Gb and a further 2Gb per DB for auto growth = 11Gb.
D:\ drive of 190Gb for data and an E:\ drive of 190Gb for all backups.
Does that sound like a sensible split?

If you only have one drive, you won't gain performance by splitting it logically but you said you want to split the drive into logical drives for logs and data so if one runs out of room it will not cripple the others' content. However, if either your data drive or your log drive or your tempdb drive is out of space then you are down. I would instead keep on the same drive in order to maximize the space you do have. It really sounds like you have plenty of room for two 35 GB databases. Monitor your mdfs and ldfs for auto growths and your drive for free space. Move your system databases to the same drive. Compress your databases. If the databases are not in simple recovery model, be sure to take frequent log backups. Compress your backups. Make sure you are copying your backups to another location so you have a copy in case your drive crashes. Make sure you have jobs to prune msdb history and old backups.
I wouldn't size my log files or my tembdb based on a rule of 25% or anything else. Size them pretty small to start and watch them autogrow and then resize them to that.


Can I force MSDTC to control only within a single database? Using MS SQL Server 2008 R2 we have .NET code and we would like to use nested connections to the same database within the same transaction scope.

I understand and accept that this will require escalation to DTC.

My concern is that our sql instance houses database for distributed applications that, in some cases, require interaction through APIs only. If I enable MSDTC I believe it will apply across databases... this means that when we consume APIs or others consume our API on the same SQL instance we are potentially controller transactions between two theoretically separable applications.
Perhaps if the sql actions fail in the methods employed by the API it may be none of my business, and they shouldn't know or care whether I'm invoking their method inside if a DB transaction.

So, in my specific case, if I could use MSDTC solely within my one database it would be appropriate. I am ok with an exception if I do an action across databases requiring escalation.

Microsoft Distributed Transaction Coordinator is a service that runs on the machine that cannot be configured per database.  The service is either installed, or it isn't.

Since a distributed transaction must use a linked server, you could theoretically use permissions on any linked servers you have configured to allow only your process access.  You could use a DDL Trigger to prevent creation of any further linked servers (or at least to audit their creation).

Ad-Hoc Distributed Queries can be turned off at the server level using EXEC sp_configure 'Ad Hoc Distributed Queries' - this setting may help prevent the majority of distibuted query attempts, especially if the users don't have the ability to create stored procs.


Hide bottom status bar in SSMS Is there a way to hide the most bottom status bar? It doesn't have that much useful information (line number, column number, etc) and takes a decent amount of screen real-estate. I don't think it's hideable but I figured I'd ask.

See image below

SSMS 17.6

On v18.5
Tools->Options->Environment->General->Uncheck Display status bar


Foreign keys can't be added because they exist but they don't exist! I have a conundrum. I am re-engineering a sqlserver 2005 database, and in the course of doing so have attempted to correct a simple lookup table flaw between tblUsers, tblUserRoles, and tblRoles (tblUserRoles had its own pk field).

In trying to make this change, foreign key relationships where dropped and recreated, except they weren't.

Whenever I try to:

ALTER TABLE [dbo].[tblUserRoles] ADD CONSTRAINT [Relationship26] FOREIGN KEY ([UserID]) REFERENCES [dbo].[tblUsers] ([UserID])
go
ALTER TABLE [dbo].[tblUserRoles] ADD CONSTRAINT [Relationship27] FOREIGN KEY ([RoleID]) REFERENCES [dbo].[tblRoles] ([RoleID])
go

I get:

Msg 547, Level 16, State 0, Line 230

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Relationship26". The conflict occurred in database "AhelpDev", table "dbo.tblUsers", column 'UserID'.
Msg 547, Level 16, State 0, Line 232
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Relationship27". The conflict occurred in database "AhelpDev", table "dbo.tblRoles", column 'RoleID'.

But when I:

ALTER TABLE [dbo].[tblUserRoles] Drop CONSTRAINT [Relationship26]
go
ALTER TABLE [dbo].[tblUserRoles] Drop CONSTRAINT [Relationship27]
go

I get:

Msg 3728, Level 16, State 1, Line 236
'Relationship26' is not a constraint.
Msg 3727, Level 16, State 0, Line 236
Could not drop constraint. See previous errors.
Msg 3728, Level 16, State 1, Line 238
'Relationship27' is not a constraint.
Msg 3727, Level 16, State 0, Line 238
Could not drop constraint. See previous errors.

When I run:

select * from  sys.Foreign_keys where name in ('relationship26', 'relationship27')

It returns no records. I'm stumped, I turn to you for help!

I've even attempted to copy the database to see if I could leave it behind...I can't!

I look forward to your answers.

You cannot add the constraints to tblUserRoles because the data within this table already violates the constraints that you are trying to add.

a) Double check that all UserID values in tblUserRoles reference UserID values in tblUsers

b) Do the same as the above for tblRoles and RoleID.

c) As I imagine both of the above will return records that are not in the referenced table, you'll need to clean up your data before adding the Foreign Keys

d) Name you foreign keys something useful! If I came to work on your database, how am I supposed to know what Relationship26 means?!


Under what conditions are polymorphic associations used? What are the alternatives? As I understand it, polymorphic relationships among tables in a database allow one table to have one foreign key that references many tables.

I have found that tags are a common application of this structure. Many tables may have a tag, and a polymorphic relationship allows each of these tables to reference a single tags table.

Unfortunately, I have not been able to find very much background on this topic, either on google or on this site. Most of the writing is either too general (c.s. theory) or too specific (implementation).

I would like to learn more about their use, and how these structures compare to alternatives. Is there a good reference on the use of polymorphic associations in databases?

In general, I have never found polymorphic associations to work very well.  The problem is that once you open up something like this, you have to have unique identifiers across the tables and these need to be enforced.  Otherwise you end up with a case where "customer has the same 'id' as an invoice and therefore ship-to information is mixed up."

The alternative is to define polymorphic association groups and use foreign keys.  For example we might have a table entitled "objects" and in it we might have two columns:  table_name and object_id.  We can then use triggers to maintain that table and have all tables in the polymorphic set define foreign keys against this association catalog (by table name and id) and we can therefore guarantee uniqueness that way.  We can then set foreign keys on the other side, and reference the catalog table.  This isn't perfect  If there is a trigger malfunction it is possible for a reference to be dangling, but it avoids the worst of the join projection and insert anomilies.  Also depending on the complexity of the catalog, mutual foreign keys may be useful.

Finally some databases (like PostgreSQL) would allow table inheritance to be used to create something like this, but this is a rather advanced topic.....


Cannot Start SQL Server Reporting Service That's the first time I use SSRS so my apologies if I'm asking something stupid.

I have a SQL Server 2008 R2 already installed with SSRS. When I try to start the SQL Server Reporting Service I receive this error:

  System.ServiceProcess.TimeoutException: Time out has expired and the
  operation has not been completed.    at
  System.ServiceProcess.ServiceController.WaitForStatus(ServiceControllerStatus
  desiredStatus, TimeSpan timeout)    at
  ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean
  start)

Can someone help me with this issue? Which tests cna I do in order to debug/fix it? Thanks in advance!

If you open sql configuration manager, right click the Reporting Services service, go to properties, then advanced you should see an option to turn on error reporting.

It will also have the dump directory location. Check the latest log in there and it should tell you why it couldnt start.


Does a varchar column in be eliminated in a 3NF database? There is a debate on if a VARCHAR column breaks 3rd Normal Form. Specifically the column represents a NAME that can be derived from other metadata specific to the entity. Occasionally the column contains customized input from users.

Should the column be eliminated and the application concatenate the metadata together to display the value, OR does a VARCHAR column that occasionally gets modified not break 3NF and should stay in the table.

If there are a lot of rows with customizations, I might keep the name column in the main table.

If on the other hand, only a small percentage of the rows in the main table ever contain a customized name column, I might consider making a related table to store the name column.

For instance:

IF OBJECT_ID(N'dbo.CustomName', N'U') IS NOT NULL
DROP TABLE dbo.CustomName;
IF OBJECT_ID(N'dbo.Main', N'U') IS NOT NULL
DROP TABLE dbo.Main;
CREATE TABLE dbo.Main
(
    MainID int NOT NULL IDENTITY(1,1)
        PRIMARY KEY CLUSTERED
    , LastName varchar(30) NOT NULL
    , FirstName varchar(30) NOT NULL
) ON [PRIMARY];

CREATE TABLE dbo.CustomName
(
    MainID int NOT NULL
        CONSTRAINT PK_CustomName
        PRIMARY KEY CLUSTERED
        CONSTRAINT FK_CustomName_MainID
        REFERENCES dbo.Main(MainID)
    , CustomName varchar(30) NOT NULL
) ON [PRIMARY];

GO

INSERT INTO dbo.Main (LastName, FirstName)
VALUES ('Vernon', 'Max');

INSERT INTO dbo.CustomName (MainID, CustomName)
VALUES (1, 'Maxwell Vernon, Esq.');

The output might look like:

SELECT m.MainID
    , [Name] = ISNULL(cn.CustomName, m.FirstName + ' ' + m.LastName)
FROM dbo.Main m
    LEFT JOIN dbo.CustomName cn ON m.MainID = cn.MainID

+--------+----------------------+
| MainID |         Name         |
+--------+----------------------+
|      1 | Maxwell Vernon, Esq. |
+--------+----------------------+

For a main table with only a small number of rows, this might not be worthwhile, however if the Main table is very large, and the number of customizations is relatively small, this technique may be something worth investigating.


Better way to ensure referential integrity of one column to a composite primary key? Given a first table

CREATE TABLE table1 (
   column1 INTEGER,
   column2 TEXT,
   PRIMARY KEY (column1, column2));

does SQL have an idiomatic way to constrain column3 in a second table

CREATE TABLE table2 (
    column3 INTEGER,
    column4 TEXT,
    PRIMARY KEY (column3, column4));

such that column3 ⊆ column1?

My current solution is this kludge:

CREATE TABLE table0 (
    column0 SERIAL PRIMARY KEY);

CREATE TABLE table1 (
   column1 INTEGER REFERENCES table0 (column0),
   column2 TEXT,
   PRIMARY KEY (column1, column2));

CREATE TABLE table2 (
    column3 INTEGER REFERENCES table0 (column0),
    column4 TEXT,
    PRIMARY KEY (column3, column4));

I learned the hard way that a foreign key cannot refer to only one value of a composite primary key: https://stackoverflow.com/questions/3996774/foreign-key-relationship-with-composite-primary-keys-in-sql-server-2005.

I'm using PostgreSQL, if that matters.

Edit (2020-04-26)

One comment recommended I add a concrete example.

The table table1 contains aliases. Multiple people can be named 'Jane Doe', and any one 'Jane Doe' can also be called 'Jane Smith' and 'Jane1990'. Each person is uniquely identified by the number in column1. Either column can have duplicates, but duplicate rows are not allowed.

The table table2 lists books published by people in table1, but table1 can include people who have never published a book, and so column3 ⊆ column1. Again duplicate rows are not allowed.

In some complex domains (banking, telekom) name is always an entity not an attribute. An individual can have more than one passport (names, nationality) I would create a table structure for the scenario you provide such;

create table author (
  id integer PRIMARY KEY,
  default_name_id integer -- you may / may not need this
  -- , any other author properties
);

-- just name aliases
create table author_name (
  id integer PRIMARY KEY,
  author_id integer NOT NULL REFERENCES author(id),
  name text not null,
  unique (author_id, name)
);

alter table author add FOREIGN KEY (default_name_id) REFERENCES author_name (id);

create table books(
  id integer PRIMARY KEY,
  book_name text
);

-- a book can be published by more than one author
create table book_publisher (
  book_id integer    REFERENCES books (id),
  author_id integer  REFERENCES author (id),
  PRIMARY KEY (book_id, author_id)
);

Postgresql have array types. Its also very useful. However most of ORM's doesn't support it. If you can use arrays in your application code then its simpler;

create table author (
  id integer PRIMARY KEY,
  name text[],  -- name[1] is default name.
  -- , any other author properties
);


Problem installing mssql-tools "ODBC Driver 17" Whenever I try to apt-get install mssql-tools, I get,

Preconfiguring packages ...
(Reading database ... 302749 files and directories currently installed.)
Preparing to unpack .../msodbcsql17_17.0.1.1-1_amd64.deb ...
Installation failed, ODBC Driver 17 for SQL Server detected!
dpkg: error processing archive /var/cache/apt/archives/msodbcsql17_17.0.1.1-1_amd64.deb (--unpack):
 subprocess new pre-installation script returned error exit status 1
Selecting previously unselected package mssql-tools.
Preparing to unpack .../mssql-tools_17.0.1.1-1_amd64.deb ...
Unpacking mssql-tools (17.0.1.1-1) ...
Errors were encountered while processing:
 /var/cache/apt/archives/msodbcsql17_17.0.1.1-1_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)

I've tried removing /usr/lib/libmsodbcsql-17.so and also removing odbcinst.ini,

dpkg: error processing archive /var/cache/apt/archives/msodbcsql17_17.0.1.1-1_amd64.deb (--unpack):
 trying to overwrite '/usr/lib/libmsodbcsql-17.so', which is also in package msodbcsql 17.0.1.1-1

From ServerFault,

sudo dpkg -P msodbcsql
sudo apt -f install


Improving MySQL efficiency I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by different A and save in the database? How much can I gain?
I have one table named entry: {entryid (PK), B}, containing 6 thousand records, and several other tables with the similar structure T1: {id(PK), entryid, C, ...}, containing over millions of records. Do the following two processes have the same efficiency?

SELECT id FROM T1, entry WHERE T1.entryid = entry.entryid AND entry.B = XXX

and

SELECT entryid FROM entry WHERE B = XXX
//format a string S as (entryid1, entryid2, ... )
//then run
SELECT id FROM T1 WHERE entryid IN S

I would be considering the use of table partitioning. You don't mention mysql version or storage engine types. Here is the doc link:

http://dev.mysql.com/doc/refman/5.6/en/partitioning.html

for 5.6


Monitoring SQL Server 2012 slow queries/queries which are causing blocking I am using SQL Server 2012 and I want to get hold of the queries which are taking longer to execute along with the queries which are causing blocking on the server. When I look at the server for the running queries to identify any blocking I don't find any query.

I have setup extended events and getting only high level information from that. The tables which are getting timeouts but I need information which tells more about the queries which caused the timeouts.

Any suggestions???

You can make use of the dmv's sys.dm_exec_query_stats and sys.dm_exec_requests to analyse the resource consuming queries along with their duration for what long:

Use the query below to capture the same:

-- Execute the query inside target database
 SELECT TOP 10
      qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
      qs.total_elapsed_time / 1000000.0 AS total_seconds,
      qs.execution_count,
      SUBSTRING (qt.text,qs.statement_start_offset/2,
      (CASE WHEN qs.statement_end_offset = -1
      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
      o.name AS object_name,
      DB_NAME(qt.dbid) AS database_name
FROM
      sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
      LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
      qt.dbid = DB_ID()
ORDER BY
      average_seconds DESC;

Moreover would suggest if you are using extended events, you can capture the same. For more on how to set up and use the same, please read this whitepaper from Jonathan Using SQL Server 2008 Extended Events which should get you going alon with here

Also, when the queries run , and what all process run at the background and to check if blocking is really out there along with any particular waits , would suggest you to use SP_WHOISACTIVE from How to Use sp_WhoIsActive to Find Slow SQL Server Queries

Also, if you prefer to use Profiler you can track and troubleshoot the same as explained in How to Identify Slow Running Queries with SQL Profiler


Retrieving all PK and FK I have a big database that I need to extract all primary keys and foreign keys from each table.

I have pgAdmin III.

Is there a way to do this automatically and not go over each table manually?

You can use the function pg_get_constraintdef(constraint_oid) in a query like the following:

SELECT conrelid::regclass AS table_from
     , conname
     , pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  contype IN ('f', 'p ')
AND    connamespace = 'public'::regnamespace  -- your schema here
ORDER  BY conrelid::regclass::text, contype DESC;

Result:

 table_from | conname    | pg_get_constraintdef
------------+------------+----------------------
 tbl        | tbl_pkey   | PRIMARY KEY (tbl_id)
 tbl        | tbl_col_fk | FOREIGN KEY (col) REFERENCES tbl2(col) ON UPDATE CASCADE
...

Returns all primary and foreign keys for all tables in the given schema, ordered by tablename, PKs first.

The manual about pg_constraint.

The manual about object identifier types (regclass,regnamespace, ...).


MySQL views performance for alias purposes So I'm working on a Rails project and I have a legacy database, that is used by other applications, hence its structure cannot be modified.
The problem is that table and column names do not follow the Rails conventions. A common solution to that is to create MySQL views that just alias columns properly.

These are dead simple views that just have

SELECT old_column_1 AS new_column_1, old_column_2 AS new_column_2 FROM table_name;

Some tables are really big, like 22 million rows (2GiB). All the tables are MYISAM. Also there's one MEMORY table with 2 million rows (~350MiB)

The key question is how much of an overhead do I get with such views and does it in any way impact ROW LOCK or TABLE LOCK during INSERTs or JOINs?

YMMV.  Views sometimes fail to do the same optimizations, leading to slower execution than performing the SELECT directly.  Since your case is straight forward, probably you will incur no noticeable overhead.

INSERTs through a VIEW have limitations; sounds like you won't hit them.  Read the manual.

JOINs is a place where the optimizer may drop the ball; suggest you experiment offline with that, before committing to make the change.


MySQL Master/Slave Master/Master setup? I currently have 1 mysql server running and want to install 2 seperate mysql VPS'es. Maybe one master for inserts/updates and one slave for selects. What is the best setup for this if I have the following requirements:

Run backups on machine
When my mysql backups run on the slave machine it generates a high load. Can the master decide to take over some selects to reduce the loads?

Is there a way that the master decides where to execute the query (load balancing)? Is it possible to send all queries to one master and the master decides if he sends it to the slave, so I don't have to make 2 connections in my php code (1 for select, 1 for updates)?

Thanks,
Ward

You might look into MySQL Proxy. Features include:

load balancing
failover
query analysis
query filtering and modification

I haven't run it myself, but it seems to cover what you want.

UPDATE

Apparently the Read/Write splitting is a proof of concept and not for production use


SQL Server RDS Database Restore from S3 stuck in Restore Mode Summary:
We are doing a restore of a SQL Server database from S3 on an RDS Instance. And all though the job will say the restore is complete when we go to access the database it's stuck in "Restore" Mode and we can't do anything with it.
Detail:
We have spun-up a new SQL Server RDS Instance and we're trying to restore a database from back-up stored in S3. Which is simple enough, we had to do it in the past before maybe a year ago or so. We run the command from the AWS Documentation
EXEC msdb.dbo.rds_restore_database
 @restore_db_name = 'OurDB'
, @s3_arn_to_restore_from = 'arn:aws:s3:::bucket/SQLBackUp.bak'
, @with_norecovery = 1

And we know that command won't work if the Options Group and IAM roles aren't set up, so we've made sure they are. When we run the command it takes a few minutes but appears to run fine.
We run the command to check on the status
EXEC msdb.dbo.rds_task_status
 @db_name='OurDB'

And what as % complete grows over the next few minutes to 100% and the lifecycle value says "SUCCESS". However when I try to access the database, I'm unable to and it says it's stuck restoring and the exact error message I get is "The database OurDB is not accessible. (ObjectExplorer)"
Whenever I search RESTORE DATABASE Stuck RDS SQL SERVER or variations of that, all the stuff I find is about doing a restore of the entire RDS Instance and not an individual database. Or if I'm able to find something about restoring a database that's stuck then it's not about RDS but about regular SQL Server.
Here are some screenshots of what I'm seeing too.

It's not stuck, it's just still in restore mode because you used @with_norecovery = 1. You need to restore database with recovery to finish the process.


How to get Page Name given Page ID in Oracle Application Express Prefix: I have looked all over the net for this answer. Let me prefix the question by describing what I am trying to do: I am trying to create a unique list of shortcuts to pages in my application for each user. So I am wanting to use a dynamic list to query for shortcut list. I already have procedures in place and ready to get the user ID and the page ID. I am using apex 5.0

The Question: What is the table and column (if it even exists) I need to query for in order to get the page name if given the corresponding page ID. ie:

select UNKNOWN_COLUMN_FOR_APEX_PAGE_NAME
from UNKNOWN_TABLE_FOR_APEX_PAGE_INFO

Alternate Question: Is there an apex global variable like :APEX_USER or :APEX_PAGE_ID that I can use to get the page name/title.

Everything you want (and a lot more) is available in the view APEX_050000.APEX_APPLICATION_PAGES

SYS@mydb01> describe apex_050000.APEX_APPLICATION_PAGES
Name                        Null?    Type
--------------------------- -------- -------------------------------------
WORKSPACE                   NOT NULL VARCHAR2(255)
WORKSPACE_DISPLAY_NAME               VARCHAR2(4000)
APPLICATION_ID              NOT NULL NUMBER
APPLICATION_NAME            NOT NULL VARCHAR2(255)
PAGE_ID                     NOT NULL NUMBER
PAGE_NAME                   NOT NULL VARCHAR2(255)
PAGE_TITLE                           VARCHAR2(255)
PAGE_MODE                            VARCHAR2(16)
...
PAGE_ALIAS                           VARCHAR2(255)

And a sample query would be

select WORKSPACE, APPLICATION_NAME,
   PAGE_ID, PAGE_NAME, PAGE_TITLE, PAGE_MODE
from  APEX_050000.APEX_APPLICATION_PAGES
where workspace  = 'MY_WS' and page_id=210;

There is a built-in substitution string APP_PAGE_ID and APP_ID, so you could do this in your page to use the current page ID and application ID to get the page title:

select PAGE_TITLE from APEX_050000.APEX_APPLICATION_PAGES
where PAGE_ID = :APP_PAGE_ID and APPLICATION_ID = :APP_ID;

See https://docs.oracle.com/cd/E59726_01/doc.50/e39147/concept_sub.htm#BEIIBAJD for all the built-in substitution strings.


Index against a subset of rows? Is it possible to create an index against a subset of rows fulfilling a certain condition?

If it is possible how does one ensure the index is used?

Yes, what you're talking about is a Filtered Index.  It works just like you think, by creating an index off of a subset of data.  Below is an excerpt from the above reference:

  A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

To your other question:

  how does one ensure the index is used?

A few things will dictate this index being used:

A well-designed filtered index correlated with
A well-designed query

Obviously things like data distribution and many other factors play a role, but that goes with both filtered and non-filtered indexes.


MySQL replication and ProxySQL I just came across ProxySQL. I have one simple question, if I were to use ProxySQL, do I still need to set up Replication(master-slave) first for MySQL server? Because there is hostgroup(0,1) in ProxySQL which to determine which MySQL server master and slave based on the hostgroups. Or just configure in ProxySQL? Kindly clarify on which steps should I use. Thank you.

Yes, you will need an existing replication environment since ProxySQL is a proxy service. It can work with Percona XtraDB Cluster, MariaDB Galera cluster or MySQL InnoDB Cluster as well.