How to cache query for re-use in following UNION of queries? I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB).

This is the query I want to save:

SELECT `event`.id, event.name
FROM attendant
INNER JOIN event ON attendant.fk_event = event.id
WHERE attendant.fk_user = 1

UNION

SELECT `event`.id, event.name
FROM organizer
INNER JOIN event ON organizer.fk_event = event.id
WHERE organizer.fk_user = 1

UNION

SELECT `event`.id, event.name
FROM invitation
INNER JOIN event ON invitation.fk_event = event.id
WHERE invitation.fk_user_to = 1

And then perform multiple queries like

SELECT id FROM savedquery WHERE .....

UNION

SELECT id FROM savedquery WHERE .....;

I've found TEMPORARY TABLEs but I don't think that I could use it since I need to perform this query for multiple different users at the time they run the request for events.

VIEW is also named, so I suppose there is the same problem.

What's the best solution to do this please?

As you say, temporary tables are only visible within the session, and materialized views are not available in MySQL. There are several options (including the ones you have mentioned). I will name your original query as SELECT ...:

The WITH Oracle keyword is equivalent to CREATE TEMPORARY TABLE:

CREATE TEMPORARY TABLE savedquery AS SELECT ...; -- you execute this for every connection
SELECT id FROM savedquery WHERE .....
UNION
SELECT id FROM savedquery WHERE .....;

You will have to create it for every connection.
A VIEW is shared among connections, but it is not an actual table- it can be executed in two ways, by creating a temporary table at the beginning of the execution or by executing the corresponding subquery:

CREATE VIEW savedquery
ALGORITHM = TEMPTABLE  -- you probably want to use temptable algorithm
AS SELECT ...;         -- you execute this only once

SELECT id FROM savedquery WHERE .....
UNION
SELECT id FROM savedquery WHERE .....;

If you were using 5.6, the subquery algorithm may be better, but this is without knowing the actual query.
As materialized views are not an option, you can emulate them by creating a real table, that will be shared among connections:

CREATE TABLE savedquery AS SELECT ...;
SELECT id FROM savedquery WHERE .....
UNION
SELECT id FROM savedquery WHERE .....;

The problem with this is that it is not automatically updated (you have to do that with triggers or any other way); the positive thing is that you can add indexes (unlike views).

The problem is that your original query and your derived query are probably not going to be very efficient, and you probably could modify it to perform better, but that is another question...


How to index for a query with independent range conditions? What is the best way to index a table that I query using independent range conditions?
E.g. WHERE X < ? and Y > ?

MySQL can do Index Intersection (more commonly know as Index Merging). However, in your case, I have something more reasonable to suggest.
You mentioned in a comment

X is a VARCHAR and Y is a DATE

For the sake of example, we'll use this query
SELECT A,B FROM mydb.mytable
WHERE X < ? and Y > ? ;

You need to create an index, but you must examine which order of columns forms the smaller range for a first column. Is it (X,Y) or (Y,X) ?
CHOOSING X (VARCHAR) FIRST : This means looking over at least 1296 (36 * 36) possible alphanumeric ranges. Then, within each character range, compare a range of dates up to today's date. 36 is 26 Alphabetic and 10 Numeric.
CHOOSING Y (DATE) FIRST : This means looking a range of dates up to today's date. Then, within each date, compare a range at least 1296 (36 * 36) possible alphanumeric ranges.
You can decide which by running these two queries:
SELECT COUNT(DISTINCT Y) DateCount FROM mydb.mytable WHERE Y > ?
SELECT COUNT(DISTINCT LEFT(X,2)) CharCount FROM mydb.mytable WHERE X < ?

Which combination forms the smaller set? This would be hard to judge if you have something lopsided like X < 'Umbrella' or Y > '1929-10-29'. These are ranges too unreasonable for a query optimizer to want to use an index.
If the date range is short, create an index with (Y,X)
If the string value range is short, create an index with (X,Y)
I would create both indexes and let the Query Optimizer make the decision. Yet, there is something else you can do. Look again at the sample query.
SELECT A,B FROM mydb.mytable
WHERE X < ? and Y > ? ;

You can boost performance by creating a covering index. That's an index that has all the needed data in the index which bypasses the need to read data from the table. In the case of the above query, you could create the two indexes like this:
ALTER TABLE mydb.mytable ADD INDEX idx1 (Y,X,A,B);
ALTER TABLE mydb.mytable ADD INDEX idx2 (X,Y,A,B);

With these two indexes, the Query Optimizer will do an index scan in the worst case scenario since all the needed data is already in the index and there is no need to get anything else from the table. This is very beneficial for both MyISAM and InnoDB.

MyISAM : only caches index pages into the key buffer
InnoDB : only index page are loaded into the InnoDB Buffer Pool
See my post What are the main differences between InnoDB and MyISAM?

As for Covering Indexes, I mentioned using them in past posts

Jan 01, 2013 : Performance impact of including result column in indexed columns
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?
Oct 17, 2012 : Combining columns in index
Mar 12, 2012 : How to index this table (a_level, b_level, item_id)

SUMMARY
Dealing with independent ranges may make accurately forecasting which index combination is more relevant can be challenging. Furthermore, imagine the anxiety you will have as to how the  query optimizer will evaluate thing. In light of these, you will need to go with covering indexes as much as possible. You will have to also schedule a periodic run of ANALYZE TABLE mydb.mytable; if the table experiences heavy INSERTs, UPDATEs, and DELETEs.


How to combine two select statements result column wise? I have two select queries and i want to combine result of both column wise.I have use union between two select queries but it give me just one query result

I want expected result like this

My SQL QUERY

    SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT
   FROM tblCategory TC
    LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO
                        FROM tblAsset
                        WHERE EQUIPMENT_ID=1
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION

  UNION
  SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
  FROM tblCategory TC
  LEFT OUTER JOIN tblEvent TE
    on TE.CategoryID=TC.NO
    AND TE.AssetID IN (
                        SELECT ASSET_NO
                        FROM tblAsset
                        WHERE EQUIPMENT_ID=2
                        )
  GROUP BY  TE.CategoryID,TC.DESCRIPTION

Selecting UNION will append the results below the existing result set.
If you want the second query's results as an additional column you will need to write it as a single query and JOIN the additional column.

You can do this by including the 'second' query as a subquery and joining the results together.
Without changing your existing query structure:

SELECT      count1.DESCRIPTION,
            count1.[COUNT],
            count2.COUNT2
FROM
(
    SELECT TC.DESCRIPTION,
                count(TE.CategoryID) AS COUNT
    FROM tblCategory TC
     LEFT OUTER JOIN tblEvent TE
            on TE.CategoryID=TC.NO
            AND TE.AssetID IN (
                            SELECT ASSET_NO
                            FROM tblAsset
                            WHERE EQUIPMENT_ID=1
                                )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION
) count1
LEFT OUTER JOIN
(
    SELECT TC.DESCRIPTION as desc2, count(TE.CategoryID) AS COUNT2
    FROM tblCategory TC
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
        AND TE.AssetID IN (
                        SELECT ASSET_NO
                        FROM tblAsset
                        WHERE EQUIPMENT_ID=2
                        )
    GROUP BY  TE.CategoryID,TC.DESCRIPTION
) count2 ON count2.desc2 = count1.DESCRIPTION

Of course, this assumes your value for TC.DESCRIPTION is unique, otherwise you will potentially generate duplicate rows. If you have a primary key to join in instead this is preferable, however the underlying table structure was not given in the question.


SQL Query: Join Tables using two partial string matches Using MS Access, I have to combine two tables where two criteria must be met: Container = Container AND Booking = Booking.

I have two tables where fields are missing a value:

Table1 and Table2 have in common two colunmns:

Container
Booking

Table 2 has additional column "Date". I need to add "Date" to Table1.

Problem is

Container is often missing a "check digit" at the end, so it may be 10 or 11 characters, so i need to match first 10 of container

Booking is often missing a leading zero, so it may be 9 or 10 numbers, so i need to match last 9 of booking.

The following does not work when one or the other Table are missing a leading zero and/or container check digit. How do i adjust SQL?

RIGHT JOIN Table1 ON Table2.[Booking] = Table1.[Booking] AND Table2.[Container] = Table1.[Container];

Containers can be reused on new bookings so booking number must also be match.

Not ultra familiar with Access, but I'm pretty sure you have LEFT and RIGHT functions available to you.  Something like this seems likely to work:

    ON  RIGHT( Table2.[Booking], 9 ) = RIGHT( Table1.[Booking], 9 )
    AND LEFT( Table2.[Container], 10 ) = LEFT( Table1.[Container], 10 );


Enable TLS 1.2 and disable SSL 3 on SQL Server 2008 I know similar questions have been asked. What I'm asking for are concrete instructions on what to do, or not do, for my particular installation. I have some questions that I can't find the answer to, and I need some answers before I can install Microsoft's hotfix.

The goal is to disable SSL 3 on the machine by adding the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\Schannel\Protocols\SSL 3.0\Server\ - add value Enabled = 0. However this causes SqlServerAgent to not start.

Running SQL Server 2008 R2 (SP3) build 10.50.6220.0 (X64).

Here's Microsoft's official download to enable TLS1.2 support: link to microsoft

The page reads First build that supports TLS 1.2 - 10.50.6542.0. This is higher than my build number. Does it mean the hotfix only is compatible with builds from 10.50.6542 and up? And so my build is not supported?

If my build is supported, then I supposed I should install it. Then what happens next?

In order to be compatible with TLS 1.2, you must be running SQL Server 2008 R2 version 10.50.6542.0 or later.

To get to that version level, you need to install Service Pack 3 for SQL Server 2008 R2, then install the hotfix from KB3144114 from https://support.microsoft.com/en-us/Hotfix/KbHotfix?kbnum=3144114&kbln=en-us which will update your SQL Server to v 10.50.6542.

Be aware, the above hotfix should never be installed on a production system unless you've extensively tested it in your test-environment first.


How to format a date field into diferente languages in the same command? (multiple locales) The following will return the day in Italian:

