First Normal Form: Definitive Definition I am trying to get a definitive version of what is First Normal Form. Everything I read has a slightly different spin.

Many authorities, such as Date, say that by definition a relation is always in First Normal Form, while others give a list of requirements. This means that there are from zero to many requirements for 1NF.

I suppose the difference is that between tables and relations: tables can be a complete mess, while a relationship follows certain restrictions. The fact that a relation is represented as a table in SQL thus creates some confusion.

I am focusing specifically on 1NF as it relates to SQL databases. The question is: what properties are required to ensure that a table is in the first normal form?

Many authorities suggest that if a table represents a relation, then it is already in 1NF. This pushes the definition of 1NF back to the definition of a relation.

Here are some properties of a table in 1NF:

Column Order is insignificant [1]
Rows Order is insignificant
All rows are the same length (ie, row data matches column headers)
There are no duplicate rows (this can be guaranteed using a surrogate primary key, but the PK is itself not required)
There are no repeating columns
Each column contains a single value (atomic)

[1] Technically attributes are unordered, but in a table, the row data must be in the same order as the column headers. However, the actual order is insignificant.

On multiple data:

The concept of atomic data is that an item cannot be further broken down. This concept has been qualified in that although technically everything can be broken down ad nauseum, the data in question cannot be practically broken down any further, depending on how the data will be used.

For example, a full address or a full name should normally be broken down further, but the components such as the given name or town name should probably not be broken down any further, despite the fact that as strings they can be.

As regards repeating columns, it is a poor design column to have nearly repeating columns, such as phone1, phone2 etc. In general, repeated data indicates the need for an additional related table.

Dependence

There should not be any relationship between rows, other than that they conform to the same headers.

There should also be no relationship between columns, but I believe that is the subject of higher normal forms.

The question is: How much of the above is in the definition of 1NF? Does the independent rows bit also come into it?

Preliminary
The definition of normal form (which from the presentation of “Further Normalization of the Data Base Relational Model” in 1971 is known as first normal form) and the definition of the relational paradigm itself was published in 1970 in the scientific paper that provided a strong foundation for the practice of database administration, i.e., “A Relational Model of Data for Large Shared Data Banks” (RM for brevity) created by Dr. E. F. Codd, who is a Turing Award recipient and the authority with regard to the relational framework.
Yes, there is plenty of explanations, interpretations, expositions, deviations and opinions about Dr. Codd’s text, but I personally prefer to stick to the original source and I highly suggest that you analyze it by yourself so that you can draw your own conclusions.
I certainly do not understand the RM in its entirety, but what I do understand of it permits me appreciating its excellence, vision, intention and scope, and although decades later one can note that it has a few slight imprecisions, they do not reduce, in any way, its genius and elegance. In its field, the RM has stood the test of time in a unique way, and remains unmatched.
The act of emphasizing the aforementioned imprecisions would be —using a charitable term— unfair because, seeing it from from a considerable distance, this seminal material required a few refinements and extensions, yes, but the main body of the work was rock solid from the very conception (and, indeed, Dr. Codd made most —if not all— of such refinements and extensions himself).
I continue rereading the RM constantly in order to strengthen my comprehension of this exceptional source of knowledge (and my esteem of it keeps growing on every reread); the objective is to stand on the shoulders of giants.
Relations and tables
It is important to note that as relations are abstract resources,  Dr. Codd envisioned the utility of representing them in tabular form (he initially used the term “array representation” but subsequently utilized “table” or “r-table”), so that the users, designers and administrators of a relational database (RDB) can approach them in a more familiar or concrete manner. Therefore, within the context of a RDB implementation, it is valid to use table as a shorthand for relation, as long as said table stands for an actual relation. This feature is —although obvious— quite significant because before evaluating whether or not a table represents a relation that complies with first normal form (1NF), it has to represent, precisely, a relation.
The RM naturally contains the qualities that a table must have to determine if it in fact portrays a relation, but I will offer an informal and unpretentious interpretation about them here (another one, yes!):

It must have a name (each particular relation in a database structure must be distinguished from the rest).
Each of its rows must depict exactly one tuple of the pertinent relation.
The order of its rows is not important at all.
Each of its columns must have a name that stands for the meaning of exactly one domain of the concerning relation, and said name must be different from the names of the rest of the columns of the table (a column must be uniquely differentiated and must carry a distinctive meaning and, yes, the role played by a database modeler and the business experts to define each domain of significance with accuracy is paramount)
The order of its columns has no significance.
All of its rows must have the same number of columns.
It must have at least one column, or one combination of columns, that uniquely identifies every one of the tuples depicted via rows; in this way, all of the rows must be different (yes, this stresses the importance of having at least one KEY declared, and when there are two or more KEYs one should be defined as PRIMARY based on pragmatic reasons, while the rest can be deemed as ALTERNATE; but yes, before making the decision, each of the KEYs was a “candidate” for a definition as PRIMARY).

Having a table that in actual fact represents a relation is critical as, when it undergoes manipulation operations of a relational kind, then the result is, again, a table that represents a relation. In this manner, the behavior of said table is predictable.
Atomic domains (columns)
In the first sections of the RM, Dr. Codd presents several samples of relations in order to introduce some concepts; so, in order to comprehend the meaning of atomic domain, let us start with the following excerpt from the RM that details some pertinent points:

So far, we have discussed examples of relations which are defined on simple domains—domains whose elements are atomic (nondecomposable) values. Nonatomic values can be discussed within the relational framework. Thus, some domains may have relations as elements. These relations may, in turn, be defined on nonsimple domains, and so on.

In this way, one can say that each of the aforementioned expository relations fits in one of two kinds, say either kind A or kind B:

Kind A groups only relations (tables) that are structured with domains (columns) that contain exclusively simple values in every one of their tuples (rows), i.e., such domains (columns) do not contain relations (tables) as values, which in this context means that the values are atomic because they cannot be decomposed successively into new relations (tables). Hence, the relations of this class are the ones that are normalized, i.e., they comply with 1NF, their form is desirable.

Kind B is exclusively integrated by relations (tables) that have one or more domains (columns) that hold relations as values in each respective tuple (row), and that signifies that said values are nonatomic since they can be subsequently broken down into new relations (tables), i.e., they are decomposable. Thus, the relations of this sort are unnormalized, i.e., they infringe 1NF, they are in an undesirable form.

Normalization
Dr. Codd introduces the section about normalization in the RM with the following paragraph:

A relation whose domains are all simple can be represented in storage by a two-dimensional column-homogeneous array of the kind discussed above. Some more complicated data structure is necessary for a relation with one or more nonsimple domains. For this reason (and others to be cited below) the possibility of eliminating nonsimple domains appears worth investigating! There is, in fact, a very simple elimination procedure, which we shall call normalization.

Then he goes on to show:

A group of relations where one is unnormalized (it has domains that contain relations as values, i.e., they are nonatomic; i.e., they are non-simple)

A group of relations that are a normalized (i.e., one that was decomposed; i.e., one which relation valued domains were broken down into simple ones which signifies that they are atomic)

And then he describes the procedure for obtaining normalized relations from unnormalized ones.
In this respect, the relations he employed to illustrate a normalization exercise and the exercise description itself are quite clear, and I recommend again that you analyze them yourself (and I as well hope this encourages some readers to engage with the text).
Sucessively, he indicates:

Further operations of a normalizing kind are possible. These are not discussed in this paper.

And said operations, i.e., second and third normal form (2NF and 3NF) are actually detailed in “Further Normalization of the Data Base Relational Model”, and as mentioned above, after the presentation (and the later printing and publication) of this paper, the original normal form became known as first normal form.
As a practitioner can observe, having unnormalized relations (tables) introduces (almost always unnecessary) convolution into RDB implementations.
A relation that satisfies 1NF, eases the definition of constraints and data manipulation operations which can be implemented by means of a data sublanguage that is less complex than that required for unnormalized relations (tables), as Dr. Codd points out in the following lines:

The adoption of a relational model of data, as described above, permits the development of a universal data sublanguage based on an applied predicate calculus. A first-order predicate calculus suffices if the collection of relations is in normal form. Such a language would provide a yardstick of linguistic power for all other proposed data languages, and would itself be a strong candidate for embedding (with appropriate syntactic modification) in a variety of host languages (programming, command- or problem- oriented). […]
[…]
The universality of the data sublanguage lies in its descriptive ability (not its computing ability).

The bewilderment
From my point of view, the bewilderment has arisen, due to (a) the aforementioned excess of interpretations, explanations, etc., about 1NF and the RM itself, and because of (b) further attempts to redefine 1NF that state that having relations with domains that hold values that are, in turn, relations comply with 1NF as long as they are one single value for each corresponding tuple.
My take on your other pointss

There should not be any relationship between rows, other than that they conform to the same headers.

I am not sure if I understand the intention of that statement correctly but, apart from conforming to the same headers, there must be a connection between the (tuples) rows of a relation (table) as each of them should be an assertion about a particular occurrence of the specific entity type (defined in terms of the business context of interest) that the relation (table) is supposed to represent.

There should also be no relationship between columns, but I believe that is the subject of higher normal forms.

