Is nested view a good database design? I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect statement.

Students

SELECT studentID, first_name, last_name, SchoolID, ... FROM students

CREATE VIEW vw_eligible_student
AS
SELECT * FROM students
WHERE enroll_this_year = 1

Teachers

SELECT TeacherID, first_name, last_name, SchoolID, ... FROM teachers

CREATE VIEW vw_eligible_teacher
AS
SELECT * FROM teachers
WHERE HasCert = 1 AND enroll_this_year = 1

Schools

CREATE VIEW vw_eligible_school
AS
SELECT TOP 100 PERCENT SchoolID, school_name

FROM schools sh
JOIN
     vw_eligible_student s
     ON s.SchoolID = sh.SchoolID
JOIN
     vw_eligible_teacher t
     ON s.SchoolID = t.SchoolID

At my workplace, I have investigated one of our in-house database application. I checked through the objects found out that there are two or three layers of the view stack each other. So that was remind me about what I read in the past. Can any one help explaining it?

If it is not OK to do so, I want to know that it is limited to just SQL Server or it is for database design in general.

Additional Info:
I updated an example from my company. I change a bit to be more general without too many technical (too many columns in this example). Mostly the nested view we used is based on abstract or aggregated view. For example, we have a large student table with hundred of columns. Say, Eligible Student View is based on students who enrolls this year. And student eligible view could be use other places such as in stored-procedure.

Regardless of platform, the following remarks apply.

(-) Nested views:

are harder to understand and debug

e.g. What table column does this view column refer to? Lemme dig through 4 levels of view definitions...
make it harder for the query optimizer to come up with the most efficient query plan

See this, this, this, and this for anecdotal evidence. Compare to this, which shows that the optimizer is often smart enough to correctly unpack nested views and select an optimal plan, but not without a compilation cost.

You can measure the performance cost by comparing the view query to an equivalent one written against the base tables.

(+) On the other hand, nested views let you:

centralize and reuse aggregations or business rules
abstract away your underlying structure (say, from other database developers)

I've found that they are rarely necessary.

In your example you are using nested views to centralize and reuse certain business definitions (e.g. "What is an eligible student?"). This is a valid use for nested views. If you are maintaining or tuning this database, weigh the cost of keeping them against that of removing them.

Keep: By keeping the nested views you incur the advantages and disadvantages enumerated above.
Remove: To remove the nested views:

You need to replace all occurrences of the views with their base queries.
You must remember to update all relevant queries if your definition of eligible student/teacher/school changes, as opposed to just updating the relevant view definition.


Delete the maximum ID row How can I find duplicate rows and for each group of duplicate rows delete the max ID row, in MySQL?

Using this query, I can find duplicate rows:

SELECT * , COUNT( awb_no ) c
FROM tbl_cod_rpt_COPY
GROUP BY awb_no
HAVING c >1

And this query gives me the maximum ID for each group:

SELECT * , MAX( tbl_cod_rpt_id )
FROM tbl_cod_rpt_COPY
GROUP BY awb_no
HAVING COUNT( awb_no ) >1

Now how would I delete the tbl_cod_rpt_COPY rows matching the MAX( tbl_cod_rpt_id ) values from the query above?

MySQL allows a join in a DELETE statement. You can use this feature to first get a derived table, similar to the query you have that finds all maximum ids for groups that have more than 1 row, then join this derived table to the table itself.

The following assumes that (tbl_cod_rpt_id) is the primary key of the table:

DELETE
    t.*
FROM
    ( SELECT awb_no, MAX(tbl_cod_rpt_id) AS tbl_cod_rpt_id
      FROM tbl_cod_rpt_COPY
      GROUP BY awb_no
      HAVING COUNT(*) > 1
    ) AS dup
JOIN
    tbl_cod_rpt_COPY AS t
  ON
    t.tbl_cod_rpt_id = dup.tbl_cod_rpt_id ;


InnoDB row metadata size (minimum row size) I'm trying to decide whether to normalize a thing further, and need to know what the smallest a row in InnoDB can be (I've got MySQL 5.6).

This is not exactly my case, but for simplicity, let's assume I just have a surrogate PK (bigint) and two TINYINT columns, neither of which can be null. How many bytes can I expect each row to need for storage if I'm gonna have 1 billion of these?

I'd imagine each row requires some metadata (I know it does at least for when there are NULLable columns), but can't seem to find the documentation on how to calculate it.

Then, how much can I expect to be eaten by the free space in the page, and the metadata in the page (or is that all together)?

I want to know so that I can better judge when I would have a long table that is narrow enough to avoid proper normalization.

Each row in InnoDB (let assume COMPACT format) has these headers:

Offsets - one or two byte per variable length type field.
NULL flags - one bit per NULL-able field, aligned to a whole number of bytes.
So called "Extra bytes" - a bunch of flags like is_deleted, pointer to next record ect. Five bytes in COMPACT format, fixed.

Then comes primary key field(s).
Then 6 bytes transaction id and 7 bytes rollback pointer.
Then remaining field values.

For the given row (BIGINT, TINYINT, TINYINT) it's size will be:

0 bytes offsets
0 bytes NULL flags
5 bytes "Extra bytes"
8 bytes BIGINT
6 bytes trx_id
7 bytes roll_ptr
1 byte TINYINT
1 byte TINYINT

So, 28 bytes per record.

But to calculate how much space the table will take you need to take into account:

PAGE header (38+56 bytes)
page trailer
page fill factor
infinum, supremum records
space used by non-leaf pages

Check these slides for more details
http://www.slideshare.net/akuzminsky/data-recovery-talk-on-pluk


Transaction Log file size is increasing in my Azure (IAAS) SQL Server 2014 database Recently we have migrated our application (both web app and database) to Azure (IASS). There we have a VM with SQL Server 2014 installed. Also I have restored all the necessary databases and SQL Server jobs on the server (from the existing On-Premise DB server). All the settings are verified and the application was working fine for a week.