SET lc_time_names = 'it_IT';
select date_format('2018/01/01','%W') as day_italian;

However I need to convert or format the date into multiple languages, so it would return me another column in English, Japanese, so on...

My problem is that I have to set the locale BEFORE running the select command.

Sorry, I've looked into the source code around lc_time_names and there is no solution around getting multiple lc_time_name translations with a single query.

MariaDB has an information_schema table called locales however it doesn't got as far as exporting the lc_time_names of days/months etc. All the names are stored in the server (code, not a database), and it would be moderately easy to export these to an information_schema table, however noone has written the code to do this.


calculate days spent of all visitors from encounters I have an encounters table

CREATE TABLE visitors(
 id serial PRIMARY KEY,
 start_date TIMESTAMP NOT NULL,
 --  visitor_id bigint NOT NULL -- skip for now,
 end_date TIMESTAMP
);

(end_date can be null when visitor is still inside)

and two dates(start_dt and end_dt) are provided on which I need to calculate how many total days all visitors have spent in a certain given date range. eg

if from jan 1 2019 till jan 7, 100 visitors were present, these are 7 * 100 = 700 visitor days
if on jan 1, there were 10 visitors and on jan 2, there were 20 visitors then 1 * 10 + 1 * 20 = 30 visitor days for these given dates

Note: dates will be inclusive

Any help in building this query is very much appreciated(I am not able to achieve this query. I am also newbie in this area). thanks

Edit: So far I have tried/reached this

(WITH dates AS ( SELECT start_date, COALESCE(end_date::date, '2019-01-01') AS end_date FROM "visitors"),
    calendar AS (
    SELECT
    i :: DATE AS DATE,
    date_trunc( 'month', i ) :: DATE AS MONTH
    FROM
    generate_series ( '2018-01-01' , '2019-01-01', '1 day' :: INTERVAL ) i
    )
    SELECT COUNT ( * ) days FROM dates d
    INNER JOIN calendar C ON C.DATE BETWEEN d.start_date AND d.end_date);

Edit: I see some answers e.g age difference but these calculate age based on difference and not both dates being inclusive

I have achieve required results with this query

(WITH params (start_dt, end_dt) as (values ('2019-02-14'::timestamp, '2019-02-18'::timestamp)),
  dates AS ( SELECT (CASE
                      WHEN (visitors.start_date < params.start_dt) THEN params.start_dt
                      ELSE visitors.start_date END)                        AS start_date,
                    (CASE
                      WHEN (COALESCE(visitors.end_date, params.end_dt) > params.end_dt) THEN params.end_dt
                      ELSE COALESCE(visitors.end_date, params.end_dt) END) AS end_date,
                    visitors.id                                             as visitor_id
            FROM "visitors",
                  params)
  SELECT date_part('day', age(dates.end_date, dates.start_date)) days,
        dates.end_date,
        dates.start_date,
        dates.visitor_id
  FROM dates,
      params
  where dates.start_date >= start_dt
    AND dates.end_date <= end_dt);

Note: I would love to edit this answer(if wrong or needs update) or will appreciate if someone has better answer


Oracle - Apply SQL to all schema in database I'm trying to run the same SELECT against all the schema in my Oracle database.
I found an example of how to do this, but when I try to run it against my database I get the below error:

PLS-00306: wrong number or types of arguments in call to

This is my PLSQL

DECLARE
v_sql varchar2(128);
 cursor c1 is
    select username from dba_users WHERE account_status = 'OPEN' and DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
BEGIN
    for REC in c1 loop
        v_sql := 'select * from '||REC||'.accountdetails';
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/

So this should in effect be doing this...

SELECT * FROM USER1.ACCOUNTDETAILS;
SELECT * FROM USER2.ACCOUNTDETAILS;
SELECT * FROM USER3.ACCOUNTDETAILS;
etc.

Any advice greatly appreciated

C

From Akina comments, I now have

DECLARE
cursor c1 is select username from dba_users WHERE account_status = 'OPEN' and DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
BEGIN
    for REC in c1 loop
        select * from REC.accountdetails;
    end loop;
end;
/

but i get

PL/SQL: ORA-00942: table or view does not exist
PL/SQL: SQL Statement ignored

DECLARE
v_sql varchar2(4000);
 cursor c1 is
    select o.owner,o.object_name from dba_users u,dba_objects o  WHERE u.account_status = 'OPEN' and u.DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM')
    and u.username=o.owner and o.object_name='ACCOUNTDETAILS' and o.object_type='TABLE';
BEGIN
    for REC in c1 loop
        v_sql := 'select * from '||REC.owner||'.'||REC.object_name;
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/


Array stringifies to using `{}` in Postgres output I have an array column but when I output it using SQL SELECT, it looks like

{{"a":"b","c":"d"},{"e":"f","g":"h"}}

But why does it start and end with {{ and }}? Shouldn't it be [{ and }] for arrays? Inside output of JSONB columns, arrays look as expected (square brackets, not curly braces, surrounding the array values).

Have I done something wrong, or is the output above truly an array of objects?

From the docs in Array Input and Output Syntax

  The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure. The decoration consists of curly braces ({ and }) around the array value plus delimiter characters between adjacent items. The delimiter character is usually a comma (,) but can be something else: it is determined by the typdelim setting for the array's element type. Among the standard data types provided in the PostgreSQL distribution, all use a comma, except for type box, which uses a semicolon (;). In a multidimensional array, each dimension (row, plane, cube, etc.) gets its own level of curly braces, and delimiters must be written between adjacent curly-braced entities of the same level.

Not sure if stingification of ARRAY is in the spec or not, but for PostgreSQL it's perfectly normal to stingify it with {}. Note, this is not symmetrical with the literal syntax to create an array.

SELECT ARRAY[1,2,3];
  array
---------
 {1,2,3}

But you can see that it's valid and to be expected because you can cast to an array explicitly.

SELECT '{1,2,3}'::int[];
  int4
---------
 {1,2,3}

If in your case you have two jsonb in the ARRAY, it'll get stringified as such

SELECT ARRAY[
  $${"a":"b","c":"d"}$$::jsonb,
  $${"e":"f","g":"h"}$$::jsonb
];
                              array
-----------------------------------------------------------------
 {"{\"a\": \"b\", \"c\": \"d\"}","{\"e\": \"f\", \"g\": \"h\"}"}

Note that a two JSON-objects in a PostgreSQL array, is different from two objects in a JSON array.

SELECT $$[{"a":"b","c":"d"},{"e":"f","g":"h"}]$$::jsonb;
                    jsonb
----------------------------------------------
 [{"a": "b", "c": "d"}, {"e": "f", "g": "h"}]


Index Maintenance for varbinary We have a SQL 2016 database that has a 1.9bn row table which has a varbinary(255) column which we use to store the HashBytes of an nvarchar(2000) field in the same table.

We have a Non-Clustered index on the varbinary field and our index maintenance scripts perform a REORGANIZE on this every 2-3 days. But this takes 10+hours to complete.

Any way to improve the speed of index maintenance for varbinary fields?

If you have a table with 1.9 billion rows, I'm guessing it has a fairly low change rate as a percentage. Check how many rows you're actually inserting/updating on a daily basis - it's likely less than 1%.

In that case, it doesn't make sense to reorganize the whole table every 2-3 days (especially given that it takes 10+ hours.) I'd start by only doing index maintenance weekly, if not MONTHLY on a table of that size.

Start by taking a step back and asking, "What's the problem I'm trying to solve by doing index reorganizations?" If the answer is slow select statements, then index maintenance on a 1.9bn row table isn't going to be the answer.


Is searching an alternate index on a partitioned myISAM table concurrent or serial? I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since the alternate key does not include the value used in the partition selection function, selecting on the alternate index can't use "partition pruning". Therefore, a select for an alternate key must involve searches on all 30 partitions. This much makes sense.

I was recently told that such a search is carried out serially -- on one partition after another. I find this hard to believe, since it sounds like something that could easily be done concurrently. Can anyone confirm this behavior?

mySQL 5.6.14

I agree that MySQL has to do search all 30 partitions. Since you are searching by an auto_increment column, it must be declared as a PRIMARY KEY.
Since indexes are BTREEs, I would expect O(log n) search time per partition.
MySQL (really Oracle) says in the Documentation on Partitioning Overview:

Other benefits usually associated with partitioning include those in the following list. These features are not currently implemented in MySQL Partitioning, but are high on our list of priorities.
Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By “parallelized,” we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions.
Achieving greater query throughput in virtue of spreading data seeks over multiple disks.

Given these comments about partitions being processed in parallel on their wishlist, it would have to be true at this present time to say that all partitions have to be searched sequentially.
UPDATE 2014-02-21 10:51 EST
With regard to your original question, you stated

Partitioning on SHA1 PRIMARY KEY
Partitioned MyISAM table with 30 partitions

OBSERVATION #1
Given the fact that SHA1 output is always 40 characters, my guess is that you are not using RANGE partitioning.
Here is what MySQL Documentation says on HASH partitioning

Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value or set of column values is to be stored; with hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.

Since you are not using RANGE partitioning

MySQL will place new data into a partition after figuring out which partition to choose based on how even data are distributed.
There is no mechanism to reasonably determine any ordering of the PRIMARY KEY.
Using PARTITION BY HASH creates buckets for data with no rhyme or reason for ordering.
Using PARTITION BY LINEAR HASH could create ordered buckets.

The overall problem is that the PRIMARY KEY is not an INT. You cannot set up any real RANGE partitioning for a non-scalar type (such as CHAR string). Given this scenario, MySQL cannot avoid doing a SELECT lookup without opening every partition.
OBSERVATION #2
A partitioned MyISAM table with 30 partitions is made up of

30 .MYD files
30 .MYI files