I do not know if I am properly interpreting the meaning of that statement either but, in fact, and in accordance with my response to the previous aspect, there must be a relationship between the domains (columns) of a relation (table) as well, which is precisely why it is a relation (the essential structure of the relational model and of a concrete RDB implementation).
To exemplify, with regard to the hypothetical relation (table)

Salary (PersonNumber, EffectiveDate, Amount)

the tuple (row)

Salary (x, y, z)

would convey the meaning

The Salary payed to the Person identified by PersonNumber x, on EffectiveDate y corresponds to the Amount of z

Therefore, each tuple (row) of the Salary relation (table) must fit into the structure of the assertion shown above, and the difference would be the replacement of the pertinent domain (column) values, but there must exist a relationship between (a) all the Salary domains (columns) and also between (b) all their corresponding values with respect to each tuple (row); such a relationship it is indispensable.
Higher normal forms (2NF and 3NF) are useful to get rid of functional dependencies between domains (columns) of a relation (table), they assist in avoiding undesirable connections between domains (columns), as said undesirable connections allow the introduction of update anomalies. Both 2NF and 3NF are helpful to test the soundness of the structure of the relations (tables) in a certain RDB implementation.


Process to change collation on a database I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.

So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly.  So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:

Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
 foreach-object
    { Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }

Is this a valid approach to changing collation on everything?  At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).

Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.

Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation

Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.

Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:

Collation error in Jira when the database and server/default collation do not match in SQL Server

Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:

  The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.

whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement  ;-).

My advice: don't change anything! You even said:

  a database that has been in production for a few years

If it has been in production for years without running into problems, then there are likely no problems :-)


Running Total of a count per day in SQLite I usually write a couple of very simple queries on a redshift database which used in a marketing visualization tool.  And I repeat - VERY simple - I am a novice at this.

I am now faced with a new data source (SQLite) and I'm stuck.

I have a table with :

order_id
order_date

where there are several orders per day.  And I am trying to get a running total of the daily count of order ids.

For example the table would contain :

order_id      order_date
2541          2017-06-05
26777         2017-06-05
123           2017-06-06
8795          2017-06-07

And I'm trying to get to this result :

Day          RunningTotal
2017-06-05 : 2
2017-06-06 : 3  (the 2 of the previous day + 1 this day)
2017-06-07 : 4

In POSTGRESQL i would use

SELECT
order_date,
SUM (COUNT(order_id)) OVER (ORDER BY order_date rows between unbounded preceding and current row) as RunningTotal
FROM table
ORDER BY
order_date
GROUP BY
order_date

How do I do this in SQLite?

I've googled - and see many examples of either SUM or COUNT but seldom combined and never to achieve a running total of a count.

For anyone trying something similar : this is what worked in the end :

select
a.ORDER_DATE,
sum(b.ID_COUNT) RunningTotal
from
(select
ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT
from orders
group by ORDER_DATE
order by ORDER_DATE) a,
(select
ORDER_DATE, Count(DISTINCT(SOFTWARE_ID)) ID_COUNT
from orders
group by ORDER_DATE
order by ORDER_DATE) b
where a.ORDER_DATE >= b.ORDER_DATE
group by a.ORDER_DATE
order by a.ORDER_DATE;


solution for non-technical user uploads to SQL Server DB? I need a way to get subjective call QA scoring into our business intelligence system.

It currently lives in Excel. The problem with a direct upload from Excel to the DB is that there is no validation or response from the db necessarily (although I guess I can program these in).

Is there a solution that already exists that allows a non-technical user to import data, and already have it validated against the database?

There are myriad data entry software solutions out there, but as far as I know, actually receiving input from sql server (These are the valid values) is something that's not very common.

MS Access is a possible solution - however, for some reason, their data entry forms do not allow for a bulk copy/ paste, which would slow down end users. You have to enter one item at a time.

There are lots of tools and methods available for loading data. But the easier you want this to be for end users the more complex and sophisticated your dataloading routine will become.

Data validation and business rules need to be defined and implemented. Somebody has to do it. No software is smart enough to do that "auto-magically".

You could build data checking into the spreadsheet. Hopefully this would stop errors before you load. It is possible to import data directly from a db query and there is a variety of lookup and validation tools in excel.

You could build an import routine in ssis  and apply validations, filters, lookups and substitutions as part of the import process. It can even handle errors and redirect invalid records back to the user. But this takes time and effort.

My preferred approach would be to use a db to begin with rather than excel. That way datatypes, dependencies, rules and constraints can be enforced from the start. You could develop an interface in MS-access.

Unfortunately all of these things take time. The more sophisticated you want the app to be more time it will take to develop.


SQL Server 2019 (RTM) - 15.0.2000.5 period has expired after upgrade to CU 8 Hi i have MS SQL with version
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

When i use this version and performed Log shipping, there is an error and i check on microsoft doc, the error because bugs in sql and i need to upgrade the sql server with Cumulative Update that i download in here https://www.catalog.update.microsoft.com/Search.aspx?q=sql%20server%202019
i use the latest CU. at first everything was fine until about 60 days i get error that says SQL SERVER Evaluation period has expired. And this happened in 3 servers (Production, DR, UAT). after several weeks after upgrade to CU8, got period has expired.
is it normal? because when doing installation, what i do is next and next

In the version output you provided it states that you're running the Enterprise Evaluation Edition. This edition can only be used for a short period (I believe 180 days) before it expires and cannot be used. It can also never be used for production workload as per the license agreement.
For Production/DR
If you have previously purchased SQL Server licenses, you need to download your product key from the licensing portal and then follow these instructions to apply the product key to your instance of SQL Server.
If you have not purchased licenses, speak to your licensing reseller to obtain licenses, then follow the instructions to update your license key.
NOTE: You can upgrade your evaluation license to Enterprise, Standard or Web Edition of SQL Server depending on your requirements and available licenses.
For UAT
Follow these instructions for upgrading your edition, but instead of applying a license key, select Developer Edition, which is free for non-production workloads.


Custom database alert - Can it be done? I have this neat little query which let me know any ongoing query that stuck running for more than 3 minutes.

SELECT
sqltext.TEXT,
s.session_id, r.status,
r.total_elapsed_time, r.cpu_time, r.wait_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID AND r.total_elapsed_time > 300000000

I came across this article that show how to set up SQL agent alert and send as notification.  Can I combine with my query so it sends alert email when I have query running longer than 3 minutes?

One easy way to do this is to create a SQL Agent job with a step that runs the following code:

<Your SELECT statement>
IF (@@ROWCOUNT > 0)
    BEGIN
        RAISERROR('Long-running query detected!', 11, 1 )
    END

Configure notification on the job to send an email on failure, and if any rows are returned by the query, you'll get an email that the job failed.

Instead of setting up the job failure notification, you could set up a SQL Agent alert on the error that is raised as in the article you referenced.  But if the job notification is adequate it's less work to set up.


Can't create table in database with db_owner role (SQL Server 2012) I have database in SQL Server 2012 SP2 Enterprise. I created new SQL Login and mapped to X database with db_owner role. Now I can't create table on this database. When I run simple create table script I got below error.

  Msg 262, Level 14, State 1, Line 1
  CREATE TABLE permission denied in database 'INVENTORY_TEST'.

There is no database or server trigger. I did not find any SQL Server bug related to this problem. Any idea?

The problem solved. In the Database options - Permissions tab public Database role was added and it was Deny permission for Create database option. After I removed Deny permissions it is OK now. User can create table.


Which TLS protocol and ciphers are supported by SQL Server 2005? What is the latest TLS protocol (TLS 1.0 or TLS 1.1) to be used in SQL Server 2005? Also what ciphers are supported?

(Note: extended support for SQL Server 2005 ended on April 12, 2016.)
From this Microsoft support page:

Will SQL Server 2005 be supported for TLS 1.2?
TLS 1.2 support is offered only for SQL Server 2008 and later versions.

From this SQL Protocols blog entry, for SQL 2000 and 2005:

The currently recognised protocols are, from highest to lowest: TLS1.1, TLS1.0, SSL3.0, SSL2.0.

and

[The] cipher suite to use is negotiated by SSL/TLS and depends upon the cipher suites supported by the OS on the client and the server.


Why would a query in code be slower than a query in a SQL tool When we face performance issues with out application, one of the first things we do is try to isolate if the problem is the code, the queries or the database itself. The most frustrating part of this is that the same query, executed in code or executed in a SQL tool such as the Query Analyzer of SQL Server Management Studio, can have radically different timings.

We are currently facing an issue where the same query executed in the code takes (on average) 15-20 seconds. The same query on one of our development laptops (against the same database-server) will take the same amount of time, whereas another development laptop will run the query in under 3 seconds.

I'm looking for reasons why the same query, run against the same database at the same time of day, would have such radically different timings depending on where it's run from (and why a previously good performing query would suddenly turn into a slower-than-a-snail-riding-a-tortoise monstrosity without any changes to the database). Does a tool such as SQL Server Management Studio execute queries differently as opposed to our code?

Different execution plans.

When you run the query from different environments many things must be the same in order to reuse the cached query plan.

