SQL Query for searching table 'C' records only if records from table 'A' has specific values I Have Three Tables, TBL-A, TBL-B, TBL-C, TBL-D

TBL A has records for userstatus

RECID    USRID    USERSTAT    USRNAME
1        1        1           JOHN
2        5        1           HARRY
3        6        4           GARY

TBL B has user details

RECID   USRID   FNAME  MNAME  LNAME MOBNO
1        1      JOHN   F      MOSES 1234567
2        2      MARY          MOSES 5421679
3        5      HARRY         KENEDY 1234567
4        6      GARY          MOLE   1234567

TBL C has additional details only for the user having CAT as 1(in Table A)

RECID   USRID   GRPID
1       1       2
2       5       4

TBL D has details of GRP ID

GRPID   GRPNAME GRPDETAIL
 1      group1   FIRSTGROUP
 2      group2   SECOND GROUP
 4      group4   FOURTH GROUP

On basis of user input of mobno(TBL B) all the record having mobno='xxxxx' along with corresponding records of TBL A(userstat), TBL B(FName, Mname, Lname), TBL C(GRPID)-(if existing else output as null or blank), TBL D (GRPNAME,GRPDETAILS)-(if existing else output as null or blank).
should be searched.

PLS Help

Vivek - just use (Left or Right) outer joins. This will make sure that the null values will be shown if the data does not exist within the joined table.


RHEL Postgresql 9.4 and PostGIS I'm a dba new to postgresql. I have Linux RedHat 7 without openshift.

I have to install some DB on 9.4 version with postgis.

My sysadmin has published the following repositories :

RHEL 7 server
RHEL 7 EPEL
yum.postgresql.org for 9.2, 9.3 and 9.4

I managed to install :

Postgres 9.2 from postgresql.org repo with postgis 2.1 for PG9.2
Postgres 9.4 from RH repo

And the issue is when I want to have PostGis for PG9.4, I can't find it anywhere in the repos.

Which repo, which package should I have to get PostGis for PG9.4 ?

Thanks

You will probably want the postgis2_94.x86_64 package from the pgdg94 repository, because that is the official RPM package repository for the PostgreSQL project. It also includes a rich ecosystem of tools around PostgreSQL in addition to all of the currently supported PostgreSQL releases according to the Versioning Policy of the PostgreSQL project.


Create a test enviroment in Oracle 10g I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually indices, constrains and triggers.
Is there a way to authomatically make a copy of the prod-enviroment including all tables, indices, triggers and constrains without the data in the tables?
Thanks in advance for your help.

This can be achieved with the expdp CONTENT flag:

expdp username/password DIRECTORY=directory_object_name \
                        DUMPFILE=dumpfile_name \
                        TABLES=table_names|TABLESPACES=tablespace_names|FULL=y \
                        CONTENT=metadata_only


Live Database Sync. MySQL - SQL Server I have a rare scenario. Is there any possibility to achieve the target? Here is the situation:

I have developed a responsive site for a supermarket. I am using php with foundation framework and the back-end is MySQL. It is for promoting their products. They already having software for billing, which was developed using WPF with SQL Server back-end. Now they need to include online shopping in the web site, and they need to reduce the product from the local software according to the online sale.

I can use replication if both the databases are the same. But in this case, the local DB is SQL Server and the live DB is MySQL. How can I achieve the target of syncing live and local DBs?

You can use Linked Server for SQL Server side. Creata a stored procedure to check online mysql tables data. If product sold decrease the local product count on the Local SQL Server.

Why do you cover this problem on the application side using transaction ?


Unexpected results while using HAVING without GROUP BY Given below is the snapshot of the SQL queries:

MariaDB [university]> select * from department;
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Elec. Eng. | Taylor   |  85000.00 |
| Finance    | Painter  | 120000.00 |
| History    | Painter  |  50000.00 |
| Music      | Packard  |  80000.00 |
| Physics    | Watson   |  70000.00 |
+------------+----------+-----------+
7 rows in set (0.000 sec)

MariaDB [university]> select * from department where budget > (select avg(budget) from department);
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Finance    | Painter  | 120000.00 |
+------------+----------+-----------+
3 rows in set (0.000 sec)

MariaDB [university]> select * from department having budget > avg(budget);
+-----------+----------+----------+
| dept_name | building | budget   |
+-----------+----------+----------+
| Biology   | Watson   | 90000.00 |
+-----------+----------+----------+
1 row in set (0.051 sec)

MariaDB [university]> select * from department having budget < avg(budget);
Empty set (0.000 sec)

I am trying to select all the departments where the budget is greater than the average budget.

The expected result includes three entries but when I use HAVING clause it selects only one of the rows.

Also, in the last SQL statement: select * from department having budget < avg(budget) the result is an empty set which I haven't expected. I am using MariaDB 10.3. Any explanation of why it's happening like that would be appreciated! Thanks!

This is not valid SQL - even though MariaDB and MySQL (until version 5.6) allow it:

having budget > avg(budget)

So, if you get unexpected results, don't worry much. Just do not use invalid SQL syntax ;) If you are not sure what syntax is valid and what not, you can add ONLY_FULL_GROUP_BY to the SQL mode setting and you'll get errors instead of unexpected / inconsistent results.

What is actually happening in the query:

select *
from department
having budget > avg(budget);

Whenever there is an aggregate function (SUM(), COUNT(), AVG()) either in the SELECT list or in HAVING condition, a GROUP BY the whole set is implied. So the query is equivalent to:

select dept_name, building, budget
from department
group by ()
having budget > avg(budget);

This means the whole set has to be collapsed in a single row and only aggregate expressions should be allowed, not columns. So the whole SELECT list is invalid (all 3 columns) and the budget in the HAVING condition. However, MySQL and MariaDB have allowed this since very early versions, for some efficiency optimization (in certain cases where it would not create inconsistent results).

In the specific case (or similar ones), that a dept_name, a building and a budget have to de displayed in the SELECT result, an arbitrary row is chosen and displayed. In more complicated cases, you can even see results from different rows, showing a department A with the budget from department B !

The same (arbitrary) budget value is used in the HAVING filter, giving the weird results you noticed.

A few more details can be found in MySQL's documentation:

5.6 (old behaviour): MySQL Handling of GROUP BY
8.0 (current behaviour): MySQL Handling of GROUP BY

You first query is the right way to do it:

select *
from department
where budget > (select avg(budget) from department);

Another way is to use window functions (available in both MariaDB and latest MySQL):

select *
from
  (
    select *,
           avg(budget) over () as avg_budget
    from department
  ) as a
where budget > avg_budget ;


Best tool/approach to report on table contents over time I've got a table in SQL Server full of data that is a working DB of data collection reports. This table only stores the current state (meaning it doesn't keep historic anything).

There are a few key metrics that I'd like to begin tracking over time from this table. Most notably the table has a user's name, their assignments (one row for each), status values (in progress, scheduled, etc.) and last activity date.

Because the table itself doesn't have any history information, I'm looking for the best way to sample the data on some periodic basis recording the current state values and timestamping it.  Once this is done, I'll be able to ask questions via reporting like "How many reports was Joe working on on tuesday and what were their statuses?"

What would be the best way to do this, short of creating a SQL Server table to house the information, writing scripts to do the collection and a report/query interface for all of this?

My google-fu is usually really strong but the general concepts are colliding way too firmly with proper names of things that are completely unrelated, thus I can't find any good info out there.

Anyone got experience with such a process?  What can you tell me to get me on the right direction?

Edit: I do not currently collect the aggregate data, nor do I currently have a place to store it, though that could be as simple as a SQL Server table. I'm not going to be able to spend as much time as I'd like supporting this reporting capability, so I was hoping for some type of a packaged solution that some admins can maintain with minimal development efforts.

You can create triggers that are fired upon insert/delete/update in the table to write the data you need in a separate history table. You can then later query this table to create your reports. No need to change the application that is now responsible for maintaining the data.


Add job using tsql related When using sp_add_jobstep, are there any limits on @command when I choose @subsystem to 'TSQL', say, maybe I can only set @command to some simple, single tsql like:

'select * from o2o.dbo.sth'

Or could it be complicated like this?:

use hxo2o
go
begin
  declare @last_sync_date_updater nvarchar(2000)
  declare @tb_name nvarchar(200)
  declare tb_name_cur cursor for
    select table_name from dbo.o2o_tables

  open tb_name_cur
  fetch next from tb_name_cur into @tb_name

  while @@fetch_status = 0
  begin
    set @last_sync_date_updater =
        'update dbo.o2o_tables set table_last_sync_date = (select '
        + case
        when @tb_name = 'trace_user_actions' then 'max(operation_date_time)'
        else ' dbo.InlineMax(max(create_time), max(update_time)) ' end
        + 'from dbo.' + @tb_name + ')'

    exec (@last_sync_date_updater)

    fetch next from tb_name_cur into
    @tb_name
  end
close tb_name_cur
deallocate tb_name_cur
end

If not, should I use another @subsystem instead? Or some other methods? I tried to google and stack for the issue but labor in vain.

It can be as complicated as you want, no issues with that. My own recommendation, for better manageability, if the tsql you are planning to use on the job step is too long, better create a stored procedure. This way you just call the sp from the job step and you do all your modifications there. And on the job itself just take care of the scheduling.


How to troubleshoot "Encryption not supported on the client OLE DB provider "SQLNCLI11" for linked server" error We are using LocalDb for integration/unit testing of a .NET Core application. I have been using this solution for months and recently started getting sporadic errors. It seems to happen the first time the tests try to execute a linked server query. The linked server query is hitting another database on the same LocalDb instance on my local machine, i.e. there is not really a remote connection happening.  I am at a loss of how else to troubleshoot.
To set up the linked server connection, we are doing a simple SP call, nothing has changed with this from when it was working consistently.
EXEC sp_addlinkedserver @server='{ServerName}'

Encryption not supported on the client

