Scalable Relational Database Design I'm designing a web application (using Django) for a client that will need a fairly large database behind it. I've never worked with databases that handle large amounts of data before so I'm hoping to get some help.

The basic layout of this database is simple:  there are users, each user can have multiple child entries, and each entry is composed of a description and up to 25 images. Each user is permitted to have 40 entries per month.

So far this is pretty simple. The client says he wants the initial release to be able to accommodate his expected 20,000 users. This web application will be used by individuals within an organization, not the general public. So his estimate of initial users is very accurate.

If the timescale we're looking at is a year, that means that I need a database that can accommodate 20,000 user rows, 9,600,000 entry rows, and 240,000,000 images.

The first thing I want to know is how to store these images. I think the best thing to do is to store URLs to the images and actually store the images themselves outside of the database. But what is the best way to store these URLs? Should I have a comma separated list within each entry? This seems like a simple approach to me, that way I would only need two tables, one for users and one for entries. The entries table would contain an ID, a description, and a comma separated list of image URLs that can be processed by Django to serve content. Again, I've never done anything like this before so suggestions are very much appreciated.

My more complicated question is about scalability. My client says he can expect to have these 20,000 users, but that if his service takes off that number might go as high as 250,000. Is there a way to implement a SQL style database that can handle 20,000 users here but also leave room for expansion, rather than simply buying hosting and using a database such that the possible 250,000 users would be accommodated from the start?

So you're going to want to store your images on a fast CDN like Cloudfront. Normally I recommend storing pictures in-database, but not 240M of them :)

I'd avoid storing the whole URL, as it's likely to be repetitive. Just store the file name (a five-char case-sensitive name using a-z 0-9 would give you ~900M combinations. If your CDN is case-insensitive wrt URLs then use 6 chars ).

Are there really going to be 240M unique images, or can they be "shared" among entries? Do you expect every user to fill every entry with the max # of images?

You'd want some tables like this:

create table users (
  user_id serial primary key,
  ...
);

create table entries (
  entry_id serial primary key,
  user_id int not null references users(user_id), --index me
  description text,
  ...
);

create table pictures (
  file_name char(5) primary key,
  entry_id int not null references entries(entry_id) --index me
);

or perhaps:

create table pictures (
  picture_id serial primary key,
  file_name char(5) not null unique,
  entry_id int not null references entries(entry_id) -- index me
);

Use connection pooling!


Where can I find a list of the available extensions for PostgreSQL? Just wondering if there is an official list for extensions that are available for PostgreSQL?

This is a frequently asked question.

Users and SQL calls them "extensions". You add them with CREATE EXTENSION.
They get packaged by distros like Ubuntu as postgresql-contrib or the like.
The docs calls them "Additional Supplied Modules", but "modules" isn't really used anywhere else.  I'm not sure you should ever call anything a module, but it seems to be used here to refer to packaged extensions. Perhaps it's from the days when extensions were just SQL scripts.

You can find the list of PostgreSQL extensions officially supported here.

PGXN.org is a great resource for finding third party modules/extensions/contrib-ie stuff too.


Why is this query throwing an "Invalid Column Name" error? Trying to run a simple UPDATE query as follows:

UPDATE [TILL].[dbo].[appParameters]
SET [TILL].[dbo].[appParameters].[ParameterValue] = "1"
WHERE [TILL].[dbo].[appParameters].[ParameterName] = "StaffTablesRefreshed"

If the field [ParameterName] is the value "StaffTablesRefreshed", then set the value of [ParameterValue] to "1".

When executed, SQL Server throws an error:

  Invalid column name 'StaffTablesRefreshed'

Both [ParameterName] and [ParameterValue] are nvarchar(255).

Am I missing something obvious here?

Thanks,
Ken

Try replacing the double quotes with single quotes?


Rotation of bin.log file I am trying to change the binary log rotation from days to hours. By default there is an option only for days in the MySQL config file: expire_logs_days.

Is there any way to change the binary log rotation from days to hours or even minutes?

I am running MySQL version 5.7.23 on Ubuntu 16.04.

The only way to get binlog rotation in hours is to script it

Here is the code

#!/bin/bash
HRS=16
mysql -uroot -prootpass -e"PURGE BINARY LOGS BEFORE NOW() - INTERVAL ${HRS} HOUR"

Put this code in a script called /root/mybinlog_rotate.sh

Make sure you run chmod +x /root/mybinlog_rotate.sh

Add that script in the crontab to rotate every 30 min

0,30 * * * * /root/mybinlog_rotate.sh

This is a little heavy handed but it can be done

I suggested this months ago : Maximum overall size of MariaDB binary logs


Issues with module signing and SSIS catalog internal procedures I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user.

I found this article which detailed creating a certificate and signing the module so that a privileged login could execute it.

When I try to run any command impersonating the login, I receive the following error:

  Cannot execute as the server principal because the principal "CrossDbCertLogin" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Here's a script to reproduce the problem:

create certificate CrossDatabaseCert
ENCRYPTION BY PASSWORD = 'passA'
WITH SUBJECT = 'Certificate for Cross-Database Database Impersonation'
GO

CREATE Login CrossDbCertLogin FROM CERTIFICATE CrossDatabaseCert
GO

backup certificate CrossDatabaseCErt to file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(
    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passA',
    ENCRYPTION BY PASSWORD = 'passB'
)
go

use msdb
create certificate CrossDatabaseCert from file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passB',
    ENCRYPTION BY PASSWORD = 'passA'
)
Go

EXECUTE AS LOGIN=N'CrossDbCertLogin';
SELECT 1

Eventually I add the certificate to the stored procedure, but it seems the impersonation isn't even functioning so I'm trying to get that working first.

What am I doing wrong?

EDIT: A little more info on my procedure.

When I change it to "EXECUTE AS OWNER" (in the same form as the example I followed), I receive the following errors.

  Msg 27123, Level 16, State 1, Procedure create_execution, Line 39
  The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

  Msg 27123, Level 16, State 1, Procedure set_execution_parameter_value, Line 34
  The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

  Msg 27123, Level 16, State 1, Procedure start_execution, Line 32
  The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Inside my procedure I call three internal SSIS Catalog Procedures: Create_Execution, Set_Execution_Parameter_Value, and Start_Execution.

When I change the procedure to execute as the SQL Agent Service account, I get the following error:

  Msg 15199, Level 16, State 1, Procedure prepare_execution, Line 34
  The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

EDIT2: I ended up using a SQLCLR SProc to call my original proc, which solved all the issues.

In Vedran's code which you reference, he also signs the stored procedure in the other database.  I do not see in your code that you have taken that final step.

Erland Sommarskog has an extensive discussion at http://www.sommarskog.se/grantperm.html of this problem.  This includes a discussion of, in his words, "the Problematic EXECUTE AS".

In the following link, which is for starting SQL Agent jobs, you can see yet another approach to delegating rights to a login which has no particular rights.  See: Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

If that appeals to you, you can create your own stored procedure to allow your user to start the SSIS process.

10/20/2014 Edit: "Cannot execute as the server principal because ..."

First of all, if you are a sysadmin, then the problem is not your rights, but the context you are trying to run.  (I believe that you are a sysadmin.)  So it could be something like:

If the server principle you want to use is a Windows Group it cannot be impersonated.
Have you recently restored a system database (e.g. msdb) from another server? Then the account you see in msdb may not match an account on the current server. (You might see the same name in database_principals, but the sid could be different.) If so, try using exec sp_change_users_login 'AutoFix','principalname'.
If the owner of a database (in master.sys.databases)is not the same sid as the
database's dbo user, then it cannot be impersonated.  (This is unlikely in msdb since sa is the normal owner of msdb.)
If the login was dropped, even if the user remains in the database, you may not be able to impersonate the account.

And there are likely other flavors as well.


Find the number of where statements that matched in postgresQL Right now I have a query that looks something like this

SELECT id
FROM products
WHERE products.details->'make' = ?
    OR products.details->'model' = ?
    OR products.details->'year' = ?

I'd like to sort the results by how many of the where statements matched, so for example if the three terms that were used in the query were ford, f150, 1985, and I got two results. One that had all three terms in it (a ford f150 from 1985), and a second result that had only two matches (a ford f150 from 1991).

Is there a way for me to do this with postgreSQL 9.3, or will I need to use my application layer to figure out how many matches there were?

One way:

SELECT id
FROM   products
WHERE  details->'make' = ?
    OR details->'model' = ?
    OR details->'year' = ?
ORDER  BY CASE WHEN details->'make' = ?  THEN 0 ELSE 1 END
        + CASE WHEN details->'model' = ? THEN 0 ELSE 1 END
        + CASE WHEN details->'year' = ?  THEN 0 ELSE 1 END;

It's a bit simpler to invert the logic:
NULL and FALSE results increase the value (and decrease the ranking).


How to return a set of rows from this function? I am new to Postgres functions and have a table with the following structure:

CREATE TABLE options.options (
  delta double precision,
  gamma double precision,
  rho double precision,
  theta double precision,
  impvol double precision,
  value double precision,
  vega double precision,
  id bigserial NOT NULL,
  date bigint,
  ticker text,
  callput text,
  chg double precision,
  maturity integer,
  symbol text,
  strike double precision,
  implied double precision,
  last double precision,
  vol double precision,
  ask double precision,
  bid double precision,
  CONSTRAINT options_pkey PRIMARY KEY (id)
);

And I am trying to build the following function:

CREATE OR REPLACE FUNCTION generate_term_structure_by_moneyness(arg_ticker text
                                  ,arg_date integer
                                  ,arg_underlying float
                                  ,arg_lower float
                                  ,arg_higher  float)
  RETURNS SETOF varchar(250) AS -- declare return type!
$BODY$
BEGIN -- required for plpgsql

   RETURN QUERY
    select maturity,avg(Implied) from options.options
    where ticker=arg_ticker and date=arg_date and strike/arg_underlying>arg_lower
    and strike/arg_underlying<arg_higher group by maturity order by maturity asc;