Including the query text for adhoc queries, various SET options and the default schema of the logged in user if the query relies on implicit name resolution.

This is usually a sign of a parameter sniffing issue where the optimum plan generated can vary according to the value of the parameters passed in. When you run the query in SSMS if it can not reuse the cached plan the application is using you get a new plan compiled that is suited for the parameter values under test.

See Slow in the Application, Fast in SSMS? Understanding Performance Mysteries for more.


PostgreSQL warm standby: sync WAL files I'm new to PostgreSQL (using ver 8.4.20) and need to setup a master and standby SQL server setup.  I am planning to use warm standby setup.  I have read the docs on PG and understand the basics, but a few things aren't clear:

If I use log shipping, what program is responsible for moving the logs from the master to the standby? Do I need to setup an rsync job, or does PG take care of this automatically?
If I wanted to keep control of this synchronizing (by using rsync), could I disable PG's synchronization (if such a thing exists)
Assuming I need to setup an rsync job, do I sync the archive directory?  or the main PG log directory?
Is it possible to combine log shipping (to cover longer outages) and streaming/SR (for record level synchronization)?  Is that a desirable thing?
Is it possible to script something on the standby which tells me if it cannot sync with the master because too many old archive files are missing).  So that I can send myself an alert, or run some other script.

If you use log shipping, then the archive_command on the master and the restore_command on the replica are responsible for copying the logs. The contents of those commands is up to you.  You can use scp, rsync, tar, ssh, or just plain cp command to and from a shared network folder; or any number of other things.  It is quite easy to come up with commands that generally work but fail in some edge cases, so you might want to use canned solutions that do it for you, like pg_barman or pgbackrest, as they are more likely to get the edge cases correct.

I don't know what your second question means.

For the 3rd, your archive directory generally shouldn't be on the same storage system as your database directory is, otherwise you have a single point of failure, which rather defeats the purpose.  So your rsync command should go inside archive_command, so that each file is sent to where ever it is you want it to go as soon as it is ready for archival. If you do want your archive_command to copy the data to a local archive, than your hand-rolled rsync should definitely be rsyncing from that local archive directory, not pg_xlog.

It is possible to combine both streaming and log shipping.  This is really useful if you also want to be able to do PITR (point-in-time-recovery), as then you need the WAL log archive anyway.  If you are not interested in PITR, then you should probably just use streaming replication, with replication slots to cover long outages.


SQL Server - Standard Deviation in a Windowed Query SQL Server provides the STDEV function, but it cannot be used in conjunction with window functions.

For example

SELECT
     TestName
    ,[BatchSize]
    ,[NumIterations] = COUNT(*)
    ,[Min] = MIN(RuntimeMs)
    ,[Average] = AVG(RuntimeMs)
    ,[Max] = MAX(RuntimeMs)
    ,[StdDev] = STDEV(RuntimeMs)
FROM #TimingResult
GROUP BY TestName, [BatchSize]

How can I compute percentiles and standard deviation at the same time?

I found a solution here. Essentially, you have to compute it using statistics:

SELECT DISTINCT
    TestName
    ,[BatchSize]
    ,[Iterations] = COUNT(*) OVER(PARTITION BY TestName, [BatchSize])
    ,[Min] = PERCENTILE_DISC(0) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[5th] = PERCENTILE_DISC(0.05) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[10th] = PERCENTILE_DISC(0.1) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[25th] = PERCENTILE_DISC(0.25) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[Avg] = PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[75th] = PERCENTILE_DISC(0.75) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[90th] = PERCENTILE_DISC(0.9) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[95th] = PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[Max] = PERCENTILE_DISC(1) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[StdDev] = SQRT((
        (COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize])
        * SUM(RuntimeMs*RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]))
        - POWER(SUM(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]), 2))
        / (COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize])
        * COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]) - 1))
FROM #TimingResult

P.S. Feel free to use this statement when performance testing code. That's why I wrote it (in particular for this question).


Why does PostgreSQL FDW talk to multiple backend servers for this point query? I've set up a PostgreSQL FDW server with the following table, sharded by user_id over four servers:

CREATE TABLE my_big_table
(
    user_id bigint NOT NULL,
    serial bigint NOT NULL,         -- external, incrementing only
    some_object_id bigint NOT NULL,
    timestamp_ns bigint NOT NULL,
    object_type smallint NOT NULL,
    other_type smallint NOT NULL,
    data bytea
) PARTITION BY LIST (mod(user_id, 4)) ;

CREATE SERVER shardA
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.11', port '5432', dbname 'postgres', fetch_size '10000');
 .
 .
 .
CREATE SERVER shardD
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.200.14', port '5432', dbname 'postgres', fetch_size '10000');

create foreign table my_big_table_mod4_s0 partition of my_big_table
    for values in (0) server shardA
    OPTIONS (table_name 'my_big_table_mod4_s0');
 .
 .
 .
create foreign table my_big_table_mod4_s3 partition of my_big_table
    for values in (3) server shardD
    OPTIONS (table_name 'my_big_table_mod4_s3');

Given a query for a single user_id, I was hoping for FDW to select a single backend based on simple partition pruning, but explain shows a foreign table scan against all four servers... How can I hint FDW to be smarter?

PostgreSQL lacks the insight into the "mod" function that it would need to declare that user_id=97 also implies that mod(user_id,4)=1.  If you provide that insight manually, it would likely honor it:

WHERE user_id=$1 and mod(user_id,4)=mod($1,4)

This has nothing to do with FDW.  If all partitions/tables were local, the answer would remain the same.

You could use hash partitioning, then it would generate the insight automatically.


Snapshot and RCSI.When does SQL Server go to VersionStore? When SQL Server reads data with SNAPSHOT or RCSI on, how does it know it has to look a record up in the version store vs the table?

Let’s pretend session A modifies a few records of a table and escalates the row-level locks to a page-level one. At the same time another session B tries to read that page. It sees the page being under X lock but what happens afterwards? How does session B know which records on that page are modified and therefore in the version store, and which are not? 14 bytes pointers? But, to me, all the rows on the page can have them: some pointers can be relevant, some can be just outdated.

Thanks!

Referencing the documentation about Understanding Row Versioning-Based Isolation Levels, I've extracted what I think are the important take-aways with regards to your question, but I'd recommend reading the entire post for additional valuable information.

  When either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION
  database option is set ON, the SQL Server Database Engine assigns a
  transaction sequence number (XSN) to each transaction that manipulates
  data using row versioning. Transactions start at the time a BEGIN
  TRANSACTION statement is executed. However, the transaction sequence
  number starts with the first read or write operation after the BEGIN
  TRANSACTION statement. The transaction sequence number is incremented
  by one each time it is assigned.

  When either the READ_COMMITTED_SNAPSHOT or
  ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies
  (versions) are maintained for all data modifications performed in the
  database. Every time a row is modified by a specific transaction, the
  instance of the Database Engine stores a version of the previously
  committed image of the row in tempdb. Each version is marked with
  the transaction sequence number of the transaction that made the
  change. The versions of modified rows are chained using a link list.
  The newest row value is always stored in the current database and
  chained to the versioned rows stored in tempdb.

  Row versions are held long enough to satisfy the requirements of
  transactions running under row versioning-based isolation levels. The
  Database Engine tracks the earliest useful transaction sequence number
  and periodically deletes all row versions stamped with transaction
  sequence numbers that are lower than the earliest useful sequence
  number.

  Behavior When Reading Data

  When a transaction using the snapshot isolation level starts, the
  instance of the Database Engine records all of the currently active
  transactions. When the snapshot transaction reads a row that has a
  version chain, the Database Engine follows the chain and retrieves the
  row where the transaction sequence number is:

  Closest to but lower than the sequence number of the snapshot    transaction reading the row.
  Not in the list of the transactions active when the snapshot    transaction started.

  Read operations performed by a snapshot transaction retrieve the last
  version of each row that had been committed at the time the snapshot
  transaction started. This provides a transactionally consistent
  snapshot of the data as it existed at the start of the transaction.

  Read-committed transactions using row versioning operate in much the
  same way. The difference is that the read-committed transaction does
  not use its own transaction sequence number when choosing row
  versions. Each time a statement is started, the read-committed
  transaction reads the latest transaction sequence number issued for
  that instance of the Database Engine. This is the transaction sequence
  number used to select the correct row versions for that statement.
  This allows read-committed transactions to see a snapshot of the data
  as it exists at the start of each statement.


Slow select Queries on Log shipping standby server We have a database in primary DC around 15 TB with partitioned tables with largest one around 7 TB.

To avoid users from reporting a LS set up was done for above database in different DC where the LS restore job runs once nightly.

on Primay- update stats and update stats AYNC are both set to True and hence reflected the same on secondary. I am not sure why someone kept both enabled and is that how it should be?

Now on secondary, i see lots of blocking occurring when a SELECT query runs from users results in select statman statement for auto update stats for that read only database.

  I am not able to get why there would be one when auto update async is
  enabled?

  Also, will the update stats done on primary (once a week) be restored during
  LS restore? if not, whats the better way to run update stats on
  secondary database involved in LS standby mode, may be to run daily for better select performance out there?

