Pre-sorting postgres table instead of running CLUSTER Does pre-sorting a csv by a designated column and then inserting the data into postgres have the same effect as not pre-sorting, importing the data, creating the index and then CLUSTERing the table using that index?

Aside from the index you mentioned, and it being the default CLUSTER sort order now (Postgres remembers), both tables will turn out identical. Same order, no bloat.

Parallelism could potentially change this. But for now, writing queries are not parallelized. The manual:

  Even when it is in general possible for parallel query plans to be
  generated, the planner will not generate them for a given query if any
  of the following are true:

  The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within
  a CTE, no parallel plans for that query will be generated. As an
  exception, the commands CREATE TABLE ... AS, SELECT INTO, and CREATE MATERIALIZED VIEW
  which create a new table and populate it can use a parallel plan.

So neither INSERT nor COPY use parallel queries and a single process writes rows as received, i.e. in the order of the CSV file - even if there are no formal guarantees. Tables are heaps in theory and Postgres (like most RDBMS) generally treats the physical order of rows as implementation detail. But in the current implementation order is preserved, and that won't change without good reason. Parallelism would be a good reason, but that's hard to implement for writing queries.

Related:

Does (or can) the PostgreSQL COPY TO command guarantee a particular row order?
Does Postgres preserve insertion order of records?


How to search for a string in all columns of a table? I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. For example, I have a table like this.

I need a query to return all rows that has Italy in any of their columns i.e the query shoud return 1st and 3rd rows.

You can use an IN condition for that:

select *
from the_table
where 'Italy' IN (name, native, place);


My SQL 5.6.4 DATETIME column milliseconds is not getting stored Beginning from MySQL 5.6.4, milliseconds support is provided for date columns like DATETIME etc.

I just created a table to check if its working:

create table testdata(startime  DATETIME(5));

However, when I save some DATE(java.lang.Date) value using iBATIS framework it is ignoring milliseconds and storing:

2012-04-16 17:54:55.00000

Any idea how to resolve this issue?

When I use an SQL query like INSERT INTO testdata VALUES ('2012-04-16 17:54:55.32345') it stores milliseconds properly.  I want it to work via iBATIS.

Similar issue (both Hibernate and MyBatis don't store milliseconds) was solved by upgrading the MySQL driver from mysql-connector-java-5.1.17.jar to mysql-connector-java-5.1.26.jar.


Let's start by considering the downpayments and payments together.

WITH AllPayments AS (
    SELECT * FROM TableB
    UNION ALL
    SELECT * FROM TableC
)

Now get the total and latest date.

, YearlyDetails AS (
    SELECT TableAID, YEAR([Date]) AS TheYear,
        SUM(Amount), MAX([Date]) AS LastDate
    FROM AllPayments
    GROUP BY TableAID, YEAR([Date])
)

Now you can easily join this to TableA...

So putting all of it together:

WITH AllPayments AS (
    SELECT * FROM TableB
    UNION ALL
    SELECT * FROM TableC
), YearlyDetails AS (
    SELECT TableAID, YEAR([Date]) AS TheYear,
        SUM(Amount) AS Amount, MAX([Date]) AS LastDate
    FROM AllPayments
    GROUP BY TableAID, YEAR([Date])
)
SELECT a.ID, a.Name, y.Amount, y.LastDate as [Date]
FROM TableA AS a
JOIN YearlyDetails AS y
ON y.TableAID = a.ID;


How do I use the unload command to export a table to a IXF file For a table T in schema S, how do I use the unload command to export T data into a IXF file? The IBM DB2 documentation isn't very helpful for this simple case.

I am trying to compare performance between the export command and the unload command. This is on DB2 9.7 for AIX

Ah ok.

If you are referring to the UNLOAD command, that is only available on DB2 for z/OS. DB2 LUW does not have an UNLOAD command....except if you purchase IBM's separate product Optim High Performance Unload. (I am assuming you are wishing to use that?)

Optim High Performance Unload uses the db2hpu command. And though we do not use the tool where I work, it appears that the syntax you are after is the following:

db2hpu -t 'S.T' -d <database-name> -format ixf -m 'filesystem/messages.MSGS' -o 'filesystem/myfile.IXF'


Multiple values for unique id? I have two tables: customer and boxes in one-to-many relationship.
The structure of the tables is in addition :

Customer table :   id#| name | date_purchased | address |
                    1    John     2012-9-10       N/A

Boxes table:   id#| type | quantity |
                1    big      4

The id of the boxes table is foreign key to the customer table.

What if John has purchased more than 4 big boxes ? For example: he has ordered 2small boxes as well. How to add this to the database design schema?

I would create three tables instead:

Customer table:

Customer_id, name, address, etc.
PK: customer_id

Boxes table:

Box_id, size, description, etc.
PK: box_id

Orders table:

Customer_id, Box_id, Quantity, Timestamp.
PK: all four fields.
Foreign keys box_id and customer_id to the respective tables.

You will have one entry for each customer, each box type, and as many boxes of different sizes ordered as you wish.


Entity-Relationship Diagram problem: trying to resolve a tentative many to many relationship The case study states "each car belongs to a car type" and "each mechanic specializes in a car type or many car types".

My current entity type definitions:

Car
CarType
Mechanic

My Entity-Relationship Diagram so far:

Car        1-----0..*   CarType
CarType 0..*-----1      Mechanic
Car     0..*-------0..* Mechanic

As stated in the title, I am attempting to resolve a possible many to many relationship.

I'm not sure I entirely understand your question as it could be better structured, is this the structure you are looking for?

A CarType can have many cars BUT a car can only have one CarType. 1-to-many

A Mechanic can specialise in many CarTypes AND a CarType can have many Mechanics specialise in it.
many-to-many

A mechanic can then work on multiple cars BUT a car can only be worked on by 1 mechanic.
1-to-many

So when implementing the structure will look something like this:

Hope this is of help, apologies if i haven't answered what you are looking for.


Filter rows if value exists in different columns We're on SQL Server 2016 and 2019
I have a data-set where I'm rolling up a parent/child relationship. I'm doing this because some of the parent rows will return a SalesValue of 0 when their children will the value we're looking for.
Here's my query:
SELECT ART.StockCode
     , BOM.Component
     , ART.QtyInvoiced
     , ART.Invoice
     , ART.SalesValue
     , ART2.SalesValue
  FROM TABLE1 ART
  JOIN TABLE2 IM
    ON IM.StockCode = ART.StockCode
  LEFT OUTER JOIN TABLE3 BOM
    ON BOM.ParentPart = ART.StockCode
   AND IM.KitType = 'K'
  LEFT OUTER JOIN TABLE4 ART2
    ON ART2.StockCode = BOM.Component
   AND ART2.Invoice = ART.Invoice

And the data it returns
StockCode   Component   QtyInvoiced    Invoice     SalesValue   SalesValue2
315128      NULL        1.000000             1         154.32          NULL
317324      NULL        1.000000             1          68.31          NULL
317350      NULL        1.000000             1         428.90          NULL
318482      NULL        1.000000             1         171.24          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
900550      NULL        1.000000             1        2396.08          NULL
900572      NULL        1.000000             1        1448.84          NULL
900581      NULL        1.000000             1        2017.96          NULL
901012      900581      1.000000             1           0.00       2017.96
901012      317350      1.000000             1           0.00        428.90
901012      317324      1.000000             1           0.00         68.31
901062      900572      1.000000             1           0.00       1448.84
901062      318482      1.000000             1           0.00        171.24

What I'm trying to do is exclude any StockCode that has a value in the Component field that has the same Invoice number
In the example above, we're looking to exclude stock codes 900581, 317350, 317324, 900572, and 318482 because they already exist in the Component field.
Here's what I'm going for:
StockCode   Component   QtyInvoiced    Invoice     SalesValue   SalesValue2
315128      NULL        1.000000             1         154.32          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
900550      NULL        1.000000             1        2396.08          NULL
901012      900581      1.000000             1           0.00       2017.96
901012      317350      1.000000             1           0.00        428.90
901012      317324      1.000000             1           0.00         68.31
901062      900572      1.000000             1           0.00       1448.84
901062      318482      1.000000             1           0.00        171.24

WITH cte AS (your query)
SELECT t1.*
FROM cte t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM cte t2
                   WHERE t1.StockCode = t2.Component )

This works by wrapping your original query up into a CTE, then only returning rows from it if there are no other rows where the Component column is equal to the StockCode column.


Can I add "Included Columns" to an index without affecting performance? I have a non-clustered index with 63 Million leaf level rows. Currently it does not have any included columns. I would like to add one included column while the site is online. Will this significantly affect performance if I do it through the SSMS GUI?

Why would you do anything through the SSMS GUI???

Try doing it as a pro, by a script. Your best option is to create the index online with drop existing, changing the definition to match the new desired schema in the process:

create index ... on ... include ... with (online = on, drop_existing = on);

This, obviously, is subject to the usual restrictions for ONLINE operations, and will cause the normal overhead caused by an ONLINE index create/rebuild operation. But the good news is that the operation will be online, will not block existing queries and the old index will be available for queries while the new one is being built. There will be a query drain stage at the end when all queries will block while the indexes are being swapped, but that should be a very fast operation (as long as there are not long running user transactions blocking the swap itself).


Encrypting table in SQL Server I'm a .NET guy that is trying to start using table encryption on some of my Users tables but I could do with some questions being answered first. I don't want just anyone to be able to see these tables.

When should you start encrypting your tables in SQL server, should I encrypt all tables?

How to avoid going to far?

The steps required to encrypt a table?

Encryption is a huge topic. There are lots of caveats, and most of those are about business requirements, not about technology.

Start by taking a step back and look at the big picture. Pop the why? stack until the business requirements are clear. Then start formulating requirements about what data needs to be protected and what kind of attacks are expected. Only after then start exploring what encryption would be suitable and what wouldn't.

Who or what would attack the database? Rogue admin? Oops, data-at-rest encryption doesn't help much. Network sniffer? Hope you are using TLS between application and SQL Server. SQL injection? Something else?

What would happen in case of data breach? Vandalism? Lawsuits? Loss of reputation? Heist of Bitcoins? Authoritarian government hangs dissidents?

Some domains, like health care and banking, often have strict laws about data protection. US, EU and rest of the world have their own quirks.

As practical issues, key management is among the most complex one. Do you have a key management process? Without one, managing certificates and encryption keys is going to be painful. Restoring old backups and server migrations are suddenly extra complex, as backup itself isn't enough.