END; -- required for plpgsql
$BODY$ LANGUAGE plpgsql;

The issue I am having is the following error when trying to run the following query:

Query

select * from generate_term_structure_by_moneyness('TQQQ',20151221,120.2699,.98,1.02)

Error

ERROR:  structure of query does not match function result type
DETAIL:  Returned type integer does not match expected type character varying in column 1.
CONTEXT:  PL/pgSQL function generate_term_structure_by_moneyness(text,integer,double precision,double precision,double precision) line 5 at RETURN QUERY
********** Error **********

ERROR: structure of query does not match function result type
SQL state: 42804
Detail: Returned type integer does not match expected type character varying in column 1.
Context: PL/pgSQL function generate_term_structure_by_moneyness(text,integer,double precision,double precision,double precision) line 5 at RETURN QUERY

I believe the error is coming from returns setof but I am unsure how to cater to this result type.

In pseudo code the returned data is expected in the form of:

[int,float; int, float; ...]

Your query returns two columns (one integer, one float), but the function is defined as returning a table with a single varchar column.

You need to change your function definition to:

RETURNS table(maturity integer, avg_implied numeric)

to match the query result.

returns setof and returns table are the same thing but returns table lets you define names for each column in the result.

You also don't need PL/pgSQL for this, a simple SQL function will do just fine:

CREATE OR REPLACE FUNCTION generate_term_structure_by_moneyness(arg_ticker text
                                  ,arg_date integer
                                  ,arg_underlying float
                                  ,arg_lower float
                                  ,arg_higher  float)
  RETURNS table(maturity integer, avg_implied numeric)
$BODY$
    select maturity,avg(Implied)
    from options.options
    where ticker=arg_ticker
      and date=arg_date
      and strike/arg_underlying > arg_lower
      and strike/arg_underlying < arg_higher
    group by maturity
    order by maturity asc;
$BODY$
LANGUAGE sql;

You probably also want to re-think the usage of the approximate data type double:
http://floating-point-gui.de/

date is a horrible name for a column. For one because it is also a keyword, but more importantly it doesn't document the column's content. Is that a "start date"? An "end date"? A "due date"? A "registration date"? A "change date"? A "creation date"? And even more so if you don't actually store a date in that column but an integer value. What kind of date is the value 42 supposed to be?


How to increase performance of UPDATE of ~ 50.000.000 entries We have a query that uses a temporary table to update certain columns in the database:

CREATE TABLE #NEW_OLD (
    NEW nvarchar(20),
    OLD nvarchar(20)
);
INSERT INTO #NEW_OLD
VALUES
('DOMAIN\KEY', 'NEW_KEY'),
('DOMAIN\KEY2', 'NEW_KEY2')
(...)
-- (~ 5.000 keys are inserted)

BEGIN TRANSACTION
BEGIN TRY

    DECLARE @TABLES_COLUMNS TABLE(TABLE_NAME varchar(100), COLUMN_NAME varchar(100))

    INSERT INTO @TABLES_COLUMNS (TABLE_NAME, COLUMN_NAME)
    VALUES
    ('[dbo].[MyTable1]', '[MyColumn1]'),
    ('[dbo].[MyTable2]', '[MyColumn2]')
    (...)
    -- ~ 20 columns are included. The tables have ~ 10.000.000 rows.

    -- Looping over table and column name variables with cursor
    DECLARE TABLES_COLUMNS_CURSOR CURSOR LOCAL FAST_FORWARD FOR
        SELECT TABLE_NAME, COLUMN_NAME
        FROM @TABLES_COLUMNS

    -- Variables to hold table and column name
    DECLARE @TABLE_NAME varchar(30), @COLUMN_NAME varchar(30)

    OPEN TABLES_COLUMNS_CURSOR

    -- Instead of fetching twice, we set up no-exit loop
    WHILE 1 = 1
    BEGIN

        -- And then fetch
        FETCH NEXT FROM TABLES_COLUMNS_CURSOR
        INTO @TABLE_NAME, @COLUMN_NAME

        -- And stop if nothing is fetched
        IF @@FETCH_STATUS <> 0
        BEGIN
            BREAK
        END

        -- Now, update each field with the new key that is associated with that key in the temporary table
        EXECUTE
        (
            'UPDATE ' + @TABLE_NAME + ' ' +
            'SET ' + @COLUMN_NAME + ' = new_old.NEW ' +
            'FROM ' + @TABLE_NAME + 'AS t ' +
            'INNER JOIN #NEW_OLD AS new_old ' +
            'ON t.' + @COLUMN_NAME + 'COLLATE DATABASE_DEFAULT = new_old.OLD COLLATE DATABASE_DEFAULT'
        )

    END

    CLOSE TABLES_COLUMNS_CURSOR
    DEALLOCATE TABLES_COLUMNS_CURSOR

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

This query takes hours to run. The dynamic part (EXECUTE) should not influence performance too much in SQL Server 2008.

How can the performance be improved?

One more thing. If these tables are very larges even if you have good indexes, you should updates these tables rows in cycle, because the transaction log is growing every time and big transactions takes more times.

Example:

CREATE TABLE UpdateTable(id int IDENTITY(1,1), name varchar(200), namenew varchar(200))

INSERT INTO UpdateTable VALUES ('Old1','New2');
INSERT INTO UpdateTable VALUES ('Old2','New2');
go

SET NOCOUNT ON;
DECLARE @cnt INT = -1;

WHILE (@cnt<> 0)
BEGIN
    UPDATE TOP (1) UpdateTable
    SET NAME = namenew
    WHERE name <> namenew

    SET @cnt = @@ROWCOUNT;
 END

You should change the top (1) to an acceptable value like 100000 or bigger you can measure it on your environment


What is Oracle CPU Time and CPU Usage and why is it important? I am trying to understand Oracle's CPU Usage and CPU Time metric for its monitoring (such as in the ASH Viewer) for individual queries

From my understanding:

CPU Time -- the amount of CPU time (in milliseconds) spent on database user-level activities (this does NOT include system-level activities such as garbage collection)

CPU Usage -- CPU Usage (in %) is the percentage of time the CPU spent on database user-level activities over other user-level activities

Does this mean if query #1 has higher CPU usage compared to query #2, it's using more resources than query #2?
Why is knowing CPU Usage helpful for determining how resource heavy a query is? What about memory usage?

The second question is what I don't understand.

This is a complex question but I will try to summarize and generalize.

The green is CPU used and CPU wait.  If CPU used is 100% (in terms of how many cores you have; this shows up in the OEM Top Activity graph which is similar to the ASH report) then your CPU starts waiting and this causes performance problems.  You would want to use your CPU as much as possible, but not at 100% because this causes the server to "flatline" and creates a queue of users waiting for CPU response.  There are other classes of wait events that would cause CPU to wait, such as concurrency locks, as this is bad for performance.  So if something shows more green, then yes it uses more CPU and this is not necessarily bad unless there are other sessions waiting for the resources it is using.

The blue is I/O usage and wait, and for the same reasons you want your sessions to consume I/O resources up to a point, and not have other sessions wait for I/O reads and writes.

Your orange is commit wait. You don't want your SQL to over-commit because this writes an excessive amount into the redo logs and in turn archive logs.

Now the memory usage is where it gets complex.  There are different layers of memory and cache.  Generally you want to read data from memory buffers rather than disk, and there are usually layers of cache on your operating system and I/O subsystems that help Oracle access commonly used data, and get cache hits.  The cache buffers in oracle are in the SGA (or server memory) and you want to get cache hits as far as possible here.  Reads from these buffers are regarded as 'consistent' and will show up as green.  Reads from disk are 'physical' and show up as blue.

Then you have PGA and UGA which are user areas of memory on the server and the client.  If PGA usage becomes too large and exceeds the amount of memory on the server, your operating system goes into 'swap' and reads from disk, and this can cause severe performance backlogs.  Your reads from SGA cannot go into swap because SGA is pre-allocated memory on startup of the database.

So in general your total resource pool consists of CPU, I/O and memory and there is a complex relationship between them. You want to use each resource up to a point, but keep enough free for contingencies.  If optimizations are done and resource usage is consistently near 100% then it's time for more optimization or splitting/upgrading servers.  Oracle RAC (clustering) is sometimes used to address these complex problems.


Import data from excel to table in sql Please I have issue that when I try to import data from excel to table in SQL it fails with below errors even I modified all the tables in the tables to be nvarchar(max) and I tried to enter dummy data in the first row

It worked correctly for me with the below

1- Convert from XLSX to XLS

2- Add incremental primary key at XLS sheet

2- Then save as text Unicode

3- Go to Microsoft SQL then right click on the database then import flat file

4- Convert data types to nvarchar(max) if need it


How To Store EAV Data When It Has Nested Objects/Entities I have json data that have both nested objects and attributes. Eg of one json object:

(After json_decode into an array:)

Array
(
    [attributename1] => attributevalue1
    [attributename2] => attributevalue2
    [NestedEntity1] => Array (

                [0] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                             )
                [1] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                             )
                [2] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                            [NestedEntity1.2] => Array (

                                    [0] => Array (
                                                [attributename1] => attributevalue1
                                                [attributename2] => attributevalue2
                                                 )
                                    [1] => Array (
                                                [attributename1] => attributevalue1
                                                [attributename2] => attributevalue2
                                                 )

                             )

                  )

) )

Note: entities can be further nested in nested entities

My question is: How can I store this data into MySQL?

If it's just attributes, it's easy with a table in MySQL set up for EAV data. But if I have nested objects, I am quite lost as to how to store this. An important thing is that I'd have to be able to pivot the table and make queries like "SELECT * FROM entity WHERE A LIKE B etc.