Then we noticed a problem with the log file size (size is increasing  and reached around 42 GB and not able to execute any of the basic query and everything breaks :( ). The same DB and Job ran in on-premise server for a year and  this issue didn't happen.

Some information about the application and DB

The application contains a couple of SQL scheduling jobs which run each 2 hours of a day and the job take maximum of 5-8 minutes to complete.
Application contains less than 200 users and concurrent users are around 20-30.

So please let me know any one of your comments/input on this (Transaction log file size increment) issue. Also please let me know if you need any other information

Do you ever do a full database backup?  That will zap the log, which is what you're looking for.  See T-SQL BACKUP command for syntax.  Not sure what backups look like on Azure, but that should help.


How to do something like UPDATE DELAYED in MySQL I have an averages table that should keep track of an average value over time.  I don't want to have a row for each value, just a single row that continuously updates the average.  What I've come up with is this:

set @value=4;
set @name="myAverageValue";
UPDATE `timing` SET
  `max` = greatest(`max`,@value),
  `average` = `average` + ((@value - `average`) / (`count` + 1)),
  `count` = `count` + 1
WHERE `name` = @name

Many clients may be doing this at the same time, and I don't want there to be any locking issues.  I don't care what order the updates are run in, since in the end it will all end up the same.  I just want to have a query that sends the UPDATE to the database, and it will process it eventually, similar to an INSERT DELAYED.  Does UPDATE LOW_PRIORITY do this, or does that cause the client to wait until it is available?

While there is no such command as UPDATE DELAYED, you can create this mechanism in a rather strange way. Please follow my rather crazy train of thought...
SCENARIO : Let's say you have just a single DB Server called M1

You want to fire UPDATEs at a single table called mydb.timing
You don't want UPDATEs to log jam the table

SOLUTION : Serialize UPDATEs
MECHANISM : Would you believe MySQL Replication ?
That's right, I said MySQL Replication.
You are probably saying right now

How in the world would having a Slave create UPDATE DELAYED ?

Here is how you do it:
STEP 01 : Using another DB Server (call it M2), create a MySQL Instance with binary logging enabled.
STEP 02 : mysqldump all user databases with no data, just structures (called it MySQLSchema.sql)
STEP 03 : Edit MySQLSchema.sql, change all ENGINE=MyISAM into ENGINE=BLACKHOLE
STEP 04 : Edit MySQLSchema.sql, change all ENGINE=InnoDB into ENGINE=BLACKHOLE
STEP 05 : Run source MySQLSchema.sql on M2.
STEP 06 : Run RESET MASTER; on M2
STEP 07 : Add replicate-do-table=mydb.timing to /etc/my.cnf on M1
STEP 08 : service mysql restart on M1
STEP 09 : Setup replication from M2 to M1
STEP 10 : In your application, fire your code
set @value=4;
set @name="myAverageValue";
UPDATE `mydb.timing` SET
  `max` = greatest(`max`,@value),
  `average` = `average` + ((@value - `average`) / (`count` + 1)),
  `count` = `count` + 1
WHERE `name` = @name

only at M2
That's it. No locking can ever happen on M2 since mydb.timing on M2 uses the BLACKHOLE Storage Engine (a.k.a /dev/null Storage Engine). Having MySQL Replication from M2 to M1 forms a queue that serializes UPDATEs. The delay appears in the form of MySQL Replication's asynchronous operation. Replication lag (in this instance only) is totally acceptable since you are looking for UPDATE DELAYED.
Give it a Try !!!
CAVEAT
This turns MySQL Replication upside down because the production server becomes a Slave to a BlackHole Server. With this strange topology, you can also implement DELETE DELAYED.
Make sure you include this option
[mysqld]
expire-logs-days=1

on M2. Otherwise, lots of UPDATEs makes for lots of binlogs


SQL Server not using all cores I have a server with 6 cores, I've installed SQL Server 2012 web edition, based on core licensing, allowing up to 16 cores.
However, it only uses 4 cores.
In the error log file:

2015-07-09 14:00:32.52 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Web Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
2015-07-09 14:00:32.70 Server      SQL Server detected 6 sockets with 1 cores per socket and 1 logical processors per socket, 6 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

Should I define something else to use all cores?

Thank you.

The problem was with the VM provider.
Sql Server 2012 Web license states it's limited to 4 sockets or 16 cores, so if you want to use 16 cores you need a machine with 4 sockets that have 4 cores each.
In my case I had 6 sockets with one core each, that's why only 4 were usable.

Hope this helps future developers facing a similar problem.


Search entire database(210 tables) for a unique Id (PK) and get the table name Rewritten Question:

I need to look through an entire database, 210 tables for a unique ID that is a number.

I have limited access to my database through things called nodes (Visual programming.)

These nodes allow for simple querys like SELECT x FROM x WHERE x I believe I can use most commands here.

I'm unfamilar with sql and thought I might be able to get some help here.

Below you can see an example of a node where I can type in commands.

and below that my database structure.

What i'm looking to do is look through the entire database for a unique number, this number changes dynamically but can always be found in the "Id" column of every table. This column is also a Primary Key.

By searching through the database and finding this dynamic unique number in one of the 210 tables I then need to be able to get the table name that the data is located in and the row information ie; all the different column entries for that row.

Example:

more info:

In your query:

SELECT table_name
FROM information_schema.columns
WHERE (:uniqueid ) IN (SELECT 'Id'
                       FROM information_schema.columns
                       WHERE 'Id' = (:uniqueid ))

you are trying to mix data and meta-data. information_schema contains only meta-data so you won't find :uniqueid in there. You will have to extract meta-data first, and then iterate over that some how. Assuming all columns are named id (which is a bad choice, but nothing you can avoid if I get it right), something like the following pseudo-code:

c1.execute("SELECT table_name FROM information_schema.columns WHERE column_name = id")
for row in c1.fetchall():
    t = row[0]
    c2.execute("SELECT 1 FROM %s WHERE id = :uniqueid" % (t) )
    if c2.fetchone():
        print "Found %s in %s" %  (:uniqueid, t)
        sys.exit(0)

will print the first table where there is an id = :uniqueid

Another option is to generate a union all for all tables:

SELECT 't1' from t1 where id = :uniqueid
UNION ALL
SELECT 't2' from t2 where id = :uniqueid
UNION ALL
...
UNION ALL
SELECT 't210' from t210 where id = :uniqueid


Why are these two HADR DMVs reporting different states? SQL Server 2012 (11.0.5058.0) Enterprise Edition

We have 8 Availability Groups in a 2(HA)+1(DR) cluster and our monitoring DMVs are reporting results that confuse me. 6 Availability Groups are configured for HA and DR, 1 is configured for HA only, and 1 is configured for DR only.

Each of the 6 HA/DR Availability Groups have "SQLB" as a primary and "SQLA" as a secondary (synchronous) HA replica and "SQLC" as a secondary (async) replica.

On both secondaries:

SELECT dhags.group_id, dhags.synchronization_health_desc
FROM sys.dm_hadr_availability_group_states dhags

reports that all Availability Group replication sync health are NOT_HEALTHY and

select replica_id,synchronization_health_desc
from sys.dm_hadr_availability_replica_states

reports that all replicas have a sync health of HEALTHY.

The primary replica reports all Availability Groups and replicas with a sync health of HEALTHY.

While I understand that one reports on replica sync health and the other reports on AG sync health, it seems logical to me that if the more granular (AG) state was not healthy, that would affect the overall health of the broader context (replica). I cannot find MSDN documentation that describes how the health is determined at each level.

Why would the secondaries report NOT_HEALTHY for Availability Group sync health, but HEALTHY for replica sync health, and why does this differ from the primary's report?

Sadly, sys.dm_hadr_availability_replica states is not a reliable indicator of replica health. Here's the Connect item on one of the bugs we've run into where that DMV stops refreshing - note in the comments that log_send_queue_size in the DMV sys.dm_hadr_database_replica_states shows 0 even when there's log data to be sent.

Note that the Connect item is marked as Won't Fix. Sad trombone.


mongo db-Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2 while adding secondary member in replica set, i get following error:
"Either all host names in a replica set configuration must be localhost references, or none must be; found 1 out of 2"
my first machine configuration is:

second node config:

You can have all replica set nodes in the same physical computer (localhost), but in different ports! (node1:27017, node2:27027, ...)
You CANNNOT have different RS nodes:

at one machine at same port (f.ex. 27017)
different machine and bind to localhost address

So, you need to build your replica set using IP-addresses.


MYSql - Ubuntu 20 - When importing DB, it gets duplicated in lowercase instead of writing to file This is my first post here, I'm trying to import a DataBase.
I'm using Ubuntu with MySQL to run .net core app.
I've exported the Database in Windows MYSql WorkBench, uploaded the file to the server via FileZilla.
After creating the DB in MYSql, it appears as "WebApp3DB" after SHOW DATABASES; is run.
after importing the file with the command:
mysql -u root -p WebApp3DB < /home/ubuntu/netcoreapp/WebApp3DB.sql

The WebApp3DB db won't get updated with the tables and values, but a new DataBase is created with the name webapp3db (all lower case)and it has the tables and values inside.
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| WebApp3DB          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| webapp3db          |
+--------------------+

Being so .net won't recognize it.
Please advise.

Edit server's configuration file, set lower_case_table_names parameter value in [mysqld] section to 2 (uncomment if commented, create if not exists), and restart server. See Identifier Case Sensitivity for details.
Remember, that this solution affects on all databases - but, as you show, this is the only user database on the server instance, so this method may be the best solution in your case.

Alternative solutions:
Open your WebApp3DB.sql file in some text editor and comment CREATE DATABASE and USE statements in it. Will fail if lower_case_table_names is set to zero, and database alias is added to tablenames specifications in backup queries.
Re-create the backup disabling "Insert database creation command" option.


Copy table from one server to another including keys and indexes I have been working on a small project for about two weeks.

We want to consolidate a few tables from several remote server databases in order to run queries and reports on separate from the live databases....kind of like a DIY mini-data warehouse.

We have three SQL Server 2008 remote servers and I am compiling the data onto one local server running three separate SQL Server Express 2017 instances.

The SQL Server Express databases are all set up, and I have successfully tested copying the tables required using several different methods including:

SSMS Import/Export Wizard
as well as scripting using Linked Servers from the new database:

INSERT INTO dbo.table
  SELECT *
  FROM remoteserver.remotedatabase.dbo.remotetable

Both of these successfully copied tables and data BUT not keys, indexes, and other constraints.

I am only recently working in databases, so I am at a loss myself, and Google yielded the above suggestions for copying, but I cannot find anything that includes keys, indexes and other constraints when transferring tables BETWEEN servers.

Since you want to selectively script out certain tables, you can use dbatools to programatically script out tables, triggers, etc using Export-DbaScript.

Alternatively, you can also try Export-DbaDacPackage with -table parameter.

Since you are using linked server, my answer on Which one is more efficient: select from linked server or insert into linked server? will help as well.


Distinct rows by order I have the following SQL, generated from ActiveRecord in Rails 4 (partly with the Geocoder gem)

problem is, that even though DISTINCT is used, hopefully to get only unique items, the items returned duplicated. The reason for the duplication is that they can occur in several shops or brands (which is why I added the distinct, which doesn't quite help)

SELECT DISTINCT items.*,
                <calculate distance from shops coordinates> AS distance,
                <calculate bearing> AS bearing,
                items.*,
                shops.id AS closest_shop_id
FROM "items"
INNER JOIN "collection_items" ON "collection_items"."item_id" = "items"."id"
INNER JOIN "galleries" ON "galleries"."id" = "collection_items"."gallery_id"
LEFT OUTER JOIN brands ON brands.id = galleries.galleriable_id
AND galleries.galleriable_type = 'Brand'
INNER JOIN shops ON shops.brand_id = brands.id
LEFT OUTER JOIN shops AS othershops ON galleries.galleriable_id = othershops.id
AND galleries.galleriable_type = 'Shops'
WHERE <shops coordinate are within some bounds>
ORDER BY updated_at DESC,
         distance ASC LIMIT 10

*things in < > are calculation simplification to make query less messy

How can I make this query return distinct items?

Update:

Checking with PGAdmin, I notice the results are distinct, but on everything, see data

So, to make these distincts, I need to decide which row to ignore.

Is there a way to take only the first occurance (by the order specific) of the id column?

such that it will take the first row of id 23171?

With PostgreSQL you have to use the following if the rows are not identical considering all columns:

SELECT DISTINCT ON (column1, column2) * FROM ...


Database Is In InRecovery Mode. I have a database that has entered "Recovery Mode". It was working fine, but ended up in this status a few days ago.

I have been told that a cause can be the partition holding the datafiles running out of space. However in this case the drive has plenty of space. The log files for the database are also of a reasonable size (300-450mb)

I found this script on google that is meant to return the about of time a database has left before recovery is complete. It dosn't give me any results. We do not have a backup of the database, and we really need its data back

DECLARE @DBName VARCHAR(64) = 'databasename'

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT]  VARCHAR(MAX))

INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName

SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT])  - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]

FROM @ErrorLog ORDER BY [LogDate] DESC

Check out Paul Randal's recommendations: Set the database to EMERGENCY mode, the database will be accessible to sysadmins only. It'll be read-only, so nothing can be written to the transaction log

Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database


Can Status of NOEXEC be Checked with a DMV? On larger DDL scripts for deployments, usage of SET NOEXEC ON as part of error handling is commonplace to prevent execution of downstream code. If coupled with SET NOCOUNT ON, there's a chance code execution may be inadvertently suppressed unless the proper post-deployment checks are done. I checked sys.dm_exec_sessions and there is no attribute for either SET statement. Is there an alternate method of checking the status of either statement for the current session or any user's session from the DBA perspective?