Please advise

Statistics in read only databases.

TL;DR

In Read only databases, regular permanent statistics can be leveraged to satisfy query plans. If sql server needs new statistics for the read only db  or these permanent statistics in the database have become stale, then temporary statistics can be created / updated. These statistics reside in TempDB and are managed by SQL Server (you can only drop them).

There are two kinds of temporary statistics, these that get created due to missing statistics and those that get 'updated'.

The creation of temporary statistics can be removed by adding the statistics manually on the primary database, or generating the estimated execution plans of the reporting queries (also on the primary instance/db). More on that further in this answer.

Permanent statistics can be updated and 'transformed' to temporary statistics on a read only database.

The temporary stat updates can be resolved by either updating your statistics on the primary db more often or disabling the auto stat updates on the read only db after log restores are done.

To disable the auto update stats you could execute this on the read only database: ALTER DATABASE [Database] SET AUTO_UPDATE_STATISTICS OFF; This will stop the updates of temporary statistics on this Read only database.

Another important part regarding Restores to a standby database & temporary statistics is that when applying log backups, the temporary statistics get  updated again, even though they still  exist in sys.stats.

In your case explaining the daily issues with Statman queries (recalculating the temp statistics each day if they need to be created / updated).

Norecovery --> Standby --> Norecovery ... removes temporary statistics

Another interesting part regarding temporary statistics is that they will be gone when the db state changes to restoring using RESTORE DATABASE ... WITH NORECOVERY.

use MASTER
GO
RESTORE DATABASE [ReadOnly2] with NORECOVERY
RESTORE DATABASE [ReadOnly2] WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

Effectively flushing the temporary statistics on all objects

SELECT * From sys.stats where is_temporary = 1;

And recalculating the same 2 stats between each state change + test query running.

Both of these permanent statistics show up in the xml of our execution plan

   <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[IX_Bla_indexedval]" ModificationCount="12000000" SamplingPercent="15.8812" LastUpdate="2019-06-12T10:52:32.25" />
      <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[PK__Bla__3214EC075017BD54]" ModificationCount="12000000" SamplingPercent="15.2345" LastUpdate="2019-06-12T10:52:35.34" />

With the modificationcount, samplingpercent & lastupdate changed after executing the query again with the temporary statistics 'updated'.

ModificationCount="0" SamplingPercent="5.71018" LastUpdate="2019-06-13T11:32:36.5"

Creation of temporary statistics

Regular, non temporary statistics do not get updated / you cannot update (not even temporary) statistics  on read only databases.

What you are seeing on the reporting instance is the creation / 'updating' of temporary statistics.

These statistics reside in TempDB & SQL Server Creates & Updates them.

Replicating the behaviour