I suspect I might have to split the nested entities into new tables of their own, then create one-to-many relationship between the "parent" and "child". However:
(1) I have no clue as to do this such that any json data can be accomodated. This probably means some program has to detect automatically a nested object.
(2) ALthough I am open to populating the data form a single json object into multiple tables, one representing each (nested) entity, I was thinking if it might be possible to populate everything into a single table in MySQL. This would prevent the need for creating new table schemas when the need arises.

Proposed data structure is a tree. You have the root node having leaves and trunks attached to it. Each trunk also have a leaves/trunks. Here leaves are the attributes/values while trunks are the elements of the tree. You need at least two tables for the Tree and EAV stored separately:

Tree                EAV
+----+--------+     +----+---------+------+-------+
| id | parent |     | id | tree_id | attr | value |
+----+--------+     +----+---------+------+-------+

Here Tree stores the structure/subordinance while EAV stores the items within the structure. An exact implementation is not so complicated with modern versions of MySQL/MariaDB having recursive CTEs.


How to open Oracle listener for three database instances? I have 3 database instances:

orcl
JIP02T
JIP04T

When I start the database, does it start all 3 instances?
When I start the Oracle listener, does it start a listener for all 3 instances?

When I echo $ORACLE_SID:

[ora112@localhost admin]$ echo $ORACLE_SID
orcl

I can only see orcl, and the listner is started only for one database instance. I can connect to only one database instance using SQL Developer.

How can I start the database and listeners for other database instances as well?

Ok, I would like to answer my question. As i have understood what wrong I have done.

I created 3 database instances. So I should start 3 database instance one by one.

So how do we start 3 database instances?

every database instance has its own SID, so before you start the database, you need to put the
correct SID for that.

this is how i did it