If working on Enterprise Edition or Developer Edition, TDE is relatively simple to set up.

Column encryption is suitable for hiding a subset of data. An example about securing national id numbers.

Always Encrypted moves the crypto to application layer. Good solution for some cases, abysmal for the rest.

Dynamic data masking isn't actually encryption, but suitable for many cases in which confidential data must be partly exposed.


Best practice design when expecting large data in a table I have come across a way that kind of works for me, and was just wondering if it would have any side effects, or, if I'm just thinking about this the wrong way.

I first had this idea when I was building a relatively simple location database. It would hold a table of 249 countries, and a table of 143000 cities with their longitude and latitude.

--------------------------
-         Country        -
--------------------------
-   id  -    name        -
--------------------------
-    1  -   England      -
-    2  -    Wales       -
--------------------------

-----------------------------------------------------------
-                City                                     -
-----------------------------------------------------------
- id -     name   -      lng    -    lat     - COUNTRY_id -
-----------------------------------------------------------
-  1 -  London    -  -0.127758  - 51.507351  -    1       -
-  2 - Canterbury -   1.078909  - 51.280233  -    1       -
-----------------------------------------------------------

Now, when I was using the Haversine equation to find the closest city given a longitude and latitude, it would only do about 10 results per second. My thought behind this was because it had to do this calculation on 143000 cities ...

So, instead of go out and buy a supercomputer to do these calculations, I thought I could narrow down which cities it had to do the calculations on.

I done this by basically dividing the world into 2448 grid squares, and putting those cities in a table of their own, effectively now having 2448 tables. I then use PHP to find which grid square the given longitude and latitude resides in, and then query that table, and it's surrounding 'grid squares', or tables.

This resulted in a 10 fold speed increase, returning over 100 results per second.

I was wondering if the same concept could be used in say, a user database, where the tables may be split depending on the first 2 characters of the persons username. So, if you had 1,000,000 users, (And they were only allowed a-Z for their usernames), you could effectively have these spanned over 676 tables, averging about 1500 users per table, and then increasing the speed at which a user could log on?

Ha ... Notice the question mark at the end ...

So, I'm expecting a lot of 'Nope ... Thats just wrong' ... But I kinda want to know if my brains just having a stupid week, or if someone has seen something along these lines.

Yes, the same concept could be used.  What you have done is re-implement table partition, but in user space.  Most industrial-strength RDBMSs will have this built in.  The provided functionality often includes additional abilities, such as efficiently adding and removing partitions at run time without applicaiton changes.  By choosing to roll your own you miss out on these additional features.  Additionally you complicate some things, such as surrogate ID uniqueness checking, aggregate queries across your whole user community and DRI referencing the "user" table.

Be aware that your sub-tables are very unlikely to be well balanced.  There aren't many Mr. Aardvark or Ms. Zymology in the world but a lot of Smiths and Jones.

The reason key lookups are faster on smaller tables is because the indexes have fewer levels, assuming you have B-Tree indexes.  Therefore the DBMS has to read fewer pages to get from the index's root node to its leaf & data pages.  The index on your 676 sub-tables is likely to be only one or two levels deep and so incur only one or two page reads to read a key's row.  In contrast a full B-Tree built on 1M rows may be three or four levels deep and require that many page reads per lookup.  Built-in partitioning can give you similar benefits if you define your index as partitioned, too.

This is a good reason to keep your index keys compact, if you have a choice.  For example, if your user name is 30 characters long this will take 30 bytes to store and you will get a certain number on a page.  If instead you calculate an integer hash (4 bytes) of the user name and index that, there will be 30/4 = 7-ish times more rows per index page and the index will likely have fewer levels. (Of course you will have to account for potential hash collisions.)  Similarly limiting the amount of free space you have in the index to allow for inserts will help increase density.


Best way to speed up a linked server query - 49 columns plus two calculated columns and 170 mill rows I am trying to speed up at linked server query. The source table has 170 mill rows, and we need to retrieve all of them.
Trying to insert this query in a table on destination server takes over 2 hours. And then the index takes between 30 min and one hour. It is to be used in a query, joining col51 with another table, and col50 is used in the where clause.

Is there any way we can speed this up or use a different approach?

Here is the query:

SELECT * into destination.table FROM OPENQUERY(linkedserver,'
SELECT
col1
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col30
col31
col32
col33
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
   ,BINARY_CHECKSUM(
col2
col3
col4
col5
col6
col7
col8
col9
col10
col11
col12
col13
col14
col15
col16
col17
col18
col19
col20
col21
col22
col23
col24
col25
col26
col27
col28
col29
col34
col35
col36
col37
col38
col39
col40
col41
col42
col43
col44
col45
col46
col47
col48
col49
) AS col50
   ,CASE WHEN col1= 1  THEN CONVERT(BIGINT,CONCAT(1*100,col8))
         WHEN col1= 2  THEN CONVERT(BIGINT,CONCAT(2*100,col13))
         WHEN col1= 3  THEN CONVERT(BIGINT,CONCAT(3*100,col9))
         WHEN col1= 4  THEN CONVERT(BIGINT,CONCAT(4*100,col10))
         WHEN col1= 10 THEN CONVERT(BIGINT,CONCAT(10*100,col11))
         ELSE CONVERT(BIGINT,CONCAT(col1*100,col3))
        END AS col51
  from source_server.table')

After inserting to table, this query is the other part - which also takes a long time. Suggestions to enhance it?

SELECT
           ISNULL(A.Col1,B.Col1) Col1
          ,ISNULL(A.Col2,B.Col2) Col2
          ,ISNULL(A.Col3,B.Col3) Col3
       ...etc
         ,ISNULL(A.Col50,B.Col50) Col50
         ,ISNULL(A.Col51,B.Col51) Col51
  FROM Table1 A -- linked server table
  FULL OUTER JOIN Table2 B ON A.Col51=B.Col51 --local table
  WHERE (B.Col50 IS NULL AND A.Col50 IS NOT NULL)--new rows
       OR  (A.Col50 IS NULL AND B.Col50 IS NOT NULL)--deletes
       OR  (B.Col50 IS NOT NULL AND A.Col50 IS NOT NULL AND B.Col50 <> A.Col50) --updates

it is in some way unreadable. Why not trying to call a stored procedure and getting the dataset?

select * from
OPENQUERY(link_server_name,'exec your_sp par1,par2)

What's more, instead of using CONVERT what about of using CAST?

CAST is ANSI compliant.

Best,


Can sp_MSforeachDB be used for DBCC CheckDB without errors being masked? I have a test database with an index I intentionally corrupted to test DBCC CheckDB.

When I run:  DBCC CHECKDB (Test2) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY

I get, as expected:

Msg 8928, Level 16, State 1, Line 1
Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data): Page (1:156) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data), page (1:156). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:156) and previous child (0:0), but they were not encountered.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'Names' (object ID 2105058535).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'Test2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test2).

However, when I run:  exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY'

I get no errors.

I understand that sp_MSforeachDB is not supported.  However, several blogs suggested using sp_MSforeachDB to check all the DB's on a SQL Server Instance.

Question:  Am I doing something wrong, and if so, what is the fix, or is this some idiosyncrasy with sp_MSforeachDB?

It sounds like sp_MSforeachDB is skipping your Test2 database. This is one of many known issues with sp_MSforeachDB as noted by Aaron Bertrand:

Execute a Command in the Context of Each Database in SQL Server

You can confirm by removing the NO_INFOMSGS parameter:

exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH DATA_PURITY'

This should cause messages to be returned for every database. Search the results for your Test2 database. I bet it isn't there.

With DBCC CHECKDB all error messages are displayed by default. Specifying or omitting the WITH ALL_ERRORMSGS parameter has no effect.

You may like to look at Ola Hallengren's solutions for SQL Server Backup, Integrity Check, and Index and Statistics Maintenance.


Is there some drawback to define SPARSE columns? I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?

Columns which are SPARSE but don't have NULL values in it will take up more space than a non-SPARSE column.  You'll only want to use a SPARSE column when the data is actually sparse.


Function quote_nullable(timestamp without time zone) is not unique When I run this query on "enterprisedb 9.2", it raises an error:

select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date;

Error:

LINE 1: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Error **********

ERROR: function quote_nullable(timestamp without time zone) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Character: 8

In Postgresql 9.1, this query above run ok.

Please tell me what is happening here. Why is the function quote_nullable(timestamp without time zone) not unique?

Works as is in my installation (Postgres 9.3).

You probably can fix this by casting to text.

SELECT quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy')::text);

And do not cast the result to date. It  would be pointless to call quote_nullable() if you try to cast the result back to date. It would also fail for NULL input.

Postgres allows function overloading. My installation has one instance of quote_nullable() taking text and another one taking anyelement (polymorphic type). The latter takes care of the date.

As for timestamp without time zone: Postgres first looks for an exact match (date in your case), then considers other options like types that can be cast implicitly or polymorphic types. The manual has the exact procedure for Function Type Resolution.

Not sure about EnterpriseDB. Run this to diagnose:

SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  proname = 'quote_nullable';

More about function overloading in this related answer on SO:
ERROR: function addgeometrycolumn is not unique


A way to take a backup of SQL Server 2000 database on a different domain network I am trying to take backups of databases on SQL Server 2000. This server is on a different network domain. I can access SQL Server Management Studio using sa login and password from the network domain I work on. I did some research for a work around and tried the following recommended script I read about.

EXEC master.dbo.xp_cmdshell 'NET USE B:\\xxx\backups\xxxx\sql /user: xxxx\xxxx xxxxxxx'
GO
BACKUP DATABASE [XXX] TO DISK = N'B:\\xxx\backups\xxxx\sql\XXX.bak' WITH NOFORMAT, INIT, NAME = N'XXX-Full Database Backup', SKIP, REWIND, NOUNLOAD, STATS = 10
GO

Failed with an error:

  Msg 3201, Device error or device off-line

Is it possible to take a backup on a different network domain using SQL Server Management Studio as in my case? If so, any suggestions.

The example of issuing xp_cmdshell to execute a 'NET USE' and then issuing a BACKUP DATABASE won't work because the 'NET USE' is executed outside of the known environment of SQL Server.
The xp_cmdshell starts a separate shell|cmd session, executes the NET USE then closes the shell|cmd session and returns to SQL. So the cmd session that the net use/mapping was applied to no longer exits when the BACKUP DATABASE is executed.

