Foreign keys - link using surrogate or natural key? Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack Douglas' answer in this question, and his reasoning seems sound to me. I'm aware of the discussion beyond that that rules change, but this would be something that would need to be considered in any situation.

The main reason for asking is that I have a legacy application that makes uses of FKs with natural keys, but there is a strong push from devlopers to move to an OR/M (NHibernate in our case), and a fork has already produced some breaking changes, so I'm looking to either push them back on track using the natural key, or move the legacy app to use surrogate keys for the FK. My gut says to restore the original FK, but I'm honestly not sure if this is really the right path to follow.

The majority of our tables already have both a surrogate and natural key already defined (though unique constraint and PK) so having to add extra columns is a non-issue for us in this insance. We're using SQL Server 2008, but I'd hope this is generic enough for any DB.

Neither SQL nor the relational model are disturbed by foreign keys that reference a natural key. In fact, referencing natural keys often dramatically improves performance. You'd be surprised how often the information you need is completely contained in a natural key; referencing that key trades a join for a wider table (and consequently reduces the number of rows you can store in one page).

By definition, the information you need is always completely contained in the natural key of every "lookup" table. (The term lookup table is informal. In the relational model, all tables are just tables. A table of US postal codes might have rows that look like this: {AK, Alaska}, {AL, Alabama}, {AZ, Arizona}, etc. Most people would call that a lookup table.)

On big systems, it's not unusual to find tables that have more than one candidate key. It's also not unusual for tables that serve one part of the enterprise to reference one candidate key, and tables that serve another part of the enterprise to reference a different candidate key.  This is one of the strengths of the relational model, and it's a part of the relational model that SQL supports pretty well.

You'll run into two problems when you reference natural keys in tables that also have a surrogate key.

First, you'll surprise people. Although I usually lobby strongly for the Principle of Least Surprise, this is one situation where I don't mind surprising people. When the problem is that developers are surprised by the logical use of foreign keys, the solution is education, not redesign.