ora112@localhost ~]$ ORACLE_SID=JIP02T
[ora112@localhost ~]$ ls
db.rsp  Desktop  summary
[ora112@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 27 01:23:35 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 5511979008 bytes
Fixed Size                  2238056 bytes
Variable Size            1107298712 bytes
Database Buffers         4378853376 bytes
Redo Buffers               23588864 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64                                                                             bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Then i need to set the SID for other database instance.

ora112@localhost ~]$ ORACLE_SID=JIP04K
[ora112@localhost ~]$ ls
db.rsp  Desktop  summary
[ora112@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 27 01:23:35 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 5511979008 bytes
Fixed Size                  2238056 bytes
Variable Size            1107298712 bytes
Database Buffers         4378853376 bytes
Redo Buffers               23588864 bytes
Database mounted.
Database opened.
SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64                                                                             bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

There after i just started the oracle listener, oracle listener is opened on port 1521.

So, using sql developer, i created 2 database connections to the two database instances using same port number 1521.


How do I correct this value too long for type character varying(5)? I created a small development database for practice. It has a table cities with columns cityname and state. The cityname in there is 'Cincinnati', long name right?

mytestdb=# SELECT * FROM cities;
 cityid |  cityname  | state
--------+------------+-------
 12345  | Cincinnati | Ohio
(1 row)

I am unclear as to how and why I am getting this error message upon attempting to add 'San Francisco'.

mytestdb=# INSERT INTO cities VALUES ('San Francisco','CA');
ERROR:  value too long for type character varying(5)

So first, what's the difference..

SELECT x, length(x)
FROM ( VALUES
  ('Cincinnati'),
  ('San Francisco')
) AS t(x);

Here is the output

       x       | length
---------------+--------
 Cincinnati    |     10
 San Francisco |     13

So..

San Francisco is three characters longer.
They're both over 5 characters.
That can't be the problem.

And further, if Cincinnati was in a varchar(5), it'd have to get truncated.

So the problem is your cityid. It is varchar(5). you probably want that to be an int anyway -- it'll be more compact and faster. So ALTER the table and fix it.

ALTER TABLE cities
  ALTER COLUMN cityid SET DATA TYPE int
  USING cityid::int;

As a side note... maybe someday PostgreSQL will speak column names in error messages. until then at least it's more verbose than SQL Server.


PostgreSQL: Immutable, Volatile, Stable I'm unclear about the true meaning in the definitions for IMMUTABLE, VOLATILE and STABLE functions.

I read the documentation, specifically the definitions of each.

  IMMUTABLE indicates that the function cannot modify the database and
  always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use
  information not directly present in its argument list. If this option
  is given, any call of the function with all-constant arguments can be
  immediately replaced with the function value.

  STABLE indicates that the function cannot modify the database, and
  that within a single table scan it will consistently return the same
  result for the same argument values, but that its result could change
  across SQL statements. This is the appropriate selection for functions
  whose results depend on database lookups, parameter variables (such as
  the current time zone), etc. (It is inappropriate for AFTER triggers
  that wish to query rows modified by the current command.) Also note
  that the current_timestamp family of functions qualify as stable,
  since their values do not change within a transaction.

  VOLATILE indicates that the function value can change even within a
  single table scan, so no optimizations can be made. Relatively few
  database functions are volatile in this sense; some examples are
  random(), currval(), timeofday(). But note that any function that has
  side-effects must be classified volatile, even if its result is quite
  predictable, to prevent calls from being optimized away; an example is
  setval().

My confusion comes in with the condition for IMMUTABLE and STABLE that the function ALWAYS or CONSISTENTLY returns the same result given the same arguments.

The IMMUTABLE definition states that the function does not database lookups or otherwise use information not directly present in its argument list.  So, to me, that means such functions are used to manipulate data provided by the client, and should not have SELECT statements...although that just sounds a bit odd to me.

With STABLE, the definition is similar in that it says it should consistently return the same result.  So, to me, that means that everytime the function is called with the same arguments, it should return the same results (same exact rows, every single time).

So, to me...that means that any function that performs a SELECT on a table or tables that can be updated, should only be volatile.

But, again...that doesn't sound right to me.

Bringing this back to my use-case, I am writing functions that perform SELECT statements with multiple JOIN's on tables that are constantly being added to, so the function calls would be expected to return different results each time it's called, even with the same arguments.

So, does that mean that my functions should be VOLATILE?  Even though the documentation indicates relatively few database functions are volatile in this sense?

Thank you!

IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot access the value of configuration properties, etc.
STABLE can use any inputs that are themselves STABLE: other STABLE or IMMUTABLE functions, and SELECT queries of tables. It's safe to query tables because the function's view of those tables will not change within the query's current snapshot. You can access GUC values (current_setting(...)) so long as you know they won't also be assigned to within the current statement.
VOLATILE functions are everything that doesn't fit the above:

Anything with side-effects
Anything that does writes
Anything that queries external data not managed by the PostgreSQL snapshot
...

In general, just leave everything VOLATILE unless you have a good reason not to.
The main reason to use IMMUTABLE is when writing functions that are to be used as part of index expressions.


How to upgrade PostgreSQL minor releases without a dump/restore? Our production server is running PostgreSQL v8.2.3 on CentOS5.2.  We're planning to upgrade to the latest version in 8.2 series, that is v8.2.20.  Also, in future, we've planned to upgrade to the most recent minor release as soon as possible (as and when released by PostgreSQL).

Initially, we've installed v8.2.3 from source.  PostgreSQL is installed in /usr/local/pgsql/ and data directory is available in /usr/local/pgsql/data

PostgreSQL Versioning policy says:

  Upgrading to a minor release does not
  require a dump and restore; merely
  stop the database server, install the
  updated binaries, and restart the
  server.

Now, without a dump/restore, what are the recommended ways/steps involved in upgrading PostgreSQL to latest version for minor releases?

EDIT: Well, I should have asked my question this way.  Since the doc says install the updated binaries, my question is whether the latest version can be installed as-is. Will my data directory /usr/local/pgsql/data/ be disturbed in any way? Or should this data directory be moved temporarily until the latest installation  is completed.  In addition,  iss there anything, as an admin, I need to take care, like, taking a backup of /usr/local/pgsql/data/postgresql.conf, etc.?

If you are doing a minor version upgrade, nothing will or should touch your data directory.  If you are using binary packages, just run whatever upgrade procedure they provide (apt/yum upgrade, etc.).  If building from source, just do make install over the old files and then restart the server (pg_ctl restart or whatever you're using).

The documentation could perhaps be amended to say that you could even install the new binaries before restarting the server.


How do completely empty columns in a large table affect performance? I have 400 million rows in a Postgres db, and the table has 18 columns:

id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without time zone,
h integer,
i timestamp without time zone,
j integer,
k character varying(32),
l integer,
m smallint,
n smallint,
o character varying(36),
p character varying(100),
q character varying(100)

Columns e, k, and n are all NULL, they do not store any values at all and are completely useless at this point. They were part of the original design, but never removed.

Edit - most of the other columns are non-NULL.

Questions:

How can I calculate the impact this has on storage?
Is it equal to size of column * # of rows ?
Will dropping these empty columns noticeably improve performance for this table?
Would the page cache be able to fit more rows?

Since the columns e, k, and n can be NULL, I assume "100% empty" means NULL.
NULL storage is cheap. Each NULL "costs" one bit in the null bitmap for storage and otherwise hardly affects performance. Effective storage requirements depend on whether the null bitmap for each row already exists and has room for 3 more bits.
Tables with up to 8 user columns utilize a spare byte after the tuple header for the null bitmap in the current implementation. For any more, another MAXALIGN (typically 8) bytes are allocated providing for another 64 columns (for a total 72 now). Etc. So the null bitmap effectively costs 8 bytes per row for most tables, including yours which has 18 columns.
The null bitmap is allocated completely or not at all per row and only if there is at least one actual NULL value. If all columns are defined NOT NULL, there can never be one.
In the absence of other, more important considerations, move columns that are always (or mostly) NULL to the end of the row. Helps performance a tiny bit.
Answer to question 1.
The effective storage requirement is:

0 bytes per row where other columns are also NULL.
8 bytes (typically) per row where no other column is NULL, for allocating the null bitmap

Answer to question 2.
Dropping would not buy you much. Page cache would be the same or slightly less depending on 1. Handling queries, backups, source code and such would be simplified.
Further reading:

Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
Adding nullable column to table costs more than 10 minutes
Measure the size of a PostgreSQL table row
Configuring PostgreSQL for read performance

What you did not ask for
After a round of "column tetris" I suggest this table layout - again barring other, more important considerations:
  id serial NOT NULL
, a  integer
, g  timestamp
, i  timestamp
, b  integer
, c  integer
, h  integer
, j  integer
, l  integer
, d  smallint
, f  smallint
, m  smallint
, o  varchar(36)
, p  varchar(100)
, q  varchar(100)
, k  varchar(32)      --  always NULL
, n  smallint         --  always NULL
, e  timestamp        --  always NULL

Saves at least 16 bytes per row compared to your original layout, probably around 20+, depending on your varchar columns. Instructions for "column tetris" here:

Calculating and saving space in PostgreSQL

How to reorder columns
The simplest method is to create a new table and copy data into it. Then you get a pristine new table without dead columns (and dead rows). You could sort (cluster) the physical order of rows in the process, too.
Changes in place are possible too, but there is no convenient tool (I would know of) to generally reorder the sequence of columns. Depending objects like views, functions (depends), FK constraints, indexes etc. are limiting factor here. Those can block you from deleting a column, you have to drop and recreate depending objects.
And of course, concurrent access to the table conflicts with DDL commands changing it. You need exclusive table locks in this case.
It's simple and cheap to delete completely empty columns and add them at the end of the row. Switching the sequence of populated columns in the middle of the table is not as simple or cheap. I would create a new table for that.
This related answer has recipes and links to more:

Best way to populate a new column in a large table?


How can I log which queries are in a distributed transaction on an MS SQL server? I am looking at migrating a database from a self-hosted cluster to Microsoft Azure SQL. I am aware that there are a few distributed transactions involved, which isn't supported on Azure SQL.

Is there a way that I can log all distributed transactions and their queries, so that I can inspect the client application and remove the requirement for distributed transactions?

You should be able to track the DTCTransaction event in SQL Trace or XEvents.

Or you could just shut down MSTDC (not in production, of course) and see what fails.

Also note that updates to .NET's SqlClient enables some distributed transaction scenarios with Azure SQL Database without MSDTC.  See

Distributed transactions across cloud databases


In real terms, how much effect does a bad fillfactor have on performance? There's a storm brewing, or maybe I should say firestorm.  A customer has recently implemented SQL Server 2012 Always On but that's not the most important bit.

A list of fires to be put out has been arrived at, and one of them is "incorrect fill factor".  Someone in their infinite wisdom had decided to recreate a multi-billion-record table with over 10 indexes without specifying the FILLFACTOR for the indexes during the recreation (showing in sys.indexes as 0%).

I have always though that page splitting is bad, but in real terms, how bad is bad? Is it worse than not having the index at all? Is there a concrete argument for putting out this fire before the others? Note: it will take a significant amount of time for each index rebuild.

FYI - SQL Server 2012 Enterprise Edition

This is only a partial answer to your question, because you don't provide enough information on how the database will be used.  I also think this question may be more appropriate on the database administrators site.

For instance, if the data is loaded nightly and the database is only used during the day for querying, then you want the fill factor to be 100%.  That will be the most efficient approach for querying, minimizing the number of pages.

Page splitting in indexes occurs when a new record lands on a page that is full.  The page is split into two pages, which are then, roughly, 50% full.  Once the page is split, there is more space for those values.

If the data is being updated with multiple updates or inserts per second, then page splitting is unnecessary overhead.  However, on indexes, this will probably happen the first time a new record lands on an index page.

Note that page splitting is going to happen anyway, once pages are filled.  This would happen anyway, regardless of the fill factor.  The major question is "start-up" time, because initially basically all inserts/updates will result in page splits.  But this should quickly dampen.

The overhead also depends on the characteristics of the implementation.  If the indexes fit into memory, or if your disk system is solid-state disk, then the overhead might not be noticeable at all.

My guess is that this is not the most important problem, unless consistency of performance when the new database first goes online is paramount.


Is possible to use ORDER BY with IF ELSE condition? Here I have some data. I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the remaining records in descending order(date). I have written a query, but it is not working as expected:

(SELECT *
FROM `test`
WHERE fav = 1
ORDER BY date DESC)

UNION

(SELECT *
FROM `test`
WHERE fav = 0
    AND date>DATE(NOW())
ORDER BY date ASC)

UNION

(SELECT *
FROM `test`
WHERE fav = 0
    AND date>=DATE(NOW())
ORDER BY date DESC)

Please share the query if you know how to do it. Thank you.

I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the remaining records in descending order(date).

Try:

SELECT *
FROM `test`
ORDER BY fav=1 desc,
         fav=0 AND `date`>=DATE(NOW()) DESC,
         CASE WHEN fav=0 AND `date`>=DATE(NOW())
                  THEN UNIX_TIMESTAMP(`date`)
                  ELSE -UNIX_TIMESTAMP(`date`)
              END ASC;


MySQL Multi-Master Replication Topology Disclaimer: I've never worked with replication before, but I'm doing all my research. Not really having a bunch of in-depth knowledge about MySQL makes a lot of the documentation hard to understand.

We have a remote server, and many 'onsite' servers. This app is mission-critical, and because of the unreliable internet connections on site, the onsite machines have fully functional instances of the application. Onsite machines are behind various firewalls and generally are not able to access each other. We can set up SSH tunnels to allow the cloud DB to communicate with the onsite DBs.

Here's a simple diagram:

Async Multi-Master replication seems to be the best bet for us, but most of the documentation seems to imply that multi-source replication only works for slaves and/or multi-master replication only works circularly and a single master (in this case, the cloud server) can not replicate directly to multiple other masters.

Am I interpreting this incorrectly (merely unable to find documentation on how to do this), or is this the case? If it's the case, are there are 3rd party packages like Tungsten or Galera we can use? We would prefer to use as vanilla MySQL as possible, though.

Edit for clarification: Here's a my.cnf section I found describing a 2-master setup. I'd like to set N number of these on the cloud server, and just one each (the cloud server) on the onsite servers. We'd like to do everything through my.cnf and avoid using/scripting mysql shell commands.

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

MULTIMASTER REPLICATION
If you want to use Galera, just get Percona XtraDB Cluster. It comes right-out-of-the-box with Galera libraries wrapped up inside. Just put the wsrep options in my.cnf to to enable Galera. See the PXC Documentation for more info.
If you have EC2, you can also use the PXC Setup Guide in EC2.
MULTISOURCE REPLICATION

multi-source replication only works for slaves

This is correct. The terminology fits what it says. It is a slave that can replicate from multiple masters. This is possible with MariaDB and MySQL 5.7
If you want to perform multisource replication with vanilla (more like sugar-free) MySQL 5.1/5.5/5.6, you will need to set the up as round robin. Page 227,228 of the Book (ISBN 0596807309)

has the algorithm for doing robin robin multisource replication

Setup the slave to replicate from one master. We'll call this the current master.
let the slave replicate for a fixed period in time. The slave will read from the current master and apply them while the client
responsible for handling the switch just sleeps.
Stop the I/O thread of the slave using STOP SLAVE IO_THREAD.
Wait until the relay log is empty.
Stop the SQL thread using STOP SLAVE SQL_THREAD. CHANGE MASTER requires you stop both threads.
Save the slave position for the current master by saving the values Exec_Master_Log_Pos and Relay_Master_Log_File columns from SHOW SLAVE
STATUS output.
Change the slave to replicate from the next master in sequence by taking the previously saved positions and using CHANGE MASTER to setup
replication.
Restart the slave threads using START SLAVE.
Repeat the sequence starting from step 2.

I wrote a post about this technique before I read the book

Apr 26, 2011 : MySQL in Star Topology (StackOverflow)
Apr 28, 2011 : MySQL in Star Topology (ServerFault)

BTW Pages 228,229 of the Book has sample code in Python
MySQL REPLICATION
Please do not put the options for connecting up replication in my.cnf.
They have been obsolete since MySQL 5.5
It says so in the MySQL 5.6 Documentation under the heading

Obsolete Replication Slave Options
The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.6, the server aborts with an unknown variable error. To set the replication parameters formerly associated with these options, you must use the CHANGE MASTER TO ... statement (see Section 13.4.2.1, “CHANGE MASTER TO Syntax”).
The options affected are shown in this list:
--master-host
--master-user
--master-password
--master-port
--master-connect-retry
--master-ssl
--master-ssl-ca
--master-ssl-capath
--master-ssl-cert
--master-ssl-cipher
--master-ssl-key


get unique records from last 6 months i am facing the  below issue

Table structure :

some_table CREATE TABLE `some_table ` (
`wallet_id` int(11) NOT NULL AUTO_INCREMENT,
`wallet_amount` double(10,2) NOT NULL,
`actual_wallet_amount` double(10,2) NOT NULL,
`customer_id` int(11) NOT NULL,
`last_update_on` datetime NOT NULL,
`redeemption_date` date NOT NULL,
`expiry_date` date NOT NULL,
`wallet_status` varchar(200) NOT NULL,
`order_id` varchar(250) NOT NULL,
PRIMARY KEY (`wallet_id`)
) ENGINE=InnoDB

query :
SELECT *
FROM  some_table
WHERE redeemption_date <= CURDATE( ) - INTERVAL 6 MONTH
AND wallet_status !=  "EXPIRED"

Desired Result:

Need the get all the unique customer_id with latest last_updated_on
Need the get all the records in the table which have only one enter last 6 months and above
when adding group by clause on customer_id i am not getting latest
updated customer_id
i need all the latest unique customer_id

please help thanks in advance.

This type of queries are sometimes called "greatest-n-pre-group" (there's even a tag for these questions at SO!) and there are various ways to write them.

Assuming that the (customer_id, last_updated_on) is unique - i.e. there are no two rows with same last_update_on timestamp for the same customer - then you could first GROUP BY customer_id and get the maximum timestamp in a derived table and then join to the original table. Like this:
-- query 1

SELECT s.*
FROM
    ( SELECT customer_id,
             MAX(last_update_on) AS last_update_on
      FROM some_table
      WHERE redeemption_date > CURDATE() - INTERVAL 6 MONTH
        AND wallet_status <> 'EXPIRED'
      GROUP BY customer_id
    ) AS m
  JOIN
    some_table AS s
      ON  s.customer_id = m.customer_id
      AND s.last_update_on = m.last_update_on
WHERE s.redeemption_date > CURDATE() - INTERVAL 6 MONTH
  AND s.wallet_status <> 'EXPIRED';

Another variation is to first find the customer ids (similarly to above) but then join to the table using a correlated subquery. This is often more efficient if one can find easily the distinct customer ids (so, perhaps not in your case). Here is how it can be done, so you can test both:
-- query 2

SELECT s.*
FROM
    ( SELECT customer_id
      FROM some_table
      GROUP BY customer_id
    ) AS d
  JOIN
    some_table AS s
      ON  s.wallet_id =                   -- the PRIMARY KEY
          ( SELECT si.wallet_id           -- of the table
            FROM some_table AS si
            WHERE si.customer_id = d.customer_id
              AND si.redeemption_date > CURDATE() - INTERVAL 6 MONTH
              AND si.wallet_status <> 'EXPIRED'
            ORDER BY si.last_update_on DESC
              LIMIT 1
          );


Join second table based on third table with conditions I"m using Doctrine for a website that has 3 tables to connect together. In simplest terms, they look like this

Contractor
- id
- name

ContractorJob
- id
- contractor_id
- job_id
- status

Job
- id
- start_date
- end_date

I want to join Jobs to Contractors based on a date range, but you can see that the only way to do that is through the ContractorJob table. It means that a simple inner join between Contractor and ContractorJob is essentially random, and therefore the subsequent join between ContractorJob and Job is mostly useless.

How can I join Contractor to Job using start_date and end_date as conditions?

Pseudo query is as follows

select from contractor c
left join ContractorJob cj on cj.contractor_id = c.id
left join job j with j.contractor_job = cj.id and j.start_date < :start_date and j.end_date > :end_date
where j.id is null

(find all contractors who do not have a job in the given date range)

*note: Doctrine is a web framework DB manager that handles low level query processing so that applications can use more high-level functions. It allows you to say query.innerJoin('a.second_table', 'b') and have the correct join made

There are several problems with the query

The select list is missing.
The join syntax needs ON, not WITH:  a JOIN b ON <some condition>
There is a missing on condition (what you correctly identified as arbitrary/random join between Contractor and ContractorJob).
There are column names mismatches between the code and the design: (Job.contractor_job vs. Job.id). Which is the correct one?

How to write it with LEFT JOIN / IS NULL. The parentheses are redundant, placed only for making the joins more clear:

select
    c.*
from
        Contractor as c
    left join
        (
            ContractorJob as cj
        inner join
            Job as j
        on
                j.id = cj.job_id
            and j.start_date < :start_date
            and j.end_date > :end_date
        )
    on
        c.id = cj.contractor_id
where
    cj.contractor_id is null ;

How to write it with NOT EXISTS which I find easier to grasp:

select c.*
from Contractor as c
where not exists
    ( select *
      from ContractorJob as cj
        inner join Job as j
        on  j.id = cj.job_id
      where j.start_date < :start_date
        and j.end_date > :end_date
        and c.id = cj.contractor_id
    ) ;


ORA-27101 while trying to login after shutdown immediate SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 19:29:19 2018

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

Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name: sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

I think you did the following steps and caught this error.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

You tried to start SQL*PLUS and starting the database.

[oracle@myserver oracle]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Apr 24 00:20:59 2018

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

Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -688980181
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

Enter user-name: sysdba
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -688980181
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

The problem is the database has not started to do the authentication for these supplied usernames.

You could connect to the idle instance using as as sysdba as the username to start the instance.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  729808896 bytes
Fixed Size          8797056 bytes
Variable Size         452985984 bytes
Database Buffers      260046848 bytes
Redo Buffers            7979008 bytes
Database mounted.
Database opened.
SQL>


Access rights on SQL Server 2012 I have a Microsoft SQL Server 2012 server used by two Active Directory groups.

How can I configure this groups so that:

group 1:
Can do everything on database level, including read everything, write everything, add new databases, modify or delete them, and restore backups.
However, the group can not change the settings of the server or shut it down.
group 2:
Can read and write in every database but cannot modify its structure nor change server settings.

I've tried to add the groups to the model database, which works fine when creating new databases.  However, if there are restores from other servers, the groups have no access to it.

Is there a way to define a server wide role or right, such that it applies to every database, regardless if the database was created or restored?

GRANT ALTER ANY DATABASE TO [somelogin]; or adding the user to the db_creator server-level role, will allow that login to create and/or restore databases, however, that user will not be able to access that database unless the restored database already has a user with the same SID that has the appropriate access.  See my answer here for more details.

There is no server-level role or right that allows a login access to any database without simultaneously allowing that login to modify server-level properties.

The typical setup for creating/restoring databases consists of having a sysadmin member do the restore, and modify access per the requirements for that specific database.

Technet has a link to an excellent poster that describes all the rights and permissions grantable inside SQL Server.


Rebuild index error: "The job was stopped prior to completion by User" I have a rebuild index job that is set to run every night. The job used to run fine, but last night for some reason the SQL Server Agent Job was stopped. The rebuild index job was running on 9 different databases and it used to take less than 2 minutes, but it stopped all of a sudden last night with an error in the last step.

Here is error message:

  The job was stopped prior to completion by User [User_name]. The job
  was invoked by Schedule 37 (daily).

  Executed as User: User-Name. The step was cancelled (stopped) as the
  result of a stop job request.

I checked on SQL Server logs, there are no error messages. Where can I find exact root cause of job stopped?

Any suggestions?

The user stopped the job, probably because it was running long.

That is not an error message, it is telling you what happened.

Contact the user and ask them why the stopped the job.


Is there a combination of columns in sys.dm_exec_sessions that is unique per the server? In SQL Server, each session has its own spid. Spids are unique at any given notice, but spids, like process and thread identifiers in the OS are recycled.

However sys.dm_exec_sessions has other columns with session metadata. Is there a combination of columns that is guaranteed to be unique for a server instance?

Sessions belong to connections (1:M) and connections have unique GUID identifier, see sys.dm_exec_connections:

  connection_id uniqueidentifier Identifies each connection uniquely. Is
  not nullable.

Whenever you capture sys.dm_exec_sessions, join with sys.dm_exec_connections and capture the connection_id too.


Alternate Name of Data Dictionary What is the alternate name of Data Dictionary?
► Index
► Metadata
► Data
► System Catalog

What I know already is, data dictionaries store all the various schema and file specifications and their locations. They also contain information about which programs use which data and which users are interested in which reports. But what is the alternative name of data dictionary?

In his seminal paper A Relational Model for Large Shared Data Banks, Dr. E.F. Codd mentiones a system catalog. In his famous Twelve Rules he stipulates an (online) catalog.

Then, in his book entitled The Relational Model for Database Management, Version 2, he defines catalog and meta-data as follows:

  The catalog is […] a relational database itself—one that is dynamic and active and that represents the meta-data. Meta-data consists of data that describes all of the data in the database, as well as the contents of the catalog itself.

In chapter 15 of the same book, he makes a distinction between the terms catalog and dictionary that I consider quite relevant regarding your question:

  In the relational model, the catalog holds the database description. In some relational DBMS products this description is also called the catalog, while in others it is called the directory. Whatever it is called, it should be carefully distinguished from a dictionary, which normally includes all of the information found in the catalog, but also contains a large amount of information concerning the application programs that operate on a scheduled or non-scheduled basis upon various parts of the database.

Which confirms your description of a data dictionary.

After that, in the same book and chapter, he makes a comment that relates a catalog and a dictionary very closely:

  […] authorized
   users can easily extend the catalog so that it becomes a full-fledged, active, relational data dictionary, whenever the DBMS vendor fails to do so.

So, one can say that a data dictionary is an enhanced catalog.

In practice, of course, specific SQL platforms can use the same terms or establish their own names to refer to these elements. For instance, Oracle provides, precisely, a data dictionary that, according to this link from the online documentation, is…

  […] a read-only set of tables that provides information about the database.

  A data dictionary contains:

  The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
  How much space has been allocated for, and is currently used by, the schema objects
  Default values for columns
  Integrity constraint information
  The names of Oracle users
  Privileges and roles each user has been granted
  Auditing information, such as who has accessed or updated various schema objects
  Other general database information

So, in this case, the data dictionary includes the catalogue.

Each alternative name will depend on the particular platform of choice.


DB2 Audit Policy not producing EXECUTE data I'm trying to set up an Audit facility on a DB2 AWSE V10.5 FP5 database

running on Red Hat Linux Server 6.8.

The definition for the policy is :

ALTER
AUDIT POLICY Failures
    CATEGORIES
        Audit STATUS Failure,
        Checking STATUS Failure,
        Context STATUS Failure,
        ObjMaint STATUS Failure,
        SecMaint STATUS Failure,
        **execute with data STATUS Failure**,
        Validate STATUS Failure
    ERROR TYPE NORMAL ;

This is producing data for all the categories, except EXECUTE.

My test script includes statements that fail with SQL0204, SQL0206, etc. but no data is extracted into the execute.del file in the audit output.

Anyone have an idea what I'm missing?

A note in the manual explains why these statements do not make it into the audit log:

  The preparation of a statement is not considered part of the execution. Most authorization checks are performed at prepare time (for example, SELECT privilege). This means that statements that fail during prepare due to authorization errors do not generate EXECUTE events.

SQL0204N and SQL0206N, both of which complain about invalid object names, are raised during the statement parse phase, that is, before execution starts. Errors raised after execution starts, such as SQL0911N (lock timeout), will be audited.


Where can I find guidelines for building a SQL Server machine specifically for DBCC CHECKDB work? I'm working on defining specs for a new server to run DBCC CHECKDB scans on all databases in our portfolio (85 instances, 1300 DBs, 15 TB allocated). I have a custom application that copies dump files to a single server, then performs the restore and DBCC and required logging for reporting, but sequential I/O throughput is my bottleneck (I was working with set of RAID 6 LUNs from our SAN using 7.2K spindles, getting 300 MB/s read but only 50 MB/s write).

Is there a whitepaper or other resource available to help spec out a server for Maximum Consumption Rate specifically for DBCC? I looked at the MSFT whitepaper for Fast Track Data Warehouse servers, but I'm not sure it's the right fit.

Thinking outside the box, I'm contemplating going to a DAS enclosure like PowerVault and using RAID 0. The system DBs will be on RAID 1 on local storage but using RAID 0 for the restore and DBCC work makes sense because as soon as I finish testing a DB I drop the DB to make room for the next one. I can keep a few spindles as hot spares to rebuild a down array if needed and pick up where I leave off.

Any suggestions would be deeply appreciated!

You want disks that are as fast as you can afford.  DBCC CHECKDB is going to be limited by physical IO speed and pretty much nothing else.

That said, you do still need to run DBCC CHECKDB on your production server as you aren't doing physical IO checks on the production server at this point.


MySQL: Trying to update to 5.5 I've followed this instructions, but after mysqld --skip-grant-tables --user=mysql, I'm getting this:

110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test
110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2)
110913 20:29:18 [ERROR] Aborting
110913 20:29:18 [Note] mysqld: Shutdown complete

what I think it could be normal because the tutorial ask to do "rm -R /var/lib/mysql" on the step before. So I then create /var/lib/mysql, execute again "mysqld --skip......" and I get this:

110913 20:32:07 [Note] Plugin 'FEDERATED' is disabled.
110913 20:32:07  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

This is my /etc/my.cnf:

 # The following options will be passed to all MySQL clients
[client]
user            = mysql
#password    = your_password
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir         = /usr/local/mysql
datadir         = /usr/local/mysql/data
tmpdir          = /tmp
log_error       = /var/log/mysql/error.log
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

I have ubuntu 10.10.

Any idea?

Javi

The error means mysqld does not have the access rights to the directory.

When you recreated /var/lib/mysql, you need to make sure the /var/lib/mysql directory is owned and writeable by the mysql user.


SPIDs stuck indefinitely in suspended state I have a few application servers that all connect to a central database server. The applications execute stored procedures (that get XML) on the database.

Every once in awhile a transaction will get "stuck" and stop processing. The spid is in the suspended state with the wait type - ASYNC_NETWORK_IO. The last reads and writes in sys.dm_exec_connections are not current. Basically, it's just sitting there indefinitely. The stored procedure is using TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Tops waits on the server are:

wait_type           wait_time_s     pct     running_pct
CXPACKET            16737173.38     56.60       56.60
ASYNC_NETWORK_IO    3628438.90      12.27       68.87

These "stuck" spids can block index rebuilding, specifically on a table that is being used in a select in the stored procedure. I have isolated this section in the store procedure.

Why does this spid just sit there if it's not doing anything? We are thinking that the application times out when processing large amounts of XML. If this is the case then is there anything I can do on the SQL side to close this connection or should this be handled on the application side?

The application developers are basically saying that it's a SQL issue because it's a stuck transaction.

The only way to get rid of these now is to set up a job that would check to see any non current spid and kill it or kill it manually. Neither is really an acceptable solution. How can I handle these SPIDs?

Running SQL Server 2008 R2 (SP2) - 10.50.4000.0

If you've got SPIDs sitting with the wait type of ASYNC_NETWORK_IO it's either row by row processing within the application, the application is calling huge recordsets which is can't process fast enough, or there's a network capacity problem.


Strange looking where clause in sql server I've found fa strange looking code in T-SQL.
Here is the strange looking part:

WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (businessLogicCondition))

The query works well, but what is the point to do @term1 IS NULL OR (@term1 IS NOT NULL. Maybe it is connected with the fact that sql server doesn't have short-circuits WHERE condition evaluation.
From my C# background it seems really strange.

The question is: is it really a good practice to do such double comparisons? does it affect execution plan?

Added:
my businessLogicCondition is misleading, so here is revised version:

WHERE @term1 IS NULL OR (@term1 IS NOT NULL AND (ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1))

from my point of view the query is equivalent to:

WHERE @term1 IS NULL OR ua.FirstName LIKE @term1 OR ua.SecondName LIKE @term1

the question is whether the original option can improve execution plan, or it depends?

SQL Server does NOT have guaranteed short circuiting in that scenario. The point of this construct is so that businessLogicCondition is only evaluated as part of the WHERE clause in the case where @term1 is populated. It may still be evaluated as part of query processing, because you can only control order of evaluation in a few special cases (no pun intended).

This WHERE clause would actually have different meaning, unless businessLogicCondition actually referenced @term1 explicitly and in such a way that it could only return true if @term1 is NOT NULL:

WHERE @term1 IS NULL OR (businessLogicCondition)

businessLogicCondition may still be evaluated, but this form of the WHERE clause could, potentially, return rows based on businessLogicCondition, regardless of whether @term1 is NULL or NOT NULL.

Now, with the pertinent info added to your question:

No, in this specific case, the @term1 IS NOT NULL AND is redundant and cannot possibly help the optimizer come up with a better plan (except maybe in the case where there is a filtered index with that same). In fact in the above scenario I mentioned before I had that information, it won't lead to a better plan either, but it can change whether the results are correct or not.

It is important to note that as written SQL Server will optimize and cache an execution plan based on the first execution. It may be the case that a better plan can be used in the scenario where @term1 is NOT NULL (e.g. when @term1 is something like somestring%, it may be able to seek, but not with %somestring%). So you might consider using dynamic SQL to formulate one version of the query or the other, depending on the value of the parameter, especially if you have multiple optional parameters (I call this "the kitchen sink" procedure). The OR that remains may still be problematic; there are a lot of variable factors.

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.


Full Text Search only performs 'starts with' searches Pretty basic question, but how can I get the containstable search to do an actual 'contains' search, rather than a 'starts with' one?

For example, from the examples in the documentation:

CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);
INSERT Flags VALUES ('France', 'Blue and White and Red');
INSERT Flags VALUES ('Italy', 'Green and White and Red');
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');
SELECT * FROM Flags;
GO

CREATE FULLTEXT CATALOG TestFTCat;
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;
GO

SELECT * FROM Flags;
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green') ORDER BY RANK DESC;
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, 'Green or Black') ORDER BY RANK DESC;

