Can I restore a database to a point of time without backup? I have an SQL 2005 database with logging set to FULL but without any backups. Is it possible to restore it to a point of time?

I had a database using the simple recovery model, that had never been backed up.

I deleted some things that I shouldn't have and came to this thread and after reading it my heart was in my throat thinking of #1 telling the client and #2 all the work I'd be putting in to replace the data.

I continued to have a look on google and found ApexSQL recover. Link

I detached the database, took a copy of the detached data and log files then reattached the original and then ran the program. I told it the database and pointed it at the copy of the detached log file. It found all the deletions and scripted the recovery.

All in all, I'm very happy with the product and would recommend it to anyone. There is a 14 day trial, but it only recovers 1 in 10 rows.


How do I select data with a case statement and group by? I'm trying to manipulate data a little by using a case statement but can't seem to figure it out.

Stops
--------------------
1Stop
1-Stop
1 stop
1 Stop
1stop
1 Stop
2-Stop

Im trying to come up with:

1-Stop.... 6
2-Stop.... 1

What I've tried is:

select CASE when
Stops = '1Stop' OR
Stops = '1 Stop' OR
Stops = '1 stop' then '1-Stop'
ELSE Stops END, count(*)
from table group by Stops

The problem is that you can't use the alias Stops in the GROUP BY. In your query when you are using the GROUP BY it is using the individual values for each row. You'd need to use a subquery to get the result:

select stops, count(*) Total
from
(
  select
    CASE
      when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
      ELSE Stops
    END as Stops
  from yourtable
) d
group by stops;

See SQL Fiddle with Demo.

Or if you don't want to use a subquery, then you could repeat the CASE expression in the GROUP BY:

select
  CASE
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
    ELSE Stops
  END as Stops,
  count(*)  as Total
from yourtable
group by
  CASE
    when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
    ELSE Stops
  END

See SQL Fiddle with Demo


Execute Subquery once only How do I execute a Subquery once in a Query like this

http://sqlfiddle.com/#!9/8b149d/1/0

select
id,
(
  select group_concat(user)
  from table_b
) as subquery
from table_a

to maintain performance of the Query, Or it is better If I run two Queries separatly?

I want a result as if I used

select group_concat(user) from talbe_b;
select id from table_a;

but in a single query.

If you want a single result set with the first row containing a list of all users from table_a, and subsequent rows listing all ids from table_b (one per row), you could UNION the two queries. However, you would have to cast the results to a common data type (presumably something like varchar), and you would technically need to include a sort order field to guarantee that the users are in the first row.

SELECT values
FROM (select group_concat(user) as values, 1 as sort_order from table_b
      UNION ALL
      select CAST(id as varchar) as values, 2 as sort_order from table_a
     ) sq
ORDER BY sort_order
;

This will almost certainly be at least slightly slower than running the two queries separately. Not sure if that will matter, in your circumstance.

(Note:  UNION checks for and eliminates duplicate rows; UNION ALL just combines all rows without checking. Since we don't need to worry about duplicates....)


Unable to query SQL server metadata through linked server I have a scenario where a user wants to do the following through a linked server:

SELECT *
FROM [DBTEST].[AdventureWorks].sys.objects
WHERE object_id = OBJECT_ID(N'dbo.DatabaseLog');

The linked server is configured to use Be made using this security context and the linked server login has read access on the [DBTEST].[AdventureWorks] database.

The above query does not return any rows when executed through the linked server. However, when I login directly to DBTEST using the linked server login and do the following, it does return a row:

SELECT *
FROM [AdventureWorks].sys.objects
WHERE object_id = OBJECT_ID(N'dbo.DatabaseLog')

I am confused why it is not working through the linked server and what I need to do in order to fix it.

One more strange thing is even if I grant sa rights to the linked server login it still does not return any rows when executed through the linked server.

Both servers are SQL Server 2008 Enterprise Edition.

The issue is not security related :-).

In your query, you are selecting from a table on a remote system, but the WHERE condition is using the OBJECT_ID() function, which can only see the local instance, and is not running in the scope of the Linked Server connection.

Why don't you try:

select *
from [DBTEST].[AdventureWorks].sys.objects
where [name] = N'DatabaseLog';

And that query makes more sense anyway since you already had the name, so no sense in querying to get the ID from the name just to use the ID in a second query ;-).

If you want / need to also filter on the Schema name, then you will have to add the JOIN to the [DBTEST].[AdventureWorks].sys.schemas table as OBJECT_SCHEMA_NAME is also local scope only.


Oracle HIPAA/FIPS-Compliant Encrypted Connections I'm currently evaluating encrypting data in-transit for an Oracle SE2 12.1 Amazon RDS instance in accordance with HIPAA requirements.
Specifically, I'm considering the use of Oracle Native Network Encryption [1,2] (the alternative option being Oracle SSL [3,4]). I'm trying to piece together the documentation from Oracle, Amazon, HIPAA, NIST, etc. to determine the appropriate HIPAA-compliant approach.
From Amazon's HIPAA Whitepaper [5]:

Connections to Amazon RDS for Oracle containing PHI must use transport encryption and evaluate the configuration for consistency with the Guidance. This is accomplished using Oracle Native Network Encryption and enabled in Amazon RDS for Oracle option groups.

From the HIPAA Guidance [6]:

Valid encryption processes for data in motion are those which comply, as appropriate, with NIST Special Publications 800-52, Guidelines for the Selection and Use of Transport Layer Security (TLS) Implementations; 800-77, Guide to IPsec VPNs; or 800-113, Guide to SSL VPNs, or others which are Federal Information Processing Standards (FIPS) 140-2 validated.

Based on this doc, it seems that the Oracle network encryption must be FIPS 140-2 validated. However, I'm not certain whether the Oracle network encryption meets this criteria; I haven't quite been able to confirm this from the Oracle/FIPS/other docs.
In particular, I've found the following Oracle 12.1 documentation related to FIPS 140-2:

The FIPS 140-2 cryptographic libraries are designed to protect data at rest and in transit over the network.
Oracle Database uses these cryptographic libraries for Secure Sockets Layer (SSL), Transparent Data Encryption (TDE), and DBMS_CRYPTO PL/SQL package.
To configure Transparent Data Encryption and the DBMS_CRYPTO PL/SQL package program units to run in FIPS mode, set the DBFIPS_140 initialization parameter to TRUE.
You can configure the SSLFIPS_140 parameter for Secure Sockets Layer.

However, given the documentation, I'm not sure that these FIPS parameters apply to the Oracle native network encryption, nor whether they are necessary.

Is Oracle native network encryption [1,2] HIPAA/FIPS compliant?
If so, is any configuration required or is it compliant by default?
Are there certain encryption algorithms that are/are not compliant?

[1] http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html
[2] http://docs.oracle.com/cd/E11882_01/network.112/e40393/asoconfg.htm#ASOAG9581
[3] http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.SSL.html
[4] http://docs.oracle.com/cd/E11882_01/network.112/e40393/asossl.htm#ASOAG9689
[5] https://d0.awsstatic.com/whitepapers/compliance/AWS_HIPAA_Compliance_Whitepaper.pdf
[6] https://www.hhs.gov/hipaa/for-professionals/breach-notification/guidance/index.html

SSLFIPS_140 is used for SSL-based encryption. That is not related to native network encryption.

  Is Oracle native network encryption [1,2] HIPAA/FIPS compliant?

  If so, is any configuration required or is it compliant by default?

https://docs.oracle.com/database/121/DBSEG/asoappe.htm#DBSEG1061

  For native encryption, this behavior of cryptographic operations
  landing in RSA/Micro Edition Suite (MES) and being accelerated is
  similar to the above, except that it is determined by the FIPS_140
  setting in sqlnet.ora (instead of the SSL_FIPS140 setting in
  fips.ora).

This parameter is available since version 12.1.

  Are there certain encryption algorithms that are/are not compliant?

https://docs.oracle.com/database/121/DBSEG/asoappe.htm#DBSEG9828

  Only the following cipher suites are approved for FIPS validation:

  SSL_DH_anon_WITH_3DES_EDE_CBC_SHA
  SSL_RSA_WITH_AES_256_CBC_SHA
  SSL_RSA_WITH_AES_128_CBC_SHA
  SSL_RSA_WITH_AES_256_GCM_SHA384
  SSL_RSA_WITH_3DES_EDE_CBC_SHA

Could not find a list specifically for native encryption, but you can set the algorithms based on the above. After setting the above parameter, if you choose an algorithm that is not compliant, you can not connect to the database and will receive the below error:

ORA-12630: Native service operation not supported


Why do I get ORA-27102 ON WINDOWS 64 BIT if I increase the memory to 2.5GB BANNER

  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
  Production PL/SQL Release 11.2.0.2.0 - Production CORE    11.2.0.2.0
  Production TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
  NLSRTL Version 11.2.0.2.0 - Production   Windows Server 2008 R2
  Enterprise
  6.00 GB RAM 64-bit operating system

Current memory settings

*.memory_max_target=1536M
*.memory_target=1024M

When I try to increase the memory_max_target to 2.5 GB I get the below error on startup

  ORA-27102: out of memory OSD-00022: additional error information
  O/S-Error: (OS 1450) Insufficient system resources exist to complete
  the requested

  SQL> startup
  pfile='C:\oracle\app\product\11.2.0\dbhome_1\database\INITorcl.ORA';

Could some one shed some light.

How much real memory free does Windows task manager show, before you start up Oracle?  If less than 2.5 GB, that's the problem.  You can try increasing memory_max_target slowly from its current value, say in 256 MB increments, until it errors again, then I'd back it off by maybe 512 MB to give some headroom.


SELECT ta.Branch, tm.Description, COALESCE(ta.Values, 0) Values
FROM tbMonths tm
LEFT JOIN tbAccountValues ta ON ta.Month = tm.Description

The reason this works is that a LEFT JOIN requires two columns that form a relationship (be that PK/FK or otherwise). These columns have to contain identical values to JOIN the rows together.

In your example the tbAccountValues.Month and tbMonths.Description columns seem to contain the same values, so a JOIN can be made on those columns.

  It almost works though the problem is the branch column becomes null for those with 0 values

WITH tbBranches AS (SELECT DISTINCT Branch FROM tbAccountValues),
tbBranchesMonths AS (SELECT tB.Branch, tM.Description FROM tbBranches tB, tbMonths tM)
SELECT tBM.Branch, tBM.Description, COALESCE(tA.[Values], 0) [Values]
FROM tbBranchesMonths tBM
LEFT JOIN tbAccountValues tA ON tA.Month = tBM.Description
                            AND tA.Branch = tBM.Branch