I was able to replicate the behaviour of temporary statistics creation on a table with 100M rows in a read only database (Addendum #1)

With the troublesome StatMan Queries you mentioned.

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [NonIndexedVal] AS [SC0] FROM [dbo].[Bla] TABLESAMPLE SYSTEM (7.707678e-001 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

Maxdop 16 (max cores) due to me having MAXDOP set as 0 on my test machine, YMMV

Restarting the instance

When I restart the instance I see the same behaviour, showing that these are in fact, temporary statistics. Some Q&A on temporary statistics can be found here.

Now what we are seeing is the creation of temporary statistics and they get created before the query runs.

For big tables, they do note that sample rate is still applied to temporary statistics.

  Other point to note is that statistics created as part of auto-stats
  use data sampling so the creation of these statistics is fast and does
  not depend on the size of the table

Source

How could you resolve the creation of the statistics when sql server restarts / the database is restored?

If it is possible to do so, you could script them out and create them on the main 'primary' database.

Finding the temporary statistics

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE is_temporary = 1;

Scripting the statistics

Without T-SQL
Or by using the T-SQL answer on the question How to script statistics in Sql Server? (using T-SQL) By Martin Smith

Which could solve your main issue with the statistics being created time and time again.

Another idea if the issue is due to temp stats being created & you cannot script them out correctly, could be creating the estimated execution plans for the reporting queries on the primary database. This should create the statistics needed when auto create statistics = on.

Temporary statistic updates

The other problem that could arise are stale permanent statistics.
As noted in this aforementioned blog, stale permanent statistics can be updated and set to is_temporary=1.

This means that permanent statistics on a read only database can become temporary statistics until the instance is restarted. When you update the statistics on the primary it should get carried over to the secondary when the log is applied.

Asynchronous temporary statistic updates

We see the asynchronous statistic updates also working for these temporary statistics!

After running addendum #1, we run the next snippet:

   USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF

USE [ReadOnly]
GO
INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(10000000) --10M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

The above SELECT runs instantly.

After running the query, the aftermath is shown.

The temp statistic updates run after query execution.

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [Indexedval] AS [SC0] FROM [dbo].[Bla] WITH (READUNCOMMITTED)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

Disabling auto update statistics on read only databases

You could disable the updating of permanent statistics to temporary statistics by running the next statement, you can change this setting on a read only database and it will still work.

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF;

Solution for the temporary stat upgrades.

  Also, will the update stats done on primary (once a week) be restored
  during LS restore?

Updating your stats will be brought over to the secondary, updating them more where possible will result in less stale statistics and less stale permanent statistics being converted to temporary statistics.

Looking into both of these would be my go to in tackling this problem.

Disabling temporary statistics by using a traceflag

When massively enabling traceflags to help a colleague and digging deeper between 2 and 3 thousand, it appears that traceflag 2362 can be used to disable temporary statistics.

You can enable them like this:

DBCC TRACEON(2362,-1);

And all newly temp stats will not be created. Existing temporary statistics will remain until they are removed. For example by setting the db offline and online again.

Applying log backups to a standby database & Temporary statistics

Addendum 2

When running the queries in addendum #2, and applying log backups to the standby database the stats get updated after each restore.

Even after applying an 'empty' log backup.

In addendum 2, between each log backup restore the following query runs:

SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

These trigger temp stat updates each time.

Proof

All this means that applying logs at night will make the temp stat updates run each day, without a restart of the instance.

Resolving this issue

set auto update stats off on the standby db after applying logs. ALTER DATABASE [ReadOnly2] SET AUTO_UPDATE_STATISTICS OFF;
Run your reporting queries when the restores finish to create the temp stats
Update the statistics on your primary database more often before
applying the logs.
Enabling traceflag 2362

When restoring a log backup they still exist:

SELECT name, is_temporary From sys.stats where is_temporary = 1;

name    is_temporary
PK__Bla__3214EC075017BD54   1
IX_Bla_indexedval   1

But they do get recalculated when rerunning the query.

Addendum #1 (Table with 100M records in a read only db.)

CREATE DATABASE [ReadOnly]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'ReadOnly', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'ReadOnly_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [ReadOnly] SET COMPATIBILITY_LEVEL = 140
ALTER DATABASE [ReadOnly] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE [ReadOnly] SET  READ_WRITE
ALTER DATABASE [ReadOnly] SET RECOVERY SIMPLE
ALTER DATABASE [ReadOnly] SET  MULTI_USER
ALTER DATABASE [ReadOnly] SET PAGE_VERIFY CHECKSUM

USE [ReadOnly]
GO
CREATE TABLE dbo.Bla(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, Indexedval INT,NonIndexedVal INT);
CREATE INDEX IX_Bla_indexedval on dbo.Bla(Indexedval);

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(10000000) --10M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;
GO 10

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;

USE [ReadOnly]
GO
SELECT NonIndexedVal
FROM dbo.Bla
WHERE  NonIndexedVal = 999999;

#Addendum 2

ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET RECOVERY FULL

BACKUP DATABASE [ReadOnly] to disk = 'D:\temp\ReadOnly.bak'
WITH COMPRESSION, STATS=5

RESTORE FILELISTONLY FROM DISK = 'D:\temp\ReadOnly.bak'

RESTORE DATABASE [ReadOnly2] FROM disk = 'D:\temp\ReadOnly.bak'
WITH MOVE  'ReadOnly' to 'D:\temp\ReadOnly2.mdf'
,MOVE 'ReadOnly_log' to 'F:\temp\ReadOnly_log2.ldf'
, STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
GO
ALTER DATABASE [ReadOnly2] SET AUTO_UPDATE_STATISTICS ON

SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

USE [ReadOnly]

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(2000000) --2M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;

USE MASTER
GO
BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog.trn'
WITH COMPRESSION, STATS=5

RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog2.trn'
WITH COMPRESSION, STATS=5

RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog2.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);
SELECT * From sys.stats where is_temporary = 1


Oracle OS Authentication only works on certain databases/clients We currently face a problem where Oracle OS authentication only works on certain client/server combinations.

The environment consists of a development client with its own database server (excluded for this example), an "integration" client with its own database server (for integration tests) and a "reference" client with its own database server (we keep these identical to our customer client/server).

All servers are Windows 2003 running an Oracle 10.2.0.3 database, all clients are Windows XP running an Oracle 10.2.0.1 client. The development client is physical, everything else is virtualized on the same virtual host. All of the machines use the same network domain.

The same user (from an Active Directory) is used on each machine for tests, and both of the databases have an externally identified user created like

create user "OPS$DOMAIN\USERNAME" identified externally;
grant connect to "OPS$DOMAIN\USERNAME";

Now when trying to connect to a database using

connect /@TNS_NAME

our reference database (which still worked four months ago) declines connections with

  ORA-01017: Invalid username/password. Logon denied.

These connections work on the integration database when connecting from the development and integration client (or the integration server itself), but not from the reference client.
Note that all of the clients can connect to both of the servers using e.g. schema owner + password.

The following picture displays the current state of working and failing OS authenticated connections:

Further information:

os_authent_prefix is set to "OPS$".
os_remote_authent is set to FALSE on both servers, but as connections do work for one server, this should not be the issue.
externally identified users are written in all caps.
SQLNET.AUTHENTICATION_SERVICES is set to  (NTS) on both servers

What could be the problem here? I would suspect a configuration issue for the reference database, but then why can't the reference client connect to the integration database using OS authentication? (Note that regular connections work for that case).
Are there maybe two issues?

PS: I know OS Authentication is kind of insecure, but this is not an issue here.

Fixed the problem:

The source of the problem was that the broken clients and server were referencing a central TNSNames.ora file on a different file server. This was intended; however, I did not know that this also makes those machines search the sqlnet.ora there.
Someone - or some installer or something - placed an empty sqlnet.ora file in that central location. I haven't tried if the clients only ignore their local files if there is a central one, but in this case any changes to the local sqlnet.ora files had no effect.

Adding the line

SQLNET.AUTHENTICATION_SERVICES = (NTS)

to that central sqlnet.ora file obviously fixed it.


I can't install db-mysql in node.js When I execute this command: npm install db-mysql, I get the following result.  What's the solution to this problem?

C:\Users\Mouad>npm install db-mysql
npm http GET https: //registry.npmjs.org/db-mysql
npm http GET https: //registry.npmjs.org/db-mysql
npm http GET https: //registry.npmjs.org/db-mysql
npm ERR! Error: SELF_SIGNED_CERT_IN_CHAIN
npm ERR!     at SecurePair.<anonymous> (tls.js:1283:32)
npm ERR!     at SecurePair.EventEmitter.emit (events.js:92:17)
npm ERR!     at SecurePair.maybeInitFinished (tls.js:896:10)
npm ERR!     at CleartextStream.read [as _read] (tls.js:430:15)
npm ERR!     at CleartextStream.Readable.read (_stream_readable.js:294:10)
npm ERR!     at EncryptedStream.write [as _write] (tls.js:344:25)
npm ERR!     at doWrite (_stream_writable.js:211:10)
npm ERR!     at writeOrBuffer (_stream_writable.js:201:5)
npm ERR!     at EncryptedStream.Writable.write (_stream_writable.js:172:11)
npm ERR!     at write (_stream_readable.js:547:24)
npm ERR! If you need help, you may report this log at:
npm ERR!     <http: //github.com/isaacs/npm/issues>
npm ERR! or email it to:
npm ERR!     "npm-@googlegroups.com"

npm ERR! System Windows_NT 6.1.7601
npm ERR! command "C:\\Program Files\\nodejs\\\\node.exe" "C:\\Program Files\\nodejs\\node_modules\\npm\\bin\\npm-> cli.js" "install" "db-mysql"
npm ERR! cwd C:\Users\Mouad
npm ERR! node -v v0.10.4
npm ERR! npm -v 1.2.18
npm ERR!
npm ERR! Additional logging details can be found in:
npm ERR!     C:\Users\Mouad\npm-debug.log
npm ERR! not ok code 0

Either change config to allow SELF_SIGNED_CERT

npm config set strict-ssl false

or change registry url to http:// one

npm config set registry="http://registry.npmjs.org/"


Professor told us to store serialized Java objects as blobs instead of defining relational tables Instead of actually defining a tables with the correct attributes, my professor told us we could map objects to ids like this:

id (int)  |   Serialized Object (blob)
   1               10010110110

I can see so many problems with this; data redundancy, having to track ids separately, having to pull the whole table in to memory to search for anything, and **if I want to change my model in the Java code I will no longer be able to deserialize the blob stored in the database into that model.

Either I am forever stuck with that model or I have to do some other really ugly stuff to change my model.** This whole things seems like bad form to me. Am I justified in disagreeing with my professor? Is there some benefit to doing this that I have not thought of? If I'm correct should I say something to my professor about this? He was preaching this to my whole class and even said that he has built projects that way. A second opinion would be great.

The course is named Software Design.

My professor did not say that this was the best way, but he did say that it was a legitimate alternative to defining relational tables.

The model is not dynamic in any way.

It is not, in itself, a bad thing - at all. Arguing about "which is better" without a proper context (=exact requirements) is an exercise in futility.
The part in bold is wrong. You can easily extend objects already serialized to add new fields and achieve full binary compatibility with the older objects. You can also simply create new classes instead of changing the original ones.

Your discussion with the professor should focus on pros and cons of "relational" versus "key-value store" in different scenarios, not on abstract "betterness". Or you could as well have a discussion on whether Christmas is superior to Thanksgiving.

-- an edit, after reading other answers.

One of the other answers goes as far as to state that that "it's hard to imagine a case where pros outweigh the cons".

Because the whole discussion must be about concrete problems (otherwise we can't even define "better" and "worse"), let me give you one concrete example. It's completely made up, but I tried to flesh out as many details as possible.

Imagine you have an online gaming site, with a database that stores statistics of players in different online games (played in-browser, written in GWT and cross-compiled to javascript). Some of the games are strategic, some are action games, some are platformers. The database is relational and stores players and history of plays and the score.

One day you get an additional requirement: let the players save the game state to the cloud, during the game, so they can restart the game later, at the same point. Needless to say, the only reason to store this temporary state is to return to the game, the state itself will never be introspected.

Now you have two basic choices:

since the games are written in Java, you can quite easily take the model, send it to the server, serialize it in one line of code and store as a blob. The table will be called "saved_games" and it will have foreign keys to the player and so on. From the point of view of the database a "save game" is an opaque, indivisible blob.
you can create a separate relational model for each of your 100 games
(this will be tens of tables per game). For pacman alone, for example, you will have to have a table storing positions of all the uneaten pellets, bonuses, positions and current state of ghosts. If someone, someday, modifies the game, even slightly, you will have to update the relational model. Also, for each type of game, you will have to implement a logic to write the Java model to the database, and to read it back.

The answer by Justin Cave says, that you should go with the second option. I think this  would be a huge mistake.

Also, I have a hunch that Justin Cave's perception is that what I presented above is an "edge" or "rare" case. I believe that unless he can present some sort of hard data (based on a representative sampling of all the IT projects in the world, not just, say, enterprise applications in the US), I will consider such opinion a classic case of a projection bias.

Actually, the problem of serialized Java objects in a relational database is far deeper than it seems. It touches the very core of the 1NF, namely  what is the domain of an attribute?.  If you are really interested in the topic, there's a great article by C. J. Date, in his Date on Database: Writings 2000-2006.


Blocked access to batch file I am unable to execute batch files to copy backups from SQL Server 2008 R2 hosted on Windows 2008 R2 to a file share.

Error:

  Access denied

SQL Server Agent service has full control on the soure and destination folders.

This only happens in Windows Server 2008 R2 on my production environment, suspecting additional security controls on Windows Server 2008 causing this issue.

If you are running the batch file through xp_cmdshell you are using a different set of permissions.  Here is the BOL entry for xp_cmdshell.

Basically though if you are running it as a sysadmin then xp_cmdshell uses the service account for SQL Server, not the service account for the Agent.  If you are not running it as a sysadmin then it uses a proxy account.  The proxy account is stored in credential called ##xp_cmdshell_proxy_account##.


Why are NoSQL databases not ACID compliant? Not having ACID properties means that the database works well on clusters. But ACID is something very fundamental. How can a database work well if there is no atomicity, consistency, isolation and durability (ACID)?

How can a database work well if there is no atomicity, consistency, isolation and durability?

They can't. This is why those features were created.

Sometimes you can work around the lack of these features because you need to scale horizontally or scaling becomes your first and primary concern. You may not need need consistency now, you may be able to handle it later.. You may not need all the data returned in order: you may want the queries executed in parallel over clusters. Or your data may itself be fairly un-valuable.

Look at major products that implement these types of systems though -- generally, they suck and they have lots of errors. Ever post something to your wall on Facebook just to see it disappear and reappear multiple times? Or to have a conversation you're commenting on vanish for an extended period of time and come back? That's hundreds of people working to manage data using "NoSQL" and compiling PHP to C++. It doesn't especially work well. It works, and for most companies that's further than they can get developing an alternative.


How to add another table to publisher and subscriber without reinitializing the subscriber? Is there chance to add another table in SQL Server 2005 transactional replication without reinitializing subscriber.

Thanx.

There is always 70% of answer in every question, I did research Sql Server Books Online and found the solution:

After a publication is created, it is possible to add and drop
articles. Articles can be added at any time, but the actions required
for dropping articles depend on the type of replication and when the
article is dropped.
Adding Articles   Adding an article involves:
adding the article to the publication; creating a new snapshot for the
publication; synchronizing the subscription to apply the schema and
data for the new article


How can I maintain the dateonly field Hello I have a table in Postgres and one of the fields is type Date with the format '2019-09-10' and I'm transferring this table into a SQLite one BUT in this SQLite I'm getting this field as Date with this format 'Fri Sept 13 2019 00:00:00 GMT+0000 (UTC)' and I don't want that I just want it to be as in the Postgres table '2019-09-13'

this is the DDL of the Postgres:

CREATE TABLE public."Checks" (
    "customerId" int4 NOT NULL,
    "checkDate" date NULL
);

and this is the DDL for the SQLite:

CREATE TABLE `Checks` (`customerId` INTEGER NOT NULL NOT NULL, `checkDate` DATE);

SQLite only supports some datatypes, and DATE is not one of them (https://sqlite.org/datatype3.html).

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE `Checks` (`customerId` INTEGER NOT NULL NOT NULL, `checkDate` DATE);
sqlite> insert into Checks values (1,1);
sqlite> insert into Checks values (2,'08-09-2019');
sqlite> insert into Checks values (3,'2019-09-08');
sqlite> insert into Checks values (4,'eight september 2019');
sqlite> insert into Checks values (5,'there is no checking if this really is a date!');
sqlite> select * from checks;
1|1
2|08-09-2019
3|2019-09-08
4|eight september 2019
5|there is no checking if this really is a date!
sqlite>


After upgrade to 2016, can't view SQL Server Logs Just upgraded to SQL Server 2016 from SQL Server 2014 SP1 CU4.

This is a clustered instance

I am no longer able to use xp_readerrorlog and xp_enumerrorlog

Msg 22004, Level 16, State 1, Line 10
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 10
Error log location not found.

Found this link:
Unable to open SQL Server Logs

I can open the error logs manually via file explorer
The startup parameters for SQL Server Service indicate the right
location for the error logs.
Running repair on the instance didn't resolve the problem
Disabling Shared Memory for the instance didn't resolve the problem.
rebooting after all these issues and repairs didn't help
No aliases are being used to connect to this instance
127.0.0.1 is in the hosts file

Basically they are saying to repair the instance.

Any other options?

Windows patching and majid's solution is what worked for us too. Just adding my 2c that may help others. We have a cluster with 2008, 2014 SP2 and 2016 CU2. After upgrading an instance to 2016, we had the following errors:

2014 and 2016: syspolicy_purge_history job failed on step 3
2016: SQL agent would start then stop
2016: Couldn't view SQL error logs

Windows patching, which primarily applied .net security patches fixed the syspolicy_purge_history job in 2014. The new ODBC driver fixed remaining issues in 2016.

Note: installing 2016 SP1 before installing this driver (https://www.microsoft.com/en-us/download/details.aspx?id=53339), will install a newer ODBC driver, that appears to have the same issues when running on a cluster. We attempted this on a test environment, but had to uninstall SP1 and the new ODBC 13.1 driver, then install CU2 and the other ODBC 13.1 driver. Haven't attempted to install SP1 again yet


How to calculate conditional running additions in SQL Server 2012 I have a table with below mentioned structure

create table #test
(
ID int identity(1,1),
A int,
B int
);

and having data as

ID  A   B
1   9   NULL
2   9   1
3   9   2
4   8   NULL
5   8   4
6   8   5

I want to calculate a new column let us say C based on values of this table.
If we consider it as an excel table then, formula for calculation would be =IF(B1="",C1+A2,B1+A2).
i.e. first value of calculated column would  always be A1 and then from second row I have to apply above excel formula in SQL SERVER 2012 to calculate a new column.

I am aware about LAG and LEAD functions, but my computation involves value of computed column of previous row for computation of current computing value.

I am clueless about such calculation in SQL SERVER.
What would be the best way to compute such a condition?

EDIT
Expected output would be

9
18
10
10
18
12

The select statement which I tried is

SELECT
    CASE WHEN LAG(B) OVER (order by ID) is null THEN 351
    ELSE LAG(B)
    OVER     (order by ID) + A END
FROM #test;

EDIT 2
Test Data:

INSERT INTO #test (A, B)
VALUES
    (NULL,355)
    ,(NULL,NULL)
    ,(NULL,NULL)
    ,(NULL,NULL)
    ,(NULL,NULL)
    ,(NULL,NULL)

Result:

0
355
355
355
355
355

Below is a CTE method to facilitate evaluating values from the previous row with LAG.

CREATE TABLE #test
(
      ID int IDENTITY
    , A int
    , B int
);
INSERT INTO #test (A, B)
VALUES
      (9, NULL)
    , (9, 1)
    , (9, 2)
    , (8, NULL)
    , (8, 4)
    , (8, 5);

WITH test AS (
    SELECT
          ID
        , A
        , LAG(A) OVER(ORDER BY ID) AS prev_A
        , LAG(B) OVER(ORDER BY ID) AS prev_B
        , A + COALESCE(LAG(B) OVER(ORDER BY ID), 0) AS C
    FROM #test
)
SELECT
    A + COALESCE(CASE
            WHEN prev_B IS NOT NULL THEN prev_B
            ELSE LAG(C) OVER(ORDER BY ID)
            END, 0) AS C
FROM test
ORDER BY ID;

EDIT

To handle the NULL A values in the edited question, try the recursive CTE method below.

WITH
    curr AS (
        SELECT
              A
            , B
            , ROW_NUMBER() OVER (ORDER BY ID) AS rownum
        FROM #test
    )
    , prev AS (
        SELECT
              A
            , B
            , COALESCE(A, 0) AS C
            , rownum
        FROM curr
        WHERE rownum = 1
        UNION ALL
        SELECT
              curr.A
            , curr.B
            , CASE
                WHEN prev.B IS NULL THEN prev.C + COALESCE(curr.A, 0)
                ELSE prev.B + COALESCE(curr.A, 0)
              END AS C
            , curr.rownum
        FROM prev
        JOIN curr ON curr.rownum = prev.rownum + 1
    )
SELECT C
FROM prev;


sp_blitzcache not returning an execution plan for a long running query No execution plan is returned only a null value in the DMV sys.dm_exec_query_plan's query_plan column.  Any clue as to why?  I have used sp_blitzcache 'reads' and 'duration' to show the long running query but no execution plan returns.

Here's a few reasons why queries won't show up in sys.dm_exec_query_plan, the DMV used by sp_BlitzCache:

The query is encrypted (like an encrypted stored procedure or function)
The server has Optimize for Ad Hoc turned on, and the query was only executed once
The server is under memory pressure, and plans are being removed from the cache
The plan has over 128 levels of nested elements, so sys.dm_exec_query_plan can't show it (but you can still get it via sys.dm_exec_text_query_plan if you want the text version)


re-write active record query to PostgreSQL I want to rewrite the active record query to Actual PostgreSQL query

Camera.joins("left JOIN users on cameras.owner_id = users.id")
                    .joins("left JOIN vendor_models vm on cameras.model_id = vm.id")
                    .joins("left JOIN vendors v on vm.vendor_id = v.id")
                    .where(condition).order(sorting(col_for_order, order_for))

as you can see how these tables are related in the above joins.

also conditions could be

"lower(cameras.exid) like lower('%#{params[:fquery]}%') OR lower(cameras.name) like lower('%#{params[:fquery]}%') OR
      lower(vm.name) like lower('%#{params[:fquery]}%') OR lower(v.name) like lower('%#{params[:fquery]}%')
      OR lower(users.firstname || ' ' || users.lastname) like lower('%#{params[:fquery]}%') OR
      lower(cameras.config->>'external_host') like lower('%#{params[:fquery]}%')

Reason of writing this query, I have another table camera_shares which is related to cameras table with camera_id, I also want to get the count of camera_shares against a camera_id, How could that be possible to geth the similar results in PostgreSQL

I'm not sure about all your condition stuff but going on your textual description of the problem..

  Reason of writing this query, I have another table camera_shares which is related to cameras table with camera_id, I also want to get the count of camera_shares against a camera_id, How could that be possible to geth the similar results in PostgreSQL

You'll want something like this...

SELECT camera_id, count(*)
FROM cameras AS c
JOIN camera_shares AS cs
  USING (camera_id)
GROUP BY camera_id;


Oracle to PostgreSQL I'm having som test databases running Oracle 10 & 11 that I plan to try to migrate to PostgreSQL.

Some googling hinted that the perl script "ora2pg" could be of use, and free of charge. However, now I've spent too much time trying to get that running on my winx64 machine, since MinGw won't compile it and activeperl modules hates me.

Are there any other worthy, free, tools to take a look at or should I get a Linuxbox up & running and try ora2pg that way?

Regards

We also found existing tools tricky to work with, so ended up hacking together a solution using the sqlalchemy library in python. Our general approach was to mirror the source database; hackily convert data types where necessary; create the target database; copy across the data in chunks.

For an example, see: https://github.com/MIT-LCP/oracle2postgres/blob/master/migration.ipynb. Using this approach, we migrated a fairly large database (~100 schema, with >1000 tables, some tables with >200 million rows) from Oracle to Postgres.

For more detail, see: https://github.com/MIT-LCP/oracle2postgres. The package can be installed with pip install oracle2postgres. If you hit problems when reusing the code, feel free to raise an issue and we'll do our best to help.


CTE query doesn't print the right structure? I have this table which represents hierarchy :

childID  parentID          NAME          siblingOrder
1          1               a               0
212        1               ab              1
112        1               ac              2
412        1               ad              3
-912       112             aca             0

The structure is:

a
+----ab
+----ac
     +---aca
+----ad

(The siblingOrdercolumn is for controlling the order of ab,ac,ad  )

I already have this solution which uses the  siblingOrder :

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)  AS VARCHAR(1024))  AS PATH
   FROM   @myTable
   WHERE   childID = parentID
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)AS VARCHAR(1024) )
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