Works fine, and so does:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"Gree*"') ORDER BY RANK DESC;

But this does not:

SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen"') ORDER BY RANK DESC;
SELECT * FROM CONTAINSTABLE (Flags, FlagColors, '"*reen*"') ORDER BY RANK DESC;

Should I be using a different query, or is what I'm looking for not possible?

This is, I think, a common misconception when it comes to full text search in SQL Server.

Full Text Search enables searching for entire words as well as a number of other things.  From the CONTAINS documentation

  CONTAINS can search for:

  A word or phrase.
  The prefix of a word or phrase.
  A word near another word.
  A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
  A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

So just like with normal indexes on text strings, you can do prefix searching.

Neither full text search, nor normal SQL Server indexes, support "postfix" or "contains" searching within a word or text string.  So no leading wildcards, essentially.

Note that the same limitations apply to CONTAINSTABLE:

  CONTAINSTABLE is useful for the same kinds of matches as the CONTAINS predicate and uses the same search conditions as CONTAINS.

I imagine these limitations exist because of the way indexes are logically stored in SQL Server - as b-trees, where it's very easy to "jump" or "seek" to the different parts of an index based on the start of the data in the indexed column.


2 Tricks needed:

UNIX_TIMESTAMP() allows application of integer division to datetimes
Pivoting isn't part of SQL proper, but group_concatcan do something similar.