fiddle


Sort order specified in primary key, yet sorting is executed on SELECT I'm storing sensor data in a table SensorValues. The table and primary key is as follows:

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

Yet, when I select the sensor value valid for a specific time the execution plan tells me it is doing a sort. Why is that?

I would have thought that since I store the values sorted by the Date column, the sorting would not occure. Or is it because the index isn't solely sorted by the Date column, i.e. it can't assume that the result set is sorted?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

Edit: Can I do this instead?

Since the table is sorted DeviceId, SensorId, Date and I do a SELECT specifying only one DeviceId and one SensorId, the output set should already be sorted by Date DESC. So I wonder if the following question would yield the same result in all cases?

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

According to @Catcall below, the sort order is not the same as the storage order. I.e. we can't assume that the returned values are already in a sorted order.

Edit: I've tried this CROSS APPLY solution, no luck

@Martin Smith suggested I'd try to OUTER APPLY my result against the partitions. I found a blog post (Aligned non-clustered indexes on partitioned table) describing this similar problem and tried the somewhat similar solution to what Smith suggested. However, no luck here, the execution time is on par with my original solution.

WITH Boundaries(boundary_id)
AS
(
  SELECT boundary_id
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
  UNION ALL
  SELECT max(boundary_id) + 1
  FROM sys.partition_functions pf
  JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
  WHERE pf.name = 'PF'
  AND prf.value <= 1339225010
),
Top1(SensorValue)
AS
(
  SELECT TOP 1 d.SensorValue
  FROM Boundaries b
  CROSS APPLY
  (
    SELECT TOP 1 SensorValue
      FROM SensorValues
      WHERE  SensorId = 53
        AND DeviceId = 3819
        AND "Date" < 1339225010
        AND $Partition.PF(Date) = b.boundary_id
        ORDER BY Date DESC
  ) d
  ORDER BY d.Date DESC
)
SELECT SensorValue
FROM Top1

For a non partitioned table I get the following plan

There is a single seek predicate on Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010.

Meaning that SQL Server can perform an equality seek on the first two columns and then begin a range seek starting at 1339225010 and ordered FORWARD (as the index is defined with [Date] DESC)

The TOP operator will stop requesting more rows from the seek after the first row is emitted.

When I create the partition scheme and function

CREATE PARTITION FUNCTION PF (int)
AS RANGE LEFT FOR VALUES (1000, 1339225009 ,1339225010 , 1339225011);
GO
CREATE PARTITION SCHEME [MyPartitioningScheme]
AS PARTITION PF
ALL TO ([PRIMARY] );

And populate the table with the following data

INSERT INTO [dbo].[SensorValues]
/*500 rows matching date and SensorId, DeviceId predicate*/
SELECT TOP (500) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values
UNION ALL
/*700 rows matching date but not SensorId, DeviceId predicate*/
SELECT TOP (700) 3819,52,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values
UNION ALL
/*1100 rows matching SensorId, DeviceId predicate but not date */
SELECT TOP (1100) 3819,53,1, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) + 1339225011
FROM master..spt_values

The plan on SQL Server 2008 looks as follows.

The actual number of rows emitted from the seek is 500. The plan shows seek predicates

Seek Keys[1]: Start: PtnId1000 <= 2, End: PtnId1000 >= 1,
Seek Keys[2]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010

Indicating it is using the skip scan approach described here

  the query optimizer is extended so that a seek or scan operation with
  one condition can be done on PartitionID (as the logical leading
  column) and possibly other index key columns, and then a second-level
  seek, with a different condition, can be done on one or more
  additional columns, for each distinct value that meets the
  qualification for the first-level seek operation.

This plan is a serial plan and so for the specific query you have it seems that if SQL Server ensured that it processed the partitions in descending order of date that the original plan with the TOP would still work and it could stop processing after the first matching row was found rather than continuing on and outputting the remaining 499 matches.

In fact the plan on 2005 looks like it does take that approach

I'm not sure if it is straight forward to get the same plan on 2008 or maybe it would need an OUTER APPLY on sys.partition_range_values to simulate it.


Is it safe to delete dummy tables Oracle 11g XE i created a database schema in 11g express edition, however there are some APEX tables and some dummy data...

Is it safe to delete them? also i would like to know what does apex means and why they have a dollar sign, thanks...

APEX$_ACL                      TABLE
APEX$_WS_FILES                 TABLE
APEX$_WS_HISTORY               TABLE
APEX$_WS_LINKS                 TABLE
APEX$_WS_NOTES                 TABLE
APEX$_WS_ROWS                  TABLE
APEX$_WS_TAGS                  TABLE
APEX$_WS_WEBPG_SECTIONS        TABLE
APEX$_WS_WEBPG_SECTION_HISTORY TABLE

I used SYS to create a new user, the new user was created along with a workspace and a database schema, please see the images above.

I wouldn't suggest removing those tables, no.  The Oracle XE browser-based interface is written in APEX (Application Express, a framework for developing web-based apps in the Oracle database).  If you break APEX, that interface will likely stop working.


In sys.dm_os_memory_cache_entries view, what does original cost mean? Looking at a couple of DMVs, and trying to see if I can programmatically figure out the cost of the cached plans in my SQL Server.  I'm hoping that column represents the same concept of cost that is used by the optimizer to determine if the query should be parallelized, but I'm not sure.  Is that the case?

Edit: below is the script I'm using:

SELECT  text
      , objtype
      , refcounts
      , usecounts
      , size_in_bytes
      , disk_ios_count
      , context_switches_count
      , original_cost
      , current_cost
FROM    sys.dm_exec_cached_plans p
        CROSS APPLY sys.dm_exec_sql_text(plan_handle)
        JOIN sys.dm_os_memory_cache_entries e
          ON p.memory_object_address = e.memory_object_address
WHERE   cacheobjtype = 'Compiled Plan'
        AND type IN ( 'CACHESTORE_SQLCP', 'CACHESTORE_OBJCP' )
ORDER BY objtype DESC
      , usecounts DESC;

From the documentation for sys.dm_os_memory_cache_entries, this represents the combined CPU + I/O cost that the optimizer uses both for the value of the plan (and those metrics) and also to determine how quickly to evict those plans from the cache. Though, for your use case, I suspect current_cost may be more relevant. Be curious to see what comes next - how exactly are you going to use this information?


difference between create constraint non clustered and create index non clustered I am new to sql server. i was trying to learn creating index. i tried to create a table with below query:

CREATE TABLE [Ref].[Country](
    [CountryId] [uniqueidentifier]  NOT NULL,
    [CountryName] [varchar](50) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_CountryName] UNIQUE CLUSTERED ([CountryName] ASC),
    CONSTRAINT [PK_Country] PRIMARY KEY ([CountryId])
)

The non clustered index got created automatically on primary key. Now, i want to create NCI on Primary key column with INCLUDE. I did create it with below query:

CREATE UNIQUE NONCLUSTERED INDEX [NCI_CountryId] ON [Ref].[Country]
(
    [CountryId] ASC
)
INCLUDE ([CountryName])

As you can see in below image, there is two NCI on same column that is CountryId, one is created by default while creating a table(PK_Country) and another which i created with INCLUDE using above query(NCI_CountryId).

I do not want two NCI's on same column, need only NCI created with INCLUDE. Is there a way to create NCI with include on PK at time of creating table ?

I'm working on sql server on Azure VM.

With this:

CREATE TABLE [Ref].[Country](
    [CountryId] [uniqueidentifier]  NOT NULL,
    [CountryName] [varchar](50) NOT NULL,
    [CreatedDate] [datetime2](2) NOT NULL,
    [ModifiedDate] [datetime2](2) NULL,
    CONSTRAINT [UCI_CountryName] UNIQUE CLUSTERED ([CountryName] ASC),
    CONSTRAINT [PK_Country] PRIMARY KEY ([CountryId])
)

  i want to create NCI on Primary key column with INCLUDE.

You already have it.  A non-clustered index always contains the "row locator", which for a table with a clustered index is the clustered index key.  The leaf level of the non-clustered PK with have (CountryId,CountryName) rows, but sorted by CountryId, whereas in the clustered index the rows are sorted by CountryName.

For a unique index the clustered index columns are stored as "included columns" only on the leaf level.  For a non-unique index the clustered index columns are added as trailing index keys.

But you cannot include additional columns in a Primary Key constraint.  You can implement the identical physical design by adding a unique, non-clustered index with included columns.

Also there's nothing wrong with using a UNIQUEIDENTIFIER for the clustered index key, so long as your table isn't large, or you generate the value with NEWSEQUENTIALID().


Merging two selects then sort by and remove duplicates I have two select statements. Is it possible to merge the two results together and remove duplicates from the final merged result list (as opposed to treating the queries separately).

Query 1

SELECT DISTINCT
    UM.UMKEY,
    OS.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM (OS LEFT JOIN UM ON OS.HOMEKEY = UM.UMKEY)
LEFT JOIN UM AS MUM ON OS.MAILKEY = MUM.UMKEY
WHERE
    (OS.OS_EMAIL != '')
    AND (UM.INTERNATIONAL != 'Y')
    AND (OS.DECEASED != 'Y')
    AND (OS.GENDER  = 'F')
    AND (OS.MAIL_LIST = 'Y')
ORDER BY UM.UMKEY ASC

Query 2

SELECT DISTINCT
    UM.UMKEY,
    DF.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMILY = DF.DFKEY)
INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE
    ST.STATUS = 'FULL'
UNION
SELECT DISTINCT
    UM.UMKEY,
    DF.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMB = DF.DFKEY)
INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE ST.STATUS = 'FULL'
ORDER BY UM.UMKEY ASC

UNION is definitely the way to "stack" rowsets on top of each other, while removing duplicates. (Use UNION ALL to keep duplicates.)

Your queries appear to have some cleanup possible, so we'll do that as well.

Observations:

In the first query, the extra join to UM AS MUM seems unnecessary because no columns are used from that table, and due to the likely uniqueness of UMKEY it won't have multiple matching rows, so there is no effect at all on the final query. I'm removing this join.
The second query's two parts are almost the same and could be combined by using the single join condition ON DF.DFKEY IN (ST.FAMILY, ST.FAMB). Depending on your indexes and other factors, simplifying this way could be okay performance or could be worse. Or, querying twice as you're doing could be best. Instead, I've chosen a way that has at times beaten the performance of both of those for me. (And for what it's worth, my guess is that these two columns represent denormalized data, and refactoring a bit to put the family information into a separate table could be a sensible way to go forward—what if you had FAMC, FAMD, FAME, and so on?)
UM.INTERNATIONAL != 'Y' effectively makes the join to UM an INNER JOIN. So we'll use INNER JOIN to that table.
For what it's worth, the extra and unneeded parentheses add clutter, not clarity (to me, anyway), so I'll be removing them in my answer.
Some of the duplication in each query can be made common, such as the join to UM being the same.
I would encourage you to, where possible, use equality instead of inequality, as it's easier for the mind to comprehend (especially don't use double negatives). If your Y/N columns do not have NULLs and only contain the values Y and N, I recommend switching from OS.DECEASED != 'Y' to OS.DECEASED = 'N' and from UM.INTERNATIONAL != 'Y' TO UM.INTERNATIONAL = 'N'. This is probably merely a matter of style as it likely makes no difference to the query engine (depending on its cardinality estimates and other factors), though there is the possibility that an equality predicate could in some situations perform better than an inequality one.

I believe this query will return exactly the same rowset as you get with the other answers, but it has the potential for running in 1/2 to 1/3rd the time.

SELECT
   UM.UMKEY,
   M.MAILTITLE,
   UM.ADDRESS01,
   UM.ADDRESS02,
   UM.ADDRESS03
FROM
   (
      SELECT
         OS.MAILTITLE,
         OS.HOMEKEY
      FROM
         OS
      WHERE
         OS.OS_EMAIL != ''
         AND OS.DECEASED != 'Y'
         AND OS.GENDER  = 'F'
         AND OS.MAIL_LIST = 'Y'
      UNION
      SELECT
         DF.MAILTITLE,
         DF.HOMEKEY
      FROM
         DF
         INNER JOIN (
            SELECT
               FAMILYKEY = CASE X.WHICH WHEN 0 THEN ST.FAMILY ELSE ST.FAMB END
            FROM
               ST
               CROSS JOIN (SELECT 0 UNION ALL SELECT 1) X (WHICH)
            WHERE
               ST.STATUS = 'FULL'
         ) ST
            ON DF.DFKEY = ST.FAMILYKEY
   ) M
   INNER JOIN UM
      ON M.HOMEKEY = UM.UMKEY
WHERE
   UM.INTERNATIONAL != 'Y'
ORDER BY
   UM.UMKEY
;

If you are using a DBMS that supports syntax such as CROSS APPLY (SQL Server) or UNPIVOT, the rows from the ST table can be handled in possibly a slightly more efficient manner. For example, the innermost INNER JOIN could look like this in SQL Server:

INNER JOIN (
   SELECT
      F.FAMILYKEY
   FROM
      ST
      CROSS APPLY (VALUES
         (ST.FAMILY),
         (ST.FAMB)
      ) F (FAMILYKEY)
   WHERE
      ST.STATUS = 'FULL'
) ST
   ON DF.DFKEY = ST.FAMILYKEY


How do I replicate a MySql server's data without using mysqldump? I've got a working server with a lot of data on it. I've built a new server that will be lots faster. Both are running x686 code on Debian Squeeze.

A quick test using one of the tables shows that it takes about ten days to load up
the output of a simple
mysqldump dbname tablename > table.sql
mysql dbname < table.sql

Since I have lots of tables with lots of data, this isn't going to work. It would take until Christmas to load them all up.

Is there any way to just copy the binary files that mysqld is using on the first server to the disks on the second and use them without the dump/reload?

If not, are the dump or load options that will get me a two order of magnitude speed improvement?

A typical table has 10 million to 100 million records. Records lengths vary from 100 bytes to 10K bytes per record.

It would be nice to have the old slow server be a replication client of the new fast one, but I think I have to get the data up first.

Thanks

The question essentially equals this one: "what is a fast backup + recovery method for MySQL". This is because setting up MySQL replication is essentially taking a backup of some sorts, and opening it on another machine (and then syncing the slave with the master, which is a simple operation).

So it boils down to the fact mysqldump is too slow for you (makes sense). I've just answered the same here, but here are the highlights:

Percona Xtrabackup is a common and tested (and free) backup tool. It is of interest to you if you're using InnoDB since it makes a hot backup of the InnoDB tablespace, with small impact on the running server. The resulting backup reflects the time at which the backup process ended, which means shorter initial lag for your slave.
File system snapshots, via filesystems such as ZFS, or via LVM, or otherwise disk based snapshots, are all good solutions. Some (LVM in particular) making for more load than others.

What's common to the above solutions is that they make for a binary backup, not a logical one.

For your next question - yes. You can use MyDumper, which makes for a multithreaded backup (complemented by the multithreaded MyLoader tool). This backup is logical, and uses mysqldump internally, concurrently. It is typically a few times faster than standard mysqldump.


Group stored procedures in oracle We are writing tons of oracle stored procedures to handle our business logic. We would like to do a physical grouping for better maintenance and understanding.

I can see package is helpful for encapsulation and logical grouping... Is there anyway we can create namespace physically to group certain procedures based on business logic.

Please advice us on this...

Presumably you have business logic that relates to how you process data in the DB. That data is likely to be table based and the tables are likely to be per business entity.

For example, there might be a table that holds details of customer accounts. Therefore all business logic pertaining to creating, maintaining, validating, listing, etc. of customer accounts would exist in the package PKG_ACCOUNTS.

Without knowing how granular or generic your procedures are trying to be its hard to see how you would need something more that that. That sort of approach, with packages covering logic that can apply to multiple objects, has always worked very well for me.

Perhaps some examples would help if you don't see that this is sufficient.


Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model Below is an image of hard coded generalization UML logical model of entities.  I'd like to implement these classes as tables in a physical data model.

Two derived classes/entities (Class2 and Class5) show single inheritance generalization because they each are derived from a single base class/entity.  Class2 IS-A Class1.  Class5 IS-A Class4.

And Class3 shows multiple inheritance generalization because it has two base tables (or two parent entities).  Class3 IS-A (Class1 AND Class4).  Or you could read it Class3 IS-A Class1 OR Class3 IS-A Class4.

Here are my questions.  Choosing one example from each.  I'd like to be consistent.  How should this be implemented?  Or maybe I missed a scenario?

1.) Single inheritance: How would the PK/FK values get implemented in the derived tables (Class2 for example)?

(1a) Have attribute1 be PK of derived table and be FK #1 (pointing to only parent/base table PK).

OR...

(1b) Have attribute1 be PK of derived table.  And attribute2 be the FK to derived table (pointing to only base/parent table PK).

2.) Multiple inheritance: How would the PK/FK values get implemented in the derived table (Class3 for example)?

(2a) Have attribute1 be PK of derived table and be FK #1 (pointing to first parent/base table PK).  Then have attribute2 be FK #2 (pointing to second parent/base table PK).

OR...

(2b) Have attribute1 be PK of derived table.  And have attribute2 be FK #1 (pointing to first parent/base table PK).  And have attribute3 be FK #2 (pointing to second parent/base table PK).

I solved this (for single inheritance) like so:

each extension of a class inherits, as its primary key the base classes primary key and always targets with its fk the immediate base class:

class A
class B extends A
class C extends B

A(id,p1)
B(id,p1,p2)
B(id,p1,p2,p3)

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p2: sth

Table C:
id: primary key + foreign key -> B.id
p3: sth

this would reqiure a join to completely retrieve B or C.

alternatively you can have each extension mirror the properties of the base classes:

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p1: sth
p2: sth

Table C:
id: primary key + foreign key -> B.id
p1: sth
p2: sth
p3: sth

here you can retrieve any class without joins but data is duplicated and has to be kept consistent.

which solution you can use strongly depends on how you want to retrieve the data and if you can live with the overhead of the join in version 1 or overhead of writing data multiple times in version 2.

I also added a meta-column to the table to know if a row does have extensions or not which is useful for a some queries:

Table A:
id: primary key
p1: sth
class: A,B,C

Table B:
id: primary key
p2: sth
class: B,C

Table C:
id: primary key
p2: sth
class: C


pt-online-schema-change error creating triggers I am trying to add an index with pt-online-schema-change. The dry-run succeeds, but I am stuck at creating triggers. Any help ?

pt-online-schema-change --alter "ADD INDEX domain_index (domain) USING BTREE" D=master,t=website,u=root --execute --ask-pass
Enter MySQL password:
No slaves found.  See --recursion-method if host inoopa-storage has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `master`.`website`...
Creating new table...
Created new table master._website_new OK.
Altering new table...
Altered `master`.`_website_new` OK.
2020-06-05T16:02:10 Creating triggers...
2020-06-05T16:12:19 Dropping triggers...
2020-06-05T16:12:19 Dropped triggers OK.
2020-06-05T16:12:19 Dropping new table...
2020-06-05T16:12:19 Dropped new table OK.
`master`.`website` was not altered.
Error creating triggers: 2020-06-05T16:12:19 DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction [for Statement "CREATE TRIGGER `pt_osc_master_website_del` AFTER DELETE ON `master`.`website` FOR EACH ROW DELETE IGNORE FROM `master`.`_website_new` WHERE `master`.`_website_new`.`id` <=> OLD.`id`"] at /usr/bin/pt-online-schema-change line 11074, <STDIN> line 1.

The error message shows a lock wait timeout.

This means you cannot lock the table.

There is always three triggers needed for pt-osc on the source table:

INSERT trigger
DELETE trigger
UPDATE trigger

The triggers are not create atomically. They are created one at a time.

If root@localhost has GRANT ALL PRIVILEGES on *.*, you should be able to create the trigger. It looks like the table must have very high read/write traffic.

You will have to clean up as follows:

Kill the pt-osc

Login to MySQL and run

USE master
DROP TABLE `_website_new`;
DROP TRIGGER pt_osc_master_website_ins;
DROP TRIGGER pt_osc_master_website_upd;
DROP TRIGGER pt_osc_master_website_del;

The, start pt-osc over again


Optimize for ad-hoc workload I know what this option does and how to enable it.  My question is what things will happen if I were to enable this.

Without giving too much info, our accounting system is a Microsoft Dynamics product and it uses a VM, 32GB RAM (28GB available to SQL server [2008 R2]).  They had their vendor come and take a look at our configuration to address certain performance issues that the accounting team has been seeing.  They even had another DBA take a look at our configuration.  One of his recommendations was to 'look at missing indexes'.  We could say that about almost every SQL server instance in existence, each table has a unique non-clustered index on it, not my choice but im told that making any changes to the indexes on tables that the software accesses could potentially cause issues per the vendor.  His second was to enable 'optimize for adhoc workloads'.  I've looked into this option a few times before and many say it should always be enabled, whereas some say it's not truly necessary and can go either way.