So order by PATH actually do the job  :

The problem:

The problem is that I Must(!) put values in the siblingOrder in order for it to work !

Otherwise  , For example :

If I put 0 in all siblingOrder this is the result :

(yes,  now sorting by path  - doesn't work...)

I need  that aca will always be under ac

(The only reason I added the siblingOrder is to order siblings !) and I don't want to enforce adding siblingOrder when not needed

Question :

Is it possible to enhance the query so that siblingOrder will affect only to siblings ?

I mean , If I don't care about the order of the siblings ( by putting 0) , I still expect the aca to be under ac

Sqlonline : with siblingOrder

Sqlonline - without siblingOrder

You need to include the parentID and the childIDin the path. So instead of

RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)

you should write

RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6) + RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10)

(That is in two places in your code)

Used this way you can provide the siblingOrder where desired. If present it will override the child order. If not present the children will be ordered by their childID.

SQL Fiddle without siblingOrder

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 0),
    (112, 1, 'ac', 0),
    (412, 1, 'ad', 0),
    (-912, 112, 'aca', 0)
;

Query 1:

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable
   WHERE   childID = parentID
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

Results:

|                                                                             PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER |    COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|                                                       00000000010000000000000001 |     0 |       1 |        1 |            0 |           a |
|                            00000000010000000000000001.00000000010000000000000112 |     1 |     112 |        1 |            0 |      ----ac |
| 00000000010000000000000001.00000000010000000000000112.0000000112000000000000-912 |     2 |    -912 |      112 |            0 | --------aca |
|                            00000000010000000000000001.00000000010000000000000212 |     1 |     212 |        1 |            0 |      ----ab |
|                            00000000010000000000000001.00000000010000000000000412 |     1 |     412 |        1 |            0 |      ----ad |

