mysql change schema on the fly Recently I faced the problem that my Db became very huge and I cannot allow to myself downtime every time when I want to change the schema.

This caused me to start looking for solution that do schema changing online.

1) pt-online-schema-change. Very attractive project, but honestly I was disappointed. Having downloaded the newest version, I had to change the code in order to get the script working. At the beggining script fails with error messages. All of them I found on the bug report of Percona. After patching script did it's job, hopefully well.

2) oak-online-alter-table. Also very interesting project, facebook mentioned it as good product  on Online Schema Change for MySQL. Tomorrow I gonna try it.

If you are more experienced, please share with us your opinion. What's the best and most reliable product?

My first quesiton is, what is "very huge?"  Depending on the answer to that question, there may be other ways to optimize your migrations to reduce the time that they take.

Second question is, how often are you making schema changes on your live application?

If the online schema change tools don't work for you, you could try this from another angle.  Use master/master replication in active/passive mode to minimize your downtime.

The following assumes that you have db1 and db2 in an active/passive master/master configuration...

Perform the schema change on the db2 with binary logging disabled (SET SQL_LOG_BIN=0 in the session before you perform the ALTER TABLE)
Point your application at db2, thus making it the active server
Perform schema change on db1 with binary logging disabled
optionally, switch back to db1

For this to work, your schema migrations have to be backwards compatible, which is likely the case anyhow in most applications.  This isn't zero downtime, but it is close.  Realistically, most applications can tolerate a small amount of downtime periodically for maintenance.  True zero downtime is a very expensive luxury.

To potentially speed up your table alters, look into using Percona Server and the expand_fast_index_creation setting.

Also, another option for online schema changes is Facebook's OSC.


Find which databases have a particular service in SQL Server Service Broker From what I currently understand, the names of services are global to the instance and you can refer to them without knowing which database contains the service and associated queue etc.  In addition, you can have the same service in different databases on the same instance and in that case, you should use routing and can also use the service_broker_guid to distinguish them when you BEGIN DIALOG CONVERSATION.

Is there a better way than looking in all databases to see what databases the service name exists in?

The sys.services catalog view is restricted to within a particular database context only, either the current database or it can be called with a fully qualified database_name.sys.services.

In particular, I have some triggers that need to send messages to a service in another database, but I don't want them to fail if the service is not there, or if there are two databases with the service, or the database name is different etc.

If performance is too slow to do all these safety checks in the trigger every time, I can do regular maintenance checks to drop them, but whether the trigger is there all the time or not, I still would like the best way to know if the service is defined in the instance at all in one or more databases.

I did manage to come up with this dynamic SQL, but that would probably have to go in a proc or something:

USE master;
DECLARE @DatabasePattern AS varchar(max) = '%';
DECLARE @ServicePattern AS varchar(max) = '%';
DECLARE @SQLTemplate AS varchar(max) = 'UNION ALL
SELECT database_id = {database_id}
    , database_name = ''{database_name}''
    , service_name = s.[name]
    , service_broker_guid = ''{service_broker_guid}''
FROM {database_name}.sys.services s
WHERE s.[name] LIKE ''' + REPLACE(@ServicePattern, '''', '''''') + '''
';
DECLARE @SQL AS varchar(max);