Second, ORMs aren't generally designed around the relational model, and they sometimes embody assumptions that don't reflect best practice. (In fact, they often seem to be designed without ever having input from a database professional.) Requiring an ID number in every table is one of those assumptions.  Another one is assuming that the ORM application "owns" the database. (So it's free to create, drop, and rename tables and columns.)

I have worked on a database system that served data to hundreds of application programs written in at least two dozen languages over a period of 30 years.  That database belongs to the enterprise, not to an ORM.

A fork that introduces breaking changes should be a show-stopper.

I measured performance with both natural keys and surrogate keys at a company I used to work at. There's a tipping point at which surrogate keys begin to outperform natural keys. (Assuming no additional effort to keep natural key performance high, like partitioning, partial indexes, function-based indexes, extra tablespaces, using solid-state disks, etc.)  By my estimates for that company, they'll reach that tipping point in about 2045. In the meantime, they get better performance with natural keys.

Other relevant answers: In Database Schema Confusing

If you don't know the answer, go with the Surrogate. Here's why - if assumptions are made about business rules, and those assumptions are false or the rules change, your data is garbage. Here is an example:

Person, Role, PersonRole

current business rule states that a Person has one Role. You make a table that links Person and Role where PersonRole (PersonName, PersonBirthDate, PersonMotherMaidenName, ..., RoleCode)

Now you are a true purist when it comes to Natural Keys! But seriously, what if the org decides that a person can now assume multiple roles? What are the downstream effects of supporting the change in business needs?


Shrink database after drop table? I had a table with 70+ million records in a SQL Server database, I dropped that table (a one-time thing) to free up space on the disk, but it looks like the size didn't change much. I do see that I could shrink the db to a minimum.

Is that the way to do it?

The last time I did a shrink using SQL Server Management Studio, it took a few hours to complete. Is there a faster way?

Dropping a table will free up the space within the database, but will not release the space back to Windows.  That requires shrinking the database file.  However we don't want the database file full.  We want lots of free space so that as we load more data we don't have to grow the data file frequently.  This causes fragmentation of the data file on the physical disks.

As for making shrinking faster, no there's no way to make it faster.  Shrinking the database requires reading and rewriting most of the data within the database so that all the white space can be released from the database file back to the OS.  All this IO takes time, and causes a lot of fragmentation problems.

Shrink is quite expensive procedure and might take hours. To use space most efficiently you may transfer tables into new filegroup with clustered indexes precreated and drop previous. I personally prefer this because it's easier to make this "shrink" in several steps and more predictable- thus easier to plan. When you start DBCC SHRINK you don't know how much time it will take.

Update (Thanks to mrdennny for pointing that): this is a very specific approach and can only be used if you have a read-only database (like data warehouse) because while copying data to another table no writes are allowed to this table for the sake of consistency. To save time and gain max performance of it you may switch database to SIMPLE recovery model and use TABLOCK hint - this will allow system to use minimal logging and do much less writings into transaction log.


Automatically execute query in MS SQL Studio every hour I support an application in a big enterprise, one of my roles is to clean-up data. There is a query I need to execute every hour, and I would like to automate it. Due to organization policies, I can't create SQL Server Agent jobs or modify schema, I can only manipulate data.

An endless

WHILE(1=1)
BEGIN
WAITFOR DELAY '01:00';
--do work
END

does the job for me, but I shrug at the thought of a perma-open connection.

Ideally, I would script the MS SS itself to execute a given piece of code every hour, but I'm not sure if that is possible.

Is there any solution to this problem?

Your friend is sqlcmd (Microsoft Technet)

Create a SQL file with the script required to run your cleanup job
Run the script with sqlcmd.exe and any required parameters
Create a Windows Scheduled Task and add the command with all the required parameters

E.g.

sqlcmd -d YOUR_DB -E -i YOUR_SCRIPT.SQL -o OUTPUTFILE.TXT

Good luck.

Using "SQL Server Agent" which is found within MS SQL studio (in the object explorer, expand your server and it should be normally at the bottom of the list) is probably your best bet.

This will create a job which can be set to execute at defined intervals. The jobs will run as a service on the server rather than the client. This means if your client disconnects it will still run, and if the server is rebooted (for an unknown reason), the job will continue to run without you needing to initialise it again.

The downside is that you will likely need elevated access rights to be able to do this.


How can I query data from a linked server, and pass it parameters to filter by? I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.

The basic syntax looks something like this:

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

The query runs quickly if run locally on the the individual servers, however it takes a long time to run if it's run from a linked server using the 4-part names like above.

The problem appears to be it's querying the linked server for the unfiltered result set first, then joining it to the #tmpIds table on the local server afterwards, which makes the query take an very long time to run.

If I hardcode the Ids to filter the result set on the linked server, such as

SELECT T1.Id, ...
FROM Server.Database.dbo.Table1 as T1
-- INNER JOIN #tmpIds as T ON T1.Id = T.Id
INNER JOIN Server.Database.dbo.Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Server.Database.dbo.Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Server.Database.dbo.Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Server.Database.dbo.Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Server.Database.dbo.Table6 as T6 ON T5.Id = T6.Id

WHERE T1.Id IN (1, 2, 3)

it runs quickly in just a few seconds.

Is there a way to run this query so it filters the result set of the query from the linked server by the #tmpId table first, before returning the result set to the local server?

Some things to note

The query is very large and complex, and Dynamic SQL is not a viable option for it due to the maintenance nightmare that causes.

I would be open to suggestions about how to use Dynamic SQL for something else though, such as running a stored procedure or UDF if there's a way to do that over a linked server (tried a few different methods such as sp_executeSQL, OPENROWSET, and OPENQUERY, but those all failed).
Because it uses the 4-part naming convention, I cannot use a UDF on the remote server
Distributed Transactions are disabled, so the following does not work

INSERT INTO #table
EXEC Server.Database.dbo.StoredProcedure @ids

The performance problem actually had to do with the LEFT OUTER JOIN tables. If I changed them to INNER JOIN, or if I excluded their data from the SELECT columns, the query ran fine.

What I ended up doing was creating a View on the linked server containing all the data I wanted from it, then simply joining to it from the primary server with the #tmpIds table.

I didn't think this would work since I thought joining everything and pulling it down to the second server before filtering was the same as what I was doing now, and would lead to the same performance problem, but surprisingly that doesn't appear to be the case.

CREATE VIEW MyView
AS

SELECT T1.Id, T2.ColA, ...
FROM Table1 as T1
INNER JOIN Table2 as T2 ON T1.Id = T2.Id
INNER JOIN Table3 as T3 ON T2.Id = T3.Id
LEFT OUTER JOIN Table4 as T4 ON T3.Id = T4.Id
LEFT OUTER JOIN Table5 as T5 ON T4.Id = T5.Id
LEFT OUTER JOIN Table6 as T6 ON T5.Id = T6.Id

GO

and

INSERT INTO #tmpTable (Id, ...)

SELECT T1.Id, T1.ColA, ...
FROM Server.Database.dbo.MyView as T1
INNER JOIN #tmpIds as T ON T1.Id = T.Id

All the joined columns were correctly indexed, however according to this answer

  Even though there may be indexing on tables on the remote server, SQL may not be able to take advantage of them while it can build a local query plan that does take advantages of indexing.

And this one

  Let the linked server do as much as possible.
  It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server

so I am guessing that the query plan used for the query was not using the Indices defined, and SQL Server was generating a poor query plan for the LEFT OUTER JOIN tables.

You have experienced one of the main problems with linked servers.

ALL data is retrieved over the network and then filtered.

IF the #tmpFile is relatively small compared to the others you could consider a stored procedure that RECEIVES #tmpFile, then runs all the joins locally and returns a dataset to the caller.


deadlock detected during oracle expdp I have an Oracle 11g and I wanted to export all my shcema objects,

with this command:

expdp as '/ as sysdba' directory=DIR dumpfile=DWH%U.dmp logfile=DWH.log filesize=40000m parallel=32 schemas=DWH

This is the start of the output I'v got:

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1840. GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 14 with process name "DW0D" prematurely terminated
ORA-31671: Worker process DW0D had an unhandled exception.
ORA-39078: unable to dequeue message for agent KUPC$A_1_100742920000000 from queue "KUPC$C_1_20190915100610"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 610
ORA-04020: deadlock detected while trying to lock object SYS.KUPC$C_1_20190915100610
ORA-06512: at "SYS.KUPW$WORKER", line 1887
ORA-06512: at line 2
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/SYNONYM/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "DWH"."T3"                            9.119 GB 21698829

The exception occured but the export hasn't failed.. and I can see that the tables start to be exported.

What is this exception mean? deadlock on object of sys?
I can see that the object "KUPC$C_1_20190915100610" is a queue of SYS..
Should I need to restart my export or can I use it after finished?

Thank you.

since you have multiple errors I recommend you to do the following:

if you have an old dump inside this directory move it to another place.
execute the following command select * from v$sgastat where pool='streams pool'; and increase STREAMS_POOL_SIZE value.
check open_cursor value and increase it (make sure scope=both when you edit it).

Note: I would recommend leaving step 3 to the end. Try to take a backup before executing it.


Discarding results from CTE based on another CTE WITH R1 AS
(
    // some complex query
),
R2 AS
(
    // uses R1
    // another complex query
),
// here I already have dataset with Id, Name, ProductReference and other columns
R3 AS
(
    select  Name, ProductReference, Count(*) as Count
    from Products
    group by Name, ProductReference
    having Count(*) > 1
)

Now I need to filter R2 results based on R3, so that if Name and Reference combinations ARE present in R2 then only select where ID is minimum, discard rest of them

R2
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
2       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data
5       n1          someguid1           some data
6       n1          someguid2           some data

R3
Id      Name        ProductReference    Count
1       n1          someguid1           4

Expected Output
Id      Name        ProductReference    OtherColumns
1       n1          someguid1           some data
3       n2          someguid2           some data
4       n3          someguid1           some data

if im not mistaken, you can get the result you want in R3,
simply use

select min(id) as Id,Name,ProductReference
from Products
group by Name,ProductReference

the result will return exactly what you are looking for
in your example, n1 will be grouped to its minimum id, thus the result set will be

  Id,Name,PR,OC
  1,n1,...,... 3,n2,...,... 4,n3,...,...

What you want is something like this (see below and fiddle here for complete DDL and DML):

(Also, I made the other_columns column all different so they couldn't be GROUP BY-ed. which could lead to incorrect results - at least, that's what I  found...).

SELECT * FROM r2
WHERE id = (SELECT id FROM r3)
AND name = (SELECT name FROM r3)
AND product_reference = (SELECT product_reference FROM r3)
UNION
SELECT * FROM r2
WHERE name != (SELECT name FROM r3)
ORDER BY id, name;

Result:

id  name    product_reference   other_columns
1   n1              someguid1   blah1
3   n2              someguid2   blah3
4   n3              someguid1   blah4

=============== DDL and DML ==============

Table r2:

CREATE TABLE r2
(
  id INTEGER NOT NULL,
  name VARCHAR (5) NOT NULL,
  product_reference VARCHAR (20) NOT NULL,
  other_columns VARCHAR (20) NOT NULL
);

Populate:

INSERT INTO r2
VALUES
(1, 'n1', 'someguid1', 'blah1'),
(2, 'n1', 'someguid1', 'blah2'),
(3, 'n2', 'someguid2', 'blah3'),
(4, 'n3', 'someguid1', 'blah4'),
(5, 'n1', 'someguid1', 'blah5'),
(6, 'n1', 'someguid2', 'blah6');

Table r3:

CREATE TABLE r3
(
  id INTEGER NOT NULL,
  name VARCHAR (5) NOT NULL,
  product_reference VARCHAR (20) NOT NULL,
  pcount INTEGER NOT NULL
);

INSERT INTO r3 VALUES (1, 'n1', 'someguid1', 4);

Query:

SELECT * FROM r2
WHERE id = (SELECT id FROM r3)
AND name = (SELECT name FROM r3)
AND product_reference = (SELECT product_reference FROM r3)
UNION
SELECT * FROM r2
WHERE name != (SELECT name FROM r3)
ORDER BY id, name;

Result:

id  name    product_reference   other_columns
1   n1  someguid1   blah1
3   n2  someguid2   blah3
4   n3  someguid1   blah4


Efficiencies and pitfalls of a dynamically updated "Uber" View to streamline future code We have a number of tables (about 40) with approximately 2500 columns between them that all have matching primary keys, but different unique columns.  For various procedures and views, we'll draw on any number of them - sometimes hundreds of them - to build the necessary results.

I've been considering creating a view that ties all these tables and columns together primarily for the sake of simplicity in referring to the tables and columns in code for subsequent procedures and views, but I wanted to check that I'm not making a major mistake in doing so.  Unfortunately, I don't directly have the ability to change the tables, although there are circumstances where the tables will have columns added or removed, so I need to account for that.

My first question is, I would expect for any subsequent query against the view that the SQL Server optimizer would exclude any tables or columns from the actual execution that aren't actually used in the result set, correct?

I would also need to create a stored procedure to update the definition of the view so that it adjusts the columns included in the view as they change, correct?  Are there any features or possible hang-ups I'm not considering here?

To summarize discussion on the topic, creating this sort of "Uber" view with several tables that have matching primary keys and similar (if not the same) row counts is not necessarily a bad idea for the purpose of streamlining code.

Additionally, any tables or columns not used in subsequent queries on such a view are dropped from the execution plan in most cases, so performance is most likely unharmed by unnecessary joins in the view definition.

However, how the view is used down the road is an important consideration to bear in mind and should be avoided in multi-layer view within view, or view within stored procedure calls.

One interesting little note I learned while testing this out is there is a View width limit of 1,024 columns in SQL Server, so my particular case is simply not possible to accomplish.

I think your view solution could be appropriate in a scenario where you would vertically partition the data, i.e. where all tables contain the same columns and the view would merely UNION those rows with check constraints.

However, what you're proposing is a view that joins a large number of tables together, i.e. partitioning horizontally. Even if we assume that all tables have matching primary key definitions, making joins a lot more efficient, the server still has to join each and every one of them, every time, even if you use just a single column. Here's why:

Example:

CREATE VIEW dbo.everything
AS
SELECT a.a1, a.a2, a.a3, a.a4, b.b5, b.b6, b.b7, b.b8
FROM dbo.tableA AS a
INNER JOIN dbo.tableB AS b ON a.key1=b.key1 AND a.key2=b.key2

If you query this view, the server will still need to check that there is a matching record in dbo.tableA for each row in dbo.tableB and vice-versa. The more tables you add, the more joins. You would work around this by using FULL JOIN, but I doubt that this will have the desired effect once you add a lot of tables.

In my opinion, designing your queries to use just the needed tables (as they do currently) will be your best bet. If you need to improve query performance, I would look at creating specifically targeted indexes on frequently used tables/columns.

Basically, it comes down to performance vs simplicity of use, but the performance penalty for your view solution may be severe. Building a stored procedure to actually update the view is a fairly simple matter.


mysql - order by first condition in where clause I have a table consists of the following fields

id, name

with the following values

1, ciro
2, test ciro
3, ciprox
4, other
5, other

i would like to get all the values that begin with "ci" or contain "ci" but show me the results before they start with ci and then the rest.

the query

select * FROM table WHERE name like 'ci%' or name like '%ci%' order by name;

I returns

1, ciro
2, test ciro
3, ciprox

I want

1, ciro
3, ciprox
2, test ciro

therefore before the rows in which name begins with "ci" and then those that contain

is there a way without using two queries to obtains the result?

This is quite a common problem! A case expression helps here. A query something like (untested)

select *
from table
where name like '%ci%'
order by case when name like 'ci%' then 0 else 1 end, name;

should do what you want.

You should go for parent-child-query. The child or inner query will  return the sorted set and In outer query just put your condition:

select * from (
  select * from `table_name` order by attribute asc) as sample_table_name
where attribute >=x;


Securing Always Encrypted SQL Server from "sa" I'm currently studying SQL Server Always Encrypted feature. How can I disable column decryption to "sa" if all they have to do is add Column Encryption Setting=enabled in the login's additional login parameters in SQL Server Management Studio?

I read articles in Google. They only said we can secure data from admins but no example how.

The column encryption key (CEK) is used to encrypt the data and is stored in the database. The CEK is secured using  a column master key (CMK).

The CMK is stored outside of SQL Server. Metadata about the key is stored within SQL Server.

If you restrict access to the CMK your database administrators / system administrators (sa) will not have the ability to decrypt.

You can't simply because the key all stored in the catalog, and the catalog is unrestricted to sa.

I didn't write the query, I just googled for sys.column_encryption_key_values (which I knew stored the sym encryption keys), and found someone else who did the work.

SELECT cek.name, cmk.key_path, cekv.encrypted_value, cekv.encryption_algorithm_name
FROM sys.column_encryption_key_values cekv, sys.column_encryption_keys cek, sys.column_master_key_definitions cmk
WHERE cek.column_encryption_key_id = cekv.column_encryption_key_id
   AND cmk.column_master_key_definition_id = cekv.column_master_key_definition_id;

All bets are off with symmetric encryption if someone gets root.


Understanding data abstraction levels The three-level ANSI SPARC Database Architecture suggests three data abstraction levels, namely, external, conceptual, and internal levels.

If I understand correctly, the external level represents the users' views, the conceptual level is the conceptual diagram (ER model), and the internal level is the relational model.

My problem is that I found in the literature that besides those, there is a fourth level (the lowest one), which is the physical level.

I want to understand what is it concretely? Is it at this point that we define the type of index, access paths, and things related to physical access to data?

Although the definitions of the schemas or levels of representation of the ANSI/SPARC architecture did not reach the desired precision, they are only three, and they are named external, conceptual and internal, respectively.

As far as I know, there is no fourth level, because the physical structures employed by a database management system (DBMS for brevity) compose, in fact, the internal schema, as I will explain below.

Note: Even if imprecise, the ANSI/SPARC architecture is definitely a very useful reference when building a system, so it looks like the literature you found contains inaccurate information or perhaps it makes a reference to the (also very useful) notion where the layers of abstraction of a database are called conceptual, logical and physical, which has different implications.

Brief descriptions of the three ANSI/SPARC levels

I deem opportune to describe each level (ordered from the highest to the lowest degree of abstraction) so as to expose its scope:

The external level is the way in which the information is presented to end-users of a computerized information system; e.g., the specific organization of the pieces of information as displayed or requested in the screens or windows (via, e.g, lists, grids, labels, text boxes, check boxes, drop-down menus, etc.) of an application program.
The conceptual level involves the representation of the structure of the information as (a) perceived by the business experts and (b) modeled by the database analysts, commonly in terms of entity types, properties and associations, so it is comprised of ideas or conceptualizations, it is not “concrete” yet and its representation requires constructs that are in lower levels of abstraction. The conceptual level is informally referred to as the business rules of a certain organization regarding its informational requirements. As an side, schemas of this kind are usually —though not necessarily— expressed via graphical devices like, e.g., entity-relationship diagrams to facilitate communication between the interested parties.
The internal level has to do with how the DBMS of relevance stores the information in a concrete form; e.g., arrays, pages, extents, files, records, fields, pointers, etc. This is the lower level  of abstraction since, to put it some way, the DBMS “saves” the information in “real” structures, so this may also be conceived as the physical level of the architecture but naming it in this way does not imply that there is a fourth level. It is worth to mention that there can be different sublevels of abstraction within the internal level itself, but that factor depends on the particular mechanisms used by the DBMS in question.

Relational databases created on SQL DBMSs with respect to the ANSI/SPARC architecture

When a practitioner uses a SQL platform (Microsoft SQL Server, IBM DB2, Sybase ASE, PostgreSQL, etc.) to build a database using the mechanisms proposed by the relational model by Dr. E. F. Codd:

The base tables, columns, types (and domains when available) and constraints must reflect the characteristics of the conceptual schema (entity types, properties, associations, cardinalities) so that the data (in the shape of rows) remains consistent with it. The declarations made by means of a data sublanguage (e.g., SQL DDL) of the base tables, columns, types and constraints are instruments that formalize the conceptual schema, so they are considered logical constructs and, as such, they do not yet “tocuch” the physical level, they are still free from DBMS-specific concrete storage structures. Normalization, being a logical procedure, applies here, not at the conceptual schema.
The views (i.e., derived tables that “combine” columns from one or more base tables or from other derived tables as well, typically by virtue of SQL DML operations involving JOINs) maintain a close correspondence to the external level, because they gather and provide the information requested or sent by the screens or windows of one or more application programs, just like the end-users see it.
The indexes, pages, extents, files1, records2, fields3, partitions, allocation maps, and many other arrangements match the internal schema, since they are the “physical” structures utilized by a DBMS to store the information.

The SQL DBMS functions as a mediator (1) between the internal and the logical representations of the conceptual schemas, and (2) between the internal and external representations through connections via views.

Notes

1, 2, 3 Note how important it is to distinguish between (a) files, records and fields and (b) tables, rows and columns —or relations, attributes and tuples—, as they are elements that belong to distinct levels of abstraction and, hence, are susceptible of very different operations and treatments, having therefore very dissimilar impacts.

that ANSI standard defines 3 different levels of describing the view+schematics of a database.
The "lowest" layer is independent of that - it is not part of the aforementioned standard - as it is the database itself.


What's a good way to get SQL Server to release memory to the operating system on a dev machine? On my dev box I have my SQL Server configured with a high maximum amount of memory (8GB of 16GB). This is required for many development tasks.

Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.

What's a good way to get SQL Server to suddenly release all it's memory to the operating system?

DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.

Restarting SQL Server is a problem because this causes a 1 minute database recovery to be run due to this bug/feature: Stopping the SQL Server service causes databases to not shut down cleanly (please vote for that connect item; seems like an egregious bug).

I routinely increase and decrease the max server memory configuration option for the SQL Server 2005-2016 instances running on my laptop (with lock pages in memory enabled). Has never caused me any problems, and memory always seems to be released quickly. I also stop, start, and restart instances as I find I need them (using SQL Server Configuration Manager, which I keep open).

As far as checkpoint is concerned, how you configure that depends on version. Manual checkpoint with a duration target has been available since 2005 (though it is undeniably more effective in later versions).

Running a manual checkpoint <duration> per database before shutdown will help spread the I/O load and reduce recovery time on startup. Managed checkpointing will also reduce I/O needed when a memory shrink is requested because there will be fewer dirty pages to write to persistent storage. There is no particular benefit to dropping clean buffers before reducing the max memory setting that I have noticed.

Not using 'lock pages in memory' may increase the rate at which SQL Server can release memory, but I rely on that to avoid paging SQL Server memory to disk. If you don't need it for that purpose, you might choose to run without it.

Stop the SQL Server (MSSQLSERVER) service and restart it. Does the trick.


Is table aliasing a bad practice? I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type:

SELECT t1.id, t2.stuff
FROM
              someTable    t1
   INNER JOIN otherTable   t2
      ON t1.id=t2.id
;

But... Why is this acceptable in stored procedures and such? It seems like all it does is harm the readability of the statement while saving an extremely minor amount of time. Is there any functional or logical reason to do this? It seems to add ambiguity rather than remove it; the only acceptable reason I can see for using this format is if you were adding a semantically meaningful alias -- for example, FROM someTable idsTable -- when the table name isn't descriptive enough.

Is table aliasing a bad practice or is this just a misuse of a helpful system?

Table aliasing is a common and helpful practice.

It saves you keystrokes when referencing columns anywhere in your query.
It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used.
It is even required when you join a table to itself or when you join to the same table multiple times. This is so the query optimizer knows which table you are referencing when you mention a column.

The following reporting extract illustrates all of the above points nicely:

INSERT INTO reporting.txns_extract
SELECT
    -- 30+ columns snipped
    --
    -- Would you want to type out full table names for each
    -- column here?
FROM
    -- ... and in the JOIN conditions here?
                billing.financial_transactions  ft_cdi   -- alias required here
    INNER JOIN
                billing.cash_application_links  cal
            ON  ft_cdi.key_num = cal.applied_ft_key_num
    INNER JOIN
                billing.financial_transactions  ft_pmt   -- alias required here
            ON  cal.owner_key_num = ft_pmt.key_num
    LEFT OUTER JOIN
                billing.invoice_lines           invl
            ON  ft_cdi.key_num = invl.invoice_key_num
    LEFT OUTER JOIN
                billing.charges                 chrg
            ON  invl.creator_key_num = chrg.key_num
    LEFT OUTER JOIN
                billing.customer_services       cs
            ON  chrg.cs_key_num = cs.key_num
    INNER JOIN
                billing.billers                 bil
            ON  ft_cdi.biller_account_key_num = bil.biller_account_key_num
    INNER JOIN
                billing.formal_entities         fe
            ON  bil.frml_key_num = fe.key_num
WHERE
    -- ... and in the WHERE conditions here?
        ft_cdi.transaction_type <> 'Payment'   -- alias tells me this table is not for payments
    AND ft_cdi.status = 'Approved'
    AND ft_pmt.transaction_type =  'Payment'   -- alias tells me this table is for payments
    AND ft_pmt.status = 'Approved'
    AND ft_cdi.last_user_date >   ft_last_user_date_begin
    AND ft_cdi.last_user_date <=  ft_last_user_date_end
;

My first job that required interacting with SQL regularly involved migrating tens of thousands of lines of legacy queries after dozens of columns and tables had been moved and renamed. This process took years. This process could have taken at most a couple of days had no one used table aliases (or at least avoided them when they were unnecessary).

How?

Consider the following query.

select
employeeid,
a.cust,
b.date,
c.first_name,
c.last_name
on employee as a
inner join customers as b
using(transactionid)
left join transactions c
on c.transactionid = b.transactionid

Don't think too hard about it. I made it up and what it does isn't important. Just imagine you were told transactions.first_name and transactions.last_name have moved to transactions_table.firstname and transactions_table.lastname respectively. Meanwhile, transactions_table is indexed on transaction_datetime_id instead of transactionid. Sure, you could spend a couple of minutes backtracking this new query and swapping out transactions for transactions_table, the join involving c.transactionid for c.transaction_datetime_id, and finally firstname and lastname for first_name and last_name respectively.

Now consider the following query.

select
employee.employeeid,
employee.cust,
customers.date,
transactions.first_name,
transactions.last_name
on employee
inner join customers
on employee.transactionid = customers.transactionid
left join transactions
on transactions.transactionid = customers.transactionid

Now imagine making the same changes. It should be immediately clear you can just run a few blind regular expression based substitutions on the query.

transactions.transactionsid => transactions_table.transaction_datetime_id
transactions.first_name => transactions_table.firstname
transactions.last_name => transactions_table.lastname
(?<![A-Za-z0-9.])transactions(?![A-Za-z0-9]) => transactions_table

I'm not the first person to encounter this problem and I won't be the last (the group I met at a company I joined later went through the same painstaking process).

If you want to blame me for being new to heavy SQL at the time, then please note that my predecessor was a 4 year full-time SQL veteran and was the same person that wrote these queries that needed updating. He was already a year into the process of migrating them when he left and I took over. Tremendous resources are wasted because of unnecessary table aliases. Seriously.


ALTER TABLE - Rename a column This is driving me nuts!

I want to rename a column from read-more to read_more in my blog table

I tried all this:

  ALTER TABLE blog RENAME COLUMN read-more to read_more;
  ALTER TABLE blog CHANGE COLUMN 'read-more' 'read_more' VARCHAR(255) NOT NULL;

And I always get this!

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN read-more to read_more' at line 1

I'm using MySQL 5.5

The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes:

ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;

Here's what worked for me:

ALTER TABLE vm_list CHANGE `vm_notes]` vm_notes VARCHAR(255);

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Yes, I somehow got a column named "vm_notes]" in there.


What is the MAXDOP value for the server which doesn't have NUMA Im using a SQL Server with the below configurations.

4 Core.
8 Logical processors.
No NUMA.
NO hyperthreading.

I read this Doc:

Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server

Since I don't have NUMA and HyperThread.

So what could be my best MAXDOP setting?

For SQL Server 2005 and later versions

Server with single NUMA node    Less than 8 logical processors  Keep MAXDOP at or below # of logical processors

Server with single NUMA node    Greater than 8 logical processors   Keep MAXDOP at 8

Server with multiple NUMA nodes Less than 8 logical processors per NUMA node Keep MAXDOP at or below # of logical processors per NUMA node

Server with multiple NUMA nodes Greater than 8 logical processors per NUMA node Keep MAXDOP at 8

See : Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server


When to use XML data type I'm responsible for creating a database on a project. We have fields that are rarely going to have a value (1 in every 10,000 records) and I'm trying to work out the best way to store this in the database.

As far as I can see I have 3 options:

Add a column in the table for each extra value
Add a linked table which references the original table and has records only where we need to store a value
Use the XML data type in the original table and store all of the values in this.

Are there any other options that I've not considered?

I'm trying work out the pros and cons of each method. As far as I can tell 1 would be the easiest and 2 would take the least amount of space but I'm struggling to find many resources for 3.

Sounds like what you need is sparse columns and filtered indexes and go with option 1. These are fully supported and documented features for exactly this scenario.

  The SQL Server Database Engine uses
  the SPARSE keyword in a column
  definition to optimize the storage of
  values in that column. Therefore, when
  the column value is NULL for any row
  in the table, the value requires no
  storage.

I can't imagine an XML solution performing well in this scenario, it will have a huge overhead of redundant metadata and will be slow to query.

A fourth option: don't use tables. Tables are very badly suited to this kind of data (in fact, to any kind of data that hasn't been force-fitted into tabular form). Just use XML.