SQL Fiddle with siblingOrder

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 1),
    (112, 1, 'ac', 3),
    (412, 1, 'ad', 2),
    (-912, 112, 'aca', 0)
;

Query 1:

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable
   WHERE   childID = parentID
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

Results:

|                                                                             PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER |    COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|                                                       00000000010000000000000001 |     0 |       1 |        1 |            0 |           a |
|                            00000000010000000000000001.00000000010000010000000212 |     1 |     212 |        1 |            1 |      ----ab |
|                            00000000010000000000000001.00000000010000020000000412 |     1 |     412 |        1 |            2 |      ----ad |
|                            00000000010000000000000001.00000000010000030000000112 |     1 |     112 |        1 |            3 |      ----ac |
| 00000000010000000000000001.00000000010000030000000112.0000000112000000000000-912 |     2 |    -912 |      112 |            0 | --------aca |


"Inner" Group By Query sequential scan I have inner-query in left-join that is using sequential scan instead of index scan, and I have no idea on how to improve it.

I have two tables:

pages - columns: id
shares - each page has many shares, columns: page_id, view_counter

shares have an index on page_id and shares is a large table.

The query is:

SELECT id,
       shared.views     views_count,
       is_shared,
       shared.views > 0 AS tracked
FROM   pages
       LEFT JOIN (SELECT shares.page_id,
                         Sum(shares.view_counter)
                  FROM   shares
                  GROUP  BY page_id) AS shared
              ON shared.page_id = pages.id
WHERE page_id IN (1, 2, 3)

The query plan for this query is:

Merge Right Join  (cost=813559.28..895407.83 rows=4 width=21)
  Merge Cond: (shares.page_id = pages.id)
  ->  GroupAggregate  (cost=813558.85..867374.64 rows=2239970 width=8)
        Group Key: shares.page_id
        ->  Sort  (cost=813558.85..824030.88 rows=4188812 width=8)
              Sort Key: shares.page_id
              ->  Seq Scan on shares  (cost=0.00..238288.12 rows=4188812 width=8)
  ->  Index Scan Backward using index_pages_on_id_desc on pages  (cost=0.43..33.53 rows=4 width=13)
        Index Cond: (id = ANY ('{1,2,3}'::integer[]))

How can I re-write the query so I won't have the sequential scan on shares?

I think this is a good candidate for using LATERAL join:

SELECT id,
       shared.views     AS views_count,
       is_shared,
       shared.views > 0 AS tracked
FROM   pages
       LEFT JOIN LATERAL
                 (SELECT sum(shares.view_counter) AS views
                  FROM   shares
                  WHERE  shares.page_id = pages.id
                 ) AS shared
              ON TRUE
WHERE pages.id IN (1, 2, 3) ;


Autotask 'auto optimizer stats collection' and scheduler jobs logging We have a DB hosting Peoplesoft schema, with more than 33.000 tables. Since
december Oracle has left more than 18 thousand of them with no statistics
renewed:
SQL> select to_char(last_analyzed,'YYYYMM') ,count(*) from dba_tables where
   2 owner='SYSADM' group by to_char(last_analyzed,'YYYYMM') order by 1;
TO_CHA  COUNT(*)
------ ----------
202012     18369
202101        23
202103        14
202104       832
           14490
Yet if I check one of them (as an example) within these 18369 tables, I find
it has been modified way above the 10% default threshold for stats
recomputation; select from dba_tab_modifications shows:
TABLE_NAME             INSERTS   UPDATES   DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
PS_JOB                    8063    181375       395 12-APR-21
SQL> select t.table_name,ts.stale_stats,
   2 t.monitoring,t.last_analyzed,t.num_rows from dba_tables t,dba_tab_statistics
   3 ts where t.owner=ts.owner and t.table_name='PS_JOB' and
   4 ts.table_name='PS_JOB';
TABLE_NAME          STALE_S MON LAST_ANAL  NUM_ROWS
-------------------- ------- --- --------- ----------
PS_JOB              YES    YES 08-DEC-20    162039
SQL> select count(*) from sysadm.ps_job;
 COUNT(*)
----------
   180794
In DBA_AUTOTASK_JOB_HISTORY or DBA_SCHEDULER_JOB_RUN_DETAILS views the
analyze-table jobs "ORA$AT_OS_OPT_SY_nnnnn" never take more than 35 to 40
minutes.
Yet this table is skipped from all stats recomputations that occur every day...
I have 2 questions:
o Someone knows if internal process doing stats recomputing job (i.e.
procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC, which is not documented)
is logged somewhere with relevant details ?? (I'm very surprised that, given
the amount of tables this schema contains, it only takes a little more than
half an hour to complete...)
o What are conditions, or tests performed by DBMS_STATS in the background to
yield list of tables of which statistics should be recalculated ?? (why this
PS_JOB table hasn't been recomputed yet since Dec, 8th ??, in spite of its
growth)
In advance - thanks a lot.
Regards,
Sebino

Try
select sample_size from dba_tables where table_name='PS_JOB';

What does that return?
If that number is not as expected, is it possible another DBA used something like DBMS_STATS.SET_GLOBAL_PREFS
in this way for example:
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '20');

instead of using the default of DBMS_STATS.AUTO_SAMPLE_SIZE? (which may be more than 10 itself)
That figure of 20 would change it from the traditional default of 10.  However AUTO_SAMPLE_SIZE will have Oracle determine the value and that may have been set, and Oracle chose a value larger than 10.
I am also in the dark about 'auto optimizer stats collection' and exactly how it has been working internally since 11g.  We have some windows though (8 total) that run for longer than 3 hours of stats gathering on our production databases.


Cannot reinstall SQL Server 2008 R2 after downgrade from Windows 10 to Windows 7 Scenario: using SQL Server 2008 R2 Express under Windows 7 without problems.

Then I upgraded to Windows 10 without problems.

Then I downgraded back to Windows 7 (due to problems with other applications). SQL Server is gone.

When I run x64_ENU + SQLEXPR_x64_ENU to reinstall I get the following error message during "Setup Support Files" (with ExecuteStandardTimingsWorkflow apparently the current task) and cannot go further:

  Microsoft SQL Server 2008 R2 Setup The following error has occurred: An
  error occurred during the installation of assembly
  'policy.8.0.Microsoft.VC80.CRT,version="8.0.50727.42",type="win32-policy",
  processorArchitecture="amd64",publicKeyToken="1fc8b3b9a1e18e3b"'.

Running SFC  /SCANNOW did not reveal any problems.

Program_Install_And_Uninstall also did not. Microsoft Visual C++ 2005 Redistributable Package (x64) was not installed, so I installed it.

