Querying for 2nd highest numeric value Database set up here (was too large for sqlfiddle):
https://pastebin.com/Hd0DbQ5z

This is an MS Access DB.

I am looking to get the top score as well as the second top score.  I have the following query for getting the top score which is the exact results I am looking for:

SELECT e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN
(SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

I would like to tweak this query to show me the 2nd place scores, here is the closest I got (but with some unexpected results):

SELECT e.GAME_VER, e.CLAN, e.SCORE, e.S_REGION
FROM dev AS e
INNER JOIN (SELECT GAME_VER, MAX(SCORE) AS MAXVAL FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' AND ID NOT IN ( SELECT ID FROM dev as f INNER JOIN  (SELECT GAME_VER, MAX(SCORE) AS MAXVAL2 FROM dev WHERE REGION = 'NA' AND S_REGION = 'USA' GROUP BY GAME_VER) AS MAXSCORES2 ON  (f.SCORE = MAXSCORES2.MAXVAL2) AND (f.GAME_VER = MAXSCORES2.GAME_VER)) GROUP BY GAME_VER)  AS MAXSCORES ON (e.SCORE = MAXSCORES.MAXVAL) AND (e.GAME_VER = MAXSCORES.GAME_VER)
ORDER BY e.GAME_VER;

(Sorry for the formatting, the query is kind of messy)

This query gives me the results that I am looking for, but it is returning rows from different S_REGION values even though I have them constrained to a single region in the query.

+----------+---------+----------+----------+
| GAME_VER |  CLAN   |  SCORE   | S_REGION |
+----------+---------+----------+----------+
|     3.25 | Melons  | 93.97925 | USA      |
|    3.375 | Melons  | 94.96925 | USA      |
|      3.5 | Bananas |   95.883 | USA      |
|    3.625 | Grapes  |    96.74 | USA      |
|     3.75 | Bananas |   97.503 | USA      |
|    3.875 | Grapes  |   98.354 | USA      |
|     3.99 | Lemons  |   96.777 | USA      |
|        4 | Grapes  |   99.092 | USA      |
|    4.125 | Grapes  |   99.698 | USA      |
|     4.25 | Grapes  |   100.39 | USA      |
|    4.375 | Grapes  |  100.994 | USA      |
|    4.375 | Apples  |  100.994 | PANAMA   |
|      4.5 | Bananas |  101.618 | USA      |
|    4.625 | Bananas |   102.22 | USA      |
|     4.75 | Bananas |  102.804 | USA      |
|    4.875 | Bananas |  103.342 | USA      |
|     4.99 | Lemons  |  101.619 | USA      |
|        5 | Bananas |  103.823 | USA      |
|    5.125 | Bananas |  104.428 | USA      |
|     5.25 | Bananas |  105.014 | USA      |
|    5.375 | Bananas |   105.49 | USA      |
|      5.5 | Bananas |  105.944 | USA      |
|    5.625 | Grapes  |  106.379 | USA      |
|     5.75 | Grapes  |    106.5 | USA      |
|     5.75 | Grapes  |    106.5 | CANADA   |
+----------+---------+----------+----------+

What is going wrong? Or is there a better way of writing the query to get the results I'm looking for. (I'm a noob).

Regarding the mismatched regions:  Only the subqueries (i.e. the innermost aggregate queries) are constrained. Such constraints do not directly limit the outer query. So if a score from another S_REGION matches a score from the constrained region, then it will also be returned in the outer query.  That is unless you further constrain the outer query to have the same constraints.

This is true even of your first query even though you might not have noticed: If there ever is another region with tied top scores, you would get values from the other regions also. Try it... add a row with another region matching a USA high score and it will appear in your first top-score query.

Perhaps more useful would be to treat REGION and S_REGION exactly as you have GAME_VER in that you group by these values in all subqueries, then only add specific constraints in the final query.  Not only would this fix your problem of returning mismatched regions, it would easily allow you to view top scores from any region simply by removing or changing the constraints only on the outer query.  This is what I'll do in the examples below.

Embedded subqueries are convenient for posting on Stack Overflow and for keeping everything together in one place.  However, Access is not a fully optimized SQL engine.  It neither creates nor stores optimized execution plans for each subquery.  For subqueries in the WHERE clause, it will almost always re-execute each subquery for every row, even if theoretically it could run it just once and then refer back to the same temporary dataset.

Access does a better job at optimization of saved queries.  Thus, I recommend "saving subqueries" then using JOINS on the saved query rather than embedded SQL.  It allows easier reuse of the subquery and within Access it allows the subquery to be edited and viewed like a normal query.

With all that in mind, here's my suggested solution:

Saved query MAXSCORES:

SELECT e.REGION, e.S_REGION, e.GAME_VER, Max(e.SCORE) AS MAXVAL
FROM dev AS e
GROUP BY e.REGION, e.S_REGION, e.GAME_VER;

Replacement for the first query of top scores (properly filtered on desired region):

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAXSCORES
    ON (e.S_REGION = MAXSCORES.S_REGION) AND (e.REGION = MAXSCORES.REGION)
      AND (e.GAME_VER = MAXSCORES.GAME_VER) AND (e.SCORE = MAXSCORES.MAXVAL)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN;

The 2nd place scores could be obtained just how you did it (of course updated with appropriate constraints), but as already stated, I avoid embedded subqueries in the WHERE clause.  Further, the following query not only returns the 2nd place scores, but it also indicates which groups did not have any 2nd place score--it returns null values.  Lastly, this query returns the 1st and 2nd place scores in one query on the same row which could be convenient for various comparison queries.

Saved query MAX2NDSCORE:

SELECT e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL,
  Max(IIf([SCORE]<[mx].[MAXVAL],[SCORE],Null)) AS MAX2NDVAL
FROM dev AS e LEFT JOIN MAXSCORES AS mx
    ON (e.REGION = mx.REGION) AND (e.S_REGION = mx.S_REGION)
      AND (e.GAME_VER = mx.GAME_VER)
GROUP BY e.REGION, e.S_REGION, e.GAME_VER, mx.MAXVAL;

A query that returns 2nd place clans for the desired region:

SELECT e.REGION, e.S_REGION, e.GAME_VER, e.CLAN, e.SCORE
FROM dev AS e INNER JOIN MAX2NDSCORES AS mx2
    ON (e.SCORE = mx2.MAX2NDVAL) AND (e.GAME_VER = mx2.GAME_VER)
      AND (e.S_REGION = mx2.S_REGION) AND (e.REGION = mx2.REGION)
WHERE (e.REGION='NA' AND e.S_REGION='USA')
ORDER BY e.REGION, e.S_REGION, e.GAME_VER, e.CLAN

Finally, its worth stating that these queries do not properly consider or identify tied scores, but there is nothing in the question or table schema that suggests there cannot be tied scores. The queries could return multiple clans that have the same score.


Index for range of timestamp with varchar fields I am using postgress database (Version 9.5.4), I have a table structure like this,

create table test (
    id serial primary key,
    company_name varchar(255) not null,
    department varchar(255) not null,
    joined_date timestamp not null
);

Following are my exact queries (Only the below three not any other),

Query 1

SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25';

Query 2

SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx';

Query 3

SELECT *
FROM test
WHERE joined_date::date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx'
  AND department = 'yyy';

I want to speed up my query execution time, I tried to add index some thing like this,

CREATE INDEX index_test ON test (joined_date, company_name, department);

After creating index, the query execution time doesn't have any improvement. On adding EXPLAIN keyword, I found the index is no longer used. But when adding equals condition instead of between keyword, index is being used. But my use case would be within date range and optional fields with equal condition. How to achieve my index use case with most efficient performance?

Index  will be used if you remove cast to date (joined_date instead of joined_date::date) from WHERE clause (upper bound in BETWEEN should be changed accordingly).

  select *
  from test
  where joined_date >= '2017-09-25' AND joined_date <'2017-10-26'  ;

Alternatively , you can create index as
CREATE INDEX index_test2 ON test (cast (joined_date as date), company_name, department);

Proof

CREATE TABLE test (
    id           serial       PRIMARY KEY,
    company_name varchar(255) not null,
    department   varchar(255) not null,
    joined_date  timestamp    not null
);

INSERT INTO test(company_name, department,joined_date)
SELECT
  md5(x::text),
  CASE WHEN x%2 = 0 THEN 'foo' WHEN x%3 = 0 THEN 'bar' ELSE 'baz' END,
  now()
FROM generate_series(1,1e6)
  AS gs(x);

CREATE INDEX index_test
  ON test (joined_date, company_name, department);

VACUUM ANALYZE test;

Query 1

EXPLAIN ANALYZE SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25';
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..8.45 rows=1 width=49) (actual time=0.069..0.069 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone))
 Planning time: 0.142 ms
 Execution time: 0.097 ms
(4 rows)

Query 2

EXPLAIN ANALYZE SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx';
                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..8.45 rows=1 width=49) (actual time=0.016..0.016 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone) AND ((company_name)::text = 'xxx'::text))
 Planning time: 0.111 ms
 Execution time: 0.047 ms
(4 rows)

Query 3

SELECT *
FROM test
WHERE joined_date BETWEEN '2017-09-25' AND '2017-10-25'
  AND company_name = 'xxx'
  AND department = 'yyy';

                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_test on test  (cost=0.42..6.20 rows=1 width=49) (actual time=0.017..0.017 rows=0 loops=1)
   Index Cond: ((joined_date >= '2017-09-25 00:00:00'::timestamp without time zone) AND (joined_date <= '2017-10-25 00:00:00'::timestamp without time zone) AND ((company_name)::text = 'xxx'::text) AND ((department)::text = 'yyy'::text))
 Planning time: 0.120 ms
 Execution time: 0.046 ms
(4 rows)


How to find view dependencies in Netezza? I want to find the dependent table of views in Netezza. Is there a table that provides the details?

This is actually pretty hard to do on Netezza, because there are no system tables keeping track of table dependencies in views. Therefore you need to parse every view to get the referenced objects. There is a Unix support script on the server which does exactly this, and will dump the results in a table. You might need to install the support package. Replace databasename by your database and run this command.

nz_view_references -create -schemas ALL DATABASENAME

When the script is finished (be patient), the results are located in table SYSTEM..NZ_VIEW_REFERENCES