Each MYD requires a file handle. Each MYI requires a file handle. Thus, you can have 60 file handles open. Here is what you can do to see how many file handle open up after a mysql restart
FLUSH TABLES my_partitioned;
SELECT variable_value INTO @open1
FROM information_schema.global_status
WHERE variable_name='open_files';
SELECT bar FROM my_partitioned WHERE foo_ak = 42;
SELECT variable_value INTO @open2
FROM information_schema.global_status
WHERE variable_name='open_files';
SET @opendiff = @open2 - @open1;
SELECT @opendiff;

What you can conclude:

If @opendiff is close to 60, MySQL tried to open all the partitions.
If @opendiff is close to 0, MySQL had pruned the partitions

The second bulletpoint is what you want because it will show how heavy handed MySQL is at opening partitions,... or not.


How to track down app name for query in query plan cache? I found a query in the plan cache that needs parameterizing.  Not sure which app created this query.  Is there a good way in SQL to track down the app that put a given query in cache?
sql2014

No, information about what sent the query to the server (user, application, IP, etc.) isn't stored in the plan cache or Query Store, or associated views.
You can catch that information while a query is running using sp_WhoIsActive, or logging it to a table.
If you have a good monitoring tool, it may have gathered that information. If you have a bad one, it probably didn't.


Database design what are your thoughts For years I have dinked with and now considering making it into a real project.

I am thinking about developing a 'driver' of sorts that sits on top of RDMS( oracle, MSSQL, MySQL - others to follow). That given a table description the driver creates stored procedures and client code example(s) (front-end, back-end, etc).

The end result would be that the geek would create a table in any of the above mentioned RDMS's and my code would produce selected client interfacing.

For example Enter table deffinition. Check some buttons and click some buttons and out comes a zip file that has a web form .NET project to add/update/delete/select records from the given database(or specific tables). The developer can then take that code and with minor changes, (cosmetic security) and use it as there own.

The beauty would be that if the developer wants to define table for mysql they can change to to MSSQL with click button, and whatever code produced before will remain unbroken.

I realize this is 'code review' and I have no code. But any good durable code goes through this design phase.

Any thoughts on the concept?

I appreciate the thoughts thus far.  I agree with much of what is said.  The deliverable after selecting option boxes and clicking buttons would not be 'finished product'.  Largerly the developer will still be required to make the screen pretty.  If a there is two tables tblOrders and tblOrderItems - the end  result would be two 'forms' for each.  This is not ideal for the 'user' but helpful for the developer.  Each part of the deliverable will require some developer/DBA 'tweaking'.

Code generators are great! Code generators are evil!

10-15 years ago, I would have said that having a code generator for quickly creating the boiler plate code for database driven applications would have been a great gift to mankind.

5-10 years ago, I would have said code generator sucks, they generate too much duplicate code and that having a data-driven user interface (where the fields on the screen, validation, etc is driven by meta-data in a database instead of coding the screens one by one) would have been a great gift to mankind that supplanted the code generators.

Today I would say - write each screen individually. Use existing framework that wire fields and model objects and possibly ORM when doing simple CRUD. But do design each screen to the exact purpose of the screen. Application screens that mirror a RDMS table too much is only good for managing lookup tables. Don't annoy the user with geeky interface that are designed against a computer storage model (RDMS)... make a screen that has only what they need. That may mean that it will save to multiple tables, etc. Who cares. The user isn't a database.

So my thought? Don't waste your time making a code generator.