.
    Test{redacted} Aborted: Exit code is -532462766 (Unhandled exception. System.Data.SqlClient.SqlException (0x80131904): Encryption not supported on the client.
OLE DB provider "SQLNCLI11" for linked server "(localdb)\MSSQLLocalDB" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
OLE DB provider "SQLNCLI11" for linked server "(localdb)\MSSQLLocalDB" returned message "Client unable to establish connection".
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
{redacted}

A similar stack track with an SSL credentials error

Exception Info: System.Data.SqlClient.SqlException (0x80131904): SSL Provider: No credentials are available in the security package

It seems you are using always encrypted for some columns in the database. This is not supported or functional with linked servers (or other means of distributed queries), as documented here . Quote from that page:
"The following features don't work on encrypted columns:
Transactional or merge replication
Distributed queries (linked servers, OPENROWSET(T-SQL), OPENDATASOURCE(T-SQL))"
For Always Encryped (AE), the encryption/decryption of data is done by the client, the API on the client side. APIs that supports AE include ADO.NET, JDBC and ODBC. Linked Server uses OLEDB (the calling server is a client to the remote server), and OLEDB do not have the functionality to encrypt/decrypt for AE.


How to lock entire Postgres table but allow reads Sorry for the noob question, but what is the correct table level lock mode to allow reading from a table but not writing to it?

The scenario that I have is that I want to prevent two tables from being written to while a third table is receiving an update. As soon as that update completes it's okay for the other two tables to unlock and receive additional updates.

If it isn't possible to prevent those tables from being written to while still allowing them to be read from then I could also just lock them entirely so they can't be written to or read from during the lock.

Here is the documentation page, I just don't understand what it's saying: https://www.postgresql.org/docs/current/explicit-locking.html

Don't do that. Explicitly locking tables is almost always the wrong thing to do. If you describe the use case in more detail, I am confident that a better solution can be found.

Locking tables frequently will keep autovacuum from doing its important work on the table, and eventually an anti-wraparound autovacuum job will come along and block you, bringing processing to a standstill.


Can I somehow Improve the relationships between tables in the following sample? I am currently developing a database for a school management system... There are tables for GRADE, SECTION and SHIFT...

GRADE
====
id PRIMARY KEY

SECTION
====
id PRIMARY KEY

SHIFT
====
id PRIMARY KEY

And yet another table which comprises such data called GROUP through foreign keys which additionally has a primary key...

GROUP
====
id PRIMARY KEY
grade_id FOREIGN KEY REFERENCES GRADE(id)
section_id FOREIGN KEY REFERENCES SECTION(id)
shift_id FOREIGN KEY REFERENCES SHIFT(id)

Now where I have an desing stigma and would like advice from a designer with more experience... A table called SUBJECT which comprises a foreign key referencing GRADE and a primary key (A certain grade always has the same subjects)...

SUBJECT
====
id PRIMARY KEY
grade_id FOREIGN KEY REFERENCES GRADE(id)

Now I have yet another table called CLASS referencing SUBJECT, PROFESSOR and GROUP through a foreign key too in addition to its primary key...

CLASS
====
id PRIMARY KEY
subject_id FOREIGN KEY REFERENCES SUBJECT(id)
professor_id FOREIGN KEY REFERENCES PROFESSOR(id)
group_id FOREIGN KEY REFERENCES GROUP(id)

I wonder if it is ok that if drilling down one level on CLASS table references makes possible to access presumably the same GRADE (Through SUBJECT and GROUP) in two different ways. I don't include the code for the tables because I do think it would make this question somewhat long and of course there are more tables. My database is already implemented and works  with the proper application but I would like to see if design could be improved. Thanks in advance. *Note : I know I am using reserved words for table names in my case elaboration, but only for illustrative purposes, also did not include all the tables... Too I have an STUDENT table which as a reference to a GROUP, that is why I included a primary key on such table. I hope now my question is better explained, I am new to this kind of site.

Asssuming that I have understood the problem, the following picture describes (a part of) your database, in which an arrow from A to B corresponds to a foreign key from table Ato table B:

Actually, there is a redundancy in that Grades are referred both by Groups and Subjects and each class must have the same grade if it is accessed through one of the two relations.

I think that in a case like this you have two options:

Keep this schema, and check, maybe with a trigger, that when you insert a class it refers to a course and a subject with the same grade, or
change the schema, removing from Subject the foreign key to Grade, and, if you need to find the grade of a certain subject, joining that table with classes, courses and grades. In this way you simplify the situation, but you could create subjects not tied to specific grades.

I have a light preference for the first solution: in both of them, however, it is important that the objects in tables Grade, Subject, Course and Classes be inserted with great attention to not cause inconsistencies. The best way to do this is to define in the database stored procedures to operate on more than one object and/or perform all the proper checks, and require that the application(s) use only them, without inserting explicitly records.


Naming a second additional column referencing another table column? I'm working with a table in database that references to a column in another table.
Such as:

Product Table
------------
Id
Description
Currencies_Code

Currencies
------------
Id
Code
Description

We have the convention:

"Foreign key fields should be a combination of the name of the referenced table and the name of the referenced fields."

MyColumn: ExternalTableName_ColumnName

Until there, everything is fine. Now, a second column should be added referencing to Currencies table "Code" column for a second scenario where another currency should be used.

My initial idea was using these columns:

Currencies_Code_Internal

Currencies_Code_External

However, the suffix Internal/External breaks the convention.

Which way of naming would be the most appropiate in this case to change the convention?

You may consider that the breaking of the convention is actually indicative of an error or inefficiency in the design:

For example,

I would have a lookup table for this instead of two occurences of the same foreign key field in a table.

Product Table
------------
Id
Description

Product_Currencies Table
------------
Product_Id
Currencies_Id
Condition_Id

Condition Table
-----------------------
Id
Description

Currencies
------------
Id
Code
Description

In effect, the Product_Currencies table formalizes the relationship :
Given this condition, use this currency for this product.

The condition table might contain values like:

Condition
ID     Description
----------------
1      Internal
2      External

The naming convention being broken may be a symptom of a problematic design- many conventions are partially intended to cause issues when violated.

Having two Currencies_Code in one table implies conditional use, which may imply another relation is necessary to describe it.

If you must have an answer to the original question:

It's totally and entirely dependent on who enforces and/or codifies your standard.


How to choose the last recorded added without sorting I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int)

I need to run a simple query:

SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC

The query is run using a Stored Procedure on SQL Server 2008 R2.

The objective is to get the last record for @userid in terms of DTime.

While checking the execution plans I've noticed the sorting (ORDER BY) is almost 80% of the cost to run this query (which is the most "expensive" query on my DB for now) - how could I change the query so I won't use ORDER BY and thus eliminate that heavy cost.

Thanks!

The generic way to access a table in order without a sort is a clustered index, which physically stores the rows in that order. However a table can only have one clustered index. From the documentation:

  PRIMARY KEY constraints create
  clustered indexes automatically if no
  clustered index already exists on the
  table and a nonclustered index is not
  specified when you create the PRIMARY
  KEY constraint.