index does not get used when there is "OR" in the query I have an index on the check_timestamp column, the index gets used by the following query

... where check_timestamp < NOW() - INTERVAL '30 days' ORDER BY  check_timestamp  NULLS FIRST LIMIT 10

But the index does not get used with the following query

... where (check_timestamp < NOW() - INTERVAL '30 days' OR check_timestamp IS NULL) ORDER BY check_timestamp  NULLS FIRST LIMIT 10

Why is this happening? any way around it?

Below is the database schema

id  uuid [gen_random_uuid()]
street_db   text
check_timestamp timestamptz NULL
last_accessed   timestamptz NULL
website text
street  text
in_use  boolean [false]
zipcode_db_id   uuid NULL

In general PostgreSQL is capable of using an index in this situation.  It can scan the index two times and combine them with a BitmapOr.

The reason it cannot or has chosen not to do this in your particular case would depend on the particulars of your case, which you are not sharing with us.  Perhaps it just thinks the alternative plan will be cheaper.


How can we optimise this OR and ORDER BY query? We have a table with the following structure:

+----+----------+-------------+---------------------+-----------+---------+----------+
| id | Batch_id | Merchant_id |     ExpiryDate      | Allocated | MaxUses | IsBarred |
+----+----------+-------------+---------------------+-----------+---------+----------+
|  1 |        1 |        5793 | 2019-11-03 01:23:40 |         1 |       1 |        0 |
|  2 |        1 |        1238 | 2019-11-07 01:23:40 |         0 |       1 |        0 |
|  3 |        2 |        9427 | 2019-11-09 04:49:40 |         1 |       1 |        0 |
+----+----------+-------------+---------------------+-----------+---------+----------+

In DDL terms this is:

CREATE TABLE `merchant_coupon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ExpiryDate` datetime DEFAULT NULL,
  `MaxUses` int(11) NOT NULL,
  `Batch_id` int(11) DEFAULT NULL,
  `Merchant_id` int(11) DEFAULT NULL,
  `Allocated` int(11) NOT NULL,
  `IsBarred` tinyint(1) NOT NULL
  PRIMARY KEY (`id`),
  KEY `IDX_8C9078EBAF5CDCE0` (`Batch_id`),
  KEY `IDX_8C9078EBE10A5A83` (`Merchant_id`),
  CONSTRAINT `__FK_8C9078EBAF5CDCE0` FOREIGN KEY (`Batch_id`) REFERENCES `batch` (`id`),
  CONSTRAINT `__FK_8C9078EBE10A5A83` FOREIGN KEY (`Merchant_id`) REFERENCES `merchant` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10388795 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

We would like to find the row which:

Is not Barred
Has Allocated < Max Uses
Has an Expiry Date greater than a certain date
Is a member of one or more batches
Is the soonest expiring (i.e. ExpiryDate is closest to now)
We only need 1 of the rows.

Our query at the moment looks like this:

    SELECT mc.*
    FROM merchant_coupon mc
    WHERE 1 = 1
      AND mc.IsBarred = 0
      AND mc.AllocatedCount < mc.MaxUses
      AND mc.ExpiryDate > '2018-11-13 18:44:17'
      AND (mc.Batch_id = 288
               OR mc.Batch_id = 446
               OR mc.Batch_id = 561
               ...
               OR mc.Batch_id = 4167
               OR mc.Batch_id = 5731
        )
    ORDER BY mc.ExpiryDate ASC
    LIMIT 1

This works, but we suspect there are opportunities to improve it.

The explain looks like this:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,mc,range,"IDX_8C9078EBAF5CDCE0,IDX_8C9078EBE10A5A83",IDX_8C9078EBAF5CDCE0,5,,184481,Using where; Using filesort

Some other things to note:

The database is MySQL
The IDX_8C9078EBAF5CDCE0 is on the Batch_id. We also have an index on the Merchant_id
The Batch_id normally groups the rows into groups of between 2-10,000 items
The Batch that the Batch_id refers to also refers to the Merchant_id, so we could eliminate the AND Merchant_id = <x>, but we keep it as a safety check, just in case
The table contains about ~10 million rows, and is growing in the order of ~20,000-60,000 a week atm.
Once a row is selected, the Allocated column will be incremented - we do a SELECT ... FOR UPDATE in fact.
IsBarred is mostly 0.
In most cases MaxUses is 1, but in some it can be a number in the range of 1-1000.

What indexes would most likely optimise the query? Or, what changes could we make to the query to optimise it?

I've been reading dba stackexchange for a while now, and these kind of questions come up often, and they always kind of frustrate me.

People often seem to go straight to explain plans, and or looking for special indexes like they're magic bullets - but they're really not.

What you should get into doing (everybody) is thinking these problems through from a logical standpoint - if you, as a human, wanted to search through ~10 million lines of data to achieve this, how would you best organise the data to help you do this? That's all an index is, basically, is an ordering of the data. The computer does the work a bit faster than you, that's all.

By all means use those other tools and experimentation to help you with your understanding and refinement of yours skills, but don't use it as the primary go-to. That's a crutch.

Maybe I'm a bit of a idealist, and anyone who is more than a couple of years younger than me just isn't used to manually dealing with these kind of tasks, you just type them in to google - but think of how you might use a phonebook*

you don't open it at page 1, find Andrew Aardvark, ask youself "is this Walter Williams?" . no... next
line 2 - Arthur Abraham - is that Walter Williams? ... no.
line 3 - Alex Acer - Walter Williams? no
And so on.

No, you use an algorithm.

these were big fat books, made of paper, with everyone's phone number in. We had them in the 20th century.

So anyway, here's how I always approach your problem.

[1] Continue thinking of this problem like a phonebook.

[2] What columns could I sort the book by to help me search the data? - you're right in that it's those columns affecting the selection - columns in the WHERE clause, and columns in the ORDER BY

[3] So systematically... column by column

[4] mc.IsBarred - I didn't get as far reading your notes (IsBarred is mostly 0) before I disounted this. The name gave me the clue - this is presumably effectively a binary flag - Low cardinality data is generally not a good sorting column

Think of the phonebook. If we sorted the phonebook not by name, but by hair colour - even if we knew Walter Williams had red hair, and could home straight in on the 10% of the book containing just those guys (me included) it'd still take a while to search them one by one.

[5] mc.AllocatedCount or mc.MaxUses - no. The problem here comes from your comparing one column against the other. There's no ordering you could apply to the list by either column, or one then the other, which could zero you in quickly on to a small subset of data.

[6] mc.BatchID - this doesn' appear to be an unreasonable candidate. Although your code doeesn't actually show exactly how many values you're selecting from, or the cardinality of the data.

If you could search through just those sections of the phonebook relating to batch 446, and then those for batch 561, etc. That would certainly narrow the amount of searching you did - but you would still have to search through and compare every single row in that set, and then find the one which matches the rest of the clause, AND has the earliest date.

OK, we'll pencil it that column as a possible...

[7] mc.ExpiryDate - appears in both your where clause AND your order by ...

interesting...

If you sorted your phonebook by expiry date, you could turn to the page corresponding to 18:44 on 2018-11-13, and find the first record. If it matched all of your where criteria - you would have to look no further. Since it is by definition the earliest expiring date after that date - you know this is the record you're looking for.

If it's not, you have to look at the second row, then rinse and repeat. AS soon as you find a matching row, you know it's got the earliest expiry date and can stop.

This is the only potential indexing which can find you your single row in one attempt.

mc.ExpiryDate IS YOUR CANDIDATE FOR INDEXING

I have a few caveats.

[A] I'm not overly familiar with MySQL. Exactly how the query engine will interpret your code telling it to ORDER BY and then LIMIT, I don't know for sure. As a human I can think this through as detailed above - but whether MySQL will attempt to fetch all matching rows before finding earliest I can't say. I sesiously doubt it'd be that silly, but have no firm evidence.

[B] If you're adding 60,000 rows of data to the table in a week - that's 60,000 operations which will run marginally slower each time, as they have to maintain an extra index. Think through the implications of that.

There are always thade-offs with these things...

[C] If your where clause still filters out many many rows, the query may still have to search through many records. It depends a lot on the actual data. But that's where your own knowledge is key in this kind of situation. Use that!


ENUM column auto set I have two tables "using" foreign keys:

Referencing table