Is it correct to dynamically add rows to a table in sqlite, or should I use csv? (I'm working with objective c.) Just like when adding a new contact in the ios contact app, you can add multiple phone numbers, and it doesn't know how many there will be, so therefore they don't know how many rows they should initially add in the table.
So how does that work, do they dynamically add rows to the table, or do they use csv?

They just properly normalize their database so that it is possible to store an arbitrary number of numbers; something like this:

CREATE TABLE Contacts (
    ContactID INTEGER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE PhoneNumbers (
    ContactID INTEGER REFERENCES Contacts(ContactID),
    Number TEXT
);


Vendors with Most Invoices in State I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work:

SELECT Max(NumInvoicesState)
FROM
 (
   SELECT  VendorState,VendorName,
      Count(InvoiceID) AS NumInvoicesState
   FROM VENDORS V
   JOIN Invoices I
     on V.VendorID=I.VendorID
   GROUP by  VendorState, VendorName
 ) AS Alias

My idea is to first do a query to get a table with a list of vendors by state and the number of invoices for each vendor.

I thought of joining the inner-table to Vendors and then group by VendorState, but that does not seem to work.

I guess I could SELECT VendorName, VendorState then join it with Vendors on, say VendorState and then Group by Vendors. But then I get all the Vendors, not just the ones with the max.

I think the inner query is right.

The MAX without a matching GROUP BY in the outer query will give a single value from the whole dataset. In your example the dataset would be the inner query. This is not what you want, so you have to add group by state.

Now you have the problem of vendor name. It can't be in the select list unless it is also in the GROUP BY, but that will give the wrong result. It is necessary to do this in two steps. In the outer query just deal with states:

Select state, max (..) as MaxByState
From (inner query)
Group by state

Then join this back to the inner query on the MAX() value:

Select
  ...
from (
    Select state, max (..) as MaxByState
    From (inner query)
    Group by state
) as mbs
inner join (
  Inner query
) as iq
on mbs.state = iq.state
and mbs.MaxByState = iq.NumInvoicesState;

In other words, here are the most numerous invoices in each state. Which vendors have these many invoices?

If two vendors have the same number of invoices in a state there will be two rows output.

Since the inner query is repeated verbatim it would be better presented as a non-recursive CTE.

SQL Server 2012 has a number of windowing functions built in. The most useful one here is RANK (). It does just what you think it should do.

Select
  RANK ( ) OVER (PARTITION BY state order by NumInvoicesState )
from (inner query) as a

This eliminates all of the second-order dataset alignment on derived aggregate values required above. It is much easier to read and understand, and hence to maintain.


Is it Possible to perform Restore on Secondary Replica using Copy_Only Backup that was taken on the same node? I'm looking to use copy_only backups taken on a secondary replica, remove that DB from the Availability Group, then restore the DB using the copy_only backup and add it back into the Availability Group. That all works fine but when I try to join the DB back to the Availability Group I get an error.
'Database "AGTest" is not in a recovering state which is required for a mirror database or secondary database. The remote database must be restored using WITH NORECOVERY.'
This seems like a straightforward error so I do what it says, Perform the same restore but with NORECOVERY and try to add to AG again. Another Error.
'The remote copy of database "AGTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.'
This is the part that confuses me, I've tried to restore it with the latest log backups from the primary. No Dice.
Any help or suggestions is greatly appreciated.

When trying to add a secondary replica to an AG, it needs to be sufficiently close to the primary that whatever transactions are in the active portion of the primary's transaction log are enough to bring the secondary up to current.
Let's say for the sake of argument that your copy only backup is taken once a day at midnight (00:00) and you're starting the process of adding the replica the next day at 09:00 (i.e. nine hours have elapsed since the backup was taken). Let's say for the sake of argument that the restore process was instantaneous (it won't be, but we'll get to that in a moment). Once that restore is complete, you have a database that is consistent with the primary as it was at 00:00. You now need to apply transaction log backups in order to cover the intervening nine hours. Let's say for the sake of argument that those also take no time to restore. Now the primary and secondary are consistent with each other and you should be able to join the secondary replica to the AG.
In the above scenario, all of the restores took zero time. In reality, that's not the case. As you're doing restores on the secondary, your primary database is also taking traffic. It's not a big deal, you just need to apply more transaction log to cover the intervening time. Making the above scenario slightly more realistic, let's say that the restore of the full backup takes 2h. So now it's 11:00 and you start restoring all the transaction logs you have from midnight onward. Let's say that that process takes an hour. Now it's 12:00 and your secondary is restored up to 11:00. You kick off another round of transaction log restores to cover 11:00-12:00. Et cetera until your secondary is sufficiently close to the primary that it can make up the difference with the active portion of the log.
How will you know? Two things. First, if you try to add the secondary and it's not close enough, you'll get the error message you got in re: The remote copy of database "AGTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.'. Second, if there are any t-log backups of the primary that you haven't restored to the secondary, you'll need to restore them.
Clearing up one last thing - the reason you need to do all of the above restores (not just the full backup, the t-log backups as well) using the with norecovery clause is that that's what allows more restores to be done on top of the resulting database. That clause does exactly what it says - it skips the portion of the restore process that runs through so-called crash recovery (which is the process that does roll forwards and roll backs of transactions so that the resulting database is transactionally consistent with the backup).


Should NOT IN be avoided? Among some SQL Server developers, it's a widely held belief that NOT IN is terribly slow, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. (example).

Is there any truth to that?

Is there, for example, some known bug in SQL Server (which version?) that causes queries using NOT IN to have a worse execution plan than an equivalent query that uses

a LEFT JOIN combined with a NULL check or
(SELECT COUNT(*) ...) = 0 in the WHERE clause ?

I don't think it has anything to do with being terribly slow; it has to do with being potentially inaccurate. For example, given the following data - orders that could be placed either by an individual customer, or a B2B partner:

DECLARE @Customers TABLE(CustomerID INT);

INSERT @Customers VALUES(1),(2);

DECLARE @Orders TABLE(OrderID INT, CustomerID INT, CompanyID INT);

INSERT @Orders VALUES(10,1,NULL),(11,NULL,5);

Let's say I want to find all of the customers who have never placed an order. Given the data, there's only one: customer #2. Here are three ways I might go about writing a query to find that information (there are others):

SELECT [NOT IN] = CustomerID FROM @Customers
  WHERE CustomerID NOT IN (SELECT CustomerID FROM @Orders);

SELECT [NOT EXISTS] = CustomerID FROM @Customers AS c
  WHERE NOT EXISTS (SELECT 1 FROM @Orders AS o
  WHERE o.CustomerID = c.CustomerID);

SELECT [EXCEPT] = CustomerID FROM @Customers
EXCEPT SELECT CustomerID FROM @Orders;

Results:

NOT IN
------
                 -- <-- no results. Is that what you expected?

NOT EXISTS
----------
2

EXCEPT
------
2

Now, there are some performance issues as well, and I talk about them in this blog post. Depending on the data and indexes, NOT EXISTS will usually outperform NOT IN, and I don't know if it could ever perform worse. You should also note that EXCEPT can introduce a distinct sort operation, so you may end up with different data (again, depending on the source). And that the popular LEFT OUTER JOIN ... WHERE right.column IS NULL pattern is always the worst performer.

Martin Smith has a lot of good supporting information in his answer on SO, too.


View with SentryOne Plan Explorer from SSMS context menu doesn't work if Plan Explorer is already open I am using SentryOne Plan Exporer.

When I right click in the execution plan pane in SSMS, the first option is

  View with SentryOne Plan Explorer

Clicking this fires up an instance of Plan Explorer and opens the execution plan.

If I leave Plan Explorer open and then run another query in SSMS and right click in the Execution Plan pane and once again click View With SentryOne Plan Explorer, nothing happens. I would have thought either a new tab or new instance of Plan Explorer would open or a new tab in the already running instance?

Am I doing something wrong or is this a feature?

Well, the best option is to just generate the plan from within SentryOne Plan Explorer in the first place, instead of switching back and forth. You get much richer metrics this way, because it captures a bunch of things SSMS doesn't, including actual costs, I/O, and Index Analysis.
But yes, this was a defect in an earlier release, that came about after the add-in had to change yet again due to the evolution of SSMS and how it works with add-ins.


Update row based on match to previous row I have the following table:

Declare @m_TestTable table
(
RowID                  int,
OrderNo                nvarchar(10),
DetailID               int,
FirstUniqueDetailEntry int
)

Insert into @m_TestTable Values (1,'Order1',1,1)
Insert into @m_TestTable Values (2,'Order1',1,1)
Insert into @m_TestTable Values (3,'Order2',10,1)
Insert into @m_TestTable Values (4,'Order2',10,1)
Insert into @m_TestTable Values (5,'Order2',15,1)
Insert into @m_TestTable Values (6,'Order3',20,1)
Insert into @m_TestTable Values (7,'Order3',20,1)
Insert into @m_TestTable Values (8,'Order3',30,1)

The requirement I am battling with is to update the FirstUniqueDetailEntry column to be 0 if the OrderNo and DetailID are the same as the previous OrderNo and DetailID, resulting in:

'Order1',1,1
'Order1',1,0
'Order2',10,1
'Order2',10,0
'Order2',15,1
'Order3',20,1
'Order3',20,0
'Order3',30,1

Attempt:

Update @m_TestTable
Set FirstUniqueDetailEntry  = 0
where
    [OrderNo] = (Select [OrderNo] from @m_TestTable t where t.RowID =  RowID -1)
and [DetailID] = (Select [DetailID] from @m_TestTable t where t.RowID =  RowID -1)

UPDATE m
  SET FirstUniqueDetailEntry = CASE
    WHEN m2.OrderNo = m.OrderNo
    AND m2.DetailID = m.DetailID THEN 0 ELSE 1 END
  FROM @m_TestTable AS m
  INNER JOIN @m_TestTable AS m2
  ON m.RowID = m2.RowID + 1;

SELECT * FROM @m_TestTable ORDER BY RowID;

Results:

RowID   OrderNo   DetailID   FirstUniqueDetailEntry
-----   -------   --------   ----------------------
1       Order1    1          1
2       Order1    1          0
3       Order2    10         1
4       Order2    10         0
5       Order2    15         1
6       Order3    20         1
7       Order3    20         0
8       Order3    30         1


Transaction Log Backup Maintenance Plan and Newly Restored Databases When we add a new database (restore from a staging instance) to our live instance we have a job which sets it to Full recovery (in case the tech forgot that) and then a maintenance plan that runs a Full backup each night. We also have a maintenance plan that runs a transaction log backup every 30 minutes. How do I stop the latter failing whilst we are waiting for the first Full backup to complete? I’m thinking I maybe cannot achieve this with maintenance plans but need a bespoke agent job?

Check out Ola Hallengren's maintenance scripts. They have a @ChangeBackupType parameter that lets you automatically escalate to fulls if necessary.


SQL Server 2008 R2 memory on 64 bit OS My application server is running SQL Server 2008 R2.
OS is Windows 2008 R2 with SP1.
Total physical memory is 16 GB.

When I looked into the Task Manager, the sqlservr.exe was using "6,734,588 K".

When I looked the Memory Setting, it's all default (see below).

It is obvious that the max memory can go as high as 2,147,483,647,000,000 in BYTES, which is more than the available physical memory.

How much is the recommended setting for typical server like mine?

My application sometimes throw "out of memory" error. I haven't linked that issue with the SQL Server, but I am trying to see what I can do in terms of memory usage.

If I changed the max memory setting, do I need to restart the SQL Server?

If application is not reserved for database engine purposes only, it was recommended to limit it to 60%-70% of the total resources available at the server. Enterprise applications those using open transactions when the application is running, most likely to reach maximum memory usage as it can reserve.

Changing maximum server memory via SSMS does the triggering automatically and no restart of the services needed. Hereby you can also use following script to get it programmatically which also doesn't need a service restart afterwards;

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'12000'  // Amount of Memory Limit in MegaBytes
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE ";
GO


I need to remove duplicate values SQL Left Join My SQL code:

SELECT
users.id,
users.username,
job.category,
locations.location
FROM users
INNER JOIN job ON users.username = job.username
INNER JOIN locations ON job.username = locations.username

Results:

5   Don   Fashion/Design/Beauty         Mr
5   Don   Eng                           Mr
5   Don   Arts/Audio/Video Technology   Mr
5   Don   Fashion/Design/Beauty         Colombo
5   Don   Eng                           Colombo
5   Don   Arts/Audio/Video Technology   Colombo
5   Don   Fashion/Design/Beauty         gm
5   Don   Eng                           gm
5   Don   Arts/Audio/Video Technology   gm

I need to remove those 6 duplicate results in SQL to get the below results. Please kindly help me to figure out this.

5   Don   Fashion/Design/Beauty         Mr
5   Don   Eng                           Colombo
5   Don   Arts/Audio/Video Technology   gm

It is not possible to display in the format you want (without a lot of extra work and an assumption that there are always as many locations as job categories.)

Using GROUP_CONCAT however will allow you to return 1 row per username which contains all the information you seek, regardless of the number of job categories and locations.

SELECT
users.id,
users.username,
GROUP_CONCAT(job.category) AS job_categories,
GROUP_CONCAT(locations.location) AS locations
FROM users
INNER JOIN job ON users.username = job.username
INNER JOIN locations ON job.username = locations.username
GROUP BY users.id

It will return results like:

5   Don   Fashion/Design/Beauty,Eng,Arts/Audio/Video Technology        Mr,Colombo,gm


Creating a lost and found schema I have a school assignment to create a lost and found schema.

An admin can log a found item (someone turned it into the lost and found office)
A user can report a lost item
A user can claim a found item

Would the below schema work or should I somehow merge lost/claim into one table?

I would create tables for:

People
Actions
Items
Actions By People Against Items

In SQL, this would look like:

CREATE TABLE dbo.Actions
(
    ActionID int NOT NULL PRIMARY KEY
    , ActionDescription varchar(100) NOT NULL
);

CREATE TABLE dbo.People
(
    PersonID int NOT NULL PRIMARY KEY
    , PersonName varchar(100) NOT NULL
);

CREATE TABLE dbo.Items
(
    ItemID int NOT NULL PRIMARY KEY
    , ItemDescription varchar(200) NOT NULL
);

CREATE TABLE dbo.ItemActions
(
    ItemID int NOT NULL FOREIGN KEY
        REFERENCES dbo.Items(ItemID)
    , PersonID int NOT NULL FOREIGN KEY
        REFERENCES dbo.People(PersonID)
    , ActionID int NOT NULL FOREIGN KEY
        REFERENCES dbo.Actions(ActionID)
    , ActionDate datetime NOT NULL
);

Sample data:

INSERT INTO dbo.People (PersonID, PersonName)
VALUES (1, 'Ella')
    , (2, 'Mike')
    , (3, 'Sue');

INSERT INTO dbo.Actions (ActionID, ActionDescription)
VALUES (1, 'Lost')
    , (2, 'Found')
    , (3, 'Claimed');

INSERT INTO dbo.Items (ItemID, ItemDescription)
VALUES (1, 'Apple')
    , (2, 'Pear')
    , (3, 'Peach')
    , (4, 'Ameoba');

INSERT INTO dbo.ItemActions (ItemID, PersonID, ActionID, ActionDate)
VALUES (1, 1, 1, '2018-03-04 12:34:56')
    , (1, 2, 2, '2018-03-05 09:17:00')
    , (2, 1, 1, '2018-04-01 10:42:01')
    , (3, 1, 1, '2018-04-01 11:40:05')
    , (3, 2, 2, '2018-04-02 15:40:00')
    , (2, 3, 3, '2018-04-03 17:54:46');

Query:

SELECT i.ItemDescription
    , p.PersonName
    , ia.ActionDate
    , a.ActionDescription
FROM dbo.ItemActions ia
    INNER JOIN dbo.Items i on ia.ItemID = i.ItemID
    INNER JOIN dbo.People p ON ia.PersonID = p.PersonID
    INNER JOIN dbo.Actions a ON ia.ActionID = a.ActionID;

Results:

╔═════════════════╦════════════╦═════════════════════════╦═══════════════════╗
║ ItemDescription ║ PersonName ║       ActionDate        ║ ActionDescription ║
╠═════════════════╬════════════╬═════════════════════════╬═══════════════════╣
║ Apple           ║ Ella       ║ 2018-03-04 12:34:56.000 ║ Lost              ║
║ Apple           ║ Mike       ║ 2018-03-05 09:17:00.000 ║ Found             ║
║ Pear            ║ Ella       ║ 2018-04-01 10:42:01.000 ║ Lost              ║
║ Peach           ║ Ella       ║ 2018-04-01 11:40:05.000 ║ Lost              ║
║ Peach           ║ Mike       ║ 2018-04-02 15:40:00.000 ║ Found             ║
║ Pear            ║ Sue        ║ 2018-04-03 17:54:46.000 ║ Claimed           ║
╚═════════════════╩════════════╩═════════════════════════╩═══════════════════╝

This setup means you could add other actions such as "Thrown Away" to the list of possible actions without disrupting your design.


For a software application, does every workstation need database drivers? really a just a newb question. In the case of something like an enterprise system for a company, where end-users are executing continuous reads and writes to a database, it does not appear that in most cases each workstation needs the database being used's drivers. Credentials are being passed through the software, to the databases IP and port, and I am supposing that when a new user is added to the software, it is adding them to the database and their read / write privileges are assigned at that point. The user provides their login information to the software which serves as their credentials to make read / writes to the database.

However, if I want to access the database from third-party software, Excel or what not, I then do need the drivers for the database. I guess I am not sure why this is and was wondering if someone could explain it to me.

An example, a company I used to work for used IBM DB2. We had an enterprise system around it with DB2 as the database. Using the software surrounding it, I don't recall ever needing the drivers for it. When we started writing custom reports for it in Excel, then I needed the ODBC drivers as did any other workstation that needed the Excel -> DB2 connection.

It all depends on the application and how it is designed.

Two main cases:

Client application connects to an application server, application server connects to the database.

In this case, no need for database drivers on the client side. The client application never talks directly to the database but calls the server-side software only. The client application in this case can be a web browser or a custom application (large or small) - doesn't matter. The application server software hides the details, and the end-user workstation doesn't even know what type of database (if any!) is used in the backend.

This is how most of the web works.

Here, end-users don't necessarily need credentials on the database. Some applications require it (they essentially delegate authentication to the database), some on the other hand deal with the authentication themselves. In that case, the server-side application software will have credentials on the database but the end-users will not. The server-side application will usually use the database to store identifiers and privileges in its own format, and allow/deny actions based on that.

The end-users won't need network access to the database server(s) in this scenario, only to the application servers.
Client application connects directly to the database

Usually in this case, the client-side application is fairly large (for "Enterprise" software). All the application processing is done in the client-side software and/or stored procedures in the database, the client interacts directly with the database.

Here you need drivers on the client side. (But that may not require a separate driver installation. E.g. a Java application could come bundled JDBC drivers and use those directly.)

End-users need to have credentials in the database, with appropriate privileges (I'm completely ignoring some horrors with shared accounts). They also need network access to the appropriate port(s) on the database server(s), either directly, via proxies, VPNs or whatever.

(And there are hybrids of these two approaches, with some work done via an application server, some directly on the database. I haven't seen many of those though.)

The first case is more common these days in my experience, with the browser having become the most commonly-used client-side software.


How to query group for increase or decrease in value In MySQl I have a table positions and I would like to find out if position of certain app for certain feed in a certain country type has been increasing/decreasing and by how much from the last reading.

This is the table structure:

CREATE TABLE `positions` (
  `application_id` int(11) unsigned NOT NULL,
  `country` varchar(2) NOT NULL,
  `feed_id` int(2) NOT NULL,
  `position` int(4) NOT NULL,
  `created` datetime NOT NULL,
  KEY `application_id` (`application_id`,`store`,`feed_id`,`position`),
  KEY `created` (`created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

country is a two character ISO country code, every row belongs to one application through application_id and feed_id is another table that is helping to sort out the large amount of data.

What the query should look like? I have gotten to a pont where I group by the country code where application and feed id's are in WHERE clause but can't figure out the values :(

Join positions to itself.  In pseudo-sql:

select
    o.position,
    n.position
from positions as o        -- old
inner join positions as n  -- new
    on o.application_id = n.application_id
    and o.country = n.country
    and o.feed_id = n.feed_id
    and o.created = <max value for this app, country and feed that's less than n.created>

There's a discussion on this SO question for other options.


Profiling PostgreSQL We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive.

Our machine:

SO: CentOS 6
HW: 8 cores, 32GB RAM, 4Tb hard-drive;

We, for development and testing, are using the default PostgreSQL configuration, what I think, is somewhat wrong. I want to take advantage of the full capacities that our hardware provides, and so far we didnt got a lot of traffic. When this is release to the public, there will be a lot of users concurrently.

Our application has some queues and async strategies to handle the load, but, the main question is: how can I safely tune PostgreSQL, run some tests, and assure that the tuning was somewhat effective?

The default Pg config is almost certainly sub-optimal, it's a conservative default intended for "will run anywhere" not "will be fast".

There's lots to be said on that; see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server, http://wiki.postgresql.org/wiki/Performance_Optimization, and Greg Smith's "PostgreSQL 9.0 High Performance". (By way of disclaimer, I now work with Greg at 2ndQuadrant, but I was recommending his book before I moved to 2ndQuadrant).

I won't repeat what's already covered in those references.

I advise that you run pg_test_fsync to see whether fsync() performance is acceptable on your I/O subsystem, as this is critical for decent transaciton-processing throughput. It doesn't matter as much for read-mostly workloads. If it's poor, consider getting a couple of high quality SSDs with proper built-in power-fail protection or a RAID controller with a battery-backed write cache. By way of comparison, my single cheap SSD laptop tends to hit about 5000 ops/sec in pg_test_fsync.

Also use sysbench to get some general idea of what disk seq and random throughput are like. This will be useful when you're adjusting random_page_size and seq_page_size.

Most importantly of all, use a connection pooler like PgBouncer instead of raising max_connections above a couple of hundred.


After Restoring Log Shipping to Secondary Server, First Stored Procedure Execution is Slow We've set up log shipping to a secondary SQL server on Standby/ Read-Only to offload all SSRS report generation.
This works fine within the restrictions imposed by:

Kicking out the user during the transaction log restore (we got around this by setting up multiple instances and restoring the most recent transaction logs using a round-robin schedule)
The data being out of date by, at most, the time-frame indicated by the scheduled transaction log backup/ restore job.

Unfortunately, the first time any/ all stored procedure are run, after the transaction log was restored, it takes a much longer time to complete than normal. All subsequent executions of that same stored procedure complete within the expected time. If we then execute another stored procedure, the first time it is slow and all subsequent executions complete in the expected time.

For reference, the difference in execution is ~00:02 normally compared to ~01:00 on the first run.

I assume this has something to do with either the server execution statistics or the stored procedure parameter sniffing/ stored execution plan.
Is there any way to get around this issue? Or is this inherent to the transaction log restore?

If it was just the very first execution of any stored procedure we could get around this easily by executing any stored procedure upon restore, but it appears to affect the first time all stored procedures are executed.

I tried running count( * ) on the 11 tables the stored procedure I'm using for testing touches. The first run took 00:32, and subsequent count(*) took 00:00. Unfortunately, this did not have any impact on the first run of the stored procedure.

I don't see any results on either my primary or secondary servers for is_temporary stats, either before or after execution of a stored procedure.

I'm currently on SQL Server 2012Query Exection Plan:The query execution plan at first glance appears significantly different, however, upon saving the execution plan and opening the .sqlplan file generated they are exactly the same. The difference appears to be coming from the different versions of SSMS I am using, 2014 on the primary server and 2018 on the secondary. When viewing the execution plan on the secondary it shows underneath every node's % and time cost ### of ### (##%) - neither those numbers, nor the actual execution plan change upon further executions.I also included client statistics and they show almost exactly the same, the only difference being the primary server executes with 1.4 seconds of Wait time on server replies and the secondary takes 81.3 seconds.I do see a large number of PAGEIOLATCH_SH locks from the first execution, as you predicted:

diff after first exec vs diff after second exec
waiting_tasks_count    10903    918
wait_time_ms          411129  12768

One of the odd things about this situation, is, except for the round-robin multiple instances part of the setup we already have our production SSRS server reading from a standby/ read-only database that is fed by periodic transaction logs and do not experience these slow downs on the first execution of a stored procedure. Our users are kicked off every time the transaction log is restored, though, which is the problem the above setup is supposed to resolve.

There are a few possible things going on here, here's a non-exhaustive list:

the execution plan cache is cleared by the log restore, so plans will need to be recompiled the first time. If your plans have long compile times, this could explain the difference.  You didn't mention exactly how long the delay is on the first run compared to the subsequent runs

this one seems like the least likely - you can see your plan compilation time in actual execution plan XML

the buffer pool is also cleared during the restore, so all data has to be read from disk on the first execution

if this is the case, you'll likely see high PAGEIOLATCH* waits during the initial run if you check wait stats

Some things you could do to mitigate this are

"warm up" the buffer cache (by reading all data from important tables into memory using SELECT COUNT(*) FROM dbo.YourTable),
"warm up" the proc cache by running all the critical stored procedures as a post-restore step

Providing us with a "fast" and "slow" example of an execution plan could help us track down exactly which thing is happening.

If you are on SQL Server 2012 or newer, then it's possible that sync stats updates are causing the delay.  These "readable secondary stats" get created in TempDB, since the log shipping secondary is read-only.  You can read more about that here (the article is about AGs, but the same thing applies in this scenario):

AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

If this is the problem causing your slowdown, then one solution would be to find those stats, and then create them in the production database, so that they are up-to-date and available after the restore.  You can look for temp stats with this query:

SELECT * FROM sys.stats WHERE is_temporary = 1;

Based on the wait stats you provided, and the fact that the plans are the same, this is pretty conclusively due to the buffer pool being cleared by the log restore.

On a normal run, you get 12,768 ms (almost 13 seconds) of IO waits.
On the first run, you get 411,129 ms (almost 7 minutes) of IO waits.

The SELECT COUNT(*) approach you tried may not have helped due to different indexes being used by the actual procedure vs the COUNT(*) query.  You have a few options here:

Go through each execution plan and make note of the indexes being used, and then pull those indexes into memory as a post-restore step - using index hints this time (SELECT COUNT(*) FROM dbo.YourTable WITH (INDEX (IX_Index_Being_Used_By_Proc)))
Go through the process of scripting out a process to run each procedure as a post-restore step (this seems a bit easier than option 1)
Tune the queries so that they don't need to do so many reads (not sure how feasible this is)
Speed up the I/O subsystem - get faster disks, local SSDs, more channels to the SAN, etc (this is probably the hardest and most expensive option


SSIS Performance tuning Suppose my system has 8GB RAM and I have installed SQL Server 2012 on it along with the DataTools. Sql server engine uses 6GB memory. Will the SSIS uses remaning memory apart from 6GB or it uses 6GB?
Also is there a way to count the number of buffers used and the records accomodated in each buffer and the size of the buffer?

If am not wrong buffer refers to the RAM memory?

SSIS will use as much memory as it needs. If there is not enough physical DRAM on the box, it will take it from the page file (which generally results in a big slowdown)

The memory for running SSIS does not come out of the SQL Server memory space, it comes out of the memory that is left on the box after SQL Server has taken what it wants. In your case, this would be 2GB. Remember that out of those 2GB, you will also need space for the OS and another other applications running on the box.

In other words, think of SSIS just like any other application - not as part of SQL Server (even though it installs with it).


How to update one table based on another table's values on the fly? I have a table in the name of ips as below:

CREATE TABLE `ips` (
 `id` int(10) unsigned NOT NULL DEFAULT '0',
 `begin_ip_num` int(11) unsigned DEFAULT NULL,
 `end_ip_num` int(11) unsigned DEFAULT NULL,
 `iso` varchar(3) DEFAULT NULL,
 `country` varchar(150) DEFAULT NULL
) ENGINE=InnoDB

Lets assume I have a countryid field on this table from country table which is as below:

CREATE TABLE `country` (
 `countryid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `ordering` smallint(5) unsigned NOT NULL DEFAULT '0',
 `iso` char(2) NOT NULL,
 PRIMARY KEY (`countryid`)
) ENGINE=InnoDB

There is about 100,000 records in ips table. Is there any query for the following scenario:
Check if ips.iso is equal to country.iso, if it's equal then add country.coutryid to that record. I couldn't think of any way to do it. Do you have any idea how to do that?

UPDATE ips INNER JOIN country
    ON ips.iso = country.iso
SET ips.countryid = country.countryid

Using MySQL update multiple table syntax:

14.2.11 UPDATE Syntax

Note that you have two different lengths and data types on your iso columns.  There are, in fact, two separate sets of ISO codes, 2-letter and 3-letter, so you may not in reality be able to join these columns:

ISO 3166-1

The join condition USING (iso) instead of ON ips.iso = country.iso works too.


Select rows based on a minimum in one column Working with PostgreSQL 9.4, I want a result that takes into consideration only min() (all aggregate functions) values of distance on JOIN or WHERE. But its seems those things are not allowed. So, I am selecting the distance between two points and I want to filter it just to take the min() values into consideration.

To resume the question clearly, suppose these rows:

id; gid; distance; time_interval
142; 028; 62; "21:46:00"
200; 028; 53; "08:20:11"
128; 034; 92; "09:24:43"
179; 034; 70; "08:09:34"
194; 034; 92; "05:31:05"
199; 034; 88; "07:15:48"
200; 034; 61; "14:13:43"
202; 035; 24; "17:32:34"
200; 036; 76; "06:02:11"
154; 037; 97; "12:58:58"
154; 040; 30; "11:34:10"
132; 042; 80; "07:01:12"
142; 042; 67; "19:30:21"

How can I extract id, gid, distance and the time_interval that consider just the min(time_interval) values correspondent to that gid. The problem is that I just can see this with this query:

SELECT id, gid, distance , min(time_interval)
FROM dis
WHERE time_interval = min(time_interval)
GROUP BY id, gid, distance

The result should be:

id; gid; distance; time_interval
200; 028; 53; "08:20:11"
194; 034; 92; "05:31:05"
202; 035; 24; "17:32:34"
200; 036; 76; "06:02:11"
154; 037; 97; "12:58:58"
154; 040; 30; "11:34:10"
132; 042; 80; "07:01:12"

Use DISTINCT ON:

SELECT DISTINCT ON (gid)
       id, gid, distance, time_interval
FROM   dis
WHERE  time > 0  --  filtered just to take positive values (orig. Q)
ORDER  BY gid, time_interval; -- optionally more expressions to break ties

Details:

Select first row in each GROUP BY group?


How to trace security_error_ring_buffer_recorded errors in SQL Server 2014 Express Just so everyone doesn't have a high expectation of my level of knowledge on this topic, I'm not a DBA; I'm a .NET developer who has to try and figure this out by virtue of being "the one most likely to be able to figure it out".

We're running SQL Server 2014 Express in Mixed Mode for an e-commerce website, and I've noticed several thousand security_error_ring_buffer_recorded errors in the space of 7 days.

There aren't any corresponding errors that I can see in any SQL
Server or windows logs.
There are no logs which correlate the session_id and SPIDs.
There's no information on any particular entry    other than the
details listed below (and other than the timestamp,    session_id,
and call stack, they're all identical).
There are no 139F or 5023 error codes in any logs as well. No
State 11 errors, either.
I don't see either an SQL Server user or a Windows user associated with
these entries.
The SQL Server itself is behind a Windows firewall and only a
small number of IPs have access to it (approx. 20, and I can account for all of them). The RDP to the server is restricted to the same IPs.

So my questions are these:

Is this something I should be concerned with (I tend to be just given the volume of entries)?
How else can I debug these errors to figure out where the underlying problem is?

Thanks.

Take a look on this article: Troubleshooting ImpersonateSecurityContext in RING_BUFFER_SECURITY_ERROR

  The error message 5023 details are:

  “The group or resource is not in the correct state to perform the
  requested operation”

  According to Microsoft documentation “The ImpersonateSecurityContext
  function allows a server to impersonate a client by using a token
  previously obtained by a call to AcceptSecurityContext (General) or
  QuerySecurityContextToken. This function allows the application server
  to act as the client, and thus all necessary access controls are
  enforced.”

  Basically this function is called when impersonation is required. To
  do so, there must already be a valid context handle.

  Analyse error logs to identify Login failures and report on the state
  number.

  Some reasons I've spotted are:

  1) A service (set as Local System) on a server attempting to log on to
  SQL Server and not having access

  2) IP registered on the DNS server with the same FQDN

You can also check SQL Server error log to check if SPN was successfully registrated.


Distribution database corrupt - What to do? The below error message has been received from one of our SQL Server databases:

  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x6847f47b; actual: 0x15160a19). It occurred during a read of page (1:934295) in database ID 5 at offset 0x000001c832e000 in file 'C:\XYZ.MDF'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Obviously I have to run DBCC CHECKDB on the corrupt database. Before doing that, I tried to find out what the database name was (using database id) and what object the error was related to, using and the page number that was given in the error message.

So I understood that the database name was distribution and the table name was MSrepl_errors.

I'm not much familiar with SQL Server replication features, so would appreciate if someone can please help me with the below questions:

In order to investigate and/or possibly fix the issue, I have to first execute DBCC CHECKDB; however, CHECKDB cannot be run  without first putting the database in single user mode, is this correct?
Regarding the fact that the distribution database is not used by end users, do I still have to put it in the single user mode? And, if yes, what will be the impact on the replication jobs?

However, CHECKDB cannot be run without first putting the database in single user mode. Is this correct?

It shouldn't need to be in single user in order to run checkdb. You can run DBCC CHECKDB('distribution') WITH NO_INFOMSGS, ALL_ERRORMSGS to check it.

Since we don't know where the current issue is, it's hard to say what/if any impact to the current topology would be. We'd really need to know more, feel free to update the question with the output from CHECKDB.

Please do note that system databases, which the distribution database is considered, are not supported to have REPAIR_ALLOW_DATA_LOSS run on them. In fact, it should only be run in the most devastating of situations and under duress. Choosing to do this to a system database will immediately become an unsupported configuration.


AWS DMS endpoint test connection error I am trying to migrate Microsoft SQL Server databases from on-premise to an RDS instance. I have created a SQL Server 2014 Express Edition instance in RDS and I have SQL Server 2014 Express Edition on premise in in my corporate network. I am able to connect to the RDS instance from my on premise SSMS. While using the DMS service to migrate and creating endpoints, I am getting below error.

  Error Details:
  [errType=ERROR_RESPONSE, status=1022506, errMessage=Failed to connect Network error has occurred, errDetails= RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired ODBC general error.]]

I am getting the same error even when I am testing the target endpoint which is an RDS instance. I have kept RDS and replication instance on the same VPC. Port 1433 is open in my system and as I am able to connect to the RDS instance via SSMS so there shouldn't be a port issue.

Please help.

I had a similar issue, though I was using DMS to migrate data from S3 to an (RDS) SQL Server 2008. I figured out what my issue was, I didn't require SSL on the SQL Server endpoint. I additionally had to make sure my IAM policy had the right level of access to the services. Hope this helps.

Additionaly:

  AWS DMS currently does not support SQL Server Express as a source or target.

Reference: Troubleshooting Migration Tasks in AWS Database Migration Service (AWS Database Migration Service | User Guide)


External size of table still not released (as disk space) after issuing truncate command In PostgreSQL 9.3.10 (Ubuntu 14.04 OS), I have a table called groupseen whose size is as follows:

              Table               |  Size   | External Size
----------------------------------+---------+---------------
          links_groupseen         | 394 MB  | 362 MB

I understand that External Size is the size that related objects of this table (like indices) take.

Upon issuing the command truncate links_groupseen;, only 394 MB disk space was released (I checked right after, via df -h). My two questions are:

What can I do to get rid of the space taken up by this table's indices (External Size) as well? Shouldn't truncate have taken care of everything?
I was using the following command to check the size of this table. Even though External Size hasn't yet been released, using this command now doesn't show the outstanding 362 MB. How do I monitor the outstanding External Size related to this table?

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
 FROM pg_catalog.pg_statio_user_tables
 ORDER BY pg_total_relation_size(relid) DESC;

In case required, here's how the table looks like:

                            Table "public.links_groupseen"
     Column     |           Type           |                          Modifiers
----------------+--------------------------+--------------------------------------------------------------
 id             | integer                  | not null default nextval('links_groupseen_id_seq'::regclass)
 seen_user_id   | integer                  | not null
 seen_at        | timestamp with time zone | not null
 which_reply_id | integer                  | not null
Indexes:
    "links_groupseen_pkey" PRIMARY KEY, btree (id)
    "links_groupseen_seen_user_id" btree (seen_user_id)
    "links_groupseen_which_reply_id" btree (which_reply_id)
Foreign-key constraints:
    "links_groupseen_seen_user_id_fkey" FOREIGN KEY (seen_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_groupseen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED

Note: this SO answer doesn't solve  my problem because the OP in the question stated how disk space wasn't released for him at all. In my case, the subtlety is that only External Size is outstanding.

I think with a truncate even without a VACUUM FULL the space is released back to the OS. My guess is per the other question that you've got a backend process holding the underlying file open.

Find out what the file is. SELECT pg_relation_filepath('links_groupseen');
Find the data directory SHOW data_directory;
Run truncate.
See what's holding it open. lsof $data_directory/$relation_filepath

I think the most likely bet is something is holding it open if not, we'll have to know whether or not Pg is deleting the underlying relation_filepath before and after the truncate command and if anything has that file open (if it exists). You may have to run lsof before the truncate too.


sum() of column by distinct() id column I have the following query:

select
    sum(leads.source_cost) as cost,
    coalesce(sum(assignments.recipient_revenue), 0) as revenue,
    (coalesce(sum(assignments.recipient_revenue), 0) -
              sum(leads.source_cost)) as profit,
    date_format(leads.updated_at, "%m/%d/%Y") as date
from `leads`
left join `assignments` on `assignments`.`lead_id` = `leads`.`id`
                       and `assignments`.`deleted_at` is null
where leads.updated_at between "2017-08-24 04:00:00"
                           and "2017-08-26 03:59:59"
group by `date`

I need the sum(leads.source_cost) to use distinct leads.id in my query because a lead can be assigned more than once.

How do I do this? I can't do a group by because I need the group by to use the date alias.

https://tiaashish.wordpress.com/2014/01/31/mysql-sum-for-distinct-rows-with-left-join/

Here is a blog post that describes exactly this situation and provides a very nice solution to it, using a simple formula:

SUM(leads.source_cost) * COUNT(DISTINCT leads.id) / COUNT(*)


access denied when mysql to localhost using public ip I would like to use the command below to connect to my Mac's local database (xx.xxx.xxx.xxx is the Mac's ip address)

mysql -u cgao -h xx.xxx.xxx.xxx -p

and I got the error message:

ERROR 1045 (20000): Access denied for user 'cage'@'dhcp-xx-xxx-xx-xxx.dyn.mycompany.com' (using password: YES)

But it will succeed when I enter the same command in another computer. How can I solve it? Can I only use localhost or 127.0.0.1 to local machine?

I feel it is somehow related to the dynamic ip address in my company. But I didn't find the exact answer on the web. Can someone elaborate the reason and share suggestions?

If you want to login to MySQL on the local machine using the IP address do this

mysql -u cgao -h xx.xxx.xxx.xxx -P3306 --protocol=tcp -p

This force a TCP/IP connection locally


Single query to return counts over different IDs in a single record select count(title_id)as algodata from titles where pub_id =1389
select count(title_id)as binnet   from titles where pub_id =0877
select count(title_id)as newmoon  from titles where pub_id =0736

The database used is pubs in SQL Server.

Can I use a single query to  show the count of records for each publisher (here there are 3 publishers) in a single record?

Similar to @ypercubes but to get one row without 3 separate queries

select
     count(CASE WHEN pub_id = '1389' THEN title_id END) as algodata,
     count(CASE WHEN pub_id = '0877' THEN title_id END) as binnet,
     count(CASE WHEN pub_id = '0736' THEN title_id END) as newmoon
from titles
where pub_id IN ('1389', '0877', '0736')

Also, decide if your values are numbers or strings and be consistent to avoid datatype conversions


Connection error while connecting to Oracle XE This just started happening this morning, and I'm not sure what the cause is.  IT just installed some sort of Windows update but I'm not sure if this is related to the issue.  Basically, when I try to connect to Oracle I get a connection timeout error.  When I start to connect, I immediately see the following error message in the alert_xe.log file:

***********************************************************************

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

  VERSION INFORMATION:
        TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
        Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
        Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
  Time: 28-JUL-2016 09:04:38
  Tracing not turned on.
Thu Jul 28 09:04:38 2016

***********************************************************************
  Tns error struct:

Fatal NI connect error 12560, connecting to:
 (LOCAL=NO)
    ns main err code: 12560

  VERSION INFORMATION:
        TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
        Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
        Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production

  Time: 28-JUL-2016 09:04:38
TNS-12560: TNS:protocol adapter error
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12560

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

I'm not really an Oracle expert, I'm a C# programmer and we just happen to use Oracle as our backend.  Any ideas on how to get me unstuck so I can continue my work?  Thanks!

The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection process before the server process was completely spawned. Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.  This is also triggered when a DB session is killed/aborted manually from the OS prompt.

  To remove a primary ORA-609 error, you should increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure. If the problem is due to connection timeouts, and increase in the following parameters should eliminate or reduce the occurrence of the ORA-609 error.

  Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
  Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

Reference
See: ORA-609 opiodr aborting process unknown ospid tips (Burleson Consulting)


SMO : The login already has an account under a different user name, when calling TransferData() I need to implement the following requirement. I have setup a remote sql server express, certain users shall be able to create databases on remote server and assign other users to have access on it  (read,write). Users shall be able to see only the databases created by them or in which they have rights.
First I have created an empty database on remote server => dbo for new database is x, then  I've created a different login with:
USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Then deny view to any database:
USE master;
GO
DENY VIEW ANY DATABASE TO [username];

Then create new user for login:
USE dbName;
CREATE USER [username] FOR LOGIN [username] ;
GO

Give db owner role:
EXEC sp_addrolemember 'db_owner', [username]

Give permission to see database (here is a little bit tricky because in ssms it can view all the databases but it can have access only on the mapped ones)
USE master GRANT VIEW ANY DATABASE TO [username];

Now on my given database I have 2 users one is username and the other is dbo which is mapped on the user that created the database and is different form username.
After that I'm using SMO to transfer this databases from remote server to localhost and while executing transferDatabase.TransferData(); I'm getting the following error "The login already has an account under a different user name." When I create the transfer instance I set also the property CopyAllUsers= true.
How can I fix this?
I've found out that if i make the following changes
USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

Then deny view to any database:
USE master;
GO
DENY VIEW ANY DATABASE TO [username];

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username];
GO

Now username sees in ssms only the databases on which is mapped and the transfer will probably work. But now I cannot distinguish between the first creator of the database and the recently granted user. For one database I should grant dbowner permissions to more than one user.
So if then I create another login and I execute:
USE master;
GO
ALTER AUTHORIZATION ON DATABASE::dbName TO [anotherUser];
GO

Now username can not access the database.

First, if you do both:
GRANT VIEW ANY DATABASE TO [username];

and
DENY VIEW ANY DATABASE TO [username];

the DENY still wins, so there was no sense to grant it, this is also the default permission that every new login has so there was no need to grant it again.
Second, you don't see the difference between database principal dbo and any member of the fixed database role db_owner. When you make some user a member of db_owner this user has all possible permissions in this database. But this has nothing to do with the fact "to see some database in Object Explorer".
To "see" any database in OE, login should have view any database permission.
Login and not user. So your login can has no view any database permission but still be a member of db_owner role, in this case user has all the permissions in that database but it cannot "see" any database in OE.
Dbo database principal is a special principal where any sysadmin (i.e. a member of sysadmin server role) is mapped, and the OWNER of database is also mapped to dbo.
The owner of database is one and only one login that created this database or was assigned to be database's owner by executing alter authorization statement:
ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username]