So if this is your main access pattern for this table, then perhaps making the PK unique nonclustered and (DTime desc, UserID) a composite clustered index is the way to go. But be careful not to optimize for one access pattern at the expense of any others (e.g. is there another part of your app that wants fast lookups by RecordID, you would want to make sure that wasn't suboptimal now).


Moving Innodb database from Server A to server b Instead of using dump, is there a way I can move the data files from one server to another instead?

We are migrating our application to a new server in January, the one concern is the downtime when taking first server offline to do a dump of the database and the time to restore it on new sever.

I'm finding dump and restore is taking a long time due to size of database.

Thanks,
Jim

UPDATE:
Operating system is windows server
Old server is 5.5
New server is 8.0

Tables can be moved in physical form, although it is not instant: https://dev.mysql.com/doc/refman/5.7/en/innodb-table-import.html

A more automated way would to use partial or full backups with tools such as mysql enterprise backup or Percona XtraBackup: https://www.percona.com/software/mysql-database/percona-xtrabackup which will allow you to backup a database quickly without impacting sourde database availability.

In the future, having a replica of your database will be the easiest way to increase your availability during maintenance (switchovers should only take seconds).


Oracle Global Temp table - data is truncated but the table remains in the DB ! is this an expected behavior or a configuration? We are trying to create global temp table using

    Create global Temporary table t1 ( id NUMBER, description VARCHAR2(20) )
ON COMMIT Delete Rows;

and trying to drop it at the end manually  but the temp table created cannot be dropped by after but the session the data is truncated at the end.

Not able to drop it  - is this how oracle works with GTT ?
What are the alternatives ??

As the comments indicate Oracle Global temporary tables work differently than some might expect.  They are intended to be permanent structures.  The settings allow for data to be retained when a session is closed or deleted.

The key reason to use them is that each user can only access data for that user.

Your developers can all use the same table and insert/update/delete without interfering with other users.

There is no need to drop a GTT after using it.


Passing comma separated values to parameters in stored procedure I have a requirement to retrieve data from a table based on the comma separated values sent in the parameters for stored procedure. As of now I have made the code work for one single value not sure how to make it work for multiple values.

Sample table:

CREATE TABLE [dbo].FinalStatus
(
    [ID] [int] Primary key IDENTITY(1,1) NOT NULL,
    [Col1] [varchar](15) NULL,
    [Col2] [varchar](15) NULL,
    [Col3] [varchar](100) NOT NULL,
    [LastUpdatedDate] [datetime] NOT NULL DEFAULT (getdate())
)

Test data:

Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg2')
Insert into FinalStatus (Col1, Col2, Col3) values ('11','C21','Some Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('12','BC21','Some Msg2')

Stored procedure:

CREATE PROCEDURE [dbo].[FindResult]
    (@col1  VARCHAR(15) = NULL,
     @col2 VARCHAR(15) = NULL)
AS
    SET NOCOUNT ON
BEGIN
    DECLARE @c1 VARCHAR(15)
    DECLARE @c2 VARCHAR(15)

    SET @c1 = @col1
    SET @c2 = @col2

    SELECT
        Col2, Col1,
        LastUpdatedDate, Col3
    FROM
        dbo.FinalStatus
    WHERE
        (Col1 = @c1 OR @c1 IS NULL)
        AND (Col2 = @c2 OR @c2 IS NULL)
    ORDER BY
        LastUpdatedDate DESC
END

Execution script for single value (it works till here):

--To get all data
EXEC [dbo].[FindResult]

--passing first parameter alone
EXEC [dbo].[FindResult] @col1 = '10', @col2 = NULL

--passing second parameter alone
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'c21'

Now how to make it return appropriate result even when we pass multiple values to the parameter?

Something like this:

EXEC [dbo].[FindResult] @col1 = '10,12', @col2 = NULL
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'ABC21, c21'

The underlying table would minimum have 100000 records at any given point in time.

Did tried reading "Arrays and Lists in SQL Server 2008" by Erland Sommarskog but its way over my head. So looking for some help in modifying this stored procedure.

There are several ways of doing it. Changing the data model may also be a better option if you can do it.
1. Comma separated parameters
If you want to stick with the comma separated string, you can find many string split functions online using CLR, CTE, XML, ... I am not going to put them all here and I won't benchmark them. But you must know that they all have their own issues. For more information you can look at this post from Aaron Bertrand: Split strings the right way – or the next best way
Sample query (using XML conversion):
DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @delimiter nvarchar(1) = N',';
SELECT v1 = LTRIM(RTRIM(vals.node.value('(./text())[1]', 'nvarchar(4000)')))
FROM (
    SELECT x = CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML).query('.')
) v
CROSS APPLY x.nodes('/root/data') vals(node);

Output:
v1
10
15
13
14

Main query:
The idea is to convert it to a table. It can then be used in a correlated sub query:
DECLARE @c1 nvarchar(100) = N'10,15,13,14';
DECLARE @c2 nvarchar(100) = N'C21, B21';
DECLARE @delimiter nvarchar(1) = N',';

SELECT *
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c1, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT CAST('<root><data>' + REPLACE(@c2, @delimiter, '</data><data>') + '</data></root>' AS XML) AS x
    )t
    CROSS APPLY x.nodes('/root/data') vals(node)
    WHERE  LTRIM(RTRIM(vals.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

Only the WHERE clause has to be updated in your procedure. Parameters remains of varchar type with comma separated values.
Output:
ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

2. XML parameter(s)
If you can change the parameter' types, the XML data type can be used. It can be easily deserialized by the procedure and the query.
This query is pretty similar to the previous one. However it gives a better control over invalid values and special characters since there is no conversion or search and replace.
DECLARE @c xml = N'
    <root>
        <c1>10</c1><c1>15</c1><c1>13</c1><c1>14</c1>
        <c2>C21</c2><c2>B21</c2>
    </root>';
DECLARE @delimiter nvarchar(1) = N',';

SELECT *
FROM FinalStatus f
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c1') val1(node)
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col1
)
OR EXISTS (
    SELECT 1
    FROM (
        SELECT x = @c.query('.')
    ) v
    CROSS APPLY x.nodes('/root/c2') val1(node)
    WHERE  LTRIM(RTRIM(val1.node.value('.[1]', 'nvarchar(4000)'))) = f.Col2
);

The parameter' types have to be changed to xml. I used only one variable with c1 and c2 nodes but 1 variable for each node (c1, c2, ...) and different node names can also be used:
DECLARE @c1 xml = N'<root><data>10</data><data>15</data><data>13</data><data>14</data></root>';
DECLARE @c2 xml = N'<root><data>C21</data><data>B21</data></root>';
DECLARE @c3 xml = N'...';

The correct path and variables must be updated in the nodes part of the CROSS APPLY.
Using .Net, Powershell or other languages, an array or other types can easily be converted to xml and used as a parameter of the procedure.
...
3. User-Defined Table Types
Another option would be to create a Table Value Type that can be used by the Stored Procedure parameters.
Table Type
CREATE TYPE [dbo].[TableTypeCols] AS TABLE
(
    [col] varchar(15)
);

Stored Procedure with Table Type parameter(s)
You then update the Stored Procedure using this newly created type:
CREATE OR ALTER PROCEDURE [dbo].[FindResult]
    @c1 [dbo].[TableTypeCols] READONLY
    , @c2 [dbo].[TableTypeCols] READONLY
AS
    SELECT * -- fs.[...], fs.[...], ...
    FROM [dbo].[FinalStatus] fs
    WHERE
        EXISTS (
            SELECT 1 FROM @c1 c1 WHERE c1.col = fs.Col1
        )
        OR EXISTS (
            SELECT 1 FROM @c2 c2 WHERE c2.col = fs.Col2
        );
GO;

READONLY is mandatory in the parameter declaration.
Stored Procedure call with Table Type parameter(s)
With SQL, you can call it the same way using a table variable:
DECLARE @tc1 [dbo].[TableTypeCols];
DECLARE @tc2 [dbo].[TableTypeCols];

INSERT INTO @tc1(col) VALUES('10'), ('15'), ('13'), ('14');
INSERT INTO @tc2(col) VALUES('C21'), ('B21');

EXEC dbo.FindResult @c1 = @tc1, @c2 = @tc2;

Output:
ID  Col1    Col2    Col3        LastUpdatedDate
1   10      ABC21   Msg1        2016-07-20 09:06:19.380 => match c1
2   10      ABC21   Msg2        2016-07-20 09:06:19.390 => match c1
3   11      C21     Some Msg1   2016-07-20 09:06:19.390 => match c2

Please note that if you are planning on using more than a couple rows for each parameters, you should run some performance test with real data and make sure it works properly on your system.
.Net C# Call
The procedure can the be call from you .Net code
// Create & Fill Parameter 1
DataTable dt1 = new DataTable();
dt1.Columns.Add("col", typeof (string));
DataRow row = dt1.NewRow();
row["col"] = ("10");
dt1.Rows.Add(row);
/*
    ... add more row ...
*/

// Create & Fill Parameter 2
DataTable dt2 = new DataTable();
dt2.Columns.Add("col", typeof (string));
DataRow row = dt2.NewRow();
row["col"] = ("C21");
dt2.Rows.Add(row);
/*
    ... add more row ...
*/

// Output dataset
DataSet ds = new DataSet("SQLDatabase");

using (SqlConnection conn = new SqlConnection(...))
{
    // Stored Procedure with table parameters
    SqlCommand sqlComm = new SqlCommand("dbo.FindResult", conn);
    sqlComm.CommandType = CommandType.StoredProcedure;

    // Parameter 1
    SqlParameter param = new SqlParameter("@c1", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt1
    };
    sqlComm.Parameters.Add(param);

    // Parameter 2
    SqlParameter param = new SqlParameter("@c2", SqlDbType.Structured)
    {
        TypeName = "dbo.TableTypeCols",
        Value = dt2
    };
    sqlComm.Parameters.Add(param);

    // Call Stored Procedure
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = sqlComm;
    da.Fill(ds);
}

SqlDbType.Structured is mandatory.
Please note that I am not a .Net expert and there are other or better ways to write and use this code.
4. Json Type Parameters (Since SQL Server 2016)
With minimal changes, you can declare a NVARCHAR(MAX) parameters with json string.
json parameters
DECLARE @C1 NVARCHAR(MAX);
SET @jsonC1 =
N'[
    { "col" : "10"}, { "col" : "15"}, { "col" : "13"}, { "col" : "14"}
]';
DECLARE @C2 NVARCHAR(MAX);
SET @jsonC2 =
N'[
    { "col" : "C21"}, { "col" : "B21"}
]';

Query
SELECT *
FROM dbo.FinalStatus f
WHERE
EXISTS (
    SELECT 1 FROM OPENJSON(@C1) WITH(col nvarchar(15)) AS c1 WHERE c1.col = f.Col1
)
OR
EXISTS (
    SELECT 1 FROM OPENJSON(@C2) WITH(col nvarchar(15)) AS c2 WHERE c2.col = f.Col2
);

Please note that OPENJSON requires SQL Server 2016.


Online Hostel Management System ER Diagram I have created an ER diagram for an online hostel management system.  Please advise me if this diagram makes sense or needs improvement.

You should probably have a table "StaffHostel" which has StaffID/HostelID FKs. It should also have another field called "IsManager", which can be True/False. That is a better way to represent the Staff - Hostel relationship. Table would look like:

HostelID (FK)
StaffID (FK)
IsManager (True/False)

If this is for a Hostel, note that people often don't book Rooms, but book beds! There can be many beds in a single room (usually up to 8) with customers booking a bed in a particular room. That will complicate your design significantly.

You may want to put RoomPrice into the Room table and have a "DefaultRoomTypePrice" in the RoomType table, but that is getting into semantics. I just know from experience sometimes room can be the same type but different prices for certain reasons.

It is unclear how your Catalog table is linked to the Hostel, explain a bit more? What is a Catalog?

Otherwise looks pretty good, the only thing I can imagine you might want to do differently is to hand off the payment to another provider, storing Credit Card information is a very dangerous thing to do, leave it to the big boys who implement stringent security practices.


Does the Postgres 9.4.9 update fix MultiXactId "does no longer exist" in addition to "has not been created yet" errors? Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine):

Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew Gierth, Álvaro Herrera)
The usual symptom of this bug is errors like "MultiXactId NNN has not been created yet -- apparent wraparound".

I am currently chasing errors in PG 9.4.5 which read:
2019-10-19 15:37:17 EDT [13024]: [1-1] ERROR:  MultiXactId 1074790449 does no longer exist -- apparent wraparound

Does the above fix in PG 9.4.9 address the error I am getting?

I upgraded to PG 9.6.8. This particular environment is a legacy application that runs on an "appliance" style deployment so I'm forced into only updating to certain versions. 9.6.8 was the smallest increment I could take.

The initially appeared to have solved the MultiXactId xxxx does no longer exist problem with some simple queries. Subsequent queries to create indexes failed with the original error.


Updating an FTS indexed column returns "Invalid InnoDB FTS Doc ID" Environment:

Ubuntu 12.04 (and 13.04)
MySQL 5.6.11

I have a table which has a full text index on it (real table has much more columns and rows):

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
  FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id INT NOT NULL ,
  title VARCHAR(200),
  body TEXT,
  UNIQUE KEY (FTS_DOC_ID)
) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx on articles (title);

INSERT INTO articles(id,title,body)
  VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...');

MySQL documentation suggests to create the FTS_DOC_ID (with the right syntax) to prevent a full table rebuild.