Does ALTER INDEX ALL REBUILD use more transaction log space with simple recovery model than rebuilding each index individually? An "ALTER INDEX ALL REBUILD" operation on SQL Server 2012 failed because the transaction log ran out of space. The indexes have never been reorganized or rebuilt, so fragmentation is over 80% on nearly all of them.

The DB uses simple recovery model. I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

In other words, could I reduce transaction log growth by writing a script to perform each rebuild individually? Are there any other factors to consider?

I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

1) Log flushing: the SIMPLE recovery model does not clear the log after every transaction, but at checkpoints. (link for more info)

2a) REBUILD ALL: yes, REBUILD ALL works as a single transaction. The index rebuilds within have their own transactions, but the overall operation isn't fully committed until the end. So yes, you might limit log file growth by rebuilding individual indexes (and possibly issuing CHECKPOINT commands).

2b) Proof! Here, have a demo script. (Built in 2016 dev)
First, set up a test db, with table and indexes:

USE master
GO

CREATE DATABASE Test_RebuildLog
GO

ALTER DATABASE Test_RebuildLog
SET RECOVERY SIMPLE
GO

USE Test_RebuildLog
GO

CREATE TABLE IndexTest
(ID int identity(1,1),
a char(1),
b char(1))

CREATE CLUSTERED INDEX CIX_IndexTest_ID ON IndexTest(ID)
CREATE INDEX IX_IndexTest_a ON IndexTest(a)
CREATE INDEX IX_IndexTest_b ON IndexTest(b)