This way you assigned the LOGIN username to be the owner of this database. The owner of database is of course a member of db_owner role but not vice versa. DBO will always "see" the database it owns in OE as any sysadmin will.
To your question:
the error you receive tells you that you cannot execute
CREATE USER [username] FOR LOGIN [username]

Because the LOGIN username is already mapped to this database, as dbo.
So you cannot re-map it to another username.
If you want your script to run without errors change the database owner to any other login, for example to sa login, this way the login username can be created in this database.
Then if you want your username to be database owner just drop your user username from this database and alter authorization to username, this way it will be mapped to dbo.
Alternatively, if you already know that you want this user to be database owner, drop the user username from your database prior to transfer and it still be there in the database as it is mapped to dbo.

So if then I create another login and I execute:
USE master; GO ALTER AUTHORIZATION ON DATABASE::dbName TO
[anotherUser]; GO Now username can not access the database.

This is because as I explained above, database OWNER can be only one login at a time. If you want your ex-owner to access this database you should map it:
USE dbName;
CREATE USER [username] FOR LOGIN [username] ;
GO

You don't need to map a login to database only if it is sysadmin or database owner (which, I repeat it, can be only one). Any other login should be mapped to database (create user for login) in order to access it.


Hierarchy for dimensional model I am trying to model a DW where I have many levels of geography (Neighborhood, District, City, State).