Putting it together:

select
  startpit,
  FROM_UNIXTIME(UNIX_TIMESTAMP(startpit)+@interval) as endpit,
  group_concat(concat(camname,': ',num)) as events
from (
select
  from_unixtime(
    floor(
      unix_timestamp(camtimestamp)/@interval
    )*@interval
  ) as startpit,
  camname,
  count(*) as num
from events,
(select @interval:=3600) as init
-- where
group by floor(unix_timestamp(camtimestamp)/@interval)*@interval, camname
) as baseview
group by startpit;

See this SQLfiddle (I replaced changed every second rows from 'wsl1' to 'wsl2' since your test data has only one cam.

EDIT

MySQL 8.0 defaults to a very buggy implementation of strict grouping. It will e.g. think, that FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval is (quote) "not functionally dependent" on FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval. This can easily be resolved by using the name of the calcualted column (startpit). So

SELECT
  startpit,
  FROM_UNIXTIME(UNIX_TIMESTAMP(startpit)+@interval) AS endpit,
  GROUP_CONCAT(CONCAT(camname,': ',num)) AS the_events
FROM
(
  SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval
) AS startpit,
  camname,
  count(*) as num
FROM event,
(
  SELECT @interval := 3600) AS init
-- where
  GROUP BY startpit, camname
) AS baseview
GROUP BY startpit;

works with MySQL 8.0, while the functionally identical original does not. This is not a flaw in the SQL, but in how MySQL 8.0 understands it. Unexperienced SQL programmers might consider this a good thing.

EDIT 2

There is no pivoting in SQL, i.e. the number of columns can not depend on the data so the format with a variable number of columns and an integer in each is not reachable via pure SQL.

Assuming, that the SQL is generated by another layer higher in the stack you can reach it by creating SQL along the lines of