INSERT IndexTest
(a,b)
VALUES ('a','b'),('z','y'),('s','r')

Now you can compare log activity between REBUILD ALL and rebuilding individually

CHECKPOINT
GO
ALTER INDEX ALL ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

CHECKPOINT
GO
ALTER INDEX CIX_IndexTest_ID ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_a ON IndexTest REBUILD
ALTER INDEX IX_IndexTest_b ON IndexTest REBUILD

SELECT *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation = 'LOP_COMMIT_XACT'
OR Operation = 'LOP_BEGIN_XACT'
GO

Note how the first open transaction (Transaction ID 0000:000002fa for me) isn't committed until the end of the REBUILD ALL, but for the index-by-index rebuilds, they are successively committed.

Rdfozz is correct that's the best way to decide whether your largest index can be rebuilt based on current storage. Just run dm_exec_requests while the operation is happening (or SQL Profiler) to see if all indexes are being rebuilt. I would also consider changing recovery model to bulk logged. This is what I do and there are transaction log backup's during the window still.  See below article
https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


Functional dependencies and candidate key example Hi there I'm still learning for my examination and I got this exercise:

A table is given:

F-Name,           F-Nation,    Team-Name,   Team-Nation
-------------------------------------------------------
Lance Armstrong,     USA,   Discovery Channel,   USA
Jose Luis Rubiera,   ESP,   Discovery Channel,   USA
Jan Ullrich,         GER,   Team Telekom,        GER
Andreas Klöden,      GER,   Team Telekom,        GER
Davide Rebellin,     IT,    Team Gerolsteiner,   GER

a) Which are the candidate keys?

b) Which non-trivial functional dependencies existing?

c) Is the table in 2NF (second normal form)? If not modify the table to be in 2NF.

d) Is the table in 3NF (third normal form)? If not modify the table to be in 3NF.

a) I would say that the candidate keys are (F-Name, Team-Name) or (F-Name, Team-Nation)

b) The dependencies are:

Team-Name -> Team-Nation
F-Name -> F-Nation

c) no it is not in 2NF because Team-Nation is not dependant from the F-Name which is part of the key candidate.

Table after normalization to 2NF:

Table driver

F-Name, F-Nation, Team-Name

Table teams

Team-Name, Team-Nation

d) I couldn't find any transitive relations so I guess the solution of c) would be in 3NF also am I right?

Ok so I can say on a) there are two solutions. If F-Name is Unique the candidate key is F-Name otherwise F-Name, Team-Name

b) The dependencies are:

F-Name -> F-Nation, Team-Name
Team-Name -> Team-Nation

and my solutions on c) and d) are right?


SQL Minimum Row count I have a stored procedure that allows users to pass in parameters.

SELECT *
FROM Table1
WHERE columnA =@paramA, ColumnB=@paramB....

I need to hide the results if there are less than 10 rows, but return them if there are more than 10. Is there a clean way to do this? Any help would be great.

You can use count(*) over() to make sure your result set has that many rows.

You didn't say what to do if the rows = 10, only > or <, so you may need to change the operand to >= based on what you want.

DB FIDDLE

select *
into mytable
from (select 'X' as c1) x
cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))as y(Y)

select * from mytable

--change the 10 to 11 to see it not return
select * from
(
select *, COUNT(*) OVER() CT
from mytable
where c1 = 'X'
) sub
where CT > 10

If the output has some sort of logical order, or even some arbitrary order that fits in with your 10 rows requirement, you could use the ROW_NUMBER() window function, like so:

SELECT *
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY columnA) AS rownumber
    FROM Table1
    WHERE columnA =@paramA AND ColumnB=@paramB
) AS subq
WHERE rownumber > 10


Is a relational database with a dynamic number of tables a good design? I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records.
This table has a fixed structure, so if there are 5 such entities in my system, there will be 5 different tables with the same schema.
The schema is generic with one of the columns in the schema as JSON for flexibility. I do not expect queries based on the fields in the JSON. I expect the following queries on each entity:

On the auto-increment id primary key column with LIMIT and OFFSET where I need to read X rows from the record with id Y.
On the creation date column with LIMIT X.

I expect thousands of such entities to be created on the fly so in turn there will be thousands of tables in the database.
In future when one of these entities have fulfilled their purpose, the table would be simply deleted.
I expect most of these tables to have not more than 100 rows while there will be a few with at least 1M rows as time goes by.
This design makes data easy to query as my application can determine the table name from the entity name.
Is this a bad design?
Is there a limit to the number of tables in a database in RDBMS (the above design is with Postgresql 11 in mind) keeping performance in mind?
Should I use any different datastore to achieve this other than RDBMS? Any suggestions?

Is this a bad design?

Yes.  See comment by LowlyDBA.

Is there a limit to the number of tables in a database in RDBMS ...

Each RDBMS has a different maximum number of tables.  There is a Q&A for this is on Stack Overflow.

Should I use any different datastore to achieve this other than RDBMS?

No. RDBMS should be fine if you design your schema correctly.

Any suggestions?

From your description, a single table with Entity_Name as part of the Primary Key should work. A table with 100M rows is not a problem for today's databases.
create table data_for_entity (
  entity_name     varchar(50),
  entity_id       int,
  entity_value    text, -- or whatever PostgreSQL uses for JSON
  constraint data_for_entity_pk Primary Key (entity_name, entity_id)
);

Your requirements seem to fit an OS's file system more than a RDBMS.


Could not continue scan with NOLOCK due to data movement SQL Server 2005 I am working on a sql database and it got corrupted due to power failure while I am doing some data entry on the system.

I set my database to emergency mode so that I could get some data out of it before dumping the database but I keep getting the error:

  Could not continue with NOLOCK due to data movement

I set the database to offline mode and now when i try to turn it online.It is giving me this error

Set offline failed for Database 'xyz'.

The log scan number (1224:41:1) passed to log scan in database 'xyz' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
An error occurred during recovery, preventing the database 'xyz' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Database 'xyz' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 9003)

The database is corrupt.  Setting it to offline sealed your fate.

If it's mission-critical data that you need to recover, contact Microsoft Support.  It's going to cost $500, but it's worth a shot if you need that data.

Otherwise, restore from backup.

I change the mode of the database to emergency, then restore it with 'RESTORE_WITH_DATA_LOSS', it works only very very few records has been lost, moreover rather than contacting Microsoft guys for that, i've tried another tool like Phoenix data recovery, they also worked and costs only $129.


sp_updatestats vs Update statistics Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

  exec sp_updatestats vs update statistics tablename

updating the tables using sp_updatestats with default value NO will update the statistics with default sampling rate.

similarly updating statistics of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE) will also update the table statistics with default sampling.

So is there any difference between both methods? Am I missing anything here ?

Update :

I know that sp_updatestats runs on all the tables but using UPDATE STATISTICS we can update statistics of specific table.

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

Adding to what is already mentioned by @Gameiswar, the other difference which I know with sp_updatestats, when you run it for all tables of database it ONLY updates statistics of tables which has at least one row changed. BOL says

  For disk-based tables, sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

So when you run sp_updatestats and it says statistics for all tables have been updated this is incorrect/misleading only for tables which has at least one row changed was stats updated.

While going with UPDATE STATISTICS you have quite lot of options to use for stats update.

Plus when you rebuild the index with full scan or with default options the stats are updated for that index so no need to rebuild stats again for it.

PS: Whatever the difference may be, I would not use sp_updatestats as the mechanism it uses to update stats is not good, why should i update stats for table if just one row has changed and I have 100K rows in table, it will just consume resources and cause more issue.

EDIT:

If you want to update stats selectively and do not want to run sp_updatestas use below query to filter out outdated stats. This query uses DMF and would work from SQL Server 2008 R2 SP2 , SQL Server 2012 Sp1 and above, Copied from Erin Stellato's blog

NOTE: Realize that different tables may have different thresholds and you will need to tweak the query above for your databases. For some tables, waiting until 15% or 20% of the rows have been modified may be ok. But for others, you may need to update at 10% or even 5%, depending on the actual values and their skew.

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;

If you want to avoid all such hassle you can also use Ola Hallengren update stats script which takes care of lot of such things.

Sampling rate of SP_Updatestats is very low like 0.05%. Especially on VLDB's
It is advisable to use DBCC Update statistics on each table to update stats with appropriate sampling. Best would be to perform DBCC update statistics with Full scan.
If this is not possible, use sampling rate of 25%.


Is a relation R(A,B,C) with functional dependencies {AB->C, C->B} in BCNF? If not, then how do I make it BCNF while being lossless and dependency preserving?

I can see that the primary key of R is AB. And in BCNF every functional dependency should be of the form [Key] -> [Key or Non-key]. But C->B doesn't satisfy that.

I know I will lose the AB->C dependency if I do any decomposition. But I am unable to figure out which decomposition would be lossless. I would be grateful if someone could help me out.

Given AB->C, C->B we can infer only that AB and AC are keys. It follows that C is not a superkey and therefore R does not satisfy BCNF.

The following is a possible decomposition that would satisfy BCNF

R1{A,B} KEY {A,B}
R2{A,C} KEY {A,C}
R3{C,B} KEY {C}

Based on the following criteria for the definition of BCNF:

  X → Y is a trivial functional dependency (Y ⊆ X)

or

  X is a super key for schema R

I would say that your relation R(A,B,C) is in BCNF with the functional dependencies of {AB → C, C → B}

The reason why is that there is an implicit relationship of {A → C}. B is directly determined by C, but C is also directly determined by the combination of AB. If B were to actually matter in determining C, we'd have a dependency loop.

This implicit relationship allows {AB → C} to fall under "X is a super key for schema R", since A is a primary key of the schema. A → C, C → B, therefore A → BC. Any relation that contains A is a superkey.

The second functional dependency is a trivial dependency, {C → B}.


Caching results of query for part of table that almost never change In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below):

Query1

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..

In our application this query can occure with any number and any combination of conditions so we cannot use any index to somehow speed up this query so a simple sequence scan is being used (actually parallel sequence scan).

The feature of Table1 is as follows: new lines in it change very often and old ones almost never change (lets assume that the lines that often change is lines that created 'today' and the lines that almost never change is lines that created 'not today').

Because of this feature, the following idea emerged: if in our application occurs the need of execution of a Query1 (see above), we can actually not execute Query1, but instead we can execute 2 queries (see below): one for new rows (Query2) and one for old rows (Query3) and further we can summarized the results of these two queries to get total COUNT result. And to speed up overall performance we can perform Caching the results of query for old rows (Query3). Yes, we should clear this cache when some UPDATE occure on old rows, but it happens very rare.

Query2

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..
    AND Table1.creation_date = 'today'

Query3

SELECT
    count (*)
FROM
    Table1
WHERE
    Table1.field1 = 'something'
    AND Table1.field2 = 'something'
    AND Table1.field3 = 'something'
    ..
    AND Table1.creation_date != 'today'

So the question is:

Is this a good idea and it can speed up performance or it is a bad idea and it is better not even consider this approach?

It's best not to use a count(*).  Instead, use a count(PrimaryKey), where the Primarykey is whatever your PrimaryKey happens to be.

For example, if the PrimaryKey is EmplID, then use count(EmplID).  If you don't have a PrimaryKey, create one.  Without a PrimaryKey, the query will always use a table scan (where it looks at every single row to find the data) rather than using an indexed seek. Think of it like a filing cabinet. If all your records are organized alphabetically, you always know where to find customers whose names start with "F". If it's not organized, you have to look through every single piece of paper to find the right customer records.

For each of your WHERE conditions, make sure each field is indexed.  Field1 should be indexed, Field2 should be indexed.  It doesn't matter if the value changes.  As long as the field is indexed and you REBUILD or RECREATE the index often, it should work just fine.

You shouldn't need Query2 and Query3.  If you decide to use Query2, make sure the Creation_Date is indexed as well.


PostgreSQL alternative to SQL Server’s `try_cast` function Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error.

This makes it possible to then use a CASE expression or a coalesce to fall back on. For example:

SELECT coalesce(try_cast(data as int),0);

The question is, does PostgreSQL have something similar?

The question is asked to fill in some gaps in my knowledge, but there’s also the general principle that some prefer a less dramatic reaction to some user errors. Returning a null is more easily taken in one's stride in SQL than an error. For example SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. In my experience, user errors are sometimes better handled if there is a plan B.

If casting from one specific type to one other specific type is enough, you can do this with a PL/pgSQL function:

create function try_cast_int(p_in text, p_default int default null)
   returns int
as
$$
begin
  begin
    return $1::int;
  exception
    when others then
       return p_default;
  end;
end;
$$
language plpgsql;

Then

select try_cast_int('42'), try_cast_int('foo', -1), try_cast_int('bar')

Returns

try_cast_int | try_cast_int | try_cast_int
-------------+--------------+-------------
          42 |           -1 |

If this is only for numbers, another approach would be to use a regular expression to check if the input string is a valid number. That would probably  be faster than catching exceptions when you expect many incorrect values.

With PostgreSQL, internally when a coercion fails it generates a fatal exception using ereport. That's unrecoverable in coercions.

Sample Data

Let's assume an error rate of 1/5

CREATE TABLE foo AS
  SELECT CASE WHEN x%5=0 THEN 'a' ELSE x::text END
  FROM generate_series(0,1e6) AS gs(x);