Here's a quick example...

CREATE TABLE dbo.MJZTest
    (Col1 int)
ON [PRIMARY2]
GO
IF @@ERROR<>0
    SET NOEXEC ON

INSERT INTO dbo.MJZTest (Col1)
VALUES (1)
GO

In the above example, if the [PRIMARY2] filegroup doesn't exist, preventing table creation, the @@ERROR check will call the SET NOEXEC ON statement which will prevent actual execution of the subsequent INSERT statement. I've seen this type of code generation with tools such as RedGate SQL Compare.

The catch is, if the person executing the code neglects to run SET NOEXEC OFF to reset the session during debugging, they may get a Command(s) completed successfully. message regardless of the actual outcome.

NOCOUNT can be determined for the current session by parsing @@OPTIONS or by running DBCC USEROPTIONS.
Unfortunately, the only way I could find to determine the value of this option for a different session is by capturing the ExistingConnection event in SQLTrace or Extended Events.

Example 1:

DBCC USEROPTIONS

Output:

+-------------------------+----------------+
|       Set Option        |     Value      |
+-------------------------+----------------+
| textsize                | 2147483647     |
| language                | us_english     |
| dateformat              | mdy            |
| datefirst               | 7              |
| lock_timeout            | -1             |
| quoted_identifier       | SET            |
| arithabort              | SET            |
| ansi_null_dflt_on       | SET            |
| ansi_warnings           | SET            |
| ansi_padding            | SET            |
| ansi_nulls              | SET            |
| concat_null_yields_null | SET            |
| isolation level         | read committed |
+-------------------------+----------------+

Example 2:

SELECT *
FROM (
    VALUES
    (1     ,'DISABLE_DEF_CNST_CHK'),
    (2     ,'IMPLICIT_TRANSACTIONS'),
    (4     ,'CURSOR_CLOSE_ON_COMMIT'),
    (8     ,'ANSI_WARNINGS'),
    (16    ,'ANSI_PADDING'),
    (32    ,'ANSI_NULLS'),
    (64    ,'ARITHABORT'),
    (128   ,'ARITHIGNORE'),
    (256   ,'QUOTED_IDENTIFIER'),
    (512   ,'NOCOUNT'),
    (1024  ,'ANSI_NULL_DFLT_ON'),
    (2048  ,'ANSI_NULL_DFLT_OFF'),
    (4096  ,'CONCAT_NULL_YIELDS_NULL'),
    (8192  ,'NUMERIC_ROUNDABORT'),
    (16384 ,'XACT_ABORT')
) AS v(num, descr)
WHERE @@OPTIONS & num = num;

Output:

+------+-------------------------+
| num  |          descr          |
+------+-------------------------+
|    8 | ANSI_WARNINGS           |
|   16 | ANSI_PADDING            |
|   32 | ANSI_NULLS              |
|   64 | ARITHABORT              |
|  256 | QUOTED_IDENTIFIER       |
|  512 | NOCOUNT                 |
| 1024 | ANSI_NULL_DFLT_ON       |
| 4096 | CONCAT_NULL_YIELDS_NULL |
+------+-------------------------+

Unfortunately, I could not find a way to tell whether NOEXEC is on or off.


DB design for my project (addressing people) Here is my database design:

My first goal is easily grouping people and their own devices (this project will be for asset management) by country, city, office or state.

Second goal is preventing duplicate entries for the same office. Once an office is created with dependency information, other people need to be added with existing information of certain office.

What do you think about my design? Ideal or more complex than required?

edit:

Thank you for your wisely comment. If I change my structure like below, can an user add a city for multiple countries. Isn't it? Can add London city for England and France. As well as state or address. Sholud I prevent this condition with filtering to populate listbox with query?

Vague requirements

Your requirements are too vague. Add more detail. Talk about how each entity relates to another in very precise terms.

Address

The address should be assigned to the office. If the office can have only one address, then address should likely be part of the office table.

The hierarchy of country-state-city makes no sense to me. Explain exactly how your requirements demand that structure. Reporting on such values is done by queries, with sorting and grouping, not building out separate tables. Generally, a related table with only a single semantic column should not be a separate table.

You talked about assigning people to offices but your design makes no such association.

Data-Validation

Some of your concerns seem to be related to validating the data-entry of the address. Usually the app is the front-line of defense for that kind of data-validation, with checks on the database side playing a secondary role.

Either way (app or db), your attempts shown here are meager, and insufficient to ensure valid data. If you are going to do data-validation on the address, do it seriously: Rather than hack your own checks, leverage a postal address verification system driven by postal-code. Either use a remote service or provide your own locally with complete dataset of known addresses and an existing library for look-up and comparing of address info. Your business-problem data should not be directly linked to such tables, but rather your app or database trigger should call out to the remote or local address-verification service.

Naming issues

Change People to singular Person.

Fix spelling of Adress to Address.

Use all lowercase in the names for maximum portability and avoid problems.

Fix Name appearing twice on the address table.


Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause I'm trying to get the last time the data was modified in the tables of the database without any duplicating object_ids ,as I was getting 4 of the same object_Ids because the last_user_update field is present four times for the same object_id .I have tried different variations by grouping with Object_id ,max(Object_Id) and different fields and I still get the same error .
What is the right way to do this ?
This is the error message I'm getting

Column 'sys.dm_db_index_usage_stats.user_scans' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    select  max(i.object_id)
                ,S.name
                ,I.last_user_scan
                ,i.user_scans
                ,i.user_lookups
            ,i.last_user_update
                ,i.last_system_scan
                ,i.user_updates
                ,s.modify_date
    from sys.dm_db_index_usage_stats I
    Inner Join sys.objects S
    ON I.object_id = S.object_id
    WHERE type = 'U'

group by S.name

You're getting the same object_id multiple times because you get a row per index, not a row per object. The point of this DMV is to show you which indexes are getting used more or less than others. So maybe you want:

select  I.object_id
            ,S.name
            ,si.name
            ,I.last_user_scan
            ,i.user_scans
            ,i.user_lookups
            ,i.last_user_update
            ,i.last_system_scan
            ,i.user_updates
            ,s.modify_date
from sys.dm_db_index_usage_stats I
Inner Join sys.objects S
ON I.object_id = S.object_id
INNER JOIN sys.indexes AS si
ON I.[object_id] = si.[object_id]
AND I.index_id = si.index_id
WHERE type = 'U';

If you really just want the last access and the sum of all activity against a table, without caring about which index(es):

SELECT s.name
            ,MAX(I.last_user_scan)
            ,SUM(i.user_scans)
            ,SUM(i.user_lookups)
            ,MAX(i.last_user_update)
            ,MAX(i.last_system_scan)
            ,SUM(i.user_updates)
            ,MAX(s.modify_date)
from sys.dm_db_index_usage_stats I
Inner Join sys.objects S
ON I.object_id = S.object_id
WHERE type = 'U'
group by S.name;

These aren't ideal because if you have dbo.Accounts and test.Accounts, they will get grouped together.


Why is this SSRS Report rounding a BIGINT Value to the nearest Thousandth when exporting to Excel? I have a SQL Server Reporting Services (SSRS) 2012 report that is populated via stored procedure (SP).  The SP returns various fields, one of which is a BIGINT datatype.  When I execute the SSRS report directly the value is properly displayed, but when I export the output to Excel, it gets rounded to the nearest thousandth.

For instance, this is an example of what I'm seeing.

The proper value: 480758225903628660

The value shown in the Excel File: 480758225903629000

It's rounding 8660 to 9000.  Truly frustrating, and Microsoft's documentation mentions nothing about this behavior.  I've also dug everywhere on the report that I can think of that may affect this, such as the Text Box Properties:

...the Placeholder Properties:

... and the Value of the field itself:

I can "fix" it by converting the field to a string by wrapping the field value in a CStr() or .ToString() function, but that also treats the output as a string and not a number.  Is there a way to export this to Excel so that the rounding doesn't occur but still keeps this value numeric?

Like MguerraTorres suggests, SSRS has probably got confused somewhere, and the value suggests it's getting cast to some kind of floating point datatype somewhere along the line.

Try checking the datatype SSRS is assigning by looking at the xml in the .rdl file raw text.

for a simple bigint column I'm seeing an SSRS field definition something like this:

<Field Name="i">
  <DataField>i</DataField>
  <rd:TypeName>System.Int64</rd:TypeName>
</Field>

You might then be able to edit the datatype directly in that text stanza and trick SSRS into sorting itself out


How to transfer 70GB MySQL with no downtime? I have a large amount (4000) of individual databases, 90% of which are small. They all include about 20 tables each, and in total, they use about 75GB of space. All tables are MyISAM.

I want to transfer them to a newer, more powerful server with an SSD for increased performance.

We do not need to continue using MyISAM - i.e. we're not using fulltext or anything - so whilst we could convert to InnoDB and then use Xtrabackup, the time it will actually take to convert to InnoDB concerns me.

Also, during the conversion, wouldn't the database be locked? I don't want any (or at least, less than 5-10 minutes) downtime on the master server.

I cannot use Xtrabackup because it will bring everything down while it copies as it's all MyISAM. I can't use rsync because mysqld has to be stopped during the copy. I can't convert to InnoDB and then use Xtrabackup because the time it will take to convert about 80000 tables (even if they are small) concerns me, and those tables will lock during the conversion process.

And I can't find any other solutions to get this data across! Help is hugely appreciated.

You are going to have to bite the bullet somewhere in this process.

You should determine which databases need to be dumped first (smallest to largest):