Now SQL Server 2008 R2 Setup ran a lot further, but then generated a similar (but not identical) error at

  Install_SqlSupport_KatmaiRTM_Cpu64_Action: CreateShortcuts

The following components were installed:

SQL Server 2008 R2 Native Client
SQL Server 2008 R2 Policies
SQL Server 2008 R2 setup (English)
SQL Server Compact 3.5 SP2 ENU
SQL Server Compact 3.5 SP2 Query Tools ENU

Deleting all these, and also all components of Visual Studio, and trying again did not change this.

A few more suggestions—adding as an answer to accept more characters so I can include more. . .

BE SURE TO RUN EVERYTHING AS ADMINISTRATOR

Command prompt (as administrator) run SFC /SCANNOW - Once this completed, check the log file it points to in the window for other errors and post back that as an edit to your question, I think it's called CBS.log, so just look for error with the date and time around the time you run once it's complete
https://support.microsoft.com/en-us/mats/program_install_and_uninstall
You may want to ensure this is installed (or uninstall re-install)
https://www.microsoft.com/en-us/download/details.aspx?id=21254
If you have Visual Studio installed, you may want to consider uninstalling and reinstalling it as well
If there are any other prerequisite components installed on this system for SQL Server 2008 R2 Express, you may want to consider uninstalling and reinstalling those as well - see notes below

Note, sometimes with the SQL Server and Visual Studio and other components, software, etc. as such, you have to install them in the order based on their version or things in the Windows registry, DLL component, etc. just get out of wack.

For example, if you have some component (or software) of the 2005 and 2008 version you need, it's best to install the oldest first (2005) and then once that is complete install the newest (2008).  I've seen this too many times where someone installs some old component or software as such on top of newer software, etc. that's already installed then the system start having strange problems.

If all else fails, you can backup your important data, and then do a fresh wipe of your HD and install a fresh copy of Windows. Microsoft has no way of emulating all software on everyone computers in the whole world, so perhaps when you upgraded and then downgraded, something just got corrupt and there was something on your system that they didn't test with, etc.  I typically always do fresh system upgrades rather than in-place upgrades but I'm in a business environment mainly too.


Columnstore index and low selective columns I have a table with almost 1T rows.
create table bigtable (
  K1 int, K2 date, -- PK columns
  C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
  B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
  primary key (K1, K2)
)

I want to use columnstore to increase the query performance. There are the following options.

Create a cluster columnstore index
Create a noncluster columnstore index for all the columns
Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?

The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?
I'm using SQL Server 2014. I can drop/recreate the nonclustered columnstore index periodically in my case because the data is not updated frequently.

One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).

So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).


Unable to start listener of standby database As part of data guard configuration, I've installed standby database using Oracle Software only and configured .ora files/folders as required. But while trying to start the listener or check the status in standby, it's throwing the following error.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
64-bit Windows Error: 61: Unknown error

Then, I tried to start db using pfile and that too shows error as follows.

SQL> startup pfile=C:\app_home\product\11.2.0\dbhome_1\database\initLOCAL2.ora
ORA-12560: TNS:protocol adapter error

As I'm doing this for the first time, I'm very much confused at this stage. How can I resolve this?

Here are the steps I follow when creating a standby for a dataguard environment on windows server, please confirm in comments at which step you currently are to help identify your issue here.
(prd1 is the current alive instance, stby1 is the standby instance you're trying to create)

1- install the oracle binaries and patch them to the same level as the live instance without create a database on the stby1's host server

2- On the prd1 instance create a control file for the standby ALTER DATABASE CREATE STANDBY CONTROLFILE AS  'D:\CONTROL01.CTL';

3- copy the content of %ORACLE_HOME%\database (windows) to the same path from prd1 to stby1

4- use a CMD window to manually create the windows services for the standby database instance

set ORACLE_HOME=[path to oracle_home]

oradim -new -sid STBY1

set ORACLE_SID=STBY1

sqlplus / as sysdba

startup mount;

5- Make sure the listener.ora and tnsnames.ora files are configured correctly on both prd1 and stby1

%ORACLE_HOME%\network\admin\tnsnames.ora on both server contains something like

prd1_server1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prd1)
    )
  )
stby1_server2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = stby1)
    )
  )

6- %ORACLE_HOME%\network\admin\listener.ora on server1 (prd1) contains

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME =prd1_DGMGRL)
      (ORACLE_HOME = [path to oracle_home])
      (SID_NAME = prd1)
    )
  )

7- listener.ora on stby1 server2 contains something like

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME =stby1_DGMGRL)
      (ORACLE_HOME = [path to oracle_home])
      (SID_NAME = stby1)
    )
  )

8- Once you got all the above settings figured out you go on the stby1 instance and recover the database using SQLPLUS

RECOVER STANDBY DATABASE;


AVG with OVER - invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause I have what I believe to be a correctly written sql query that calculates the average using an over clause to window the results. However, I'm getting an error when I include the over clause.

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

Which gives me the following error:

  Msg 8120, Level 16, State 1, Line 93
  Column 'day_Sales_total.SalesItems_Breakfast' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Which is bizarre, because as SalesItems_Breakfast is inside the AVG function.

However, the below query runs fine, the only difference being the over clause has been removed:

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

What's going on?

Probably you need

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

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


Capture live stored procedure calls (as per Query Profiler) in SQL Azure TL;DR - Is it possible to capture live procedure calls (with parameter names/values) in SQL Azure, to replicate the output of SQL Profiler illustrated below - or - is there a better way to achieve this?

Historically when my web application has performed badly, I used SQL Server Profiler to capture calls from the web app, and see the stored procedures and their parameters as they were executed.  I then copied/pasted them into SSMS and used the execution plan to modify the procedures until performance was enhanced:

Now, while testing out SQL Azure, I see a lot of people asking how to use Profiler with SQL Azure, and being told it can't (and probably won't in future).  While that might not be true any more (as a lot of questions online are quite old), the question above remains.

I've tested out the Azure Performance Insight, but it gives a good overview, and doesn't capture quite what I'm looking for.

You can use SQL Server Management Studio XEvent Profiler and you may also see Azure Data Studio supports seeing XEvents from cloud and on-premises but SSOS uses the ring buffer target.


Reinitialize Table Values in SQL SSDT Unit Testing I am creating SQL Server Unit Tests. We are testing various stored procedures.

In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (truncate/delete) the database tables, and resetup for each test. This way every unit tests will have a clean environment to validate sprocs which insert, select, update, delete, etc,

Does anyone where or how to reinitialize the tables values in Sql Unit Testing? Resources are pretty new for unit testing in SQL SSDT VS 2017, so I think lot of people are trying to figure out and understand.

Feel free to show or add pictures below.

http://www.sqlservercentral.com/articles/Unit+Testing/155651/
http://www.erikhudzik.com/2017/08/23/writing-sql-server-unit-tests-using-visual-studio-nunit-and-sqltest/

Pictures in Visual Studio SSDT:

Also, trying to review this class in SQLDatabaseSetup.cs:

 [TestClass()]
    public class SqlDatabaseSetup
    {

        [AssemblyInitialize()]
        public static void InitializeAssembly(TestContext ctx)
        {
            // Setup the test database based on setting in the
            // configuration file
            SqlDatabaseTestClass.TestService.DeployDatabaseProject();
            SqlDatabaseTestClass.TestService.GenerateData();
        }

    }
}

using Microsoft.Data.Tools.Schema.Sql.UnitTesting;

I have created the usp_Generate_Merge_For_Table procedure a few years ago exactly for this purpose.
It can generate a MERGE command with a Values Constructor in order to "re-initialize" the contents of a specific table.

You can find it here:

https://gist.github.com/EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb#file-generate_merge_for_table-sql

Direct link to the raw script:

https://gist.githubusercontent.com/EitanBlumin/7faba0b39c4f90d4cfa879a45f3e01eb/raw/608177e77da0a42d686098ab10703bbba8c54d64/Generate_Merge_For_Table.sql

Simply create the procedure in your database, run it while sending it your table name as a parameter, like so:

EXEC usp_Generate_Merge_For_Table 'cities', 'dbo'

And then you can copy and paste its output in your Unit Testing project.

You can find more info and detailed instructions on how to use it here:

https://eitanblumin.com/2018/10/28/generate-merge-statement-as-a-snapshot-of-your-tables-content/


postgres btree index on large text column I have a table with big text column on which I want to enforce uniqueness,
postgres can only do this with a btree index. I get the following error :

CREATE unique INDEX idx_protein_seq_sequence ON protein_seq USING hash (sequence);

[54000] ERROR: index row requires 8736 bytes, maximum size is 8191

Are there solutions ?

I'm thinking of using a trigger (update and insert) and find duplicates with a hash index (a hash index seems to have no limit on size of text columns);

You can use an exclusion constraint instead of a unique constraint:

alter table protein_seq add exclude using hash (sequence with =)

An exclusion constraint is much more general than a unique constraint, and allows you to exclude on things like overlapping ranges.  When you exclude "with =", it reduces to basically the same thing as a unique constraint, except it retains the flexibility to use a hash index.