You might try writing a .bat/.cmd file that contains:
NET USE
SQLCMD -S -E -Q" Backup database....." -o backup.out
OR SQLCMD -S -E -i backup.sql -o backup.out

and then call backup.bat via xp_cmdshell "backup.bat"

But, I don't think that will work either.

The only way to make a mapped drive available to SQL is to have the NET USE execute when the Server/Host starts up, BEFORE the SQL Server service starts, and, the NET USE will need to be initiated under the same credentials as the SQL Server is using.

Maybe set the SQL Service to MANUALLY start and create a Scheduled Task that runs at startup that first maps the NET USE then does a NET START for SQL Server?

But I'm doubtful on that too...

Upon further investigation, it is possible to backup to a UNC path with SQL 2000, but, there are prerequisites:
https://support.microsoft.com/en-us/help/555128

Backups to a network folder require the following prerequisites:
1)  The SQL Server service must be running under a domain-level account.
2)  The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share.
3)  The remote share should only be accessed via UNC name.  Mapped drives may not be consistently visible to the SQL Service.

After that, you can set vars in your SQL script and pass them to the backup command:

DECLARE @FileName AS VARCHAR(100)
DECLARE @Date AS CHAR(8)

SET @Date = CONVERT(CHAR(8), GETDATE(), 112)

SET @FileName = '\BackupHost\BACKUP\dbname' +  @Date + '.bak'

BACKUP DATABASE dbname TO DISK = @FileName


Scripting an application role with a hashed password I need to script an application role with a hashed password, so I can copy it from one database to another.

Consider the following example code, which uses an Application Role to provide elevated access to a non-trusted user:

USE tempdb;

CREATE LOGIN LimitedLogin
WITH PASSWORD = 'Password1'
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF;

CREATE USER LimitedLogin
FOR LOGIN LimitedLogin
WITH DEFAULT_SCHEMA = dbo;

CREATE APPLICATION ROLE MyAppRole
WITH PASSWORD = 'Password2'
    , DEFAULT_SCHEMA = dbo;

EXEC sp_addrolemember @rolename = 'db_datareader'
    , @membername = 'MyAppRole';

CREATE TABLE dbo.Numbers
(
    [Number] int CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1) NOT NULL
);

INSERT INTO dbo.Numbers
VALUES (1)
    , (2);

GO

Once we've created the test setup in tempdb, we can login as the [LimitedLogin] user, and run the following:

-- login as [LimitedLogin]

USE tempdb;

SELECT *
FROM dbo.Numbers;

The following error is returned, as expected:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Numbers'
    , database 'Test', schema 'dbo'.

However, once we execute the sp_setapprole with the appropriate password, we can see the desired results from the dbo.Numbers table:

DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole @rolename = 'MyAppRole'
    , @password = 'Password2'
    , @fCreateCookie = 1
    , @cookie = @cookie OUT;
SELECT @cookie;

SELECT TOP(10) *
FROM dbo.Numbers;

EXEC sp_unsetapprole @cookie = @cookie;

I'd like to be able to automate creating the Application Role by scripting it from a source database to be applied to a destination database.  I can easily perform the majority of that by:

SELECT 'CREATE APPLICATION ROLE ' + QUOTENAME(dp.name) + '
WITH PASSWORD = ''xxxx''
    , DEFAULT_SCHEMA = ' + QUOTENAME(dp.default_schema_name) + ';'
FROM sys.database_principals dp
WHERE dp.type_desc = 'APPLICATION_ROLE';

However, I'd really like to be able to have the hashed password in the script, something like:

CREATE APPLICATION ROLE [MyAppRole]
WITH PASSWORD = 0x12345678 HASHED
    , DEFAULT_SCHEMA = [dbo];

Is this possible?  Presumably the hashed version of the application role password is stored somewhere in the database.

Using the SQL Server Management Studio "script to ->" functionality, the Application Role is scripted with a new password, as:

/****** Object:  ApplicationRole [MyAppRole]    Script Date: 9/22/2015 10:18:12 AM ******/
/* To avoid disclosure of passwords, the password is generated in script. */
declare @idx as int
declare @randomPwd as nvarchar(64)
declare @rnd as float
select @idx = 0
select @randomPwd = N''
select @rnd = rand((@@CPU_BUSY % 100) + ((@@IDLE % 100) * 100) +
       (DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))
while @idx < 64
begin
   select @randomPwd = @randomPwd + char((cast((@rnd * 83) as int) + 43))
   select @idx = @idx + 1