So far all is good and I can query using the MATCH...AGAINST to use the FTS index. However when I need to update an indexed column:

UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=9;

I get a:

Error code 182, SQL state HY000: Invalid InnoDB FTS Doc ID

If I manually take care of this column like this:

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

Then the update is done. But this is not acceptable because I have several processes in parallel that update this table (though all different rows) and the risk is to get the same ftsid in different processes.
Note that updating the body column which is not indexed by the FTS index doesn't have this behaviour. I.e.:

UPDATE articles set body = 'Info: DBMS stands for DataBase ...' WHERE id=9;

successfully update the database.

Is it the expected behaviour? Or is it a bug?

I found a bug reported in the MySQL buglist about the opposite case (cannot update a non-fts indexed column but can on an fts indexed one) but not this case.

The first thing I noticed is that the id column is not indexed. For all intents and purposes, your acting PRIMARY KEY is FTS_DOC_ID since it is the only unique key present without a PRIMARY KEY definition.

Since id is not indexed, I could see mysqld wanting to do a full table scan to fulfill the query. You should want to create a UNIQUE KEY on id as well if you intend on having two different unique keys and having two different references to one row.

Note that FTS_DOC_ID has the auto_increment attribute. There is no need to increment it manually, like you did with

UPDATE articles a1, (SELECT MAX(FTS_DOC_ID)+1 AS ftsid FROM articles) a2
set title = 'New MySQL Tutorial', FTS_DOC_ID=ftsid  WHERE id=9;

In fact, by doing this, you are actually taking the FTS_DOC_ID and changing it on id.

Looking back at your first query

UPDATE articles set title = 'New MySQL Tutorial'  WHERE id=9;

Why would this be a problem (and essentially a bug) ? Again, this goes back to id not being indexed.

When a column is not indexed, the Clustered Index of the table (where the PRIMARY KEY resides) is unaware of the presence of id. How is this so?

According to the Book

Chapter 12 Pages 260,261 (Subheading Secondary Indexes) says the following:

  What about other indexes? How are they structured, and what is their
  relationship with the data found in the clustered index?

  These additional indexes are know as "secondary indexes," and you can
  create as many of them as you need for an InnoDB table. Because our
  sample table schema definition requests an index on the last_name
  column, InnoDB creates one secondary index on this value.Each entry in
  the secondary index contains the primary key value from the clustered
  index,thereby facilitating linkage back to the data.

Your First Query

Since id is not indexed your first update failed. MySQL (eh Oracle) should have caught this problem. So, it is a bug.

Your Second Query

The update works because you accessed the Clustered Index indirectly by trying to manually update the FTS_DOC_ID column.

Your Third Query

It worked because you accessed no indexes at all, neither on id nor on body. In that instance, a full table scan would easily fulfill this request.

Epilogue

If I were you, I would report this as a bug ASAP because MySQL 5.6's FULLTEXT indexing for InnoDB is what people want these days and query plans should be accommodating it with WHERE clauses whether accessing with the Clustered index or not.


Interpreting sp_BlitzCache: # Executions but since when? I'm getting started with sp_BlitzCache  (which is amazing!) and trying to understand the results better. When does the # Executions count from? e.g. if it says 343 executions is that today, since server start, or what? I'm running it like this:
sp_blitzCache @MinutesBack = 1440, @sortOrder = 'query hash'

But the # Executions appears not to change based on the @MinutesBack parameter so I'm guessing it's since some fixed time period?
I'm using SQL 2019.

Scroll across to the right side of the results, and there's a "Created At" column. That column shows when the plan first went into the cache. In most cases, SQL Server tracks the execution count since that time - but there can be exceptions in some cases involving recompiles of a query.
The source is the execution_count column in sys.dm_exec_query_stats.


Can I lookup my Primary Key in SQL Server from Excel Spreadsheet? Disclaimer:  I'm a database novice teaching myself for a research project.

Background:  I am querying an electronic medical record (EMR) system for information pertinent to my project and I do not want to store medical record numbers (just say "no" HIPAA violations!). The database is designed with Visit_ID as a Primary Key and four fields which result in a unique combination: admission date & time, length of stay, admitting physician, and room number. All other information will be based on the Visit_ID. For example, the "Medications" table starts with Visit_ID as a Foreign Key and all other information such as start date, stop date, etc.

Problem:  The EMR system obviously does not know my Visit_ID. Therefore, all of my queries will have the four fields which are defined by Visit_ID in my database.

Question:  Can I resolve or lookup the Visit_ID within the database from the four columns in my Excel sheet?

Example:  I query the EMR for the medications the patients had in June. My query returns an Excel spreadsheet with my four fields (Admission Date & Time, Admitting Physician, Length of Stay, and Room) and all the necessary information about the medications. Can I import data from that Excel spreadsheet into my "Medications" table but have SQL Server resolve Visit_ID instead of keeping those four fields?

Yes, Excel can query data from a database. On the Data tab there are tools to create and maintain connections. IIRC it uses ODBC so all major database products can be referenced.


How to copy blob (binary data) from one DB to DB using a script? We are trying to copy a blob (binary data) from one Database to another Database using a simple script. This is the steps we do.

Select the value using MSSQL Studio Client.
Copy it into the clipboard.
Paste it into anINSERT script using notepad.
Apply it into the destination database.

We notice that the value is now an ASCII that starts with 0x255.... which seems to be the hexadecimal representation of the binary data. Is there a way we can convert it back to binary during the insert?

Is there a better way to copy blob data using scripts? Assume, we don't have direct connection to destination Database.

I would use the "Generate Scripts" option in SQL Server Management Studio.

In Object Explorer - highlight the database hosting the source
table.
Right-click menu - Tasks | Generate Scripts
Select specific table object - click Next page
Click Advanced button
Under General Options
    - Change 'Type of data to script' to "Data Only"
Save to file - change folder and filename as required.

I tried a simple test with a table storing a varbinary field.  The output from the Scripting process looked ok:

USE [TestDB]
GO
/****** Object:  Table [dbo].[tjc_blob]    Script Date: 07/04/2017 14:46:23 ******/
INSERT [dbo].[tjc_blob] ([id], [datablob])
VALUES (1, 0x6162636465666768696A6B6C6D6E6F707172737475767778797A)


Restrictions on creating new SQL tables? I'm working on a huge database consist of more than a 1000 table, the database is increasing dramatically and I need to put some restrictions on creating new tables. thinking out loudly, a prompt message could show up when the developer create new table or creating a rule for adding the purpose behind creating the table? Also I need to find a way that temp tables or testing tables will be scheduled somewhere for a clean up process later on.
Please don't hesitate to share any idea could be useful! :)

If this is a production database, you should remove developer's access rights to create tables. This can be achieved via the REVOKE command. You may have further work to do if the developers are adminstrators.

Developers should then create new tables in a developement database and these are migrated to production via the DBA team following a code review and test cycle.

Alternatively, you could create a DDL trigger that could stop users creating tables via a ROLLBACK and create a warning message for the developers as you suggest.

Finally, to clean up test tables, you will need some method to identify them (table name pattern, owner etc). You can then automate a script to drop such tables.


SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' Whats wrong with this statement?
why it gives below error message

Error Message:

  SELECT failed because the following SET options have incorrect
  settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for
  use with indexed views and/or indexes on computed columns and/or
  filtered indexes and/or query notifications and/or XML data type
  methods and/or spatial index operations.

Statement

  SELECT  ISNULL(STUFF((SELECT ', ' + u.TITLE + ' ' + u.FirstNAME  + ' ' + LastNAME   [text()]
                                         FROM table1 c
                                         INNER JOIN table2 u ON c.user = u.user
                                         AND c.Task = @task
                                         AND c.Type = 'Assign'
                                         FOR XML PATH(''), TYPE)
                                        .value('.','NVARCHAR(MAX)'),1,2,' ')
                                 ,'') UNAMESLIST

As documented in SET QUOTED_IDENTIFIER (Transact-SQL)

  SET QUOTED_IDENTIFIER must be ON when you invoke XML data type
  methods.

A simple test

set quoted_identifier off

DECLARE @xmlRecords XML
SET     @xmlRecords = '<records><record orderId="1" refCode="1234"></record></records>'

SELECT  records.record.value('(@orderId)[1]', 'INT') AS orderId
FROM    @xmlRecords.nodes('/records/record') records(record)

  Msg 1934, Level 16, State 1, Line 8 SELECT failed because the
  following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or query notifications and/or xml data
  type methods.

You are using FOR XML PATH

SET QUOTED_IDENTIFIER off

DECLARE @T TABLE (id VARCHAR(5),col1 XML)

INSERT INTO @t (id,col1) VALUES ('1','<node1>one</node1>')