WITH Statements AS (
    SELECT SQL = REPLACE(REPLACE(REPLACE(@SQLTemplate
                    ,'{database_id}', db.[database_id])
                    ,'{database_name}', REPLACE(db.[name], '''', ''''''))
                    ,'{service_broker_guid}', db.[service_broker_guid])
    FROM sys.databases db
    WHERE db.[is_broker_enabled] = 1
        AND db.[name] LIKE @DatabasePattern
)
SELECT @SQL = STUFF((SELECT SQL
FROM Statements
FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, LEN('UNION ALL'), '');

EXEC (@SQL);

you can have the same service in different databases on the same instance and in that case, you should use the service_broker_guid to distinguish them when you BEGIN DIALOG CONVERSATION

No.  Typically you should let Service Broker Routing to find the correct target service for the new conversation.  If you have multiple services with the same name on a SQL Instance, the default behavior is to round-robin new conversations across the matching broker instances.  If you want all the conversations initiated by from a database targeting a service b to route to a specific database, then add a route in the initiating database specifying the broker_id of the target database.  EG

CREATE ROUTE b_route
    WITH
    SERVICE_NAME = 'b',
    BROKER_INSTANCE = 'EF782150-7F7B-4544-937E-B03CF2A3248C',
    ADDRESS = 'LOCAL' ;

This allows you to specify the routing at deployment time, and your service broker code does not need to specify the target broker instance.


Cannot export SELECT result as expected I noticed this issue when exporting the Oracle SQL execution result from Intellij.

The database version is: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production.

My SQL is

SELECT TableA.seq||'-'||TableB.id doc_id
FROM schemaA.TableA
JOIN schemaB.TableB
JOIN schemaA.TableC
JOIN schemaA.TableD ON ...

The result of SELECT COUNT(doc_id) is 137272, however, the exported CSV file has less than 137272 (sometimes 137258, not fixed) lines.

I checked the result and to my surprise found that there are duplicated doc_id which was not expected.

But if I add ORDER BY doc_id to the SQL statement and re-export the result, everything becomes fine.

I have also used SQL*PLUS to export to CSV, but encountered a similar situation.

I thought it might be caused by paging, so I wrote a Java program to get the data. No luck, the output file also has less than 137272 lines, and with some duplicated lines.

If I change the FetchSize to 1000, sometimes the result is OK. If the fetchsize is 100, it is always wrong.

try (Connection conn = DriverManager.getConnection(JDBC_CONNECTION,
        JDBC_USER, JDBC_PASSWORD)) {
    try (Statement statement = conn.createStatement(); FileWriter fw = new FileWriter("out.txt")) {
        statement.setFetchSize(100);

        try (ResultSet rs = statement.executeQuery(sql)) {
            while (rs.next()) {
                fw.write(rs.getString(1));
                fw.write("\n");
            }
        } catch (SQLException ex) {
            // not shown in this code snippet
        }
    }
}

I have asked here, and someone suggested to me to ask in stackexchange, so I ask again here.

Can you try PL/SQL? With FOR loops, Oracle performs implicit bulk collects of 100 rows at a time, as per this link.

Make sure you are doing the order by in your select, as you said this gave you the correct results.  I also recommend you to clean your duplicates.

SET SERVEROUTPUT ON
declare
  cursor c1 is
    select <YOUR SELECT>
begin
  for c in c1 loop
    null;
  end loop;
end;
/


SQL Injection penn testing from the queries only Is there an established method or tool available to perform pen testing on an application by only testing queries it sends to the database?

For example, if I have a bunch of SQL Servers hosting various websites and a query came through that wasn't parameterised, is there a way I can detect these?

Example query that probably isn't secure:

SELECT x,y,z FROM logins WHERE username = 'xx' and password = 'yyy'

...instead I would expect a secure application to be probably be using sp_executesql.

Parameters are passed as an RPC (remote procedure call) to SQL Server over the TDS protocol. Consequently, a parameterized query will show as RPC starting/completed events in a trace (SQL Trace or Extended Events). Depending on the API and application methods used, parameterized queries might call API system procedures (e.g. sp_prepare) instead of sp_executesql or may call user stored procedures directly.

Ad-hoc queries are passed as a batch one or more SQL statements. These will show as a batch starting/completed events in a trace. Ad-hoc queries can be a SQL injection risk when:

the query is not static or
it built in the application code with string concatenation from untrusted sources.

Static queries might contain literals too so one would need to guess at the context and examine the app code to determine if the query is a vulnerability.

Be aware that even parameterized queries can pose an injection risk if built dynamically with a mix of parameters and literals, or by concatenation from untrusted sources. I can't think of a way to identify such practices without examining the app code or finding the vulnerability in penn testing.


access 2003 to sql server 2008 I search a way to export an access 2003 database to sql server express 2008.

the export is on la (other computer than the server).
the computer have office 2003.
the computer doesn't have admin right (cant install things who needs admin)

access sql migration said that he cant(because 2008 is too new)

whats the better(easyer and shortway) to export the db?

You can use the Import/Export wizard with SQL Server. I came across a very good write-up (nice pretty pictures) here on it.


Fetch Only new data from auto-updating online sql database I have an online MySQL (presto) database which keeps on auto-updating (i.e., new rows keep getting added to the tables(views, to be exact) every day). I want to fetch the data from it every day but don't want to refetch the data that I have already fetched before.

I am using python3 for extracting the data from the online host database as:

from pyhive import presto
import numpy as np
cursor=presto.connect().cursor()
sql = 'select * from schema.table'
cursor.execute(sql)
rows = np.array(cursor.fetchall())
cursor.close()

Doing this takes time and fetches all the data (new data as well as already fetched data). I am storing the data in rows variable in a csv file afterward.

Is there any way to get only the new data (id is the primary key in the table)?

Thanks in advance.

Assuming id is an incrementing number or something else which grows every time you insert a new row (such as a timestamp), this should be quite straightforward:

First read the last id value in the csv file from the previous run, and then use this in your query line:

sql = 'SELECT * FROM schema.table WHERE id > %d ORDER BY id ASC' % id

UPDATE:

If id is not an incrementing key, e.g. it's a UUID, then this becomes much harder. Other DBMSes (e.g. Oracle) have rowid which could probably have been used here, though that is not a MySQL feature. MariaDB has rowid in its CONNECT storage engine, so you could set up a local MariaDB instance, install the CONNECT engine, create a CONNECT table connected to your remote MySQL table schema.table with the special rowid column using a statement such as:

CREATE TABLE connect_table (
 col1 float,
 col2 datetime,
 col3 varchar(20),
 row_id int special=rowid
)
ENGINE=CONNECT TABLE_TYPE=MYSQL DBNAME=mytest TABNAME=table
CONNECTION='mysql://root:password@localhost';


SQL 2008: Why is *any* user, with minimal permissions able to restart/stop the SQL instance through Management Studio So I'm on a project that I need to help admin SQL 2008 .
I'm coming from a MySQL background.

Why is it that a basic user, with the most basic/default permissions, through Management Studio is able to restart or stop the entire instance of SQL but not view a simple table?

Correct Answer:
It looks like SSMS looks at the context of the user who opens up SSMS itself, and not the user who connects to the DB engine.

I was able to log in to MS SQL Server as a SQL Server user and I was still able to restart the service under these 2 conditions:

Logged in directly into the server via RDP as a user with admin or start/stop services rights, opened up SSMS, and was able to restart it
Opened up SSMS across a different domain impersonating a domain user which has admin rights on that box using /runas netuse.

Original Answer (Partially Correct):
It seems you might be confusing "MS SQL Server" permissions and "Windows" permissions. The MS SQL Server user permissions don't control access to Windows Services, a MS Windows domain or local user server controls who can or cannot restart the services.  You can modify these settings through secpol.msc.

If you log in to MS SQL Server as a SQL Server user (even sa or a sa equivalent user) the 'restart' option is greyed out.  If you log in to it as a "Windows User" who also has admin or restart services to the Windows machine, then you can restart it.


Is Star/Snowflake database design good for applications? What is the alternative? I need to solve an exercise for a pharmaceutical company, they ask me to create a database structure from scratch given a few informations.
Because I want it to be scalable and "future proof" I decided to follow the star/snowflake structure. For now it looks more star than snowflake but the idea is there. I have a particular question about the main Fact Table (Study) and how to represent the Dimension Table Status.
I came up with 2 options:

OPTION 1: Fact Table Study > Foreign Key > Dimension Table Contract > Foreign Keys > Dimension Table Status.

So we pass through two Dimension Tables in order to represent the Status on the Fact Table. I will do that through a JOIN in a view or whatever...

OPTION 2: Fact Table Study > Foreign Key > Dimension Table Status.

This way we link directly the Fact Table (Study) to the Dimension Table Status.

Should I go for Option 1 or Option 2?
I'm afraid to link too many Dimension Tables to the Fact Table. This is the first time I try to create a database structure.
Any advice is welcome, any suggestion is welcome, especially from seasoned database architects but also from amateurs.
Thank you

EDIT: adding a few more info

Thank you @AntC for your question. In fact this is not at all a Data Warehouse scenario, the scenario is that of a pharmaceutical company that needs a new software to track their clinical trial.
But of course the most known schemas are Star/Snowflake and I don't want to use any hierarchical schema. At the same time I want to avoid shapes like triangle, diamond, circle because even if now there are only 100 users who knows what this database will be in 10 years. The idea is to shape something for the long term and in my opinion the Star/Snowflake shape is choose also for normal application as far as I know.

In Option 2 the Status_Code appears as non-key in two tables: Study, Contract. But which of those entities does it belong to? And what are the relative cardinalities of those entities?
Looking at Option 1, the Status_Code belongs to the Contract; there can be multiple Studys for a given Contract.
So if you were to adopt option 2, you give OLTP a headache: anytime there's a change in Status of a Contract, transaction processing needs to replicate that change to all Studys, with an obvious risk of getting out of synch. That's called an update anomaly.
Denormalising the model like Option 2 is legitimate in data warehouses, because we want fast reporting. It is unwise in a transaction processing-oriented data model, because of the processing cost and risks of maintaining data in synch.
So for an application focussing on transaction processing, Option 1 is indicated. And yes, you would create a Join view Study > Contract > Status.


How do I facilitate access to an Azure based SQL View for non-SQL users on Prem? Afternoon

I'm hoping this one is simple and my general lack of Azure knowledge is the problem, but I need to present data from within a view to some non-SQL end users via some form of application / web view.

Is there a simple tool or function anyone is aware of in Azure that will allow this?  Something akin to a traditional SSRS front end where they can navigate to and run this simple report for example?

Thanks in advance for any direction you can provide.

You can use Power BI service and Power BI Mobile Apps to share reports with others as explained here. Take a look here at the Publish to Web feature in Power BI.

Here you can see how easy is to create a report from SQL Azure data on Power BI.

Hope this helps.


Speed up DB restore from file (mysql) I am running 5.5.60-MariaDB and I need to upload a DB on the server. The .sql file is located on an SSD drive (locally) and it contains ~700 million rows, with 4 columns indexed. Everything is included in the dump file.

My question is, is it normal that it is taking already 24h and still the DB is not up? The server is not busy doing other things, has 16 cores and 125 GB of RAM.

The command I am using is:

mysql -u root myDB < database_dump.sql

My configuration file is as follows:

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/var/lib/mysql/mysql.sock
innodb_buffer_pool_size=4GB

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory
!includedir /etc/my.cnf.d

Both the tmp and data directory are on the same SSD disk, and the sql dump as well. I do not know if this a normal time to expect, or I should change something in my settings, that it why I reach out. The engine of the DB to upload is InnoDB.

Thanks!

If you are unsure anything is happening during the restore here are a few ways to check for activity:

Using Top

From the command line run top. It will show you what's happening right now on the system.  You can then filter the list to look for MySQL by pressing o and typing COMMAND=mysql

If RAM and CPU usage looks fairly low it may indicate the bottleneck being your system's IO being the slowest part while you finish the restore. In the top header you'll see the wa value, which is the time the CPU spends waiting for I/O to complete.

You could interrogate the IO subsystem more by using the linux iostat command. You may be hitting IO bottlenecks but, unless you are planning on doing this regularly, this import task isn't going to be typical system usage so don't worry too much about IO performance just from this. You're importing a 157GB file - that's a lot of data to read, insert and build indexes for!

Mysql client

Log into the server using mysql. Run the command show processlist to see a list of currently running queries. You should see the inserts taking place and changing on each execution of the query.

Filesystem

take a look in the /home/ssd/mysql_datadir directory to see if the physical database files are growing as the data is being inserted. This will show you the file size every two seconds. They'll be growing nicely as the data imports.

watch -n 2 ls -la /home/ssd/mysql_datadir/

ibdata1 will be the data for your database. ib_logfile0 and ib_logfile0 is used by MySQL\MariaDB to help ensure data consistency after an unclean shutdown.

It's a bit too late to restart your import but in future you can use a small utility called pipe viewer to see where in the backup file the import has got to by piping the import through it.

pv database_dump.sql | mysql -u root myDB

It will give you a fancy progress bar and show you the read rate of the file and give an estimated completion time. This is only measuring the reading of the file though, not how long MariaDB is taking to process things.

I hope that helps!


Is there a symbol I can use to have a where clause match all columns? I'm using sql parameter's and I have a query that I'd like to use for two similar queries. The first uses two where clauses the other uses only one of the clauses is there a symbol I can use for the second query to always equal true?

For example:

SELECT path, markdown
  FROM public."Comment"
  where path = 'relative/path/' AND markdown = 'here I am';

Second example:

SELECT path, markdown
  FROM public."Comment"
  where path = '/other/relative/path/' AND markdown = ALL;

All being a stand in for some symbol in the query that would make it effectively only search based on the path (because the markdown where clause would return true on all columns).

I do not agree with the approach you are thinking of using, but if you are dead set on using it, then I would probably structure the query like this:

SELECT path, markdown
  FROM public."Comment"
  where path = '/other/relative/path/' AND (TRUE OR markdown = 'here I am');

Essentially adding a 3rd parameter to your query .. one use case would use the 3rd param and set the 2nd param to FALSE, while another would set the 2nd param to TRUE..


Database management tool for compact edition (.sdf) database What is the best database management tool for remotely managing a compact edition (.sdf) database created in WebMatrix?

I'm not sure what it's the best, but one of the most popular is SQL Server Compact Toolbox. Also many alternatives are listed at ErikEJ's blog.


SQL Server Setup has encountered the following error: Mixed mode assembly is built against version 'v2.0.50727' I had SQL Server 2016 Standard Edition installed on a server. Due to some drive reconfiguration, I had to do a reinstallation. I uninstalled using the program and features and just as I was about to launch the setup to install it again, I went ahead and deleted the "Microsoft SQL Server" folder under "Program files".
Most of the files were deleted and few remained, mainly in "C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016".
The problem is now when I am trying to launch the setup, it displays the error:

TITLE: SQL Server Setup failure.
SQL Server Setup has encountered the following error:
Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.
Error code 0x84B10001.

Not sure how to proceed next.

Not a direct answer but some suggestion:

Use SQL Server Installation Center to start: System Configuration Checker. This tool will check for conditions that prevent a successful SQL server installation.

You can alterantivly:

1) Check setup log files at %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log

2) Check Your .net 4.0 installation and search your GAC for redundant dll files:


Copy complete structure of a table Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:

select * into dbo.table2 from dbo.table1;

This is just a simple copy of the table; all of the indexes / constraints are missing. How can I copy a table structure (without using a backup)?

I am primarily looking to do this manually, but if that's not possible I'll accept any solution.

You can script a table relatively easy using the UI of course:

This will output a CREATE TABLE script and you only have to search and replace the old name with the new name (and verify that an object with the new name doesn't already exist).

But if you're trying to automate this (e.g. generate the create table script in code), it is a little more cumbersome. The above scripting option doesn't just pull the entire CREATE TABLE DDL from a single location in the metadata; it does a whole bunch of magic behind the scenes in the code to generate the eventual CREATE TABLE script (you can use Profiler to see where it gets its data, but you can't see how it assembles it). I suggested an option for this:

http://connect.microsoft.com/SQLServer/feedback/details/273934

However this was met with very few votes and was quickly shot down by Microsoft. You may find it much more worthwhile to use a 3rd party tool for generating schema (I've blogged about this).

In SQL Server 2012 there are new metadata functions that allow you to get much closer than the work you have to do in 2005, 2008 and 2008 R2, piecing together column information from the metadata (which has a lot of caveats, for example if it's decimal you have to add the precision/scale, if [n[var[char]] you have to add the length specification, if n[var]char you have to cut the max_length in half, if it is a MAX you have to change -1 to MAX, etc etc). In SQL Server 2012 this part is a little easier:

SELECT name, system_type_name, is_nullable FROM
  sys.dm_exec_describe_first_result_set('select * from sys.objects', NULL, 0)

Results:

name                   system_type_name  is_nullable
--------------------   ----------------  -----------
name                   nvarchar(128)     0
object_id              int               0
principal_id           int               1
schema_id              int               0
parent_object_id       int               0
type                   char(2)           0
type_desc              nvarchar(60)      1
create_date            datetime          0
modify_date            datetime          0
is_ms_shipped          bit               0
is_published           bit               0
is_schema_published    bit               0

I've blogged about this, too.

Arguably this is much closer to your targeted CREATE TABLE statement than a convoluted approach using sys.columns, but there is still a lot of work to do. Keys, constraints, text in row options, filegroup information, compression settings, indexes, etc. It's a very long list and I'll once again suggest you look at a 3rd party tool for this instead of, at the risk of repeating an over-used analogy, re-inventing the wheel.

That all said, if you need to do this through code but you can do it outside of SQL Server, you can consider SMO/PowerShell. See this tip and the Scripter.Script() method.


Fixing corrupted Firebird database I have a 200 GB Firebird database (created with Firebird 2.1.3, currently running 2.1.5), and it appears to be corrupted. When I try to do a backup I get:

  database file appears corrupt <>
  wrong page type
  page 0 is wrong type (expected 0 found 1)
  gds_get_segment failed

gfix ends with a

  deadlock

How can I fix it?

UPDATE

While this particular database wasn't fixed, i guess the solution is try IB FirstAID.

Recommended reading:

Common InterBase/Firebird errors caused by corruptions and their recovery chances
Firebird and InterBase corruptions reasons

Here's a short step-by-step walkthrough:

disconnect users and disable incoming connections to the database
make a copy of database file (or two copies) and work on that
use GFIX with -v option to validate the database file
use GFIX with -v and -f to do full validation

If problem is not too serious, you can try to backup the broken db and restore under a new name:

use GFIX -mend to prepare corrupt database for backup
use GBAK -b -g to backup the database. -g disables garbage collection
use GBAK -c to restore backup to a new database.

If you succeed, you have fixed the problem and have a functional database. If not, you can try to create an empty database with the same structure and pump the data to it .

One of the reasons why backup or restore can fail is if some broken database triggers exist, and prevent connection to the database. For example, a database trigger might use some table which has a broken index, etc. To work around this, connect to database with isql tool using -nodbtriggers option and then disable those triggers. You can enable them later when you fix other problems and get a working database again.

Another reason restore might fail is when you have broken data, so some of validity constraints (check constraints, etc.) cannot be satisfied. In this case, you can try to restore your database using -N[O_VALIDITY] command switch to gbak.

If you're interested in a more detailed information of the process of fixing the database, as well as explanation of some types of corruption, take a look at the following page:

http://www.ibphoenix.com/resources/documents/search/doc_5

If all fails, you can try IBSurgeon tool, which is able to fix most problems and extract data. Also, IBSurgeon's website has a detailed
explanation of causes of database corruption and ways to fix it:

http://ib-aid.com/option,com_content/task,view/id,58/Itemid,62/

check this guide http://www.firebirdfaq.org/faq324/


What's the difference between WITH CTE & WITH CTE ()? As shown in Using Common Table Expressions on MSDN, you can define a CTE as:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

and use it like:

SELECT <column_list> FROM expression_name;

Let's say I have following 2 CTEs

with cte1 as(
select name from Table1
)

with cte2(name) as(
select name from Table1
)

A query outputs the same results for both CTEs as the inner query is same. The only difference between these two is that cte2 has column name((name)) defined in its declaration.

When I execute both CTEs, I don't see any difference in the execution plan.

I am just curious to know:

What difference does it make if I don't specify any column names in CTE definition?
Why I should/should not specify column names while creating CTE?
Does it affect query execution plan by any chance? (As far as I have seen, it doesn't make any difference.)

You nearly have the answer for one of your questions already.

In the MSDN page, there is a line directly after your quote that explains this:

  The basic syntax structure for a CTE is:

  WITH expression_name [ ( column_name [,...n] ) ]

  AS

  ( CTE_query_definition )

  The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

(Emphasis added)

This would mean that you would need to specify column names in a few situations:

This would work:

WITH [test_table] ([NoName], [CAST], [Function])
AS
(
    SELECT
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];

as would this:

WITH [test_table]
AS
(
    SELECT
        1 as [NoName]
      , CAST('1' AS CHAR(1)) as [CAST]
      , dbo.CastToChar(1) as [Function]
)
SELECT * FROM [test_table];

But this would not as it does not have distinct names for the columns:

WITH [test_table]
AS
(
    SELECT
        1
      , CAST('1' AS CHAR(1))
      , dbo.CastToChar(1)
)
SELECT * FROM [test_table];


What is a “part-key”? I'm a beginner and student with regards to database design. The tutor recently discussed “part-keys”. I am struggling to find any information on what a part-key is, and I suspect it probably goes by another name.

The example we were given in class was this:

ACTIVITY(Activity(PK), Fee)
STUDENT_ACTIVITY(StudentID(PK), Activity(PK))

  Is the table showing part-key dependency?

  Yes. Since the second determinant (Activity) is part of the primary key (StudentID, Activity).

Is anyone able to give me any further information?

This example does not contain a partial dependency to me.

A partial dependency would be if a table column functionally depends on part of the table's primary key, like in this denormalized table:

CREATE TABLE student_activity (
   studentid bigint NOT NULL,
   activity character varying(30) NOT NULL,
   fee numeric(10,2) NOT NULL,
   PRIMARY KEY (studentid, activity)
);

where fee depends on activity.

The solution would be to split this table in two parts like in your question.


MySQL: How turning off the logging affect the performance? OS: RHEL5
DB: MySQL 5.1.73 community edition
DB size: ~1GB

I have setup a testing environment and turned off all logging - general logs, binary logs, slow query logs etc.

I am using Oracle application testing suite (OATS) to run the load test. I am getting poor load test result (higher timing and less passed transactions) after logging off. Ideally I should get better result.

Is it correct or I am missing something?
What is the affect of getting log off?

Below is the result of

SELECT * FROM information_schema.global_variables WHERE VARIABLE_Name like 'log\_%' OR variable_name like '%\_log'

gives me:

+---------------------------------+----------------------------+
| VARIABLE_NAME                   | VARIABLE_VALUE             |
+---------------------------------+----------------------------+
| BACK_LOG                        | 50                         |
| LOG_BIN_TRUST_ROUTINE_CREATORS  | OFF                        |
| LOG_WARNINGS                    | 1                          |
| LOG_OUTPUT                      | FILE                       |
| LOG_QUERIES_NOT_USING_INDEXES   | OFF                        |
| LOG_ERROR                       | /var/lib/mysql/rpc4939.err |
| GENERAL_LOG                     | OFF                        |
| LOG_BIN                         | OFF                        |
| RELAY_LOG                       |                            |
| LOG_SLOW_QUERIES                | ON                         |
| SLOW_QUERY_LOG                  | ON                         |
| LOG_BIN_TRUST_FUNCTION_CREATORS | OFF                        |
| LOG_SLAVE_UPDATES               | OFF                        |
+---------------------------------+----------------------------+

Except for the general log and the slow log with very small long_query_time, performance shouldn't get affected too much when on, as most logging should be written to the filesystem cache (those logs do not need to be transactional safe).

There are certain combination of configuration and workloads that certainly can affect negatively performance:

example: binary log with binlog_sync and no group commit on a heavy-write server
binlog disallows group commit for innodb in earlier versions of mysql
huge transactions altering blobs in row-based replication
workload is mostly very fast primary key lookups, que log those queries in text (a read generates a write)
Error log constantly outputing critical errors with GBs of information

But in general, with a health filesystem cache (not too much memory pressure), things should be ok (small to almost negible amount of overhead, or admissible thanks to the extra features that they provide). For example, even the binary log added, let's say, a 5% overhead, it allows read scales through replication and point in time recovery.

What should not happen is that disabling them reduces performance. Either that is an error of measure or it is provoked by something else.

Also, for future readers, be careful not to confuse "server logs" (error, general, slow, binary) with the InnoDB transaction log.


How to SELECT a sibling in an adjacency list type table? I'm trying to get the next sibling in a list according to an integer position column. Rows are siblings when they have the same parent_id.
Given that sibling foo.id = 192 and foo.position = 0, I want to get sibling bar that has a position of foo.position + 1
The below query returns the right result except that the AND a.position = 0+1 should not be hardcoded.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 192
)
AND a.position = 0+1
AND a.root_id = b.id;

The following query won't work of course but shows what I'm trying to do.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id, position as pos FROM tasks WHERE id = 192
)
AND a.position = pos+1
AND a.root_id = b.id;

Is this possible without first querying the needed information and saving it into a variable to use in a second query?
Would it be faster than saving the result of a "pre-query" to use in a second query?
EDIT:
Here's some test queries that set up a demo
Setup
CREATE TABLE tasks (
    id int unsigned NOT NULL AUTO_INCREMENT,
    root_id int unsigned,               /* top level task */
    parent_id int unsigned,             /* the task id that is the parent of this task */
    `title` varchar(255) NOT NULL,
    position int unsigned,              /* the index in the array that will contain this task */
    FOREIGN KEY (root_id) REFERENCES tasks(id),
    FOREIGN KEY (parent_id) REFERENCES tasks(id),
    PRIMARY KEY(id)
);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES
(NULL, NULL, 'Foo Project', 0),
(NULL, NULL, 'Bar Project', 1),
(NULL, NULL, 'Baz Project', 2);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES
(1, 1, 'Foo Child A', 0),
(1, 1, 'Foo Child B', 1),
(2, 2, 'Bar Child A', 0);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES
(2, 6, 'Bar Child A Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 2', 1);

Query for needed info
The following query is suboptimal but retrieves the info needed.
SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 8
)
AND a.position = (SELECT position FROM tasks WHERE id = 8)+1
AND a.root_id = b.id;

It seems you need

SELECT t1.title taskTitle,
       t2.title projectTitle
FROM tasks t1
JOIN tasks t2 ON t1.parent_id = t2.parent_id
             AND t1.position + 1 = t2.position
WHERE t1.id = 192


How to fetch the columns of the synonyms for linked servers in SQL server I am using two servers local (SQL Server running on my system) and remote (SQL Server running on another system). I have created synonyms in my local server for a table which is located on the remote server.

Synonym used- CREATE SYNONYM [dbo].[test] FOR remoteserver_name .[database_name].[schema_name].object_name

Requirement

I need to fetch the columns of the synonym.

I can retrieve the columns of the synonym using below query in SQL server.

select * from dbo.test

where

dbo is synonym created schema name (local server schema name) and test is synonym name.

I have tried to retrieve the columns of the synonyms using below query, but I cannot fetch the columns

SELECT sys.schemas.name   AS SCHEMA_NAME,
       sys.synonyms.name  AS view_name,
       sys.columns.name   AS COL_NAME,
       sys.types.name     AS data_typename
FROM   sys.columns
       INNER JOIN sys.synonyms
            ON  OBJECT_ID(sys.synonyms.base_object_name) = sys.columns.object_id
       INNER JOIN sys.schemas
            ON  sys.schemas.schema_id = sys.synonyms.schema_id
       LEFT OUTER JOIN sys.types
            ON  sys.columns.system_type_id = sys.types.system_type_id
WHERE  sys.types.system_type_id = sys.types.user_type_id
       AND sys.schemas.name = N'scehmaname'
       AND sys.synonyms.name = N'synonymname'

Can you please suggest some other way to retrieve the columns of the synonym or correct the above query?

Your error is that you ask your local server about the object from another, remote server.

OBJECT_ID(sys.synonyms.base_object_name) from your code will be resolved on a local server because OBJECT_ID() operates within local server, but you pass to it the name from remote server, so you'll get NULL here.

If you want to get remote object's object_id, you should do smth like this:

exec ('select object_id(''[remote_db].[remote_schema].[remote_table]'')') at [remote_server]

And in general, if you want to get the columns of remote object, you should execute your code on the remote server, looking for the columns of remote object in the remote sys.columns in the remote database, for example, this way:

declare @obj_name sysname = '[remote_server].[remote_db].[remote_table]';
declare @code nvarchar(4000) =
N'select c.name   AS COL_NAME,
       t.name     AS data_typename
from sys.columns c
     join sys.types t
        on c.system_type_id = t.system_type_id
where c.object_id = object_id(@obj_name)';

declare @param_def nvarchar(400) = N'@obj_name sysname';

execute [remote_server].[remote_db].dbo.sp_executesql @code, @param_def, @obj_name = @obj_name;

UPDATE 0

  Scenario : I have server A which has two database( database1 and
  (database2 ). I have created synonym in the database1. CREATE SYNONYM
  [dbo].[synonym] FOR database2.[dbo].[objectname] How to write query
  commonly to get object_id for this scenario and remote object ?

As Scott Hodgin mentioned, you can use PARSENAME() to get rid of remote_server's part of your object like this:

declare @cmd varchar(8000) = (select
       'select object_id(' +
       quotename (
       PARSENAME(base_object_name,3)  + '.' +
       PARSENAME(base_object_name,2)  + '.' +
       PARSENAME(base_object_name,1), '''')+ ')'
from sys.synonyms )

exec (@cmd) at [server_A];

UPDATE 1

  I want to know whether I can access the stored procedure of the remote
  server(Server B) from local server(Server A).

  ...

  I cannot get the list of the stored procedure.

You can get the list of remote stored procedures using this code:

select *
from remote_server.remote_db.sys.objects
where type = 'p';


MySQL 5.1.73 to 5.6.17 structure + data export Are there any known issues to export structure and data from MySQL 5.1.73 to 5.6.17? Nothing fancy and to export-import user will use phpMyAdmin.

Thanks

Should be binary compatible, but I would ALWAYS use mysqldump. See here (options not always optimal) and here (phpMyAdmin can't handle large dbs) for why. See here and here for how. And let's not forget the docco!


Multiple disks vs single disk on virtualized environment We're planning to install a SQL Server on this physical architecture:

A hyper-v cluster based on windows server 2019
A cluster shared volume where all VMs live
A full-flash SAN that contains the CSV

I've always read that is a common best practice to have multiple disks for data files, log files, temp db files ect...
But that best practice is referred to a bare-bone installation.
So, what I'm asking is: what are the actual benefits of doing so on the above described architecture (if any)?

what are the actual benefits of doing so on the above described architecture (if any)?

There are some management benefits, particularly:

You get separate disk performance counters in the VM for Data, Log, and TempDb

You can migrate any one of them to a different storage device without reconfiguring the VM or SQL Server.

You can manage Storage Quality of Service seperately for each virtual disk, capping or reserving IOPS.

You get seperate IO queues in the guest VM which may marginally improve performance, especially in a scenario where perf-critical log writes are intermixed with large volume data file IO.


Advisable number of foreign keys in a single table I was wondering, keeping performance and optimizations in mind, while designing the database, is there any advisable number of foreign keys you should keep in a single table?

At present, few of my tables are using 4 foreign keys. Would like to know, if number of foreign keys in a single table causes some performance or scalability issues?

I am using MySql

Yes, ultimately FKs can affect performance. They will certainly affect insert/delete performance (more lookups), but you have the advantage of guaranteed data integrity.

You should be more than happy to pay a high price for data integrity - a high performance database with inconsistent data is worse than useless - certainly not as much use as a database that's a bit slow but with consistent data.

Yah gets what yah pays for!


MySQL - Unable to login with password, but without password its working I have a master-slave setup, Slave is not in read only mode.(we can do all writes).
I have created a user.

create user 'bhuvi'@'%' identified by 'bhuvi';
grant all privileges on *.* to 'bhuvi'@'localhost';
flush privileges;

But when I try to login, its showing

[root@1user]# mysql -u bhuvi -p
Enter password:
ERROR 1045 (28000): Access denied for user 'bhuvi'@'localhost' (using password: YES)
[root@1user]#

But I can able to login with -p

mysql -u bhuvi

I don't why its happening.
AndI tried to create users like `'user'@'localhost, this is working, But I don't know why the user has '%' is not working.

(Im trying access the user login on the same server where the MySQL is installed)

network mask - is part of user name in MySQL

create user 'bhuvi'@'%' identified by 'bhuvi'; - You are create user with password
grant all privileges on . to 'bhuvi'@'localhost'; - You create 2nd user without password

1st have password, but not privileges

2nd have all privileges, but not password


How to query hstore tag keys that match part of the filter string In PostgreSQL query, how can I filter the results by using any record that tags with a key like %building%

something like:

..
where
    Table.tags ? '%building%'

I didn't find an answer within the postgresql documentation (https://www.postgresql.org/docs/9.6/static/hstore.html)

You need to create a set of the keys in order to be able to compare them using like.

Something like:

select *
from the_table t
where exists (select *
              from skeys(t.tags) as x(tagname)
              where x.tagname like '%building%');

But this is not going to be fast.


Designing Students Exams and Classes Database I'm trying to build a database for students official exams where a student study in a school and does his official exams in a different school.

My current CDM:

After converting this CDM into PDM then into SQL I'm having some problems using the database.

My problems with the current design is:

Student table has a SCHOOLID for the school that the student studies in and SCH_SCHOOLID for the school the student will be doing his exams in and this is what I actually want. The problem is that I'm not sure how to link the student exam school with his CLASSROOM and SEAT in that school.
Another thing I need in my database is that one or many CLASS can have the same EXAM with the same LANGUAGE or in a different LANGUAGE. The generated SQL does not seem to provide the option to do that.

Any help on how I can improve my CDM to get the desired result is much appreciated.

This is my generated SQL:

My thoughts after a quick review (edited):

An Exam should not have a Day/Start Time/End time. The Exam entity represents the EXAM definition, it should have subject, year etc, NOT the time it is to be sat. I assume from your design that a class can have multiple exams. What you are missing is an ExamEvent entity. This would represent an exam event, where everyone sits down and takes it, NOT the definition of an exam (as the same exam can happen in different schools, by your description). An ExamEvent needs to have an ExamID, ClassCode (think about having a surrogate ID for the CLASSEXAM Entity), Time/Date, LanguageID (assuming everyone sits the exam at the same time in the same language), RoomCode. It should also have its own surrogate key, if needed: ExamEventID. Review the name of this entity.
It looks like you are then missing another key entity - a StudentExam entity, which would have: StudentID, ExamEventID and SeatCode. This represents the student attending the exam and where they are sitting. If each student sits the exam in a different language, but at the same time, the Language entity needs to relate to this entity NOT the ExamEvent entity.
Sticking with what I have specified above, you should then remove the 2nd Student/School link (currently representing the place the student is taking the exam). Instead, using the design I specified, the place the student is taking the exam is defined by the StudentExam object as it links via ExamForClass, through to ClassRoom, through to School.


Do I need to detach a database to attach it? I am trying to move data from one SQL Server server to another (located on another computer). I know of the detach/attach method, but I don't want to disturb the prod environment (detaching a prod database is out of the question). Can't I just copy/paste the MDF and LDF files IF nothing is being actively written to/read from  the prod?

What if there are processes that are reading or writing to the database, what are my options to create an "attach" file without performing detach?

Thank you very much. I read on two questions related to this but neither one applies here.  One suggests using backup/restore, but that seems a bit difficult.

Unfortunately you can't just copy the "hot" mdf & ldf files, even while no data is actually being written to the DB. As long as the files are attached to a SQL Server you cannot copy or move them. In fact I don't think Windows will let you even copy them as they should be locked by the server process.

The trivial solution is just using backup and restore, it's painless and secure. In fact there should be already be a regular backup process since you mention that this is production system, maybe you don't even need to actually take a backup, just wait for the next one to take place and use it to restore. On SQL Server the backup/restore process is really straightforward, probably one of the easiest on all the RDBMS ecosystem.

Any other method will be more difficult and likely yield worse results. I wouldn't recommend them at all. You could try to copy the files using VSS (Volume Shadow Copy) and see if you get consistent enough files to attach. Alternatively you could create an empty database and just export all the tables, views, procedures, etc.


pg_ctl won't start postgres I'm new to PostgreSQL.  Although I could access a localhost database either with PGAdminIII or psql (eg sudo -u postgres psql mydb), I've not been able to connect from Dart.  I think my difficulties are with Postgres.

Previously when I've used Postgres (Windows 10) I could start the server with

pg_ctl start -l ~/pglog.txt

Now (ubuntu 16.04), this same command gives me this error:

pg_ctl: could not open PID file
    "/var/lib/postgresql/9.5/main/postmaster.pid": Permission denied*

(PGDATA is set to /var/lib/postgresql/9.5/main)
Folder main has user/group of postgres/postgres as does it's parent, postgresql.  /var/lib has owner/group root/root.

If I run the same command as the postgres user (su -c 'pg_ctl start -l ~/pglog' postgres), after entering the password, the error message is:

su: Authentication failure

(I'm sure I have the pw correct, but is there some way to check/change/confirm that...bearing in mind that I now can't get the server running.)

The bin folder is in my path. There are postmaster.pid and postgresql.conf files in the data directory.

I have changed authentication for local users in pg_hba.conf to md5 rather than peer.

I think I installed postgresql with this command:

sudo apt-get install postgresql postgresql-contrib

ref (https://help.ubuntu.com/community/PostgreSQL).

Having changed a few permissions, then, as far as I can tell, changed them back again, the server no longer runs on bootup.  That is, I can now no longer connect with either PGAdminIII or psql.  I've not been able to find out how it would have started previously.

I feel sure that I have some permissions incorrect?  Other suggested topics on StackOverflow have not been sufficient to allow me to resolve this.  One answer is the following:

  Sounds like a permission problem on the data directory. Did you check if the postgres (operating system) user has full access to /usr/local/pgsql/data and all sub-directories?*

Another answer included:

  "Permission denied" is usually a problem with the access rights on the data directory. Make sure the postgres user account has full access to /usr/local/pgsql/data

8 March:
Thanks Roman.  Using your suggested command I get

sudo: pg_ctl: command not found

But running without sudo I got the following promising message (which is weird given that that's essentially what I was using before but with no response):

pg_ctl start -l /tmp/pglog.txt
server starting

Notwithstanding this, I still don't think the server is running - this is the message from either PGAdmin3 or psql.

Evan, thank you also. The latter gives no error and returns to the prompt after perhaps 2 seconds - so it appears to be doing something.  But trying to connect with PGAdmin3 or psql still indicates that no server is listening.  Eg:

psql postgres
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I've checked postgres.conf and see that the port seems to be correct.

In Ubuntu you don't start PostgreSQL with pg_ctl. You can, but don't. Instead, use this

sudo service postgresql start

su -c will only work if you enable the root account. Don't do that. Instead, use sudo.

See my answer here for how to get up and running on Ubuntu


Hotel booking schema What is the correct way to design my application, for a Hotel reservation system.

So far i've got 3 tables

Customer

id pk

Reservation

id pk
customer_id fk
check_id_date
check_out_date

Room

id pk

And the premise is: a customer can have multiple reservations.
A reservation could include more than one room.
I understand the relationships between them are 1:many for Customer-Reservation.

My question is how should i model the relationship between Reservation & Room.
If i set it as 1:many, i dont take into account the date for the reservation.

There's no one correct way of doing it. What I would do, is to introduce another table on top of what you already have. A table that I would call ReservationHeader, this is the table that would hold a header information and then beneath it I would add another table called RoomReservation which will hold multiple bookings related to ReservationHeader. FInally, I will create another table called Room which holds all data about rooms;

Customer {id (pk), Customer_Name, etc, etc, ....}
ReservationHeader {id (pk), customer_id(fk), reservation_date, etc, etc, ...}
RoomReservation {id (pk), reservation_header_id (fk), room_id (fk), check_in_date, check_out_date, etc }
Room {id (pk), room_number, room_type, etc, etc, ... }

See the diagram below for clarity. You can then add other fields and build on top of that.

You might also might try to think ahead and consider how you are going to query the tables to check for room availability. There are already plenty of good answers covering that stackexchange.


Improve Query Performance I have the following sqlite3-Tables:

db.execute("""CREATE TABLE IF NOT EXISTS STATIONS
       (StationUID INTEGER PRIMARY KEY AUTOINCREMENT,
       StationNumber           TEXT    NOT NULL,
       StationName             TEXT    NOT NULL,
       StationLongitude        REAL    NOT NULL,
       StationAltitude         REAL    NOT NULL);""")

print "Created table STATIONS successfully"

db.execute("""CREATE TABLE IF NOT EXISTS TRAVELDAYS
       (TrainDaysUID           INT  NOT NULL,
       Day                     INT  NOT NULL,
       Value                   INT  NOT NULL,
       PRIMARY KEY (TrainDaysUID, Day));""")

print "Created table TRAVELDAYS successfully"

db.execute("""CREATE TABLE IF NOT EXISTS TRAINS
       (TrainUID INTEGER PRIMARY KEY AUTOINCREMENT,
       TrainNumber           TEXT    NOT NULL,
       TrainDaysUID          INT     NOT NULL,
       TrainType             TEXT    NOT NULL,
       TrainZone             TEXT    NOT NULL,
       FOREIGN KEY(TrainDaysUID) REFERENCES TRAVELDAYS(TrainDaysUID));""")

print "Created table TRAINS successfully"

db.execute("""CREATE TABLE IF NOT EXISTS ROUTES
       (RouteUID INTEGER PRIMARY KEY AUTOINCREMENT,
       StartStationUID       INT     NOT NULL,
       EndStationUID         INT     NOT NULL,
       FOREIGN KEY(StartStationUID) REFERENCES STATIONS(StationUID),
       FOREIGN KEY(EndStationUID) REFERENCES STATIONS(StationUID),
       UNIQUE (StartStationUID, EndStationUID) ON CONFLICT IGNORE);""")

print "Created table ROUTES successfully"

db.execute("""CREATE TABLE IF NOT EXISTS LEGS
       (LegUID INTEGER PRIMARY KEY AUTOINCREMENT,
       TrainUID            INT     NOT NULL,
       RouteUID            INT     NOT NULL,
       StartTime           INT     NOT NULL,
       EndTime             INT     NOT NULL,
       TravelTime          INT     NOT NULL,
       DwellTime           INT             ,
       FOREIGN KEY(TrainUID) REFERENCES TRAINS(TrainUID),
       FOREIGN KEY(RouteUID) REFERENCES ROUTES(RouteUID));""")

print "Created table LEGS successfully"

Table STATIONS has 10965 entries
Table ROUTES has 13026 entries
Table LEGS has 610593 entries
Table TRAINS has 44920 entries

I need to improve the performance of this query:

cr.execute("SELECT l.TrainUID, l.RouteUID, l.LegUID, l.StartTime, l.EndTime, l.TravelTime, l.DwellTime, s1.StationName, s2.StationName "
           "FROM LEGS AS l "
           "INNER JOIN ROUTES as r ON (r.RouteUID = l.RouteUID) "
           "INNER JOIN STATIONS AS s1 ON (r.StartStationUID = s1.StationUID) "
           "INNER JOIN STATIONS AS s2 ON (r.EndStationUID = s2.StationUID) "
           "WHERE l.StartTime >= 800 AND l.EndTime <= 1200 AND "
           "l.TrainUID IN ( "
                "SELECT DISTINCT l.TrainUID "
                "FROM LEGS AS l "
                "INNER JOIN ROUTES as r ON (r.RouteUID = l.RouteUID) "
                "INNER JOIN STATIONS AS s1 ON (r.StartStationUID = s1.StationUID) "
                "INNER JOIN STATIONS AS s2 ON (r.EndStationUID = s2.StationUID) "
                "INNER JOIN TRAINS AS t ON (t.TrainUID = l.TrainUID) "
                "INNER JOIN TRAVELDAYS as d ON (t.TrainDaysUID = d.TrainDaysUID) "
                "WHERE t.TrainDaysUID == d.TrainDaysUID AND d.Day == 300 AND d.Value == 1 AND (s1.StationNumber = 8000105 OR s2.StationNumber = 8000105)) ")

What can you recommend?

EDIT
I created now those indexes, but it is not really faster.

db.execute("""CREATE INDEX Idx1 ON TRAVELDAYS(TrainDaysUID, Day, Value);""")
db.execute("""CREATE INDEX Idx0 ON STATIONS(StationNumber);""")

Running EXPLAIN QUERY PLAN on your query gives this:

0|0|0|SCAN TABLE LEGS AS l
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|5|SEARCH TABLE TRAVELDAYS AS d USING AUTOMATIC PARTIAL COVERING INDEX (Day=? AND Value=?)
1|1|4|SEARCH TABLE TRAINS AS t USING AUTOMATIC COVERING INDEX (TrainDaysUID=?)
1|2|0|SEARCH TABLE LEGS AS l USING AUTOMATIC COVERING INDEX (TrainUID=?)
1|3|1|SEARCH TABLE ROUTES AS r USING INTEGER PRIMARY KEY (rowid=?)
1|4|2|SEARCH TABLE STATIONS AS s1 USING INTEGER PRIMARY KEY (rowid=?)
1|5|3|SEARCH TABLE STATIONS AS s2 USING INTEGER PRIMARY KEY (rowid=?)
1|0|0|USE TEMP B-TREE FOR DISTINCT
0|1|1|SEARCH TABLE ROUTES AS r USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE STATIONS AS s1 USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE STATIONS AS s2 USING INTEGER PRIMARY KEY (rowid=?)

Any "AUTOMATIC" index is an index that SQLite thinks is worth creating temporarily; try this:

CREATE INDEX x1 ON TRAVELDAYS(Day, Value);
CREATE INDEX x2 ON TRAINS(TrainDaysUID);
CREATE INDEX x3 ON LEGS(TrainUID);


Populating missing data based on previous month-end values Given the following data:

create table #histories
(
    username varchar(10),
    account varchar(10),
    assigned date
);

insert into #histories
values
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-08'),
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07') ;

... which represents when a given user was assigned to an account.

I am looking to establish who owned a given account on the last day of each month (the assigned date is the date that the account transferred ownership), with any missing month-ends populated (possibly created from a handy dates table that I have available, with useful columns DateKey, Date and LastDayOfMonth, [courtesy of @AaronBertrand])1.

The desired results would be:

PETER, ACCOUNT1, 2017-01-31
PETER, ACCOUNT1, 2017-02-28
DAVE, ACCOUNT1, 2017-03-31
DAVE, ACCOUNT1, 2017-04-30
FRED, ACCOUNT1, 2017-05-31
FRED, ACCOUNT1, 2017-06-30
FRED, ACCOUNT1, 2017-07-31
JAMES, ACCOUNT1, 2017-08-31
PHIL, ACCOUNT2, 2017-01-31
PHIL, ACCOUNT2, 2017-02-28
PHIL, ACCOUNT2, 2017-03-31
JAMES, ACCOUNT2, 2017-04-30
PHIL, ACCOUNT2, 2017-05-31

Doing the initial part of this with a windowing function is trivial, it's adding the "missing" rows that I'm struggling with.

One approach to this problem is to do the following:

Emulate LEAD on SQL Server 2008. You can use APPLY or a suquery for this.
For rows without a next row, add one month to their account date.
Join to a dimension table that contains month end dates. This eliminates all rows that don't span at least a month and adds rows to fill in the gaps as necessary.

I modified your test data a little bit to make the results deterministic. Also added an index:

create table #histories
(
    username varchar(10),
    account varchar(10),
    assigned date
);

insert into #histories
values
('PHIL','ACCOUNT1','2017-01-04'),
('PETER','ACCOUNT1','2017-01-15'),
('DAVE','ACCOUNT1','2017-03-04'),
('ANDY','ACCOUNT1','2017-05-06'),
('DAVE','ACCOUNT1','2017-05-07'),
('FRED','ACCOUNT1','2017-05-08'),
('JAMES','ACCOUNT1','2017-08-05'),
('DAVE','ACCOUNT2','2017-01-02'),
('PHIL','ACCOUNT2','2017-01-18'),
('JOSH','ACCOUNT2','2017-04-08'), -- changed this date to have deterministic results
('JAMES','ACCOUNT2','2017-04-09'),
('DAVE','ACCOUNT2','2017-05-06'),
('PHIL','ACCOUNT2','2017-05-07') ;

-- make life easy
create index gotta_go_fast ON #histories (account, assigned);

Here's the laziest date dimension table of all time:

create table #date_dim_months_only (
    month_date date,
    primary key (month_date)
);

-- put 2500 month ends into table
INSERT INTO #date_dim_months_only WITH (TABLOCK)
SELECT DATEADD(DAY, -1, DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '20000101'))
FROM master..spt_values;

For step 1, there are plenty of ways to emulate LEAD. Here's one method:

SELECT
  h1.username
, h1.account
, h1.assigned
, next_date.assigned
FROM #histories h1
OUTER APPLY (
    SELECT TOP 1 h2.assigned
    FROM #histories h2
    WHERE h1.account = h2.account
    AND h1.assigned < h2.assigned
    ORDER BY h2.assigned ASC
) next_date;

For step 2, we need to change the NULL values to something else. You want to include the final month for each account, so adding one month to the starting date suffices:

ISNULL(next_date.assigned, DATEADD(MONTH, 1, h1.assigned))

For step 3, we can join to the date dimension table. The column from the dimension table is exactly the column you need for the result set:

INNER JOIN #date_dim_months_only dd ON
    dd.month_date >= h1.assigned AND
    dd.month_date < ISNULL(next_date.assigned, DATEADD(MONTH, 1, h1.assigned))

I didn't like the query that I got when I put it all together. There can be issues with join order when combining OUTER APPLY and INNER JOIN. To get the join order I wanted I rewrote it with a subquery:

SELECT
  hist.username
, hist.account
, dd.month_date
FROM
(
    SELECT
      h1.username
    , h1.account
    , h1.assigned
    , ISNULL(
        (SELECT TOP 1 h2.assigned
            FROM #histories h2
            WHERE h1.account = h2.account
            AND h1.assigned < h2.assigned
            ORDER BY h2.assigned ASC
        )
        , DATEADD(MONTH, 1, h1.assigned)
    ) next_assigned
    FROM #histories h1
) hist
INNER JOIN #date_dim_months_only dd ON
    dd.month_date >= hist.assigned AND
    dd.month_date < hist.next_assigned;

I don't know how much data you have so it might not matter for you. But the plan looks how I want it to:

The results match yours:

╔══════════╦══════════╦════════════╗
║ username ║ account  ║ month_date ║
╠══════════╬══════════╬════════════╣
║ PETER    ║ ACCOUNT1 ║ 2017-01-31 ║
║ PETER    ║ ACCOUNT1 ║ 2017-02-28 ║
║ DAVE     ║ ACCOUNT1 ║ 2017-03-31 ║
║ DAVE     ║ ACCOUNT1 ║ 2017-04-30 ║
║ FRED     ║ ACCOUNT1 ║ 2017-05-31 ║
║ FRED     ║ ACCOUNT1 ║ 2017-06-30 ║
║ FRED     ║ ACCOUNT1 ║ 2017-07-31 ║
║ JAMES    ║ ACCOUNT1 ║ 2017-08-31 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-01-31 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-02-28 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-03-31 ║
║ JAMES    ║ ACCOUNT2 ║ 2017-04-30 ║
║ PHIL     ║ ACCOUNT2 ║ 2017-05-31 ║
╚══════════╩══════════╩════════════╝


9 digit zip codes are reporting LEN of 12 I am attempting to resolve some difficulties in getting zip codes to display properly.

The original spreadsheet has zip codes of mixed 5 and 9 digit formats. After the import process, these 9 digit zip codes are reporting a length of 12 digits. Now when I try to add a hyphen '-' to the 9 digit zip codes I'm getting abnormal results and errors because of the wrong length and various data type conversion issues.

The import was performed using an openrowset method for importing the data from a spreadsheet.

When I query the newly imported data, I see the zip codes showing the same as they were in the spreadsheet, but the length is wrong.

SELECT ZIP,
    LEN(LTRIM(RTRIM(Zip))) AS ZIPLENGTH
  FROM XLS_IMPORT

ZIP         ZIPLENGTH
45750       5
432013256   12
441153221   12
44120       5
441351362   12

If I select the left 9 characters of the data, everything gets converted to float and the zip codes are now unreadable.

SELECT LEFT(ZIP,9) FROM XLS_IMPORT
WHERE LEN(LTRIM(RTRIM(ZIP))) = 12

ZIP
4.32013e+
4.42034e+
4.56637e+
4.41153e+
4.36045e+
4.41133e+

How can I get these zip codes back to the correct 9 digits? Or how can I add a hyphen to those 9 digit zip codes which are reporting length of 12? My end goal is to simply get the 9 digit zip code to have a hyphen in the middle.

The datatype of the Zip column is float.

I just discovered that some of my spreadsheets like NJ and NY have an apostrophe before the leading 0 in the zip code. I will need to investigate how to handle the '0xxxx zip codes to get this to work on some of my spreadsheet imports.

Zip codes are strings, not numbers. Some of them have 1 or even 2 (but not more than 2) leading zeroes. The datatype in the import table should be VARCHAR(10) so that it can hold 5 digit and 9 digit + hyphen zip codes. Even if you never have to store postal codes of other countries, and even if the values will only ever have numeric digits (i.e. 0 - 9), this data is still string data, just like phone numbers.

Based on the import query shown in your other question (automate import and export of process EXCEL -> SQL SERVER - > EXCEL without using SSIS ):

SELECT * INTO XLS_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\RSG_ETL_Tool\Ohio\OH.xls; HDR=YES; IMEX=1',
'SELECT * FROM [OH$]');

I would suggest not relying upon the SELECT INTO construct to create the XLS_IMPORT table, but instead create the import table(s) manually, and then use the INSERT INTO ... SELECT FROM OPENROWSET() construct. Doing this would allow you to do the following things to improve this situation:

Create the ZipCode field as VARCHAR(10)
Convert the values on the way in using the STR function which, for an initial FLOAT value of 432013256, will return  432013256 instead of 4.32013e+008 (which is what you get when you convert to VARCHAR).
Fix any missing leading zeroes using something like the following:

CASE
   WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 3 AND 4
          THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 5)
   WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 7 AND 8
          THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 9)
   WHEN LEN(LTRIM(STR(@ZipColumn))) IN (5, 9) THEN LTRIM(STR(@ZipColumn))
   ELSE 'BadZipCode'
END

Example:

DECLARE @ZipColumn FLOAT = 032013256.000000;

SELECT CASE
          WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 3 AND 4
                 THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 5)
          WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 7 AND 8
                 THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 9)
          WHEN LEN(LTRIM(STR(@ZipColumn))) IN (5, 9) THEN LTRIM(STR(@ZipColumn))
          ELSE 'BadZipCode'
       END;

Returns:

032013256

Ideally, you would fix the column definition in the spreadsheet to be a string. But even if you do that, it might still be a good idea to keep this code around.

  My end goal is to simply get the 9 digit zip code to have a hyphen in the middle.

With that goal in mind, the following inline TVF can be used to both convert the FLOAT value to VARCHAR, and to add in the hyphen for ZIP + 4 values.

Code for iTVF:

CREATE FUNCTION dbo.FormatZIPCode(@NumericZIPCode FLOAT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

WITH string AS
(
    SELECT  LTRIM(STR(@NumericZIPCode)) AS [Value],
            LEN(LTRIM(STR(@NumericZIPCode))) AS [Size]
), converted AS
(
SELECT  CASE
                WHEN st.[Value] IS NULL THEN NULL
                WHEN st.[Size] BETWEEN 3 AND 4
                    THEN RIGHT('0000' + st.[Value], 5)
                WHEN st.[Size] BETWEEN 7 AND 8
                    THEN RIGHT('0000' + st.[Value], 9)
                WHEN st.[Size] IN (5, 9)
                    THEN st.[Value]
                ELSE 'BadZipCode'
            END AS [ZIP],
            st.[Size] AS [OriginalSize]
    FROM        string st
)
SELECT  IIF(cnv.[OriginalSize] >= 7, STUFF(cnv.[ZIP], 6, 0, '-'), cnv.[ZIP])
               AS [FormattedZIPCode]
FROM        converted cnv;

Test:

SELECT  *
FROM    (VALUES (CONVERT(FLOAT, NULL)), (1), (12), (123), (1234), (12345),
                (123456), (1234567), (12345678), (123456789)) src(val)
CROSS APPLY dbo.FormatZIPCode(src.[val]) frmt;

Returns:

val         FormattedZIPCode
---------   ----------------
NULL        NULL
1           BadZipCode
12          BadZipCode
123         00123
1234        01234
12345       12345
123456      BadZipCode
1234567     00123-4567
12345678    01234-5678
123456789   12345-6789

In order to be clearer about what is being suggested, the following shows all of the suggestions noted above put together:

CREATE TABLE dbo.XLS_IMPORT
(
  Col1     DataTypeForCol1,
  Col2     DataTypeForCol2,
  ZIPCode  VARCHAR(10),
  ...
);

INSERT INTO dbo.XLS_IMPORT (Col1, Col2, ZIPCode, ...)
  SELECT xls.Col1, xls.Col2, zip.[FormattedZIPCode], ...
  FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',
         'Excel 12.0; Database=C:\RSG_ETL_Tool\Ohio\OH.xls; HDR=YES; IMEX=1',
         'SELECT * FROM [OH$]')
  CROSS APPLY dbo.FormatZIPCode(xls.[ZIP]) zip;

Some of the spreadsheets are "aware" of there being leading zeroes, and so prefixed the field in Excel with a single apostrophe so that Excel would treat the value as a string instead of as numeric (e.g. '01234). In that case, you can use the REPLACE function to strip out that apostrophe.

-- Test incoming string data (potentially prefixed with a single apostrophe)
SELECT  src.[val], frmt.[FormattedZIPCode],CHARINDEX(N'''', src.[val])
FROM    (VALUES (NULL), (N'''01234'), (N'''123456789'), (N'123'), (N'12345678')) src(val)
CROSS APPLY dbo.FormatZIPCode(REPLACE(src.[val], N'''', N'')) frmt;

However, you can't use REPLACE on all spreadsheets because the spreadsheets that have the ZIP column as numeric will do a CONVERT_IMPLICIT as it passes the value to the REPLACE function and the converted value will be in scientific notation (e.g. 1.23457e+008). So if you can't be certain which datatype is going to be returned by OPENROWSET, then you can use IIF (or CASE if using a version of SQL Server prior to 2012) and CHARINDEX to test for the presence of an apostrophe. If the data is being returned as FLOAT, then the implicit conversion to VARCHAR that will occur when passing the values into CHARINDEX will not be an issue since there is no apostrophe in 1.23457e+008 and the converted value is only going to CHARINDEX and not to dbo.FormatZIPCode.

-- Test handling incoming data as both numeric and string
SELECT  src.[val], frmt.[FormattedZIPCode],CHARINDEX(N'''', src.[val])
FROM    (VALUES (CONVERT(FLOAT, NULL)), (1), (12), (123), (1234), (12345),
                (123456), (1234567), (12345678), (123456789)) src(val)
CROSS APPLY dbo.FormatZIPCode(
       IIF(CHARINDEX(N'''', src.[val]) > 0, REPLACE(src.[val], N'''', N''), src.[val])
                             ) frmt;

SELECT  src.[val], frmt.[FormattedZIPCode]
FROM    (VALUES (N'''01234'), (N'''123456789'), (N'123'), (N'12345678')) src(val)
CROSS APPLY dbo.FormatZIPCode(
       IIF(CHARINDEX(N'''', src.[val]) > 0, REPLACE(src.[val], N'''', N''), src.[val])
                             ) frmt;


syntax error at or near - INSERT This might be a little silly, but can't figure out why this insert is not working, I did surround the IP with single / double quotes!

psql -U dbuser hosts -h dbhost -c 'INSERT INTO HOSTS ('type','name') VALUES ('"test"', '"10.100.133.1"')'
Password for user dbusr:
ERROR:  syntax error at or near ".133"
LINE 1: INSERT INTO HOSTS (type,name) VALUES (test, 10.100.133.1)
                                                          ^

Do I need to escape anything?

This works fine:

postgres=# create table hosts ( type varchar(20), name varchar(20));
CREATE TABLE
postgres=# \q
postgres@ironforge:~$ psql -c "insert into hosts (type,name) values ('test','10.100.133.1')"
INSERT 0 1
postgres@ironforge:~$


Unable to Run psql in Windows Command Prompt I have just installed postgreSQL and have set up the user postgres and its password. In the psql Shell I am able to login as normal however I can't login in Windows Command Prompt. When I run the psql command it asks for Password for user username for which I don't know the password.

I have tried running dropuser username in the psql shell however this doesn't make a difference. When I use psql -u postgres to login and run postgres=# \du, the other role doesn't appear:

                                  List of roles
Role name |                         Attributes                         |
Member
of
----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

If you want to override the default behaviour that chooses the current Windows' user name as the login for the database, you can define the environment variable PGUSER that contains the user you want to use by default.

Once that is defined and you type psql it assumes that user as the default.

Related: How do I specify a password to psql non-interactively?


How to convert timestamp with time zone to numeric (unix) in PostgreSQL? I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format?

I found this question, but it shows only how to convert timestamp without time zone to unix timestamp.

To solve this, I looked here.

For example, I'm in a time zone which is at UTC + 1,

so,

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';

gives:

2019-06-06 17:41:00.216647

which is 1 hour behind clock time because I'm on UTC + 1.

So, then from here, I got select extract(epoch from ts) from data

Combining the two gives:

SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'));

which for me gives:

date_part
1559843103.53928

YMMV - fiddle available here - put your timestampz into the formula for current_timestamp and you should be golden!

Interstingly, you can check your results here.

p.s. welcome to the forum! :-)


Scripting SQL Server Replication I am trying to use SSIS to take a backup of Replication scripts. I am using Script task and using ReplicationServer class under Microsoft.SqlServer.Replication.

The problem is, the package is failing at ReplicationServer.Script(ScriptOptions.Creation) statement with exception "You do not have sufficient permission to run this command."

Does anyone know what kind of permissions are required?

User who runs the package must have access to the folder to which you are writing the file. User also needs access to SQL Server with sysadmin rights.

Also, verify you have properly added all libraries.

For more, please read my blog article at http://www.dbadrive.in/2013/03/ssis-package-to-backup-replication_25.html.

Please share some more details of your code if the error persists.


Normalise an expenditure table in a budget database We have a budget database that stores expenditure items per budget.  There are over 100 expenditure items each having common attributes: Amount, Frequency, Comment. The table will have over 300 columns in total and look similar to below.  How would you guys look to normalise this table?

Expenditure
BudgetID
FoodAmount
FoodFrequency
FoodComment
FuelAmount
FuelFrequency
FuelComment
BlahblahAmount
BlahblahFrequency
BlahblahComment
etc...

Thanks.

Assuming they all have the same three columns you mentioned an no more, have you considered this:

expenditure_budget_item:
id | type_id | amount | frequency | comment
30   6         50       null        "test comment"
31   7         40       null        null

expenditure_budget_types:
id | name   | description
6    "Food"   "Food expenditures"
7    "Fuel"   "Fuel expenditures"

Let me know if there's any details I may have missed.


Schema Dump Without User Mapping Passwords Is it possible to do a pg_dump --schema-only of the database schema but not include any username/password combinations that are in user mappings?

For example, I would like items such as the following to not be put in the dump:

CREATE USER MAPPING FOR greg SERVER db_name OPTIONS (
    password 'supersecret',
    username 'bobs_uncle'
);

The only thing I can think of is to create a single-purpose non-super user and grant them read access to all the objects in the database.  Then run pg_dump connected as that user. Perhaps not the most elegant solution.


Finding the query causing the missing index DMVs How can I find the query causing the missing index DMVs suggesting to create a particular index?

Thank you.

Short answer: you can't with 100% accuracy.

Long answer: you can query the plan cache to identify plans with missing index warnings and compare the results with what you find in sys.dm_db_missing_index_* DMVs. Here's a script that you could use to query the plan cache. If the plan doesn't get cached or gets pushed out the cache for any reason, you won't find any matches, so we're back to the short answer.

To be 100% sure, you would have to trace the workload and capture the plans with missing indexes. It's not lightweight and it's not trivial. Here's a script to do that. With a small effort it could be ported to Extended Events.


When PostgreSQL will use Index Only Scan over Bitmap Index + Heap Scan Today I've been doing some reading on the difference between the two and I think I've got a reasonable idea of each.

Index Only: Accesses heap pages as it moves through the index, possibly access the same page multiple time.

Bitmap Index Scan + Bitmap Heap Scan: Builds a bitmap of heap pages that need to be visited containing a matching tuple. The map(s) are then combined and sorted and heap pages accessed.

What I didn't get a definitive answer on, is the conditions that will determine when one will be used over the other. As far as I can gather, these are some of them:

The retrieved columns are a subset of the indexed columns.
A vacuumed and analyzed table (index only will only work on these)
Number of rows that will be fetched - though this didn't make sense to me.

Index Only:  Will not visit the heap at all for a block if the block is marked as all-visible in the visibility map.  If it does need to visit a block, it might do so on multiple occasions, and those occasions might be separated from each other by a lot of time because multiple distant index entries can point to the same block.  During the intervening time, the block might have been pushed out of the cache and so need to be read back in.

Bitmap: Cannot use the visibility map to avoid visiting a block.  All visits to the same block will occur back-to-back, so it is unlikely to get shoved out of cache between visits.  It also visits in physical order even when the index correlation is low, and so gets credited with having its table heap IO be more sequential and less random.  But that is only important when you read a substantial part of the table, meaning fetching a lot of rows.

Higher pg_class.relallvisible means it is more likely than an IOS will not need to visit the table heap at all for a block, and so favors IOS.  Keeping relallvisible high requires vacuuming.

Higher pg_stats.correlation for the table column that is the leading column of the index means that multiple visits to a block are likely to occur from the same region of the index, and so the block is unlikely to be shoved out of the cache between visits when using an IOS, and so favors an IOS.

Higher values of effective_cache_size means blocks are estimated to stay in the cache for longer meaning repeated distant visits are likely to find them in the cache still, which again favors IOS.

These have a complex relationship.  If all blocks are marked allvisible, then the correlation and effective_cache_size do not matter, as there will not be any heap visits at all.


How to create a trigger in a different database? Is it possible to create a stored procedure that creates a table trigger (DDL) in a different database than the one the stored procedure itself resides.  The databases are on the same server instance. If yes then how?

For example this does not work:

create PROCEDURE [dbo].[CreateTriggrer]
  @db varchar(60)
AS
BEGIN
  SET NOCOUNT ON;
  declare @statement nvarchar(max) = N'CREATE TRIGGER [dbo].[TestTrigger]
   ON  [$database$].[dbo].[TestTable]
   AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  PRINT ''test''
END'

    set @statement = REPLACE(@statement,'$database$',@db)
    EXEC dbo.sp_executesql @statement = @statement

END

When called like this:

EXEC [dbo].[CreateTriggrer] @db = N'TestDatabase'

It returns this error:

  Msg 2108, Level 15, State 1, Procedure TestTrigger, Line 6
  Cannot create trigger on 'TestDatabase.dbo.TestTable' as the target is not in
  the current database.

Which is fair enough. Is there way to achieve what I want?

DECLARE @statement NVARCHAR(MAX) = N'CREATE TRIGGER [dbo].[TestTrigger]
  ON [dbo].[TestTable]
  AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
    PRINT ''test'';
  END';

DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@db) + '.sys.sp_executesql';

EXEC @sql @statement;

This is not entirely intuitive, but it should work. Here is a simpler example that you can test independently:

USE [master];
GO
DECLARE @db SYSNAME = N'tempdb';

DECLARE @statement NVARCHAR(MAX) = N'SELECT DB_NAME(), * FROM sys.database_files';

DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@db) + 'sys.sp_executesql';

EXEC @sql @statement;

Credit to Erland Sommarskog - you should read this article if you want to understand more deeply how it works.


Why SQL Server Standard 2017 is using more memory than limit described in documentation? Documentation says:

  For example: A Standard Edition of SQL Server has buffer pool memory
  limited to 128GB, so the data and index pages cached in buffer pool is
  limited by 128GB. Starting with SQL Server 2016 SP1, you can have an
  additional 32GB of memory for Columnstore segment cache per instance
  and an additional 32GB of memory quota for In-Memory OLTP per
  database. In addition, there can be memory consumed by other memory
  consumers in SQL Server which will be limited by "max server memory"
  or total memory on the server if max server memory is uncapped.

We are using columnstore so I expect that SQL Server will be limited by using 128+32 Gb of memory. Memory optimized tables are not used.

But in reality more than 215 Gb is being used for buffer pool:

Memory Clerks

Overall sql process takes ~300Gb.

  select * from sys.dm_os_process_memory

Max memory set to 360Gb. Server has 380Gb.

What am I missing?

Version:

  Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)
  Mar 12 2019 19:29:19   Copyright (C) 2017 Microsoft Corporation
  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0
  (Build 14393: ) (Hypervisor)

UPDATE (2019-04-26)
Here is interesting thing - MEMORYCLERK_SQLBUFFERPOOL is presented by two records. Row #3 is exactly what I'd expect to see for Standard Edition. But what is the first row?
Server has 2 processors with 48 cores total.

I believe you missed the documentation. Please see the highlighted points in image below

The memory used by memory optimized tables is 32 GB PER DATABASE

So let us say you are having SQL Server standard edition having columnstore indexes and having memory optimized tables in 3 databases.

The maximum memory utilized can be

128+32+(3*32)= 256 GB.

Let me know for how many databases you are using memory optimized tables ?. The 128 GB limit is only for buffer pool


Performance of Inline-TVF vs. Views I have a database where i am using inline TVFs (table value functions) instead of views.  For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together inside the TVF [fnCarBrands].

These TVFs are then called by other TVFs to do further processing and reporting.  So I might take my function [fnCarBrands] and join to the table [Purchase Year] to form a function [fnCarBrandHistory].  And so on for several layers of TVFs.

I could probably get the same functionality using views, since my inline TVFs are really just joins of tables and other TVFs.

How does the performance of inline TVFs written in this way compare with views?

The query optimizer treats an inline table valued function exactly like a view:

CREATE FUNCTION dbo.InlineUdf(@arg1 int)
RETURNS TABLE
AS
RETURN
(
    ... your query here ...
);

A multi-statement table-valued function is run more like a stored procedure.  They typically have to be executed multiple times, rather than be folded into the main query:

CREATE FUNCTION dbo.MultiStatementUdf (@col1 int)
RETURNS @result TABLE
(
    id int primary key NOT NULL,
    ...
)
AS
BEGIN
   DECLARE @var1 int
   set @var1 = 42

   INSERT @result
   SELECT ...
   RETURN
END;


SQL GroupBy Select min return multiple columns I have the following sample of a SQL table, which will be filled with around 400 million rows:

I want to be able to return columns Id, UID, STP, A, B, C and then group by the UID and keep the one with the lowest STP values, where a date is in between the StartDate and EndDate (i.e. 23/05/2011)
This is what I have so far:
SELECT Id, UID, STP, A, B, C
FROM [drp-data].[dbo].[schedules]
WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23')

Microsoft SQL 2019.

;WITH CTE AS
    (
    SELECT Id, UID, STP, A, B ,C
        ,ROW_NUMBER() OVER (PARTITION BY UID ORDER BY STP) AS rn
    FROM [drp-data].[dbo].[schedules]
    WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23')
    )
    SELECT  Id, UID, STP, A, B ,C
    FROM CTE
    WHERE rn = 1

please see this post too: Retrieving n rows per group


Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true I have morning job schedule that starts at 5:30 AM....starting on 5-27-2014. I've begin implementing table replication on one of our heavily used tables...and use the replicated table for reporting purposes only.

Basic Publication
Type = Snapshot
Only one table article being used
No filters
Publish every 20 mins.
Replication agents checkup job every 10 mins

**The unique problem is that the 5:30 AM job calls a SP that has logic to a linked instance...but the logic doesn't (insert, del, or update) the trigger table...actually the logic doesn't use the table at all. But, I've have identified the table and triggers.

Error Message from the failed job:

  Message
    Executed as user: **********. ...because the server option "disallow_results_from_triggers" is true.
    [SQLSTATE 01000] (Message 531)  Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true.
    [SQLSTATE 01000] (Message 531)  Cannot set NOCOUNT to OFF inside the trigger execution because the server option "disallow_results_from_triggers" is true.
    [SQLSTATE 01000] (Message 531)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
    [SQLSTATE 01003] (Message 8153)  Warning: Null value...  The step failed.

Wait and you shall receive....this is the 1st this happened after my last edit

Message

Executed as user:********. ...because the server option "disallow_results_from_triggers" is true.

[SQLSTATE 01000] (Message 531)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153)  Warning...  The step failed.

Steps to solve the problem...
1. Found all triggers in instances with SET NOCOUNT OFF inside triggers and comment out the line....using the script provided in this thread
2. Ran the job for weeks with no errors until today (7/16/2014 5:30 AM).

Find the triggers that have SET NOCOUNT OFF and fix them. I can't possibly imagine what good can come from having that line anywhere. NOCOUNT should be ON in every single module on your server.

This will give you the definition (and may introduce some false positives, so check closely), you'll just need to add GO between them and change CREATE to ALTER:

SELECT 'GO
-- ' + QUOTENAME(s.name) + '.' + QUOTENAME(m.name) + '
GO',
  m.definition
  FROM sys.triggers AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
  ON t.[object_id] = m.[object_id]
  WHERE LOWER(m.definition) LIKE N'%set%nocount%off';

You may need to repeat this task in multiple databases.

Once you've fixed all of those, try the job again. If the error continues happening, try to narrow down the trigger(s) responsible and inspect the definition more closely. You may have to reverse engineer the job and all of the things it calls in order to find it, but once you have, post it here.


Finding distinct rows across two tables: Full Outer Join more efficient than Union? When finding distinct rows across two tables where we can't necessarily ensure are pre-sorted, is it a good idea to use a FULL OUTER JOIN rather than a UNION?  Are there any downsides to this approach?  If it is consistently faster, why does the query optimizer not choose the same plan for UNION that the FULL OUTER JOIN would use?

We were able to bring a specific production query from ~10 minutes to ~3 minutes by re-writing a UNION as a FULL OUTER JOIN.  A UNION seems like the more intuitive way to write the logic, but upon exploring both options, I have observed that the FULL OUTER JOIN is more efficient in terms of both memory and CPU usage.

See the following scripts if you'd like to run a simplified and anonymized version of our production query:

Setup script

-- Create a 500K row table
SELECT TOP 500000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t1
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Create a 5MM row table that will match some, but not all, of the 500K row table
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY NEWID()) AS id, v1.number % 5 AS val
INTO #t2
FROM master..spt_values v1
CROSS JOIN master..spt_values v2

-- Optionally, key both tables to see the impact it has on query plans and performance
-- Both queries end up with essentially the same plan and performance in this case
-- So that means that at least there is not a downside to using the FULL OUTER JOIN when the data is sorted
--ALTER TABLE #t1
--ADD UNIQUE CLUSTERED (id)
--ALTER TABLE #t2
--ADD UNIQUE CLUSTERED (id)

FULL OUTER JOIN

The FULL OUTER JOIN chooses the small of the two tables as the build side of a hash join, meaning that the memory usage is proportional to the size of the smaller table (500K rows).

-- CPU time = 3058 ms,  elapsed time = 783 ms.
-- MaxUsedMemory: 29016 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
    FROM #t1 t1
    FULL OUTER JOIN #t2 t2
        ON t2.id = t1.id
        AND t2.val = t1.val
) x
GO

UNION

The UNION builds a hash table for a hash aggregate on the overall data set, meaning that the memory usage is proportional to the total number of distinct rows (5.4MM rows in this case; generally, at least the number of rows in the larger of the two tables).  The memory usage is over 10x greater than the FULL OUTER JOIN, and both CPU time and elapsed time are slower as well.  If I were to scale this up to the point that hash aggregate can't fit within a single query's memory grant, the performance difference would become huge (as it was in our large production query).

-- CPU time = 4651 ms,  elapsed time = 1188 ms.
-- MaxUsedMemory: 301600 KB
-- Table '#t1'. Scan count 5, logical reads 1301, physical reads 0
-- Table '#t2'. Scan count 5, logical reads 12989, physical reads 0
SELECT COUNT(*), AVG(id), AVG(val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION
    SELECT t2.id, t2.val
    FROM #t2 t2
) x

The semantics of the two queries are not the same - UNION removes duplicates, whereas the FULL OUTER JOIN will not:

DECLARE @T1 AS table (id bigint NULL, val integer NULL);
DECLARE @T2 AS table (id bigint NULL, val integer NULL);

INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);

SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
FROM @t1 t1
FULL OUTER JOIN @t2 t2
    ON t2.id = t1.id
    AND t2.val = t1.val;

SELECT t1.id, t1.val
FROM @t1 t1
UNION
SELECT t2.id, t2.val
FROM @t2 t2;

Output:

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
╚════╩═════╝

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
╚════╩═════╝

That said, the optimizer does not know many FOJN tricks, so it is always possible that there is a better way to express the query than the natural UNION. Only commonly-useful and always-correct transformations are implemented.

Note that with a unique constraint only on the larger table, the optimizer chooses a hash union, without expensive duplicate-removal on the probe input, that makes it choose Concat Union All in the question example:

ALTER TABLE #t2
ADD CONSTRAINT UQ2
UNIQUE CLUSTERED (id);

SELECT COUNT(*), AVG(x.id), AVG(x.val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION
    SELECT t2.id, t2.val
    FROM #t2 t2
) AS x;

The FOJN rewrite may well be a useful one in cases where you know there cannot be duplicates within each input set, but this condition is not enforced with a unique constraint or index (particularly on the large input).

If such a uniqueness guarantee does exist, and yet the optimizer does not select a Hash Union, you might try an OPTION (HASH UNION) hint, to see how it compares.


SQL Server Internal Memory Error I am using SQL Server 2016 Standard Edition that shows the following errors on DBCC CHECKDB and also on updating statistics.

 Version: Microsoft SQL Server 2016 (SP2-CU2) (KB4340355) - 13.0.5153.0 (X64)
 Jun 28 2018 17:24:28 Copyright (c) Microsoft Corporation Standard Edition
 (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

MAX Memory set at 215GB from 244GB x1e.2xlarge AWS EC2
There are around 260 databases.
Locked Pages in Memory is on.
Page File size is 8GB.

Memory optimized tables that uses less than 10GB of memory.

  Failed to reserve pages: FAIL_PAGE_RESERVATION

  Error: 8645, Severity: 17, State: 1. A timeout occurred while waiting
  for memory resources to execute the query in resource pool 'internal'
  (1). Rerun the query.

  Error: 701, Severity: 17, State: 135. There is insufficient system
  memory in resource pool 'internal' to run this query.

After multiple errors like these, the server shutdown itself.

Page life expectancy is 1297050, no memory grants pending, doesn't appear to have any memory pressure.

Any ideas on this issue?

You state:

  MAX Memory set at 215GB

As @Shanky stated in the comments, set max server memory (MB) to 128 GB via sp_configure as that's the limit of usable memory for SQL Server Standard Edition.

I've run into a similar issue with SQL Server Express edition when I had it set higher than it's usable limit.