Black-listing bad data.

The normal solution to this problem is to accept liberally when you create types. That's pretty much how things work now. If you need to protect against some kind of bogus input, rather than catching in the case of failure just set it to null before it fails.

SELECT NULLIF(x, 'a')::int
FROM ( VALUES ('6'),('a'),('7') ) AS t(x);

If you want you can put that into an IMMUTABLE SQL statement too.

CREATE FUNCTION safer_but_not_totally_safe_coercion( i text )
RETURNS int AS $$
  SELECT NULLIF(i, 'a')::int;
$$ LANGUAGE sql
IMMUTABLE;

-- Inlined and fast.
SELECT safer_but_not_totally_safe_coercion(x, 'a')::int
FROM ( VALUES ('6'),('a'),('7') ) AS t(x);

You can also use regexes and whatever else you want as far as verification is concerned.

EXPLAIN ANALYZE SELECT safer_but_not_totally_safe_coercion(x) FROM foo;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..21925.02 rows=1000001 width=5) (actual time=0.025..210.685 rows=1000001 loops=1)
 Planning time: 0.173 ms
 Execution time: 240.462 ms
(3 rows)

Try/Catch

This method is way slow.

EXPLAIN ANALYZE SELECT try_cast_int(x) FROM foo;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..264425.26 rows=1000001 width=5) (actual time=0.104..7069.281 rows=1000001 loops=1)
 Planning time: 0.056 ms
 Execution time: 7151.917 ms
(3 rows)

If you need it then by all means you need it, however it wouldn't be the first tool I grab for.


Scaling out SQL Server and syncing data across multiple machines I don't have expertise in architecting databases, and I've been teaching myself new stuff every day. I'd like to make an Internet-scale application using SQL Server as the data store. I haven't found any good information online with regards to scaling out SQL Server.

My understanding is that scaling out is great for write throughput, but it doesn't necessarily scale reads. A simple example (which is relevant in my case) is, if data is sharded by posting user id, status 1 posted by user X living in shard A will have all its likes and comments across the whole federation. So, if I need to fetch the comments on this status, I need to hit every database and merge and sort/filter results in application memory. This is bad for the databases because they are kept busy and bad for the web servers because I will be using CPU and RAM for post processing the objects. Ideally, I'd like to write to one database and read from one database for maximum scalability.

Now, what I'm thinking of doing is, instead of sharding by posting user id, shard by receiving user id. So, if user X posts status 1, user Y living in shard B can insert a comment in shard A, and I can enforce a parent-child relationship between the status and the comment. User Z living in shard C can insert a like in shard A for the comment, so the comment and the like can constitute a parent-child relationship. The benefit of this approach is I query only one database to get all the comments and likes for a specific status rather than naively querying every single shard.

However, I need to get results like "comments on status 1 by people who are male or 18+ years old". This is a crucial functionality I want to implement. I still have to hit other databases to get information about the users. In order to eliminate this, I'm thinking of creating a sync group where one database (hub) syncs all user deltas to all shards (every 5 minutes). I'm okay with eventual consistency though it has its own problems for example, if a user deletes their account, from the time the account is deleted to the time the delta is persisted to a shard, other users will not see the change potentially adding child objects to objects created by that user. This seems to me a data integrity issue.

I'm also aware of replication and caching to increase read throughput.

My question is, which approach should I pursue? If I choose the second one, will I have trouble syncing data across potentially hundreds or thousands of servers? Not to mention the hub is essentially a single point of failure.

Creating a Scale Out database at Internet scale is pretty huge step. You will face a lot of issues that are not critical on a single big database. From your notes I see that you understand some of the basic issues you face.

Since Microsoft has papers on using SQL Server for scale out, I suggest that you study those first. Your scale out strategy will need to take into account the database server you choose.

For Microsoft SQL Server you should first study: http://msdn.microsoft.com/en-us/library/aa479364.aspx

This paper discusses the decisions that you need to make and why they are important. It offers 5 SQL Server strategies for scaleout:

  • Scalable Shared Databases

  • Peer-to-Peer Replication

  • Linked Servers

  • Distributed Partitioned Views

  • Data-Dependent Routing

As you go down the stack, things get more complicated, but also provide more powerful ways of scaling out.

I tried PartitionDB* yesterday. Basically, from my understanding, it takes DPV (Distributed Partitioned Views) and does the work for you, maybe one of their union examples will work for you.

Note, you need to gather that shard/partition field ready in all your sharded/partitioned tables.

* I am a beta user, but not affiliated with this product otherwise.


Disable autocommit for SQL Server connection in Oracle SQL Developer I am using Oracle's SQL Developer to execute queries against my SQL Server 2008 database as I simply can't work with SSMS as long as it doesn't support Ctrl + Return to execute statements...

Anyway, statements in SQL Server seem to be autocommit even when using Oracle SQL Developer. Is it possible to change this on a permanent basis? BEGIN TRANSACTION is not a solution for me.

SQL Server uses autocommit mode by default. This cannot be changed permanently.

There are two ways implicit transactions (non-autocommit) can be turned on:

At the server level such that new sessions use it by default, using sp_configure 'user options' -- this may or may not work depending on how SQL Developer was implemented.
For a session, using SET IMPLICIT_TRANSACTIONS ON.

In SQL Developer you can try this technique:

/*sqldev:stmt*/begin transaction;
INSERT ...
update ...
/*sqldev:stmt*/rollback;

See:
http://stackoverflow.com/questions/17826779/disable-autocommit-in-sql-developer-when-using-mysql


Oracle 12c: Password File Questions For one of my assignments I have been attempting to demonstrate password file functionality. However, I have run into some unexpected behaviors. I have tried searching around for answers but can't find any relevant ones; additionally, behavior differs from what the documentation specifies in some scenarios.

I am using 12c and have made sure that the remote_login_passwordfile parameter is set to EXCLUSIVE.

I'm sure I'm probably just missing some base knowledge as the behaviors are consistent; the problems I am having are as follows:

Deleting the password file itself (without replacing it) and then
doing a subsequent lookup of v$pwfile_users removes all of the
sys-privileged users from the CBD, but users from my PDB still
remain in the lookup. Are PDB passwords stored somewhere other than
that file? How are they showing up if the file no longer exists?
The IGNORECASE option for the new file seems to only have an effect on common users rather than PDB users; newly-assigned CDBs cannot login without the correct case, but PDB users can, again making me wonder about some separate password storage.
In both the CDB and the PDB, the ENTRIES option seems to have no effect - despite setting it to 2, I can continually add way more than 2 users in both containers.
I wanted to demonstrate that the password files actually worked by doing some sort of sysoper operation with one of my newly-privileged accounts, such as shutting the database down. I can do this in the PDB but it will not let me do it in the CDB despite the documentation explicitly stating that sysoper users have that privilege. Am I missing some other piece of information that would allow this to happen? The error I get is insufficient privileges (and yes, I have made sure to connect using as sysoper).

Any insights are greatly appreciated - thanks!

Deleting the password file itself (without replacing it) and then doing a subsequent lookup of v$pwfile_users removes all of the sys-privileged users from the CBD, but users from my PDB still remain in the lookup. Are PDB passwords stored somewhere other than that file? How are they showing up if the file no longer exists?

The password of PDB users(local users) as well as common users with administrative privileges are stored in data dictionary in encrypted form.

SQL> select password from sys.user$ where name='JAY';

PASSWORD
--------------------------------------------------------------------------------
4FF80DF8D8DBE02E

SQL> select username, common from dba_users where username='JAY';

USERNAME      COM
---------     ---
JAY           NO

If no password file exit common user is unable to get sysdba privilege.

SQL> create user testuser identified by testuser container=all;

User created.

SQL> grant sysdba to testuser;
grant sysdba to testuser
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

Unable to add common users to the password file as I have removed the password file prior to creating this user.

But assigning SYSDBA to local users is possible!

SQL> alter session set container=orapdb1;

Session altered.

SQL> create user newuser identified by newuser container=current;

User created.

SQL> grant sysdba to newuser;

Grant succeeded.

It confirms that the password for PDB administrative users are stored in data dictionary rather password file.

  The IGNORECASE option for the new file seems to only have an effect on common users rather than PDB users; newly-assigned CDBs cannot login without the correct case, but PDB users can, again making me wonder about some separate password storage.

Same applies here. As PDB administrative privileged users(local users) are not stored in password file IGNORECASE and ENTRIES do not apply to them.

  In both the CDB and the PDB, the ENTRIES option seems to have no effect - despite setting it to 2, I can continually add way more than 2 users in both containers.

It allows us to store the password of the privileged user until the Operating System block is filled(generally up to 4 passwords per block) as per the Oracle Documentation. I have demonstrated this behavior after the excerpt from Oracle Documentation.

  ENTRIES

  This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

I have created password as:

[oracle@ora12c dbs]$ orapwd file=orapworacdb entries=2

Enter password for SYS:

Then I created four test users and tried to grant the SYSDBA privilege but the grant failed for fourth user. Becuase there were already four users with SYSDBA privilege including SYS.

SQL> select * from v$pwfile_users;

USERNAME               SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                TRUE  TRUE  FALSE FALSE FALSE FALSE      0
C##_ABC                TRUE  FALSE FALSE FALSE FALSE FALSE      0
C##_ABC2               TRUE  FALSE FALSE FALSE FALSE FALSE      0
C##_ABC3               TRUE  FALSE FALSE FALSE FALSE FALSE      0