I have several demographic data that should be included in the model. These data include count of people living in that area, average monthly income, average age, and others. The lowest level I have data for is neighborhood, which means it can be grouped together in order to calculate the values for the upper levels.

The Geography is modeled in a single (denormalized) dimension, where each level gets its own column.

Now I have to fit the demographic data in the model. Should I put it in another, separate dimension, or should I put it in the Geography dimension? What about the aggregated levels?

Putting it all on the Geography dimension would leave me with a large number of columns:

Id
Neighborhood Name
District Name
City Name
State Name
Neighborhood Avg Monthly Income
Neighborhood Avg working population age
Neighborhood Number of people
...
District Avg Monthly Income
District Avg working population age
District Number of people
...
City Avg Monthly Income
City Avg working population age
City Number of people
...

Is this correct? This seems rather convoluted. I searched for alternative designs on several books (including Kimball's), but haven't been able to find anything satisfactory.

Are there better, proven, approved and reliable alternatives to this design?

My take on this is that you may perhaps want to try using a parent-child dimension, so the model will allow for a theoretically infinited amount of levels. That way, you can connect facts to each level of the hierarchy.

Here's an example:

CREATE TABLE dimGeography (
    id
    parent_id
    geographyType
    geographyCode
    geographyName
    PRIMARY KEY (id)
);

CREATE TABLE factDemographics (
    date
    geography_id
    monthlyIncome
    workingPopulationAge
    numberOfPeople
    PRIMARY KEY (date, geography_id)
    FOREIGN KEY (geography_id) REFERENCES dimGeography (id)
);

The advantages of this approach are:

you're free to add or remove levels as you like over time without changing the data model
you can connect facts (demographics) to each level
no duplicate measure columns, i.e. just one column called "number of people"
future-proof: some countries or regions may use different types of levels - for instance, not all countries use "state".

The disadvantages that I can think of are:

you may have to redesign your existing (?) dimension or fact table
your BI tool may not be able to handle parent-child dimensions
more complex SQL queries when aggregating the facts on higher levels

I haven't the slightest idea if this is the "correct" solution according to Kimball, Inmon, etc. Instead, I would urge you to see if it applies to your particular needs. ;)


SQL 2005 SP2 Database on SQL 2012 Instance I've an SQL Server 2005 SP2 instance that contain old databases that are not compatible with other SP of SQL Server 2005.
Those databases are on physical server that don't have any space to backup them so i planned to put them on a new virtual machine on a SQL Server 2012 instance with compatibility level at 90.

But i want to know if the compatibility level of the 2012 instance is equivalent of an SQL Server 2005 SP4 because if is this i can't put the database on it.

The Compatibility Level never changes due to a Service Pack upgrade. So SP4 has the same Compatibility Level as SP2, same features and limitations etc, it's just presumably better/faster/more stable/more secure.

SQL2005 databases have Level 90 (unless they were restored from even older versions without updating the Level) and can be restored on SQL 2005 through 2012.

For the complete table of SQL Server versions vs supported Compatibility Levels, see:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level


Mongodb replica config slaveDelay but data still sync immediately I've set slaveDelay to 600s but when the Primary data change, the Secondary data change immediately. Here's the curent configuration of repl with 2 server.

"members" : [
{
    "_id" : 0,
    "host" : “primary.host1:27017",
    "arbiterOnly" : false,
    "buildIndexes" : true,
    "hidden" : false,
    "priority" : 1,
    "tags" : {

    },
    "slaveDelay" : NumberLong(0),
    "votes" : 1
},
{
    "_id" : 1,
    "host" : “secondary.host2:27017",
    "arbiterOnly" : false,
    "buildIndexes" : true,
    "hidden" : true,
    "priority" : 0,
    "tags" : {

    },
    "slaveDelay" : NumberLong(600),
    "votes" : 1
}]

I don't know where did I go wrong, or how is the slaveDelay work, since the secondary data should update after 600s.

If secondary do "initial sync", it will reach primary first, but then it will "sleep" (this case 600 seconds) to fall back and after that, it will keep syncing 600s behind.

You can see that (sleep) in mongod.log if you watch what is happening at end of initial sync.  After that sleep, if you look "last" document in opLog, it's time stamp is that 600s behind. Of course you can see that with rs.printSlaveReplicationInfo() too.


mongodb set feature compatibility to 3.4 fail I want to enable the features of my Mongodb 3.4 after upgrading. I have sharded cluster enviroment And after I ran the commands:

use admin
db.adminCommand ({setFeatureCompatibilityVersion: "3.4"})

In mongos instance I got the following output:

  {"ok": 1}

But when I try to check with it succeeded with command:

db.adminCommand ({getParameter: 1, featureCompatibilityVersion: 1})

I get the following output:

  {"ok": 0, "errmsg": "no option found to get"}

And when I check in ops manager I see that the command did not work

You probably ran your command

 db.adminCommand ({getParameter: 1, featureCompatibilityVersion: 1})

on a mongos instance. The documentation states in this case:

  The operation is undefined on the mongos instances. For a sharded cluster that has access control enabled, to run the command against a member of the shard replica set, you must connect to the member as a shard local user.

When you connect to a mongod instead, the command should work as expected.


Is it possible for SQL statements to execute concurrently within a single session in SQL Server? I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.

While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.

The good news is it's extremely unlikely to happen in the wild because

1) #Temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime).  And these are by far the most common case.