SELECT
  startpit,
  FROM_UNIXTIME(UNIX_TIMESTAMP(startpit)+@interval) AS endpit,
  SUM(IF(camname='wls1', 1,0)) as wls1,
  SUM(IF(camname='wls2', 1,0)) as wls2,
  SUM(IF(camname='wls3', 1,0)) as wls3
  -- and so on
FROM
(
  SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(camtimestamp)/@interval) * @interval
) AS startpit,
  camname,
  count(*) as num
FROM event,
(
  SELECT @interval := 3600) AS init
-- where
  GROUP BY startpit, camname
) AS baseview
GROUP BY startpit;

You can even put creating this SQL as a string into a stored procedure and then use PREPARE STATEMENT and EXECUTE to run it. Be advised, that tis creates a stored procedure where the number of output columns depends on the data, which is unusual at least.


Management Studio all buttons disabled I get an odd problem in my SQL R2.
Although I can attach a new db to my db instance, but all buttons on the tool menu panel are disabled.
So I cannot execute any sql query statements.
It is really annoying !!!
I have been stuck on it for ages.

Could someone help me figure it out?

Thank you so much.

I have found the solution for this issue.

Simply go to
Start -> Microsft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Installation Center (64-bit)

Then go to the Maintenance option -> Repair.

Done.


MySql counting results with left joins doubles the results I have this query generated by Django where a user has orders and jobs as related tables. jobs are of two types: clean and wash. I want to count for each user shown the number of orders, clean jobs and wash jobs.

One of the users has 3 clean jobs in the database but this query returns the value of 6 for column clean_ct. If I remove the join to the order table and the orders count the counts are as expected so the second LEFT OUTER JOIN is causing this doubling to occur. I'm suspecting a wrong GROUP BY but I haven't found a solution. Any ideas?

SELECT DISTINCT `auth_user`.`id`,
                `auth_user`.`username`,
                Count(DISTINCT `order`.`id`) AS `orders_ct`,
                Count(CASE
                        WHEN `job`.`service` = 'clean' THEN 1
                        ELSE NULL
                      end)                   AS `clean_ct`,
                Count(CASE
                        WHEN `job`.`service` = 'wash' THEN 1
                        ELSE NULL
                      end)                   AS `wash_ct`
FROM   `auth_user`
       LEFT OUTER JOIN `order`
                    ON ( `auth_user`.`id` = `order`.`user_id` )
       LEFT OUTER JOIN `job`
                    ON ( `auth_user`.`id` = `job`.`user_id` )
GROUP  BY `auth_user`.`id`
ORDER  BY `auth_user`.`id` DESC
LIMIT  100

clean_ct should be 3 but returns 6

You have two joins from the base table (auth_user) and both joins are to tables with 1-to-many relationship with the base table. That creates the multiple rows. If for example, an auth_user has 3 orders and 7 jobs, the join (before any group by) will result in 21 rows.