When I tried to grant SYSDBA to fourth user C##_ABC4 I got the following error.

SQL> grant sysdba to c##_abc4 container=all;
grant sysdba to c##_abc4 container=all
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapworacdb' is full

  I wanted to demonstrate that the password files actually worked by doing some sort of sysoper operation with one of my newly-privileged accounts, such as shutting the database down. I can do this in the PDB but it will not let me do it in the CDB despite the documentation explicitly stating that sysoper users have that privilege. Am I missing some other piece of information that would allow this to happen? The error I get is insufficient privileges (and yes, I have made sure to connect using as sysoper).

It depends upon how you have granted the SYSOPER role. To shutdown the database instance user should have SYSOPER privilege on all containers.

The following demonstrate the scenario.

I have common user call C##_TEST.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:47:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select common from dba_users where username='C##_TEST';

COM
---
YES

I have granted SYSOPER to the user for the ORACDB(CDB) only.

SQL> grant sysoper to c##_test container=current;

Grant succeeded.

Then I have connected to container database using C##_TEST user as SYSOPER and tried to shut down the database and got the ORA-01031 error.

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:05 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
ORA-01031: insufficient privileges
SQL>

Now I again granted the SYSOPER privilege with the option container=all. And this time the user can shut down the database.

[oracle@ora12c dbs]$ sqlplus sys@oracdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:49:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant sysoper to c##_test container=all;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

[oracle@ora12c dbs]$ sqlplus c##_test/test@oracdb as sysoper

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 2 08:50:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

follow the following video for password file in oracle 12c

https://www.youtube.com/watch?v=Y7IrPNUEOSM&t=51s


Delete databases from oracle 12c I want to delete multiple databases and its entries. These instances are not getting started. I want to delete data files also.

EDIT: I am thinking delete all data files. Please help on below concern.

Shall I delete data files manually? Will it cause any problem to my working database instance?

The easiest and safest way to delete a database is using the RMAN DROP DATABASE command.

If on Linux, set up your ORACLE_HOME and ORACLE_SID environment variables appropriately then start the database(s) using startup mount exclusive restrict.  Then drop it using RMAN's DROP DATABASE.  If you want to delete the backups/contents of the FRA as well, then use DROP DATABASE INCLUDING BACKUPS.

You could also use the GUI (DBCA - Database Creation Assistant), but this may be trickier depending on the environment.

Oracle has a Deinstall program. Go into the Oracle home of the database you want to remove. In the bin directory you will see a deinstall.pl (*nix) and a  deinstall.bat (Windows). Depending on which OS you are using start either one or the other.

This will delete everything except the inventory file. You will then need to go into the OS and remove the database entry from the oratab file.


Does reporting webservice url contain the function that helps to export the report to the client version? The reporting webservice url contains the webservice url that contains all the information of the Reports which are created in SSRS. As the report has been created on reporting service, i want to export/send that report to my client using the SOAP feature.

http://localhost/reportserver_INSTANCE2012/reportservice2005.asmx?wsdl

While creating the project under soapUI using the above wsdl, it returns 105 functions like

Function Name

CreateBatch()
CancelBatch()
CancelJob()
CreateDataDrivenSubscriptionMethod()
CreateDataSource
CreateFolder
CreateLinkedReport
createModelReport
createReport
createReportHistorySnapshot
createResource
CreateRole
CreateSchedule
CreateSubscription
DeleteItem
DeleteReportHistorySnapshot
DeleteRole
DeleteSchedule
DeleteSubscription
DisableDataSource
EnableDataSource
ExecuteBatch
FindItems
FireEvent
FlushCache
GenerateModel
GetCacheOptions
GetDataDrivenSubscriptionProperties
GetDataSourceContents
GetExecutionOptions
GetExtensionSettings
GetItemDataSourcePrompts
GetItemDataSources
GetItemType
GetModelDefinition
GetModelItemPermissions
GetModelItemPolicies
GetPermissions
GetPolicies
GetProperties
GetRenderResource
GetReportDefinition
GetReportHistoryLimit
GetReportHistoryOptions
GetReportLink
GetReportParameters
GetResourceContents
GetRoleProperties
GetRoleProperties
GetScheduleProperties
GetSubscriptionProperties
GetSystemPermissions
GetSystemPolicies
GetSystemProperties
GetUserModel
InheritModelItemParentSecurity
InheritParentSecurity
ListChildren
ListDependentItems
ListEvents
ListExtensions
ListJobs
ListModelDrillthroughReports
ListModelItemChildren
ListModelPerspectives
ListReportHistory
ListRoles
ListScheduledReports
ListSchedules
ListSecureMethods
ListSubscriptions
ListSubscriptionsUsingDataSource
ListTasks
Logoff
LogonUser
MoveItem
PauseSchedule
PrepareQuery
RegenerateModel
RemoveAllModelItemPolicies
ResumeSchedule
SetCacheOptions
SetDataDrivenSubscriptionProperties
SetDataSourceContents
SetExecutionOptions
SetItemDataSources
SetModelDefinition
SetModelDrillthroughReports
SetModelItemPolicies
SetPolicies
SetProperties
SetReportDefinition
SetReportHistoryLimit
SetReportHistoryOptions
SetReportLink
SetReportParameters
SetResourceContents
SetRoleProperties
SetScheduleProperties
SetSubscriptionProperties
SetSystemPolicies
SetSystemProperties
UpdateReportExecutionSnapshot
ValidateExtensionSettings

I would like some one to help me out which function is used to send the existing report of SSRS reporting to my client. I am using the wsdl feature to connect directly to the report server and want to use function listed on above, to send the report in any format like pdf etc.

follow the given instruction, and should be able to print the report in format passed format:

https://surendragurjar.wordpress.com/2015/07/11/ssrs-wsdl-web-service-endpoint-using-java/


update one column based on query containing another (joined) table Consider the following select query:

SELECT s.id, s.event, s.column, e.oldcolumn
FROM section as s
INNER JOIN event as e
ON s.event = e.id
WHERE s.id = 1

Now I wish to update the section table so that the values are equal to the oldcolumn from the event table.

I tried:

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."section"
INNER JOIN public."event"
on public."section".event = public."event".id
WHERE s.id = 1

However this, on postgresql, returned the following error:

ERROR:  table name "section" specified more than once
SQL state: 42712

How would I do such a thing, using joins inside an update query?

Disclaimer, I don't use PostgreSQL but I'm pretty familiar with SQL Server.

From what I understand in the PostgreSQL documentation here, I think the error you are getting is caused by the inclusion of public."section" INNER JOIN in your FROM statement. When using UPDATE you specify the table you are updating so adding public."section" in the FROM is not necessary. Per the documentation "Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item)."

If you change your code to what I have below, I think it would resolve your issue. Though I'm not sure about the quotation marks you have, I just copied your format. The main difference being, only have the tables you're joining to in the FROM and move the ON to the WHERE.

UPDATE public."section"
SET public."section".column = public."event".oldcolumn
FROM public."event"
WHERE public."section".event = public."event".id AND public."section".id = 1

This is a complete example, but check the update sentence at the end also check this example

DECLARE @section TABLE
(
    id  INT PRIMARY KEY,
    event int,
    [column] int
);

INSERT INTO @section(id, event,[column])
VALUES
    (1,3,77),
    (2,2,55),
    (3,4,88),
    (4,6,44),
    (5,1,33);

DECLARE @event TABLE
(
    id  INT PRIMARY KEY,
    oldcolumn int
);

INSERT INTO @event(id, oldcolumn)
VALUES
    (1,177),
    (2,155),
    (3,188),
    (4,144),
    (5,133);

SELECT * FROM @section s

SELECT s.id, s.event, s.[column], e.oldcolumn
FROM @section s
INNER JOIN @event e ON s.event = e.id
WHERE s.id = 1

UPDATE s
SET s.[column] = e.oldcolumn
FROM @section s
INNER JOIN @event e ON s.event = e.id
WHERE s.id = 1

SELECT * FROM @section s


Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL) Loan table associated to due table.

also

Invoice table associated to due table.

In due table either one of them foreign key should present and other one should be null.

What kind of constraint will be perfect ?

Thanks.

As it sounds, you cannot solve this with one constraint, but nothing prevents you from defining more of them.  I believe something like this should work:

CREATE TABLE due (
    ...,
    d_invoice_id REFERENCES invoice (i_id),
    d_loan_id REFERENCES loan (l_id),
    ...,
    CHECK (   (d_invoice_id IS NULL AND d_loan_id IS NOT NULL)
           OR (d_invoice_id IS NOT NULL AND d_loan_id IS NULL))
);

A column that has a foreign key defined on does not have to be NOT NULL, unlike the column it references.  Apart from that, you may define checks of arbitrary complexity - it is advisable to keep complexity at its necessary minimum, though.

I believe you cannot have such a contraint. You may have a column contraint or a table constraint. When working on foreign keys, they are on the table that "points" to the other table, and cannot use columns of other tables. So a constraint on table invoice can only check for invoice columns and cannot access loan columns.

You might work around the problem using triggers on both load and invoice tables. A trigger is function and can operate on any other table, beside the one it is attached.


GeoNear with Mongoose This is my mongoose schema -