With optimize for adhoc workloads, I know that single use plans are stored as a stub and the entire plan is not actually kept in cache until the plan is run two times.  With a system such as this, are we REALLY going to see any sort of performance gains?  Per Kimberly Tripp's article, I've run this query:

SELECT objtype AS [CacheType]
    ,count_big(*) AS [Total Plans]
    ,sum(cast(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs]
    ,avg(usecounts) AS [Avg Use Count]
    ,sum(cast((
                CASE
                    WHEN usecounts = 1
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs - USE Count 1]
    ,sum(CASE
            WHEN usecounts = 1
                THEN 1
            ELSE 0
            END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

and here is the result I get:

Now, as we can see, we have approx 1.7GB of single use plans.  Probably worth it to turn this option on as the box has 28GB available to SQL server, right?  Well, We're talking about freeing maybe 1-1.25GB as the stubs still take space, just not as much.  I ran a query to pull all single use plans and get their size, our largest plans are about 1-1.5MB.

So, here are my questions (sorry it took a while to actually get to these):

Do we really think that we will see a measurable performance gain here?  If so, how can we quantify it aside from looking at the space used in the query above?
Understandably, I hesitate throwing switches like this on production systems.  What implications should I be cautious of?  Will the procedure cache wipe itself and rebuild?  Are there any issues that I should be made aware of when turning this on?

Kris,

  •Do we really think that we will see a measurable performance gain
  here? If so, how can we quantify it aside from looking at the space
  used in the query above?

That depends, but my gut instinct with the data you've given is - no. Sure, you'll potentially save some of that space as a plan stub will still take memory just not all that much (compared to your 1 MB plans). So you'll net memory, we get that. However, we don't know how many of those plans were executed a single time, and then some point later while still in cache executed again. This brings up the question about compilations/recompiles and the cpu utilization to go along with it. If you have a good bit of headroom then it may be a trivial issue (pun intended).

If your server isn't under memory pressure, I would not expect to see too much of an improvement in terms of "performance" depending on how you want to classify that. If you're swapping and having some slight memory pressure this could alleviate it for a few moments - though upping the VM memory would have the same effect at a much faster implementation without negative side effect cost.

  •Understandably, I hesitate throwing switches like this on production
  systems. What implications should I be cautious of? Will the procedure
  cache wipe itself and rebuild? Are there any issues that I should be
  made aware of when turning this on?

According to BOL it will not affect anything currently in your plan cache: "Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected." http://msdn.microsoft.com/en-us/library/cc645587(v=sql.105).aspx

You may see the potential (depending on eventual re-use) of cpu hits for plan compiles (since it would have to do it twice, once for the original execution and then one for the second when it is stored).

Some other "weirdness" would include monitoring tools, especially if they are grabbing execution plans as plan stubs do not have any associated with them. Some odd results may come from tools that expect there to be one associated all the time.

I'm not extremely familiar with Dynamics but IIRC, it has a specific Microsoft setup guide like SharePoint. I'd double check this won't invalidate your supportability for the product.


SQLCMD stuck on "set textsize 4096" I am using SQL Data Compare to generate a script that updates data in a QA database (SQL Server 2012).

The script is around 500mb and I added a GO every 100 lines.
When I start the script with:

sqlcmd -E -S [server] -d [db] -i "[path to file]"

the connection is made but it is stuck on the first sql query made by sqlcmd and not the script with the error message:

  SET TEXTSIZE 4096

I tried to let it run one afternoon just to see if I get an error or a timeout etc but I get nothing it just get stuck on it. All I can do is stop sqlcmd (CTRL+C in the console).

I tried with Management Studio 2008 and 2012.

Does anyone have an idea why I get this error?

Update :
I tried running it with the -o but nothing was written to the file even after 3 hours.
These are the first lines of the generated script :

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
[delete update or insert...]

I see in your script, there is a

BEGIN TRANSACTION

and followed by delete/insert/update. I guess if you have a big volume change, this transaction may take longer depending on your destination db's status, such as whether there is conflicting sessions blocking your current session, or your destination db's log file growth is set to 1024KB. So I'd suggest that you can start Profiler to check which sql statement is started, and also check whether there is any blocking issue.


View with a concatenated text field with line breaks Using SQL Server 2012, I need to create a view with a field that concatenates a log field from another table, roughly:

select
    id,
    description,
    STUFF((
        SELECT
            '------------' + char(13) + char(10) +
            MessageLog.MessageText + char(13) + char(10) +
            '- on ' +
                format(MessageLog.MessageDateTime, 'yyyy-MM-dd HH:mm') +
                ' by ' + MessageLog.MessageAuthor + char(13) + char(10)
        FROM
           MessageLog
        WHERE
            Incident.IncidentNumber = MessageLog.IncidentNumber
      ORDER BY MessageLog.MessageDateTime DESC
      FOR XML PATH('')),
    1,
    2,
    ''
    ) AS allMessages
from
    Incident

The line breaks (char(13)+char(10)) come back as hex encoded &#x0D; instead of, well, line breaks.  The data from the table gets dumped into an HTML input field and I have no chance for any post-processing.

I have tried storing the line break as just char(13); just char(10); \n; <br />; and just hitting enter between single quotes.  Nothing renders correctly. I am guessing that I am abusing STUFF() / XML but cannot figure out where my mistake is.

How can I create my allMessages value with line breaks?

I found this example here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138165

CREATE TABLE #MessageLog
    (
      MessageText VARCHAR(MAX),
      MessageDateTime VARCHAR(MAX),
      MessageAuthor VARCHAR(MAX),
      IncidentNumber INT
    )

CREATE TABLE #Incident
(
id INT,
[description] VARCHAR(30),
IncidentNumber INT
)

INSERT INTO #MessageLog (MessageText, MessageDateTime, MessageAuthor, IncidentNumber  )
SELECT 'masg1 'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 4 IncidentNumber
UNION ALL
SELECT 'msg 2'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 4 IncidentNumber
UNION ALL
SELECT 'msg3'MessageText, GETDATE()-6 MessageDateTime,'abc' MessageAuthor, 3 IncidentNumber

INSERT INTO #Incident
SELECT 1, 'desc', 4
UNION ALL
SELECT 1, 'desc', 4
UNION ALL
SELECT 1, 'desc', 3

-- the solution
SELECT        STUFF(( SELECT  '------------' + CHAR(13) + CHAR(10)
                        + MessageLog.MessageText + CHAR(13) + CHAR(10)
                        + '- on ' + MessageLog.MessageDateTime + ' by '
                        + MessageLog.MessageAuthor + CHAR(13) + CHAR(10)
                FROM    #MessageLog MessageLog
                WHERE   Incident.IncidentNumber = MessageLog.IncidentNumber
                ORDER BY MessageLog.MessageDateTime DESC
              FOR XML PATH (''),TYPE ).value('.[1]','varchar(max)')
              , 1, 2, '') AS allMessages
FROM    #Incident Incident


Figuring out if SQL Server database needs to be defragmented I am tasked with looking at if a database in SQL Server 2014 needs to be defragmented.  I ran DBCC SHOWCONTIG but am having trouble interpreting the results.

use databasename;
GO
DBCC SHOWCONTIG ('Table');
GO

The results are:

DBCC SHOWCONTIG scanning 'Table' table...
Table: 'Table' (802206008); index ID: 1, database ID: 20
TABLE level scan performed.
- Pages Scanned................................: 2239
- Extents Scanned..............................: 280
- Extent Switches..............................: 279
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [280:280]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 31.79%
- Avg. Bytes Free per Page.....................: 366.8
- Avg. Page Density (full).....................: 95.47%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Questions:

What do the percentages above mean? (like the difference between logical and extent scan fragmentation).
What percentage do I need to check and what value indicates the disk needs to be defragmented?

Logical Scan Fragmentation  Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

Extent Scan Fragmentation   Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

Did you try this page: DBCC SHOWCONTIG?


Avoiding entitized characters when using FOR XML PATH for string concatenation I have this query:

SELECT DISTINCT
    f1.CourseEventKey,
    STUFF
    (
        (
            SELECT
                '; ' + Title
            FROM
            (
                SELECT DISTINCT
                    ces.CourseEventKey,
                    f.Title
                FROM
                    CourseEventSchedule ces
                INNER JOIN Facility f
                    ON f.FacilityKey = ces.FacilityKey
                WHERE
                    ces.CourseEventKey IN
                    (
                        SELECT CourseEventKey
                        FROM @CourseEvents
                    )
            ) f2
            WHERE
                f2.CourseEventKey = f1.CourseEventKey
            FOR XML PATH('')
        )
    , 1, 2, '')
FROM
(
    SELECT DISTINCT
        ces.CourseEventKey,
        f.Title
    FROM
        CourseEventSchedule ces
    INNER JOIN Facility f
        ON f.FacilityKey = ces.FacilityKey
    WHERE
        ces.CourseEventKey IN
        (
            SELECT CourseEventKey
            FROM @CourseEvents
        )
) f1;

It produces this result set:

CourseEventKey Titles
-------------- ----------------------------------
29             Test Facility 1
30             Memphis Training Room
32             Drury Inn &amp; Suites Creve Coeur

The data is accurate, but I can't have FOR XML PATH('') because it escapes certain special characters.

To be clear, I'm using FOR XML PATH('') because it is possible for records with the same CourseEventKey to have multiple Facility titles associated with them.

How can I retain the data returned by this query without using FOR XML PATH('')?

Try changing you statement to match this:

FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)')

This uses the .value xml method to solve your entitization problem.


Is there a public repo where I can contribute to the MySQL .NET connector? We have found ourselves making some performance and scale improvements for the MySQL .NET driver from here http://dev.mysql.com/downloads/connector/net/

We know where to get the source in a ZIP archive. But is there a proper source repo that we can contribute to? A google search yields a blank.

At the time of this writing, there is not public repository for Connector/NET, if you have performance/scaling improvements you are welcome to contribute them (together with a bug report) at bugs.mysql.com.


MS-Access vba sql update where not working I am having trouble with an MS-Access VBA sql update command.  I have a form which has a combobox of GroupMembershipIDs, and a button.  I am trying to write code which will iterate through the combobox and update the 'Member' field (Yes/No) for all the selected records.

Here is my code:

Private Sub btnRemoveFromGroup_Click()

Dim stDocName As String
Dim VarItem As Variant
Dim sqlcmd As String

For Each VarItem In Me.List12.ItemsSelected
GroupMembershipID = Me.List12.Column(0, VarItem)
MsgBox GroupMembershipID
'That is OK

sqlcmd = "UPDATE 1_tbl_Memberships SET [1_tbl_Memberships].Member =
No WHERE ([1_tbl_Memberships].GroupMembershipID = GroupMembershipID)"
'Something not right here

DoCmd.SetWarnings True
MsgBox sqlcmd
'This looks OK

DoCmd.RunSQL sqlcmd

Next VarItem
DoCmd.SetWarnings False
Me.List12.Requery

End Sub

I have put in a couple of message boxes to help me troubleshoot. The error I get is that the 'where' clause does not seem to be working. When I run this code, I am warned that I am about to update xxx number of records (ie all of them), as if the where criteria is being ignored.

Could someone point me in the right direction? - Where am I going wrong!

Thanks in advance,

Richard.

Its happening because variable name is not correctly placed inside update code and as both column name and variable name are same so according to your current code you are updating all the records.

Try this

sqlcmd = "UPDATE 1_tbl_Memberships SET [1_tbl_Memberships].Member =
No WHERE [1_tbl_Memberships].GroupMembershipID = "& GroupMembershipID &""

http://msdn.microsoft.com/en-us/library/office/aa212163(v=office.11).aspx


Upgrading from MongoDB 2.4 to 3.6 I wish to upgrade my mongo from 2.4 to 3.6. As i see in the official documents, and googling, that i need to do an upgrade for each 0.2 version, meaning doing 6 (!!!) upgrades in order to upgrade my db to 3.6.

Is this in any way avoidable? this process is messy as it requires doing so on many servers and on many of our clients.

Furthermore, i cannot seem to make any upgrade work. i have downloaded the 2.6 version and tried to make an upgrade but all the available executable's from mongo's official website have resulted in an unknown exception.

Any advice will be welcomed as im a bit lost in this process.

The best option is (my opinion) mongoexport data to disk and then mongoimport it to 3.6.


Column names display issue in terminal I am using SQL*Plus and connected to the HR schema.

When I do SELECT * FROM tab; it gave me three column names displayed in different lines.

Is there a way to correct it?

Of course, SQL*Plus does not autoformat your output, but you can do it:

SQL> col tname format a30
SQL> col tabtype format a7
SQL> set numwidh 10

SQL> select * From tab where rownum = 1;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ACCESS$                        TABLE


Create a new table oracle Hello I recently changed companies and the databases as well. Having worked with postgres so far, oracle is still fuzzy to me, so here I am stuck with creating a table. This is my SQL attempt :

CREATE SEQUENCE binaries_seq;
      CREATE TABLE binaries (
       id       NUMBER DEFAULT binaries_seq.NEXTVAL,
       PRIMARY KEY (id),
       data     BLOB NOT NULL
      )

Hardly descriptive error shows up :

SQL Error: ORA-00984: column not allowed here

Even found an article suggesting this approach :
http://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php

What am I doing wrong? I was trying to create a table having one blob field and an id field.

This is my oracle version :

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

You are, as per your tag, not using Oracle 12c.

The syntax id NUMBER DEFAULT t1_seq.NEXTVAL, is only valid from 12c onwards, so you get an error.

On Oracle 11 and prior, you want to use an insert trigger to apply the sequence's nextval to the primary key:

create or replace trigger binaries_ins
  before insert on binaries
  for each row
  when (new.id is null)
begin

  select binaries_seq.nextval into :new.id from dual;

end binaries_ins;


What is happening with locks in my database? I'm having some problems on my database. At any given time, I'm trying to execute an update with five records on my table, but this table is locked and I get a time out executing the query.

I realized that if I execute update in only four records, the statement run sucessfully. And when I try to update the five recods together, it doesn't work.

Example:

--ONLY LOCKS IF THE UPDATE IS IN ALL FIVE RECORDS. (any combination works fine)

UPDATE X SET STATUS = 1 WHERE ID IN (1,2,3,4,5) -- THIS LOCKS THE UPDATE!!

UPDATE X SET STATUS = 1 WHERE ID IN (2,3,4) -- THIS  DOESN'T LOCK AND RUN SUCESSFULLY!

UPDATE X SET STATUS = 1 WHERE ID IN (1,4,5) -- THIS  DOESN'T LOCK AND RUN SUCESSFULLY!

UPDATE X SET STATUS = 1 WHERE ID IN (1,2,3,5) -- THIS DOESN'T LOCK AND RUN SUCESSFULLY!

Does anyone have an idea to how I fix this situation?

EDIT 1:
My isolation level is Read Commited.

When I run sp_lock, I realized that two locks in my table: PAG and KEY.
Searching more about KEY LOCK, I created an index for the column on the where clause, and this solve my problem.

EDIT:
When I removed the where clause (without index) , it worked fine too.


Linked server available to users without permissions I have users seeing a linked server which they are not supposed to see.

The linked server is defined so that only I will have access to it, but everybody can see and use it.

I have created the new linked server using the following steps:

Connect SSMS Object Explorer to the SQL Server instance
Expand Server Objects, right click Linked Servers and left-click New Linked Server...
Under General tab choose SQL Server for Server type and write the Server's name
Under Security tab in the upper part click Add, choose "sa" as Local Login, enter Remote User's name and Remote Password
Under Security tab in the lower part (under For a login not defined in the list above, connections will:) choose the first option: Not be made
Click OK and start testing

Now the only person supposed to see the linked server is me ("sa"), but somehow other users can see it and use it.

Note 1: The users who can use the linked server have permissions on the remote server, they are not seeing data that they should not see, they are just able to access it from the linked server when they should not be able to.

Note 2: I am the only sysadmin on both instances.

There is no way to "hide" a linked server or prevent unauthorized users from trying to use it. All you can control is whether they actually have access on the other side. A linked server in and of itself is not an actual object; like a synonym, you don't grant access to the alias, just to the thing(s) it references.

Try it out, and you will see that some of your assumptions (e.g. that "a login ... can not see the linked server in object explorer") are false. I did this:

Created a login, and granted no privileges whatsoever, just CONNECT / public role.
Created a linked server, and did not add this login to the list of logins.
Opened a new instance of Management Studio, connecting with this login.
I was able to see all linked servers under Server Objects > Linked Servers.
I was also able to query sys.servers to see the list of linked servers.

I was able to run the following query no problem:

SELECT name FROM [linked server].master.sys.objects;

However I was unable to run queries against non-system databases, since I had not granted explicit access to any non-system database or the objects inside. E.g.:

SELECT SalesOrderID FROM [linked server].AdventureWorks2012.Sales.SalesOrderHeader;

Yielded this error:

  Msg 7314, Level 16, State 1, Line 1
  The OLE DB provider "SQLNCLI11" for linked server "linked server" does not contain the table ""AdventureWorks2012"."Sales"."SalesOrderHeader"". The table either does not exist or the current user does not have permissions on that table.

I expect that you can achieve more restrictive results by explicitly denying access to specific objects on the linked server (or by DENY CONNECT SQL to that login altogether), but this does not limit its effect to the linked server; it also affects direct connections.

In any case, I fail to see the point of what you're trying to accomplish:

If the user doesn't have access on the other side, what is the harm in letting them try? (You might have better luck telling them not to and auditing them than trying to prevent them from trying in the first place.)
If the user does have access on the other side, why can't they use the linked server?


Oracle - how to know what internal executable or program performs DML (SELECT,UPDATE,INSERT,DELETE)? My goal is to bypass the query parsing when writing data (INSERT or UPDATE). Is it possible to use directly the executable beneath these commands?

No, it is effectively impossible. The RDBMS is simply too complicated. The block structure has been reversed engineered, but to even begin to do what the RDBMS itself does with those blocks is a gigantic task.

If your goal is to speed up INSERTs, then you can use an /*+APPEND*/ hint in some situations to speed things up, or use Data Pump to do the imports.

Query parsing itself shouldn't really be a major overhead if your application is written properly and uses bind variables.

If you can be more specific about the performance problems you are seeing, and whether you're CPU or IO limited, we'll be able to help you.


Can I skip major versions in pg_upgradecluster? I'm upgrading postgresql 9.3 to 9.5 on ubuntu/xenial and I would like to know if it is possible to run pg_upgradecluster to go directly from 9.3 to 9.5 or whether I need to go through 9.4 in between?

You should be able to do that with pg_upgradecluster either way, using -m dump which uses pg_dump and pg_restore or -m upgrade which uses pg_upgrade according to the manpage here. Using -m upgrade should be much faster than the dump and restore method that -m dump uses.


MySQL Exporting a query to text file via Cron Job I'm in the beginning stages of hacking together code that syncs two completely different pieces of software/webware. I'm looking for a way to run a SQL query and output the results to a text file. I'd like this information to be updated daily so I'm leaning towards a cron job.

My current line of thought is to just write a script that logs into the database(phpBB) run the query (All users that have X number of posts) and then export that list of users to a text file.

My question/s:

Do I have to worry about putting the database username/password into this cron script?
Is there another more efficient way for me to get this information from the database without logging into MySQL?
Is Cron jobs appropriate for retrieving information from a database?
Is there any MySQL specific cron/scripting issues I need to be aware of while programing this.

Please keep in mind that I will be using linux to code and execute this script and the MySQL server is hosted on a unix machine.

After talking to someone more knowledgeable then me I have came up with the following answers to my original questions.
TLDR; Cron should be fine for what your doing but don't write a bash script, write a PHP script instead, it's better.

Q1. Do I have to worry about putting the database username/password into this >cron script?

A1. As long as the script is in a safe spot on the server that is running the >>cron it should be fine. I can also create a new database user with very >>limited access specifically for this script.

Q2. Is there another more efficient way for me to get this information from >the database without logging into MySQL?

A2. There is a more efficient way to get the information, MySQLi or PDO via a >>PHP script, but there is no way to get around logging in.

Q3. Is Cron jobs appropriate for retrieving information from a database?

A3. Cron jobs should work fine for running the script on a regular basis.

Q4. Is there any MySQL specific cron/scripting issues I need to be aware of >while programing this.

A4. There isn't any cron specific issues but Bash or any other *nix based >>scripting language shouldn't be used because of limited access to MySQL. >>Instead a php script should be created utilizing either (PDO or MySQLi).

If anyone can answer the "Why" PHP is better then Bash Scripting in this scenario feel free to leave a post or comment.


VACUUM FREEZE vs. VACUUM FULL Can someone explain the difference between these types of VACUUM in PostgreSQL?

I read the doc but it just says that FULL locks the tables and FREEZE "freezes" the tuples. I think that's the same. Am I wrong?

Here's a short concise answer.

Vacuum full takes out an exclusive lock and rebuilds the table so that it has no empty blocks (we'll pretend fill factor is 100% for now).

Vacuum freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed, ever. Next update this frozen id will disappear.

For instance, the template0 database is frozen because it never changes (by default you cannot connect to it.)

Every so often the autovacuum daemon will check a database and its tables to see what needs to be vacuumed. If a table is vacuum freeze'd and then never updated, the autovacuum daemon will simply pass it by. Also the "wrap around" protection in postgresql will never kick in on that table either.

tl;dr freezing marks a table as not needing any autovac maintenance. The next update will unfreeze it.


Is it possible to insert multiple IP addresses in a single entry in a pg_hba.conf file? Let's say I have the following
3 child IP addresses - 192.168.10.15 ,192.168.10.41 and 192.168.10.81
1 master IP address - 192.168.10.37
I want the 3 child IP addresses to be able to access the master IP address. Now, in order to do this, the pg_hba.conf file of the Master must contain an entry for each of the child IP addresses.
My question is instead of having 3 separate entries like this,

host  all  all  192.168.10.15/32  md5
host  all  all  192.168.10.41/32  md5
host  all  all  192.168.10.81/32  md5

Can it have 1 entry for all 3, something like

host  all  all  192.168.10.15/32,192.168.10.41/32,192.168.10.81/32  md5

I tried the above method but none of the 3 IPs got the access. Is it even possible?

The documentation says:

  This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

  An IP address range is specified using standard numeric notation for the range's starting address, then a slash (/) and a CIDR mask length.

Your addresses cannot be expressed with a CIDR mask (without also matching the other 125 addresses in the 0…127 range).


Create a table dynamically in SQL Server I am trying to create a table dynamically depending upon current date in SQL Server. But I am unable to do so. I am getting error as invalid identifier.

These are the details:

SQL query:

DECLARE @tableToDump nvarchar(100);

SET @tableToDump = 'backupCdc'+cast(cast(GETDATE() as date) as char(100));

DECLARE @DynamicSQL nvarchar(1000);

SET @DynamicSQL=N'create table '+ @tableToDump +' ('+'cid int primary key AUTO_INCREMENT, employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))';

exec @DynamicSQL;

Output (error):

  The name 'create table backupCdc2015-09-10 (cid int primary key AUTO_INCREMENT, employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))' is not a valid identifier.

You can try with different format of date. I also changed AUTO_INCREMENT to Identity

DECLARE @tableToDump nvarchar(100);

SET @tableToDump = 'backupCdc'+CONVERT(varchar(10),getdate(),112);

DECLARE @DynamicSQL nvarchar(1000);

SET @DynamicSQL=N'create table '+ @tableToDump +' ('+'cid int primary key IDENTITY(1,1), employeeno Varchar(100), fieldName Varchar(100) NOT NULL, fieldValue Varchar(1000))';
/*Adding braces is important as suggested by ypercube */
exec (@DynamicSQL);


How to permanently fix IntelliSense poor functioning on SSMS? I have literally installed on my development machine three different versions of SSMS standalone: 2008, 2012, 2014, and I work with SQL Server 2008 databases. On every single one of those versions, I tried to have IntelliSense display my available columns when I type a table alias and a dot "Table." for example. But that doesn't show up over 90% of the time. This happens similarly on any SSMS version I try.

I've searched for other people having this issue and found various cases, but none of those provided solutions, such as refreshing the IntelliSense cache and disabling/re-enabling IntelliSense itself, de-selecting e re-selecting certain options, work for me.

Could this be a problem related to the instances of SQL Server that I am connecting to, rather than with SSMS itself?

Edit: I have also tried to install a third party addon to SSMS, it then starts displaying as expected, but cripples my performance (making typing somewhat slower as it loads things in the background, which I really don't like), so I would rather fix the original functionality instead of resorting to that.

Thanks

SSMS 2016 is available for free. It has much better IntelliSense than in previous versions. https://msdn.microsoft.com/en-us/library/mt238290.aspx I highly recommend upgrading.

Other reasons why you may be having problems, from personal experience:

Highly utilized server makes it take longer to work. I have personally seen it take 5 minutes on a really bogged down machine.
Local machines with low resources.
Incorrect query syntax. This can cause IntelliSense to not load anything because it can not tell what it is supposed to be doing.
Poor connection between local machine and server. We just moved locations at my job, with a slower connection, and my IntelliSense load times have drastically changed.


Excluding a specific table from a SSDT deploy I have an existing database with everything in schema dbo. I have an SSDT project with objects I am adding to it with schema foo

I have a table that looks like this in the project:

CREATE table foo.a (
    id INT NOT NULL
        CONSTRAINT [PK_foo_a] PRIMARY KEY CLUSTERED
        CONSTRAINT [FK_foo_a] FOREIGN KEY REFERENCES [dbo].[a],
    desc NVARCHAR(50) NOT NULL
)

It depends on dbo.a. dbo.a has many columns that are foreign keys to other columns. Someone else (who maintains the default schema) might change dbo.a.

I'd like to simple store dbo.a as:

CREATE table dbo.a (
    id INT NOT NULL
        CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
)

So it gets built internally, but not deployed. Is that possible?

You can use the AgileSqlClub SqlPackage Deployment Filter.

Brief instructions reproduced from the original article by Ed Elliott:

Download the filter from agilesqlclub.codeplex.com
Put the DLL into the same folder as sqlpackage.exe
Add these command line parameters to your deployment:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(BLAH)"

This will neither deploy, drop or alter anything in the BLAH schema.

See the original article for complete details.


Error during MySQL encryption, Code: 3185. "Can't find master key from keyring (...)" I was trying to configure MySQL keyring plugin and I did the following:

• Made sure that keyring_file.dll is present either in C:\Program Files (x86)\MySQL\MySQL Server 5.7\lib\plugin directory.

• added to my.ini:

innodb_file_per_table = 1
early-plugin-load = keyring_file.dll

• restarted MySQL service and even the whole dedicated Windows Server
• run the following SQL(s):

ALTER TABLE <table_name> ENCRYPTION='Y';

and I got the following error:

  Error Code: 3185. Can't find master key from keyring, please check in
  the server log if a keyring plugin is loaded and initialized
  successfully.

Very strange, because I did the same on other server with the same version of MySQL and it worked.

I executed this:

SELECT @@plugin_dir

and it shows right directory:

  C:\Program Files (x86)\MySQL\MySQL Server 5.7\lib\plugin\

WHY do I get that error ??

What is your MySQL Version?

I find the following 'solution' and i will try upgrade mine 5.7.20 to 5.7.22:

  Posted by developer:

  Fixed in 5.7.22, 8.0.4, 9.0.0.

  Cosmetic change to
  error message. No changelog entry needed.

Maybe it will solve your problem.


How to get same column values to make multiple columns? I don't know if my question is valid or not but I'm working on an attendance system, and I want to get some output for my project.

I have an attendance table that holds three columns: emp_id, atten_type and date. The atten_type column can contain one of three values; that is 'present', 'absent' and 'leave'.

Now, I want the output like this:

So grouping by emp_id, month and suming the count based on atten_type?

SELECT emp_id,
    SUM(IF(atten_type='present',1,0) as 'atten_type as present',
    SUM(IF(atten_type='leave',1,0) as 'atten_type as leave',
    SUM(IF(atten_type='absent',1,0) as 'atten_type as absent',
    MONTH(`date`) as 'month'
FROM
    attendance
GROUP BY emp_id, `month`


macOS Catalina - postgreSQL - sysctl.conf still relevant? I had the following settings in /etc/sysctl.conf before Catalina:

# PostgreSQL Config for the MAC
kern.sysv.shmmax=2147483648
kern.sysv.shmall=524288
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.maxprocperuid=1024
kern.maxproc=2048

The Catalina install moved this file to the /Users/Shared folder as /etc is now considered private to macOS.

Are these settings still relevant for Catalina, and if so, where do they need to go?

Thx.

You don't need the shared memory parameters any more, because PostgreSQL has been using POSIX shared memory for a while now.

Making sure that the PostgreSQL user can have enough open files is a good idea though.


Why does a SELECT statement dirty cache buffers in Postgres? I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output.
From the documentation, "dirtied" indicates:

  The number of blocks dirtied indicates the number of previously
  unmodified blocks that were changed by this query; while the number of
  blocks written indicates the number of previously-dirtied blocks
  evicted from cache by this backend during query processing.

This sounds to me like the process of marking a block dirty should only happen when updating data though.  My query is a SELECT, however, and only reads data. I would imagine it would only report hits or reads. I am obviously mistaken.  What exactly is happening in this situation, though?

This has a simple reason.

In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits.

There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.


SQL Server 2012 Mirror in a Disconnected state - how do I fix it? We had a hardware failure last night and we got our servers up and running.  After logging into SSMS I find all the mirrored databases in a disconnected state.  I've done some searching with google-fu, but I've not found what I need.

Can anyone point me in the right direction?

Edit#1

I broke the mirror and attempted to reconfigure it through the wizard. Once I "Finish" it looked like it was going to work, then it failed with the following error:

  The server network address "TCP://SeanServer00.DBA.com:5022" can not
  be reached or does not exist. Check the network address name and that
  the ports for the local and remote endpoints are operational.
  (Microsoft SQL Server, Error: 1418)

Actually, everything stayed the same, I found out that the service account some how lost the CONNECT permission.

So, I gave the service accounts the CONNECT permission and all the mirrored database synced as expected!


How to transition from a statement based log to row based log for Galera replication? I'm new to clustering and I'm trying to implement a Galera cluster. I'm transitioning from a standalone MySQL instance to a MariaDB Galera Cluster consisting of (at the moment) 2 nodes.

I have installed MariaDB-Galera-server.x86_64 10.0.13-1.el6 & MariaDB-client.x86_64 10.0.14-1.el6 on a CentOS 6 server.

I have also converted all the MyISAM tables to InnoDB.

My question is how do I make the switch? I was reading the (old?) codership notes on how to set up the my.cnf (https://web.archive.org/web/20131021065420/http://www.codership.com/wiki/doku.php?id=mysql_galera_configuration) and they say that these parameters are Required:

binlog_format=ROW
innodb_autoinc_lock_mode=2

My current log file (even though it's a standalone instance and not a cluster) is statement based and my database has a lot of data. How does making this change affect my current logs and current data?

Should I just make changes as per the my.cnf listed in the codership example file and play around until I get it right? Any other advice before I get started?

If you have a one-node cluster, add those settings to my.cnf

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2

Login in a run

mysql> reset master;
mysql> set global innodb_fast_shutdown = 0;

Shutdown mysql

service mysql stop

Go to the folder where the binary logs are located and manually delete the binary logs (should only be one at this point) and also the binary log index file.

Start up MySQL

service mysql start

That's all you need to do.


Why am I getting `Impossible WHERE noticed after reading const tables` in explain query? I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result:

Impossible WHERE noticed after reading const tables`

The query I ran:

explain SELECT rid FROM relationship WHERE fromid=78 AND toid=60

Any help?

EDIT1:
When I use the below query:

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND is_approved='s'  OR is_approved='f' OR is_approved='t'

I see USING WHERE instead of the previous message, but when I use the below query:

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND (is_approved='s'  OR is_approved='f' OR is_approved='t')

I again get the first impossible ... message! What these parenthesis do here?

EDIT2:

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `fromid` mediumint(8) unsigned NOT NULL,
 `toid` mediumint(8) unsigned NOT NULL,
 `type` tinyint(3) unsigned NOT NULL,
 `is_approved` char(1) NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `fromid` (`fromid`,`toid`),
 KEY `toid` (`toid`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`fromid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`toid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

EDIT3:
As mysql site say:

  Impossible WHERE noticed after reading const tables

  MySQL has read all const (and system) tables and notice that the WHERE
  clause is always false.

But in the query I get the result I want, the WHERE part is not false. Is there someone who could explain this and shed some light on the subject?

You are getting the message

  Impossible WHERE noticed after reading const tables

This is documented in the page you already linked.

  MySQL has read all const (and system) tables and notice that the WHERE clause is always false

const tables are defined as

  The table has at most one matching row, which is read at the start of
  the query.  ... const is used when you compare all parts of a PRIMARY KEY or UNIQUE
  index to constant values.

You have a UNIQUE KEY on  (fromid,toid). The query on WHERE fromid=78 AND toid=60 can be satisfied by reading this unique index. From the message you are getting this must return no results.

Similarly the query WHERE fromid=60 and toid=78 AND (is_approved='s'  OR is_approved='f' OR is_approved='t') can also use this index to locate the row of interest (though it still has a residual predicate to evaluate were any row to match).

Your other query is different

SELECT rid
FROM   relationship
WHERE  fromid = 60
       AND toid = 78
       AND is_approved = 's'
        OR is_approved = 'f'
        OR is_approved = 't'

AND has a higher precedence than Or, so this is the same as

SELECT rid
FROM   relationship
WHERE  ( ( fromid = 60 ) AND ( toid = 78 ) AND ( is_approved = 's' ) )
        OR ( is_approved = 'f' )
        OR ( is_approved = 't' )

This can no longer use that index and has different semantics in that it will return any rows where is_approved IN ('f','t') irrespective of what the values in the other columns are.


What to consider when generating rows via cross joins vs recursive unions? These two queries return the same result (generating a sequence of integers):

1
2
3
4
5
...
16

What performance/other considerations should be taken into account when choosing one or the other?

A (from a blog post)

;with
T1(number) AS ( SELECT 1 UNION ALL SELECT 1 ),
T2(number) AS ( SELECT 1 FROM T1 a cross join T1 b ),
T3(number) AS ( SELECT 1 FROM T2 a cross join T2 b ),
Nums(number) AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) from T3 )
select * from Nums;

B

;with Ints(i) as (
    select 1
    union all
    select (i + 1)
    from Ints
    where i < 16
)
select * from Ints;

I prefer B...

Community wiki answer- please edit to improve:

Depending on how many rows you're generating, the second query may be slower as the recursive part of your CTE will be forced to run serially.

The recursive CTE solution may also have poorer cardinality estimates, because SQL Server finds it difficult to assess how many rows will be produced.

The query optimizer recognizes that the results of the first query are already sorted but does not recognize that for the recursive query. This means that using the recursive approach in a query that benefits from sorted data may lead to an unnecessary sort.

The recursive CTE as is can only generate up to 101 rows before hitting an error. To get a larger result set you need to add a MAXRECURSION query hint.

As an aside, regardless of which method you prefer, if you terminate every statement with a semicolon, you don't need to put another semicolon at the beginning, i.e. it's all right to have with … instead of ;with ….

Related:

SQL, Auxiliary table of numbers
What is the best way to create and populate a numbers table? on Stack Overflow


How do I revert the MUST_CHANGE in this SQL Statement? I executed

SELECT N'ALTER LOGIN ' + QUOTENAME(name)
  + N' WITH PASSWORD = N'''' MUST_CHANGE, CHECK_POLICY = ON;'
  FROM sys.sql_logins
  --WHERE is_policy_checked = 0;

But I don't want the MUST_CHANGEoption on the login.

Did this query actually change anything or is this just a simple select?

That query didn't change anything so you don't need to revert anything.

That is just a simple select generating an ALTER LOGINstatement.

If you didn't execute the resulting statement nothing has been changed.


What is the syntax of mongodump and mongorestore? I have a problem in my exercise:

Export the db atelier. For that, use the command ./mongodump -d atelier My answwer : $ mongodump -d atelier
Import the contenu of the db atelier in the db atelier2. For that, use the command ./mongorestore (use --help for see options). My answer : $ mongorestore dump/atelier -d atelier2

It doesn't work. The contenu of atelier is never copied in the db atelier2, the db atelier2 give no results so it must contains the collections of the data base atelier. Why it doesn't work ?

Please help me

Thanks

The dump path has to be the final argument of mongorestore.

https://docs.mongodb.com/manual/reference/program/mongorestore/

mongorestore -v --db atelier2 dump/atelier

should do the trick. If not the verbose flag -v should give hints to the deeper cause.


SQLITE: Multiple foreign key referenced to multiple table cascade delete In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where satisfied.

I have four tables.

TableA:
id, primary key
name

TableB:
id, primary key
issues,
tb_aid, #foreign key ref to TableA id
tb_cid, #foreign key ref to TableC id
tb_did, #foreign key ref to TableD id
tb_eid, #foreign key ref to TableE id

TableC:
id, primary key
column1,
tb_bid, #foreign key ref to TABLE B id

TableD:
id,
name

TableE
id,
name

I tried JOIN but unfortunately its not working in SQLite. I dont know how to use TRIGGER. Is it possible to use ON CASCADE DELETE for the above case.

You can use on cascade delete

Example

Turn on FK Support

PRAGMA foreign_keys = ON

Create sample data

CREATE TABLE TableA
(
id INTEGER primary key ,
name varchar
);
CREATE TABLE TableC
(
id INTEGER primary key,
name varchar
);
CREATE TABLE TableB
(
id INTEGER primary key,
issues varchar,
tb_aid INTEGER,
tb_cid  INTEGER    ,
CONSTRAINT fk_TBLC
FOREIGN KEY (tb_cid)
REFERENCES TableC(id)
ON DELETE CASCADE,
CONSTRAINT fk_TBLA
FOREIGN KEY (tb_aid)
REFERENCES TableA(id)
ON DELETE CASCADE
);
INSERT INTO TableA(id,name) VALUES(1,'test');
INSERT INTO TableC(id,name) VALUES(1,'test');
INSERT INTO TableB(id,issues,tb_aid,tb_cid) VALUES(1,'test',1,1);

Delete the value from TableA

DELETE FROM TableA where id = 1;

Select from tableB

SELECT * FROM TableB;

Result

  id    issues  tb_aid  tb_cid

DB<>Fiddle

You do have bidirectional FK's in your example, while I do not think it is a good design, you could use something like the DB Fiddle below.

DB<>Fiddle


Random characters after table name in FROM clause of SELECT query I am rather new to SQL and database administration. I am maintaining/modifying a PHP application that interacts with Postgresql, and have encountered the statement:

SELECT * FROM client_messages c;

I do not understand the meaning of the final 'c' character. In fact, it seems like it is possible to have random characters after the table name in the FROM clause of a SELECT, without affecting the results of the query. Experimenting with the 'psql' utility, I find that these three queries all produce the same result, without any errors:

SELECT * FROM cltk2980dat c LIMIT 5;
SELECT * FROM cltk2980dat LIMIT 5;
SELECT * FROM cltk2980dat kdopwijfdwlhoeufhnekfhyufhf LIMIT 5;

Yet, the author of the program I am maintaining seems to have really wanted that final 'c' character in the SELECT query. I cannot find any explanation for this in manuals of SQL syntax, though I can find explanations of many other ways to modify the FROM clause of a SELECT.

Is there some significance to that final character? Will apparently random characters after the FROM table-name ever have any significance under some circumstances?

As @dezso points out it's a table alias. You can also use as which makes it a bit more comprehendable:

SELECT * FROM client_messages as c

Why use an alias? It makes it easier to refer to the table. Assume the following query:

SELECT *
FROM client_messages
JOIN client_address
    ON client_messages.client_id = client_address.client_id

Using table aliases:

SELECT *
FROM client_messages as cm
JOIN client_address as ca
    ON cm.client_id = ca.client_id

so it's a bit shorter which is nice. When using self joins it becomes necessary to use aliases:

SELECT *
FROM client_messages as cm1
JOIN client_messages as cm2
    ON cm1.x = cm2.y
WHERE cm1.... = ...

Otherwise there is no way to determine which one of the two tables one is referring to.

As a side-note, avoid using SELECT * other than for ad-hoc querying.


How to get rid of using temporary when using DISTINCT in MySQL? When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary?

http://sqlfiddle.com/#!8/f61a2/3/0

CREATE TABLE `test` (
  `a` varchar(45) NOT NULL,
  `b` varchar(45) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `index1` (`b`,`date`),
  KEY `index2` (`b`,`date`,`a`)
) ENGINE=InnoDB;

INSERT INTO test (a, b, date) VALUES ('a1', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a1', 'b2', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b1', now());
INSERT INTO test (a, b, date) VALUES ('a2', 'b2', now());

INSERT INTO test (a, b, date) VALUES ('a3', 'b1', '2000-01-01 01:01:01');
INSERT INTO test (a, b, date) VALUES ('a3', 'b2', '2000-01-01 01:01:01');

EXPLAIN SELECT
DISTINCT a
FROM test
WHERE b IN ('b1', 'b2') AND
date > NOW() - INTERVAL 1 MONTH;

Do you have performance issues because this sounds like premature optimization?

The only good single query what uses indexes is an UNION (which will also filter out duplicates).

If you run EXPLAIN on this query, using temporary won't pop up in the explain..

SELECT
 a
FROM test
WHERE b = 'b1' AND
date > NOW() - INTERVAL 1 MONTH

UNION

SELECT
 a
FROM test
WHERE b = 'b2' AND
date > NOW() - INTERVAL 1 MONTH
;

Edited 2019-09-01

  Demo is not valid anymore as sqlfiddle does not run the old MySQL version anymore
  and because MySQL source code was updated...

  I would suggest looking into db-fiddle and switch between MySQL versions 5.5/5.6/5.7, then the problem should be visible between 5.5 and 5.6

By looking into source code file sql/union.cc (C++ code) of MySQL version 5.7.2 m12 (most likely (all) lower versions also using this code, but not sure about that), we can see how UNION works in MySQL. You will discover that the EXPLAIN output sometimes tells you lies, because the source code (in one of the input parameters) indicates that:

  is_union_distinct if set, the temporary table will eliminate
  duplicates on insert

This makes sense because duplicated records are filtered out. Source code snapshot is below:

/*
  Create a temporary table to store the result of select_union.

  SYNOPSIS
    select_union::create_result_table()
      thd                thread handle
      column_types       a list of items used to define columns of the
                         temporary table
      is_union_distinct  if set, the temporary table will eliminate
                         duplicates on insert
      options            create options
      table_alias        name of the temporary table
      bit_fields_as_long convert bit fields to ulonglong

  DESCRIPTION
    Create a temporary table that is used to store the result of a UNION,
    derived table, or a materialized cursor.

  RETURN VALUE
    0                    The table has been created successfully.
    1                    create_tmp_table failed.
*/

bool
select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
                                  bool is_union_distinct, ulonglong options,
                                  const char *table_alias,
                                  bool bit_fields_as_long, bool create_table)
{
  DBUG_ASSERT(table == 0);
  tmp_table_param.init();
  count_field_types(thd_arg->lex->current_select(), &tmp_table_param,
                    *column_types, false, true);
  tmp_table_param.skip_create_table= !create_table;
  tmp_table_param.bit_fields_as_long= bit_fields_as_long;

  if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                 (ORDER*) 0, is_union_distinct, 1,
                                 options, HA_POS_ERROR, (char*) table_alias)))
    return TRUE;
  if (create_table)
  {
    table->file->extra(HA_EXTRA_WRITE_CACHE);
    table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
  }
  return FALSE;

}

There is a function that is called create_tmp_table, and thus MySQL is always creating a temporary table when the function create_result_table is called


Can I remove the need for "GO" in SSMS? Is there a way to cause every statement to be sent to the server immediately, or in its own batch, in SSMS?

I am working on a large set of statements within which I alter tables, disable/enable temporal history, change values, etc., and SSMS seems to be getting in the way in a big way. If I ran the commands as normal in a CLI, or I select-and-run manually, it works. However for some reason in SSMS I need to insert go statements in between everything in order for it to work.

For example, adding a column and then subsequently updating its values, without go it tells me the column name is invalid, with go it works as expected. I can only imagine that SSMS is somehow interpreting the commands and comparing to its idea of the tables and causing the "error"--not the server.

I would prefer a statement like go away at the start of the whole set/file, to make it actually run each line as-is. Is this possible?

The short answer is no, it cannot be turned off.
GO is a 'batch terminator'.  When SSMS executes 'statements' they are 'batched' together.  This could be a single query, an entire script with no 'GO's or could be the code between 'GO's.
That is why you are getting the errors when you are trying to add a column and update it in one hit.  They need to execute as separate batches.
It is also possible that, say you had 3 statements separated into batches that a single batch in a script could fail but not the entire script.
Query1 -- success
GO
Query2 -- failed
GO
Query3 -- success
GO

GO is not a Transact SQL Statement, it is a command recognised by SSMS and sqlcmd.  See here on MSDN for more details.
You will also find that variables do not persist past a GO statement and must be re-declared or your statement/batch sequence amended.
Incidentally, you don't have to use 'GO' as a batch terminator, you could use anything.  It can be amended from the Tools-->options menu, under query execution.  Be aware though, changing it and using something different will only work for the copy of SSMS you are using.  If you are working in a team, and every member of the team has not changed to use 'NewBatch' keyword then the script will fail in their copy of SSMS (as I'm fairly confident it does in sqlcmd too!).


Mirror copies of redo logs I'm running an two node RAC database with ASM having two DGs one for data and one for redo. I have only one copy of redo and I have the need to have another copy of redo logs.
1. How to go ahead with adding mirror copies of redo?
2. Will it demand downtime?
3. Can I add the copies which I'm going to create in the data DG?
4. I also need to create a copy for the control file. How can I do that as well?

Thanks,
Tesla.

You won't need downtime just to add additional redo logs to your redo groups.

Take a look at this:

Oracle Configuration Best Practices

And then look at this:

Creating Redo Log Groups and Members

You will need downtime to add additional control files:

Creating Additional Copies, Renaming, and Relocating Control Files


Addtional configuration when deploying Data Tier Applications? I'm starting a few new database projects and I'm attempting to create them at Data Tier Applications. There are two items I'm not able to find documentation for. I would like to set the db owner to SA and set the initial filesize and growth rate. Even if those items are outside the scope of the app, I would expect that there would some way to specify that at publish time, either in SSDT or SSMS. I can find no documentation either way. Is this the case?

There is no direct way to set/change the ownership as a part of CREATE or ALTER DATABASE statement.

If you want to enforce that, you can do this by adding a post deployment script which calls sp_changedbowner.

This article describes that in detail. Similar approach can be applied to set the initial file size and growth Rate as well.

Also, as a note: sp_changedbowner is now depreciated, so it is better to use ALTER AUTHORIZATION


Is it possible to enforce corresponding hierarchies? I constructed the following toy example to illustrate my question.

The left side of the schema describes a an abstract product consisting of its parts.

The right side of the schema describes actual produced instances of these products/parts. The horizontal relations indicate from which "template" the instance was built. Note that it is possible that a part_instance is initially not yet assigned to a product_instance.

Here's the challenge:

Assigning a part_instance to a product_instance should only be possible if the part from which the part_instance was built is actually a part of the product from which product_instance was built from. So the (optional) hierarchy on the right should be enforced to correspond to the hierarchy on the left.

Or as an example: Let's assume part1 belongs to product1 and part2 belongs to product2. Now let's create productInstance1 linked to product1, productInstance2 linked to product2, partInstance1 linked to part1 and partInstance2 linked to part2. It should be allowed to assign partInstance1 to productInstance1, but not to productInstance2. Similarly it should be allowed to assign partInstance2 to productInstance2 but not to productInstance1.

Question:

Is it possible to enforce this rule in the database?

Based on the comments to my question it looks like the solution is to add product_id to the primary key of both the part and product_instance tables.


Human relations between users I'm involve in a development of a tiny Social Network where users must be able to establish relations between them and also have permissions over contents. For a example: I add one user as my friend but this user doesn allow me to see all his/him contents so I only have access to those contents that user allow me (permissions). So I have a problem and need help designing the DER of this part. I think in have this tables:

- users (id, name)
- relations_type (id, name, active)
- users_relations (id, id_user_1, id_user_2)
- users_permissions (id, id_relation, id_module, id_user, view, edit, delete)

This cause the following:

Two rows for every relationship: User 1 > User 2 and User 2 > User 1 because when I search (SELECT) I need to know which are User 1 friends and also which are User2 friends. If I leave only one way relationship then I need a UNION and this migth slow  my DB

Is that correct? How yours handle this when CRUD on this? I'm using MySQL by the way and MyISAM tables.

Cheers and thanks in advance

You may want to recognize directional relationships (e.g. "is the mother of", "is the son of") or you may need to acknowledge that relationships are not bilateral insofar as the users don't grant each other exactly the same rights.  In that case you need to keep two records in users_relations for each relationship.  Otherwise, I would just capture each user linkage once.

Union queries take about twice as long as single queries, but when these queries are built on fast operations like index seeks this isn't so bad.  In any case you'd be trading this off against twice as much storage, which mitigates the impact slightly.

I'd be much more concerned about the potential headaches of managing the redundant data (if it is indeed redundant).

You could pick a convention like lowest id_user first to make certain kinds of operations more predictable - such as updates and deletes.


Query parent table and get child tables columns I have two tables, one inherits the other:

CREATE TABLE parent (
    col1 INTEGER
);

CREATE TABLE child (
    col2 INTEGER
) INHERITS( parent );

// Insert some data
INSERT INTO parent( col1 ) VALUES( 1 );
INSERT INTO child( col1, col2 ) VALUES( 2, 2 );

Is there any way to query the parent table, such that it gets all of the columns of the child tables?

This query returns just the parent columns:

SELECT * FROM parent;

| col1 |
| ---- |
| 1    |
| 2    |

My desired query would return all columns in parent and child:

SELECT ...  -- some query, with desired result:

| col1 | col2 |
| ---- | ---- |
| 1    | NULL |
| 2    | 2    |

As long as the set of inherited column values is unique across all tables, there is a simple solution with a NATURAL join (one of the rare use cases for this clause!):

SELECT * FROM ONLY parent NATURAL FULL JOIN child;

Since NATURAL is (per documentation):

  ... shorthand for a USING list that mentions all columns in the two tables that have the same names.

You get each column only once without explicit SELECT list, and rows from the parent table are extended with NULL values for added columns - exactly the way you desire.

This even works with NULL values in any of the columns.

To also see the origin of each row in the result, insert another row to demonstrate the difference:

INSERT INTO child(col1) VALUES(1);

Then:

SELECT COALESCE(p.tableoid, c.tableoid)::regclass AS tbl, *
FROM   ONLY parent p NATURAL FULL JOIN child c;

 tbl   | col1 | col2
-------+------+----
parent | 1    | NULL
child  | 1    | NULL
child  | 2    | 2

SQL Fiddle with more test rows.

You can join to multiple child tables this way if all additional column names in all child tables are unique. Else you'll have to spell out an explicit SELECT list and explicit join conditions.

The set of inherited column values has to be unique across all child tables.