SELECT ISNULL(STUFF((
                SELECT ', ' + id
                FROM @t
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 2, ' '), '') UNAMESLIST

  Msg 1934, Level 16, State 1, Line 8 SELECT failed because the
  following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
  Verify that SET options are correct for use with indexed views and/or
  indexes on computed columns and/or query notifications and/or xml data
  type methods.


PostgreSQL failsafe start after disk crash We use PostgreSQL to store logs.

A disk crashed and some files in the file system were corrupt.

I read this: https://wiki.postgresql.org/wiki/Corruption

But this page does not solve my needs.

I don't care if some transactions got lost.

I care for availability.

Above page suggests "Consider contacting a professional support service provider".

That's not an option.

I see two ways to solve this:

Solution 1: If PostgreSQL does not start, then rename the data directory. Init postgres, create required DB and tables.

Solution 2: Use a tool which can start a corrupted DB. It does not matter if some transactions got lost.

Is there such a tool which can start a corrupted DB?

Given my current context (DB exists to be store logs), what other solutions could be used to ensure the DB is up again soon after a corruption?

Among the options you give, the first is the only feasible one.  But neither one of them is very good.

The only good solutions is to prevent it from happening.  If your disks randomly eat your data, maybe next time it will be the postgres binaries that get eaten, or the kernel itself, or the init script you wrote to re-init the database when it gets corrupted, or the dump files which contains the schema you want to use to reload.

If your disks are honest and true but your database got corrupted anyway because you were running with fsync=off, then don't do that.  Run with synchronous_commit=off instead, if you must.  That risks losing some transactions, but doesn't corrupt your database.


How could DBAs be more 'programmer friendly'? The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing about the divide between DBAs and programmers in some workplaces.

What could DBAs do differently to work better with programmers on issues like this?

Should we:

Study the tools and languages our programmers are using to understand their difficulties they face, particularly when working with well designed databases?
Encourage programmers to be better educated about databases and the advantages of having business logic at the database level?
Change the way we define interfaces to our data - such as by using more programmer friendly transactional APIs (eg for issues such as backwards compatibility)?

I've been a MySQL DBA for the past 6.5 years. I've also spent some 16 years as a developer and have interacted with many DBAs. Many of them pragmatic. Some of them obnoxious. A few have no idea what it means to be a DBA.

I have come to this conclusion:

Technically speaking, DBAs who have one or more of the following qualities are the best to work with:

Spent years as developers themselves
Have a grasp of database theory
Have a good understanding of how the RDBMS works internally
Have superior knowledge of the operating system

Very disciplined, knowledgeable DBAs have a lot to share and offer. They may see database performance from a perspective not really considered by Developers.  Developers know what they want from the database. DBAs know how to be "polite" to the database.

As far as personalities go, there will always be conflicts, pettiness, and maybe even envy. One thing is for certain: In no particular order, DBAs and Developers are like husbands and wives (I've been happily married for 16 years with on-going projects [4 children]).

Regardless who is viewed as the husband and who is viewed as the wife, these principles apply:

one must consult the other
one must value the perspective of the other
one must make decisions for the good of both parties
one must support, and not sabatoge, the decision made
one must not denigrade the other if decisions result in bad consequences
one must rejoice in the contribution of both parties to the success of decisions
one must consult a higher authority (HA) if a decision cannot be mutually agreed upon

These seven(7) principles apply just as much in the workplace, especially in the IT realm.

By communicating every step of way, all should :

layout their expectations
engender respect for the other party's ability to do their part based on past performance
have trust and confidence that the other party can complete their assignment
live up to our own expectations
acquiese under the guidance of the HA (see principle #7)

There is no room for micromanagement in this. DBAs SHOULD NOT TELL Developers how to think like DBAs. Developers SHOULD NOT TELL DBAs how to be Developers. Final decisions on database performance and usage must rest with DBAs. Final decisions on application needs must rest with Developers. This symbiosis must be maintained always.

FINAL THOUGHTS

Principle #7 requires active participation and oversight by the HIGHER AUTHORITY (the HA), i.e., project manager, team leader, lead developer. Your HA better know how both parties work individually and how both parties should work together. If the HA does not establish ground rules for both parties, or if the HA fails to guide the parties individually and together, projects will always come to a halt at some point and endanger the very existence (employment) of the Developer, the DBA, or even the HA.


Value of an UNSET NEW.Column in Before Update Trigger SETUP

A very simple table with 2 columns.  (UserName, Password)

In this table I have a Before Update trigger:

BEGIN
    SET NEW.Password = SHA2(NEW.Password,256);
END

Problem

When ever I update the username AND password, all is great.
BUT when I try to update just the username:

UPDATE `UserCreds` SET `UserName`= 'BOB'
WHERE `UserID`= 156

the password Also changes.

Question

How do I get the password field to update ONLY when the update statement contains

SET `Password` = 'supersafepassword'

What I tried

I tried a few variations of the following but everything still seems to evaluate to true, and the password still changes.

BEGIN
  IF NEW.Password IS NOT NULL THEN
    SET NEW.Password = SHA2(NEW.Password,256);
  END IF;
END

So i guess the real question is, What does the value of NEW.Password equal when the Update statement doesn't set it equal to anything?

In Short

If I don't Set the password in my update statement i don't want my password to change (as in changing username only)... but if I DO set it, I want it to be encrypted before saving.

Maybe mysql documentation  is not so clear, but NEW is in fact refers to pseudorecord which contains new values (let's ignore access right for simplicity).  It's probably more accurate to say that it gives you access to the row as it will be stored in the table , whereas OLD gives access to the row as it was right before UPDATE/DELETE executed.
Keeping that in mind, comparision in trigger body OLD.PASSWORD <> NEW.PASSWORD  will be true (assuming "password" field is not null, otherwise you need to use IFNULL or something similar) only if PASSWORD field was actually changed by UPDATE statement.


Generate Variables Through T-SQL Is it possible to generate variables solely through code?  If you have a base variable name, and add an int to the end of the name, could you generate variables?  Something similar to the below?

In the end I would want this syntax to generate 4 variables

@newlygeneratedvariable1
@newlygeneratedvariable2
@newlygeneratedvariable3
@newlygeneratedvariable4

Declare @i int, @totalvariables int

Set @totalvariables = '4'

for @i <= 4
Set @i = 1

    Declare @newlygeneratedvariable + @i
    Print

    @i = @i + 1

Next @i

You can do something like this:

DECLARE @i INT = 1
DECLARE @totalvars INT = 5
DECLARE @newvar VARCHAR(50) = ''

WHILE @i < @totalvars

BEGIN

SET @newvar = 'DECLARE @newvar' + CONVERT(VARCHAR(50), @i) + ' = ?;'

SET @i += 1

RAISERROR (@newvar, 0, 1) WITH NOWAIT;

END

So we're all clear, this won't create variables for you unless it's part of a larger block of dynamic SQL. You're probably better off using a temp table. But on the off chance you're using more dynamic SQL, you could use a loop like this to append the variable declarations just fine.


mysqldump with --no-tablespaces on InnoDB with index? Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not exported?

"Tablespaces" are for clumping tables into a single .ibd.  The mysqldump options relating to that control whether to output the DDL statements such as CREATE TABLESPACE.
mysqldump does not dump the contents of indexes, regardless of Engine.  It does dump the definitions of the indexes.  When reloading, the indexes will be recreated.  Yes, MyISAM has a separate file for indexes while InnoDB includes the indexes in the same "tablespace" file; but the locations of indexes is an implementation detail that does not matter when dumping and reloading tables.
If you have not explicitly created any "tablespaces", ignore options relating to them.  (Very few people use tablespaces.)


Read Replicas with multiple instances of MySQL running on different ports I have a specific need to create a single linux server which can host 4 MySQL instances with read replication. Here are a few questions I cannot seem to find answers to:

Can you setup read replication through MySQL similar to Amazon RDS, or is
Amazon using a propriety application to handle this read/write
replication? If not, are there standalone packages for this kind of replication?
Can you set individual settings for each MySQL installation? For instance I want a memory limit for MySQL running on port 64506 of 2GB, but MySQL installation on port 3306 should have a 16GB memory limit?

Yes, Amazon uses standard replication for its instances. Not only it is documented here, but also is proven with the fact that you can replicate from and to MySQL instances outside of RDS. No need for any extra packages/software, a standard MySQL server can be a master and/or a slave with the appropriate configuration and preparation.
Yes, if you run several MySQL instances on the same node (assuming you have changed its port, data directory, pid and socket), you can configure them with different buffer sizes, max_connections, etc. However, make sure to understand that there is no way to hard limit the maximum memory or CPU usage on MySQL configuration - if you want real isolation, you have to go to virtualization or other operating system limits (this is what Amazon actually does).

I recommend you to have a look at MySQL Sandbox, which makes easier to setup MySQL and replication among them on the same machine.


Date model for commenting system stored in a key-value NoSQL DB? I'm trying to leverage Cassandra's strong support for time series data to build a commenting system.

So far I'm thinking that each row would be a particular topic, and each comment would be a new column, stored in event_time descending order such that I could get the latest replies.

CREATE TABLE topic (
    topic_id text,
    user_id text,
    event_time timestamp,
    data text,
    PRIMARY KEY (topic_id,event_time),
) WITH CLUSTERING ORDER BY (event_time DESC);

However, if I want to get the last 10 comments made by 'John' for example, this seems to be difficult with the current model.

Could anyone give any advice on how I could retrieve 'John's comments efficiently, and if I would have to change the data model?

In Cassandra you need to take a query-based modeling approach.  In your case, I would create a similarly-structured query table like this:

CREATE TABLE topicbyuser (
    topic_id text,
    user_id text,
    event_time timestamp,
    data text,
    PRIMARY KEY (user_id,event_time),
) WITH CLUSTERING ORDER BY (event_time DESC);

And then, if you wanted to query for the last 10 comments made by John, this would work:

SELECT * FROM topicbyuser WHERE user_id='John' LIMIT 10;

You could also add an ORDER BY, but since you have that in your column family definition, you shouldn't need it.  Also, you should note that this query table does not replace the original topic table.  It works in conjunction with it.


Giving file system access to the SQLSERVERAGENT virtual account I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in the control panel -> user accounts area.

How do I give this service account access to the file system? Specifically, on Windows 7.

I've read several approaches and none seem to be an option for me. I tried a PowerShell approach, but the AD commands were not valid commands. I even downloaded and installed the required windows patch for those commands. I've also read that I should be able to do this via the SQL Server Configuration Manager or the Management Studio. I can't seem to figure out where to adjust these permissions, though.

The SQL Server Agent process fails to start, with the following error message:

Login failed for user 'NT SERVICE\SQLSERVERAGENT'.
Reason: Failed to open the explicitly specified database 'msdb'.
[CLIENT: <local machine>]

Based on my Googling, it has to do with this account's permissions.

I'm not sure if the database is corrupt, but it says the database is in a Normal status. The SQL Server Agent login is part of the sysadmin and public groups.

To specifically answer your question, here's how you give disk access rights to the built in SQL Server Agent account. But read on as I answer what I believe to be the real issue:
1.> Right-click your drive, select properties, select security, click the Add button and enter the SQLSERVERAGENT account(make sure your domain isn't selected in the From this location text box, but rather your computer name):

2.> Click the Check Names button to validate that the account is valid:

3.>  Now, add the file permission you need to the SQLSERVERAGENT account. For trouble-shooting purposes, you may want to give Full control and then scale it back later as needed:

That being said, you probably just need to use SQL Server Configuration Manger to re-add the SQL Agent user--according to the comments I saw about msdb and logins. Configuration Manager makes more changes to SQL Server than using the Windows Services applet--so Configuration Manager should always be used to change ANY SQL Service.
This will fix the issue if someone may have changed the account within Windows Services causing the service to fail on startup. You need to reset it within Configuration Manager. Doing so allows Configuration Manager to add to SQL Server the much needed permissions to manage the MSDB database for the Local Service account (NT SERVICE\SQLSERVERAGENT) whereas changing accounts within the Windows services applet does not.
Caveat: Versions SQL Server Express above 2000 do not include a SQL Agent. Certain aspects of it may appear to be there, but its unusable in the Express version of the product.
To begin, open SQL Server Configuration Manager and double-click the SQL Server Agent service in the SQL Server Services. Select the Built-in account radio button and choose Local Service, and click the Apply button. Important : if you already see that this account is selected chose another account and click the Apply Button. Then, change it back to Local Service and click the Apply button to allow Configuration Manager to add the correct MSDB permissions for the SQL Agent service to start. Now, restart SQL Server Agent to reflect this new setting.


When to add indexes to #temp tables? When testing stored procedures in SSMS, it sometimes says there is a missing index on
#someTempTable____________________000000000000005B] (someField) etc etc

When I do add them like this to the sp:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[#someTable] ([someField])
GO

I never seem to see a speed improvement.  So, I often do not add such indexes.  When should I be adding such indexes?

Add an index if you'll use your temp table and its index twice or more during the query run.

Or to maintain usual index tasks, like uniqueness

If your data loaded into temp table are already sorted, the to create temp table with the same clustered index as sort of data

BUT

taking into account sql server's feature of temp tables reuse- if you decide to create an index on temp table - try to do it in CREATE TABLE statement. If you'll add an index explicitly after table creation - it will prevent sql server to reuse that table next time


Count events on "user based" sliding window So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be clear in the code examples.

I could fix on the min(sale_date) of my data and calculate the windows form there, counting each event:
http://sqlfiddle.com/#!4/7cd69/6

but that would not be a sliding window. However if I fix on the max(sale_date), as this date increases the windows are brought along:
http://sqlfiddle.com/#!4/7cd69/7

In these examples, I am using the following mechanism to obtain the time windows:

SELECT DISTINCT
ma - (level - mod(level, 7)) - 7 + 1 dt_lim_bot,
ma - (level - mod(level, 7)) dt_lim_up,
level - mod(level, 7) slc_id
FROM (select max(dt) ma, min(dt) mi from sales)
CONNECT BY LEVEL <= ma - mi order by 1

(as mentioned, the 7s here could be replaced by any number of days)

Mind the detail: select max(dt) ma, min(dt) mi from sales. Max and min are found regardless if these dates are relevantes for all of my users, which wouldn't be true in a example like: http://sqlfiddle.com/#!4/74f1b/4

I know it will probably not change the result of my initial examples and the problem is probably solved already, though I was curious.

If I try to use select max(dt) ma, min(dt) mi, usr from sales group by usr instead (http://sqlfiddle.com/#!4/74f1b/3), it breaks (infinite loop?). So I figured I should change my connect by too, but couldn't come up with a quick solution.

What would be the correct way to generate these time slices, per user basis?

I am working on Oracle 11r2

Here is another option.  It still doesn't use your CONNECT BY (probably a good thing), but does have fewer table scans.  For the SQL Fiddle I used Phil's modified data.

SELECT usr, usrmax - (slice-1)*7 WindowEnd, count(*) FROM
(
  SELECT usr, max(dt) OVER (PARTITION BY Usr) usrmax
     , TRUNC((max(dt) OVER (PARTITION BY Usr) - dt)/7) + 1 Slice
  FROM Sales s
) a
GROUP BY usr, usrmax, slice
ORDER BY usr, WindowEnd;

+1 For an excellent and interesting question.


PGAdmin III Server Status Error: Could not read directory "pg_log" No such file or directory Running PostgreSQL 9.3 on Ubuntu 14.04 and pgAdmin III 1.18.1 on Windows 7.

I tried Tools->Server Status and I get an error:

  "Could not read directory "pg_log" No such file or directory"

I don't see any answers online. The server is mostly setup with default settings (for logging). The user I'm connecting with through pgAdmin is a superuser level account.

Purpose of use: monitoring what will become a live system.

Your client (Windows) is running on a different machine than your server (Ubuntu).

You cannot read the remote file system. Inspecting the logs only works if your client is on the same machine (or has access to the remote file system by way of some advanced trickery.)

To suppress the error message remove the log file from the display.
In the Server Status window press
Ctrl-Alt-F
Or uncheck View -> Logfile.


How to handle a table schema with a new relationship subtype in an interviews scenario? I have a table of "interviews" and "interview reviews" for both the interviewer and the interviewee. When the project was started there was only the need for one type of review, which is shown below

But now we have added a new type of interview which would require different types of reviews based in the interview type. I have added a many to many (M:N) relationship to store the interview type, but I am stumped as to the best way to go about adding multiple review types...

One solution I thought of was to add another table for reviews with the many to one (M:1) relationship which would contain all the data for the new review type. This would mean that the application would have to conditionally look for the review based on the type of interview, and it would lead to blank columns for all the unused review types...

How would you go about structuring this?

My gut says that the schema is rapidly getting too complex.

Let's back up and think about "Entities" and "Relationships".

There are 2 main Entities:  People and Reviews.  See if it is not too clumsy to put Interviewers and Interviewees into the same People table.  If the columns are too different, then make 2 tables.  Ditto for Reviews.

Then provide the minimum number of Relationship tables -- but only when there are many:many relationships.  (Many:1 does not need an extra table.)  The "Interview" is likely to be a Relationship table, but have a few extra columns for when/where/how-long/type/etc.

Do not "over normalize".  For example, an Interview Type is probably one of a few keywords.  These could be implemented in an ENUM without needing an extra table.


Compare sysdate month with a column data im application developer and we are using Oracle database for this project. I never used oracle before so i want some help to build a simple query. I have a CARD table with these columns EXPIREMONTH and EXPIREDAY. I would like to return the CARDS that will expire in the next month from the SYSDATE

  select * from CARDS where EXPIREMONTH == Sysdate('MM' + 1) // something like this

Thanks.

You have not specified the data type of ExpireMonth.  If it is a date or timestamp you can try this

select * from CARDS where EXTRACT(MONTH FROM EXPIREMONTH)  + 1 = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) + 1


High amount of disk writes during upsert with no changes on table We are running some nightly data sync jobs which move data from an external database into postgres using an upsert statement. The data is updated in batches and using parallel workers (10 currently).
The problem we are having is that even when no data is changed (e.g. when running two consecutive syncs with no changes in between), there is a high amount of disk io (100% disk usage, 200 MB/s is writes). Our monitoring reports that there are no updated rows and only reads are performed on the table itself.
The expected behavior would be a high amount of reads but close to zero write operations on the disk. We can reproduce this consistently, even when running two syncs on a previously empty table.
What can be the reason for this high amount of disk write operations?

Some more details on our setup:

Postgres 12.5 running on Kubernetes with 25 GB of memory
1TB disk with 3000 IOPS gp3 volumes (AWS)
Table has around 570 millions rows (120 GB)
Primary key index is 43 GB

Upsert-Statements used:
INSERT INTO ... (...)
VALUES (...)
ON CONFLICT (primayKeys...)
DO UPDATE ...
WHERE (tableColumns...) IS DISTINCT FROM (excludedColumns...)

The DO UPDATE locks the "victim" tuple before it evaluates the WHERE part.  This dirties the page containing the tuple, even if the WHERE ends up short-circuiting the UPDATE.


Updating every cell of of a table? I have a table with m columns and n rows. The ETL tools I
was using wrote an empty ('') to every cell without a value and I would like
them to be NULL instead.  This got me thinking if it would be
possible to UPDATE every cell with value '' with a NULL, in a single statement?
In my table every column happens to be VARCHAR (or CHARACTER VARYING).

    Col1|Col2|...|Coln          Col1|Col2|...|Coln
Row1   a   ''  ''    1      Row1   a NULL NULL   1
Row2   c   ''   e   ''      Row2   c NULL   e NULL
...                     -->  ...
Rown  aa    f  ''  123      Rown  aa    f NULL 123

Is it possible with SQL?  How about PL/pgSQL?  I did some
googling around but couldn't find anything really relevant.

You can use either n UPDATE statements, one for each column:

UPDATE table SET Col1 = NULL WHERE Col1 = '';
UPDATE table SET Col2 = NULL WHERE Col2 = '';
---
UPDATE table SET ColN = NULL WHERE ColN = '';

or one with CASE:

UPDATE table
SET Col1 = CASE WHEN Col1 = '' THEN NULL ELSE Col1 END,
    Col2 = CASE WHEN Col2 = '' THEN NULL ELSE Col2 END,
    ---
    ColN = CASE WHEN ColN = '' THEN NULL ELSE ColN END
WHERE
    Col1 = '' OR Col2 = '' OR ... OR ColN = '' ;      --- optional WHERE clause

The WHERE clause is not needed. But in case you run this again later, when there are very few rows with nulls, it will help to keep the transaction "smaller", updating only those rows and not the whole table.

Another equivalent, just shorter syntax, using the NULLIF() function:

UPDATE table
SET Col1 = NULLIF(Col1, ''),
    Col2 = NULLIF(Col2, ''),
    ---
    ColN = NULLIF(ColN, '')
WHERE
    Col1 = '' OR Col2 = '' OR ... OR ColN = '' ;      --- optional WHERE clause


A (simple?) solution to the "logical decoding / sequence" problem in Postgres? After reading this mind-blowing answer by Craig Ringer to my previous question Achieving "sequence number", rather than dateTime, for updates; postgres specifically I've become interested in the "logical decoding / sequence" racetrack problem in Postgres.
As I understand it (I may have stuffed-up to begin with), here is the racetrack problem in question:

The write for "A" gets a time of 100, and is written eventually at 300.
"B" will get a time of 120, and is written at 200.
A syncer reads at 250, as shown.

It gets the B, with a value of 120.
The A (100) is lost forever.  (The syncer thinks that it has everything up to 120.)
Assuming I even grasp the full depth of the problem. I see two simple-minded solutions:

Anything that writes has to have a lock.  So, actually B would not be allowed to start until A is finished. (It does not matter if a read happens in the middle of one.)

The reads have to wait on a write-lock. So, the read can't happen in the example until B is finished. (It does not matter if two writes are overlapping.)

Questions

Am I so dense I don't even know why my solutions are bad?
Is there perhaps some obvious, everyday setting for Postgres I don't know about (like: "Only allow one complete write at a time!" or something, which would implement (1) without bothering about locking.
Indeed do I even fully grasp the problem?

Regarding the logical-decoding system in Postgres.  (Doco) In my limited understanding, it works on a "channel based" approach.  So you could make, say, 1, 2, 3 or more "channels" of your logical decoding, which will each individually be at a different "points" in the stream. With logical decoding you can not (as I understand it) just have "arbitrary streaming" where you can say "give me everything from point X".

Your proposed solution is fine so long as you only need to track inserts and you don't need to be scalable. You can use an id-generator table you query with UPDATE ... RETURNING to implement it very simply; search for "gapless sequence".

Since it destroys write concurrency, it'll only be able to grow to a limited point.

It also won't help you track updates or deletes. Unless you start recording them with triggers that insert into a queue table. At which point you might as well use logical decoding. Or Londiste.

Logical decoding is indeed channel based. You can't step in at some arbitrary point. It's designed to ensure that you receive every change exactly once. You can skip over changes, though, or filter them at the output plugin level. The only restrictions really are that:

you cannot "rewind" once you confirm receipt of changes (but you can intentionally replay the same changes multiple times before you confirm; see the peek functions)
the initial slot position is always the current server's insert position or slightly after it. You can't create a slot that starts in the past.

Personally I think you should just be using one of the json based logical decoding plugins for this, but I don't know the full picture of what you're trying to achieve.


Strange Intermittent Postgres Performance Issue (docker?) I am running a local Postgres 9.3 in a docker container with a temporary database that is used for some file import operations.

The basic scenario is:

Load files into input tables
INSERT from a SELECT into the output tables (transform)
Read the output tables

There are several tables that we transform with separate and sequential statements.  We are running into severe performance degradation on two of our transform steps. It changes between which of the two will have the issue and if one has it, the other will not.

The degradation is ~100x slower, 2 seconds -> 20 minutes for one; and 4 seconds -> 40 minutes for the other.

The queries look like:

INSERT INTO target_table (
  field1,
  field2,
  -- elided
  field20,
  member_key,
  source_file_name,
  source_line_number
)
SELECT
  src.field1 as field1,
  src.field2 as field2,
  -- elided
  src.field20 as field20,
  mem.member_key as member_key,
  src.source_file as source_file_name,
  src.source_line as source_line_number
FROM
source_table src
INNER JOIN members mem
ON src.member_identifier = mem.member_identifier;

The two queries are nearly identical, the slower of the two has a few more fields, and about twice the rows. The 'source_table' and 'target_table' change, but both queries use the same members table.

In our sample set the first source table has 360,000 rows and the second has 600,000 rows. There is no where clause, so the SELECT portion will operate on all rows.

This slowdown started showing up for us about a week ago. The only thing that we changed was creating a reduced data set to work on performance issues elsewhere in the system. The original datasets for those queries were 3.6M and 6M rows and the inserts never took longer than a couple of minutes to complete.

Other Information:

This is running on an AWS EC2 i2.2xlarge box.
OS is Ubuntu 14.04.2 LTS
Postgres and the caller of this query are in Docker containers (separate)
The Postgres image is the stock postgres:9.3 image run with postgres -F (disables fsync, this is an ephemeral database)
There is nothing else happening on the box
When running the query, one CPU is at 100%, the others hover around 0%
When running the query, top reports a 'wa' parameter of 0.0%
When the query was running and stuck in the 100% CPU state, I ran just the SELECT portion from a separate connection. This completed in a couple of seconds.
When running the query, the checkpoints_req column of the table pg_stat_bgwriter is 2.

I think this is the answer. I'll update/remove this if I get any contra-indications.

In our (admittedly bizarre) use case, the tables in this ephemeral database are being loaded via COPY FROM statements. Then we start performing transforms.

My hypothesis is that the table statistics do not reflect the actual data population when the queries are run, causing the generation of a poor query plan.

I have forced the execution of an ANALYZE statement after the bulk load, but before the queries began. This seems to have addressed the issue.


insert is not working as expected while doing partitioning Created a table with one column and sample values

create table t1 ();
alter table t1 add column id_ numeric;
insert into t1 values (generate_series(1,10));

And, done partitioning odd numbers in one table and even numbers in one table.

create table t1_odd (check (id_%2!=0)) inherits (t1);
create table t1_even (check (id_%2=0)) inherits (t1);

create table t1_r15 (check (id_<6)) inherits (t1);
create table t1_r510 (check (id_>4)) inherits (t1);

Call this function while insert or update anything in table t1

create or replace function t1_func() returns trigger as $$
begin
    if NEW.id_ % 2 != 0 then
       insert into t1_odd values (NEW.*);
    else
       insert into t1_even values (NEW.*);
    end if;
    return null;
end;
$$
language plpgsql;

create trigger t1_trig before insert or update on t1 for each row execute procedure t1_func();

Call this function while delete anything from table t1

create or replace function t1_rfunc() returns trigger as $$
begin
    if NEW.id_ < 5 then
        insert into t1_r15 values (NEW.*);
    else
        insert into t1_r510 values (NEW.*);
    end if;
    return null;
end;
$$
language plpgsql;

create trigger t1_rtrig before delete on t1 for each row execute procedure t1_rfunc();

It works fine for insert, and If i tried to update, getting some error

update t1 set id_ = id_ + 1;

gives

ERROR: new row for relation "t1_odd" violates check constraint "t1_odd_id__check"
SQL state: 23514
Detail: Failing row contains (2).

and while doing delete nothing gets inserted into t1_r15 or t1_r510 (data got removed from t1).

Am I missing anything?

(Using Postgresql 9.5)

Well, with the checks you use for your child tables, it is obvious why such an UPDATE runs into problems.  Adding one to id_ changes it from even to odd or back - the new row does not match the check anymore.

The first trigger is not enough to circumvent this as it does not remove the old row from the child table.  You need a mechanism that does exactly this - delete the old row version and readd the new to either t1 or the matching child.  This trigger has to be defined on t1_odd and t2_even.


Convert yymmdd to datetime using ssis derived column Below are the strings representing date of birth (yymmdd). I want to convert it into datetime format and load into a database using ssis. How can I do it? In my derived column I have (DT_DATE)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2)), but it's not working.

470324
470324
470209
101
0

Data Flow

Here's the general approach I'd take to solving your problem.

I started with your source data and added some other conditions - a NULL as well as a 14 and 15 year to ensure my logic later is correct.

SELECT
    D.DrvDOB
FROM
(
    VALUES
    ('470324')
,   ('470324')
,   ('470209')
,   ('140209')
,   ('150209')
,   ('101')
,   ('0')
,   (NULL)
) D(DrvDOB);

This is going to seem like a lot of work, but for me, this is the most maintainable approach. If something's wrong, I can simply inspect between the derived columns and find the failing bit of logic. Many people mistakenly believe the amount of components on a data flow decrease the processing time. The reverse case can actually be true, see the sqlblog post at the end for more details.

Create a column called IsValidLength This is going to provide us a boolean to tell us whether we even need to try and parse the year, month and day from the string. I'm ensuring we don't have a NULL value and that the length is 6. Otherwise, we're going to flag as invalid

!IsNull([DrvDOB]) && LEN([DrvDOB]) == 6

I then am going to generate 3 more string columns (der_Year, der_Month, der_Day) with the parsed year, month and day. Otherwise, I'm going to use 00 as my value. Again, I only attempt to parse the string if it's valid.

(IsValidLength) ? SUBSTRING([DrvDOB], 1, 2) : "00"
(IsValidLength) ? SUBSTRING([DrvDOB], 3, 2) : "00"
(IsValidLength) ? SUBSTRING([DrvDOB], 5, 2) : "00"

Now comes the actual fun part, determining the actual year. Since your field is called date of birth, I'm assuming there are no future dated records - my apologies if you have psychic data. I took the route SQL Server, .NET library and COM use and have a prescribed year to use as the marker between what gets tagged as 19XX vs 20XX. I create an SSIS Variable called TwoDigitYearCutoff and assign it a value of 2014. In short, any years that are parsed from 00 to 14 will be tagged with the 21st century, while the rest are left in the 20th century. Adjust this value as needed.

Back in my data flow, I create yet another column, this one der_ActualYear which will be the 4 digit year.

(@[User::TwoDigitYearCutoff] / 100
- ((DT_I4)der_Year > @[User::TwoDigitYearCutoff] % 100 ? 1 : 0))
* 100 + (DT_I4)der_Year

At this point, we know the full year, the month and day along with whether this data is any good. The final piece then is to put it all together and make it an actual Date. I build out a YYYY-MM-DD string and cast that as a date. Otherwise, I cast NULL as a date (to get my metadata consistent)

(IsValidLength) ? (DT_DATE) ((DT_WSTR, 4) [der_ActualYear]
+ "-" + [der_Month] + "-" + [der_Day]) : NULL(DT_DATE)

You can now wire der_DrvDOB into your target destination knowing that you have a valid date or a NULL.

Results

As expected, valid dates or null

Biml

Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. Here, we're going to use it to describe the ETL. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that addresses a host of shortcomings with it. Specifically, we're going to use the ability to transform a Biml file describing ETL into an SSIS package. This has the added benefit of provding you a mechanism for being able to generate exactly the solution I'm describing versus clicking through many tedious dialogue boxes.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!-- 74383 -->
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLNCLI11;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=tempdb" Name="CM_OLE" />
    </Connections>
    <Packages>
        <Package Name="dba_74383" ConstraintMode="Linear">
            <Variables>

                <Variable DataType="Int32" Name="TwoDigitYearCutoff">2014<Annotations>
                    <Annotation AnnotationType="Description">Use the two digit year cutoff to specify an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years</Annotation>
                </Annotations>
            </Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT Make Dates">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC Gen data">
                            <DirectInput>SELECT
    D.DrvDOB
FROM
(
    VALUES
    ('470324')
,   ('470324')
,   ('470209')
,   ('140209')
,   ('150209')
,   ('101')
,   ('0')
,   (NULL)
) D(DrvDOB);</DirectInput>
                        </OleDbSource>
                        <!--
                            Test whether we have minimally valid data
                        -->
                        <DerivedColumns Name="DER IsValidLength">
                            <Columns>
                                <Column Name="IsValidLength" DataType="Boolean"><![CDATA[!IsNull([DrvDOB]) && LEN([DrvDOB]) == 6 ]]></Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Extract the year
                        -->
                        <DerivedColumns Name="DER ExtractYear">
                            <Columns>
                                <Column Name="der_Year" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 1, 2) : "00"</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Extract the Month
                        -->
                        <DerivedColumns Name="DER ExtractMonth">
                            <Columns>
                                <Column Name="der_Month" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 3, 2) : "00"</Column>
                            </Columns>
                        </DerivedColumns>

                        <!--
                            Extract the day
                        -->
                        <DerivedColumns Name="DER ExtractDay">
                            <Columns>
                                <Column Name="der_Day" DataType="String" Length="2">(IsValidLength) ? SUBSTRING([DrvDOB], 5, 2) : "00"</Column>
                            </Columns>
                        </DerivedColumns>

                        <!--
                            Guess at year based on cutoff date. Assume historical data and not future dated
                            since this is DOB. For supplied value of 2014,
                            if 14 is seen, it will be 2014
                            if 15 is seen, it will be 1915

                            http://technet.microsoft.com/en-us/library/ms189577(v=sql.105).aspx
                            A two-digit year that is less than or equal to the last two digits of the cutoff year is
                            in the same century as the cutoff year. A two-digit year that is greater than the last
                            two digits of the cutoff year is in the century that precedes the cutoff year. For example,
                            if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049
                            and the two-digit year 50 is interpreted as 1950.

                            https://stackoverflow.com/questions/2024273/convert-a-two-digit-year-to-a-four-digit-year
                            this.TwoDigitYearMax / 100 - (year > this.TwoDigitYearMax % 100 ? 1 : 0)) * 100 + year

                            @[User::TwoDigitYearCutoff]) / 100 - ((Int32) [der_Year] > @[User::TwoDigitYearCutoff]) % 100 ? 1 : 0)) * 100 + (Int32) [der_Year]
                        -->
                        <DerivedColumns Name="DER Guess at year">
                            <Columns>
                                <Column DataType="Int32" Name="der_ActualYear"><![CDATA[(@[User::TwoDigitYearCutoff] / 100 - ((DT_I4)der_Year > @[User::TwoDigitYearCutoff] % 100 ? 1 : 0)) * 100 + (DT_I4)der_Year]]></Column>
                            </Columns>
                            <Annotations>
                                <Annotation AnnotationType="Description">This might be overkill, but it sure was fun</Annotation>
                            </Annotations>
                        </DerivedColumns>

                        <!--
                            Put it all together
                        -->
                        <DerivedColumns Name="DER Make valid date">
                            <Columns>
                                <Column Name="der_DrvDOB" DataType="Date">(IsValidLength) ? (DT_DATE) ((DT_WSTR, 4) [der_ActualYear] + "-" + [der_Month] + "-" + [der_Day]) : NULL(DT_DATE) </Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER bitbucket">

                        </DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

