none
הכנסה של מספר שורות של ערכים עקראיים מסוג nvarchar לטבלא. RRS feed

  • שאלה

  • שלום לכולם,

    הייתי בראיון עבודה לא מזמן והתבקשתי ליצור טבלא בעלת (בין השאר) עמודה מסוג nvarchar ולהכניס לשם 1000 ערכים בעלי אורך ותוכן אקראי לחלוטין. ניסיתי להשתמש ב-RAND() וב- NCHAR(), אבל הבעיה הוא ש-RAND() נותן לי את אותו ערך 1000 פעמים, אלא עם כן אני מריץ אותו בלולאה ואני רוצה להימנע מלולאות. הבעיה עם NCHAR() שהוא מחזיר רק תו אחד ואני צריך אורך אקראי. יש למישהו רעיון?

    יום שלישי 29 אוקטובר 2013 08:30

תשובות

  • היי,

    אתה יכול להשתמש בפונקציה הלא דטרמיניסטית NEWID בשילוב עם CHECKSUM לדוגמה ואז לקבל מחרוזת רנדומלית באורך שתיקבע:

    לדוגמה:

    select

    top 1000 CAST(ABS (CHECKSUM (NEWID ()))% 10000 AS NVARCHAR(1000))

    from

    sys.objects a

    cross

    join sys.objects b

    • הוצע כתשובה על-ידי pituachMVP, Editor יום שלישי 29 אוקטובר 2013 09:44
    • סומן כתשובה על-ידי pituachMVP, Editor יום שני 04 נובמבר 2013 10:19
    יום שלישי 29 אוקטובר 2013 09:27
  • הי,

    הנה פתרון קצת יותר אלגנטי...

    SELECT TOP (1000)
    	String
    FROM
    	sys.columns AS T1
    CROSS JOIN
    	sys.columns AS T2
    CROSS APPLY
    	(
    		SELECT TOP (ABS (CHECKSUM (NEWID () , T1.object_id)) % 30 + 1)
    			NCHAR (ABS (CHECKSUM (NEWID () , T1.object_id)) % 1000)
    		FROM
    			sys.columns
    		FOR XML
    			PATH (N'')
    	)
    	AS
    		RandomCharacters (String)
    ORDER BY
    	NEWID () ASC;
    GO
    
    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Ivan Radchenko יום שני 25 נובמבר 2013 10:18
    יום שני 25 נובמבר 2013 09:17
    מנחה דיון
  • הי איבן,

    מה דעתך על זה?

    SELECT TOP (1000)
    	LEFT (CAST (NEWID () AS NVARCHAR(36)) , ABS (CHECKSUM (NEWID ())) % 36 + 1)
    FROM
    	master.dbo.spt_values;

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Eran Sharvit יום שני 04 נובמבר 2013 09:30
    יום רביעי 30 אוקטובר 2013 07:04
    מנחה דיון
  • יוסי אני מאוד ממליץ לך לא לעבוד בשיטה זו אם אתה מחפש רנדומליות מלאה

    א. אתה לא מייצר פונקציה רנדומלית בשום מקרה אלא מתבסס על נתונים. אם יש נתון שמופיע יותר פעמים אז יצרת עדיפות לתוצאה זו, אם יש אות שאין שום שמתחיל בו אז אתה לא תקבל בתוצאה שרשרת שמתחילה באות זו וכן הלאה.

    זה רעיון שיכול לעזור למילוי מהיר של טבלה בנתוני טקסט שניראים רנדומליים אבל זה בהחלט לא פונקציה רנדומלית במובן המקובל אלא בחירה רנדומלית.

    ב. אם מישהו מעוניין אני יכול לפרסם פונקצייה פשוטה לייצור שרשרת טקסט רנדומלית. עדיין לא הבנתי מכל השרשור אם הצורך כאן היה מספר רנדומלי או שרשרת רנדומלית (שאלתי את זה כבר כמה פעמים בשרשור וזה נאבד בדרך ללא תשובה). יש לי פונקציה שמקבלת 2 פרמטרים: (א) שרשרת תווים (ב) 0/1 סוג התוצאה. הפונקציה מייצרת שרשרת רנדומלית באורך מסויים או באורך רנדומלי (לפי פרמטר ב), מתוך רשימת תוויים בהם רוצים להישתמש (פרמטר א). זה מאפשר למשל יצירת שרשרת רנדומלית בשפה מסויימת או לבחור רק אותיות קטנות או רק אותיות (גדולות וקטנות) או אותיות ומספרים וכן הלאה... מאוד מתאים לצרכים כמו ססמה אקראית (בלי הקידוד למשל). זו פונקציה מאוד וותיקה שיושבת אצלי בכל שרת במסד נתונים של עזרים, אני נעזר בה למילוי טבלאות לצרכי בדיקות למשל.


    [Personal Site] [Blog] [Facebook]signature

    • נערך על-ידי pituachMVP, Editor יום שני 04 נובמבר 2013 05:37
    • סומן כתשובה על-ידי Eran Sharvit יום שני 04 נובמבר 2013 09:29
    יום שני 04 נובמבר 2013 05:29
    מנחה דיון
  • שלום לכולם,

    אני ממש מתנצל שנעלמתי בלי להודיע (הייתי בטיול של שבועיים בחו"ל) מעריך את עזרתכם.

    גיא,

    בהחלט, אך תשובה שנותנה פה לא ממש עונה על השאלה.

    היום הוא יום הראשון שלי בעבודה החדשה באותה חברה שבה נשאלתי את השאלה הזאת. :) להלן התשובה של DBA הבכיר של החברה (הוא ביקש לא לפרסם את שמו):

    SELECT top 1000
           LEFT (
                 NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
                 
              , 1 + CAST(29 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT)
              ) AS Str
    from sys.columns a CROSS JOIN sys.columns b
    

    • סומן כתשובה על-ידי Ivan Radchenko יום ראשון 24 נובמבר 2013 11:17
    יום ראשון 24 נובמבר 2013 11:16