SELECT db FROM
(
    SELECT table_schema db,SUM(data_length) DataSize
    FROM information_schema.tables WHERE table_schema NOT IN
    ('information_schema','performance_schema','mysql')
    GROUP BY table_schema
) A ORDER BY DataSize;

Take this list of databases and numerically generate conversion scripts

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DBLIST=`/tmp/ListOfDatabasesOrderedBySize`
SQL="SELECT db FROM (SELECT table_schema db,SUM(data_length) DataSize"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} AND engine='MyISAM'"
SQL="${SQL} GROUP BY table_schema) A ORDER BY DataSize"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
DBNUM=1000000
SQL="SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
SQL="${SQL} AND engine='MyISAM'"
SQL="${SQL} ORDER BY data_length+index_length"
for DB in `cat ${DBLIST}`
do
    (( DBNUM++ ))
    CONVSCRIPT=DB${DBNUM}_${DB}.sql
    mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVSCRIPT}
done

You can now run each conversion script by name in alphanumeric order.

As you get to the bigger databases, you should run them during off hours.

Once everything is converted to InnoDB, enable binary logging on the current server.

Next, mysqldump all of it as a point-in-time dump and gzip it

DUMP_OPTIONS="--routines --triggers --single-transaction --master-data=1"
mysqldump ${MYSQL_CONN} ${DUMP_OPTIONS} | gzip > MySQLData.sql.gz

Transport MySQLData.sql.gz to the new machine and setup replication.

I'll leave the rest to your imagination


Postgresql - Can I do a join in a function? I'm recently starting to use PostgreSQL. In SQL Server I used to use stored procedures for different kind of things. I want to know if, like in SQL Server, can I do something like in that.
I used to use this kind of things;

create procedure my_proc
@opt int=null,
@param varchar=null
AS BEGIN
IF @opt = 1
 select * from my_table1 t1
 inner join table2 on t1.id=t2.id
 where t2.param=@param
END IF
IF @opt = 2
select "nothing"
END IF

GO

If the user return the @opt variable with 1 the result could be like:

t1_id | t2_id | param
2     | 2     | qer
5     | 1     | qer
8     | 1     | qer

and if the user return a 2 in the @opt it would be something like:

nothing

Yes, except the Postgres syntax through CREATE FUNCTION is  more natural:

CREATE FUNCTION my_proc(myOpt bool, myParam varchar)
RETURNS SETOF (types)
AS $$
  SELECT types
  FROM my_table1 t1
  INNER JOIN table2 AS t2 USING (id)
  WHERE myOpt AND t2.param = myParam
$$ LANGUAGE sql;

If you need a more complex procedural language check out PL/pgSQL


What are the security implications of the privileges needed by the Bluemedora Postgres account? Blue Medora is a service for forwarding monitoring metrics from Postgres to Google Cloud Monitoring.
As part of their setup instructions they offer a SQL script to configure a least privilege user to be used to login to the account. (pasted below for reference)
If the bluemedora account were compromised, what information could be leaked or what else could it get up to?
(obviously replacing tmppassword in the script is a given)
An obvious one is that it can pull all the stats that it reports on, including reporting queries that are being executed which could lead to leakage of PII if it's included in those queries.
Based on the info present an attacker could find out the names of users and superusers of the db, all the tables being used and with the queries probably a good deal of the table structures. Which could then be used to craft a targeted attack.
Are there other more direct ways to access the data stored in the tables with this account, or to escalate privileges?
Here's the script
CREATE SCHEMA bluemedora;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE OR REPLACE FUNCTION bluemedora.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain json
    )
RETURNS SETOF json AS
$$
BEGIN
  RETURN QUERY EXECUTE 'explain (format json) ' || l_query;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
COST 100 ROWS 1000;

CREATE USER bluemedora WITH PASSWORD 'tmppassword';
GRANT SELECT ON pg_database TO bluemedora;
GRANT SELECT ON pg_stat_bgwriter TO bluemedora;
GRANT SELECT ON pg_stat_database TO bluemedora;
GRANT SELECT ON pg_stat_user_indexes TO bluemedora;
GRANT SELECT ON pg_stat_user_tables TO bluemedora;
GRANT SELECT ON pg_statio_all_sequences TO bluemedora;
GRANT SELECT ON pg_statio_user_indexes TO bluemedora;
GRANT SELECT ON pg_statio_user_tables TO bluemedora;
GRANT SELECT ON pg_tables TO bluemedora;
GRANT SELECT ON pg_tablespace TO bluemedora;
GRANT SELECT ON pg_user TO bluemedora;
GRANT SELECT ON pg_stat_replication TO bluemedora;
GRANT SELECT ON pg_stat_database_conflicts TO bluemedora;
GRANT SELECT ON pg_trigger TO bluemedora;
GRANT SELECT ON pg_stat_activity TO bluemedora;
GRANT SELECT ON pg_stat_statements TO bluemedora;
GRANT USAGE ON SCHEMA bluemedora TO bluemedora;
GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO bluemedora;

The big security problem here is that it doesn't revoke execution on its created functions from PUBLIC, which means that users other than bluemedora can use them, which is evidently not what is intended.  For pg_stat_statement this isn't really a problem, as it is located in a schema that no one else should have access to, but for explain_this it is in public schema and with PUBLIC execution rights.  And why is explain_this created in public in the first place?  If there is a reason to spread the functions over two schemas, I don't know what it is.  It looks like maybe explain_this was added to the script by someone who didn't know what they were doing.
Granting SELECT on all those catalog table is unneeded on most installations, as those already have SELECT granted for PUBLIC.  However, there is no guarantee this script will be run on "most installations", so it is reasonable to explicitly grant those permissions rather than relying on someone having not mucked with the defaults.  Most concerning to me would be the lack of schema qualification on those grants.

Based on the info present an attacker could find out the names of
users and superusers of the db, all the tables being used and with the
queries probably a good deal of the table structures. Which could then
be used to craft a targeted attack.

All of those things except for query text will be true for almost any compromised user, even with no special permissions granted.  Are you worried about social engineering attacks?


postgreSQL how can I make this work with an aggregation alias? postgreSQL 9.5.6

How can I make this work using the percent_error alias?

SELECT time::date,
  sum((status='404 NOT FOUND')::int) as not_found,
  sum((status='200 OK' OR status='404 NOT FOUND')::int) as total,
  sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float as percent_error
  from log
  group by time::date
  having sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float > 0.01;

A query like this work:

select species,
  count(*) as num
  from animals
  group by species
  having num=1;

But not this - exactly the same as the first example above except here the last line is using percent_error alias:

SELECT time::date,
  sum((status='404 NOT FOUND')::int) as not_found,
  sum((status='200 OK' OR status='404 NOT FOUND')::int) as total,
  sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float as percent_error
  from log
  group by time::date
  having percent_error > 0.01;

A "WITH query" (CTE) may be useful for you (see: https://www.postgresql.org/docs/current/static/queries-with.html - read at least the first paragraph ...)

Here's an example, very close to your situation.  Notice: date_ is the equivalent of time::date in your code:

with stats as (
  select
    date_
  , sum((status='404 NOT FOUND')::int) as not_found
  , sum((status='200 OK' OR status='404 NOT FOUND')::int) as total
  , sum( (status='404 NOT FOUND')::int) /
         sum((status='200 OK' OR status='404 NOT FOUND')::int)::float
    as percent_error
  from log
  group by date_
)
select not_found, total, percent_error
from stats
where percent_error > 0.01 ;


Deleting records from tables containing FK pointing to each other Background: Bit of an accidental DBA here. I have this reddit-like website where users submit links pointing to various internet content, and can then leave comments under each posting. This app - let's call it Links - has two corresponding tables to store data: link and publicreply (i.e. comments assoc. to each link).

Problem: I can't seem to delete records (for maintenance) from these two tables because of interdependent FK constraints. Need guidance to resolve the situation.

Details: Each Publicreply object stores a FK to the Link object it's associated to. Moreover, each Link object also saves a reference to the latest publicreply associated to it. This creates a situation whereby all Publicreply objects have a Link FK, and vice versa. As in:

Table "public.links_link"
        Column        |           Type           |                        Modifiers
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 url                  | character varying(250)   | not null
 image_file           | character varying(100)   |
 reply_count          | integer                  | default 0
 latest_reply_id      | integer                  |
 is_visible           | boolean                  | default true
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED

 Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

How do I delete records from the table Link under this scenario? And from Publicreply? Im using postgresql 9.3.10.

The problem can be solved with many ways:

First we notice that one of the FK columns is nullable. This allows to delete from both tables, using three statements in a single transaction and without need for deferrable constraints. First update latest_reply_id to null, then delete from PublicReply, then from Link:

-- Delete one (or more) `Link` rows
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    UPDATE public.links_link
    SET latest_reply_id = NULL
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted

    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted
COMMIT ;

Then we notice that one of the foreign key constraints is already defined as deferrable. This allows to delete from both tables, using two statements in a single transaction:

-- Delete one (or more) `Link` rows
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted
COMMIT ;

Using modifiable CTEs, we can delete from both tables in a single statement. The constraints do not need to be deferred for this. Example:

-- Delete one (or more) `Link` rows
-- and all the `Publicreply` rows associated with them:
WITH del_link AS
  ( DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?)                -- Link ids to be deleted
    RETURNING id
  )
DELETE FROM public.links_publicreply
WHERE answer_to_id IN (TABLE del_link) ;

Deleting from PublicReply will be a bit more complicated, depending the requirements but can be done with any of the above methods. What are the requirements?

delete a PublicReply, its parent Link and all the associated replies?
delete a PublicReply and if it's the latest reply, change the parent Link to point to the previous reply? If it's the only one, set it to NULL?
delete a PublicReply and if it's the latest reply, change the parent Link to point to the previous reply? If it's the only one, delete the parent Link as well?