References

http://technet.microsoft.com/en-us/library/ms189577(v=sql.105).aspx
https://stackoverflow.com/questions/2024273/convert-a-two-digit-year-to-a-four-digit-year
http://web.archive.org/web/20111227015148/http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/12/investigation-can-different-combinations-of-components-effect-dataflow-performance.aspx


Table that doesn't exist, exists? SHOW TABLES

shows table: foo

DESCRIBE foo

Gives me:

ERROR 1146 (42S02): Table 'foo' doesn't exist

At one point, table foo did exist, but it was dropped because it wasn't needed.

Seems the schema still thinks it's there. How do I fix that?

Here is the quick and dirty answer

Running SHOW TABLES; will go to the OS and check for the existence of .frm files only.  I mentioned this 5.5 years ago (MySQL tables do not show up in the phpMyAdmin and MySQLWorkbench)

In your case, there is a file called foo.frm. That file exists in the database folder of your database.

When you try doing things to the table at the storage engine level, that's when you get that message because there are no storage engine level files for the foo table ( no foo.ibd file if the storage engine was InnoDB or no foo.MYD/foo.MYI if the table was MyISAM).

My guess is that someone deleted the storage engine level files for the table but forgot to delete the corresponding .frm.

The solution would be to find foo.frm in the OS and delete it. The information_schema will quickly disavow any knowledge of the foo table.