When you then GROUP BY, the (created) rows are counted and you get incorrect results (in the example, you'd get the job counts multiplied by 3 and the orders count multiplied by 7). Your code would manage to avoid the x7 because it uses DISTINCT when counting orders but not avoid the x3 when counting jobs.

One solution would be to use DISTINCT in all counts. In your case, you could write the "wash" count as:

            Count(DISTINCT CASE
                             WHEN `job`.`service` = 'wash' THEN job.id
                             ELSE NULL
                           END)                   AS `wash_ct`

But this solution will become quite complicated and inefficient with many joins.

Another solution is to group by in each individual table and then join (the derived tables) to auth_user. Also:

GROUP BY and DISTINCT in the same SELECT are rarely needed:
ELSE NULL is the default in CASE expressions. (it's not a problem if you like it there, just not needed.)
You don't need to quote all table and column names. Only reserved words - which should be avoided anyway - and names with special characters.
It would best to avoid reserved words - like order - when naming tables and columns.

The query could be written:

SELECT au.id,
       au.username,
       COALESCE(orders_ct, 0) AS orders_ct,
       COALESCE(clean_ct, 0)  AS clean_ct,
       COALESCE(wash_ct, 0)   AS wash_ct
FROM   auth_user AS au
       LEFT OUTER JOIN
           ( SELECT user_id,
                    Count(*)  AS orders_ct
             FROM   `order`
             GROUP  BY user_id
           ) AS o
                    ON au.id = o.user_id
       LEFT OUTER JOIN
           ( SELECT user_id,
                    Count(CASE WHEN service = 'clean' THEN 1
                          END)  AS clean_ct,
                    Count(CASE WHEN service = 'wash' THEN 1
                          END)  AS wash_ct
             FROM   job
             GROUP  BY user_id
           ) AS j
                    ON au.id = j.user_id
ORDER  BY au.id DESC
LIMIT  100 ;


REPLICATE() return type for VARBINARY input REPLICATE ( string_expression ,integer_expression )

As documentation says, it

  returns the same type as string_expression.

I see that return type is always VARCHAR.

declare @vb varbinary(10) = 0x01
declare @res sql_variant = REPLICATE(@vb, 2)

SELECT sql_variant_property(@res, 'BaseType')

  varchar

Is the documentation wrong?

I raised this as a documentation issue.

I suspect that REPLICATE is expecting a string not a binary type, and an implicit conversion is taking place from VARBINARY to VARCHAR. If you update your example like so you get the expected type:

declare @vb nvarchar(10) = '#'
declare @res sql_variant = REPLICATE(@vb, 2)
SELECT sql_variant_property(@res, 'BaseType')

Even though it accepts a binary input, the spec does specifically state that parameter is a "string expression" which is why I suspect type coercion is happening before the function is actually run. By "expected type" I am referring to it not being fixed as VARCHAR irrespective of input type which might be implied when looking in your result on its own. So I think the documentation is correct, but perhaps not perfectly clear: the function returns the same [N][VAR]CHAR(<len>) type that is receives, and in your test it is receiving a VARCHAR() due to implicit conversion from VARBINARY to VARCHAR.


Oracle SQL 11gR2 => find out all dates where "impdp" was performed Hello my team performed several database Data Pump Import "impdp" over the last two years.

The workflow was always to import all "application" schemas ( The schemas that are used from our application) dailyBackup-2019-04-05.sql dump from one of the daily backups.

Now I need to find out the exact dates whe the "impdp"restores where performed.

I tried the following:

I found the file dpdump on my server but it only contains this three lines:

I only have one "import.log" file that got always overridden every time a restore with dpdump was taken.

Are there maybe system tables tables where I can check every time when a dpdump was performed

Where else can I look for the log some "import log" files?

Every time a Data Pump job like that is started, the database notes this in the database alert log. If you have the alert log, you can find the entries there.

Other than that, the database does not store a history of the Data Pump jobs, unless you specifically instruct Data Pump to keep the metadata by adding the KEEP_MASTER=yes parameter to your job.


Ola Hallengren - Backupdatabase Task When running Ola's full SQL Server Maintenance Solution or the individual databasebackup script I get the following error:

  MSG 207, Level 16, State 1, Procedure DatabaseBackup, Line 1545
  Invalid column name 'schemaName'.

This is part of the @databasesInParallel section which it appears to be trying to run even though this variable is set to 'N' at the beginning of the script. I haven't altered anything and this script works perfectly on other servers.

The dbo.Queue table exists, but does not have the SchemaName column.
This can be due to a custom dbo.Queue table that has nothing to do with Ola's solution. You could rename the table in the maintenance plan or drop the existing tables.

Recreating the error

Step 1: Recreate the maintenance solution Procedure

Step 2 Create the dbo.Queue table without the SchemaName

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Queue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Queue](
  [QueueID] [int] IDENTITY(1,1) NOT NULL,
  --  [SchemaName] [sysname] NOT NULL,
  [ObjectName] [sysname] NOT NULL,
  [Parameters] [nvarchar](max) NOT NULL,
  [QueueStartTime] [datetime] NULL,
  [SessionID] [smallint] NULL,
  [RequestID] [int] NULL,
  [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
  [QueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO

Step 3: Create the dbo.QueueDatabase table

Step 4: Try to take a backup

USE [MNGDB]
GO

EXEC  [dbo].[DatabaseBackup]
        @Databases = N'MNGDB',
        @Directory = N'G:\Data',
        @BackupType = N'FULL'

Error:

  Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1545
  [Batch Start Line 5] Invalid column name 'SchemaName'. Msg 207, Level
  16, State 1, Procedure dbo.DatabaseBackup, Line 1555 [Batch Start Line
  5] Invalid column name 'SchemaName'. Msg 207, Level 16, State 1,
  Procedure dbo.DatabaseBackup, Line 1561 [Batch Start Line 5] Invalid
  column name 'SchemaName'.

Why

The query fails because the table exists but the schemaname column does not.
If table does not exist, it succeeds.

Testing the part that causes the error & resolving it

  DECLARE @DatabasesInParallel char(1)  = 'N', @SchemaName varchar(255) = 'test',  @ErrorMessage nvarchar(max)
  IF @DatabasesInParallel = 'Y'
  BEGIN

    BEGIN TRY

      SELECT  QueueID
      FROM dbo.[Queue]
      WHERE SchemaName = @SchemaName

    END TRY

    BEGIN CATCH
      IF XACT_STATE() <> 0
      BEGIN
        ROLLBACK TRANSACTION
      END
      SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CHAR(13) + CHAR(10) + ' '
      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    END CATCH

  END

Error message

Msg 207, Level 16, State 1, Line 1892
Invalid column name 'SchemaName'.

Dropping the two previously created tables

drop table dbo.[QueueDatabase];
drop table dbo.[Queue];

and rerunning the statement produces no error:

  Commands completed successfully.


I suspect you are doing this:

select  Outer.ad_type,
GROUP_CONCAT( concat(' duration : ',duration, ' common :',common)
            ORDER BY  sorter ASC SEPARATOR ' , '
            )
    from  ad AS  Outer
    WHERE  IF( EXISTS ( SELECT  1 FROM  ad AS Inner
                    where  Inner.page='1494573224714' ) ,
                           Inner.page='1494573224714',
                           Inner.ad_timeout >= CURDATE()
             )
    group by  Outer.ad_type;

Try it; it should give you an error.

You are really doing

I suspect you are doing this:

select  Outer.ad_type,
GROUP_CONCAT( concat(' duration : ',duration, ' common :',common)
            ORDER BY  sorter ASC SEPARATOR ' , '
            )
    from  ad AS  Outer
    WHERE  IF( EXISTS ( SELECT  1 FROM  ad AS Inner
                    where  Inner.page='1494573224714' ) ,
                           Outer.page='1494573224714',
                           Outer.ad_timeout >= CURDATE()
             )
    group by  Outer.ad_type;

This explains your question??

More

Here might be the first step in getting rid of the UNIONs:

FROM ad AS ad1
JOIN ad AS ad2  ON ad2.page    = ad1.page
               AND ad2.ad_type = ad2.ad_type)  -- left/right/...
WHERE ( ad2.page='1494573224714'
     OR ad1.common )


Unable to find "Local" window while debugging SSIS package I'm trying to debug my package in SSIS. I need to see the value of my variables while debugging, but the problem is that I cannot find the "Local" window. The only window I have is the breakpoint window as you can see below:

What is wrong with my Visual Studio? (I'm using Visual Studio 2013)

You're looking in the correct menu for the Locals (Ctrl+Alt+V, L) window. The issue is that this is a context sensitive menu.

With an SSIS project open, I see a similar menus as you under Debug/Windows

However, if I set a breakpoint and launch the debugger, then I'll get the full suite of debugging windows available under the Debug/Windows menu


How can I check if the keys are enabled on a MyISAM table? I have a script, which populates 4 tables. It goes like this:

Disable the keys on all the tables
Populate the tables
Enabled the keys back.

The problem is that windows decided to reboot the machine at night while it was at the step 3.
I did repair on all the tables, but I have no idea whether the keys are enabled or not.

I do not want to enable it blindly, because it takes hours.

How do I check it?

Just run an EXPLAIN on a SELECT statement that would normally use a key. If the keys are disabled, then the columns possible_keys, key, key_len, ref will return null.


Tool to create a TSQL script from a SQL Server 2008 trace file Are there any tools available to create an executable tsql script from a SQL Server trace file?

Is there an easy manual way?

You could use ClearTrace by Bill Graziano (free) which cleans up trace queries and allows you to sort/filter the trace files and still have a readable tsql from the trace.

Courtesy reference of Brent Ozar's presentation on "Using Perfmon and Profiler" at SQL Bits 2010


This is usually called a GROUPING AND WINDOWS solution.

Basically you set reset points according some rules, then you set up groups by SUMming reset points and finally use aggregated functions to get the desired value.

;WITH reset AS
(
SELECT AuditID,
       CreditID,
       TS,
       Val,
       /* Next CASE returns 1 when:
             a) It is the first row of a partition
             b) Current Val=1 and previous = 0
          Otherwise it returns 0
       */
       CASE WHEN Val=1 AND COALESCE(LAG(Val) OVER (PARTITION BY CreditID ORDER BY CreditID, TS), 0) = 0
            THEN 1 ELSE 0 END AS Rst
FROM   Taco_Val
)
  , [group] AS
    (
    SELECT AuditID,
           CreditID,
           TS,
           Val,
           /* Next SUM generates a cumulative sum of reset points.
              That is a correlative number for each partition
           */
           SUM(Rst) OVER (PARTITION BY CreditID ORDER BY CreditID, TS) AS Grp
    FROM   reset
    )
    SELECT
           /* Next select returns, of every group, AuditID corresponding to
              the row with the minimum TS
           */
           (SELECT AuditID FROM [group] WHERE CreditID = g.CreditID AND TS = MIN(g.TS)) AuditID,
           CreditID,
           MIN(TS) as StartTS,
           1 as Val,
           DATEDIFF(MINUTE, MIN(TS), MAX(TS)) as Duration
    FROM   [group] g
    GROUP BY CreditID, Grp;
GO

AuditID | CreditID | StartTS             | Val | Duration
:------ | :------- | :------------------ | --: | -------:
a1      | 1        | 09/08/2018 19:24:39 |   1 |      100
a9      | 1        | 09/08/2018 21:14:17 |   1 |       46
a4      | 1        | 09/08/2018 22:08:26 |   1 |        8
a3      | 1        | 09/08/2018 22:16:24 |   1 |       60
ay      | 1        | 10/08/2018 15:12:02 |   1 |        8

db<>fiddle here

You'll find a brief explanation of every CTE on the previous fiddle.


Replace "-" with NULL with Derived Column in SSIS I am extracting information from an excel sheet and loading it into a database. Before I load it into the database, I must transform all of the "-" in the excel sheet to NULL values.

I am currently trying to do this with the derived column transformation in a data flow object. It works if the data type is a Unicode string [DT_WSTR], however it will not work with a double-precision float [DT_R8].

The expression that works for the unicode strings is below.

[EquipmentID]  == "-" ? (DT_WSTR,50)NULL(DT_WSTR,50) : [EquipmentID]

The expression that is not working for the double-precision floats is below.

[SideDoorType] == "-" ? (DT_R8)NULL(DT_R8) : [SideDoorType]

First of all, there is no need to cast NULL() function (DT_R8)NULL(DT_R8) you can use it without casting. Only you will need to cast when working with DT_STR. Read more at:

https://docs.microsoft.com/en-us/sql/integration-services/expressions/null-ssis-expression
https://stackoverflow.com/questions/43455146/why-does-this-ssis-conversion-expression-syntax-the-way-it-is

The problem is that you are using a Case statement that returns 2 datatypes

[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]

You are comparing [SideDoorType] column to a string value "-",
if it is equal, you are trying to return a double-precision floats NULL , else you are returning [SideDoorType] column.

you can try using the following expression it might be working

if [SideDoorType] type is DT_WSTR or DT_STR, you have to cast the column in the false part expression

[SideDoorType] == "-" ? NULL(DT_R8) : (DT_R8)[SideDoorType]

if  [SideDoorType] type is DT_R8, you have to cast the column in the comparaison expression

 (DT_WSTR,50,1256)[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]

And if these two expressions are not working, you have to use a string dataType like the first expression you provided, because it looks like your column contains values that cannot be converted to DT_R8, or use a Script Component instead of Derived Column and write a script to clean your data before casting it to float


My database got in to suspect mode, I repaired it using dbcc, but I want to know cause Following is the out put of dbcc checkdb.

  Msg 5282, Level 16, State 2, Line 1
  Table error: Object ID 683941979, index ID 1, partition ID 72057608853127168, alloc unit ID 72057610800988160 (type In-row data), page (30426:11).  The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.

  Msg 5282, Level 16, State 1, Line 1
  Table error: Object ID 683941979, index ID 1, partition ID 72057608853127168, alloc unit ID 72057610800988160 (type In-row data), page (61501:11).  The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
  CHECKDB found 0 allocation errors and 2 consistency errors in table 'rptpb' (object ID 683941979).
  CHECKDB found 0 allocation errors and 2 consistency errors in database 'abc'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (abc).

Generally I have seen when corruption in any index occurs, it does not put the database in suspect mode.

I want to know why this time it has put it in suspect mode.

Secondly, can I find the source of this error?

The database has corruption which is reported by DBCC CHECKDB and it is the only safe way it could repair it which did not happen in this case. Using repair_allow_data_loss will guarantee you some loss of data and you should not be going for that unless you can afford to lose data.

As any DBA, you will have point in recover backup set up so use it to recover the database which will be more consistent. For any further troubleshooting use :

https://support.microsoft.com/en-gb/help/2015748/how-to-troubleshoot-database-consistency-errors-reported-by-dbcc-check

https://sqlpowershell.wordpress.com/2016/07/11/fix-database-consistency-errors-reported-by-dbcc-checkdb/


Parameter Sniffing - Running query in code vs in management studio I just had a problem at work where a stored proc, being called by some C# code, was very slow. When I ran the same stored proc with the same parameters in Management Studio, it was very fast. After a little Googling, I followed the suggestion in this answer and it solved my problem.

I don't like fixing things without learning how they work, so I decided to read up on "parameter sniffing". My understanding is that SQL Server occasionally builds and caches an execution plan for the stored proc, and optimizes that plan based on whatever parameter values it happens to see at the time. So if you pass in different parameters later, you might get bad performance.

But in my case, I ran the stored proc with exactly the same parameter values from Management Studio and C#. Management Studio was fast, C# was slow. I would expect both to use the cached plan, so how could it be fast in one place and slow in another?

I unfortunately can't paste the code, but I can provide any additional details necessary. Just trying to understand this!

But in my case, I ran the stored proc with exactly the same parameter values from Management Studio and C#. Management Studio was fast, C# was slow. I would expect both to use the cached plan, so how could it be fast in one place and slow in another?

Welcome to parameter sniffing   It's not actually quite that simple.

It's possible that the SQL Server instance already had a compiled plan for the query when you ran your test, that was compiled for different parameters.  If this is a test system, and you won't be disrupting others, you could run DBCC FREEPROCCACHE on the instance to make sure you don't have any cached plans before running your test.

There could even be more than one plan depending on whether whitespace, capitalization, SET options, etc were different between the C# batch and the one you ran in SSMS (See Aaron Bertrand's post  Multiple Plans for an "Identical" Query for details).  You can use the queries in Aaron's post to see if you have different plans for the C# code version and the SSMS version.  Then you could compare them (in particular the SET options and the queryhash, both of which can be found by right-clicking on the SELECT operator in the plan and going to "Properties") to see what's different.

For an extremely detailed treatment on this issue, see Slow in the Application, Fast in SSMS? by Erland Sommarskog.  There's also a great resource on this site for identifying and troubleshooting the issue by Erik Darling here on the site: Why is my query suddenly slower than it was yesterday?

I hope that helps!  Let me know if you're still lost after reading those resources, it can all be a little confusing.