Will a mysqldump ever break my database? I tried to backup my database with mysqldump. I've done this on a live website also. Is there any chance that this command could ever break a website/database?

The mysqldump utility will not break your database.

Read the source code for it and/or turn on the general query log while it's running and you'll see that what all it does it look.

But...

If you are using MyISAM, or if you fail to use the --single-transaction option, or you issue a FLUSH TABLES; request from another connection while the backup is reading a large table, you stand a higher chance of stalling your web site because there are things that can happen to cause queries to block until the specific table or the entire backup is complete.

And, if you do have serious corruption in your data, mysqldump stands a good chance of running into it, since it reads every row from every table by default, which means the server will be trying to read through the corruption and could crash.  This is essentially true of any backup, of course.

But directly and ordinarily and by most reasonable definitions, mysqldump cannot harm your database contents.


MySQL: does Index Merge Sort-Union access algorithm use temporary table? My question is about index_merge optimization in MySQL.

I have a query with a set of range conditions connected with OR. EXPLAIN of the query shows that index_merge method is used and the Extra column contains only:

'Using sort_union({list of merged indexes}); Using where'

The question is: "Does this query use a temporary table?"

In the 8.2.1.4.3 The Index Merge Sort-Union Access Algorithm section of the MySQL manual it is written that:

  The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

That looks like the RDBMS is going to use a temporary table. But in the section 8.4.4 How MySQL Uses Internal Temporary Tables we can read:

  To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary (see Section 8.8.1, “Optimizing Queries with EXPLAIN”). EXPLAIN will not necessarily say Using temporary for derived or materialized temporary tables.

But there is no 'Using temporary' in the Extra column in this case. There are no subqueries or UNIONs in the query. The query looks like:

SELECT *
FROM A
JOIN B USING(pk)
WHERE (B.c1 IN ({list_of_values}) OR B.c2 IN ({list_of_values}) OR ... OR B.c9 IN ({list_of_values})) AND {some_other_conditions};

There are 9 single-column INDEXes on B.c1, B.c2,... , B.c9 columns.

I've performed an experiment: rewrote the query from using 9 ORs in a single query to a UNION DISTINCT of 9 subqueries. As expected, EXPLAIN shows for a rewritten query in the last row: select_type = "UNION RESULT", Extra = "Using temporary". Such change was done to have a query that surely uses temporary table to compare with.

Now, when I launched an app using this query in its original form (9 ORs) - each check Created_tmp_tables increased only by 1 (from the manual: invocation of the SHOW STATUS increments the global Created_tmp_tables value by 1).

When I launched the app using rewritten query Created_tmp_tables grows very fast.

I think this experiment is enough to show that temporary tables are not being created in the case of index_merge Sort-Union.

Conclusion: no, it does not.


mysql replication deadlock slave stopped I've a master and slave and got the slave stopped with the following error log in slave. I want to fix this and get the two servers on synch and get going again.

111128  8:42:24 [Note] Slave I/O thread: connected to master 'repl@masterIP:3306',  replication started in log 'mysql-bin.000006' at position 169
111128 15:46:50 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Deadlock found when trying to get lock; try restarting transaction' (1213), Error on slave: 'no error' (0). Default database: 'Dbase'. Query: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id', Error_code: 0
111128 15:46:50 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000006' position 1277768

I'm not sure how to do the following:

try restarting transaction' (1213)

Thanks.

This query fails on slave: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id'

You report a strange Error_code: 0, but try to run this query on slave? When it run succesfully then run as root:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;


Should I set document version field to be part of a primary key Should I set document version as a primary key
I am working on a document management system, and I have these two tables representing part of the whole tables.

On the DocumentVersion table I have set the primary key to be (version + documentID) . so can anyone advice if this is a  recommended approach where the documentID is of type int while the version is of type decimal(5,2) ? or it is better if I create a new field and set it as the primary key inside the DocumentVersion table?
Thanks

The DocumentVersion table makes sense to me.  The two columns well define the Document by the DocumentID relationship to the Primary Key on Document and the Version makes a unique entry for DocumentVersion.  (Just as the name implies.)

There is no problem having a primary key that included two different data types, so an INT and a DECIMAL(5,2) will coexist happily in the same Primary Key.

What you do want to avoid is having a join between mismatched data types, such as joining a VARCHAR(10) with an INT. This will likely result in problems with the join performance even when all the data is clean.  (And, of course, a VARCHAR can hold strings that would not convert to INT which would be another class of problem.)

EDIT: Depending upon your usage, a surrogate PK column DocumentVersionID could be beneficial for other tables to reference.  However, for integrity of tracking the versions, the DocumentID,Version should at least be enforced by a UNIQUE CONSTRAINT.

E.g.  ... CONSTRAINT Unique_DocumentVersion UNIQUE (DocumentID, Version);


Managing SQL Server 2008 R2 transaction logs when using principal/mirror databases My system has the main database as a principal (reads/writes only to that database) and then a mirror database on a different site that receives all database updates from the principal database.

This is all working fine, but the transaction log on the principal database is close to 80GB and growing. I have tried shrinking the log but that task fails every time. In my opinion I would not need the log at all, since all updates are mirrored to the other site.

Principal database needs to be in full recovery mode for the mirroring to work.

Can you give me some steps on how to get rid of that annoying transaction log or how to get it into a manageable size? Since the mirror site is my recovery method, can I just delete in?

Your principal database is in full recovery mode which means you cannot (by rule) shrink your transaction log until you take a transaction log backup.

Here is some information from Microsoft on the subject:

http://technet.microsoft.com/en-us/library/ms191429.aspx

http://technet.microsoft.com/en-us/library/ms179478.aspx

In SSMS, highlight your database in the Object Explorer. Then right click it, choose Tasks then Back up... Next, choose your database. Change your backup type to Transaction Log. Back up to disk somewhere that has plenty of storage (at least as much as your transaction log).

At 80GB, your transaction log backup will take a long time. Once that's done you will be able to shrink your transaction log.

I would recommend running a transaction log backup regularly in order to keep your transaction log from growing out of control.

To answer your question, no, you cannot delete a transaction log (while the database is online) and if you do delete it, you risk losing any data that has not been flushed.

ALSO, Microsoft says you cannot take a transaction log backup until you run a full backup. If you have never run a full backup on the database in question, run that first before the transaction log backup.


Partition tables based on month and year in SQL Server I have a sales table which I need to partition based on month and year of sales

table structure

S_ID           numeric(18,0)
S_CustID       numeric(18,0)
S_TotalCash    numeric(18,0)
S_Paid         numeric(18,0)
S_Date         datetimeoffset(0)

I want to enter sales records between 01/01/2014 to 31/05/2014 to the first sales tables
and from 01/06/2014 to 31/12/2014 to the second table
and use a partitioned view to insert and select records from both the table so I add a constraint as

ALTER TABLE [dbo].[sales_1]  WITH CHECK ADD  CONSTRAINT [CK_sales_1_1] CHECK  ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))

for the first table
and