CREATE TABLE `requests` (
`request_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
--
`status` enum('new','approved') DEFAULT NULL,
--       ^^^^
PRIMARY KEY (`request_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Referenced table

CREATE TABLE `status` (
`status_id` int(11) NOT NULL AUTO_INCREMENT,
`status_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`status_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now, I want something like this, If I create a new row, say, 'pending' name in the status table then status (list of ENUM values) of requests table automatically updated with 'pending' ENUM value. So, the "final" status allowed ENUM values in the request table would be:

status('new','approved','Pending')

Change the status table to match

  CREATE TABLE `status` (
  `status` enum('new','approved') DEFAULT NULL,
  `status_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`status`),
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

There is no need for AUTO_INCREMENT.

Then do this for each table:

ALTER TABLE ...
  MODIFY COLUMN `status` enum('new','approved','Pending') DEFAULT NULL;

Be sure to change all references to status_id.


Nullable, dependent attributes I'm designing a database for students. Computer science students here in Switzerland can decide between different specializations, e.g. application development, system engineering etc.

There are students from other professions in this application as well, which have no such specialization. A few examples:

+--------------------+-------------------------+
| Profession         | Specialization          |
+--------------------+-------------------------+
| Computer scientist |                         |
|                    | Application development |
|                    | System engineering      |
|                    | Support                 |
|                    |                         |
| Electrician        |                         |
|                    | none                    |
|                    |                         |
| Janitor            |                         |
|                    | none                    |
|                    |                         |
| Architect          |                         |
|                    | Small buildings         |
|                    | High buildings          |
|                    |                         |
+--------------------+-------------------------+

I hope you get the idea. My question now is, how do I design the database tables with these attributes, since they're dependent of each other? Every user has a profession, some do not have a specialization depending on their profession. So an electrician should not be an application developer, nor should an architect.

My thoughts so far 1

+-----------------------------+
| User                        |
+-----------------------------+
| #id                         |
| profession_id               |
| specialisazion_id, nullable |
+-----------------------------+

Enforce logic through constraint checks

Approach 2

+-------------------+     +----------------+     +------------+
| User              |     | Specialization |     | Profession |
+-------------------+     +----------------+     +------------+
| #id               |  +--| #id            |  +--| #id        |
| username          |  |  | name           |  |  | name       |
| specialization_id |--+  | profession_id  |--+  +------------+
+-------------------+     +----------------+

Manage the logic myself and ensure, that every profession with no specialization has a specialization entry.

Approach 3

+----------------+     +-----------------------------+     +------------+
| Specialization |     | spec_prof                   |     | Profession |
+----------------+     +-----------------------------+     +------------+
| #id            |--+  | #id                         |  +--| #id        |
| name           |  +--| specialization_id, nullable |  |  | name       |
+----------------+     | profession_id               |--+  +------------+
                       +-----------------------------+
                                                |
                                                |
                              +--------------+  |
                              | User         |  |
                              +--------------+  |
                              | #id          |  |
                              | username     |  |
                              | spec_prof_id |--+
                              +--------------+

Somehow all different approaches feel clumsy, dirty. What are the arguments for and against the different approaches? Is there a better way?

And how do I even search for this problem? Is dependent the correct naming?

Any help is greatly appreciated.

I would model this using a single table for the Professions, and a related table for Specializations.

USE tempdb;

DROP TABLE IF EXISTS dbo.Users;
DROP TABLE IF EXISTS dbo.Specializations;
DROP TABLE IF EXISTS dbo.Professions;
GO

CREATE TABLE dbo.Professions
(
    ProfessionID int NOT NULL
        CONSTRAINT PK_Professions
        PRIMARY KEY CLUSTERED
    , ProfessionName  varchar(30) NOT NULL
);

CREATE TABLE dbo.Specializations
(
    ProfessionID int NOT NULL
        CONSTRAINT FK_Specializations_ProfessionID
            FOREIGN KEY
            REFERENCES dbo.Professions(ProfessionID)
    , SpecializationID int NOT NULL
    ,   CONSTRAINT PK_Specializations
        PRIMARY KEY CLUSTERED
        (ProfessionID, SpecializationID)
    , SpecializationName varchar(30) NOT NULL
);

Some sample data:

INSERT INTO dbo.Professions (ProfessionID, ProfessionName)
VALUES (1, 'Computer Scientist')
    , (2, 'Electrician')
    , (3, 'Janitor')
    , (4, 'Architect');

INSERT INTO dbo.Specializations (SpecializationID, SpecializationName, ProfessionID)
VALUES (1, 'Application Development', 1)
    , (2, 'System Engineering', 1)
    , (3, 'Support', 1)
    , (4, 'Small Buildings', 4)
    , (5, 'Tall Buildings', 4);

The professions, with their associated specializations:

SELECT p.ProfessionName
    , sp.SpecializationName
FROM dbo.Professions p
    LEFT JOIN dbo.Specializations sp ON p.ProfessionID = sp.ProfessionID

Results:

╔════════════════════╦═════════════════════════╗
║   ProfessionName   ║   SpecializationName    ║
╠════════════════════╬═════════════════════════╣
║ Computer Scientist ║ Application Development ║
║ Computer Scientist ║ System Engineering      ║
║ Computer Scientist ║ Support                 ║
║ Electrician        ║ NULL                    ║
║ Janitor            ║ NULL                    ║
║ Architect          ║ Small Buildings         ║
║ Architect          ║ Tall Buildings          ║
╚════════════════════╩═════════════════════════╝

If your users can only have a single profession, I would create a Users table like this:

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY
        CLUSTERED
    , UserName varchar(30) NOT NULL
    , ProfessionID int NOT NULL
        CONSTRAINT FK_Users_Profession
        FOREIGN KEY
        REFERENCES dbo.Professions(ProfessionID)
    , SpecProfID int NULL
    , SpecializationID int NULL
    , CONSTRAINT FK_Users_Specialization
        FOREIGN KEY (SpecProfID, SpecializationID)
        REFERENCES dbo.Specializations(ProfessionID, SpecializationID)
    , CONSTRAINT CK_Users_ProfSpec
        CHECK (
            (SpecProfID IS NULL AND SpecializationID IS NULL)
            OR (COALESCE(SpecProfID, 0) = COALESCE(ProfessionID, 0))
            )
);

The check constraints ensure only valid professions and specializations can be added at the expense of requiring a "duplicate" ProfessionID column for specializations.

Inserting users then looks like this:

INSERT INTO dbo.Users (UserID, UserName, ProfessionID, SpecProfID, SpecializationID)
VALUES (1, 'Little Johhny', 1, 1, 2)
    , (2, 'Mary Quite Contrary', 2, NULL, NULL);

Seeing the users with their professions and specializations looks like:

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    INNER JOIN dbo.Professions p ON u.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON p.ProfessionID = sp.ProfessionID
                   AND u.SpecializationID = sp.SpecializationID;

╔═════════════════════╦════════════════════╦════════════════════╗
║      UserName       ║   ProfessionName   ║ SpecializationName ║
╠═════════════════════╬════════════════════╬════════════════════╣
║ Little Johhny       ║ Computer Scientist ║ System Engineering ║
║ Mary Quite Contrary ║ Electrician        ║ NULL               ║
╚═════════════════════╩════════════════════╩════════════════════╝

Attempting to insert invalid data looks like:

INSERT INTO dbo.Users (UserID, UserName, ProfessionID, SpecializationID)
VALUES (3, 'Peter Pumpkin Eater', 3, 2);

  Msg 547, Level 16, State 0, Line 83
  The INSERT statement conflicted with the CHECK constraint "CK_Users_ProfSpec". The conflict occurred in database "tempdb", table "dbo.Users".

If I needed to support users with multiple professions, I'd use a cross-reference table, like this:

DROP TABLE IF EXISTS dbo.UsersProfessions;
DROP TABLE IF EXISTS dbo.Users;
GO

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY
        CLUSTERED
    , UserName varchar(30) NOT NULL
);

CREATE TABLE dbo.UsersProfessions
(
    UserID int NOT NULL
    , ProfessionID int NOT NULL
    , SpecProfID int NULL
    , SpecializationID int NULL
    , CONSTRAINT PK_UsersProfessions
        PRIMARY KEY CLUSTERED
        (UserID, ProfessionID)
    , CONSTRAINT FK_UsersProfessions_ProfessionID
        FOREIGN KEY (ProfessionID)
        REFERENCES dbo.Professions(ProfessionID)
    , CONSTRAINT FK_UsersProfessions_Specialization
        FOREIGN KEY (SpecProfID, SpecializationID)
        REFERENCES dbo.Specializations (ProfessionID, SpecializationID)
    , CONSTRAINT CK_UsersProfessions_Specialization
        CHECK (
            (SpecProfID IS NULL AND SpecializationID IS NULL)
            OR (COALESCE(ProfessionID, 0) = COALESCE(SpecProfID, 0))
            )
);

Insert data:

INSERT INTO dbo.Users (UserID, UserName)
VALUES (1, 'Little Johnny')
    , (2, 'Mary Quite Contrary')
    , (3, 'Peter Pumpkin Eater');

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID, SpecProfID, SpecializationID)
VALUES (1, 1, 1, 1)
    , (1, 4, 4, 5)
    , (2, 2, NULL, NULL)
    , (2, 3, NULL, NULL);

Query the data:

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    LEFT JOIN dbo.UsersProfessions up ON u.UserID = up.UserID
    LEFT JOIN dbo.Professions p ON up.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON up.SpecProfID = sp.ProfessionID AND up.SpecializationID = sp.SpecializationID

Results:

╔═════════════════════╦════════════════════╦═════════════════════════╗
║      UserName       ║   ProfessionName   ║   SpecializationName    ║
╠═════════════════════╬════════════════════╬═════════════════════════╣
║ Little Johnny       ║ Computer Scientist ║ Application Development ║
║ Little Johnny       ║ Architect          ║ Tall Buildings          ║
║ Mary Quite Contrary ║ Electrician        ║ NULL                    ║
║ Mary Quite Contrary ║ Janitor            ║ NULL                    ║
║ Peter Pumpkin Eater ║ NULL               ║ NULL                    ║
╚═════════════════════╩════════════════════╩═════════════════════════╝

Once again, the constraints in place prevent invalid profession/specialization combinations:

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID, SpecProfID, SpecializationID)
VALUES (3, 3, 3, 2);

  Msg 547, Level 16, State 0, Line 149
  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UsersProfessions_Specialization". The conflict occurred in database "tempdb", table "dbo.Specializations".

This will also support users with a profession, that have not specialized:

INSERT INTO dbo.Users (UserID, UserName)
VALUES (4, 'Max');

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID)
VALUES (4, 1);

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    LEFT JOIN dbo.UsersProfessions up ON u.UserID = up.UserID
    LEFT JOIN dbo.Professions p ON up.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON up.SpecProfID = sp.ProfessionID AND up.SpecializationID = sp.SpecializationID

╔══════════╦════════════════════╦════════════════════╗
║ UserName ║   ProfessionName   ║ SpecializationName ║
╠══════════╬════════════════════╬════════════════════╣
║ Max      ║ Computer Scientist ║ NULL               ║
╚══════════╩════════════════════╩════════════════════╝


Joining two tables with different fields through a link-table Im a complete novice to Access (2013).

Im doing some History research and thought it would be easier to use Access. I have created two table so far.

People, Details info about them
Events (historical).

Both have different fields.

Can I create a third table that collates all the info from the first two tables?

Therefore, I can 'organise' the dates to display in order. (may need to look at DOB and Event Date to merge under name field name to do this?)

I've uploaded the file for you to see if it helps. I tried to search for answers before posting but nothing along the lines im looking at was thrown up. Also have no coding experience, so this is completely new to me.

Thank you in advance

Link to Database File

People and Events are related thorugh a many-to-many relationship, because an event can have many people related to it, and one person can be related to several events.

This is represented in a SQL database by means of a link-table, which contains (at least) two columns (fields), one indicating the unique id representing the Event, the other one indicating the unique id representing the Person (People). Let's call those columns EventId and PeopleId. The table might contain more columns if they should provide some relevant extra information (let's say you want to know which role the person had with regard to this event, such as "was the organiser", "was an attendant", "was a sponsor", "was the inducer", "led the event", "was one of the lead roles", "just was a passer-by", "was the victim", ...).

The primary key of this table is actually a compound one, comprising both (EventID, and PeopleID). So your table should be defined in a fashion similar to the one depicted in the following image:

You can let Access know how this table is related to the other ones by means of the Relationships Diagram:

The corresponding SQL definition is:

CREATE TABLE [People_x_Events]
(
    [PeopleID] LONG NOT NULL REFERENCES [People] ([ID]),
    [EventID]  LONG NOT NULL REFERENCES [Events] ([ID]),
    PRIMARY KEY ([PeopleID], [EventID])
) ;


How to create a custom map report not sure if this is the right forum. But just wondering is there a way one can make a map report out of an image or a map made on excel. I have the data and I've looked around and found that its possible to edit world maps, etc, to show information. Is it possible to make a simple map from scratch, to show something like a floor plan, something that isn't available on google maps.
Thanks guys in advance sorry if I'm not being clear on what I'm asking. If i can be clearer please ask.

Note: I'm currently using SSRS and QGIS to try do this. I have the data i need to show but don't have the co-ordinates for the thing i want to show. I thought that could be something i could do when I'm making the map. Only a small map to show one piece of information about 200 distinct data points.

Short version is yes its possible to do what you are describing.

Your report will have a spatial query to display the shapes you want. Whether that's a map or a floor plan.

You can then add analytical data in a second query to colour the map. The two queries needs to be joined by a key.
Im not familiar with qgis but ideally you'd want both the spatial data and analytics in sql server.

The other option is to have 2 layers. The first layer gives the 'map' shape and the 2nd adds points on top.

If you search Google images for "SSRS map" you'll see lots of examples.


Fault tolerance in mysql servers I have built an application as part of my UG thesis that uses mysql database (community edition). Now, my professor wants me to provide fault-tolerance to the database (by parity!!)! I argued that we wouldn't need that. Anyway I searched, and found here that mysql has built-in replication engine (a mechanism to provide fault-tolerance, right?), along with many other techniques to provide reliability. But what I learned from there is that there is a master server and some slave servers to provide the fault tolerance using replication. Now my questions are:

What if I have only one database server? Does mysql have any fault-tolerance for single standalone database servers (i.e. no master-slave formation, no cluster, etc.)?
Do I need to try to provide any kind of fault-tolerance whatsoever for the data stored in mysql database?
What kind of differences are there (in terms of fault-tolerance only) between community edition and enterprise edition of mysql servers?

Somehow I get the feeling that we don't need to do anything for providing fault-tolerance to mysql db, and it is fine by itself. But I need some solid info on the matter.

Bounty edit:

The second question from above again:

Do I need to try and provide any kind of fault-tolerance whatsoever for the data stored in mysql database?
What is the sensibleness of trying to provide fault tolerance to a mysql database by using parity (bit per byte)?

A little details about the data in the database:

The data to be specific is a collection of about 6500 Unicode strings, less than 1 MB in size, is initialization data, that will never change over time. The only transaction will be to read the data from the database, no update no delete. My application requires full-text search on those strings, and this is the only reason I am using mysql since it provides full-text search. I am aware that I could avoid mysql's FT search by using something like elasticsearch instead.

What is likely to break?
How risk-adverse are you?
Do you need to repair the failure, or just discover it?

A "parity" check can only discover failure, hence it is not fault-tolerant.

Many people decide that the main failure point is the disk subsystem, so they use RAID.  In that context, you can assume that parity is can be used to repair -- but only because there is some other mechanism to say "this drive failed".

But what if the motherboard dies?

So you use Replication and Master-Slave with the two servers sitting next to each other.

But what if the power to the building goes out?  Or a tornado hits the two servers?  Or ...

So you put the Slave in another data center.

(I could continue this silly story, but I won't.)

You can put two "instances" of MySQL on the same server, then have one replicate to the other.  Then put a lot of spin on how great your solution is.  (Never mind that practically any failure will take out both copies.)

Or you could spend a few bucks and rent Amazon space for the other server.  Then you can honestly brag about the "fault-tolerance".

After Bounty Edit

Use Engine=InnoDB; this gives you simpler recover from server crash.

After loading the data, take a dump (mysqldump or other) of the static table, and store it elsewhere.  This is for "disaster recovery" from floods, meteorites, software glitches, disk failure, etc.  Reloading would be manual and take some time (but you have not put limits on that).

Those are simple measures, and effectively cover virtually all disasters.  If I were to list other things that can go wrong with a mysql setup, "parity" does not show up as a part of any solution.

To finish the task you have, set up your disk with RAID-5.  Three drives is the minimum.  You could probably fake it with software raid and partitions of a single drive.  However, this would make useless for recovering from any kind of failure; instead it would show the use of "parity".

"Checksums" are more often used for catching (but not correcting) errors.  This is typically 4-8 bytes overhead for 512-16KB bytes of data.  That is not, technically, "parity", but it is more efficient.

One parity bit per byte gives you error detection, but not error correction.  See SECDED for correction.  That needs, for example, 8 bits on a 64-bit 'word'.  Seymour Cray said "parity is for farmers", but eventually he implemented SECDED in 'core' memories.  (This was in the '70s.  Does your prof date back that far?)


Log Shipping Copy Job Fails once in a while I have configured log shipping at multiple database server with monitored server(separate server) and all of them are running fine without any issues.

One particular server has 7 database and the log shipping copy job fails once in a while for all databases, when I checked the error message, every time error remains same:

*** Error: The network path was not found.
(mscorlib) ***

This issue is not persistent and occurs at random time and at any given day(not every day). Below is details of log shipping schedule:

Log backup job is scheduled to run every 15 minute at primary
server(file is generated at monitoring server).
LS Copy job is scheduled to run every 15 minute at secondary
    server, at the gap of 5 minute from log backup job.
LS restore job is scheduled to run at 15 minutes at secondary
    server, at the gap of 5 minutes from LS copy job.

Above jobs are having delay of 5 minutes considering network speed and file size.

Can anyone please suggest what could be the underlying issue.

For time being, I have asked Infra admin to add server name along with its IP in the hosts file at secondary server, don't know if this will solve the issue.

Note: Version of Operating System and Database is same at primary, monitoring and secondary server.

Job failed again for all databases pertaining to the same server and file didn't get copied. No details on the error message and very next run which was scheduled 15 minutes later got succeeded. I have no clue on this.

I think there is something wrong with server setting. Any input will be highly appreciated.

This is not SQL-Server error actually,

This occurs when the LS Copy Job tries to copy the file on destination location(On Shared Network location)
Now, when the location is not available due to temporary connection loss with network, or due to any reason. (when its connected through internet & VPN reasons can be multiple)

So this can be broadly concluded as Network path was not reachable(not available at that time)

Solution: Check that if network location available before copy. If network location reachable then only Init copy.

I hope this answers what you are looking for.


Copy database to a remote server on daily basis I need to replace the copy of my PROD db in a DEV system on a daily basis.

The problem is that I haven't enough space in my DEV system to restore the PROD db. Is there any way to create a shrinked .bak without make changes on original DB?

Thank you

I just want to avoid to spare disk space if possible. I'm searching
  for a way to "shrink on the fly" and then create the bak.. but seems
  to be impossible

Well, general answer here will be NO, there is no easy/simple way to get this done seeing the amount of free space , which is 50GB compared to the database (200GB) whose MDF is 80 GB.

Shrinking on FLY, No again. You have to shrink the file on prod before backing it up or later once restore (which is not the case here, as destination dev is only 50 GB).

It better to go for the option of more space , rather than going a very tedious and complex way ( will be OK, if we can be assured if it will work).

Also, refer to some suggestions/ answered at How to restore a SQL Server database and shrink its files at the same time?


Can NoSQL databases cause occasional data loss? I am currently evaluating databases to use for a new project, which will require insertion and querying of large amounts of trading data. Our team is leaning towards Cassandra, but then I read this article that seems to suggest using non-ACID compliant databases can result in occasional data loss:

  http://www.dbms2.com/2010/09/21/acid-compliant-transaction-integrity/

I can't find any further information on this on the web and can't understand how non-ACID compliance means that data loss can occur. Can anyone shed some light?

Though this is years old question...

In short, you can understand ACID as guarantee of data integrity/safety in any expected circumstances.
As like in generic programming, all the headaches comes from multi-threading.

The biggest issue on NoSQL is mostly ACI. D(urability) is usually a separated issue.

If your DB is single-threaded - so only one user can access at once -, that's natively ACI compliant. But I am sure virtually no server can have this luxury.

If your DB need to be multi-threaded - serve multiple users/clients simultaneously - you must need ACI-compliant transaction. Or you will get silent data corruption rather than simple data loss. Which is a lot more horrible. Simply, this is exactly same with generic multi-threaded programming. If you don't have proper mechanism such as lock, you will get undefined data. And the mechanism in DB called fully ACID compliance.

Many YesSQL/NoSQL databases advertises themselves ACID-complient, but actually, very few of them are really does.

No ACID compliance = You will get always undefined result under multi-user (client) environment. I don't even think what kind of DB does this.
Single row/key ACID compliant = You will get guaranteed result if you modify only single value at once. But undefined result (=silent data corruption) for simultaneous multi row/key update. Most of currently popular NoSQL DBs including Cassandra, MongoDB, CouchDB, … These kind of DBs are safe only for single-row transaction. So you need to guarantee your DB logic won't touch multiple rows in a transaction.
Multi row/key ACID compliance = You will always get guaranteed result for any operation. This is minimal requirements as a RDBMS. In NoSQL field, very few of them does this. Spanner, MarkLogic, VoltDB, FoundationDB. I am not even sure there's more solutions. These kind of DBs are really fresh and new, so mostly nothing is known about their ability or limitation.

Anyway, this is a comparison except D(urability). So don't forget to check durability attribute too. It's very hard to compare durability because range becomes too wide. I don't know this topic well…

No durability. You will lost data at any time.
Safely stored on disk. When you get COMMIT OK, then the data is guaranteed on disk. You lost data if disk break.

Also, there're difference even on ACID compliant DBs.

Sometimes ACID compliant / you need configuration / no automatic something.. / some components are not ACID-complient / very fast but you need to turn off something for this... / ACID-compliant if you use specific module... = we will not bundle data safety by default. That's an add-on, option or separated sold. Don't forget to download, assemble, setup and issuing proper command. Anyway, data safety may be ignored silently. Do it yourself. Check it yourself. Good luck not to make any mistake. Everyone in your team must be flawless DBA to use this kind of DB safely. MySQL.
Always ACID compliant = We don't trade data safety with performance or anything. Data safety is a forced bundle with this DB package. Most commercial RDBMS, PostgreSQL.

Above is typical DB's implementation. But still, any other hardware failure may corrupt the database. Such as memory error, data channel error, or any other possible errors. So you need extra redundancy, and real production-quality DB must offer fault tolerance features.

No redundancy. You lose all data if your data corrupted.
Backup. You make snapshot copy/restore. You lose data after last backup.
Online backup. You can do snapshot backup while the database is running.
Asynchronous replication. Backup for each second (or specified period). If machine down, this DB guaranteed to get the data back by just rebooting. You lose data after last second.
Synchronous replication. Backup immediately for each data update. You always have exact copy of original data. Use the copy if origin breaks.

Until now, I see many DB implementation lacks many of these. And I think if they lacks proper ACID and redundancy support, users will lose data eventually.


MySQL failover: how application can be switched to a new master in master-slave configuration? In my case I have two MySQL servers. One is master and second one is slave.

When slave is not available, my application can read and write from/to master.

When master is not available, assume there is a tool mysqlfailover which successfully nominates a new master (from slave).

It sounds good, but application has master hostname inside the configuration. So it fails to find it.

What are available options to solve this problem? How application can be switched to a new master?

I know that I can switch hostname to point to the new IP, use Kubernetes (StatefulSet) + DB in a container, but anything else as free option on bare metal?

If speaking broadly, how this problem is solved using HA/failover tools?

Thank you

We had faced a similar kind of problem. A Multi-AZ MySQL-RDS provides an automatic failover feature. Everytime a failover occurred, although the URL of the new master would remain the same but still JDBC was not able to connect to the new master.

By default, once a connection to database is made some setups will never refresh DNS entries until the JVM is restarted. This can be solved by setting the networkaddress.cache.ttl parameter of JVM. Setting this value ensures that the DNS entries are refreshed after some interval. In case you are not using Java, try to find an analogous parameter for your connection.

In other words, first ensure that the URL of the new master remains the same(you need to talk to your operations team to understand how it can be done). Once that is done, post failover discovering the new master becomes the responsibility of the application itself which can be achieved by refreshing the underlying DNS entries periodically.


How to fix the sys.host_summary table in MariaDB 10.3.13 using mysqldump while doing a backup gives the following error:

mysqldump: Got error: 1356: "View 'sys.host_summary' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" when using LOCK TABLES

What can I do to fix that?

The sys schema is not bundled with 10.3, but seems to be planned for 10.5. If you don't use/need it, you can safely remove it: DROP DATABASE sys;. If you do need it, you need to try resolving the issue outlined in the error message. For a start you can do SHOW CREATE VIEW sys.host_summary; to see which tables, views and functions it's referencing, as well as the definer/invoker.


TSQL Script Execution Through Power shell, Special Characters Rendering Problem We have a simple T-SQL script file (.sql), which we execute through Powershell. Everything works fine, except the script portion, where we are using some special characters as string (example: 'ô' ).

The same script works fine, if I just execute it through SQL Server Management Studio, but through Powershell it's not rendering these special characters properly.

Here how we access the script file to execute on all databases on instance

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
invoke-Sqlcmd -InputFile "C:\DailyPatch\run.sql" | Out-File -filePath "C:\DailyPatch\dbname.txt"
$text=Get-Content C:\DailyPatch\dbname.txt
$a = $text.length
$a=$a-3

$logFile = 'C:\DailyPatch\dblog.txt'

for ($i=3 ; $i -le $a ;$i++)
{
    write-host $text[$i]
    $error.clear()

    Invoke-sqlcmd -inputfile "C:\DailyPatch\Script4AllDBs.sql"-database $text[$i]-OutputSqlErrors $true|Out-File -filePath "C:\DailyPatch\dbname.txt"

    foreach ($rr in $error) {
        $text[$i] >> $logFile
        $rr >> $logFile
    }
}

How to set collation in Powershell to avoid such kind of issues?

ps. Unicode thing is already handled on script side

Added: Special characters are being changed to � (Replacement Character)

This issue has nothing to do with SQL Server Collations. The problem happens (i.e. the characters get transformed) prior to connecting to SQL Server. If the problem could be resolved by prefixing the string with an upper-case "N", then the script wouldn't work correctly in SSMS (as the OP states it does).

The problem here has to do with the encoding of the file not being known to Invoke-Sqlcmd, thus the bytes making up the file are interpreted incorrectly. This is why it does work correctly when pasting into SSMS (since the encoding of that window is UTF-16LE, like all of .NET / Windows, and is known to SSMS).

I checked the documentation and it does not appear that there is a parameter to set the file encoding like there is with SQLCMD. I assume that the file is being saved as either UTF-16LE ("Unicode" in Microsoft-land) or as UTF-8, but in either case, without a byte-order mark (BOM). I suggest re-saving the file with an encoding that includes the Byte Order Mark ("save with encoding" in some programs like SSMS). Be sure to pick the encoding that specifies either "BOM" or "with signature" (like in MS products). The BOM is 2 - 4 characters (depending on the encoding being used) at the beginning of the file that indicates the encoding being used (i.e. UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, or UTF-32BE). The BOM is a Unicode-only feature and does not exist for non-Unicode encodings. With it being set in the file, any program that opens the file will detect it and know what the encoding is without you needing to specify. Hopefully this holds true for Invoke-Sqlcmd.


Are operators also considered to be keys? I'm taking a MongoDB course and one of the assignments asks to include quotes around keys.

So I did for a query like this {$push: {"key":value}}

But I was told that $push is also a key because it's on the left hand side. I always thought keys were like identifiers for values whereas operators are just actions, they don't have any value associated with.

"$push" is a key in the JSON context. Don't forget everything in mongo (queries, projections, documents, method parameters) are represented as JSON objects. So, although $push is an operator for Mongo, it is still a key on the JSON object it belongs to.

JSON's specification demands key names to be surrounded by double quotes, although the Mongo shell, which is actually a Javascript interpreter, is more flexible and allows key names (field names and operators) without them, as the language does.


Pg_restore getting stuck for a particular server We have 3 postgres VMs hosted on AWS. I have taken a dump of a database from a server. I am able to restore this dump on 2nd server. But whenever I run pg_restore for the 3rd server, it got stuck after some time and I got connection timeout error.backup size is 9 MB.

below is the command I used:

pg_restore -c -h server -U postgres -d dbname "E:\backfile.backup"

I checked using pg_stat_activity, Nobody is using this database except pg_restore.Sometimes it got stuck, sometimes it throws random errors like below:

  pg_restore: [archiver (db)] error returned by PQputCopyData: server closed
      the connection unexpectedly
     This probably means the server terminated abnormally
      before or while processing the request.

How can I debug this issue?

I discussed with my network guys and they said that the server for which restore is working, is accessed using private IP through VPN. Another server is accessed using public IP without any VPN.  There is Difference in latency between public and internal IP addresses.

When I copied the backup file on the same server where Postgres server is hosted and tried restoring the database, it worked within seconds.


Remove the last character from Variable String I have a few variable data in the column "email".

For example:

roy2087@gmail.com123
sg123@yahoo.compori

How do I get rid of the characters after .com?

I used Replace(email,'.com%',gmail.com), however, we cannot use wildcard characters in Replace.

Any Suggestions?

SELECT
    SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) ;

If you want update the strings from a table, chopping off what is on the right of .com:

UPDATE
    tableX
SET
    col = SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) )
WHERE
    SUBSTRING_INDEX(col, '.com', -1) <> col ;


Issues starting Postgres server I'm trying to go through the book "7 Databases in 7 Weeks" and I'm completely stuck at starting a Postgres server.

My current issue is that when I run pg_ctl start I get the following error:

LOG:  database system was shut down at 2013-06-12 17:18:24 PDT
LOG:  invalid magic number 0000 in log file 0, segment 1, offset 0
LOG:  invalid primary checkpoint record
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 1454) was terminated by signal 6: Abort trap
LOG:  aborting startup due to startup process failure

I have no idea how to go about fixing this. I'm running PostgreSQL 9.2.4 on Mountain Lion 10.8.3.

Per JimmyStenke's suggestion, I realized I had a DB initialized from a previous install, and it seems that was causing the error. I deleted it and initialized a new DB.


What is SQL Server Parallel Data Warehouse? What is SQL Server Parallel Data Warehouse solution? What is the benefit of PDW compared to normal DW architecture based on SQL Server and SSIS, SSAS and SSRS?

It isn't inexpensive. I don't know the price tag but you are talking large capital expense measured in hundreds of thousands of dollars. It is an enterprise scale solution that is optimized for large scale enterprise data warehouses. As such, you may find it tough to get a great answer here other than things like the link I provide below.

You said you already did the research, so I won't provide you too many links other than the this one which does a great job explaining PDW.

If you have the budget and enterprise scale that requires PDW, you would be much better off talking to Microsoft and asking them for discussions with reference customers. This right now isn't deployed en masse, so you will get individualized attention if you are in the market for a massively parallel data warehousing appliance.


Question on Oracle Public DB link inside Procedures Scenario :
A procedure 'XYZ' is created with DB account 'SCOTT'. The procedure uses a public DB link to connect to another database.
We have another user 'EVE' who has access to execute procedures owned by 'SCOTT'.
If procedure XYZ is executed by EVE , how does the public DB link inside the procedure work? Does it use the credentials of SCOTT to connect to Target DB ( the owner) or does it use the credentials of EVE ( the executor)?

This depends on how the link was set up. A public link means it is accessible to anyone to access the link but the credentials used to access the remote DB can either be those of the current user or a specific user.

Take a look at CREATE DATABASE LINK for the full syntax and explanations.


How to store temp tables in memory during table "optimize"? When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to accommodate the 5G table. However, I've seen zero performance gain. I've changed the temp directory to tmpfs, but again, zero gain. In fact, regardless of the settings I change, the tmp table always seems to be written to temp files, on disk, within the database's directory.

I assume data is written to disk in a table rotation manner for safety, but I'm wondering if there is anyway to direct the optimize process to create the temp table in memory instead?

Thanks!

Please keep in mind OPTIMIZE TABLE is DDL not DML.

In terms of mechanism, this is what OPTIMIZE TABLE mydb.mytable; does under the hood

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

or

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new DISABLE KEYS;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable_new ENABLE KEYS;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Since it's DDL, mysqld has no plausible reason to copy the table to memory.

BTW for InnoDB, you could do it manually if you want to skip ANALYZE TABLE mydb.mytable;

ALTER TABLE mydb.mytable ENGINE=InnoDB;

Even if you do ANALYZE TABLE mydb.mytable;, InnoDB will do deep dives into the index anyway to do cardinality approximations (See my post From where does the MySQL Query Optimizer read index statistics?).


What does "ISO standard equivalent" actually mean? I'm reading about comparison operators. In the documentation, Comparison Operators Modified by ANY, SOME, or ALL it says (emphasis added):

  Comparison operators that introduce a subquery can be modified by the
  keywords ALL or ANY. SOME is an ISO standard equivalent for ANY.

What does this mean?

It just means that operator SOME is not some MS proprietary extension. It is part of the (ISO/ANSI) SQL standard.


Clients require instance name to connect to clustered instance We have a clustered instance, say Instance1, assigned its own virtual network name and IP, via Failover Cluster Manager. This instance is configured to listen on port 1433 in SQL Server network configuration (verified in the logs).

Within our intranet, we can connect to this instance by using just the IP address, say 172.0.1.2 (as well as the FQDN) using SSMS. However, some external clients cannot connect unless they specify 172.0.1.2\Instance1 explicitly.

I don't understand why this is the case. From what I understand, it doesn't make sense that the instance name needs to be specified given that the IP is assigned to the instance itself.

I noted in SQL Server network configuration that the IP address assigned via Failover Cluster Manager is not explicitly listed as an active enabled address. Should that be added?

From further research, it appears the issue may be a result of older SQL clients performing an "InstanceValidity" check when connecting to SQL Server (specifically sqlsvr32.dll driver). The driver passes "MSSQLServer" as the instance to verify for the InstanceValidity check.  In this case, since the instance name listening on the Default port (1433) is named "Instance1", the check fails because the Instance names do not match the InstanceValidity check.

Specifying the port number or instance name explicitly in the connection string works.


Does PostgreSQL rewrite JOINs with OR's into UNIONs? (Abbreviated) table summary:

-- Table cases:
     id                  SERIAL       PRIMARY KEY,
     application_number  VARCHAR(30)  NOT NULL,
     publication_number  VARCHAR(30)  NOT NULL,

-- Table patents:
     case_id                 INTEGER      PRIMARY KEY,  -- FK to cases(id)
     pct_application_number  VARCHAR(30)  NOT NULL,
     pct_publication_number  VARCHAR(30)  NOT NULL,

-- All character columns have working trigram indexes:
     CREATE INDEX cases_application_number_trgm_idx ON cases
         USING GIN (application_number gin_trgm_ops);
         -- (etc)

The following query is slow (~200ms) because it doesn't use the indexes:

SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%' OR p.pct_application_number ILIKE '%1234%'

The following queries are all fast (1-2ms):

-- AND instead of OR
WHERE  c.application_number ILIKE '%1234%' AND p.pct_application_number ILIKE '%1234%'

-- OR, but only table "cases"
WHERE  c.application_number ILIKE '%1234%' OR c.publication_number ILIKE '%1234%'

-- OR, but only table "patents"
WHERE  p.pct_application_number ILIKE '%1234%' OR p.pct_publication_number ILIKE '%1234%'

-- Simulating the OR with a UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  c.application_number ILIKE '%1234%'
 UNION
SELECT  c.id
  FROM  cases c
        JOIN patents p ON p.case_id = c.id
 WHERE  p.pct_application_number ILIKE '%1234%'

Here is the EXPLAIN ANALYZE output for the slow query:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=2329.80..10590.54 rows=7 width=4) (actual time=54.951..186.713 rows=35 loops=1)
   Hash Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   ->  Seq Scan on cases c  (cost=0.00..4981.99 rows=142099 width=12) (actual time=0.011..32.875 rows=142099 loops=1)
   ->  Hash  (cost=1142.58..1142.58 rows=68258 width=11) (actual time=31.105..31.105 rows=68258 loops=1)
         Buckets: 131072  Batches: 2  Memory Usage: 2473kB
         ->  Seq Scan on patents p  (cost=0.00..1142.58 rows=68258 width=11) (actual time=0.019..11.995 rows=68258 loops=1)
 Planning time: 1.875 ms
 Execution time: 186.780 ms
(10 rows)

The query as posted here is heavily reduced to illustrate the problem. The actual query is more complex and involves a text search in six (or more) columns in five (or more) tables, with about 10 output columns. I guess I could rewrite all that as series of queries and connect them into a huge UNION... is there a better way to deal with this problem?

Adding query plan with enable_seqscan disabled (as requested):

                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.71..18767.92 rows=7 width=4) (actual time=4.809..150.368 rows=35 loops=1)
   Merge Cond: (c.id = p.case_id)
   Join Filter: (((c.application_number)::text ~~* '%1234%'::text) OR ((p.pct_application_number)::text ~~* '%1234%'::text))
   Rows Removed by Join Filter: 68223
   ->  Index Scan using cases_pkey on cases c  (cost=0.42..14942.96 rows=142099 width=12) (actual time=0.004..32.695 rows=142097 loops=1)
   ->  Index Scan using patents_pkey on patents p  (cost=0.29..2275.63 rows=68258 width=11) (actual time=0.003..11.942 rows=68258 loops=1)
 Planning time: 1.007 ms
 Execution time: 150.399 ms
(8 rows)

I'm afraid there is no good solution for you now, other than rewrite into a UNION (or denormalize/refactor the data).

There is a proposal to add automatic conversion of ORs to UNIONs but it needs more testing and reviews to get it into v11 of PostgreSQL, but I have verified that it does work for a case just like yours.


Right way to restart Oracle 12c Server with pluggable databases I am new to oracle. I installed Oracle 12c on windows with a pluggable database on a test VM. Everything was working fine. After the installation I was able to connect via EM Express on port 5500, via SQLPlus and via SQL Developer.

Them I restarted the server (windows shutdown and restart) and nothing worked. After some serious reading and a lot of trial and error I was able to connect again by starting the listener, the container database and the pluggable database. Also I had to restart the Listener service via the windows services application.

Now I have a couple of questions. Given my environment what is the recommended procedure to restart my oracle server? Is shutting down windows OK or should I shut down the oracle databases and listener before shutting down windows? What is the right startup process after a restart? Startup the container and pluggable databases and them the listener or vice versa?

All of those questions translate into just one: What is the right way (procedure and order) to shutdown and restart a windows server with Oracle 12c that has a pluggable database?

I have been reading about all those aspects but given my inexperience I haven’t found a guide about what to do in what order and why.

Thanks in advance!

I want to propose my findings as an answer. I have tested these procedures and they seem to work fine.

NOTE: The whole experiment was done on Windows Server 2012 R2, however based on my readings I would say most of it applies to Linux too.

Shutdown

Shutdown the Oracle Database Instance to ensure that everything close cleanly
https://docs.oracle.com/database/121/ADMQS/instance.htm#ADMQS052
http://www.dummies.com/how-to/content/how-to-stop-an-oracle-12c-database.html
Shutdown Windows Server

Restart

Start Windows Server
Start the Listener
https://docs.oracle.com/database/121/ADMQS/network.htm#ADMQS044
Start the Database instance
https://docs.oracle.com/database/121/ADMQS/instance.htm#ADMQS052

Remarks

Restarting the Windows Server should do everything automatically but there are a couple of remarks.

Starting the Oracle Services on windows is not the same as starting the database instance. However they are configured by default to behave that way. Oracle Administration Assistant for Windows can be used to determine what exactly happens with the database instance when the Oracle Services are restarted.
https://docs.oracle.com/database/121/NTQRF/admin.htm#i1006074
Linux can also be configured to start the Oracle Database automatically using scripts. Oracle Restart was recommend in previous versions to accomplish this but is now deprecated.
https://docs.oracle.com/database/121/UNXAR/strt_stp.htm#UNXAR150
http://docs.oracle.com/database/121/ADMIN/restart.htm#ADMIN12708
This step is no longer necessary, see below: If a pluggable database was configured during installation it should be manually started after restarting the oracle services.
https://web.archive.org/web/20180911225755/http://blog.contractoracle.com:80/2013/06/oracle-12c-need-to-start-container-and.html

Do note: The 12.1.0.2 patchset introduced the ability to preserve the startup state of PDBs through a CDB restart. This is done using the ALTER PLUGGABLE DATABASE command with the OPEN and SAVE STATE options.
http://oracle-base.com/articles/12c/multitenant-startup-and-shutdown-cdb-and-pdb-12cr1.php


MongoDB: queue vs. realtime write/update On a large mongoDB I have 2 different use-cases for reading and 2 different types for write/update.

read 1: find by index -> no question on this. this works like a charm - as long as the index fits in the memory/ram.
read 2: read line by line, like a batch script which is reading a log-file one by one.
write 1: insert - append a new dataset or document.
write 2: update data, directly addresses or found by index.

Now I wonder if mongoDB is able to "priorize" different write-tasks. I have one collection which does not require the data to be written and available instantly in "real-time". It's enough to use a slow queque and process these writing tasks without hurry.
However, there are also write statements that should be processed asap and be available with a minimum delay (if any).

Does mongoDB allow me to differ between high (asap) and low prio (queque) writing/updating?

There is no priority system currently for writes (or reads, though you can send reads to secondaries) - the closest thing you will get is yielding.  For long running operations and for operations that it predicts will page in data from disk, MongoDB will yield the lock and allow other operations through, essentially interleaving operations:

If you wanted to make sure that the less important writes are throttled somewhat you could rate limit them by ensuring they are replicated W=2, REPLICAS_SAFE, or similar writes (depending on your driver).  See here for the command behind such implementations on the MongoDB side - take a look at your driver docs for the relevant equivalent there.

http://www.mongodb.org/display/DOCS/getLastError+Command#getLastErrorCommand-%7B%7Bw%7D%7D

There would then be a slight delay as the write waits for replication out to the secondaries, allowing your other, more important writes their shot at the write lock.

http://www.mongodb.org/display/DOCS/How+does+concurrency+work

In terms of the future, with 2.2 due out shortly, you will get database level locking, so as long as your 2 different profiles/priorities are in different databases you should have no lock contention (IO/RAM contention may still exist, of course).

Finally, in terms of other things to look at, for the line by line type read, I would look at capped collections and tailable cursors - see if they fit your use case:

http://www.mongodb.org/display/DOCS/Tailable+Cursors


Sample query/DDL for producing page locks on a clustered index in SQL Server (without hints) We currently have some indexes where ALLOW_PAGELOCKS is set to off. This presumably was done in order to reduce deadlocks. However I doubt that it would really had an effect back then.

Now I am trying to understand WHEN SQL Server actually chooses to start locking pages rather than keys in a clustered index. I asked Jonathan Keyhaisas recently and he told me that this could happen if I am touching rows on several subsequent pages. However I didn't manage to get any exclusive page locks by updating rows in a clustered index with a sample query.

Could you help me understand page locks better with a sample query and table? I am running SQL Server 2008 SP4.

Thanks in advance

Martin

We can use a table of about the same size, but we need some more interesting data. Specifically, data that SQL doesn't have indexed but that we're using in our modification predicates.

USE tempdb;

CREATE TABLE dbo.Dummy
(
  Id INT IDENTITY(1, 1),
  Crap DATE,
  MoreCrap VARCHAR(1000)
);

INSERT dbo.Dummy ( Crap, MoreCrap )
    SELECT TOP 1000000 DATEADD(DAY, x.Rn % 365 + 1, GETDATE()), CONVERT(VARCHAR(100), NEWID())
        FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2
        ) AS x ( Rn );

CREATE CLUSTERED INDEX cx_RemusForPresident ON dbo.Dummy (Id)

With just the CX on the Id column, we update!

BEGIN TRAN
UPDATE d
SET d.MoreCrap = 'Happy Friday'
FROM dbo.Dummy AS d
WHERE d.Crap >= '20170601'
AND d.Crap < '20170630'
AND d.MoreCrap LIKE '0%'

And just like before, though hopefully without offending Aaron Bertrand, we check out sp_WhoIsActive

EXEC sp_WhoIsActive @get_locks = 1

And blammo! Exclusive locks on Pages, and IX locks on the object, with no key locks.


Executing a SQL Stored procedure from a SSRS text box item - Is it possible I know the we can use custom code in SSRS and I know you have actions on Textbox items in a SSRS report.

Is it possible to "combine" this functionality for the following, which I appreciate is quite out the normal use of SSRS

I wish to produce a list of outstanding requisitions for purchase orders from the SQL tables of our ERP system. In the report against each item would be a Text box with the word "Approve" in
The idea is for the requisitions the user wishes to approve they would click on the "Approve" and then use the custom code would fire a SQL procedure, passing in the id of the requisition to the procedure. This procedure would interact with the ERP system to do the approval.

Is this possible?

Some pseudo code for the SSRS report code

Imports System.data.sqlclient
Dim connectionString AS String = "Server=XXXXXXX;Database=db1234;User Id=user_name;Password=my_password"
Dim Command As New SQLCommand("[CHC_AppBuilder].[Testssrscall]", connectionString)
Command.CommandType = CommandType.StoredProcedure
Command.ExecuteNonQuery()

The SQL procedure [CHC_AppBuilder].[Testssrscall] just currently executes dbmail to myself

Problems so far:

Location of the System.data.sqlclient.dll to add the references as per you would in visual studio, so far not been able to test the code.
Trigger the Stored procedure per item.

I think this used to be easier when SQL Server included SOAP/web services with SQL 2005/2008. With SOAP you could create a wrapper to a SQL web service that would call a stored procedure with a URL. Microsoft gutted that functionality in future releases of SQL Server 2012 and above. I miss the functionality.

I can think of three ways to handle this but there's probably more:

First create an ASP.NET web service

Here's a tutorial to get you to connecting to a database and accessing the web service with HTML. So basically, you could call your stored procedure from anywhere--not just SSRS.

https://docs.microsoft.com/en-us/aspnet/web-api/overview/older-versions/build-restful-apis-with-aspnet-web-api#Exercise3

And, second, embed custom code with SSRS (here's a few examples I thought were useful):

http://www.davidgiard.com/2012/09/07/EmbeddedCodeInAnSSRSReport.aspx

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdf290fb-153d-4bc2-8964-8b550df7a087/calling-stored-procedure-from-custom-code?forum=sqlreportingservices

Last, create an assembly to call a custom function (that calls your procedure):

http://davidgiard.com/2012/09/11/CallingAnExternalAssemblyFromSSRS.aspx


Can I specify the default backup location for non-automatic backups? I noticed that when the BACKUP DATABASE command is issued without the TO clause the backup file is stored to the working directory where the command is issued.

I'm wondering if it's possible to configure a default location for backups such that it's used when the location is not specified in the command.

What if the free space on the storage is not enough? Is there a setting to tell DB2 to automatically remove the oldest backup as for circular logging?

No, it's not possible to specify a default location.  The best way to do this would be to write a shell script (which specifies your location of choice) to handle your backups.

You can have DB2 remove old backups (using the AUTO_DEL_REC_OBJ database configuration parameter with proper values for NUM_DB_BACKUPS and REC_HIS_RETENTN), but that will only occur after a successful backup.

So you'll need to ensure that there is enough space for the backup to complete successfully.


Does SQL Server 2008 R2 10.50.6560.0 Support TLS 1.2? We recently upgraded a non-production SQL Server 2008 R2 system to SP3, and then Windows Updates installed the latest Security Update (10.50.6560.0).  I see that TLS 1.2 support for 2008 R2 is provided in 10.50.6542.0, but that update will not install on top of 6560.  Login attempts are failing and I can see in Event Viewer that the client is requesting TLS 1.2, but the server doesn't support it.

SSMS returns:

  ...(provider: SSL Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
  (Microsoft SQL Server, Error: 10054)

System Event Log on server:

  Event ID 36874
  An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.

I have tried updating the various SQL clients on both client and server, enabling TLS 1.2 in the registry on the server (and rebooting), to no avail.  So now I'm beginning to think that build 6560 doesn't support it, and if that's the case, I have to uninstall it somehow so that I can install build 6542 first.

We have other servers running the same build that work fine, so I'm trying to find what's different about this server--no luck yet.

Build 10.50.6560.0 does support TLS 1.2.  I was able to resolve the problem by installing a new certificate from our CA.  Apparently the older certificates that are on the server are not capable of supporting TLS 1.2.

TLS 1.2 support for Microsoft SQL Server includes information regarding the client and server versions that are required to support TLS 1.2.


Exporting Images from Sql server database into local drive My comand:
EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'

above query run and successfully download the file but images preview not shown
post downloaded that file size is 1 kb only

The bcp native datatype for a SQL image data type column includes a length prefix that won't be recognized by the rendering application. IMHO, bcp is not the right tool for this job and invoking xp_cmdshell in general is a dubious practice from a security perspective.
That being said, one way you could export the binary column contents to a file is with a PowerShell command invoked via xp_cmdshell:
EXEC xp_cmdshell 'PowerShell -Command "$con = New-Object Data.SqlClient.SqlConnection(''Data Source=.;Integrated Security=SSPI'');$con.Open();$cmd = New-Object Data.SqlClient.SqlCommand(''SET TEXTSIZE -1;SELECT column_of_type_image FROM yourdb.dbo.foobar WHERE id = 1;'', $con);[IO.File]::WriteAllBytes(''C:\exportdir\yourfile.pdf'', $cmd.ExecuteScalar());$con.Close()"'


Merge indexes in union I have two tables:

Table A:
a_id, a_href, a_name,......

Table B:
b_id, b_href, b_name......

Both tables have indexes on their href column. I intend to union these tables and join them on another, something like

Select c_id, u.name FROM TableC
Left join (Select a_href AS href, a_name AS name FROM TableA
           UNION
           SELECT b_href AS href, b_name AS name FROM TableB) AS u ON u.href = c_href

I want to preserve the index on href columns in the union, so my join is fast. Do I have to dynamically create an index for that union, or is there a better way to do this?

A UNION will not have an index.  A "derived table", which is what you have inside the parens, may be given an index -- it depends first on which version of MySQL you are running.  Please provide EXPLAIN SELECT ....

Get rid of LEFT so that the Optimizer can start with the UNION.  And add INDEX(href) in C so that the JOIN can work the other direction.

If you really need NULL for u.name, then you need the LEFT, and there is no hope for optimizing.

(The default for UNION is UNION DISTINCT, which is slower than UNION ALL.)


Can't grant role permissions under sysadmin login I'm trying to execute this script from Management Studio:

GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdaptAdmin] AS [AdminSapr]

Execution fails with this message:

  Msg 15151, Level 16, State 1, Line 1 Cannot find the role
  'AdaptTemplate', because it does not exist or you do not have
  permission.

There are existing roles AdaptTemplate, AdaptAdmin and user AdminSapr in database.
User AdminSapr is a member of db_owner. My current login (which I'm trying to execute script from) is a member of sysadmin server role.

What am I doing wrong?

This is happening because the principal in the AS clause needs to have the permission with the "GRANT OPTION".  Running this will allow you to run your original code:

GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdminSapr] WITH GRANT OPTION

My reading of Books Online made me think that what you're doing is right since AdminSapr is in the db_owner role but in testing I had to do something like the above to get this to work.


Linked Server Setup Error - SSL Provider: The client and server cannot communicate, because I am trying to setup a linked server and ending up with the following error.
Error:
SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm.

Environment:
Linked server is being created on
Microsoft SQL Server 2012 (SP3-CU8) (KB4013104) - 11.0.6594.0 (X64)  Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Windows Server 2012 Standard

IISCrypto shows these values

Source Server:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Windows Server 2008 R2 Enterprise

IISCrypto shows these values

I gave up resolving this issue for a while now.
Could someone please assist me on this?
I need to create this Linked server!! What am I doing wrong all these days?

Ensure both servers have support for TLS 1.2.

The following table, taken from the Microsoft Support site, shows when TLS 1.2 support was added to each particular SQL Server version:

╔═════════════════════════════════════════╦══════════════════════════════════════════╗
║           SQL Server release            ║    First build that supports TLS 1.2     ║
╠═════════════════════════════════════════╬══════════════════════════════════════════╣
║ SQL Server 2014 SP1                     ║ 12.0.4439.1                              ║
║ SQL Server 2014 SP1 GDR                 ║ 12.0.4219.0                              ║
║ SQL Server 2014 RTM                     ║ 12.0.2564.0                              ║
║ SQL Server 2014 RTM GDR                 ║ 12.0.2271.0                              ║
║ SQL Server 2012 SP3 GDR                 ║ 11.0.6216.27                             ║
║ SQL Server 2012 SP3                     ║ 11.0.6518.0                              ║
║ SQL Server 2012 SP2 GDR                 ║ 11.0.5352.0                              ║
║ SQL Server 2012 SP2                     ║ 11.0.5644.2                              ║
║ SQL Server 2008 R2 SP3                  ║ 10.50.6542.0                             ║
║ SQL Server 2008 R2 SP2 GDR (IA-64 only) ║ 10.50.4047.0                             ║
║ SQL Server 2008 R2 SP2 (IA-64 only)     ║ 10.50.4344.0                             ║
║ SQL Server 2008 SP4                     ║ 10.0.6547.0                              ║
║ SQL Server 2008 SP3 GDR (IA-64 only)    ║ 10.0.5545.0                              ║
║ SQL Server 2008 SP3 (IA-64 only)        ║ 10.0.5896.0                              ║
╚═════════════════════════════════════════╩══════════════════════════════════════════╝

If you have a version of SQL Server prior to those listed in the above, you should apply the latest service pack and cumulative update to enable TLS 1.2 support.

https://sqlserverbuilds.blogspot.com and https://sqlserverupdates.com/ contain excellent resources detailing where to get the latest updates for SQL Server.


mysqldump reimport isn't working correctly I am trying to restore this database from a my sql file, but I'm only getting the following output, and it's not importing anything into my db. Any thoughts?

[root@domU-12-31-39-0B-06-AA data]# mysqldump --host=localhost --password=abc1234 --    user=root --single-transaction blink10 < /data/tt.sql
-- MySQL dump 10.13  Distrib 5.1.52, for redhat-linux-gnu (x86_64)
--
-- Host: localhost    Database: blink10
-- ------------------------------------------------------
-- Server version       5.1.52

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2012-02-13  9:30:47

I might be missing something, but instead of using mysqldump, use mysql which is the command line client program:

mysql -uuser -ppassword -hhost blink10 < /data/tt.sql

This assumes there are actual SQL staetments in your tt.sql file, such as CREATE TABLE and INSERT, etc


Relationship between two multi-valued child tables Scenario

In my system, there are a number of different TradeBaskets. Each TradeBasket contains a number of different Securities. The Securities have a Type, Value, Sector etc... and new Securities are populated into the basket each day (and others can be removed).

Each TradeBasket has a unqiue list of EligibleEquity that can be accepted from the Securities within the TradeBasket. EligibleEquity can take the form of CommonStock, PfdStock, Reit, MutualFunds etc. My initial approach was to model the EligibleEquities as a multivalued attribute (instead of an entity). But I believe the best approach is to model EligibleEquity as an entity.

The TradeBasket also has a list of EligibleIndices that are accepted for the EligibleEquities in the TradeBasket. Thus, for each type of EligibleEquity in the TradeBasket, there is a list of associated EligibleIndices. It is true that each type of EligibleEquity(such as CommonStock, PfdStock etc...) can exist in a number of different EligibleIndices (such as S&P500, AS25X, CAC40 etc...) and that each EligibleIndices can be associated with a number of different EligibleEquities. Thus, I have assigned a Many:Many relationship between EligibleEntities and EligibleIndices.

ERD Design

In the ERD provided, I have colour coded the relationship between the TradeBasket and EligibleEquities and EligibleIndices.

In one case (red), I have mapped the EligibleEquity and EligibleIndices as a 1:Many relationship with the TradingBasket. In this case, I consider EligibleEquity and EligibleIndices as multi-valued attributes of the TradeBasket. Thus, in order to normalize the TradeBasket, the EligibleEquity and EligibleIndices are placed in  separate tables (using the primary key of TradeBasket as the foreign key in each EligibleEquity and EligibleIndices child table). However, is it possible to create a Many:Many relationship between ElibibleEquity and EligibleIndices if they are children to the TradeBasket?

In the other case (purple), I have mapped the EligibleEquity and EligibleIndices as a Many:Many relationship with the TradingBasket. In this case, I consider EligibleEquity and EligibleIndices as entities in their own rite. and the Many:Many relationship between EligibleEquities and EligibleIndices is straightforward.

Questions:

To facilitate a relationship between EligibleEquity and EligibleIndices, what the cardinalities should I adopt (red or purple - with respect to the ERD)?
If the EligibleEquity and EligibleIndices should be mapped as multivalued entities of the TradingBasket, what would the primary key of the "junction" table between EligibleEquity and EligibleIndices be?

This question has a similar discussion, but doesn't fully answer my question.

Thanks for any help!

Before I attempt to answer your questions, just a couple of hints that may help
you to create the perfect model :-) Bear with me.

When you construct an ERD, try and avoid "database" terminology such as:
primary key, foreign key, table, row.  Instead, use the terms "entity", "relationship", "unique identifier", "instance".
Use singular nouns as entity names.  When creating an entity, think about a SINGLE instance (of an entity).  This may make it easier to find the correct cardinalities.
You can draw as many relationships as you like.  However, only keep the relationships that make sense in your system (eg if there are 5 entities, and the relationships in your diagram make it look a bit like a pentagram inscribed into a 5-sided polygon, then there are - in all probability - too many relationships).  The MANY side of a relationship will eventually be transformed into a foreign key (which we should not worry about when working at the ERD level).

Now to your questions:

Q0) However, is it possible to create a Many:Many relationship between ElibibleEquity and EligibleIndices if they are children to the TradeBasket?

Answer 0)  Yes.  Just make sure that this really makes sense (see answer 1).

Q1) To facilitate a relationship between EligibleEquity and EligibleIndices, what should the cardinalities should i adopt (red or purple - in relation to the ERD)?

Answer 1)  Find out, and use, whatever is correct!
   Does EACH EligibleEquity (potentially) use/contain ONE OR MORE EquitableIndices?
   AND: Is EACH EquitableIndex used/contained within ONE OR MORE EquitableEquities?
   -> If the answer to both of these questions is: YES, then model a many-to-many relationship. (This will require an "intersection" or "junction" later on -> see the HYPOTHETICAL... entity in the attached ERD.)

Q2) If the EligibleEquity and EligibleIndices should be mapped as multivalued entities of the TradingBasket, what would the primary key of the junction table between EligibleEquity and EligibleIndices be?

Answer 2) -> The key for this could be: the combination of the UNIQUE IDENTIFIERs (UIDs) of TradingBasked and EligibleEquity (or EligibleIndex, respectively).

Eg - situation as discussed in "Answer 1": if the UIDs are the attributes(EligibleEquity)TYPE, and (EligibleIndex)INDEX_NAME, respectively, then a combination of these (unique) values can serve as a UID of the intersection/junction. (-> See the keys of the HYPOTHETICAL... table in the attached Relational model.)

There is more to be considered, and I'm sure that there will be more questions.  Let us know how you get on!


Does RSS Profile have an effect on SQL Server with NUMA setup? I'm curious whether the RSS Profile which is default NUMAStatic on Windows 2012 R2 could/should be changed for SQL Server with a NUMA setup.
Has anyone done performance benchmarks on high load SQL Servers with NUMA or Conservative settings?

Receive side scaling (RSS) improves the system performance when handling network data on multiprocessor systems.  This should result in better scaling for receiving data from the network.

One place to find details is at: https://technet.microsoft.com/en-us/library/hh997036.aspx

From this I would infer that the Receive side scaling primarily benefits the receipt of data from the network.

There is a link that you might find interesting for an SAP installation and, although posted in 2012, seems to be using pre-Windows 2012 Operating Systems.

Nonetheless, you may benefit from some of the details. One of the charts shows that only 1 processor is consuming the reads, while other processors are idling along doing not so much.  See the charts at:

http://blogs.msdn.com/b/saponsqlserver/archive/2012/01/12/network-settings-network-teaming-receive-side-scaling-rss-amp-unbalanced-cpu-load.aspx

Therefore, it seems that RSS is primarily affecting how quickly Windows can receive the data and make it available to other processes, such as the SQL Server.  This depends on the RSS Profile you choose.

For example:

The Default profile which disables the new RSS features, leaving behavior very similar to the Windows 2008 R2 behavior.
The NUMA scalability profile enables RSS CPUs to be assigned per NUMA node on a round robin basis. This results in "RSS assigns at least one RSS CPU per NUMA node before assigning the second RSS CPU for each NUMA node." This round robin setup spreads the task across more CPUs, thus likely improving the data throughput.

This suggests that if the server is a heavy consumer of the data network, the NUMA scalability profile could significantly increase the consumption of this data.  But once that is accomplished and the data is in cache, I cannot see  that this would carry over into other SQL Server operations.

Is your environment consuming very heavy levels of data, such that this would benefit your work? Then you might consider the various profiles available to you.

Other Low latency support includes such options as Parallel Receive Processing, Preserving in-order packet delivery, and several other options that may be a benefit to you.
Also the latest RSS "enables Windows Server 2012 to scale optimally on large servers that have more than 64 processors."

So, RSS enables Windows with better options for receiving data, but it does not seem to indicate that the SQL Server will behave in some better way.  But, I cannot find any clear direction on this and most of the papers are about a decade old.