2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.

Here's a contrived example:

using System;
using System.Data.SqlClient;

namespace ado.nettest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
            {
                con.Open();

                var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
  insert into #t(id) values (1);
  select top 10000 * from sys.messages m, sys.messages m2;
  select count(*) rc from #t;
  delete from #t;
end
');
";
                var cmdDDL = con.CreateCommand();
                cmdDDL.CommandText = procDdl;
                cmdDDL.ExecuteNonQuery();

                var cmd = con.CreateCommand();
                cmd.CommandText = "exec #foo";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();

                    var cmd2 = con.CreateCommand();
                    cmd2.CommandText = "exec #foo";
                    using (var rdr2 = cmd2.ExecuteReader())
                    {

                    }

                    while (rdr.Read())
                    {

                    }
                    rdr.NextResult();
                    rdr.Read();
                    var rc = rdr.GetInt32(0);
                    Console.WriteLine($"Numer of rows in temp table {rc}");

                }

            }

            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}

which outputs

Numer of rows in temp table 0
Hit any key to exit

because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset.  Note that if the first resultset was small, the rows might get buffered and execution could continue without sending anything to the client.

If you move the

create table #t(id int)

into the stored procedure it outputs:

Numer of rows in temp table 1
Hit any key to exit

And with the temp table declared inside the procedure, if you change the second query to