Max function and group by getting differenet count Statement 1 :

Select Load_Date , Count(*) Last_Count From <Table_Name> group by Load_Date;

Statement 2 :

Select max(Load_Date) , Count(*) Last_Count From <TABLE_NAME>;

Statement 1 max Date count is different and statement 2 max date count is different.

I need your help to understand the output.

The first query returns the count of rows across a given load_date.
Whereas the second query gives you the count of rows for maximum load_date.
Say you have 2 dates. First query returns :

LoadDate.           Count(*)
03-04-2014.          12
03-05-2014.           11

Second query returns:

Max(loadDate).      Count(*)
03-05-2014.             23

If you want that the count of just max row be found, then the query will be:

SELECT COUNT(*)
FROM <table name>
WHERE LoadDate = MAX(loadDate);


PostgreSQL Citus Extension Temp Table Is it possible with citus extension in PostgreSQL to create temp table that is copied to each worker node (like reference table)?

When I run SQL like this:

DROP TABLE IF EXISTS mypoint;
CREATE TEMP TABLE mypoint (mpoint geometry primary key);
SELECT create_reference_table('mypoint');

I get the error:

  ERROR:  unacceptable schema name "pg_temp_6" DETAIL:  The prefix "pg_"
  is reserved for system schemas. CONTEXT:  while executing command on
  mynode01:5432 SQL state: 42939