ALTER TABLE [dbo].[sales_2]  WITH CHECK ADD  CONSTRAINT [CK_sales_2_1] CHECK  ((datepart(mm,[S_Date]) >= (01)) and (datepart(mm,[S_Date]) < (06)) and ((datepart(year,[date]) = (2014)))

AND
used a partitioned view

Select * from Sales_1
union all
Select * from Sales_2

to insert and update records
 but when I try to insert data ssms shows error as

  "UNION ALL view 'Test.dbo.Sales_View' is not updatable because a partitioning column was not found."

How to solve this?

Firstly, it must be said that using SQL's native table partitioning support is preferred.  But that requires SQL Enterprise, which is a lot of money, so, presumably, here we are.

A few things for you to check:

Partition column must be part of primary key
Partition column must have one and only one CHECK constraint
Partitions must not overlap.  Perhaps you have a typo, or my eyes fail me, but your constraints on sales_1 and sales_2 overlap.
Make sure the constraints are checked (WITH CHECK CHECK CONSTRAINT).  Given your definition in the question, it probably isn't the issue, but it is in general a necessary troubleshooting step (take a look at SELECT name, is_not_trusted FROM sys.check_constraints)

EDIT

Apparently DATEPART doesn't work.  Probably you should just specify the dates directly (s_date >= '2014-01-01' AND s_date < '2014-06-01'), etc.  You could also use persisted custom columns.

See: https://stackoverflow.com/questions/11155758/partitioned-view-not-working

Some other things to note about partitioned views, most of which I ran into quickly:

IDENTITY columns will prevent inserts.  What I did to get around this was create a "staging" table with the same structure (and an identity column), and add a trigger that just inserts into the partitioned view and deletes from the staging table.  At some point I will look into using SQL 2012's SEQUENCEs
You can't run an update/delete query on the PV if it has a self-join on the PV or any of its tables
All columns have to be specified in an INSERT statement.  DEFAULT doesn't work or help.  You can't omit nullable columns or columns with a default value.
If you use replication, it tends to disable the constraints (NOT FOR REPLICATION), so you'll have to make sure that doesn't happen

You can see here at the bottom: http://technet.microsoft.com/en-us/library/aa933141(v=sql.80).aspx


All data got deleted or truncated- What happened? What are some of the ways of knowing how the 100 M rows of record table got wiped out. Database is in Full recovery mode. I am not sure how doing daily backups are happening but before the truncate or delete happened, it was 50 GB and now down to 4 GB.

Guess my question is.

How to effectively and immediately find out what query caused it?
How to avoid it in the future? Will having SQL audit run on the machine help?

Thanks

Unfortunately, there is no easy way to determine the DML command used without having some kind of auditing setup prior to the action.
You might be able to infer whether the action was a DELETE or a TRUNCATE by looking at the transaction log to see if pages were deallocated or if row data was written to the log.  If the trnasaction log shows pages were deallocated you can be certain that the table was truncated.
If you had SQL Server trace or an Extended Events session running on the server at the time the delete occurred, and that trace/session is capturing DML statements, you'd be able to see exactly what happened.
Probably the more important consideration would be to ensure you have a Recovery Point Objective that serves the business by limiting the amount of time that can go by without a backup happening that you can use for recovery.  So, with full recovery, you could be running log backups every 5 minutes, which would mean you could recover the table to a point just before the delete took place.
This blog post by Paul Randal has an excellent tutorial for inspecting a log backup.


SQL Server Error - HRESULT E_FAIL has been returned from a call to a COM component I have a huge 200MB .SQL file. When I try to open this in SQL server management studio 2008, I get the error -Error HRESULT E_FAIL has been returned from a call to a COM component.

What could the reason be and how to fix it ? Is this caused due to big files ? It does not happen when I open a 2-3 KB file.

You probably cannot open the file due to lack of memory.  A 200GB 200MB .sql file is very, very big.  You could process the file using SQLCMD.exe instead of SQL Server Management Studio.


Count results after filtering out I am using MySQL 5.7 and need to count results after filtering out the data.
My table structure:

+----+---------+---------+---------+
| id | metakey | metaval | tour_id |
+----+---------+---------+---------+
|  1 |       4 |      15 |       1 |
|  2 |       4 |      18 |       1 |
|  3 |       4 |      23 |       1 |
|  4 |       4 |      19 |       2 |
|  5 |       4 |      23 |       2 |
|  6 |       4 |      25 |       2 |
|  7 |       4 |      27 |       3 |
|  8 |       4 |      29 |       3 |
|  9 |       3 |       5 |       1 |
| 10 |       2 |       6 |       1 |
| 11 |       3 |       5 |       2 |
| 12 |       2 |       6 |       2 |
+----+---------+---------+---------+

metakey -> is something predefined, like 4 for countries,
metaval -> is real data id, for example country id

Let's say I selected rows by the following query:

SELECT tour_id
from metas
WHERE (metakey, metaval) IN ((3, 5), (2, 6))
group by tour_id
having COUNT(tour_id) = 2;

+---------+
| tour_id |
+---------+
|       1 |
|       2 |
+---------+

Now I need to extract a number of tours for each country (country meta key is 4) of that resultset.

Expected result:

|-----------|---------|
|country_id |  count  |
|-----------|---------|
|    15     |   1     |
|-----------|---------|
|    18     |   1     |
|-----------|---------|
|    19     |   1     |
|-----------|---------|
|    23     |   2     |
|-----------|---------|
|    25     |   1     |
|-----------|---------|

I wrote a query which returns a correct resultset, but it took 0.0231 seconds. Number of rows in table: 35036

SELECT metaval, count(tour_id) as numberOfTours
from metas2
where tour_id in ( SELECT tour_id
                   from metas2
                   WHERE (metakey, metaval) IN ((3, 5), (2, 6))
                   group by tour_id
                   having COUNT(tour_id) = 2 )
      AND metakey = 4
GROUP BY metaval

+---------+---------------+
| metaval | numberOfTours |
+---------+---------------+
|      15 |             1 |
|      18 |             1 |
|      19 |             1 |
|      23 |             2 |
|      25 |             1 |
+---------+---------------+
5 rows in set (0.00 sec)

Does anyone have tips to improve performance?

Test:

SELECT metaval, COUNT(DISTINCT tour_id)
FROM metas, ( SELECT tour_id
              FROM metas
              WHERE (metakey, metaval) IN ((3, 5), (4, 6)) -- or (2, 6) ?
              GROUP BY tour_id
              HAVING COUNT(tour_id) = 2 ) tours
WHERE metas.tour_id = tours.tour_id
GROUP BY metaval


TempDB Best Practices For Shared Storage Microsoft recommends separating the SQL Data Files from the Temp Files to limit device contention but what about cases involving shared storage.  I have a shared environment where the data and temp files are located on the same mount point or LUN.  Is it best practice to separate them onto different?  Or will it make a difference from a performance standpoint?

There is no a definitive answer.
Anyway I suggest, if possible, to store tempdb files in different LUN. But it is worth only if tempdb is under pressure.

In my environment I use shared storage also for tempdb for performance reasons: my local disks are slower then shared storage via EMC.


What is the most efficient way to get the minimum of multiple columns on SQL Server 2005? I'm in a situation where I want to get the minimum value from of 6 columns.

I've found three ways so far to accomplish this, but I have concerns with the performance of these methods and would like to know which would be better for performance.

The first method is to use a big case statement. Here's an example with 3 columns, based on the example in the link above. My case statement would be much longer since I will be looking at 6 columns.

Select Id,
       Case When Col1 <= Col2 And Col1 <= Col3 Then Col1
            When Col2 <= Col3 Then Col2
            Else Col3
            End As TheMin
From   MyTable

The second option is to use the UNION operator with multiple select statements. I would put this in an UDF that accepts an Id parameter.

select Id, dbo.GetMinimumFromMyTable(Id)
from MyTable

and

select min(col)
from
(
    select col1 [col] from MyTable where Id = @id
    union all
    select col2 from MyTable where Id = @id
    union all
    select col3 from MyTable where Id = @id
) as t

And the 3rd option I found was to use the UNPIVOT operator, which I didn't even know existed until just now

with cte (ID, Col1, Col2, Col3)
as
(
    select ID, Col1, Col2, Col3
    from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
    select
        ID, min(Amount) as TheMin
    from
        cte
        UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
    group by ID
) as minValues
on cte.ID = minValues.ID

Because of the table size, and frequency in which this table is queried and updated, I am concerned about the performance impact these queries would have on the database.

This query will actually be used in a join to a table with a few million records, however the records returned will be reduced to around a hundred records at a time. It will get run many times throughout the day, and the 6 columns I am querying are frequently updated (they contain daily stats). I do not think there are any indexes on the 6 columns I am querying.

Which of these methods is better for performance when trying to get the minimum of multiple columns? Or is there another better method that I don't know of?

I am using SQL Server 2005

Sample Data & Results

If my data contained records like this:

Id    Col1    Col2    Col3    Col4    Col5    Col6
1        3       4       0       2       1       5
2        2       6      10       5       7       9
3        1       1       2       3       4       5
4        9       5       4       6       8       9

The end result should be

Id    Value
1        0
2        2
3        1
4        4

I tested the performance of all 3 methods, and here's what I found:

1 record: No noticeable difference
10 records: No noticeable difference
1,000 records: No noticeable difference
10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
100,000 records: UNION subquery is significantly slower, but UNPIVOT query becomes a little faster than the CASE WHEN query
500,000 records: UNION subquery still significantly slower, but UNPIVOT becomes much faster than the CASE WHEN query

So the end results seems to be

With smaller record sets there doesn't seem to be enough of a difference to matter. Use whatever is easiest to read and maintain.
Once you start getting into larger record sets, the UNION ALL subquery begins to perform poorly compared to the other two methods.
The CASE statement performs the best up until a certain point (in my case, around 100k rows), and which point the UNPIVOT query becomes the best-performing query

The actual number at which one query becomes better than another will probably change as a result of your hardware, database schema, data, and current server load, so be sure to test with your own system if you're concerned about performance.

I also ran some tests using Mikael's answer; however, it was slower than all 3 of the other methods tried here for most recordset sizes. The only exception was it did better than a the UNION ALL query for very large recordset sizes. I like the fact it shows the column name in addition to the smallest value though.

I'm not a dba, so I may not have optimized my tests and missed something. I was testing with the actual live data, so that may have affected the results. I tried to account for that by running each query a few different times, but you never know. I would definitely be interested if someone wrote up a clean test of this and shared their results.


pt-query-digest --type binlog (percona-toolkit) gives empty output with MariaDB/Galera 10.1 My installed versions of MariaDB and Percona Toolkit on MacBook:

brew info percona-toolkit
percona-toolkit: stable 3.0.10 (bottled), HEAD
Percona Toolkit for MySQL
https://www.percona.com/software/percona-toolkit/
/usr/local/Cellar/percona-toolkit/3.0.10 (244 files, 8.4MB) *
  Poured from bottle on 2018-05-31 at 09:52:48
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/percona-toolkit.rb
==> Dependencies
Required: mysql ✔, openssl ✔
==> Options
--HEAD
    Install HEAD version

DB server versions:

show global variables like '%version%';
+-------------------------+---------------------------+
| Variable_name           | Value                     |
+-------------------------+---------------------------+
| innodb_version          | 5.6.36-82.1               |
| protocol_version        | 10                        |
| slave_type_conversions  |                           |
| version                 | 10.1.26-MariaDB           |
| version_comment         | Source distribution       |
| version_compile_machine | x86_64                    |
| version_compile_os      | Linux                     |
| version_malloc_library  | system                    |
| version_ssl_library     | OpenSSL 1.0.1f 6 Jan 2014 |
| wsrep_patch_version     | wsrep_25.19               |
+-------------------------+---------------------------+
10 rows in set (0.01 sec)

Funny is in order to install percona-toolkit I had to install Oracle MySQL and afterwords change back to MariaDB

brew install mariadb
brew unlink mariadb
brew install percona-toolkit
brew unlink mysql
brew link mariadb

We use Galera cluster with row based replication:

show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

I did a simple use case:

mysqlbinlog  mysql-bin.0013* > all.sql

pt-query-digest --type binlog all.sql
all.sql:   1% 37:37 remain
(...)
all.sql:  96% 01:23 remain
all.sql:  98% 00:30 remain

# 2417.5s user time, 51.4s system time, 89.16M rss, 4.24G vsz
# Current date: Fri Jun  1 07:42:57 2018
# Hostname: aukVivi0009
# Files: all.sql
# Overall: 0 total, 2.05k unique, 0 QPS, 0x concurrency __________________
# Time range: 2018-05-26 02:00:54 to 2018-05-31 08:05:28
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query size        10.68G       6 287.51k  492.88  833.10   1.92k  107.34

# Profile
# Rank        Query ID    Response time Calls       R/Call      V/M   Item
# =========== =========== =========== =========== =========== ===== ======

why is the output empty?
Is pt-query-digest not compatible with MariaDB? Changes in binlog format? Any workaround?

See Reading RBR binary logs with pt-query-digest and LP #1377887: pt-query-digest fails to parse binary log with RBR events

That bug report is over 2 years old and ranked as "low" priority.  Suggest you add a comment to clue Percona in that there is interest in getting it fixed.


database design for inventory from transactions I have a database design question.

We are working on an existing inventory system where stock is transferred from head-office to branch office, from branch to distributor and from distributor to dealer.

The system is used to generate inventory from transaction tables. For example for a distributor, we can find rows for that distributor from transactions( it marks items further transferred to dealers too).

This was easy as there was simple hierarchy.

Now there is a change. A stockist is being introduced. Stock can be transferred to distributors from branch or from stockist to distributors, any of these two.

Previously, we could get distributor inventory from b2d transaction tables( challan, challanrows, rowdetails).  Now, we have two channels of inward for same distributor.

Should we need change the database design? Do we need some double entry system? Or we should try to get the inventory table by merging data from 2 tables?

Ultimately, the answer will depend on what you can spend the time to do.

In the ideal world, I would recommend a DB design like the following:

stock_holder - identifies an entity that can provide or receive stock. This can actually be your existing table(s) for HO, branches, etc.; a new "bridge" table that allows you to treat each of the current entities as basically equals; or, a new table that actually holds all the data you currently have for these various entities. I assume that there may be different columns needed for different entities.
stock_exchange - at the core, holds: entity sending stock; entity receiving stock; and amount of stock. This would probably be a mutation of your current transaction table. An entry in this table would mean the sender's stock inventory is decremented by the amount, and the receiver's is incremented.

Of course, you have an entire existing working program that is probably not designed like this. And completely changing the DB design of a working system to handle what (at least seems) like a minor change is probably not practical.

So, another alternative is to set up a new table structure that still works with the existing application.

Take the data from the current tables, and figure out how to combine it into a stock_exchange table. Then, based on the stock_exchange table, create views that would replicate the existing tables. This would require that your version of the SQL language to allow INSTEAD OF triggers on views: you would create such triggers on each of your views, to make sure that inserts, updates, and deletes actually happen to stock_exchange. Then, the application keeps using the views for now; as opportunities to update the application arise, you can phase out the views.

This would allow for other oddities - emergency shipments direct from HO to a distributor, or something like that.


How to view an encrypted view or stored procedure I am working on a third party database.

When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error:

  This property may not exist for this object or may not be retrievable
  due to insufficient access rights. The text is encrypted.

I have a fairly detailed description about this problem here.

In short, the object is not really encrypted, but rather obfuscated. Therefore we can retrieve the original back. The method is a little involved but it consists of these steps:

Connect to the instance using the Dedicated Admin Connection
Select the obfuscated code like this:

SELECT @secret = imageval
FROM   sys.sysobjvalues
WHERE  objid = OBJECT_ID(@object_name);

Replace the object with another one that has the same name same object_id and the same length in bytes (e.g. using ALTER PROCEDURE)
Get the newly obfuscated code the same way as above
XOR the three values together (obfuscated original, replacement and obfuscated replacement)

That will give you the original code. However, as Kin mentioned, there might be support and even legal implications with doing this so be sure to consult your lawyer first.


See details of old sessions Is there any way of seeing details of sessions from the past (I fully expect the answer to be "no"). I am trying to find out what server a certain application is running on, that application accessing a certain database. I know the application runs at some point on a Friday evening so I was hoping that SQL server maintains a session history so I could just look up sp_who-like details for the hours in question.

Like I say, I don't expect this to be possible but right now I am absolutely clutching at straws.

Thanks in advance.

EDIT: I found the server in question by other means in the end; the job executed a SQL statement to create a file which was then FTPd to another site - that site has FTP logging that could give me the name and IP address of the source server. Thanks for the answers - I only wish I could accept them both as they both make very good points and give very good suggestions.

No, SQL Server doesn’t keep a history of session activity, imagine how fast that would grow! Your only hopes for retrieving this information from the past, as your question asks, I think, are:

That the app does something that gets logged in the default trace (and those events haven’t rolled away);
Errors that the app might have generated that clearly indicate the server it connected to ended up in the application machine’s event log; or,
Errors that the app might have generated (like deadlocks, I/O warnings, or failed logins) have been captured in the SQL Server error log or the system_health session.

Now, as mentioned in the comments, you could of course set up something to capture this activity in the future, so you don't have to sit around and wait to repeatedly hammer F5 when the app is running. You can use a variety of things, or even a combination, depending on whether you care only about connections, only about write activity, only about access to specific tables or databases, etc.:

Extended Events
logon triggers
DML triggers
SQL Server Audit
server-side trace
Wireshark
maybe even from the plan cache


Azure SQL: Azure SQL Database traffic migration to newer Gateways I have been assigned a task to look at this: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-gateway-migration. I got an email about it:

I have a pretty standard setup, with a Azure SQL server and one database, that my services connect to through a connectionstring in some resoruce groups in Azure. Pretty standard and straight forward. I don't think I am affected by this, but still, I got the email, and now I have find out. I have never heard of gateways and such.

Do I need to do anything if my services and databases is completely hosted in Azure? I cannot read that from the email.

I received the same email. I have received similar emails in the past. The only thing I do is add the IPs of the new gateways on the firewall rules on the Azure SQL Database logical server as shown on the image below.

Add the IP addresses of the gateways that belong to the zones where you have created your Azure SQL Database logical servers.

You do not have to do anything else. Based on my experience everything keeps working as usual.


How to get a user's friends' names? I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow down the process; querying for every friend. I didn't get how and what to use of left, joints, right joints, simple joints, ASs, ONs, ...

If you want to get the names of the friends for a user perhaps try something like so:

SELECT u.firstname, u.surname
FROM friend f, user u
WHERE (f.Person1 = 1 OR f.Person2 = 1)

This should get a back a list of names in your user table (providing that is what you have named your friends in the user table) that are friends with userID 1

I'm guessing your friend table contains the foreign key IDs of the friends making up the partnership?


Single node availability group - is it possible? Is it possible to have an availability group with just one node in it?

The reason I ask is because I am considering a distributed availability group spanning two single node always on availability groups thus getting away from the requirement for a WSFC. The two nodes being several miles apart which may mean a normal availability group may be problematic with data synching.

To specifically answer your question, yes you can create an availability group on a single node cluster.  I tested this with SQL Server 2017, and you can create an AG on a single node cluster using the "New Availability Group" context menu item in SSMS, or with the raw T-SQL.  However, you cannot create one using the "New Availability Group Wizard" item as it requires a second replica to be added to progress through the dialogs.

Based on your comments, I would expect that a two-node cluster with asynchronous replication and manual failover is a better solution.  There are no real benefits to using a distributed AG in this situation, and the benefit of a regular AG is that you can fail over and fail back as needed.  With a distributed AG, failover is possible, but failback is not.


Deriving formulas for input/output I'm currently enrolled in a DBS class and am having problem with an assignment. I've searched around and have been unable to understand what it is I'm meant to be doing with this derivation formula.

  A plant file with TREE-GENUS as the key field includes records with
  the  following TREE-GENUS values: Tsuga,  Ficus , Arbutus, Quercus,
  Melaleuca,  Tristaniopsis, Cornus, Sequoiadendron, Lithocarpus,
  Liriodendron, Pittosporum.
  Suppose that records with these search
  field values are inserted into a random (heap) file with a maximum of
  3 records per block.  Derive a formula for the expected number of
  disk I/O to scan these records and to search for a particular record

I've been using some software that was given with the assignment and it also asks what are the maximum number of blocks that are allowed and that is not given by the above brief. I'm not really sure how to derive a formula for this. I've assumed that because there are 3 records per block there are 4 blocks required and that a random heap file uses 1 disk i/o per write/read.

If this is a larger topic than is worth explaining a link to a reliable few pages is also helpful.

This is obviously a homework question but it seems to me a bit like a trick question from a db perspective.  I don't think there is a simple answer and here the answer may be quite a number of additional questions.  My recommendation in trying to answer the question is to sketch out how pages would work, and the like.  In essence so much depends on implementation (compare disk I/O in MySQL's InnoDB vs PostgreSQL and you will see massive differences due partly to supported  methods of access).