var userDestinationSchema = mongoose.Schema({

    uid: String,
    update_time: Date,

    location:{                  //<INDEXED as 2d>
        lon: Number,
        lat: Number
    }
});

var userDestinationModel = mongoose.model('userDestinationModel', userDestinationSchema);

How do I query the model for geoNear?

I tried doing this and it ain't working.

userDestinationModel.geoNear([parseInt(req.params.lon,10),parseInt(req.params.lat,10)],
              { maxDistance : 50, spherical : true },
              function(err, results, stats) {
                   console.log(results);
                });

maxDistance : 50

What is the unit? Km?

I can answer the distance part, but I've never used geoNear with Mongoose I'm afraid - a good way to tell if the issue lies in how you are calling things in Mongoose versus the query itself would be to try it on the mongo shell first to verify that it is returning the results you expect there.

In terms of the distance, for legacy 2d points (which you appear to be using), the $maxdistance is specified in radians - there is a page here which outlines how to use radians with spherical geometry calculations.  If you use GeoJSON instead (which I would recommend), then it is specified in meters (reference).

max distance is in meters in geoNear.

userDestinationModel.geoNear(
   {
     near: { type: "Point", coordinates: [ -73.9667, 40.78 ] },//lat long
     spherical: true,
     maxDistance: 50 //meter
   }
)


Normalization of library book table with multiple authors I have a database structure with two tables:

The first one is called Author, has two columns (AuthID, AuthName) and its Primary Key is (PK_AuthID).
The second one is called Book and has three columns (BookID, BookTitle, AuthID). Its Primary Key is (PK_BookID), and has (FK_AuthID) defined as a Foreign Key that referes to Author table.

The question is: If one Book has more than one Author, what is the best design pattern to follow in order to maintain the second Author, and at the same time keep the Book table without NULL values?

The only sensible design I can think of - since books can have 20 authors - is to always use a junction table and don't bother storing the AuthorID in the book table some of the time (and don't even think about storing a comma-separated list of AuthorIDs, please, or adding Author2, Author3, ... columns). This will just make queries complex.

BookAuthors(BookID (FK points to Book), AuthorID (FK points to Author))

In this case, you can remove the AuthID column from the Book table. Don't bother abbreviating to AuthID - you don't really think that makes you more productive or makes the code easier to read, do you?

Also, as a general rule, don't spend time trying to worry about how you will eliminate NULLs everywhere.

AuthID does not belong in the Book table at all because a book can have more than one author. My suggestion:

Author (AuthID*, AuthName)
Book (BookID*, BookTitle)
BookAuthor (BookID*,AuthID*)

*=key attributes(s).

No nulls required.


mysql: Show GRANTs for all users MySQL's SHOW GRANTS shows the permissions of the current user.

Is there a way to log in as root and show the permissions of all users?

select * from information_schema.user_privileges;

EDIT:

As mentioned by Shlomi Noach:

  It does not list database-specific, table-specific, column-specific,
  routine-specific privileges. Therefore, the grant GRANT SELECT ON
  mydb.* TO myuser@localhost does not show in
  information_schema.user_privileges. The common_schema solution
  presented above aggregates the data from user_privileges and other tables
  to give you the full picture.

If you are administering databases often, you will likely want to keep tight privileges. You can use a stored procedure to quickly run a check. This example works in mariadb might need a tweak to work with the standard mysql version.

Using the answer from Mansur Ali with a little tweak reordering the columns and adding in some ordering to better organise the output.

Using a root login:

USE mysql;
DELIMITER //

CREATE PROCEDURE ShowPrivs(start, end)
BEGIN
    SELECT Db, User, Host, Insert_priv, Update_priv, Delete_priv, Create_tmp_table_priv, Alter_priv FROM mysql.db order by Db, Host, User ASC;
END;
//

DELIMITER ;

You could change the procedure to check mysql.user table instead.

Usage, using a root login:

USE mysql;
CALL ShowPrivs();

I used mysql workbench on Ubuntu to run the create procedure part of this answer.

As an aside and a little off the topic here but, you could also have a procedure to show unknown hosts or users. An example for unknown hosts:

USE mysql;

DELIMITER //
CREATE PROCEDURE `ShowUnknownHosts`(IN Hosts_String VARCHAR(200))
BEGIN
    SELECT user,host FROM user
    WHERE FIND_IN_SET(host, Hosts_String) = 0;
END//

DELIMITER ;

Usage note: Supply a string of hosts separated by commas so only one set of '' is used:

CALL ShowUnknownHosts('knownhost1,knownhost2');

You could also make the column variable by including another parameter in the procedure and call it with ShowUnknownHosts(user,'user1,user2'); for example.


Parallel Statistics Update In SQL Server 2008 or later, is UPDATE STATISTICS WITH FULLSCAN a single threaded operation or it can use parallelism? How about update statistics with default sampling - can it use parallelism? I don't see an option specifying MAXDOP with update stats.

Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005":

Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form:

SELECT
    StatMan([SC0])
FROM
(
    SELECT TOP 100 PERCENT
        [Column] AS [SC0]
    FROM [Table] WITH (READUNCOMMITTED)
    ORDER BY [SC0]
) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 16)

Notice the MAXDOP hint there (though there is no way for a user to specify the hint manually). Where sampled statistics are gathered, the internal query uses the TABLESAMPLE clause, which prevents parallelism. The engine also generates a MAXDOP 1 hint on the internal query, which is a bit redundant.

If you ever need to reduce the parallelism, the MAXDOP hint in the internal query can be overridden using Resource Governor (Enterprise only).

SQL Server 2016 adds parallel sampled statistics update.

UPDATE STATISTICS does not have any sort of internal parallelism. It does not matter if you are running with either FULLSCAN or SAMPLING. Of course, you can run several UPDATE STATISTICS commands at once, each on a different connection, through multiple SQL Agent jobs or some other contrivance. Depending on your exact situation with hardware and data, you may find that simply reindexing the tables is faster than UPDATE STATISTICS with FULLSCAN and possibly a better option.


Need to maintain transactional consistency of the data across both servers, need to achieve these goals without manual intervention I got this question about MS SQL Server 2012 administration, two production servers in the same data center. You need to ensure that database remains available if a catastrophic server failure or a disk failure occurs.

You need to maintain transactional consistency of the data across both servers. You need to achieve these goals without manual intervention.

The correct answer for the above question is

>>
Two servers configured on the same subnet
SQL Server Availability Group configured in Synchronous-Commit Availability Mode
<<

But I think the correct answer should be

    Two servers configured in Windows Failover Cluster in the same data center SQL Server configured as a clustered instance<<

The tricky part is in this requirement:

  You need to ensure that database remains available if a catastrophic
  server failure or a disk failure occurs. You need to maintain
  transactional consistency of the data across both servers. You need to
  achieve these goals without manual intervention.

The disk failure part means a failover cluster alone won't work because the storage is shared with both nodes. If the storage where the data files live fails, then both nodes will be affected.

However, a 2-node synchronous Availability Group isn't the answer either, because as Microsoft's own documentation points out:

  If primary's session-timeout period is exceeded by a secondary replica, the primary replica temporarily shifts into asynchronous-commit mode for that secondary replica. When the secondary replica reconnects with the primary replica, they resume synchronous-commit mode.

Read further in that link in the "Factors That Disrupt Data Synchronization" section, and Microsoft elaborates on the reasons why you can't guarantee that a 2-node AG will not lose data on failover.

So what's the right answer for SQL Server 2012?

There isn't one. You can't guarantee zero data loss with 2 independent SQL Server 2012s without third party tools (like SAN replication, and even then, there's a ton of work involved.) I'm guessing the question came from a test or certification written by somebody without real-world experience. That wouldn't be the first time, and it won't be the last.

Is there a right answer for later versions?

Yes, SQL Server 2017 introduced a new REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting at the Availability Group level. The default is 0, which means as long as the primary receives the transaction, it's committed. You can change that to 1 (or more), which means that if at least that number of secondaries don't also commit the transaction, then the transaction fails.

The first scenario is actually the more correct implementation.  In future versions of SQL Server (starting with 2016), the standard "Failover cluster" model is deprecated.  Microsoft is shifting to the HA (High Availability) model via SQL Server Availability groups, which is essentially Mirroring and Clustering mixed together (no need for a witness server), with the data completely replicated to each node in the group.

Failover time is < 1 second, according to Microsoft professionals, as opposed to standard Failover Clustering, which can take 1+ minute for a successful automatic failover.  SQL Server Availability Groups would be the way to go from this point forward.


SELECT * Returns 1 Record, But SELECT COUNT(*) Shows 3k+ Records I imported a csv file that had more than 3k records into a database table in SQL Server using the Import Wizard.  I do not receive any errors, and the import wizard shows success, with 3217 records imported.  If I run the following statement, I get 3217 records:  SELECT COUNT(*) FROM tbl.  However, if I try SELECT * FROM tbl, only one record is returned.  What's going on here?

Using SQL Server 2014 Management Studio.

So I figured this out less than a minute after I hit post, despite thinking about it and googling for a bit.

So the problem is that I ran a procedure earlier that deleting some duplicate values in a table using SET ROWCOUNT 1 and a WHILE loop.  I realize that I didn't actually set rowcount back to zero (SET ROWCOUNT 0) after the break in the loop.  After doing that, I can see all the records now.

It's impossiable,SELECT * FROM tbl will return 3K records.