select @rnd = rand()
end
declare @statement nvarchar(4000)
select @statement = N'CREATE APPLICATION ROLE [MyAppRole] WITH DEFAULT_SCHEMA = [dbo], ' + N'PASSWORD = N' + QUOTENAME(@randomPwd,'''')
EXEC dbo.sp_executesql @statement
GO

Clearly, that is not helpful in my case, although it does provide an interesting method of generating random passwords.

Based on the answers so far, I have created a Connect suggestion to add support for this to the product:

CREATE APPLICATION ROLE should support the 'HASHED' syntax for passwords.

You can connect using the DAC (Dedicated Administrator Connection), and pull the password column from sys.sysowners. First, connect using:

ADMIN:Server\Instance

Then:

SELECT password_hash = [password]
  FROM sys.sysowners
  WHERE name = N'MyAppRole';

This view is only visible when using the DAC, and the column is not exposed in the parent view that is visible (sys.database_principals). Please be careful with the DAC, of course.

That all said, this doesn't help you. CREATE APPLICATION ROLE is much different from CREATE LOGIN, in that you can't supply a hashed password, only plain text. And don't even think about reverse engineering the hashed value, because modern versions of SQL Server use elaborate methods to encrypt a password. In fact if you try this yourself you will see that a different hash is created every time, even in the same statement:

SELECT PWDENCRYPT(N'foo'), PWDENCRYPT(N'foo');

Results (on my machine, this time), noting that yours will vary:

0x0200185968C35F22AF70...   0x0200D6C77A1D84A8467F...

So, I would recommend either:

Storing the application password in source control somewhere, or wherever you store other system passwords currently, and using that to generate the script to deploy the app roles to another server (or source control the entire CREATE APPLICATION ROLE script); or,
Using regular roles instead of application roles.


How to improve performance of frequently updated table in mysql? my table structure::

CREATE TABLE `gps_device_data`(
  `imei_no` bigint(15) NOT NULL DEFAULT '0',
  `ioelementpriority` int(1) DEFAULT NULL,
  `longitude` int(15) DEFAULT NULL,
  `latitude` int(15) DEFAULT NULL,
  `speed` tinyint(3) unsigned DEFAULT NULL,
  `iovalue` varchar(254) DEFAULT NULL,
  `odom` bigint(20) DEFAULT NULL,
  `alarm_category_realtime_id` varchar(70) DEFAULT NULL,
  `angle` int(4) DEFAULT NULL,
  `altitude` int(4) DEFAULT NULL,
  `satellites` int(4) DEFAULT NULL,
  `data_received_time` bigint(20) NOT NULL DEFAULT '0',
  `continue_data_received_time` bigint(20) NOT NULL DEFAULT '0',
  `movement` int(2) DEFAULT NULL,
  `eventsource` int(4) DEFAULT NULL,
  `data_location` varchar(250) DEFAULT NULL,
  `data_poi` varchar(100) DEFAULT NULL,
  `data_validity` enum('valid','invalid') DEFAULT NULL,
  `createdby` varchar(10) DEFAULT NULL,
  `createddate` datetime DEFAULT NULL,
  `modifiedby` varchar(10) DEFAULT NULL,
  `modifieddate` datetime DEFAULT NULL,
  `deletedby` varchar(10) DEFAULT NULL,
  `deleteddate` datetime DEFAULT NULL,
  PRIMARY KEY (`imei_no`,`data_received_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

query that takes too much time to generate report for vehicle.

select  odom,latitude/10000000 as latitude,longitude/10000000 as longitude,
       imei_no,iovalue,data_received_time,continue_data_received_time,
        data_location
    from  gps_device_data
    where  imei_no IN (7781112889,70433357641,356173061134123,
               865190010626029,354188046466178,356307040285917,863354010100132,
               863354010100107,8511139808,4209501721,9974052815,356307044188414,
               5252563256,7709111078,7043335764,2121212121,7043333691,
               7043333684,7043338378,100345,356307048930860,356307040328584,
               358899052926188,7621013796,7043132917,7043312765,9016102493,
               ...
               7820046194,8585858585,559862234897,123456789001,78000000021,
               2220002220,12345678911,863771023760608,863771023758834,
               9898787854,140507978,140908877,140507779,356173061103755,
               7485961230,454545454,147852390,1203203695,358174067325903
                      )
      and  data_received_time >= 1430463600000
      and  data_received_time <= 1446274800000
    Order by  imei_no,data_received_time;

my explain plan

+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | gps_device_data | range | PRIMARY       | PRIMARY | 16      | NULL | 629357 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+--------+-------------+

what my project do is track the vehicle get its information and display that information to specific owner of vehicle.

questions:

Are there any alternative queries  which gives me performance than above specified query?

Is it ok to use a Composite key with IN block?

my select query takes 25.93 seconds to execute.  If my above select query takes 2 or 3 seconds to execute its good form me. How can I reduce the time for the select query?

I have 10 millions rows in the gps_device_data table.

You have two "ranges" filtering the data.  MySQL has no good way to do that.

First, let's check some numbers.

How many rows in the result set?
How many rows without the IN clause?
How many rows without the time range?
Is this IN list and this time range typical?

With those, we may be able to decide to focus on filtering on the time range in preference over the IN range.

Meanwhile, let's get rid of the "midnight" bug... Your time range seems to be from the morning of May 1 through midnight of the morning of Oct 31.  Perhaps you meant to include all of Oct 31, not just one millisecond of that day?  Did you mean to ask for a full 6 months?  Suggest this coding style; it's less error-prone:

AND data_received_time >= 1000 *  UNIX_TIMESTAMP('2015-05-01')
AND data_received_time <  1000 * (UNIX_TIMESTAMP('2015-05-01') + INTERVAL 6 MONTH)

Back to the performance question...

If you are looking at a smallish fraction of the total time, then PARTITIONing may be useful for simulating a 2D index.  (I mention this as Case #2 in http://mysql.rjweb.org/doc.php/partitionmaint )

If you break the table into, say, 20-40 partitions PARTITION BY RANGE(data_received_time), then "partition pruning" will first filter on the time range.  Then your PRIMARY KEY, with imei_no first, filters on the other dimension.

I cannot say how much (if any) this partitioning will speed things up.  I would expect small time ranges to benefit (percentage-wise) the most.  A time range that hits all partitions will actually be a little slower than without partitioning.  (Hence, my "check some numbers" questions.)

Schema notes...  (Smaller is faster)

BIGINT is 8 bytes.  Can odom go beyond 16M miles, the limit for `MEDIUMINT UNSIGNED (3 bytes)?
INT(4) -- the (n) means nothing; suggest TINYINT (1 byte) or SMALLINT (2 bytes).
Some of the VARCHARs could be 'normalized' to say a 2-byte SMALLINT UNSIGNED.


Storing a music playlist, best approach? I am working on a web project that creates playlists online based on youtube links.

My question is how do I store a playlist on MYSQL. What I was thinking about doing was having a table with basically all information. something like this

example table

I could query this and get information "WHERE playlist_name=bob" and get every song in bobs playlist. Does this seem like a good route? Is it a problem that the table will eventually get huge? what about storing each playlist in a separate table, so bobs_playlist would be a whole new table. This would end up with MANY tables, is that a problem? are both approaches valid? is one better than the other? I just cant determine the best way to organize the information.

I would approach it with this structure:

This way you can have a more normalized structure, non-repeating data and simple tables.

In this method you would also be using WHERE PlaylistID=[bobs playlist id] instead.

With this structure's simplicity, you wont have to worry about any size issues for a long time. This structure will quickly handle millions of records. If you get bigger than that you can look into partitioning; that's not necessary for now.


Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server? after much researching and experimenting, I figured I would make an attempt to get some expert advice.

I am maintaining a stored procedure that is executed as a job. It creates a table with data nicely defined. It then uses an OpenQuery to collect some data and insert it into the table.

Example:

insert into LOCAL_TABLE
select *
from OPENQUERY(linked_server, 'select * from linktable')

The result of the openquery is 2.5 million rows. This takes roughly 2 hours to complete.

The DB server is an iSeries so I ran the query with Visual Explain and the query completes in about 1 second. I am running the query from MS SQL Server 2016. Both servers sit right next to each other connected through a gigabit switch.

I am trying to identify the bottleneck here and I believe it is the INSERT.

I have read about BULK INSERT and it would appear I cannot use BULK INSERT with an OpenQuery.

I have read about OPENROWSET(BULK... ) but I don't think I can use a linked server with this. I have to maintain using the linked server so that if the server changes the code does not need to be updated.

Finally, running this as SELECT * INTO may increase performance, but then the data types would be defined by the result set. I suppose I could work with it from there once it's local but I'd like to confirm this is worth the effort before embarking on it.

Any recommendations as to how I can improve performance here?

I ran the query with Visual Explain and the query completes in about 1 second.

Then it probably didn't fetch 2.5 million rows, which is likely your bottleneck.

  I am trying to identify the bottleneck here and I believe it is the INSERT.

Then try just running

select *
from OPENQUERY(linked_server, 'select * from linktable')

To eliminate the INSERT.

  running this as SELECT * INTO may increase performance

Test that too.  And test INSERT INTO with a Temp Table.

Using a 4-part name should not improve performance.


Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe? My application has a Product and a Purchased table.

The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the purchase table maintains a historical record of the product details.

Purchased.name and Purchased.description fields are taking up too much space. On average, 1 of 30 strings in these fields are unique, and 29 of 30 are duplicates.

I would like to avoid creating a HistoricalProduct table, as many facets of Product change on a regular basis, and that table would grow quite large, too.

Is there a way to get PostgreSQL to manage deduplication of these variable character fields automatically for me?

PostgreSQL has functions for regular expressions.
PostgreSQL string functions
So you can write triggers (function + dml event) to be done what you need. I don't believe it has some optimizer that compress strings across the records. Or maybe I didn't understand what you mean in the question.


How to check multidimensional array of arrays contains array? I have an array of arrays [ID, TYPE].

All values are integer. Array can be integer[] or jsonb type.

SELECT array[[442,2],[443,2]]

SELECT to_jsonb(array[[1,2],[3,4]])

How can I check this array(s) contains array [443,2]?

EDIT
One of the way to check described here.

1# SELECT array[[442,2],[443,2]] @> array[443,2] -- returns True
2# SELECT array[[442,2],[443,2]] @> array[2,443] -- returns True too

For my case, I want get False in 2#

Technically there is no such thing as a two-dimensional array in Postgres. Quote from the manual:

  The current implementation does not enforce the declared number of dimensions either. [...] So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior

So the value array[[442,2],[443,2]] is internally stored as array[442,2,443,2] and that's the reason why array[[442,2],[443,2]] @> array[2,443] is true, because it is internally represented as array[442,2,443,2] @> array[2,443] which obviously is true.

You can however do that if you declare an array of a row type.

create type idtype as (id integer, type integer);

Then you can do the following:

select array[(442,2), (443,2)]::idtype[] @> array[(442,2)]::idtype[];
--> true

but

select array[(442,2), (443,2)]::idtype[] @> array[(2,442)]::idtype[];
--> false


Is there a way to force Deferred Name Resolution even if the table exists when creating a stored procedure? When creating a stored procedure in SQL Server, you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that table (Deferred Name Resolution).

Is it possible to instruct SQL Server to defer the name resolution of all tables referenced in a procedure irrespective of whether they exist or not? I do want to keep the general syntax checking, so even if it were possible, hacking the stored procedure definition into a system table isn't an option.

I expect my asking to do this might seem a little bit weird, so here's some background: I auto generate table definitions and stored procedures from an application written in C# and it's very difficult for me to change the code to order the changes as SQL needs them. My code "guarantees" that the schema is consistent within a transaction, but currently I can't guarantee that the table columns are defined before I define the stored procedure which references them.

Below is a canonical example of the SQL created by the C# which "illustrates" the problem I'm trying to solve.

--Say this table already exists.
CREATE TABLE myTable
(
    a NVARCHAR(MAX)
)
GO

--My C# code creates something like this
BEGIN TRAN
GO

--the stored procedure gets generated first.
CREATE PROCEDURE mySproc
AS
BEGIN
    SELECT a,b FROM myTable
END

--then the table update
ALTER TABLE myTable
    ADD b nvarchar(MAX)

COMMIT TRAN

It is possible for me to fix this in the C# code, but I'm hoping for a simple "magic" tweak I can pull in the SQL. This will save a lot of time for me.

No.
I feel really guilty just typing that, but no, sadly.  That's the first time I've heard of this use case, and it makes perfect sense.  Best to submit a request for it on https://feedback.azure.com/forums/908035-sql-server and your grandchildren will be able to do it. ;-)


SSMS connects, Linked server fails There's a SQL Server 2012 machine which I have login and password. It's a SQL Server login, not an AD one.

I'm able to login using SSMS. But when I configure a linked server in my SQL Server I get error message saying this login doesn't have access:

  Cannot process the object ""DB"."SCHEMA"."TABLE"". The OLE DB provider "SQLNCLI11" for linked server "SERVER" indicates that either the object has no columns or the current user does not have permissions on that object.

Any idea why this is happening?

Update: This is the scripted creation of the linked server:

USE [master]
GO

/****** Object:  LinkedServer [REMOTE_SERVER_HOST]    Script Date: 16/03/2016 13:56:56 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'REMOTE_SERVER_HOST', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REMOTE_SERVER_HOST',@useself=N'False',@locallogin=N'LOCAL_LOGIN',@rmtuser=N'REMOTE_LOGIN',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER_HOST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

The called SP in the remote server should have "set nocount on" at the top. This will solve the issue; provided you have all the permissions required set.


Order by certain enum values first I've got a table like:
╔═══╦════════════════════╦═════════════╗
║ID ║ type (enum)        ║ updated_at  ║
╠═══╬════════════════════╬═════════════╣
║ 1 ║ friend_request     ║ <date_time> ║
║ 2 ║ new_article        ║ <date_time> ║
║ 3 ║ article_read       ║ <date_time> ║
║ 4 ║ article_invitation ║ <date_time> ║
╚═══╩════════════════════╩═════════════╝

I would like to order the retrieved results by the value of the enum type.
Records with value friend_request and article_invitation for the type column should come first, then the rest. Everything together should be ordered by updated_at DESC.
Now, I know I can chain multiple order by properties, so f.e.
order by type desc, updated_at desc

will order for me by type and the result of that by the updated_at values. But how do I specify for certain values of the type column? Is that even possible?

You can use a CASE expression:
order by case "type"
            when 'article_invitation' then 1
            when 'new_article' then 2
            when 'article_read' then 3
            when 'friend_request' then 4
         end,
         updated_at desc


Restore keeps failing I have a SQL Server 2014 instance with a ~900GB backup database that goes through a weekly restoration process from a full backup. The server it's on is a Windows Server 2012 R2.

I have recently started seeing the database fail to be restored each weekend.

There are 3 backup files that are copied across to the backup site, produced on the Sat evening around 8, taking only about an hour, copied down to this server just before midnight (usually taking 1-2 hours per file) then restored from at 6am the following morning.

I notice one of the backup files consistently has a date modified time of Sun 6:50, but the other two have it as Sat ~9pm. When attempting to restore to a dev server to verify the backup media it takes an exceptionally long time to read the backup device header for that file.

I have been and still currently am sifting through google results to see if I can work out an answer as well, but no luck so far. Does anyone have any suggestions of what I should be checking or ideas on what the problem might be?

The restore job is done from a procedure called by a PowerShell script. This is the email error I received the last Sunday morning for example:

  Sent: 30 October 2016 12:01
  To: email
  Subject: Server1 - Database Restore Failure

  The following database(s) have not restored correctly on Server1 -
  please investigate.

  Database1

The log file of the copy task just shows the following:

  10/30/2016 00:07:35 Copying databases matching *_database1_FULL_*.bak

The log file for restore task shows:

  10/30/2016 08:29:33 Restoring Database1 From \sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak
  10/30/2016 08:29:33 Restoring Database2 From \sharedfileserver\folder\AvailabilityGroup1_Database2_FULL_20161029_201026_1.bak
  10/30/2016 08:29:33 5 percent processed.
  (some time elapses...)
  10/30/2016 08:34:19 100 percent processed.
  10/30/2016 08:34:19 Processed 4946928 pages for database 'Database2', file 'DATABASE2' on file 1.
  10/30/2016 08:34:19 Processed 21 pages for database 'Database2', file 'DATABASE2_log' on file 1.
  10/30/2016 08:34:27 RESTORE DATABASE successfully processed 4946949 pages in 303.526 seconds (127.330 MB/sec).
  10/30/2016 08:34:27
  10/30/2016 08:34:27 3013 - RESTORE DATABASE is terminating abnormally.
  10/30/2016 08:34:27 -1
  10/30/2016 08:34:27 Done.
  10/30/2016 08:34:28 Finished execution

Note: It's Database1 that's the problem child.

I would verify the integrity of your backup files using the following commands.

Restore HEADERONLY

SQL BOL: Returns a result set containing all the backup header information for all backup sets on a particular backup device.

RESTORE HEADERONLY FROM DISK ='\\sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak'

You might find some information why your backup file is not working.

Restore FILELISTONLY

SQL BOL: Returns a result set containing a list of the database and log files contained in the backup set.

RESTORE FILELISTONLY FROM DISK = '\\sharedfileserver\folder\AvailabilityGroup1_Database1_FULL_20161029_201228_1.bak'

There was a rare occasion where the logical filename of the database was truncated in SQL Server 2008 R2

You might find more information in the ERRORLOG of the server you are restoring to. Otherwise I would consider running the restore script outside of the scheduled task in Query Window to catch any other errors.


Creating a pseudo email database I am creating a database for Job offers website between Employer and Applicants that required sending Email between the two in MSSQL. Let's say I have 3 tables like these:

Employer
ID int idetity(1,1),
Name nvarchar(50),
Email nvarchar(50)

Applicants
ID int identity(1,1),
Name nvarchar(50),
Email nvarchar(50)

Mail
MailID int identity(1,1),
Sender nvarchar(50),
Receiver nvarchar(50),
Title nvarchar(100),
Content nvarchar(1000),
Status nvarchar(50)

In Mail table, the Status indicate whether the Mail is sent, unread, read, or delete. Is this the right way to do this? Should I link the Mail table with Employer and Applicant? If so, should I link what with what? Since I want to get the sent Mail of Employer and Applicant, check whether mail is Read,Unread or deleted.

Any help is appreciate

I would create "Status" as a lookup table

SentStatus
StatusId smallint identity (1,1)
StatusLabel char (10)

As it is easier to maintain and also add values to.

Of course, you should link "mail" to "Employer" and "Applicant" table using a many-to-many relationship.

Be mindful of over-using "var" if you don't need it, and definitely of the difference between nchar and char: if you know in advance that "status" is going to be a label in English which you assign, you don't need to accommodate for "nchar" which is double in length.


What is the right data type to store only -1 0 and 1 in postgres column? I want to store only 3 states in a column (if it was 2, I would use a boolean)
I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space
Is there a better way to store only -1 0 and 1 in a PostgresQL column?

If you want to save space, you can use the "char" data type. It stores a single byte.

you can cast integer or text to "char":

SELECT 'u'::"char", 'd'::"char", 'n'::"char";

 char | char | char
------+------+------
 u    | d    | n
(1 row)

An enum uses 4 bytes since it is internally stored as a real.

What you are out to save space, you'll have to take alignment into account. The values are always aligned according to the type alignment. For example, a bigint always has to start at an address that is divisible by 8.

Now if your table is defined as

CREATE TABLE (
   smallflag "char",
   largenum  bigint
);

there will be 7 padding bytes between the columns, which would render all the space gains from "char" moot.

So place your table columns carefully.


How to insert and update bulk data From one DB to another There are two DBs on different servers. I want to run a procedure once a day so that I can get updated records. If it already exists then it should update, else insert a new row.

So far I did it like this:

if not exists (Select emp_id  From DB1.dbo.Emp_Master where emp_code = (Select pk_emp_code From DB2)
    Begin
    INSERT INTO DB1.dbo.Emp_Master(emp_code,emp_name,emp_dept ,emp_desg ,emp_mob ,email_id,emp_dob,emp_doj)
    Select pk_emp_code,first_name+ '' +last_name,dept.dept_desc,
     desg.desig_desc,office_phone,email,REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-') ,
    REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
    From DB2
        Select 1
    END
    ELSE
    BEGIN

    UPDATE DB1.dbo.Emp_Master SET emp_name=first_name+ '' + last_name,emp_dept=dept.dept_desc,emp_desg=desg.desig_desc,
    email_id=email,emp_mob=office_phone,emp_dob=REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-'),
    emp_doj=REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
    FROM [DB2]
    where emp_code =pk_emp_code
    Select 2
    END

I am getting error

  SubQuery return more than one value

What's gone wrong?

It's this part that returns the error (inside your IF NOT EXISTS):

Select emp_id  From DB1.dbo.Emp_Master where emp_code = (Select pk_emp_code From DB2)

There is no correlation between the inner and outer query. A possible rewrite is along these lines:

Select emp_id  From DB1.dbo.Emp_Master where emp_code IN (Select pk_emp_code From DB2)

Moreover, the check you're doing at the very beginning of your code will skip inserting all the rows if any of them matches.

I suppose that what you want to do here is an upsert (if matched updated, if not matched insert). You can do it with the MERGE statement or with something like this:

IF OBJECT_ID('tempdb..#updatedRows') IS NOT NULL
    DROP TABLE #updatedRows;

CREATE TABLE #updatedRows (
    emp_code int PRIMARY KEY CLUSTERED
);

UPDATE DB1.dbo.Emp_Master
SET emp_name = first_name+ '' + last_name,
    emp_dept = dept.dept_desc,
    emp_desg = desg.desig_desc,
    email_id = email,
    emp_mob = office_phone,
    emp_dob = REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-'),
    emp_doj = REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
OUTPUT INSERTED.emp_code INTO #updatedRows
FROM [DB2]
WHERE emp_code = pk_emp_code;

INSERT INTO DB1.dbo.Emp_Master(
    emp_code,
    emp_name,
    emp_dept,
    emp_desg,
    emp_mob,
    email_id,
    emp_dob,
    emp_doj
)
SELECT
    pk_emp_code,
    first_name + '' + last_name,
    dept.dept_desc,
    desg.desig_desc,
    office_phone,
    email,
    REPLACE(CONVERT(NVARCHAR,CAST(dob AS DATETIME), 6), ' ', '-') ,
    REPLACE(CONVERT(NVARCHAR,CAST(doj AS DATETIME), 6), ' ', '-')
FROM DB2
WHERE pk_emp_code NOT IN (
    SELECT emp_code
    FROM #updatedRows
);


Two-sided LIMIT in MySQL I have a table with 4 rows of data always in the same order. What I want to do is to limit the query to just get the 2th to 3th row.

for example a table 'food' with:

cake
pizza
chocolate
lettuce

SELECT * from food (what do i put here to only get the rows pizza and chocolate)

This should also work for getting for example the 4th to 8th row in a table with 10 rows.

The LIMIT clause intended to do what you need:

SELECT *
  FROM food
 LIMIT 1,2  -- offset, count
;

Refer to the SELECT syntax manual for details: https://dev.mysql.com/doc/refman/8.0/en/select.html


RegEx bug in MongoDB I run the following queries on a collection that have a lot of entries that contain [ and ].

 db.test.find({word:/[\[]/});           // could not be run in console
 db.test.find({word:/\[/ });            // could not be run in console
 db.test.find({word:/\]/});             //returns result
 db.test.find({word:/[\]]/});           //returns result
 db.test.find({word:{$regex:"\["}});    //no result
 db.test.find({word:{$regex:"\[[]"}});  //returns result

Why using \[ is such strange? Is it a bug?

I am using mongodb 2.2.0, perhaps

There are multiple issues here:

First, your console queries work fine in MongoDB v2.0.2 but don't work in MongoDB v2.2.1. This is probably a regression bug, but the JavaScript parser may have intentionally been changed between versions. The ... after entering a command means the console is waiting for a multi-line command to be completed; I'm not sure why in this specific situation.
Also, the backslash character \  in the $regex string must itself be escaped. (The JavaScript parser consumes the escape first, before the regex engine gets to see it.)

> db.test.find({word:{$regex:"\["}});    // no result
> db.test.find({word:{$regex:"\\["}});   // returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }

MongoDB 2.0.2 console output:

> db.test.drop(); version()
version: 2.0.2
>
> db.test.insert({_id: 0, word: '['});
> db.test.insert({_id: 1, word: '[]'});
> db.test.insert({_id: 2, word: ']'});
> db.test.insert({_id: 9, word: 'no brackets'});
>
> db.test.find({word:/[\[]/});           // could not be run in console
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:/\[/ });            // could not be run in console
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:/\]/});             //returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:/[\]]/});           //returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:{$regex:"\["}});    //no result
> db.test.find({word:{$regex:"\\["}});   //returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:{$regex:"\[[]"}});  //returns result
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }

MongoDB 2.2.1 console output:

> db.test.drop(); version()
version: 2.2.1
>
> db.test.insert({_id: 0, word: '['});
> db.test.insert({_id: 1, word: '[]'});
> db.test.insert({_id: 2, word: ']'});
> db.test.insert({_id: 9, word: 'no brackets'});
>
> db.test.find({word:/[\[]/});           // could not be run in console
... db.test.find({word:/\[/ });            // could not be run in console
... db.test.find({word:/\]/});             // returns result
... db.test.find({word:/[\]]/});           // returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:{$regex:"\["}});    // no result
> db.test.find({word:{$regex:"\\["}});   // returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:{$regex:"\[[]"}});  // returns result

MongoDB 2.2.1 console output (commented out lines that don't parse as expected):

> db.test.drop(); version()
version: 2.2.1
>
> db.test.insert({_id: 0, word: '['});
> db.test.insert({_id: 1, word: '[]'});
> db.test.insert({_id: 2, word: ']'});
> db.test.insert({_id: 9, word: 'no brackets'});
>
> // db.test.find({word:/[\[]/});        // could not be run in console
> // db.test.find({word:/\[/ });         // could not be run in console
> db.test.find({word:/\]/});             // returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:/[\]]/});           // returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:{$regex:"\["}});    // no result
> db.test.find({word:{$regex:"\\["}});   // returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:{$regex:"\[[]"}});  // returns result
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
>


SQL Server CASE statement dependent on other rows in result set I have some data that comes out of a cube.

I have one row in this order that has gone in as a substitution variance but I need to put it in the quantity variance bucket. The code that decides on whether it's a substitution is here at the lowest level (this is within a large CTE) (The code behind this cube is massive and exists on multiple levels)

CASE
   WHEN ISNULL(PC.ItemDimCostPrice, SC.ItemDimCostPrice) = 1
        AND (PC.ConfigId <> SC.ConfigId
             OR PC.InventColorId <> SC.InventColorId
             OR PC.InventSizeId <> SC.InventSizeId
             OR PC.InventStyleId <> SC.InventStyleId)
      THEN 1
   --New code here ... THEN 0
   WHEN PC.[Resource] IS NULL
        OR SC.[Resource] IS NULL
     THEN 1
   ELSE 0
END AS [HasSubstitutionVariance] ,

The logic that they have given me which I need to add into this is basically if the cost grouping is material (I know how to calculate this) and there exists another record with the same costgroupid and operationid (within the production order) then this will never be a substitution variance. I was thinking of using rownumber and partitioning on costgroupid and operationid but as far as I'm aware you can't use window functions in a case statement? Anyone have any ideas how I would do this? Thanks!

So this is what i came up with. Was fairly straightforward actually. So as I stated in the post that code was in a CTE. I left that code alone, later in the CTE I added

CASE WHEN MC.HasSubstitutionVariance = 1
                      AND ( COUNT(MC.CostGroupId) OVER ( PARTITION BY MC.ProdId,
                                                         MC.CostGroupId,
                                                         MC.OprId ) > 1
                            AND LEFT(MC.CostGroupId, 3) IN ( 'LAB', 'FOH',
                                                             'L T', 'F T' )
                          ) THEN 0
                 ELSE MC.HasSubstitutionVariance
            END AS HasSubstitutionVariance ,

Which says is the costgroup repeated and is it standard material, if so mark substitution variance as 0. I thought you couldn't use OVER within a CASE statement? Or is it only certain window functions you can't use within CASE?


Creating mongo 3 node replica set on ec2 problems I'm trying to create a 3 node cluster of mongo replicas on ec2 instances, with internal ips 10.168.xxx.xxx, 10.166.xxx.xxx and 10.188.xxx.xxx, my config file is:

dbpath = /home/ubuntu/mongo-data/
logpath = /home/ubuntu/mongo-data/mongod.log
logappend = true

journal = true
fork = true
smallfiles = true
pidfilepath = /home/ubuntu/mongo-data/mongod.pid
replSet = appNameReplicaSet

I launched mongod -f mongod.config on each instance, and on one of those I tried:

$ mongo
> rs.initiate();
{
"info2" : "no configuration explicitly specified -- making one",
"me" : "ip-10-168-66-132:27017",
"info" : "Config now saved locally.  Should come online in about a minute.",
"ok" : 1
}
> rs.add('10.166.xxx.xxx:27017');
//after LONG pause
{
"errmsg" : "exception: need most members up to reconfigure, not ok : 10.188.22.254:27017",
"code" : 13144,
"ok" : 0
}

I also tried:

config = {
    _id: 'appNameReplicaSet',
        members: [
        {_id: 0, host: '10.168.xxx.xxx:27017'},
        {_id: 1, host: '10.166.xxx.xxx:27017'},
        {_id: 2, host: '10.188.xxx.xxx:27017'}
    ]
}
rs.reconfig(config, {'force':true})
{ "ok" : 0, "errmsg" : "a replSetReconfig is already in progress" }

.log file:

Sat Jul 27 01:33:59.149 [initandlisten] connection accepted from 127.0.0.1:36931 #4 (1 connection now open)
Sat Jul 27 01:34:02.468 [conn4] replSet replSetInitiate admin command received from client
Sat Jul 27 01:34:02.470 [conn4] replSet info initiate : no configuration specified.  Using a default configuration for the set
Sat Jul 27 01:34:02.470 [conn4] replSet created this configuration for initiation : { _id: "appNameReplicaSet", members: [ { _id: 0, host: "ip-10-168-xxx-xxx:27017" } ] }
Sat Jul 27 01:34:02.470 [conn4] replSet replSetInitiate config object parses ok, 1 members specified
Sat Jul 27 01:34:02.473 [conn4] replSet replSetInitiate all members seem up
Sat Jul 27 01:34:02.473 [conn4] ******
Sat Jul 27 01:34:02.473 [conn4] creating replication oplog of size: 990MB...
Sat Jul 27 01:34:02.474 [FileAllocator] allocating new datafile /home/ubuntu/mongo-data/local.1, filling with zeroes...
Sat Jul 27 01:34:02.485 [FileAllocator] done allocating datafile /home/ubuntu/mongo-data/local.1, size: 511MB,  took 0.01 secs
Sat Jul 27 01:34:02.485 [FileAllocator] allocating new datafile /home/ubuntu/mongo-data/local.2, filling with zeroes...
Sat Jul 27 01:34:02.491 [FileAllocator] done allocating datafile /home/ubuntu/mongo-data/local.2, size: 511MB,  took 0.005 secs
Sat Jul 27 01:34:02.491 [conn4] ******
Sat Jul 27 01:34:02.491 [conn4] replSet info saving a newer config version to local.system.replset
Sat Jul 27 01:34:02.492 [conn4] replSet saveConfigLocally done
Sat Jul 27 01:34:02.492 [conn4] replSet replSetInitiate config now saved locally.  Should come online in about a minute.
Sat Jul 27 01:34:08.435 [rsStart] replSet I am ip-10-168-xxx-xxx:27017
Sat Jul 27 01:34:08.435 [rsStart] replSet STARTUP2
Sat Jul 27 01:34:09.441 [rsSync] replSet SECONDARY
Sat Jul 27 01:34:09.441 [rsMgr] replSet info electSelf 0
Sat Jul 27 01:34:10.440 [rsMgr] replSet PRIMARY
Sat Jul 27 01:34:26.513 [conn4] replSet replSetReconfig config object parses ok, 2 members specified
Sat Jul 27 01:37:45.835 [conn4] couldn't connect to 10.188.xxx.xxx:27017: couldn't connect to server 10.188.xxx.xxx:27017

UPDATE: added output of rs.initiate() & logs;

This is probably a firewall issue. The long pause is the socket connection hanging while it waits for it to timeout.

By default on EC2 the security groups (eg. firewall rules) for instances deny all inbound traffic. You have to explicitly specify what IP ranges or security group members are allowed to connect.

Using security groups instead of IPs is generally more maintainable. It allows you to add/remove additional servers later without worrying about updating the IP addresses that get dynamically assigned. You just need to include them in the same security group.

To get setup:

Create a security group specifically for your MongoDB instances. Let's call it "My MongoDB SG" and assume that AWS gives it the unique id sg-123456.
Edit the inbound firewall rules for the security group to allow traffic from the same security group on port. The entry should look like:

 Source         Port
 sg-123456      27017

Add the security group to each of your MongoDB instances

Then you can retry starting up the cluster.


In SQL Server, are there any other options of data Import and Export I've been working with SQL Server since SQL Server 7. So I know about BCP, Bulk insert, openrowset and SSIS.

My question is a simple one:

Do you know of any other ways to Import/Export data using SQL server
Express versions SQL 2005 to 2014 (and soon 2016).

I want to make sure that there isn't anything else due to the fact that the use of BCP (which is what I'm using right now) requires the use of xp_cmdshell and for security reasons, requires an xp_cmdshell_proxy account. I'm being challenged at work, and want to make sure nothing else exists, or that is new in Microsoft SQL Server (Express) before I respond.

SQLCMD can be used to export data. The default output is sometimes unwieldy for that, but you can use some command-line options to turn off the headers, trim trailing spaces, and to use a comma separator instead of the default space:

sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s ","

Returns:

sysrscols,3,S
sysrowsets,5,S
sysclones,6,S
sysallocunits,7,S
sysfiles1,8,S
sysseobjvalues,9,S
syspriorities,17,S
sysdbfrag,18,S
sysfgfrag,19,S
sysdbfiles,20,S

Use the -o option to send the output to a file:

sqlcmd -Q "SET NOCOUNT ON; SELECT TOP(10) name, object_id, type FROM sys.objects;" ^
  -h-1 -W -s "," -o C:\temp\SysObjects.txt

A custom .NET program (Console app, Windows app, or even Web app) can be created to import and/or export. For importing, since Table-Valued Parameters (TVPs) are not an option here, it is sometimes faster to transform/serialize the data into XML, and then execute a stored procedure that will accept the XML parameter and using the .nodes() function, deserialize it back into a rows that can be inserted via the INSERT INTO table (fields) SELECT x.value(),... FROM @XmlParam.nodes(); construct. Of course, you wouldn't want to do 1 million rows in this manner, so you would break the import file into batches of X rows and send 100 - 500 at a time. If there is a lot of data then maybe straight INSERT statements are better, but in that case you would want to wrap each batch of X rows into an explicit transaction using SqlTransaction.

Depending on how comfortable you (or someone you work with) are with .NET programming, you can use SQLCLR to build your own custom import and/or export functionality. This would work in all editions of SQL Server in all versions starting with SQL Server 2005.

Along these lines, there is a commercially available library of SQLCLR functions and procedures called SQL# (which I am the creator of). Some of the functions and stored procedures handle importing and exporting data. The File_SplitIntoFields Stored Procedure returns a result set where each row is a line from the input file, but each field in the file is a separate column. By default the datatype for each field is NVARCHAR(MAX), but there is an optional input parameter to specify what the exact datatype of each column should be. This makes it rather easy to do:

INSERT INTO dbo.MyImportTable (Col1, Col2, Col3, Col4)
  EXEC SQL#.File_SplitIntoFields
    @FilePath = N'',
    @RegExDelimiter = N',',
    @DataTypes = N'NVARCHAR(100),INT,DATETIME,BIT';

And there is an equivalent Stored Procedure called INET_SplitIntoFields that does the same thing, but rather than sourcing the data from a file, it streams it directly from a URL (hence no need to download the file locally first).

For exporting, there is a Stored Procedure called DB_BulkExport that combines many of the features of SQLCMD and SSIS. I created this after needing to do several exports and not being able to decide between SQLCMD and SSIS since each one has its pros and cons, and each one has one or two features that the other one doesn't have but all of those features were needed. For example, DB_BulkExport doesn't need any hard-coded output field specification and can handle SELECT * queries (something I couldn't figure out how to manage in SSIS but was easy with SQLCMD). It can also handle text-qualification of fields without having to update the source query to concatenate the double-quotes in there (which makes for a very ugly and hard to maintain query). In fact, it can be set to text-qualify ALL fields, or just fields that need it based on their datatype (strings, dates, GUIDs, etc).


How do I query this 20 million record view faster? For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking too much time.

Where should I look to improve the performance of this view?

The rough definition for the view is below. It includes thirteen tables and around thirty fields.

CREATE VIEW [dbo].[v_AllForSearch]
AS
SELECT
  FT.firstField AS [firstField]
, FT.fld_primary AS [fld_primary]
, FT.fld_thirdField AS [thirdField]
, FT.fld_fourthField AS [fourthField]
, ISNULL(ST.[fld_firstSearchField],'') AS [firstSearchField]
, ISNULL(TT.[fld_thirdSearch],'') AS thirdSearch
, ISNULL(TT.[fld_fourthSearch],'')AS fourthSearch
, ISNULL(TT.[fld_fifthSearch],'')AS fifthSearch
, ISNULL(FRT.[fld_sixthSearch],'') As [sixthSearch]
, ISNULL(FRT.[fld_seventhSearch],'') AS [seventhSearch]
, ISNULL(FRT.[fld_eightSearch],'')AS [eightSearch]
, ISNULL(FIT.[fld_nineSearch],'') AS [nineSearch]
, ISNULL(SIT.[fld_tenthSearch],'')AS [tenthSearch]
, ISNULL(SET.[fld_eleventhSearch],'') AS [eleventhSearch]
, ISNULL(ET.[twelthSearch],'')AS [twelthSearch]
, ISNULL(NT.[thirteenthSearch],'')AS [thirteenthSearch]
, ISNULL(NT.[fourteenSearch],'') AS [fourteenSearch]
, ISNULL(NT.[fifteenSearch],'') AS [fifteenSearch]
, ISNULL(NT.[sxteenSearch],'')  AS [sxteenSearch]
, ISNULL(NT.[seventeenSearch],'') AS [seventeenSearch]
, ISNULL(NT.[eighteenSearch],'')AS [eighteenSearch]
, ISNULL(TT.[ninteenSearch],'') AS [ninteenSearch]
, ISNULL(ELT.[twentySearch],'') AS [twentySearch]
, ISNULL(ELT.[twentyOneSearch],'') AS [twentyOneSearch]
, ISNULL(TWT.[twentyTwoSearch],'') AS [twentyTwoSearch]
, ISNULL(THT.twentyThree,'') AS [twentyThree]
, ISNULL(THT.twentyFour,'') AS [twentyFour]
, ISNULL(THT.twentyFive,'') AS [twentyFive]
, ISNULL(THT.twentySix,'') AS [twentySix]
FROM
      tblFirstTable AS FT
      LEFT JOIN [tblSecondTable] AS ST
            ON ST.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblThirdTable] AS TT
            ON TT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblFourthTable] AS FRT
            ON FRT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblFifthTable] AS FIT
            ON FIT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblSixthTable] AS SIT
            ON SIT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblSeventhTable] AS SET
            ON SET.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblEighthTable] AS ET
            ON ET.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblNinthTable] AS NT
            ON NT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblELTnthTable] AS TT
            ON TT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblEleventhTable] AS ELT
            ON ELT.[fld_primary] = FT.[fld_primary]
      LEFT JOIN [tblTwelthTable] AS TWT
                            ON TWT.[fld_id] = ELT.[fld_id]
              LEFT JOIN [tblThirteenthTable] AS THT
            ON THT.[firstField]= FT.[firstField]
WHERE fld_Status ..

A view is macro that expands. So if your view is a JOIN of 2 tables, the execution plan will show the 2 tables. The view is transparent.

This doesn't apply if the view is indexed/materialised. However then you wouldn't be asking this question.

So, what does the execution plan say? The DTA? Missing indexes dmv query? Most expensive dmv query?


Email SSMS Log Shipping Status Report We use Log Shipping to allow Business Analysis users to query data. I would like to email the Log Shipping Status report to users to see last time data is refreshed.

When I Right Click on Server--> Standard reports --> It says members of the SysAdmin Report can only view this.

How can I give access, or email Report Status to Users of the report? I prefer to keep the same color html format if possible.

You can schedule below SQL to run daily with a SQL Agent job or if you have SSRS you can easily schedule and format the report. It will give you most of the information from the standard reports.

select ls.primary_server,ls.primary_database,lsd.restore_delay,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server

from msdb.dbo.log_shipping_secondary ls
join msdb.dbo.log_shipping_secondary_databases lsd
  on lsd.secondary_id=ls.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lms
  on lms.secondary_id=lsd.secondary_id

For HTML email status report use this answer:  Here's how can you create the HTML body part of your mail


SQL Server replication subscriptions marked as inactive Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever?

It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription every time.

Note, I'm not talking about the subscriptions being marked as expired...just as inactive.

We have retention set to 0 (infinite). I'd rather have a big distribution database than have to reinitialize a 15GB database to a subscriber over the WAN. Anyway, the real issue here is that they are getting marked as inactive within just a few hours sometimes...or sometimes a few days...it seems sporadic. Is there any way to tell WHY SQL decided in its wisdom to mark them inactive?

Yes you can do that programatically using t-SQL and a sql agent job.

Basically, you have to query distribution..MSSubscriptions and check the status column value to be 0 (Inactive).

Status of the subscription:
0 = Inactive;
1 = Subscribed;
2 = Active

Below T-SQL Will help you :

-- Author: Kin Shah
-- Date: 4-1-2013
-- For dba.stackexchange.com
-- Good to find out
-- publisher_id
-- publisher_db
-- publication_id
-- subscriber_id
-- subscriber_db
select * From distribution..MSsubscriptions

--- based on the above values, run below statement
--- this can be run using SQLAgent job

if exists (select 1 from distribution..MSsubscriptions where status = 0)
begin
UPDATE distribution..MSsubscriptions
SET STATUS = 2
WHERE publisher_id = '--publisher_id -- will be integer --'
    AND publisher_db = '--publisher db name ---'
    AND publication_id = '--publication_id -- will be integer --'
    AND subscriber_id = '--subscriber_id -- will be integer ---'
    AND subscriber_db = '-- subscriber_db ---'
end
else
begin
print 'The subscription is not INACTIVE ... you are good for now .... !!'
end


How to install HANA DB on Oracle Linux 7? I want to know how to install SAP HANA DB 2.0 on Oracle Linux 7 including downloading, installing and the first connection establishing to the system from network. I've spent a few hours to find out how to do it, but haven't found all required information in one place.

To download SAP HANA DB server you need to download SAP HANA, Express Edition Download Manager first, because there's no direct links available for this server downloading.

For this Go through express edition registration first.

Go to your mailbox and click the link for account activation
Confirm usage terms and you'll see the next window where you could
download DM

Open Download Manager, specify Image as Binary Installer. If you use Windows as your desktop system, you could additionally select Clients (Windows) to connect to HANA server using hana client for windows (Image). Click Download to download required packages.

Copy hxe.tgz archive to Oracle Linux system. (I've put installer to shared folder on windows, and added this folder to /etc/fstab on Linux the next way:
//MyIPAddress/winshare /mnt/winshare cifs username=myuser,password=mypassword,rw,uid=1000,gid=1000 0 0

/mnt/winshare should be created first: sudo mkdir /mnt/winshare. And if you've just added it, you need to mount it: sudo mount /mnt/winshare
Copy: cp /mnt/winshare/hxe.tgz /tmp

Go to /tmp folder and extract it:
$ cd /temp && tar xvfz ./hxe.tgz

Go to /tmp/HANA_EXPRESS_20/DATA_UNITS/HDB_SERVER_LINUX_X86_64 folder and run command line installer using administrator privileges:
$ sudo ./hdblcm

Select install and server:
 SAP HANA Lifecycle Management - SAP HANA Express Edition 2.00.045.00.1575639312
 *******************************************************************************

 Choose an action

   Index | Action            | Description
   ----------------------------------------------
   1     | install           | Install new system
   2     | Exit (do nothing) |

 Enter selected action index [2]: 1

 SAP HANA Database version '2.00.045.00.1575639312' will be installed.

 Select additional components for installation:

   Index | Components | Description
   ------------------------------------------------------------------------------
   1     | all        | All components
   2     | server     | No additional components
   3     | afl        | Install SAP HANA AFL (incl.PAL,BFL,OFL) version 2.00.045.
   4     | epmmds     | Install SAP HANA EPM-MDS version 2.00.045.0000.1575663037

 Enter comma-separated list of the selected indices [1]: 2
 Enter Installation Path [/hana/shared]:
 Enter Local Host Name [hana]:
 Enter SAP HANA System ID [HXE]:
 Enter Instance Number [90]:
 Enter Master Password:
 Confirm Master Password:
 Enter ID of User Group (sapsys) [79]:

 ...
 Do you want to continue? (y/n): y
 ...

 Regenerating SSL certificates...
 Deploying SAP Host Agent configurations...
 Creating Component List...
 SAP HANA Express Edition System installed

Enable HANA DB port in firewall if you want to connect to it from network as it is in my case:
 $ sudo firewall-cmd --permanent --zone=public --add-port=39015/tcp
 $ sudo firewall-cmd --reload

Check connection from windows using telnet:
 telnet MyHanaDBServerIP 39015

Go to folder where you've downloaded hdb_client_windows_x86_64.zip archive, extract it, go to HDB_CLIENT_WINDOWS_X86_64 folder, run hdbsetup.exe installer and install the client

Open windows cmd and go to the location where you've installed the client: cd "c:\Program Files\sap\hdbclient" and run the next command to test connection to server:
hdbsql.exe -n MyHanaDBServerIP:39015 -i 90 -u SYSTEM

and execute a test select query:
select * from "SYS"."M_DATABASES";

Here I used password specified during installation.
Use $ su - hxeadm on your linux server to have access to HANA DB Server environment's variables, paths, utils.


Is it possible to use cross database queries in both Azure and SQL Server 2016? I'm working on a project that is designed to run on Microsoft's Azure platform. At present we have a few developers working locally on their own machines with the intent of being able to publish the solution to Azure when we go live. We have been using dynamic stored procedures (sprocs that are essentially quoted strings with the database name replaced) so that we can make cross-database calls, however it turns out that these don't work on Azure.

One solution seems to be to use elastic queries, using the SQL methodology below:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'encryptionpassword';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = 'username',
SECRET = 'password';

CREATE EXTERNAL DATA SOURCE mydb WITH
    (TYPE = RDBMS,
    LOCATION = 'path-to-database.database.windows.net',
    DATABASE_NAME = 'dbname',
    CREDENTIAL = SQL_Credential
) ;

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = mydb)

select * from dbo.tc_City

This solution seems the most elegant, as it removes the need for dynamic stored procedures, which can be horrible to debug and modify. There is one major caveat: it doesn't seem possible to use the elastic query methodology on our local SQL server 2016 installs without adding a third party database engine. We upgraded from 2012 recently because we thought it would be possible to use the same technique, but it turns out that we have to install something called PolyBase to do elastic queries, and that is only supported with external databases of different types, such as Hadoop, rather than being capable of referencing another database within the SQL 2016 engine.

PolyBase methodology:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = 'user',
SECRET = 'pass';

CREATE EXTERNAL DATA SOURCE Central WITH
    (TYPE = HADOOP,
    LOCATION = 'localhost',
    CREDENTIAL = SQL_Credential
);

CREATE EXTERNAL TABLE [dbo].[tc_City](
    [CityID] [uniqueidentifier] NOT NULL,
    [CityLabel] [varchar](50) NOT NULL,
    [County] [varchar](50) NOT NULL,
    [InsertionDate] [datetime] NOT NULL
) WITH ( DATA_SOURCE = Central)

select * from dbo.tc_City

As you can see, the code for accessing an external database via PolyBase ends up being mostly the same, with only the data source setting being different - which would mean we could essentially use one core set of SQL scripts/stored procedures and just run the relevant changes when publishing to Azure. Having to install Hadoop servers on our local machines just so that we can access another database within the same server without having to rewrite masses of SQL seems somewhat excessive and unnecessarily complicated.

Is there any way we can use the same methodology to access another database running on the same SQL server 2016 engine without having to write different SQL for the stored procedures running locally and those running in Azure? e.g. we don't want to have to write the "create external table ..." on Azure and reference the data in that table via dbo.external_tablename, whilst having to locally refer to databasename.dbo.external_tablename - as that would mean writing two different sets of stored procedures.

Can the external data source be referenced by file instead, if that allows all of the subsequent SQL to be the same?

The 'New Polybase connectors' added in SQL Server 2019 CTP 2.0 give you what you're looking for.  These are new in 2019, and are not in the 2016 and 2017 releases.

See 'New PolyBase connectors' here:

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions#databaseengine


SQL Server Transactional Replication delay I am running into strange situation currently. I have a transactional replication configured between 2 servers. When I update a record in one article it gets replicated after 30 mins. I am unable to figure out why.

Log reader and distributor agent is setup to run continuously. There is no replication latency/errors found in replication monitor. But still it happens.

A tracer token is very fast. It just shows 5 secs as overall latency. The article I am updating is added into multiple publication and my environment is SQL Server 2012. No blocking. SQL Server is running fine. I don't see any resource intensive queries running on the server.

I would appreciate if any one could help me figure out this issue.

If the tracer token goes through in about 5 seconds and your updated data is taking 30 minutes, then the data has not been committed on the publisher yet. The tracer token would not have gone through if there was an un-replicated row ahead of it. Run a trace or an XE session on the publisher to see what is going on there.


Mysql : user with privileges to create databases, users and grant all privileges on those dbs to users Is it possible in mysql to create user who is able to create database, user and grant all privileges on this database to that user?I dont want to use root user in script

There is nothing special about "root".
CREATE USER foo@localhost ...;   -- establish the existence and pwd of the user
GRANT CREATE DATABASE     -- so foo can do CREATE DATABASE
      ...                 -- and other things (as needed)
    ON *.*                -- probably have to give permission at this level
    TO foo@localhost      -- the user
    WITH GRANT OPTION;    -- so foo can do GRANTs

See also SUPER -- decide whether or not you want this non-user to have all the privileges it entails.
For security reasons, only @localhost should be used with WITH GRANT OPTION.


mysql - How to repair & backup archive table, also stop growing .ARN extension file? I have an MySQL table build with archive storage engine.  The table has over 100k records.

I have got disk full and it turned out to be a large .ARN file from an archive table. When I cleared some space, the file continued to grow until the space was consumed.

Right now the .ARN file is over 16GB and it's growing on every insert. The corresponding .ARZ file is just over 8MB.

I cannot fetch records from archive table, as the table data is corrupted.
When I tried to repair table it says "Incorrect key file for table" which means File system out of space.

But I am left with no disk space, so I have to repair and backup this table on my local machine.

How can I repair archive table, backup data from archive table and free some disk space? Why .ARN file keeps growing on every insert?

This is a very tricky question because of the internals of the ARCHIVE storage engine.
People have asked this same question in the MySQL Forums

Jan 04, 2014 : Huge and growing archive with ARN extension
Jan 28, 2015 : large .arn file filling up whole hard drive

What needs to be understood is the file layout of an ARCHIVE table:

.frm : Every table in MySQL has a .frm regardless of storage engine
.ARZ : Table data
.ARM : Table metadata
.ARN : Optimization File

Let's start with .ARZ. What's the Z stand for ??? zlib
Why zlib ??? MySQL Documentation says

Storage: Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/). You can use OPTIMIZE TABLE to analyze the table and pack it into a smaller format (for a reason to use OPTIMIZE TABLE, see later in this section). The engine also supports CHECK TABLE. There are several types of insertions that are used:
An INSERT statement just pushes rows into a compression buffer, and that buffer flushes as necessary. The insertion into the buffer is protected by a lock. A SELECT forces a flush to occur.
A bulk insert is visible only after it completes, unless other inserts occur at the same time, in which case it can be seen partially. A SELECT never causes a flush of a bulk insert unless a normal insert occurs while it is loading.

Some compression is happening to data that is being inserted. If your .ARN file is growing, it must be doing some compression related work on every INSERT in terms of encoding/encryption.
Note the zlib.net Technical Details says under the subheading Maximum Compression Factor:

Empirically, the deflate method is capable of compression factors exceeding 1000:1. (The test case was a 50MB file filled with zeros; it compressed to roughly 49 KB.) Mark loves to calculate stuff like this and reports that the theoretical limit for the zlib format (as opposed to its implementation in the currently available sources) is 1032:1. To quote him,
The limit comes from the fact that one length/distance pair can represent at most 258 output bytes. A length requires at least one bit and a distance requires at least one bit, so two bits in can give 258 bytes out, or eight bits in give 1032 bytes out. A dynamic block has no length restriction, so you could get arbitrarily close to the limit of 1032:1.
He goes on to note that the current implementation limits its dynamic blocks to about 8 KB (corresponding to 8MB of input data); together with a few bits of overhead, this implies an actual compression limit of about 1030.3:1. Not only that, but the compressed data stream is itself likely to be rather compressible (in this special case only), so running it through deflate again should produce further gains.
By way of comparison, note that a version of run-length encoding optimized for this sort of unusual data file -- that is, by using 32-bit integers for the lengths rather than the more usual 8-bit bytes or 16-bit words -- could encode the test file in five bytes. That would be a compression factor of 10,000,000:1 (or 10.000.000:1 for you Europeans, or 107:1 for all of you engineers and scientists whose browsers support superscripts).
Finally, please note that this level of compression is extremely rare and only occurs with really trivial files (e.g., a megabyte of zeros). More typical zlib compression ratios are on the order of 2:1 to 5:1.

Give the presence of .ARN that is growing, it must be a temp table used to determine how row data is compressed and then cached to disk. (ARCHIVE does not cache data in memory).
At this point, your problem is at the compression layer of the storage engine.
WHAT TO DO NEXT
Run FLUSH TABLES; and copy the .frm, .ARM, and .ARZ to another server that has the same OS server. Do not copy from Linux to Windows. Try running REPAIR TABLE there.
If you still cannot read the data, you may have to do some deeper diving. You could try downloading archive_reader.c  (from Twitter), compile it and try to read your data.
Godspeed, Spiderman !!! (My Disclaimer).
ONCE YOU HAVE RECOVERED YOUR DATA
SUGGESTION #1
Do not use the REPLACE command against an ARCHIVE table. Why ???

DELETE is not supported for an ARCHIVE table
REPLACE is supported for an ARCHIVE table, requires INSERT and DELETE privs
REPLACE is a mechanical DELETE and INSERT

If ARCHIVE does not expose the DELETE operation, but allows REPLACE to secretly do so, this could impact performance greatly.
SUGGESTION #2
SELECTs and INSERTs can peacefully coexist within an ARCHIVE table. Of course, the one and only exception would be if you insert a new row and SELECT that same row concurrently.
SUGGESTION #3
Get a bigger data disk and a lot more RAM. This should give zlib a lot more head room for data compression in RAM before resorting to using the .ARN.


Simplest way to constrain an SSIS Execute Process Task We have some complex SSIS packages, currently in SSIS 2005, that use Execute Process Task in order to call out to do some business logic.

Some of these called programs are not as stable as one could wish for.

What is the simplest way, therefore, to constrain the execution in time, or other metric?

For example, nine times out of ten, the program executes (correctly) in ten seconds.  If it runs more than five minutes, kill it and presume it returned an error.

Please presume that changing the programs is not an option.

Use

TimeOut

Specify the number of seconds that the process can run. A value of 0 indicates that no time-out value is used, and the process runs until it is completed or until an error occurs.

TerminateProcessAfterTimeOut

Indicate whether the process is forced to end after the time-out period specified by the TimeOut option. This option is available only if TimeOut is not 0.

more about Execute Process Task Editor (Process Page)


Can I use a Clob as a table key (from Java)? Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)?

I know it is probably a very bad idea to use a CLOB as a key, but I do need to use large text as a key on a one-shot operation.

The simple version would be to generate a hash from the CLOB and use this as a key. This key will fit into the allowed key width for your engine (900 SQL Server, 767 InnoDB, 1000 MyISAM etc).

The hash can be generated by the engine as a computed column, trigger, or by some ETL process, or by the application

There is a faint chance of collision (birthday problem) but this would only matter for many records and poor choice of hashing algorithm