כל התגובות

  • היי,

    אתה יכול להשתמש בפונקציה הלא דטרמיניסטית NEWID בשילוב עם CHECKSUM לדוגמה ואז לקבל מחרוזת רנדומלית באורך שתיקבע:

    לדוגמה:

    select

    top 1000 CAST(ABS (CHECKSUM (NEWID ()))% 10000 AS NVARCHAR(1000))

    from

    sys.objects a

    cross

    join sys.objects b

    • הוצע כתשובה על-ידי pituachMVP, Editor יום שלישי 29 אוקטובר 2013 09:44
    • סומן כתשובה על-ידי pituachMVP, Editor יום שני 04 נובמבר 2013 10:19
    יום שלישי 29 אוקטובר 2013 09:27
  • דרך אגב, שכחתי כמעט

    בהצלחה עם הראיון איבן :-)


    [Personal Site] [Blog] [Facebook]signature

    יום שלישי 29 אוקטובר 2013 10:17
    מנחה דיון
  • היי תומר,

    תודה רבה, אבל זה לא כל כך עובד. אני מקבל מספרים בעלי 4 ספרות בלבד אני לא מבין איך זה אמור לעבוד בדיוק. guid הוא בינארי, checksum מחזיר int, את המספר הזה אתה אופך למחרוזת עם cast... לא ברור בכלל.

    חוץ מזה, אני צריך לקבל רשומות בעלי אורך אקראי, לא לקבוע את האורך מראש...

    היי רונן,

    תודה רבה, מאוד אשתדל! :)




    • נערך על-ידי Ivan Radchenko יום שלישי 29 אוקטובר 2013 11:47
    יום שלישי 29 אוקטובר 2013 11:44
  • הרעיון של השיטה הוא לייצר מס' רנדומליים, לכן כאשר אני מבצע פעולת % (שארית חלוקה) 10000 אני אקבל בצורה רנדומלית מספרים בתווך שבין 0-9999.

    לכן ככל שתגדיל או תקטין את מס' החלוקה כך אורך המחרוזת הסופית ישתנה. את התוצאה המספרית תוכל לשרשר למחרוזת טקסט כלשהי וזה יתן לך אורך ותוכן אקראי.

    אם לא ידוע מראש מהי אורך המחרוזת המקסימלית אז שיטה זאת לא תעזור לך.

    יום שלישי 29 אוקטובר 2013 12:29
  • איבן אתה רוצה לייצר רק מספרים או שרשרת תווים?

    [Personal Site] [Blog] [Facebook]signature

    יום שלישי 29 אוקטובר 2013 16:35
    מנחה דיון
  • איבן אני אתן לך פתרון פשוט שיהיה לך יותר אינטואטיבי כניראה, אבל אני רוצה להדגיש שהפתרון שאני הייתי בוחר בו הוא זה שמתבסס על NEWID במקרה שלך כניראה והוא פתרון יותר מיטבי.

    בכל מקרה הבלוג הבא כולל הסבר לנושא שלא קשור לבעיה הנוכחית (שימוש בפונקציות עם השפעה צדדית בתוך פונקציות) אבל הקוד בו יוכל לעזור לך מכיוון שהדוגמה בה הישתמשתי היתה יצירת פונקציית RANDOM שלך. הרעיון הבסיסי לפתרון שמתאים לך הוא להכין פונקציית RANDOM שלך שתעבוד בדיוק כמו ב MYSQL או במילים אחרות תחזיר ערך שונה בכל רשומה.

    בדוק את הבלוג

    http://ariely.info/Blog/tabid/83/EntryId/121/Using-side-effecting-build-in-functions-inside-a-UDF-your-function.aspx

    מצד שני שינוי קטן בקוד שהביא תומר יבצע את ההתאמה למה שאתה צריך. בדוק את הקוד הבא:

    select top 1000 CAST(ABS (CHECKSUM (NEWID ()))AS float)
    from _ArielyAccessoriesDB.dbo.Numbers
    -- השימוש במודולו נועד רק בשביל להגביל את התוצאה אבל בדוגמה למעלה לא הגבלתי
    -- כאן למשל אני מגביל את התוצאה לערכים מאפס ועד ל 99999
    select top 1000 CAST(ABS (CHECKSUM (NEWID ()))% 1000000 AS float) from _ArielyAccessoriesDB.dbo.Numbers


    [Personal Site] [Blog] [Facebook]signature

    יום שלישי 29 אוקטובר 2013 16:57
    מנחה דיון
  • הי איבן,

    מה דעתך על זה?

    SELECT TOP (1000)
    	LEFT (CAST (NEWID () AS NVARCHAR(36)) , ABS (CHECKSUM (NEWID ())) % 36 + 1)
    FROM
    	master.dbo.spt_values;

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Eran Sharvit יום שני 04 נובמבר 2013 09:30
    יום רביעי 30 אוקטובר 2013 07:04
    מנחה דיון
  • הרעיון של השיטה הוא לייצר מס' רנדומליים, לכן כאשר אני מבצע פעולת % (שארית חלוקה) 10000 אני אקבל בצורה רנדומלית מספרים בתווך שבין 0-9999.

    לכן ככל שתגדיל או תקטין את מס' החלוקה כך אורך המחרוזת הסופית ישתנה. את התוצאה המספרית תוכל לשרשר למחרוזת טקסט כלשהי וזה יתן לך אורך ותוכן אקראי.

    אם לא ידוע מראש מהי אורך המחרוזת המקסימלית אז שיטה זאת לא תעזור לך.

    תומר, זוהי בדיוק השאלה: איך אני משרשר את תוצאה המספרית למחרוזת טקסט כלשהי? כל השאר ברור לי. לגבי אורך המרבי, אז הוא מוגדר, כמובן. לא שאלתי מהו בדיוק, אבל זה לא עקרוני. השאלה היא, איך אני מקבל רשומות באורך אקראי שהוא קטן או שווה לאורך המירבי?

    רונן,

    הרעיון שלך לשים את RAND() בתוך פונקציה הוא נחמד, אבל איך זה שונה מלולאה? אם אני לא טועה, אתה מפעיל את הפונקציה על 1000 שורות, כלומר מריץ אותה 1000 פעמים, נכון?

    גיא,

    הבעיה עם השאילתא שלך, שהמחרוזות שמתקבלות הן הקסדצימליות (0-9, A-F) ולא unicode. חוץ מזה היא אכן עונה על כל התנאים.

    יום רביעי 30 אוקטובר 2013 11:33
  • אני אתן לך דוגמה מקווה שתבין,

    אם אתה רוצה רשומות באורך מקסימלי של 5 אז אתה מגדיר בשארית חלוקה (%) 100000 כך תוכל לקבל מס' בתווך 0-99999

    ואז אם תקבל את הספרה 3 זה יהיה באורך 1 ואם תקבל 222 זה יהיה באורך 3, כמובן שהמקסימום יהיה באורך 5.

    על מנת לשרשר את התוצאה למחרוזת טקסט כלשהי אז בשאילתה עצמה תבצע את החיבור לדוגמה:

    SELECT

    SumString+RandNumber

    לכן השתמשתי בפונקצייה CAST בשאילתה המקורית שלי.

    יום רביעי 30 אוקטובר 2013 13:01
  • תבדוק לבד או תעקוב אחרי הבלוג :-)

    אם אתה מריץ את הפונקציה המובנית RAN אתה מקבל את אותו ערך תמיד, אבל אם אתה מריץ את הפונקציה שלך (שלי) של ה RANDOM אז אתה מקבל בכל רשומה בתוצאה מספר שונה רנדומלי. זה ההבדל!

    עדיין אני לא מצליח להבין דרך אגב ולא ענית לי: האם אתה רוצה לקבל שרשרת רנדומלית של תווים (ז"א גם אותיות) או מספר רנדומלי? אם כל מה שאתה צריך זה מספר רנדומלי אז יש לך בשרשור כבר כמה פתרונות מבוססים NEWID או שימוש בפונקציה RANDOM שלך (פחות יעיל).

    תרשום רשימה של דוגמת תוצאות סופיות שאתה רוצה לקבל (תייצר רגע ידנית באקסל למשל רשימה של 50 תוצאות לדוגמה... דרך אגב באקסל יש פונקציית RAND שעובדת שונה ב SQL ומייצרת בכל שורה מספר חדש בילתי תלוי בקודם). תנסה לגוון כמה שאפשר ולשים דוגמה לכל מקרה... אולי זה יעזור להבין מה אתה מחפש.


    [Personal Site] [Blog] [Facebook]signature

    יום רביעי 30 אוקטובר 2013 13:29
    מנחה דיון
  • שלום,

    חברי הקהילה השקיעו מזמנם ונסיונם על מנת לסייע לך למצוא פתרון לבעייתך.

    לכן, אם אחת מן התשובות פתרה או עזרה לפתור את בעייתך, אנא סמן אותה על ידי לחיצה על "סמן כתשובה".

    אם מצאת פתרון אחר, נשמח אם תשתף אותנו על מנת שאחרים אשר מגיעים לפורום יוכלו להעזר בו.

    תודה,

    צוות הפורומים.


    מיקרוסופט מציעה שירות זה ללא תשלום, למטרת סיוע למשתמשים והעשרת הידע הקשור בטכנולוגיות ובמוצרים של מיקרוסופט. תוכן זה מתפרסם כפי שהוא והוא אינו מעיד על כל אחריות מצד מיקרוסופט.

    יום ראשון 03 נובמבר 2013 14:26
  • היי איוון,

    מצורפת שאילתה שמחזירה מחרוזת רנדומלית. השאילתה מחזירה שתי עמודות, הראשונה נקראת RandomString1 והיא מחזירה מחרוזת קצרה יחסית, העמודה השנייה נקראת RandomString2 והיא ארוכה יותר. אני מקווה שזה יעזור. בכל מקרה בהצלחה בראיון!

    חקיקת יוסי 

    ;WITH cte_1
    AS
    (
    SELECT 
       ROW_NUMBER() OVER (ORDER BY NEWID() ASC) AS RN,
       t.name
    FROM
       sys.tables AS t
    CROSS JOIN
       sys.tables AS tt
    
    ),
     cte_2
    AS
    (
    SELECT 
       ROW_NUMBER() OVER (ORDER BY NEWID() ASC) AS RN,
       t.name
    FROM
       sys.columns AS t
    CROSS JOIN
       sys.columns AS tt
    
    )
    
    SELECT 
        cte_1.name + cte_2.name AS RandomString1,
        REPLICATE(cte_1.name + cte_2.name,CASE WHEN ABS (CHECKSUM (NEWID ())) % 4 = 0 THEN 1 ELSE ABS (CHECKSUM (NEWID ())) % 4 + 1 END) AS RandomString2
    FROM 
        cte_1 
    INNER JOIN
        cte_2 
    ON 
        cte_1.RN = cte_2.RN
    

    יום ראשון 03 נובמבר 2013 18:04
  • יוסי אני מאוד ממליץ לך לא לעבוד בשיטה זו אם אתה מחפש רנדומליות מלאה

    א. אתה לא מייצר פונקציה רנדומלית בשום מקרה אלא מתבסס על נתונים. אם יש נתון שמופיע יותר פעמים אז יצרת עדיפות לתוצאה זו, אם יש אות שאין שום שמתחיל בו אז אתה לא תקבל בתוצאה שרשרת שמתחילה באות זו וכן הלאה.

    זה רעיון שיכול לעזור למילוי מהיר של טבלה בנתוני טקסט שניראים רנדומליים אבל זה בהחלט לא פונקציה רנדומלית במובן המקובל אלא בחירה רנדומלית.

    ב. אם מישהו מעוניין אני יכול לפרסם פונקצייה פשוטה לייצור שרשרת טקסט רנדומלית. עדיין לא הבנתי מכל השרשור אם הצורך כאן היה מספר רנדומלי או שרשרת רנדומלית (שאלתי את זה כבר כמה פעמים בשרשור וזה נאבד בדרך ללא תשובה). יש לי פונקציה שמקבלת 2 פרמטרים: (א) שרשרת תווים (ב) 0/1 סוג התוצאה. הפונקציה מייצרת שרשרת רנדומלית באורך מסויים או באורך רנדומלי (לפי פרמטר ב), מתוך רשימת תוויים בהם רוצים להישתמש (פרמטר א). זה מאפשר למשל יצירת שרשרת רנדומלית בשפה מסויימת או לבחור רק אותיות קטנות או רק אותיות (גדולות וקטנות) או אותיות ומספרים וכן הלאה... מאוד מתאים לצרכים כמו ססמה אקראית (בלי הקידוד למשל). זו פונקציה מאוד וותיקה שיושבת אצלי בכל שרת במסד נתונים של עזרים, אני נעזר בה למילוי טבלאות לצרכי בדיקות למשל.


    [Personal Site] [Blog] [Facebook]signature

    • נערך על-ידי pituachMVP, Editor יום שני 04 נובמבר 2013 05:37
    • סומן כתשובה על-ידי Eran Sharvit יום שני 04 נובמבר 2013 09:29
    יום שני 04 נובמבר 2013 05:29
    מנחה דיון
  • איוון,

    קיבלת כאן כל מיני הצעות לפתרון, ולפי מה שהבנתי ממך, אף אחת לא ממש עונה על הצרכים שלך.

    אם אתה עדיין איתנו, אז אשמח אם תוכל לפרט קצת יותר לגבי הצרכים שלך, על מנת שנוכל למצוא את הפתרון המתאים בשבילך.

    תודה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    יום ראשון 10 נובמבר 2013 11:27
    מנחה דיון
  • שלום לכולם,

    אני ממש מתנצל שנעלמתי בלי להודיע (הייתי בטיול של שבועיים בחו"ל) מעריך את עזרתכם.

    גיא,

    בהחלט, אך תשובה שנותנה פה לא ממש עונה על השאלה.

    היום הוא יום הראשון שלי בעבודה החדשה באותה חברה שבה נשאלתי את השאלה הזאת. :) להלן התשובה של DBA הבכיר של החברה (הוא ביקש לא לפרסם את שמו):

    SELECT top 1000
           LEFT (
                 NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
              + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))
                 
              , 1 + CAST(29 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT)
              ) AS Str
    from sys.columns a CROSS JOIN sys.columns b
    

    • סומן כתשובה על-ידי Ivan Radchenko יום ראשון 24 נובמבר 2013 11:17
    יום ראשון 24 נובמבר 2013 11:16
  • הי,

    הנה פתרון קצת יותר אלגנטי...

    SELECT TOP (1000)
    	String
    FROM
    	sys.columns AS T1
    CROSS JOIN
    	sys.columns AS T2
    CROSS APPLY
    	(
    		SELECT TOP (ABS (CHECKSUM (NEWID () , T1.object_id)) % 30 + 1)
    			NCHAR (ABS (CHECKSUM (NEWID () , T1.object_id)) % 1000)
    		FROM
    			sys.columns
    		FOR XML
    			PATH (N'')
    	)
    	AS
    		RandomCharacters (String)
    ORDER BY
    	NEWID () ASC;
    GO
    
    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Ivan Radchenko יום שני 25 נובמבר 2013 10:18
    יום שני 25 נובמבר 2013 09:17
    מנחה דיון
  • היי גיא,

    הפיתרון שלך הוא בהחלט יותר אלגנטי, רואים שלמדת הרבה אתמול (כמו כולנו) :)

    לעומת זאת, במבט ראשון הוא גם הרבה יותר איטי... אנסה יותר מיוחר לחקור למה ובתקווה לשפר אותו עוד יותר!

    תודה רבה!

    יום שני 25 נובמבר 2013 10:18
  • איבן,

    נקודה קטנה אם אפשר...
    אני שאלתי אותך מההתחלה אם אתה רוצה שרשרת רנדומלית או מספר רנדומלי ולא ענית על זה. כל הדיון המרכזי היה לפי מספר רנדומלי כמעט עד הסוף. בדוק את ההיסטוריה בשרשור :-)

    הפתרון של המנהל שלך גרוע מאוד לשרת חי הן מבחינת נוחות וגמישות והן מבחינת משאבים ומהירות. זו שאלה מעולה לראיון עבודה ובהחלט פתרון טוב מאוד לראיון עבודה אבל...

    הסיבה ששאלתי את זה מההתחלה היא כי יש לי פונקציה מוכנה שבונה שרשרת רנדומלית. אני ממלא בעזרתת מליוני רשומות של טקסטים באורך של עד 4000 (אפשר כמובן יותר) בשביל משחקים ובדיקות.

    בוא נראה מה הבעיה בפתרון שלכם:

    1. איטי השוואה על 10 אלף רשומות הפונקציה שלי מחזירה תשובה תוך אפס שניות והפונקציה שלכם תוך 5 שניות

    * לא מדוייק כי לא בדקתי זמן אלא רק מה שה SSMS מראה למטה

    * הוספתי כמה שורות של יצירת תווים כדי שאוכל להחזיר שרשרת על עד 100 תווים

    2. השיטה הזו איומה מבחינת נוחות וגמישות

    מה אם מחר תרצה לבנות שרשרת של 4000 תווים כדי למלא טבלה של מליון רשומות

    4000 תווים יחייב אתכם לשנות את הפונקציה ולהוסיף קוד ענק והכוונה ל

    ע נ ק !!!

    הפונקציה שלי גמישה לחלוטין ויכולה להחזיר כל אורך של שרשרת שרוצים

    * זו שיטה מעולה למבחן ראיון עבודה! אני מדבר על החיים האמיתיים ואם אתם עושים שמוש בפונקציה אז אני ממליץ לך להפסיק ולעבור מייד ל CLR במקרה הנוכחי.

    כן... הפונקציה שלי היא ב CLR כמובן :-)

    הפונקציה תעלה לאוויר בבלוג בהיזדמנות ראשונה...


    [Personal Site] [Blog] [Facebook]signature

    יום שני 25 נובמבר 2013 15:17
    מנחה דיון
  • צריך לערוך הרצאה של CLR ולהפנים את הקיצוניות בהבדלים והשיפור שניתן לקבל (לפעמים)!

    אנשים משקיעים ימים ושבועות כדי לשפר 30% מהירות שאילתה בזמן שבמעבר ל CLR ניתן לשפר לפעמים בעשרות אלפי אחוזים.

     אני יכול להדגים את הנושא עם מספר פונקציות שכולנו צריכים בחיי היום יום...

    לפעמים מדובר על שורה אחת של קוד או אפילו 2 מילים של קוד ב CLR כמו במקרה של SPLIT שהיא פשוט פונקציה מוכנה ב CLR (אדם מקרניק פיתח פונקציה יותר טובה ל SQL אבל את ההבדלים ניתן לראות גם בפונקציה המובנית).


    [Personal Site] [Blog] [Facebook]signature

    יום שני 25 נובמבר 2013 15:36
    מנחה דיון
  • היי רונן,

    1) מצטער שפספתי את השאלה שלך, אבל כן עניתי עליה בעקיפין, כאשר פסלתי את הפיתרון הראשון שגיא הציע. :)

    2) לדעתי אתה קצת מגזים כשאתה קורה שפיתרון של הר"ץ שלי הוא גרוע מאוד.אני מאמון לך שהוא יותר איטי מ-CLR, אבל הוא יותר מהיר מפיתרון האלגנטי של גיא. אז אולי הוא לא פיתרון הכי טוב שיש, אבל הוא גם לא "גרוע מאוד".  חוץ מזה, הוא כמובן נועד לבדיקות ולא לשרת חי (למה שאני ארצה למלא שרת חי בנתונים רנדומליים?). בכל אופן, אשמח לשחק עם הפונקציה שלך כשהיא תעלה לאוויר. :)

    יום שלישי 26 נובמבר 2013 08:03
  • הוא פתרון מעולה

    כתבתי את זה בסיום בשביל המתח :-) ובשביל להדגיש את הבעיות בו קודם

    אבל הוא לא פתרון לשרת חי אלא רק לשאלת מבחן. לשרת חי רוצים מיטוב+גמישות

    הפונקציה כבר באוויר מאתמול, שמתי שרשור נפרד וגם הודעה בפייסבוק
    עד לרגע זה כניראה שאף אחד לא מצא שזה מועיל אפילו או שאף אחד לא קרא את ההודעה :-(
    חפש שרשור עם הכותרת SQL Random String using CLR

    שים לב שאם רוצים להריץ אותה בשרת חי באופן קבוע אז רשמתי כיצד למטב אותה. אני משתמש בה כבר הרבה מאוד זמן אבל לא יצא לי לעבוד איתה באופן חי בשרת עמוס, אלא רק למילוי טבלאות (גם בשרתים מאוד עמוסים אבל זו פעולה חד פעמית) ולכן לא עברתי על הקוד ולא השקעתי יותר מכמה שניות לכתוב אותו והוא נתקע בצורה הראשונית שלו :-)


    [Personal Site] [Blog] [Facebook]signature

    יום שלישי 26 נובמבר 2013 08:19
    מנחה דיון
  • העלתי מאמר שמבוסס על השרשור הנוכחי. במאמר כללתי קצת יותר מידע על ייתרונות וחסרונות של שיטות שונות. כמו כן העלתי קודים נוספים לאוויר של פונקציות שהיו לי ליצירת שרשרת רנדומלית.

    מקווה שזה יועיל

    http://social.technet.microsoft.com/wiki/contents/articles/21196.random-string.aspx

    * רשמתי את כל מי שלקח חלק כאן בדיון כמי שתרם למאמר. אם מישהו רוצה שאני אוריד את השם שלו משום מה אז אנא תגידו, אם כי אני לא יכול לחשוב למה שמישהו ירצה... אני חייב לשאול מטעמי הגינות :-)


    [Personal Site] [Blog] [Facebook]signature

    יום רביעי 27 נובמבר 2013 09:29
    מנחה דיון
  • רונן,

    שיחקתי קצת עם הפונקציה שלך וקיבלתי בדיוק את אותו ה-execution plan (שזה הגיוני, היות והפונקציה שלך מחזרה שורה אחת) :

    איפה אני טועה?


    • נערך על-ידי Ivan Radchenko יום חמישי 28 נובמבר 2013 13:11
    יום חמישי 28 נובמבר 2013 13:09
  • אני אתן הסבר מעמיק, יותר מאוחר (אני צריך להתארגן ולצאת עוד שעה) אבל הנה רמז טוב

    אם אני מכין 2 פונקציות של CLR שצריכות רק להחזיר את המספר 1

    פונקציה A: שורה אחת return 1

    פונקציה B: מבצעת לולאות על כל הטבלאות בשרת, מבצעת חישוב של 1+1+1...+1 ועוד כמה פעולות מתמטיות כאלה, בונה מערך של אלף נתונים באורך 10K תווים ועושה כמה לולאות נחמדות,  ובסוף מתעלמת מכל החישוב ופשוט מחזירה return1

    האם תוכניות ההרצה יהיו שונות?
    האם מהירות ההרצה (והמשאבים) תהיה זהה?


    [Personal Site] [Blog] [Facebook]signature

    יום חמישי 28 נובמבר 2013 14:20
    מנחה דיון
  • רונן,

    אם הבנתי אותך נכון, אתה רוצה להגיד שתוכניות ההרצה יהיו זהות בשתי המקרים, אבל הביצועים יהיו שונים. זכור לי משהו שראיתי באחת מההרצאות ב- ISUG, מישהו הראה שברגע שמפעילים פונקציה על כל שורה, האופטימייזר (לפעמים?) לא יודע להעריך נכון את המשקל שלה, לכן האחוזים שאנחנו רואים בתוכניות ההרצה (שהם תמיד estimated) לגמרי לא נכונים במקרים כאלה, לכן תמונה בפוסט הקודם שלי לא משקפת את המציעות. האם זה מה שניסית להגיד?  


    • נערך על-ידי Ivan Radchenko יום ראשון 01 דצמבר 2013 07:08
    יום ראשון 01 דצמבר 2013 07:07
  • "לא משקפת את המציאות" -> "לא משקפת את המציאות במלואה" :-)

    לא הכל ניראה על ידי תוכנית ההרצה. למשל הלוגיקה שפונקצייה מסויימת עובדת כמו LEFT לא מוצג לנו לפעמים, ואולי מייקרוסופט פיתחו את הקוד מאחורי הקלעים בצורה לא טובה.

    * דרך אגב לפעמים הרבה מוסתר גם בתוך ה XML שלא ניתן לראות בתוכנית ההרצה הגרפית. לכן בניטור טוב צריך לעבור על ה XML ולא רק התמונה. אבל לפעמים גם ב XML לא מוצג הכל כאמור ולא הכל אינו ידוע בכלל לשרת.

    לכן מסקנה: תוכנית ההרצה נועדה ככלי לעזרה אבל היא לא מציגה הכל. היא לא תהיה כלי יחיד לקבלת החלטות. כדאי לשים לב ל XML שמפרט יותר. חלק מהדברים ניסתרים גם בפני השרת עצמו. תמיד בעבודה עם CLR צריך לזכור שלא עובדים בשיטת IN-LINE (פונקציה שהשרת מפרק למעשה ומשלב עם השאילתה החיצונית). צריך להיות זהיר בחישובי עלויות במקרים כאלו.

    הנה תרגיל נוסף יותר קטן ופשוט לגבי הבעיה של שימוש רק בצורה הגרפית וזה עוד בלי CLR או קופסאות שחורות:

    קח את השאילתה שלך. ובנה 2 שאילתות. באחת תשאיר רק 2 שורות קוד:

     + NCHAR(CAST(1000 * RAND( ABS(CHECKSUM(CAST(NEWID() AS VARCHAR(100)))) ) AS INT))

    ובשאילתה השנייה תשאיר 100 שורות קוד.

    בדוק את תוכנית ההרצה הגרפית :-)
    רמז: למרות שברור לך ההבדל הענק הרי שהתוכנית הגרפית זהה
    אתה רוצה את ההסבר המלא יותר או שזה מובן באופן כללי מההסבר כאן?

    * אם הנושא סגור אז אנא נסה לעבור על כל השרשור הארוך והמשך את פעולת סימון/הצבעה לתשובות והודעות מועילות :-)
    זה אחד השרשורים היותר ארוכים שיש בפורום. זה לא משהו רע... אלא טוב מאוד זה מצביע על דיון אמיתי ולכן גם סיכמתי הכל במאמר. אני אשמח אם תעבור על כל המאמר שהעלתי. אתה יכול גם להוסיף שם הערות/פידבקים כמובן.


    [Personal Site] [Blog] [Facebook]signature

    יום ראשון 01 דצמבר 2013 14:32
    מנחה דיון
  • הבנתי אותך, אתה צודק. השוואתי את שתי הפיתרונות עבור 1000K שורות וקיבלתי אותה חלוקה באחוזים בתוכניות ההרצה כמו קודם, אומנם כשאני מריץ אותם בנפרד, אני רואה שהפיתרון שלך הוא פי 2 יותר מהיר.

    תודה רבה!

    יום שני 02 דצמבר 2013 11:00
  • תודה על התודה :-)

    אני שמח שזה עזר, היה שרשור מאוד ארוך. אני חושב שהוא היה מאוד מועיל גם לבאים שיראו אותו, מה גם שבזכותך היה לי נושא ל 2 מאמרים חדשים (הפרדתי את המאמר על פתרון CLR ומאמר על פתרון TSQL). דרך אגב ככל שתעלה את מספר הנתונים ואורך השרשרת תראה שהזמן שלוקח ל CLR לרוץ כמעט לא משתנה והזמן של ה TSQL קופץ פלאים. אני הרצתי על כמה מליון רשומות וה CLR סיים בכמה שניות וה TSQL לא סיים ב 20 דקות (הפסקתי אחרי 20), זה לא פי 2 אלא פי... 

    הערה חשובה: במקרה שלנו בגלל שמחזירים הרבה רשומות הרי שיש משמעות לכמות זכרון+מהירות הכתיבה לדיסק. צוואר הבקבוק אצלי במחשב במשרד בכלל לא היה הקוד של ה CLR. הקוד המתין לשרת ה SQL לכתיבה לדיסק שהיתה איטית ולכן בדקתי על שרת ושם קיבלתי את ההבדלים האמיתיים יותר (עדיין תמיד בכמות גדולה המגבלה היתה מהירות הכתיבה לדיסק כי השרת כותב ל tempdb).

    לסיכום הנושא של מהירות CLR הנה בלוג שדן בדיוק בנושא זה וביצע מדידות מדוייקות ומעמיקות (מדגים את הנושא עם פונקציית SPLIT אבל הרעיון דומה). השוואה של כמויות נתונים שונים והגרפים יכולים ללמד את ההתנהגות. ברשומה אחת אין הבדל מדיד, ב 1000 קיבלת פי 2 אבל במליון.. ובמליארד... וכן הלאה... שים לב שזמן ההרצה של ה CLR כמעט לא משתנה.

    http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

    CLR SPLIT


    [Personal Site] [Blog] [Facebook]signature

    יום שני 02 דצמבר 2013 11:59
    מנחה דיון