The reason I am asking this is because there are more SQL commands afterwards where I need to do a join between the temp table and a distributed table (which is not allowed with citus extension).

In this particular example the temp table is created in schema pg_temp_6. create_reference_table tries to create the exact same schema in the worker, and fails.

Could you just create a regular reference table, and drop it when you are done instead of creating a temporary table ?


What are the differences between SQL I/O Sim and SQL IO tools? I want to test my different SQL Server alternatives with a benchmark tool.
I found two tools by Microsoft:

SQLIO Disk Subsystem Benchmark Tool
SQL IO Sim

What are the differences between them? is it the same thing?

Thanks!

SQLIO is a generic tool for testing your disk subsystem. It allows you to specify whether to test random/sequential, data block sizes, queue depth, threading, etc.

SQLIOSim is a tool that tries to emulate the patterns that SQL Server would expose to your system.

Usually I'll use SQLIO to test the subsystem when benchmarking for raw specs. Once satisfied with my SQLIO results I'll run SQLIOSim to get a real world run through of the disk subsystem.

You might want to see Brent Ozars tutorial on SQLIO, which also mentions SQLIOSim:
http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/


MongoDB 3.2 $ifNull with Validator I'm trying to achieve a document validator that checks if a user signs up with an avatar to their user profile. If they didn't attach an avatar I want to use something like $ifNull to set the default image:

db.runCommand({
   collMod: "User",
   validator: { $or: [
       { avatar: { $ifNull: [ "$avatar", "default-avatar.png"] } }
    ]},
   validationLevel: "strict"
})

However, it seems like MongoDB separates document validation and it's aggregation methods... Is there a way to do this so that it will stay as a rule on a collection?

As you have noticed, right now this kind of "validation" is not possible in the mongodb. However, there is feature request open for such feature. If you give your 'vote' for it, maybe they make this feature. ;-)

Currently, the easiest way to archive that is do check (like that) in your application, just before inserting (or updating) document.


Extracting 'hot columns' into a separate table I have this table:

CREATE TABLE fragment
(
  fragment_id integer,
  start_date timestamp without time zone,
  end_date timestamp without time zone,
  duration integer,
  -- <10+ more columns>
  revision_1 integer,
  revision_2 integer
)

It is pretty big: 44 million rows, 27 GB of disk space. Daily insert rate is about 70k rows.

The data in this table is almost never updated except for the last two columns named revision_1 and revision_2. They are updated via triggers set on other related tables. Updates come very frequently, especially for new rows in fragment table. Each row can be updated up to 50-100 times. Old rows (let's say 1 week old), however, stop being updated, as they are considered 'processed'.

As far as I know, UPDATE operation in Postgres is implemented as something like DELETE + INSERT. So, when a value in a single column is updated, the whole row is marked as deleted and a new row is created. That's why, I think, my fragment table is autovacuumed every day which takes several hours.

The question is, is it generally a good idea to extract 'hot columns' into a separate table? I mean something like this:

CREATE TABLE fragment_revision
(
  fragment_id integer,
  revision_1 integer,
  revision_2 integer
)

The behaviour you're describing is called MVCC (Multi version concurrency control). Strictly saying it's not delete + insert. It is more like:

copy current version of row
update as requested
append new current version to a chained list of version

In the background this history is being cleaned up depending on how old your oldest transaction is. If you have long running transactions the history can grow pretty big.

  This is not postgresql specific. It's a very common method across
  many databases to handle conccurrency. To see more: MVCC in Postgresql

Therefore it does make sense to move such "hot column" to a separate table.  Especially if the row size is significantly bigger than the size of the "hot columns".

Be aware though it has some implications on the select performance:

If these columns are present in your queries for filtering or sorting query time will take a hit
To retrieve these columns you need to join the table which again has some impact on time

There is an alternative method of splitting where your main table contains only the small, filtering and sorting columns (including these columns too) and "data" is stored separately.

It's always best to try which works best for your dataset and query patterns. I did a benchmark about a year ago comparing one big table with two splitting strategy that can help you get started: http://charlesnagy.info/it/postgresql/split-or-leave-frequently-updated-column-in-postgresql