The key problem comes down to indexing.  The heap table could in fact be implicitly indexed the way MySQL's InnoDB does, or it could be unindexed (the way PostgreSQL does it).  If it is indexed, then you have additional issues.  Do you support a physical scan of the first block (to determine where to start)?  If so, how do you determine which starting point to use (if you collect statistics on the data in the tables that would help but that's additional I/O there too)?

I could imagine cases where the number of disk block reads could range from n/3 (where n is the number of records) to (n^1/10)/3, and writes to range from 1 to n/3.

In the end questions like this, particularly where you have software assigned for use in class probably should be directed to your instructor.  It isn't clear what the point of the exercise is and so much depends on implementation.


How to tell if database tuning is fruitful (PostgreSQL) As a DBA, I regularly observe my database, tune up some queries (either getting the programmers to re-write it or add index or update statistics if that's what is needed, etc). I also tune up the server options, etc.

But what I'm not happy with is that I don't have good metrics to say whether my work is worthwhile or not.

On one off queries optimization I can tell whether I got the query to run faster or not. I can use logs and pgfouine to detect slow queries.

But is there overall ways to quantify how well the database is running, metrics to keep track of that will tell me if I'm generally improving things or not really making a difference, etc?

I'm looking for metrics captured at the database level, not at the application level.

How well the database is running is just how well SQL statements are running.

Pick a representative sample of SQL statements that hit each database on your server. Picking a sample is easy; picking a representative sample isn't. Ideally, a representative sample should  include some SELECT, INSERT, UPDATE, and DELETE statements. But it might not be practical to run INSERT, UPDATE, and DELETE statements against a production database.
Put them under version control.
Run them pseudo-randomly against the server. The goal is to get representative performance, not to get the fastest performance.  The last time I did this, I didn't have much time to spend on this stuff, so I hoped that running the statements somewhat randomly would be representative. I expected that sometimes they'd hit the cache, and sometimes they wouldn't, just like user queries.  How you execute these queries can bias your results; be careful and thoughtful.
Time the execution. Save the timings in a database.
Plot the timings on a control chart. Done correctly, this will show you  baseline system performance, random variation around the baseline, and patterns that suggest that something might have changed. If you improve overall server performance, for example, expect the pattern to shift toward the lower control limit.
Think about the right time to change your control limits. (Same reference)


cross database table references I try to create table GroupArticle in database CmsGroup, one column ArticleId references another table in another database? is this possible and correct syntax?

CmsArticle

CREATE DATABASE "CmsArticle"
WITH OWNER = db_admin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;

CREATE TABLE IF NOT EXISTS "Article"(
"ArticleId" SERIAL NOT NULL,
"PublishDate" timestamp without time zone,
PRIMARY KEY ("ArticleId")
);

CmsGroup

CREATE DATABASE "CmsGroup"
WITH OWNER = db_admin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_GB.UTF-8'
LC_CTYPE = 'en_GB.UTF-8'
CONNECTION LIMIT = -1;

CREATE TABLE IF NOT EXISTS "Group"(
"GroupId" SERIAL NOT NULL,
"PublishDate" timestamp without time zone,
PRIMARY KEY ("GroupId")
);

CREATE TABLE IF NOT EXISTS "GroupArticle"(
"GroupArticleId" SERIAL NOT NULL,
"GroupId" integer NOT NULL,
"ArticleId" integer NOT NULL,
FOREIGN KEY ("GroupId") REFERENCES "Group" ("GroupId") ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ("ArticleId") REFERENCES "CmsArticle.Article" ("ArticleId") ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY ("GroupArticleId")
);

NO. You cannot create foreign key references on a different database.

Create the tables on the same database, if there is no specific reason to have them in separate databases.


Rules for Decomposition to 2 NF I am struggling with the concept of 2NF form. Lets say I have a set of functional dependencies for R1(A,B,C,D,E,H,M,K)
Where :

B  -> M,C
AB -> D
DH -> E
H  -> K
A  -> H

Whenever I try to decompose it, I directly get 3NF tables. What are the rules to get something in 2NF, with minimal set of tables ?

Now how do I take this further to 3NF or BCNF?

To simplify, say we have

R(A,B,C,D)

AB -> C
A  -> D

Relation R is NOT in 2NF -- the KEY is {AB}, but {D} is dependent only on part of the key.

Now decomposition

Step 1.   R1 {ABC}  R2 {AD}
Done.

So, both R1 and R2 are in 6NF and therefore in 5,4,3,2,1. There is no in-between -- it may happen that in some specific example you may see "progress" 1, 2, 3, BCNF; but that is an exception, not the rule.

  ... looking at a systematic approach to move from 1 NF to 2 NF to 3
  NF to BCNF ...

is a common misunderstanding.


Best approach instead of granting permissions all the time to logins So, I'm trying to fix logins/users permissions here ( everybody was using DBO for everything, weak passwords and etc ).

For each customer we have, I will create only one login. It will access test databases, homologation, and etc.

I will give the login these permissions:

USE [master]
GO
CREATE LOGIN [User_Customer]
WITH PASSWORD=N'Strong_Pass',
DEFAULT_DATABASE=[Customer_DB],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [Customer_DB]--BULK
GO

USE Customer_DB
GO
CREATE USER [User_Customer]     FOR LOGIN [User_Customer] --User
GO
ALTER ROLE [db_datareader] ADD MEMBER [User_Customer] --DR
GO
ALTER ROLE [db_datawriter] ADD MEMBER [User_Customer] --DW
GO
ALTER ROLE [db_ddladmin]   ADD MEMBER [User_Customer] --DDL
GO
GRANT EXECUTE TO [User_Customer] --EXECUTE
GO
USE [master]
GO
DENY VIEW ANY database to [User_Customer] --DENY

Is there a way to create a single role, where I can only crate the role, and assign the users mapped to this login, to the role?

I don't want to sometime forget a permission and mess everything.

I appreciate any tips about how to manage logins and users better than what I'm currently doing.

(The above script is for sql 2014. It's just for you guys to get the Idea. with sql server 2008, it's a different way ( sp_addrolemember and etc ).

Is there a way to create a single role, where I can only crate the
  role, and assign the users mapped to this login, to the role?

Yes you can do exactly as you said, create a role, add it as a member to the roles you are interested in and grant it permissions the same way you did it with a single user:

create role MyCustomRole;

ALTER ROLE [db_datareader] ADD MEMBER MyCustomRole--[User_Customer] --DR
GO
ALTER ROLE [db_datawriter] ADD MEMBER MyCustomRole--[User_Customer] --DW
GO
ALTER ROLE [db_ddladmin]   ADD MEMBER MyCustomRole--[User_Customer] --DDL
GO
GRANT EXECUTE TO MyCustomRole--[User_Customer] --EXECUTE
GO

Now you can add your users to this role:

alter role MyCustomRole add member [User_Customer];

To control what permissions your user has now just impersonate it:

execute as user = 'User_Customer';

select *
from sys.fn_my_permissions(null, 'database');

revert;

One tip for SQL Server 2014:

I saw you want to deny view any database to your logins.

After such a deny your logins will not see databases in Object Explorer, I mean all the databases included those where they are mapped to.

I think it will render their work more complicated. I don't know what exactly reasons led you to this decision, but if you really need it maybe Contained databases appeared in 2012 is the solution for you.

When you make a database contained, it's database itself that authenticates your users.
This way you do not need logins anymore, every user can be created in its own database directly and will "see" only that database in Object Explorer.

More on contained databases here:
SQL Server 2012 Contained Database Feature


Postgres 13 - how to downgrade password requirements? I have a MS .net application that needs to access a postgres database that's been recently upgraded to v13.
I'm using the Npgsql library (very old version) and get the error Only AuthenticationClearTextPassword and AuthenticationMD5Password supported for now. Received: 10
The suggestion from this post is to comment-out the line password_encryption in the file postgresql.conf and then re-set the users passwords ALTER USER ???? PASSWORD '1234'. I try this and get the same error, I also tried ALTER USER ???? UNENCRYPTED PASSWORD '1234' but this option isn't available anymore.
The basic problem seems to be that postgres 13 wants a level of security that the Npgsql library can't cope with. I can't upgrade Npgsql (not my choice!), so unless I can downgrade the password requirements I'm going to have to regress to an earlier version of postgres
I've tried setting password_encryption = scram-sha-256 also password_encryption = md5 and I've tried commenting-out the whole line. Each time I make the change I run SELECT pg_reload_conf() (as per the comments in the file) and then reset the passwords before trying to log on again. Same error every time.

ok, just found a cure that will fix the immediate problem. I've edited the pg_hba.conf file (thanks @jjanes) and changed the METHOD from scram-sha-256 to md5. This seems to work.

An attacker can try to enumerate which users exist by attempting to log in as a bunch of different user names and seeing what error message is reported, or how long it took to report the error message (or in some cases, how much power the server used before reporting the error or how much noise it made or things like that) for each username.  This can be useful to the attacker because he can then not waste time guessing passwords on for users which don't exist, or can spearphish for users which  he knows do exist.
To make user enumeration hard, PostgreSQL goes through a mock authentication if pg_hba says scram authentication is needed but the requested username doesn't exist; or if the user does exist but either has no password set or has a password stored in the wrong format.  The point of a mock authentication of course is that the client doesn't know it is mock.  So if the client doesn't support scram but scram is required by pg_hba, then you get this error message, regardless of how the password is actually stored.


What is the Postgres's equivalent/closest lock of Microsoft SQL Server's IX (Intent Exclusive) lock? In Microsoft SQL Server, the code below is used to get the IX (Intent Exclusive) lock:
SELECT request_mode FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars');

In PostgreSQL, pg_locks table is available to tell locks information. However, there are many types of locktype, which are:

relation, extend, frozenid, page, tuple, transactionid, virtualxid, object, userlock, or advisory

Which types of locktype from pg_locks is equivalent to IX (Intent Exclusive) lock from Microsoft SQL Server?
If locktype is the wrong column to look at pg_locks, please advise which column in pg_locks should I use?
Thanks

You need to take a step back and check what Intent Locks are made for.
Transaction locking for MS-SQL server is broadly explained here:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide
And for PostgreSQL it's broadly explained here
https://www.postgresql.org/docs/current/mvcc.html
MS-SQL server doc says:

Intent locks are named intent locks because they are acquired before a
lock at the lower level, and therefore signal intent to place locks at
a lower level.
Intent locks serve two purposes:
To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.

There is no equivalent to intent locks in PostgreSQL. The IS, IX, SIX, IU, SIU, UIX lock modes have no equivalent because they refer to a concept that PostgreSQL doesn't have and seemingly doesn't need.