cmd2.CommandText = "select * from #t";

It fails with:

  'Invalid object name '#t'.'

Because a #temp table created inside a stored procedure or nested batch is only visible in that stored procedure or batch and in nested procedures and batches that it calls, and is destroyed when the procedure or batch ends.


What can cause DBCC CheckDB to take longer than usual? I have a maintenance task, which do a DBCC CHECKDB WITH PHYSICAL_ONLY on the Databases on my server (SQL Server 2016 Standard Edition).
This usually takes about 7h30 for about 120 databases and it is an acceptable time. But once in a while (like yesterday), it took 10 hours to do this operation on the same amount of databases.
From the logs, I know that every DBCC CHECKDB took longer than usual to run. It's not only one DBCC CHECKDB that took 3 hours more. It's all individual CHECKDB that took a few minutes more.
The CHECKDB are running during the night, when clients are still using the Database, but where there's less activity.The databases have increased in size,
 but not that much that it would cause a 3 hours increase.
Other than client's activity on the Database, which can cause some delays, what can cause DBCC CHECKDB to take a much longer time than usual? I need to at least have an explanation to give to my boss as to why once in a while it does this and then reverts back to a normal time.
Thanks

Lots of options, kind of hard to speculate:

Slower IO to disks, perhaps due to activity elsewhere on the SAN?
Other activity on the box interfering (user activity, maintenance jobs, other databases, etc.)
Memory or CPU pressure on the box (admin logged on locally leaving things running, big SSIS packages eating up CPU or local memory)

Narrowing down which of these it might be won't be easy, you'll probably have to turn on some long-running perfmon logs and do some trending over time.


How do i set up an Alert that tells me when a replication of data failed? I've got Database Mail setup and tested fine.  Alerts do work when I stop and start the replication agent job, but I cannot get it to throw any alerts when data moves from a publisher db to a subscriber db successfully or unsuccessfully.  Ultimate goal is to notify me if something doesn't go right with a data replication.

You can set up SQL Server Agent Alerts to trap specific error numbers and respond to them by sending email notifications, or running jobs. In Management Studio, drill down to SQL Server Agent, Alerts. You might have a few defaults in there. If you don't have "Replication: agent success" and "Replication: agent failure", you can create them to watch for error numbers 14150 and 14151, respectively.

HOWEVER, since the agent success message isn't written to the SQL Server error log, you won't be able to have alerts on it by default. I just went through this trying to get email alerts for permission denied errors. You can use sp_altermessage to enable logging for a specific error message, but be careful that it isn't a frequent message, or you'll have some serious error log bloat on your hands. You don't need to do this to enable the agent failure message, which is logged by default.

EXEC sp_altermessage 14150, 'WITH_LOG', 'true'

Then in the "Response" page for the alert, you can choose which operators to notify. Set them up under SQL Server Agent, Operators if you don't have any defined.


Weak entity with a many-to-many relationship with its owner I have a database that keeps track of the hours worked by a contractor per week. I have one table for contractors that contains "con ID" as primary key. The other table Weekly Hours Tracker contains month day year (representing a particular week) and hours worked. Now from what I understand this table is a weak entity and relies on contractor table for its existence.

The relationship is defined as one contractor can work for many weeks and in one week there are many contractors working. So there is a Many to Many relationship.

So is the Weekly Hours Tracker a weak entity that has a many to many relationship with its owner entity Contractors?

Is following the correct way to represent the above tables?

(Bold attributes are keys)

Contractors Table

ConID, Name, HourlyRate, Address

Weekly Hours Tracker

ConID, Day, Month, Year, HoursWorked

Yes, your entity is OK, but I would recommend this :

Do not use a multiple fields for your date. Instead of Day, Month, Year, you should use DateWorked. Maybe you are already doing that but it doesn't reflect in your explanations. This way, your date will always be valid. You won't have to check if February 30, 2013 is a valid date or not.
I strongly recommend using only 1 field surrogate primary key like HoursID. You can create a unique index on ConID and DateWorked to validate it will not exist more than once.
You may add complexity now or later by using ProjectID and that project could also bind to a ClientID.